【ORM】关于Dapper的一些常见用法

引言

     Dapper是.Net平台下一款小巧玲珑的开源Orm框架,简单实用的同时保持高性能,非常适合我这种喜欢手写SQL的人使用,下面介绍一下如何使用Dapper.

相关资料

     Dapper的GitHub地址是dapper-dot-net,上面有一些用法和性能的介绍,有兴趣者可以上去看看.

数据库设计和实体类

    简单设计两个表,分别为部门表,雇员表.

CREATE TABLE [dbo].[T_Dept] (
    [Id]     INT        IDENTITY (1, 1) NOT NULL,
    [Dept]   NCHAR (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DeptNo] NCHAR (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[T_Employee] (
    [Id]           INT        IDENTITY (1, 1) NOT NULL,
    [DeptNo]       NCHAR (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [EmployeeName] NCHAR (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

  实体类如下

   class Depts
    {
        public int Id { get; set; }
        public string Dept { get; set; }
        public string DeptNo { get; set; }

        public List<Employees> EmployeeList { get; set; }
    }

    class Employees
    {
        public int Id { get; set; }
        public string DeptNo { get; set; }
        public string EmployeeName { get; set; }
    }

连接静态类

  public static class SqlServerConnect
    {
        public static SqlConnection SqlConnection()
        {
            string sqlconnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            var connection = new SqlConnection(sqlconnectionString);
            connection.Open();
            return connection;
        }

    }

Insert

    Insert有几种写法,分别直接绑定参数,数组,或者集合,Execute的返回值是指受影响的行数.

               using (SqlConnection con = SqlServerConnect.SqlConnection())
              {

                //直接绑定参数
                con.Execute("insert into T_Dept(Dept,DeptNo) values (@dept,@deptno) ", new { Dept = "销售部", Deptno = "1" });
                //匿名数组
                con.Execute("insert into T_Employee(EmployeeName,DeptNo) values (@EmployeeName,@DeptNo) ",
                 new[] { new { EmployeeName = "李四", DeptNo = "1" }, new { EmployeeName = "张三", DeptNo = "1" } });
                //集合
                List<Employees> employees = new List<Employees>();
                employees.Add(new Employees { DeptNo = "2", EmployeeName = "老王" });
                employees.Add(new Employees { DeptNo = "2", EmployeeName = "老李" });
                con.Execute("insert into T_Employee(EmployeeName,DeptNo) values (@employeeName,@deptNo) ", employees);
             }

 Update和Delete

     Update和Delete没什么好说的了,用法一样,举例匿名数组的用法

    using (SqlConnection con = SqlServerConnect.SqlConnection())
    {
      con.Execute("update  T_Dept set Dept=@name  where  DeptNo=@deptno  ",new[] { new { name = "人事部二", deptno = "3" },new { name = "销售部二", deptno = "1" } });

      con.Execute("delete from   T_Employee   where  EmployeeName=@name  ", new[] { new { name = "小王" }, new { name = "张三" } });
    }

Select

    Select操作主要是利用Query方法,也可以利用ExecuteScalar做一些获取数据库时间之类的操作,如下

         using (SqlConnection con = SqlServerConnect.SqlConnection())
            {
               IEnumerable<Depts> deptList = con.Query<Depts>("select id, Dept,DeptNo  from  T_Dept");

               DateTime  dateTime= con.ExecuteScalar<DateTime>("select  getdate()");

               Console.WriteLine(dateTime);
           }

存储过程

   主要演示一下输入输出参数的过程和返回多游标的过程.

   过程如下

CREATE PROCEDURE [dbo].P_Emploee_Ins
    @In_DeptNo NVARCHAR(20) ,
    @In_EmployeeName NVARCHAR(20),
    @Out_Message  NVARCHAR(20) output
AS
 
     insert  into T_Employee( DeptNo, EmployeeName) values (@In_DeptNo,@In_EmployeeName);
     set @Out_Message='完成';

CREATE PROCEDURE [dbo].P_DeptEmploee_Sel
    @In_DeptNo NVARCHAR(20) ,
    
    @Out_Message  NVARCHAR(20) output
AS
    SELECT * from  T_Dept t  where t.DeptNo=@In_DeptNo;
    SELECT * from  T_Employee t  where t.DeptNo=@In_DeptNo;

   调用如下

       using (SqlConnection con = SqlServerConnect.SqlConnection())
            {
                DynamicParameters p = new DynamicParameters();
                p.Add("@In_DeptNo", "3");
                p.Add("@In_EmployeeName", "小李");
                p.Add("@Out_Message", "", DbType.AnsiString, ParameterDirection.Output);
                con.Execute("dbo.P_Emploee_Ins", p, commandType: CommandType.StoredProcedure);
                string message = p.Get<string>("@Out_Message");
            }

      using (SqlConnection con = SqlServerConnect.SqlConnection())
            {
                DynamicParameters p2 = new DynamicParameters();
                p2.Add("@In_DeptNo", "3");
                p2.Add("@Out_Message", "", DbType.AnsiString, ParameterDirection.Output);

                SqlMapper.GridReader gridReader = con.QueryMultiple("dbo.P_DeptEmploee_Sel", p2, commandType: CommandType.StoredProcedure);

                IEnumerable<Depts> dept = gridReader.Read<Depts>();
                IEnumerable<Employees> employee = gridReader.Read<Employees>();

                string message2 = p.Get<string>("@Out_Message");
            }

事务操作

      using (SqlConnection con = SqlServerConnect.SqlConnection())
            {
                IDbTransaction transaction = con.BeginTransaction();
                con.Execute("insert into T_Employee(EmployeeName,DeptNo) values (@EmployeeName,@DeptNo) ",
                new { EmployeeName = "老王", DeptNo = "3" }, transaction: transaction);
                con.Execute("insert into T_Employee(EmployeeName,DeptNo) values (@EmployeeName,@DeptNo) ",
                new { EmployeeName = "老李", DeptNo = "3" }, transaction: transaction);
                transaction.Commit();
            }

小结

     本文介绍了Dapper的一些常见用法,而.Net4.5版的还有上述方法的异步实现的,这里就不一一示范了.话说以前都是连Oracle数据的,换成Sql Server用法几乎没有变化,感觉真好.如果你有更好的建议,请不吝留言指教.