.NET之Dapper架構運用

標籤：usermod   taf   name   ace   setting   程式包   oid   blog   var   

Dapper架構

1.項目引用Dapper的Nuget程式包;

2.配置連結類

using System;using System.Collections.Generic;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Text;using System.Threading.Tasks;namespace Dapper{    public class DapperConn    {        public static IDbConnection GetConnection()        {            string connStr = ConfigurationManager.AppSettings["conn"];            return new SqlConnection(connStr);        }    }   }

3.配置相應表的實體物件

目前是一個使用者表和一個使用者登入日誌表為例：

使用者表

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace Dapper{    public class UserModel    {        public Int32 Id { get; set; }        public String Key { get; set; }        public String Value { get; set; }    }}

使用者登入日誌表

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace Dapper{    public class UserLoginLog    {        public Int32 Id { get; set; }        public Int32 UserId { get; set; }        public DateTime CreateTime { get; set; }            }}

4.通過實體對資料庫操作

(包含基本的：增刪改查及事務提交操作)

using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Text;using System.Threading.Tasks;namespace Dapper{    public class DB    {               public int Add(UserModel model)        {            using (var conn = DapperConn.GetConnection())            {                string sql = "Insert into User (Key,Value) Value (@Key,@Value)";                return conn.Execute(sql, model);            }        }        public int Update(UserModel model)        {            using (var conn = DapperConn.GetConnection())            {                string sql = "update User set [email protected],[email protected] where [email protected]";                return conn.Execute(sql, model);            }        }        public int Del(UserModel model)        {            using (var conn = DapperConn.GetConnection())            {                string sql = "Delete from User where [email protected]";                return conn.Execute(sql, model);            }        }        public UserModel GetModel()        {            using (var conn = DapperConn.GetConnection())            {                var sql = "Select Id,Key,Value from User";                return conn.QueryFirstOrDefault<UserModel>(sql);            }        }        public IEnumerable<UserModel> GetModels()        {            using (var conn = DapperConn.GetConnection())            {                var sql = "Select Id,Key,Value from User";                return conn.Query<UserModel>(sql);            }        }        public void ImplementAffair(UserModel userModel, UserLoginLog userLogModel)        {            using (var conn = DapperConn.GetConnection())            {                IDbTransaction tran = conn.BeginTransaction();                try                {                    string query = "Update User set Key=‘測試‘ where [email protected]";//更新一條記錄                    conn.Execute(query, userModel, tran, null, null);                    query = "insert into UserLoginLog (userId,CreateTime) value (@userId,@CreateTime)";//刪除一條記錄                    conn.Execute(query, userLogModel, tran, null, null);                    //提交                    tran.Commit();                }                catch (Exception ex)                {                    //提交錯誤                    //復原事務                    tran.Rollback();                }            }        }    }}

 

.NET之Dapper架構運用