using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace DapperTest.Models
{
    public class StudentsService
    {
        private const string connectionString = "Data Source=.;Initial Catalog=efcoretest;Persist Security Info=True;User ID=sa;Password=111111";
        //单个插入
        public static int Insert(Students model)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                return connection.Execute("insert into Students(LastName,FirstMidName,EnrollmentDate) values(@LastName,@FirstMidName,@EnrollmentDate)", model);
            }
        }
        //批量插入
        public static int Insert(List<Students> models)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                return connection.Execute("insert into Students(LastName,FirstMidName,EnrollmentDate) values(@LastName,@FirstMidName,@EnrollmentDate)", models);
            }
        }
        //单个删除
        public static int Delete(Students para)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                return connection.Execute("delete from Students where ID=@ID", para);
            }
        }
        //批量删除
        public static int Delete(List<Students> paras)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                return connection.Execute("delete from Students where ID=@ID", paras);
            }
        }
        //单个修改(事务)
        public static int Update(Students para)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                IDbTransaction dbTransaction = connection.BeginTransaction();
                try
                {
                    int res = connection.Execute("update Students set LastName=@LastName where ID=@ID", para, dbTransaction);
                    dbTransaction.Commit();
                    return res;
                }
                catch (System.Exception)
                {
                    dbTransaction.Rollback();
                }
                return -1;
            }
        }
        //批量修改
        public static int Update(List<Students> paras)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                return connection.Execute("update Students set LastName=@LastName where ID=@ID", paras);
            }
        }
        //无参查询所有数据
        public static List<Students> Query()
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                //WHERE LastName like CONCAT('%',@LastName,'%')
                return connection.Query<Students>("select * from Students").ToList();
            }
        }
        //查询指定数据
        public static Students Query(Students para)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                return connection.Query<Students>("select * from Students where ID=@ID", para).SingleOrDefault();
            }
        }
        //In操作
        public static List<Students> QueryIn()
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                var sql = "select * from Students where id in @ids";
                //参数类型是Array的时候，dappper会自动将其转化
                return connection.Query<Students>(sql, new { ids = new int[2] { 1, 2 } }).ToList();
            }
        }
        public static List<Students> QueryIn(int[] ids)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                var sql = "select * from Person where id in @ids";
                //参数类型是Array的时候，dappper会自动将其转化
                return connection.Query<Students>(sql, new { ids }).ToList();
            }
        }
        //多语句操作
        public static StudentAndCourseDto QueryMultiple()
        {
            StudentAndCourseDto dto = new StudentAndCourseDto();
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                var sql = "select * from Students; select * from Courses";
                using (var multiReader = connection.QueryMultiple(sql))
                {
                    var studentsList = multiReader.Read<Students>().ToList();
                    var coursesList = multiReader.Read<Courses>().ToList();
                    dto.CourseList = coursesList;
                    dto.StudentList = studentsList;
                    return dto;
                }
            }
        }
        /// <summary>
        /// 其中，Query的三个泛型参数分别是委托回调类型1，委托回调类型2，返回类型。
        /// 形参的三个参数分别是sql语句，map委托，对象参数。
        /// 所以整句的意思是先根据sql语句查询；
        /// 同时把查询的person信息赋值给bookWithPerson.Pers，并且返回bookWithPerson；
        /// book是对象参数，提供参数绑定的值。最终整个方法返回BookWithPerson，这样我们所需要的所有信息就有了。
        /// </summary>
        /// <param name="book"></param>
        /// <returns></returns>
        public static BookWithPerson QueryJoin(Book book)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                var sql = @"select b.id,b.bookName,p.id,p.name,p.remark
                        from Person as p
                        join Book as b
                        on p.id = b.personId
                        where b.id = @id;";
                var result = connection.Query<BookWithPerson, Person, BookWithPerson>(sql,
                    (bookWithPerson, person) =>
                    {
                        bookWithPerson.Pers = person;
                        return bookWithPerson;
                    },
                    book, splitOn: "bookName");
                return (BookWithPerson)result;
            }
        }
    }
}

