Dapper 基本方法测试

Dapper 增删改查，单个，分页，存储过程等操作。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Dapper;
using System.Data;
using System.Data.SqlClient;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Collections;

namespace APP.DapperTest
{

    public class MyConnection
    {
        public static IDbConnection GetDefaultDB()
        {
            string conn = "";
            try
            {
                System.Web.Configuration.WebConfigurationManager.ConnectionStrings["Default_Conn"].ToString();
            }
            catch (Exception)
            {
                conn = "";
            }
            string conn2 = System.Configuration.ConfigurationManager.ConnectionStrings["Default_Conn"].ConnectionString;
            conn = string.IsNullOrEmpty(conn) ? conn2 : conn;
            if (string.IsNullOrEmpty(conn))
                throw new Exception("读取默认数据库连接字符串出错.");
            return new MySqlConnection(conn);
        }
    }

    public class DapperHelper
    {
        static IDbConnection connection = new SqlConnection("Data Source=.;Initial Catalog=testdb;Integrated Security=True;MultipleActiveResultSets=True");
           //mysql
           //new MySqlConnection("server=127.0.0.1;User Id=root;password=123456;Database=test;Charset=utf8;");

        public int Add()
        {
            var result = connection.Execute("Insert into Users(username,email,address) values (@UserName, @Email, @Address)",
                                  new { UserName = "jack", Email = "380234234@qq.com", Address = "上海" });
            
            return result;
        }


        /// <summary>
        /// 批量添加
        /// </summary>
        /// <returns></returns>
        public int AddBulk()
        {
            var usersList = Enumerable.Range(0, 10).Select(i => new 
            {
                Email = i + "qq.com",
                Address = "安徽",
                UserName = i + "jack"
            });

            var result = connection.Execute("Insert into Users(username,email,address) values (@UserName, @Email, @Address)", usersList);

            return result;
        }

        public T Query<T>()
        {
            var result = connection.QueryFirst<T>("select * from Users WITH (NOLOCK) where username=@username", new { UserName = "jack" });
            return result;
        }

        public IEnumerable Querys()
        {
            var result = connection.Query("select * from Users where username=@username", new { UserName = "jack" });
            return result;
        }

        public int Delete()
        {
            var result = connection.Execute("delete from Users where userid=@userid", new Users{  UserID = 3 });
            return result;
        }

        /// <summary>
        /// in查询
        /// </summary>
        public void QueryIn()
        {
            var sql = "select * from Users  where Email in @emails";
            var info = connection.Query<Users>(sql, new { emails = new string[2] { "5qq.com", "7qq.com" } }, commandType: CommandType.StoredProcedure);
        }


        /// <summary>
        /// 多条件查询分页
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ComPagers OilOrdersList(Users model)
        {
            ComPagers re = new ComPagers();
            re.PageIndex = 1; re.PageSize = 20;

            string strWhere = string.Empty;
            IEnumerable<Users> list = new List<Users>();

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@CustomerCode", model.UserName, DbType.String);
            parameters.Add("@pageIndex", model.PageIndex, DbType.Int32);
            parameters.Add("@pageSize", model.PageSize, DbType.Int32);

            //if (!string.IsNullOrEmpty(model.CardNo))
            //{
            //    parameters.Add("@CardNo", model.CardNo);
            //    strWhere += " AND CHARINDEX(@CardNo,o.BusinessParam) > 0 ";
            //}

            //if (model.Status >= 0)
            //{
            //    parameters.Add("@Status", model.Status);
            //    strWhere += " AND o.OrderState=@Status ";
            //}

            //if (model.StartTime <= model.EndTime)
            //{
            //    parameters.Add("@sTime", model.StartTime.ToString("yyyy-MM-dd HH:mm"));
            //    parameters.Add("@eTime", model.EndTime.ToString("yyyy-MM-dd HH:mm"));
            //    strWhere += " AND (o.AddDate BETWEEN  @sTime AND @eTime)";
            //}
            string strSql = @" DECLARE @s int,@e int
                                 SET @s = (@pageIndex-1)*@pageSize+1; SET @e = @pageIndex*@pageSize;
                                  SELECT * FROM
                                  (
                                    SELECT ROW_NUMBER() over(order by AddDate desc) as rowID, 
                                    *
                                    FROM dbo.tbO o WITH(NOLOCK,INDEX=IX_OrderCustomerCode)  
                                    WHERE 1=1 AND CustomerCode = @CustomerCode  " + strWhere + @"
                                   )T
                                  where rowID BETWEEN @s AND @e ;
                                  SELECT COUNT(1) AS Total FROM dbo.tbO o WITH(NOLOCK)   WHERE 1=1 AND CustomerCode = @CustomerCode  " + strWhere;

            try
            {
                using (var con = connection)
                {
                    var data = con.QueryMultiple(strSql, parameters);
                    if (!data.IsConsumed)
                    {
                        list = data.Read<Users>();
                        re.Total = data.ReadSingleOrDefault<int>();
                        re.DataList = list;
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return re;
        }

        /// <summary>
        /// 存储过程
        /// </summary>
        /// <param name="opCode"></param>
        /// <param name="cardNo"></param>
        /// <returns></returns>
        public Users GetFuelCardInfo(string opCode, string cardNo)
        {
            Users model = new Users();
            var parameters = new DynamicParameters();
            parameters.Add("@vOPCode", opCode);
            parameters.Add("@vCardNo", cardNo);
            parameters.Add("@vRetFlag", null, DbType.Int32, direction: ParameterDirection.Output);
            parameters.Add("@vRetMsg", "", DbType.String, direction: ParameterDirection.Output);
            parameters.Add("@vUserName", "", DbType.String, direction: ParameterDirection.Output);
            try
            {
                using (var dbCon = connection)
                {
                    dbCon.Execute("sp_QueryCardInfo", parameters, commandType: CommandType.StoredProcedure);
                   // model.RetFlag = parameters.Get<int>("@vRetFlag");  //输出参数
                   // model.RetMsg = parameters.Get<string>("@vRetMsg"); //输出参数
                    model.UserName = parameters.Get<string>("@vUserName");  //输出参数
                } 
            }
            catch (Exception ex)
            {
                
            }
            return model;
        }

        public class Users
        {
            public int ID { get; set; }
            public int UserID { get; set; }
            public string UserName { get; set; }
            public string  Email { get; set; }
            public string Address { get; set; }

            public int PageIndex { get; set; }
            public int PageSize{ get; set; }
        }


        /// <summary>
        /// 常用分页数据格式
        /// </summary>
        public class ComPagers
        {
            public int Total { get; set; }
            public int PageIndex { get; set; }
            public int PageSize { get; set; }

            public object DataList { get; set; }
        }

        /// <summary>
        /// 常用分页数据格式
        /// </summary>
        public class ComPagers<T>
        {
            public int Total { get; set; }
            public int PageIndex { get; set; }
            public int PageSize { get; set; }

            /// <summary>
            /// 数据列表
            /// </summary>
            public IEnumerable<T> DataList { get; set; }
        }
    }
}