Dapper ORM usage-Invincible ORM under Net), dapperorm

Dapper ORM usage-Invincible ORM (conversion) and dapperorm Under Net

Reprinted from: http://www.renfb.com/blog/2011/Article/335
If you like native SQL statements and ORM simplicity, you will certainly like Dapper ROM.
Dapper advantages:
1. Dapper is a lightweight ORM class. The Code is a SqlMapper. cs file, and a very small Dll of 40 kb after compilation.
2. Dapper is fast. The speed of Dapper is close to that of IDataReader. The data obtained from the list exceeds that of the DataTable.
3. What database does Dapper support. Dapper supports a series of databases, such as Mysql, SqlLite, Mssql2000, Mssql2005, and Oracle. If you know the principles, Dapper can also support Mongo db.
4. Dapper's r supports multi-table parallel objects. Supports one-to-many relationships. It is not invasive and can be used if you want to use it. If you don't want to use it, you don't have. No XML, no attribute. How can I write code before.
5. Dapper uses Emit to reflect the sequence queue of IDataReader to quickly obtain and generate objects. High performance.
6. Dapper supports net2.0, 3.0, 3.5, and 4.0. [If you want to use Net2.0, go to the Internet and find out how to configure and run Net3.5 under Net2.0 .]
7. Dapper syntax is very simple. The database design is not required.

The following describes how to use Dapper for efficient development. The following operations are examples of operations under Net3.5 after compilation. Most functions under Net4.0 have default values, and the parameters are simple.

// Tables in the database:
Create table ColumnCat
(
Id int identity (1, 1) not null primary key,
Name nvarchar (150) NULL,
ModifiedOn smalldatetime null default (GETDATE ()),
Parentid INT
)

Create table Column
(
Id int identity (1, 1) not null primary key,
Name nvarchar (150) NULL,
ModifiedDate smalldatetime null default (GETDATE ()),
ColumnCatid INT null
)
Common tables, categories, and content tables can be classified into lower-level categories. The following operations are basically performed on these two tables.

// Connect to the database string.
Private readonly string sqlconnection =
"Data Source = RENFB; Initial Catalog = test; User Id = sa; Password = sa ;";
// Public readonly string mysqlconnectionString =
@ "Server = 127.0.0.1; database = test; uid = renfb; pwd = 123456; charset = 'gbk '";

// Obtain the database object connected to SQL Server. SqlConnection
Public SqlConnection OpenConnection ()
{
SqlConnection connection = new SqlConnection (sqlconnection );
Connection. Open ();
Return connection;
}
// Obtain the Connection database object of MySql. MySqlConnection
// Public MySqlConnection OpenConnection ()
//{
// MySqlConnection connection = new MySqlConnection (mysqlconnectionString );
// Connection. Open ();
// Return connection;
//}
Note: If you want to change to a Mysql database, you only need to comment out the functions of the database objects connected to SQL Server, and cancel the comments of the functions of the database objects connected to MySql, uncomment the Mysql connection string and modify it to your connection information.

Query () method:
Query () is an extended IDbConnection method and is overloaded. It extracts information from the database and fills in our business object model.

// Create a class first, which is the model of the ColumnCat table of the database.
Public class ColumnCat
{
Public int Id {get; set ;}
Public string Name {get; set ;}
Public DateTime ModifiedOn {get; set ;}
Public int Parentid {get; set ;}
}

