Dapper的使用

来源：互联网发布：手机淘宝怎么看评价编辑：程序博客网时间：2024/06/05 02:27

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 TestFROM 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) OUTPUTASBEGIN    SELECT @Name AS NameResult    SET @SuccessCode = 0    SET @ResultMessage = '完成执行'    RETURN 42END

执行存储过程例子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)ASBEGIN    SELECT @Name AS NameResult1SELECT 2 AS NameResult2       RETURN 2END

阅读全文

0 0