Dapper存储过程1

public List<Role> ExecuteStoredProcedureNoParms()
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                List<Role> roleList = new List<Role>();
                roleList = connection.Query<Role>("QueryRoleNoParms",
                                        null,
                                        null,
                                        true,
                                        null,
                                        CommandType.StoredProcedure).ToList();
                return roleList;
            }
        }

Dapper存储过程2 输入输出参数

public string ExecuteStoredProcedureWithParms()
        {
            DynamicParameters dp = new DynamicParameters();
            dp.Add("@RoleId", "1");
            dp.Add("@RoleName", "", DbType.String, ParameterDirection.Output);
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                connection.Execute("QueryRoleWithParms", dp, null, null, CommandType.StoredProcedure);
                string roleName = dp.Get<string>("@RoleName");
                return roleName;
            }
        }

通用分页

CREATE PROCEDURE [dbo].[PageList]
     @TableName VARCHAR(200),       --表名
     @FieldName VARCHAR(500) = '*', --字段名
     @Where VARCHAR(100) = NULL,    --条件语句
     @GroupBy VARCHAR(100) = NULL,  --分组字段
     @OrderBy VARCHAR(100),         --排序字段
     @PageIndex INT = 1,            --当前页数
     @PageSize INT = 20,            --每页显示记录数
     @TotalCount INT = 0 OUTPUT     --总记录数
AS
BEGIN
    --SQL拼接语句
    DECLARE @SQL NVARCHAR(4000)

    --总记录数
    SET @SQL='SELECT @RecordCount=COUNT(1) FROM ' + @TableName
    IF (ISNULL(@Where,'')<>'')
        SET @SQL=@SQL+' WHERE '+@Where
    ELSE IF (ISNULL(@GroupBy,'')<>'')
        SET @SQL=@SQL+' GROUP BY '+@GroupBy

    EXEC SP_EXECUTESQL @SQL,N'@RecordCount INT OUTPUT',@TotalCount OUTPUT

    --总页数
    DECLARE @PageCount INT
    SELECT @PageCount=CEILING((@TotalCount+0.0)/@PageSize)
    
    --简单分页
    SET @SQL='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY '+@OrderBy+') AS RowNum,' + @FieldName + ' FROM '+@TableName+' AS A'
    IF (ISNULL(@Where,'')<>'')
        SET @SQL=@SQL+' WHERE '+@Where
    ELSE IF (ISNULL(@GroupBy,'')<>'')
        SET @SQL=@SQL+' GROUP BY '+@GroupBy

    IF (@PageIndex<=0)
        SET @PageIndex=1
    IF @PageIndex>@PageCount
        SET @PageIndex=@PageCount
     
    DECLARE @BeginRowNum INT,@EndRowNum INT  
    SET @BeginRowNum=(@PageIndex-1)*@PageSize+1
    SET @EndRowNum=@BeginRowNum+@PageSize-1

    SET @SQL=@SQL + ') AS B WHERE B.RowNum BETWEEN '+CONVERT(VARCHAR(32),@BeginRowNum)+' AND '+CONVERT(VARCHAR(32),@EndRowNum)
    EXEC(@SQL)
END

代码

/// <summary>
        /// 通用分页
        /// </summary>
        /// <returns></returns>
        public IEnumerable<T> GetCommonPaging<T>(string tableName, string fieldName, string where, string groupby, string orderby, int pageIndex, int pageSize)
        {
            var para = new DynamicParameters();
            para.Add("TableName", tableName);
            para.Add("FieldName", fieldName);
            para.Add("Where", where);
            para.Add("GroupBy", groupby);
            para.Add("OrderBy", orderby);
            para.Add("PageIndex", pageIndex);
            para.Add("PageSize", pageSize);
            para.Add("TotalCount", dbType: DbType.Int32, direction: ParameterDirection.Output);

            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                return connnection.Query<T>("PageList", para, commandType: CommandType.StoredProcedure);
            }
        }