// Obtain the set of ColumnCat objects.
Public IEnumerable <ColumnCat> SelectColumnCats ()
{
Using (IDbConnection conn = OpenConnection ())
{
Const string query = "select * from ColumnCat order by id desc ";
Return conn. Query <ColumnCat> (query, null );
}
}
Just as simple as embedding SQL directly in the example, it is easy to expand to a stored procedure. You can use aliases to match the columns in the result set with the attributes of the Business Object Model (ColumnCat.

// The following uses the preceding set to display the category.
List <ColumnCat> AllColumnCat = SelectColumnCats (). ToList <ColumnCat> ();
Foreach (ColumnCat cat in AllColumnCat. Where (c => c. Parentid = 0 ))
{
Response. Write ("Name =>" + cat. Name + "\ t ");
Response. Write ("time =>" + cat. ModifiedOn + "\ t ");
Response. Write ("<br/> ");

Foreach (ColumnCat c in AllColumnCat
. Where <ColumnCat> (subColumnCat => subColumnCat. Parentid = cat. Id ))
{
Response. Write ("& nbsp; ++ ");
Response. Write ("Name =>" + c. Name + "\ t ");
Response. Write ("time =>" + c. ModifiedOn + "\ t ");
Response. Write ("<br/> ");
}
}
// Display level-1 and level-2 categories on the page. If a recursion is used, it is easy to implement unlimited level classification (You know ).

// Obtain a single ColumnCat object.
Public ColumnCat SelectColumnCat (int columnCatId)
{
Using (IDbConnection conn = OpenConnection ())
{
Const string query = "select * from ColumnCat where Id = @ id ";
Return conn. Query <ColumnCat> (query, new {id = columnCatId })
. SingleOrDefault <ColumnCat> ();
}
}
Here we pass a parameter to the Query method. The parameter can be any object and its attribute matches the SQL parameter in the Query. Because the Query always returns a set, we only need to call the SingleOrDefault method, because we know that 0 or 1 rows are always returned.

// Dapper can also load nested objects. In this case, considering the category attribute of news, class objects are returned,
// Create a Column Class
Public class Column
{
Public int Id {get; set ;}
Public string Name {get; set ;}
Public DateTime ModifiedDate {get; set ;}
Public ColumnCat {get; set ;}
}

// Next we will fill in our business objects.
Public IList <Column> SelectColumnsWithColumnCat ()
{
Using (IDbConnection conn = OpenConnection ())
{
Const string query = "select c. Id, c. Name, c. ModifiedDate, c. ColumnCatid
, Cat. id, cat. [Name], cat. ModifiedOn, cat. Parentid from [Column] as c
Left outer join ColumnCat as cat on c. ColumnCatid = cat. id ";
Return conn. Query <Column, ColumnCat, Column> (query
, (Column, columncat) =>{ column. ColumnCat = columncat; return column ;}
, Null, null, false, "Id", null, null). ToList <Column> ();
}
}
Note: 1. When filling in nested objects, You have to execute the ToList <> method. Otherwise, the ExecuteReader must have enabled and available connections. The current status of the connection is closed, and no error is reported for a single object. It is estimated that the connection is closed after using, and the nested object executes ExecuteReader during map, the list set must be returned before using ends.
2. There are many parameters for nested objects, mainly the first two parameters. Other parameters can be set to null, but only two parameters can be written in version 4.0, other parameters have default values. Note the splitOn parameter. This parameter cannot be blank. Otherwise, an error is returned because the object is a reference. [SplitOn parameter means to read the split column of the second object and read the second object from which column. If the auto-increment column in the table is Id, you can set this parameter to "Id "].

Execute method:
Just as the Query method retrieves data, the Execute method does not retrieve data. It is very similar to the Query method, but it returns the total number of results (the number of affected rows) rather than a collection of objects (such: insert update and delete ].

// Add a category to the database
Public int InsertColumnCat (ColumnCat cat)
{
Using (IDbConnection conn = OpenConnection ())
{
Const string query = "insert into ColumnCat ([name], ModifiedOn, Parentid)
Values (@ name, @ ModifiedOn, @ Parentid )";
Int row = conn. Execute (query, cat );
// Update the Object Id as the new Id in the database. If you do not need to obtain the new object after adding the object,
// Add the object to the database and comment out the following line.
SetIdentity (conn, id => cat. Id = id, "id", "ColumnCat ");
Return row;

}
}

Public void SetIdentity (IDbConnection conn, Action <int> setId, string primarykey
, String tableName)
{
If (string. IsNullOrEmpty (primarykey) primarykey = "id ";
If (string. IsNullOrEmpty (tableName ))
{
Throw new ArgumentException ("tableName parameter cannot be blank, It is the name of the queried table ");
}
String query = string. Format ("SELECT max ({0}) as Id FROM {1}", primarykey
, TableName );
NewId identity = conn. Query <NewId> (query, null). Single <NewId> ();
SetId (identity. Id );
}

Public class NewId
{
Public int Id {get; set ;}
}
Because Dapper is automatically bound by class attributes, the NewId class is added to obtain the Id of the added object. In this case, @ identity is used, and an error is always reported when used in Net3.5, you have to use the Max function. Of course, if you do not need to obtain the ID of the updated object, you do not need to use SetIdentity. This function is common.

// Compile the Dapper source code to generate the dynamic type used in Net4.0. You can use the newly added dynamic type in Net4.0,
// The implementation of SetIdentity is very convenient. As follows:
Public void SetIdentity <T> (IDbConnection conn, Action <int> setId)
{
Dynamic identity = connection. Query ("SELECT @ identity as Id"). Single ();
T newId = (T) identity. Id;
SetId (newId );
}

// Update a category:
Public int UpdateColumnCat (ColumnCat cat)
{
Using (IDbConnection conn = OpenConnection ())
{
Const string query = "update ColumnCat set name = @ Name
, ModifiedOn = @ ModifiedOn, Parentid = @ Parentid where Id = @ id ";
Return conn. Execute (query, cat );
}
}

// Delete a category:
Public int DeleteColumnCat (ColumnCat cat)
{
Using (IDbConnection conn = OpenConnection ())
{
Const string query = "delete from ColumnCat where id = @ id ";
Return conn. Execute (query, cat );
}
}
The following describes the advanced usage of Dapper.

// Examples of Dapper transaction processing, such as deleting a category and deleting all news under the category. Or when you delete a product,
// Delete all associated images in the product image table.
Public int DeleteColumnCatAndColumn (ColumnCat cat)
{
Using (IDbConnection conn = OpenConnection ())
{
Const string deleteColumn = "delete from [Column] where ColumnCatid = @ catid ";
Const string deleteColumnCat = "delete from ColumnCat where id = @ Id ";

IDbTransaction transaction = conn. BeginTransaction ();
Int row = conn. Execute (deleteColumn, new {catid = cat. Id}, transaction, null, null );
Row + = conn. Execute (deleteColumnCat, new {id = cat. Id}, transaction, null, null );
Transaction. Commit ();
Return row;
}
}