NET under the Invincible ORM

Dapper ORM Usage-net Invincible ORM

http://www.renfb.com/blog/2011/Article/335

If you like native SQL statements and you like the simplicity of ORM, then you will love the dapper ROM. Click here to download
Advantages of Dapper:
1,dapper is a lightweight ORM class. The code is a SqlMapper.cs file, compiled with a very small dll of 40K.
2,dapper soon. The speed of the dapper is close to the IDataReader, and the data for the list exceeds the DataTable.
What database is supported by 3,dapper. Dapper supports a series of databases such as mysql,sqllite,mssql2000,mssql2005,oracle, of course, if you know the principle it can also support MONGO DB
4,dapper's R supports multi-table parallel objects. Support a one-to-many relationship. And no intrusion, want to use, do not want to use it. No XML no attributes. How to write the code before how it is written.
The 5,dapper principle is to get and produce objects quickly by emit the sequence queue that reflects IDataReader. The performance is really high.
6,dapper supports net2.0,3.0,3.5,4.0. "If you want to use under the Net2.0, you can go to the Internet to find out how to configure the Net2.0 run Net3.5." 】
7,dapper syntax is very simple. And no need to compromise the design of the database.

Here is how dapper is used for efficient development, the following operations are compiled in Net3.5 under the example, Net4.0 most functions have default values, the parameters are very simple.

//数据库里的表：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)

Commonly used tables, categories and content tables, categories can have sub-categories. The following operations are essentially operations on both tables.

//连接数据库字符串。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‘";

//获取Sql Server的连接数据库对象。SqlConnectionpublic SqlConnection OpenConnection(){    SqlConnection connection = new SqlConnection(sqlconnection);    connection.Open();    return connection;}//获取MySql的连接数据库对象。MySqlConnection//public MySqlConnection OpenConnection()//{//     MySqlConnection connection = new MySqlConnection(mysqlconnectionString);//     connection.Open();//     return connection;//}

Note: If you need to switch to a MySQL database, simply comment out the function that will get the SQL Server connection database object, cancel the comment of the MySQL Connection database object function, cancel the comment of the MySQL connect string, and modify the connection information for yourself.

Query () method :
Query () is an idbconnection extension method and is overloaded, extracting information from the database and populating our business object model.

//先创建一个类,是数据库的ColumnCat表的模型。public class ColumnCat{    public int Id { get; set; }    public string Name { get; set; }    public DateTime ModifiedOn { get; set; }    public int Parentid { get; set; }}

//获取ColumnCat对象的集合。public IEnumerable<ColumnCat> SelectColumnCats(){    using (IDbConnection conn = OpenConnection())    {        const string query = "select * from ColumnCat order by id desc";        return conn.Query<ColumnCat>(query,null);    }}

It's that simple, embedding SQL directly in the example, it's easy to extend to stored procedures, and you can use aliases to make the columns in the result set correspond to the properties of the business object Model (COLUMNCAT).

//下面使用上面的集合显示出分类。List<ColumnCat> AllColumnCat =SelectColumnCats().ToList<ColumnCat>();foreach (ColumnCat cat in AllColumnCat.Where(c => c.Parentid == 0)){    Response.Write("Name==>" + cat.Name + "\t");    Response.Write("时间==>" + cat.ModifiedOn + "\t");    Response.Write("<br/>");    foreach (ColumnCat c in AllColumnCat                .Where<ColumnCat>(subColumnCat => subColumnCat.Parentid == cat.Id))    {        Response.Write("&nbsp;&nbsp;++++");        Response.Write("Name==>" + c.Name + "\t");        Response.Write("时间==>" + c.ModifiedOn + "\t");        Response.Write("<br/>");    }}//将一级类别和二级类别显示在页面上，如果使用一个递归，很容易实现无限级分类(你懂的)。

//获取单个ColumnCat对象。public ColumnCat SelectColumnCat(int columnCatId){    using (IDbConnection conn = OpenConnection())    {        const string query = "select * from ColumnCat where [email protected]";        return conn.Query<ColumnCat>(query, new { id=columnCatId})                        .SingleOrDefault<ColumnCat>();    }}

Here we pass a parameter to the Query method, which can be any object whose properties match the SQL parameters in the query, because query always returns a collection, we only need to call the Singleordefault method, because we know that we always return 0 or 1 rows.

//Dapper也可以加载填充嵌套对象，考虑这样一种情形，考虑到新闻的类别属性，返回类别对象，//我们创建一个Column的类public class Column{    public int Id { get; set; }    public string Name { get; set; }    public DateTime ModifiedDate { get; set; }    public ColumnCat ColumnCat { get; set; }}

//接下来我们来填充我们的业务对象。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 populating nested objects, had to execute the Tolist<> method, otherwise the return ExecuteReader required open and available connections. The current state of the connection is closed, and a single object does not give an error, it is estimated that the connection was closed after the using end, while the nested object was ExecuteReader at the time of the map and had to return the list collection before the using ended.
2, the parameters of the nested objects are more, mainly the first two parameters, the other parameters can not be set to NULL, but in the 4.0 version can write only two parameters, the other parameters have a default value. It is especially important to note that the Spliton parameter cannot be empty, otherwise the object is quoted as an error. The Spliton parameter means reading the split column of the second object, starting from which column to read the second object, and if the self-growing column in the table is an ID, you can set this parameter to "id".

Execute method :
Just as the query method retrieves data, the Execute method does not retrieve the data, which is very similar to the Query method, but it always returns the total number (the number of rows affected) instead of an object collection such as Insert update and delete.

Next add a category public int insertcolumncat (Columncat cat) {    using (IDbConnection conn = OpenConnection ()) to the database    {        Const string query = "INSERT into Columncat ([Name],modifiedon,parentid)        values (@name, @ModifiedOn, @Parentid) ";        int row = conn. Execute (query,cat);        The ID of the updated object is the new ID in the database, and if the addition does not need to get new objects,        //Simply add the object to the database, you can 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 ("the TableName parameter cannot be null, the table name for the query");    }    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;}}

Since dapper is automatically bound by the properties of the class, the NEWID class is added to get the ID of the object added, originally intended to use @ @identity, the use of the Net3.5 is always an error, had to use the Max function to obtain. Of course, if you do not need to obtain the updated object ID, you can not use setidentity, this function is common.

Compiled dapper source generated by the Net4.0 used, you can use the Net4.0 new dynamic type,//setidentity implementation will be 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 [email protected]                          , [email protected],[email protected] where [email protected]"; C7/>return Conn. Execute (Query,cat);}    }

Delete a category: public int deletecolumncat (Columncat cat) {    using (IDbConnection conn = OpenConnection ())    {        Const string query = "Delete from columncat where [email protected]";        Return Conn. Execute (query, cat);}    }

Here's a look at the advanced usage of dapper

Dapper The example of a transaction, such as deleting a category and deleting all news under the category. or delete the product at the same time,//delete all pictures associated with the Product picture table. public int Deletecolumncatandcolumn (Columncat cat) {    using (IDbConnection conn = OpenConnection ())    {        const String deletecolumn = "Delete from [Column] where [email protected]";        Const string Deletecolumncat = "Delete from columncat where [email protected]";        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;    }}

NET under the Invincible ORM