Dapper学习入门二

Dapper学习入门一：

https://blog.csdn.net/czjnoe/article/details/106392582

准备：

创建实体Course和StudentDto：

public class Course
    {
        public string ID { get; set; }
        public string CourseName { get; set; }//[CourseName]
    }



public class StudentDto
    {
        public string ID { get; set; }
        public string NAME { get; set; }

        public Course Course { get; set; }
    }

事务：

                using (IDbConnection db = DapperFactory.GetConnection(Enums.MyDbType.SqlServer, "Data Source=localhost;Initial Catalog=test;Integrated Security=True"))
                {
                        db.Open();
                        var tran = db.BeginTransaction();
                        try
                        {
                            Dictionary<string, object> dicParam = new Dictionary<string, object>();
                            dicParam["ID"] = Guid.NewGuid().ToString();
                            dicParam["NAME"] = "陈兆杰";
                            dicParam["TIME"] = DateTime.Now;
                             effectRows = db.Execute("insert into Student(ID,NAME,TIME) values(@ID,@NAME,@TIME)", dicParam, tran);

                            tran.Commit();
                        }
                        catch (Exception ex)
                        {
                            tran.Rollback();
                        }
                    }

存储过程：

Sql Server 存储过程：

USE [test]
GO
/****** Object:  StoredProcedure [dbo].[TestProc]    Script Date: 05/31/2020 13:48:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[TestProc]
@StuId    nvarchar(50),
@Name nvarchar(50) output
as
declare @result nvarchar;
begin
    if(@StuId is not null and @StuId <> '')
    begin
        select @Name=NAME
        from Student
        where ID=@StuId;
        return @StuId;
    end
    else
    begin
        set @Name='hihihi';
        return @StuId;
    end
end

调用存储过程:

                using (IDbConnection db = DapperFactory.GetConnection(Enums.MyDbType.SqlServer, "Data Source=localhost;Initial Catalog=test;Integrated Security=True"))
                {
                        //调用存储过程
                    {
                        var param = new DynamicParameters();
                        param.Add("@StuId", "859713145");
                        param.Add("@Name", dbType: DbType.String, direction: ParameterDirection.Output, size: 50);
                        param.Add("@result", dbType: DbType.String, direction: ParameterDirection.ReturnValue,size:50);//输出值需要赋值size

                        db.Execute("TestProc", param, commandType: CommandType.StoredProcedure);
                        string Name = param.Get<string>("Name");
                        int? ReturnValue = param.Get<int>("@result");
                    }
                    }

 

多结果查询：

 using (IDbConnection db = DapperFactory.GetConnection(Enums.MyDbType.SqlServer, "Data Source=localhost;Initial Catalog=test;Integrated Security=True"))
                {

                       //多结果查询(Read表顺序，要与sql语句表的顺序一致，不然会有问题)
                    {
                        string sql = @"
                            select * from Student where ID = @ID;
                            select * from Course;";
                        using (var multi = db.QueryMultiple(sql, new { id = "1" }))
                        {
                            var orders = multi.Read<Student>().ToList();
                            var customer = multi.Read<Course>().FirstOrDefault();

                        }
                    }
                 }

 

多表查询：

要是觉得第一种太麻烦，也可以输出成dynamic类型

注意：

splitOn：用来区分第二表的字段

 using (IDbConnection db = DapperFactory.GetConnection(Enums.MyDbType.SqlServer, "Data Source=localhost;Initial Catalog=test;Integrated Security=True"))
{
      string sql = @"select s.*,c.ID,c.CourseName from Student s,Course c where s.ID =c.ID and s.ID=@ID";

                        //方法1：
                        var list2 = db.Query<Student, Course, StudentDto>(sql, (student, course) =>
                       {
                           StudentDto stuDto = new StudentDto();
                           stuDto.NAME = student.NAME;
                           stuDto.ID = student.ID;
                           course.ID = student.ID;
                           stuDto.Course = course;
                           return stuDto;

                       }, new { ID = "1786863176" }, splitOn: "ID,CourseName").ToList();

                        //方法2：
                        var dynamic = db.Query(sql, new { ID = "1786863176" }).ToList();

                          //方法3：
                        var list3 = db.Query<StudentDto>(sql, new { ID = "1786863176" }).ToList();
}

 

扩展：

using (IDbConnection db = DapperFactory.GetConnection(Enums.MyDbType.SqlServer, "Data Source=localhost;Initial Catalog=test;Integrated Security=True"))
                {

                    var  list=db.Query<Student>("SELECT * FROM Student where ID in @IDS", new { IDS = new String[] { "1786863176", "1963140912" } }).ToList();


}