• dapper hepler帮助类


    using Dapper;
    using DapperExtensions;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Threading.Tasks;
    
    namespace DBLibrarys.DBHelper
    {
        /// <summary>
        /// 数据库操作类
        /// </summary>
        public class SqlDapperHelper
        {
            static string connStrRead = ConfigurationManager.ConnectionStrings["Read"].ConnectionString;
            static string connStrWrite = ConfigurationManager.ConnectionStrings["Write"].ConnectionString;
    
            static int commandTimeout = 30;
            public static IDbConnection GetConnection(bool useWriteConn)
            {
                if (useWriteConn)
                    return new SqlConnection(connStrWrite);
                return new SqlConnection(connStrRead);
            }
            public static SqlConnection GetOpenConnection()
            {
                var conn = new SqlConnection(connStrWrite);
                conn.Open();
                return conn;
            }
    
    
            /// <summary>
            ///  执行sql返回一个对象
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <param name="useWriteConn"></param>
            /// <returns></returns>
            public static T ExecuteReaderReturnT<T>(string sql, object param = null, bool useWriteConn = false, IDbTransaction transaction = null)
            {
                if (transaction == null)
                {
                    using (IDbConnection conn = GetConnection(useWriteConn))
                    {
                        conn.Open();
                        return conn.QueryFirstOrDefault<T>(sql, param, commandTimeout: commandTimeout);
                    }
                }
                else
                {
                    var conn = transaction.Connection;
                    return conn.QueryFirstOrDefault<T>(sql, param, commandTimeout: commandTimeout, transaction: transaction);
                }
    
            }
            /// <summary>
            /// 执行sql返回多个对象
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <param name="useWriteConn"></param>
            /// <returns></returns>
            public static List<T> ExecuteReaderReturnList<T>(string sql, object param = null, bool useWriteConn = false, IDbTransaction transaction = null)
            {
                using (IDbConnection conn = GetConnection(useWriteConn))
                {
                    conn.Open();
    
                    return conn.Query<T>(sql, param, commandTimeout: commandTimeout, transaction: transaction).ToList();
                }
            }
            /// <summary>
            /// 执行sql返回一个对象--异步
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <param name="useWriteConn"></param>
            /// <returns></returns>
            public static async Task<T> ExecuteReaderRetTAsync<T>(string sql, object param = null, bool useWriteConn = false)
            {
                using (IDbConnection conn = GetConnection(useWriteConn))
                {
                    conn.Open();
                    return await conn.QueryFirstOrDefaultAsync<T>(sql, param, commandTimeout: commandTimeout).ConfigureAwait(false);
                }
            }
            /// <summary>
            /// 执行sql返回多个对象--异步
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <param name="useWriteConn"></param>
            /// <returns></returns>
            public static async Task<List<T>> ExecuteReaderRetListAsync<T>(string sql, object param = null, bool useWriteConn = false)
            {
                using (IDbConnection conn = GetConnection(useWriteConn))
                {
                    conn.Open();
                    var list = await conn.QueryAsync<T>(sql, param, commandTimeout: commandTimeout).ConfigureAwait(false);
                    return list.ToList();
                }
            }
            /// <summary>
            /// 执行sql,返回影响行数 
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <param name="transaction"></param>
            /// <returns></returns>
            public static int ExecuteSqlInt(string sql, object param = null, IDbTransaction transaction = null)
            {
                if (transaction == null)
                {
                    using (IDbConnection conn = GetConnection(true))
                    {
                        conn.Open();
                        return conn.Execute(sql, param, commandTimeout: commandTimeout, commandType: CommandType.Text);
                    }
                }
                else
                {
                    var conn = transaction.Connection;
                    return conn.Execute(sql, param, transaction: transaction, commandTimeout: commandTimeout, commandType: CommandType.Text);
                }
            }
            /// <summary>
            /// 执行sql,返回影响行数--异步
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <param name="transaction"></param>
            /// <returns></returns>
            public static async Task<int> ExecuteSqlIntAsync(string sql, object param = null, IDbTransaction transaction = null)
            {
                if (transaction == null)
                {
                    using (IDbConnection conn = GetConnection(true))
                    {
                        conn.Open();
                        return await conn.ExecuteAsync(sql, param, commandTimeout: commandTimeout, commandType: CommandType.Text).ConfigureAwait(false);
                    }
                }
                else
                {
                    var conn = transaction.Connection;
                    return await conn.ExecuteAsync(sql, param, transaction: transaction, commandTimeout: commandTimeout, commandType: CommandType.Text).ConfigureAwait(false);
                }
            }
            /// <summary>
            /// 根据id获取实体
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="id"></param>
            /// <param name="transaction"></param>
            /// <param name="useWriteConn"></param>
            /// <returns></returns>
            public static T GetById<T>(int id, IDbTransaction transaction = null, bool useWriteConn = false) where T : class
            {
                if (transaction == null)
                {
                    using (IDbConnection conn = GetConnection(useWriteConn))
                    {
                        conn.Open();
                        return conn.Get<T>(id, commandTimeout: commandTimeout);
                    }
                }
                else
                {
                    var conn = transaction.Connection;
                    return conn.Get<T>(id, transaction: transaction, commandTimeout: commandTimeout);
                }
            }
            /// <summary>
            /// 根据id获取实体--异步
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="id"></param>
            /// <param name="transaction"></param>
            /// <param name="useWriteConn"></param>
            /// <returns></returns>
            public static async Task<T> GetByIdAsync<T>(int id, IDbTransaction transaction = null, bool useWriteConn = false) where T : class
            {
                if (transaction == null)
                {
                    using (IDbConnection conn = GetConnection(useWriteConn))
                    {
                        conn.Open();
                        return await conn.GetAsync<T>(id, commandTimeout: commandTimeout);
                    }
                }
                else
                {
                    var conn = transaction.Connection;
                    return await conn.GetAsync<T>(id, transaction: transaction, commandTimeout: commandTimeout);
                }
            }
    
            /// <summary>
            /// 插入实体
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="item"></param>
            /// <param name="transaction"></param>
            /// <returns></returns>
            public static string ExecuteInsert<T>(T item, IDbTransaction transaction = null) where T : class
            {
                if (transaction == null)
                {
                    using (IDbConnection conn = GetConnection(true))
                    {
                        conn.Open();
                        var res = conn.Insert<T>(item, commandTimeout: commandTimeout);
                        return res;
                    }
                }
                else
                {
                    var conn = transaction.Connection;
                    return conn.Insert(item, transaction: transaction, commandTimeout: commandTimeout);
                }
            }
    
            /// <summary>
            /// 批量插入实体
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="list"></param>
            /// <param name="transaction"></param>
            public static void ExecuteInsertList<T>(IEnumerable<T> list, IDbTransaction transaction = null) where T : class
            {
                if (transaction == null)
                {
                    using (IDbConnection conn = GetConnection(true))
                    {
                        conn.Open();
                        conn.Insert<T>(list, commandTimeout: commandTimeout);
                    }
                }
                else
                {
                    var conn = transaction.Connection;
                    conn.Insert(list, transaction: transaction, commandTimeout: commandTimeout);
                }
            }
    
            /// <summary>
            /// 更新单个实体
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="item"></param>
            /// <param name="transaction"></param>
            /// <returns></returns>
            public static bool ExecuteUpdate<T>(T item, IDbTransaction transaction = null) where T : class
            {
                if (transaction == null)
                {
                    using (IDbConnection conn = GetConnection(true))
                    {
                        conn.Open();
                        return conn.Update(item, commandTimeout: commandTimeout);
                    }
                }
                else
                {
                    var conn = transaction.Connection;
                    return conn.Update(item, transaction: transaction, commandTimeout: commandTimeout);
                }
            }
    
            /// <summary>
            /// 批量更新实体
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="item"></param>
            /// <param name="transaction"></param>
            /// <returns></returns>
            public static bool ExecuteUpdateList<T>(List<T> item, IDbTransaction transaction = null) where T : class
            {
                if (transaction == null)
                {
                    using (IDbConnection conn = GetConnection(true))
                    {
                        conn.Open();
                        return conn.Update(item, commandTimeout: commandTimeout);
                    }
                }
                else
                {
                    var conn = transaction.Connection;
                    return conn.Update(item, transaction: transaction, commandTimeout: commandTimeout);
                }
            }
    
    
            /// <summary>
            /// 分页查询
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="sql">主sql 不带 order by</param>
            /// <param name="sort">排序内容 id desc,add_time asc</param>
            /// <param name="pageIndex">第几页</param>
            /// <param name="pageSize">每页多少条</param>
            /// <param name="useWriteConn">是否主库</param>
            /// <returns></returns>
            public static List<T> ExecutePageList<T>(string sql, string sort, int pageIndex, int pageSize, bool useWriteConn = false, object param = null)
            {
                string pageSql = @"SELECT TOP {0} * FROM (SELECT ROW_NUMBER() OVER (ORDER BY {1}) _row_number_,*  FROM 
                  ({2})temp )temp1 WHERE temp1._row_number_>{3} ORDER BY _row_number_";
                string execSql = string.Format(pageSql, pageSize, sort, sql, pageSize * (pageIndex - 1));
                using (IDbConnection conn = GetConnection(useWriteConn))
                {
                    conn.Open();
    
                    return conn.Query<T>(execSql, param, commandTimeout: commandTimeout).ToList();
                }
            }
         
        }
    }
    

      

  • 相关阅读:
    Lesson_6 作业_1 封装学生类
    Lesson_6_1 上课笔记 张三和法拉利
    Lesson_3 作业_3 输出字母金字塔
    Java简介
    Lesson_7 上课笔记_1 static关键字和导包
    软件设计师考试大纲
    <<设计模式可复用面向对象软件的基础>>组合模式(Composite)
    CS1595:已在多处定义
    <<设计模式可复用面向对象软件的基础>>读书笔记(第一章)引言
    <<设计模式可复用面向对象软件的基础>>设计模式怎样解决设计问题
  • 原文地址:https://www.cnblogs.com/sishahu/p/14155331.html
Copyright © 2020-2023  润新知