Universal Site Management backend (Bee OPOA Platform) (4)-DAL

This article describes the database access layer on the platform. At the beginning of the design, the main consideration is to facilitate the application, which is supported by the data access layer.

1. simple, convenient, and efficient. The API is simple and efficient.

2. nested call is supported. DbSession. Current

3. General purpose. Supports mainstream database applications. Verified items include Sqlite, SqlServer2008, SqlServer2005, and Oracle.

DbSession Introduction

Let's take a look at the main APIs of DbSession.

/* Implement the idisposable interface. We recommend that you use the Using Method */Public sealed class dbsession: idisposable {public dbsession (string connectionname) {/* use the connection name in the configuration file, transactions are not enabled by default */} public dbsession (string connectionname, bool usetransaction) {/* use the connection name in the configuration file, whether to enable transactions */} // <summary> // manually register the connection string. /// </Summary> Public static void register (string connectionname, string providername, string connectionstring) {} public int commandtimeout {Get; set ;} /* the timeout time for this instance call */public static dbsession current {Get;/* Get the dbsession instance in the current context, not across threads. If not stated in the context, use the first connection in the configuration file. */} Public void committransaction () {/* submits the transaction. if the transaction is not executed, the system automatically rollback. If an exception is thrown in the middle, the system automatically rolls back. */} Public int insert (string tablename, beedataadapter dataadapter) {/* specifies the table name and inserts a dataset. If it is not a field in the table, it is automatically ignored. */} Public void Update (string tablename, beedataadapter dataadapter, sqlcriteria) {/* specifies the table name, dataset, and condition set, update table */} public void Delete (string tablename, sqlcriteria) {/* specify the table name and condition set, and delete data */} public datatable query (string tablename, optional bytes) {} public datatable query (string tablename, sqlcriteria, string orderbyclause) {} public datatable query (string tablename, string selectclause, sqlcriteria, string orderbyclause, int pageindex, int pagesize, ref int recordcount) {} public datatable executecommand (string plain text, beedataadapter dataadapter) {} public list <t> executecommand <t> (string plain text, beedataadapter dataadapter adapter) {} public int insert <t> (T value ){}

        public void Delete<T>(SqlCriteria sqlCriteria) { }

        public int Save<T>(T value) { }

Public List <T> Query <T> () {} public List <T> Query <T> (SqlCriteria sqlCriteria) {} public List <T> Query <T> (SqlCriteria sqlCriteria, string orderbyClause, int pageIndex, int pageSize, ref int recordCount) {} public DataTable CallSP (string spName, BeeDataAdapter dataAdapter) {/* call the stored procedure. If the data parameter is not the stored procedure parameter, it is automatically ignored. Note that the parameter list of the stored procedure is cached. * //} # Region Schema Access // <summary> // obtain all tables, SP, View /// </summary> public List <DbObject> GetDbObject () {} public TableSchema GetTableSchema (string tableName) {} public SPSchema GetSpSchema (string spName) {}/// <summary> // according to the table structure, obtain the SQL statement for creating a table // </summary> public string ToCreateTableScript (TableSchema tableSchema) {}/// <summary> /// obtain the table structure by Type /// </summary> public TableSchema GetEntitySchema (type Type) {}# endregion}

In interface definition, most daily operations can be satisfied. This interface can also be used to obtain DataTable and Orm.

DbSesion nested call

Nested applications are usually used, especially in hierarchical system applications. Many underlying methods complete specific functions, such as obtaining document numbers. The method is as follows:

        public string GetBillId(string billType)        {            string result = string.Empty;            BillTypeConfig billTypeConfig = null;            DbSession dbSession = DbSession.Current;

DateTime dateTime = DateTime. today; string billDate = ""; // current period, for example, 20121225,201 212. billTypeConfig = dbSession. query <BillTypeConfig> (SqlCriteria. new. equal ("billtype", billType ). equal ("billdate", billDate )). firstOrDefault (); if (billTypeConfig = null) {billTypeConfig = new BillTypeConfig (); billTypeConfig. billType = billType; billTypeConfig. billDate = billDate; billTypeConfig. lastId = 1;} else {billTypeConfig. lastId = billTypeConfig. lastId + 1;} dbSession. save (billTypeConfig); // piece together the document number return result ;}

Without the red mark in the above Code, the general implementation does not care about the transaction, or the context of the current database access needs to be passed in as a variable. If you use DbSession. Current to process this type of situation, it is very elegant. This method will automatically follow the caller's connection, whether it is a new connection or whether to enable transactions.

Simple ORM implementation

All generic queryapis obtain the DataTable before performing ORM ing. The conversion between them is also achieved through the Emit dynamic generation interface (the implementation of an interface.