Dapper的使用

Dapper.NET是个开源的轻型ORM。它扩展了IDbConnection接口的功能，可以支持MS SQL Server、Oracle、MySQL、PostgreSQL、SQLite、SqlCe、Firebird等。

它的运行速度非常快，语法简单，能用很少的代码就能实现多条SQL语句一起被执行以及批量增、删、改。

下面列出其基本的用法：

首先创建对应的实体

    public class DapperDemoEntity
    {
        public int ID { get; set; }
        public string Test{ get; set; }
      
    }

新增单条记录（dapperDemo就是要填写的参数）

      public int InsertDapperDemo(DapperDemoEntity dapperDemo)
        {
            using (IDbConnection connection = Common.OpenConnection())
            {
                const string sql =
@"INSERT INTO dbo.DapperNETDemo(Test) VALUES (@Test);
  SELECT CAST(SCOPE_IDENTITY() AS INT)";

                int dapperDemoID = connection.Query<int>(sql, dapperDemo).Single();
                return dapperDemoID;
            }
        }

更新单条记录

            using (IDbConnection connection = Common.OpenConnection())
            {
                const string sql =
@"UPDATE dbo.DapperNETDemo 
  SET ParentID = @ParentID, 
  WHERE ID = @ID";
                return connection.Execute(sql, dapperDemo);
            }

删除单条记录的用法也跟上面一样，只是sql语句不同

批量插入,dapper支持list集合作为参数，批量的插入操作只需要一条sql语句即可，方便简单

        public int InsertDapperDemoList(IList<DapperDemoEntity> list)
        {
            using (IDbConnection connection = Common.OpenConnection())
            {
                var transaction = connection.BeginTransaction();
                var rowsAffectd =0;

                const string sql = @"INSERT INTO dbo.DapperNETDemo(Test) VALUES (@Test)";
                try
                {
                    rowsAffectd = connection.Execute(sql, list, transaction);
                    transaction.Commit();
                    return rowsAffectd;
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }

                
            }
        }

批量删除 ，（这里介绍使用多条sql语句，匿名参数）

      public int DeleteDapperDemoList(int id)
        {
            using (IDbConnection connection = Common.OpenConnection())
            {
                const string deleteChildSQL = @"DELETE FROM dbo.DapperNETDemo WHERE ID> 0 AND ID = @ID";
                const string deleteParentSQL = @"DELETE FROM dbo.DapperNETDemo WHERE ParentID < 1 AND ID = @ID";

                IDbTransaction transaction = connection.BeginTransaction();
                int rowsAffected = connection.Execute(deleteChildSQL, new { ID=id }, transaction);
                rowsAffected += connection.Execute(deleteParentSQL, new { ID = id }, transaction);
                transaction.Commit();
                return rowsAffected;
            }
        }  

查询某个指定的字段,(该字段属于什么类型，在<>里填写对应类型即可)

        public int GetChildDapperDemoCount()
        {
            using (IDbConnection connection = Common.OpenConnection())
            {
                const string sql = 
@"SELECT Test
FROM dbo.DapperNETDemo where ID=1";
                return connection.ExecuteScalar<string>(sql);
            }
        }

获取列表

        public IEnumerable<DapperDemoEntity> GetDapperDemoList()
        {
            using (IDbConnection connection = Common.OpenConnection())
            {
                const string query =@"SELECT *  FROM dbo.DapperNETDemo";
                return connection.Query<DapperDemoEntity>(query);
            }
        }

