在Dapper中配置数据库连接为单例

在Dapper中不想每次都创建一个新数据库连接可以通过单例模式使之配置为单例

public class ConnectionOptions
    {
        private static readonly IDbConnection _dbConnection = new SqlConnection();
        public static IDbConnection DbConnection { 
            get 
            {
                if (string.IsNullOrEmpty(_dbConnection.ConnectionString))
                {
                    _dbConnection.ConnectionString = ConnectionString;
                }
                return _dbConnection;
            }
        }//配置为单例

        private static string _connectionString;
        public static string ConnectionString{ get { return _connectionString; } set => _connectionString = value; }
    }

使用前先在startup中先配置字符串（可以写在配置文件中通过Configuration.GetConnectionString("Default")获取也行）

            SqlContext.ConnectionOptions.ConnectionString = "Data Source=(local);Initial Catalog=Dapper;Integrated Security=True";

然后我们就可以直接在Dapperhelp中直接使用了 这样就不必每次都会创建新实例

public class Dapperhelper
    {
        //static IDbConnection _dbConnection = new SqlConnection();
        //public String ConnectionString => ConnectionOptions.ConnectionString; //相当于get
        //public Dapperhelper() {
        //    if (string.IsNullOrEmpty(_dbConnection.ConnectionString))
        //    {
        //         _dbConnection.ConnectionString = ConnectionString;
        //    }     
        //}
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="transaction">事务</param>
        /// <param name="param">参数</param>
        /// <param name="buffered">缓存</param>
        /// <param name="CommandTimeout">超时时间</param>
        /// <param name="commandType">command类型</param>
        /// <returns></returns>
        public T QueryFirst<T>(string sql,object param = null, IDbTransaction transaction=null,int? CommandTimeout = null, CommandType? commandType = null)
        {
            ConnectionOptions.DbConnection.Open();
            using (transaction = ConnectionOptions.DbConnection.BeginTransaction())
            {
                var user= ConnectionOptions.DbConnection.QueryFirstOrDefault<T>(sql, param, transaction, CommandTimeout, commandType);
                transaction.Commit();
                ConnectionOptions.DbConnection.Close();
                return user;
            }         
        }//单个查询
        public IEnumerable<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? CommandTimeout = null, CommandType? commandType = null)
        {
            return ConnectionOptions.DbConnection.Query<T>(sql, param, transaction,buffered, CommandTimeout, commandType);
        }//多个查询
        public int Execute(string sql,object param = null, IDbTransaction transaction = null,  int? CommandTimeout = null, CommandType? commandType = null)
        {
            return ConnectionOptions.DbConnection.Execute(sql, param, transaction, CommandTimeout, commandType);
        }//增删改
    }

 使用一

   public User GetUserByLogin(string UserName, string PassWord)
        {
            string sql = "Select * From Users Where UserName=@username AND PassWord=@password";
            var user = _db.QueryFirst<User>(sql,new{ UserName, PassWord });
            if (user == null)
            {
                return default;
            }
            else
            {
                return user;
            }
        }

若想不使用sql语句可使用Dapper的扩展 需要下载 Dapper.Contrib 扩展包 都属于扩展方法SqlMapperExtensions

 public class DapperExtHelper<T> where T:BaseEntity
    {
        public T Get(int id)
        {
            return ConnectionOptions.DbConnection.Get<T>(id);
        }
        public IEnumerable<T> GetAll()
        {
            return ConnectionOptions.DbConnection.GetAll<T>();
        }
        public long Insert(T t)
        {
            return ConnectionOptions.DbConnection.Insert<T>(t);
        }
        public bool Update(T t)
        {
            return ConnectionOptions.DbConnection.Update<T>(t);
        }
        public bool Delete(T t)
        {
            return ConnectionOptions.DbConnection.Delete<T>(t);
        }
        public bool DeleteAll()
        {
            return ConnectionOptions.DbConnection.DeleteAll<T>();
        }
    }