Using dapper to manipulate MySQL database

First of all, I want to explain: compared to the most primitive ado, generally think that the package over a layer of ORM performance will be lossy, but in fact, in the use you will find that when you need to convert database objects into a solid model, many so-called dbhelper in fact encapsulation is very inefficient, Instead, the performance of the mature ORM framework is very high;

Get the dapper and mysql.data packages in NuGet prior to Operation:

Insert data:

        /// <summary>        ///add a piece of data/// </summary>         Public BOOLADD (User model) {intCNT =0; stringSquery ="INSERT into User (id,login_name,user_pwd,user_name,phone_num,head_portrait,enabled,create_time,update_time) "+"VALUES (@Id, @Login_Name, @User_Pwd, @User_Name, @Phone_Num, @Head_Portrait, @Enabled, @Create_Time, @Update_Time) "; using(varConnection =Newmysqlconnection (CONNSTR)) {CNT=connection.            Execute (squery, model); }            if(CNT >0)            {                return true; }            Else            {                return false; }        }

Delete data

      /// <summary>        ///Delete a piece of data by ID/// </summary>         Public BOOLDelete (intID) {intCNT =0; stringSquery ="Delete from User"+"WHERE [email protected]"; using(varConnection =Newmysqlconnection (CONNSTR)) {CNT= connection. Execute (Squery,New{Id =ID}); }            if(CNT >0)            {                return true; }            Else            {                return false; }        }

modifying data

        /// <summary>        ///update One piece of data/// </summary>         Public BOOLUpdate (User model) {stringSquery ="UPDATE user SET [email protected]_name,[email protected]_pwd,[email protected]_name,[email Protected]_num,[email Protected]_portrait,[email protected],[email protected]_time,[email protected]_time"+"WHERE [email protected]"; intCNT =0; using(varConnection =Newmysqlconnection (CONNSTR)) {CNT=connection.            Execute (squery, model); }            if(CNT >0)            {                return true; }            Else            {                return false; }        }

Querying data

        /// <summary>        ///get entity objects by ID/// </summary>         PublicUser Getmodel (intID) {stringSquery ="SELECT Id,login_name,user_pwd,user_name,phone_num,head_portrait,enabled,create_time,update_time from User "+"WHERE Id = @Id"; using(varConnection =Newmysqlconnection (CONNSTR)) {                returnConnection. Query<user> (Squery,New{Id =ID}).            FirstOrDefault (); }        }

Call a paged stored procedure

        /// <summary>        ///get a list of data by page/// </summary>         PublicIenumerable<user> Getlistbypage (intPageSize,intPageIndex,stringStrwhere,stringORDERSTR,ref introwsnum) {            using(varConnection =Newmysqlconnection (CONNSTR)) {                varparam =Newdynamicparameters (); Param. ADD ("@p_table_name","User"); Param. ADD ("@p_fields","Id,login_name,user_pwd,user_name,phone_num,head_portrait,enabled,create_time,update_time"); Param. ADD ("@p_page_size", PageSize); Param. ADD ("@p_page_now", PageIndex); Param. ADD ("@p_where_string", strwhere); Param. ADD ("@p_order_string", ORDERSTR); Param. ADD ("@p_out_rows",0, Dbtype.int32, ParameterDirection.Output); IEnumerable<User> infolist = connection. Query<user> ("Pr_pager", Param,NULL,true,NULL, CommandType.StoredProcedure); Rowsnum = param. get<int> ("@p_out_rows"); returninfolist; }

In the test without any special optimization of code, it is very good to get and produce the dapper performance of the object quickly by emit reflection IDataReader sequence queue.

Using dapper to manipulate MySQL database