这里指出一种返回子父类的列表

    public class DapperDemoEntity
    {
        public int ID { get; set; }
        public int ParentID { get; set; }
        public string DapperDemoName { get; set; }
        public Common.Type Type { get; set; }
        public DateTime ModifiedDate { get; set; }        
        public DapperDemoParentEntity ParentDapperDemo { get; set; }
    }

    public class DapperDemoParentEntity
    {
        public int ParentID { get; set; }
        public string DapperDemoParentName { get; set; }
        public Common.Type ParentType { get; set; }
    } 

     /// <summary>
        /// 返回列表
        /// </summary>
        /// <returns></returns>
        /// <remarks>
        /// 若返回列表中的每个对象所包含的另1个对象也需要返回，则需要用到splitOn参数。
        /// 然而，如果第2个对象的分割列为Id，则可省略splitOn参数。      
        /// </remarks>
        public IEnumerable<DapperDemoEntity> GetChildDapperDemoWithParentList()
        {
            using (IDbConnection connection = Common.OpenConnection())
            {
                const string query =
@"SELECT child.ID, child.Name AS DapperDemoName, child.[Type], child.ModifiedDate, 
       parent.ID AS ParentID, parent.Name AS DapperDemoParentName, parent.[Type] AS ParentType
  FROM dbo.DapperNETDemo child WITH(NOLOCK)
       LEFT JOIN dbo.DapperNETDemo parent WITH(NOLOCK) ON parent.ID = child.ParentID
  WHERE parent.ID IS NOT NULL
  ORDER BY child.ModifiedDate DESC";
                return connection.Query<DapperDemoEntity, DapperDemoParentEntity, DapperDemoEntity>(query
                    , (child, parent) => { child.ParentDapperDemo = parent; child.ParentID = parent.ParentID ;return child; }, splitOn: "ParentID");
            }
        }

存储过程的执行

        public dynamic ProcedureWithOutAndReturnParameter1()
        {
            int successCode = -1;
            string resultMessage = string.Empty;
            using (IDbConnection connection = Common.OpenConnection())
            {
                DynamicParameters parameter = new DynamicParameters();
                string name = "test1";
                parameter.Add("@Name", name);             
                parameter.Add("@SuccessCode", dbType: DbType.Int32, direction: ParameterDirection.Output);
                parameter.Add("@ResultMessage", dbType: DbType.String, direction: ParameterDirection.Output, size: 255);
                parameter.Add("@Result", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

                IEnumerable<dynamic> result = connection.Query(sql: "DapperNETDemoSP1", param: parameter, commandType: CommandType.StoredProcedure);
                                
                successCode = parameter.Get<int>("SuccessCode");
                resultMessage = parameter.Get<string>("ResultMessage");

                int s = parameter.Get<int>("Result");
                dynamic row = result.Single();
                return row;               
            }
        }

对应的存储过程为

ALTER PROCEDURE [dbo].[DapperNETDemoSP1]
        @Name VARCHAR(10),                                            
        @SuccessCode INT OUTPUT,
        @ResultMessage VARCHAR(255) OUTPUT
AS

BEGIN

    SELECT @Name AS NameResult	
    SET @SuccessCode = 0
    SET @ResultMessage = '完成执行'
    RETURN 42
END

执行存储过程例子2

 /// <summary>
        /// 返回多个结果集
        /// </summary>
        /// <returns></returns>
        public IEnumerable<dynamic> ProcedureWithOutAndReturnParameter2()
        {
            using (IDbConnection connection = Common.OpenConnection())
            {
                DynamicParameters parameter = new DynamicParameters();
                string name = "test2";
                parameter.Add("@Name", name);               
                parameter.Add("@Result", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

                IEnumerable<dynamic> resultA = null;
                IEnumerable<dynamic> resultB = null;
                using (SqlMapper.GridReader grid = connection.QueryMultiple(sql: "DapperNETDemoSP2", param: parameter, commandType: CommandType.StoredProcedure))
                {
                    resultA = grid.Read<dynamic>();
                    resultB = grid.Read<dynamic>();
                }

                List<dynamic> result = new List<dynamic>();
                result.Add(resultA.Single());
                result.Add(resultB.Single());
                return result;
            }
        }

ALTER PROCEDURE [dbo].[DapperNETDemoSP2]
        @Name VARCHAR(10)
AS

BEGIN

    SELECT @Name AS NameResult1
	SELECT 2 AS NameResult2   
    RETURN 2
END