ASP.NET Core中使用Dapper

红太狼 2021-11-01 06:32 430阅读 0赞

⒈添加 NuGet 包

1 Install-Package Dapper

⒉封装数据库类型

1 using System;
     2 using System.Collections.Generic;
     3 using System.Linq;
     4 using System.Threading.Tasks;
     5 
     6 namespace DapperDemo.Data
     7 {
     8     /// <summary>
     9     /// 数据库类型
    10     /// </summary>
    11     public enum DbType
    12     {
    13         Access,
    14         SqlServer,
    15         Oracle,
    16         MySql,
    17         SqlLite
    18     }
    19 }

⒊封装数据库连接仓库

1 using Microsoft.Extensions.Configuration;
     2 using MySql.Data.MySqlClient;
     3 using System;
     4 using System.Collections.Concurrent;
     5 using System.Collections.Generic;
     6 using System.Data;
     7 using System.Data.SqlClient;
     8 using System.Linq;
     9 using System.Threading.Tasks;
    10 
    11 namespace DapperDemo.Data
    12 {
    13     public class DbConnectionFactory
    14     {
    15         /// <summary>
    16         /// 数据库连接字符串缓存
    17         /// </summary>
    18         private static ConcurrentDictionary<string, string> connStrDict = new ConcurrentDictionary<string, string>();
    19         private static IConfiguration Configuration { get; }
    20         private static string GetConnString(string dbKey)
    21         {
    22             string connString = string.Empty;
    23             if (connStrDict.Keys.Contains(dbKey))
    24             {
    25                 connString = connStrDict[dbKey];
    26             }
    27             else
    28             {
    29                 connString = Configuration[$"ConnectionStrings:{dbKey}"];
    30                 connStrDict.TryAdd(dbKey, connString);
    31             }
    32             return connString;
    33         }
    34 
    35         public static IDbConnection GetConnection(string dbKey, DbType dbType = DbType.SqlServer)
    36         {
    37             IDbConnection connObj = null;
    38             switch (dbType)
    39             {
    40                 case DbType.SqlServer:
    41                     connObj = new SqlConnection(GetConnString(dbKey));
    42                     break;
    43                 case DbType.MySql:
    44                     connObj = new MySqlConnection(GetConnString(dbKey));
    45                     break;
    46                 case DbType.Access:
    47                     //connObj = new OleDbConnection(GetConnString(dbKey));
    48                     break;
    49                 case DbType.SqlLite:
    50                     break;
    51                 case DbType.Oracle:
    52                     break;
    53             }
    54 
    55             if (connObj.State != ConnectionState.Open)
    56             {
    57                 connObj.Open();
    58             }
    59 
    60             return connObj;
    61         }
    62 
    63         /// <summary>
    64         /// 获取数据连接
    65         /// </summary>
    66         /// <param name="connectionString"></param>
    67         /// <param name="dbType"></param>
    68         /// <returns></returns>
    69         public static IDbConnection GetConnectionByConnString(string connString, DbType dbType = DbType.SqlServer)
    70         {
    71             IDbConnection connObj = null;
    72             switch (dbType)
    73             {
    74                 case DbType.SqlServer:
    75                     connObj = new SqlConnection(connString);
    76                     break;
    77                 case DbType.MySql:
    78                     connObj = new MySqlConnection(connString);
    79                     break;
    80                 case DbType.Access:
    81                     //connObj = new OleDbConnection(connString);
    82                     break;
    83                 case DbType.SqlLite:
    84                     break;
    85                 case DbType.Oracle:
    86                     break;
    87             }
    88 
    89             if (connObj.State != ConnectionState.Open)
    90             {
    91                 connObj.Open();
    92             } 
    93 
    94             return connObj;
    95         }
    96     }
    97 }

⒋封装数据库常见方法

1 using Dapper;
      2 using System;
      3 using System.Collections.Generic;
      4 using System.Data;
      5 using System.Data.SqlClient;
      6 using System.Linq;
      7 using System.Threading.Tasks;
      8 
      9 namespace DapperDemo.Data
     10 {
     11     public class DbRepository
     12     {
     13         private IDbConnection _connection { get; set; }
     14         public string dbConnKey { get; set; }
     15         public DbType dbType { get; set; }
     16 
     17         public DbRepository(string dbConnKey, DbType dbType = DbType.SqlServer)
     18         {
     19             this.dbConnKey = dbConnKey;
     20             this.dbType = dbType;
     21         }
     22 
     23         public DbRepository()
     24         {
     25 
     26         }
     27 
     28         public void Init(string dbConnKey, DbType dbType = DbType.SqlServer)
     29         {
     30             this.dbConnKey = dbConnKey;
     31             this.dbType = dbType;
     32         }
     33 
     34         #region 属性
     35 
     36         /// <summary>
     37         /// 获取数据库连接
     38         /// </summary>
     39         public IDbConnection Connection
     40         {
     41             get
     42             {
     43                 if (_connection == null)
     44                 {
     45                     _connection = DbConnectionFactory.GetConnection(dbConnKey, dbType);
     46                 }
     47                 return _connection;
     48             }
     49         }
     50 
     51         /// <summary>
     52         /// 事务对象
     53         /// </summary>
     54         public IDbTransaction dbTransaction { get; set; }
     55 
     56         #endregion
     57 
     58         #region 事务提交
     59 
     60         /// <summary>
     61         /// 事务开始
     62         /// </summary>
     63         /// <returns></returns>
     64         public DbRepository BeginTrans()
     65         {
     66             dbTransaction = Connection.BeginTransaction();
     67             return this;
     68         }
     69 
     70         /// <summary>
     71         /// 提交当前操作的结果
     72         /// </summary>
     73         public int Commit()
     74         {
     75             try
     76             {
     77                 if (dbTransaction != null)
     78                 {
     79                     dbTransaction.Commit();
     80                     this.Close();
     81                 }
     82                 return 1;
     83             }
     84             catch (Exception ex)
     85             {
     86                 if (ex.InnerException != null && ex.InnerException.InnerException is SqlException)
     87                 {
     88                     SqlException sqlEx = ex.InnerException.InnerException as SqlException;
     89                 }
     90                 throw;
     91             }
     92             finally
     93             {
     94                 if (dbTransaction == null)
     95                 {
     96                     this.Close();
     97                 }
     98             }
     99         }
    100 
    101         /// <summary>
    102         /// 把当前操作回滚成未提交状态
    103         /// </summary>
    104         public void Rollback()
    105         {
    106             this.dbTransaction.Rollback();
    107             this.dbTransaction.Dispose();
    108             this.Close();
    109         }
    110 
    111         /// <summary>
    112         /// 关闭连接 内存回收
    113         /// </summary>
    114         public void Close()
    115         {
    116             IDbConnection dbConnection = dbTransaction.Connection;
    117             if (dbConnection != null && dbConnection.State != ConnectionState.Closed)
    118             {
    119                 dbConnection.Close();
    120             }
    121 
    122         }
    123 
    124         #endregion
    125 
    126         #region 实例方法
    127 
    128         #region 查询
    129 
    130         /// <summary>
    131         /// 查询
    132         /// </summary>
    133         /// <typeparam name="T">返回类型</typeparam>
    134         /// <param name="sql">sql语句</param>
    135         /// <param name="dbConnKey">数据库连接</param>
    136         /// <param name="param">sql查询参数</param>
    137         /// <param name="commandTimeout">超时时间</param>
    138         /// <param name="commandType">命令类型</param>
    139         /// <returns></returns>
    140         public T QueryFirst<T>(string sql, object param = null, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    141         {
    142             if (dbTransaction == null)
    143             {
    144                 using (var dbConn = Connection)
    145                 {
    146                     return dbConn.QueryFirstOrDefault<T>(sql, param, null, commandTimeout, commandType);
    147                 }
    148             }
    149             else
    150             {
    151                 return dbTransaction.Connection.QueryFirstOrDefault<T>(sql, param, dbTransaction, commandTimeout, commandType);
    152             }
    153 
    154         }
    155 
    156         /// <summary>
    157         /// 查询(异步版本)
    158         /// </summary>
    159         /// <typeparam name="T">返回类型</typeparam>
    160         /// <param name="sql">sql语句</param>
    161         /// <param name="dbConnKey">数据库连接</param>
    162         /// <param name="param">sql查询参数</param>
    163         /// <param name="commandTimeout">超时时间</param>
    164         /// <param name="commandType">命令类型</param>
    165         /// <returns></returns>
    166         public async Task<T> QueryFirstAsync<T>(string sql, object param = null, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    167         {
    168             if (dbTransaction == null)
    169             {
    170                 using (var dbConn = Connection)
    171                 {
    172                     return await dbConn.QueryFirstOrDefaultAsync<T>(sql, param, null, commandTimeout, commandType);
    173                 }
    174             }
    175             else
    176             {
    177                 return await dbTransaction.Connection.QueryFirstOrDefaultAsync<T>(sql, param, dbTransaction, commandTimeout, commandType);
    178             }
    179 
    180         }
    181 
    182 
    183         /// <summary>
    184         /// 查询
    185         /// </summary>
    186         /// <typeparam name="T">返回类型</typeparam>
    187         /// <param name="sql">sql语句</param>
    188         /// <param name="dbConnKey">数据库连接</param>
    189         /// <param name="param">sql查询参数</param>
    190         /// <param name="buffered">是否缓冲</param>
    191         /// <param name="commandTimeout">超时时间</param>
    192         /// <param name="commandType">命令类型</param>
    193         /// <returns></returns>
    194         public IEnumerable<T> Query<T>(string sql, object param = null, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    195         {
    196             if (dbTransaction == null)
    197             {
    198                 using (var dbConn = Connection)
    199                 {
    200                     return dbConn.Query<T>(sql, param, null, buffered, commandTimeout, commandType);
    201                 }
    202             }
    203             else
    204             {
    205                 return dbTransaction.Connection.Query<T>(sql, param, dbTransaction, buffered, commandTimeout, commandType);
    206             }
    207 
    208         }
    209 
    210 
    211         /// <summary>
    212         /// 查询(异步版本)
    213         /// </summary>
    214         /// <typeparam name="T">返回类型</typeparam>
    215         /// <param name="sql">sql语句</param>
    216         /// <param name="dbConnKey">数据库连接</param>
    217         /// <param name="param">sql查询参数</param>
    218         /// <param name="buffered">是否缓冲</param>
    219         /// <param name="commandTimeout">超时时间</param>
    220         /// <param name="commandType">命令类型</param>
    221         /// <returns></returns>
    222         public async Task<IEnumerable<T>> QueryAsync<T>(string sql, object param = null, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    223         {
    224             if (dbTransaction == null)
    225             {
    226                 using (var dbConn = Connection)
    227                 {
    228                     return await dbConn.QueryAsync<T>(sql, param, null, commandTimeout, commandType);
    229                 }
    230             }
    231             else
    232             {
    233                 return await dbTransaction.Connection.QueryAsync<T>(sql, param, dbTransaction, commandTimeout, commandType);
    234             }
    235 
    236         }
    237 
    238 
    239 
    240         /// <summary>
    241         /// 查询返回 IDataReader
    242         /// </summary>
    243         /// <param name="sql">sql语句</param>
    244         /// <param name="dbConnKey">数据库连接</param>
    245         /// <param name="param">sql查询参数</param>
    246         /// <param name="commandTimeout">超时时间</param>
    247         /// <param name="commandType">命令类型</param>
    248         /// <returns></returns>
    249         public IDataReader ExecuteReader(string sql, object param = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    250         {
    251             if (dbTransaction == null)
    252             {
    253                 using (var dbConn = Connection)
    254                 {
    255                     return dbConn.ExecuteReader(sql, param, null, commandTimeout, commandType);
    256                 }
    257             }
    258             else
    259             {
    260                 return dbTransaction.Connection.ExecuteReader(sql, param, dbTransaction, commandTimeout, commandType);
    261             }
    262         }
    263 
    264         /// <summary>
    265         /// 查询单个返回值 
    266         /// </summary>
    267         /// <typeparam name="T">返回类型</typeparam>
    268         /// <param name="sql">sql语句</param>
    269         /// <param name="dbConnKey">数据库连接</param>
    270         /// <param name="param">sql查询参数</param>
    271         /// <param name="commandTimeout">超时时间</param>
    272         /// <param name="commandType">命令类型</param>
    273         /// <returns></returns>
    274         public T ExecuteScalar<T>(string sql, object param = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    275         {
    276             if (dbTransaction == null)
    277             {
    278                 using (var dbConn = Connection)
    279                 {
    280                     return dbConn.ExecuteScalar<T>(sql, param, null, commandTimeout, commandType);
    281                 }
    282             }
    283             else
    284             {
    285                 return dbTransaction.Connection.ExecuteScalar<T>(sql, param, dbTransaction, commandTimeout, commandType);
    286             }
    287 
    288         }
    289         #endregion
    290 
    291         /// <summary>
    292         /// 执行增删改sql
    293         /// </summary>
    294         /// <param name="sql">sql</param>
    295         /// <param name="dbkey">数据库连接</param>
    296         /// <param name="param">sql查询参数</param>
    297         /// <param name="commandTimeout">超时时间</param>
    298         /// <param name="commandType">命令类型</param>
    299         /// <returns></returns>
    300         public int ExecuteSql(string sql, object param = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    301         {
    302             if (dbTransaction == null)
    303             {
    304                 using (var dbConn = Connection)
    305                 {
    306                     return dbConn.Execute(sql, param, null, commandTimeout, commandType);
    307                 }
    308             }
    309             else
    310             {
    311                 return dbTransaction.Connection.Execute(sql, param, dbTransaction);
    312             }
    313         }
    314 
    315         /// <summary>
    316         /// 执行增删改sql(异步版本)
    317         /// </summary>
    318         /// <param name="sql">sql</param>
    319         /// <param name="dbkey">数据库连接</param>
    320         /// <param name="param">sql查询参数</param>
    321         /// <param name="commandTimeout">超时时间</param>
    322         /// <param name="commandType">命令类型</param>
    323         /// <returns></returns>
    324         public async Task<int> ExecuteSqlAsync(string sql, object param = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    325         {
    326             if (dbTransaction == null)
    327             {
    328                 using (var dbConn = Connection)
    329                 {
    330                     return await dbConn.ExecuteAsync(sql, param, null, commandTimeout, commandType);
    331                 }
    332             }
    333             else
    334             {
    335                 await dbTransaction.Connection.ExecuteAsync(sql, param, dbTransaction);
    336                 return 0;
    337             }
    338         }
    339 
    340 
    341         #endregion
    342 
    343         #region 静态方法
    344 
    345         #region 查询
    346         /// <summary>
    347         /// 查询
    348         /// </summary>
    349         /// <typeparam name="T">返回类型</typeparam>
    350         /// <param name="sql">sql语句</param>
    351         /// <param name="dbConnKey">数据库连接</param>
    352         /// <param name="param">sql查询参数</param>
    353         /// <param name="commandTimeout">超时时间</param>
    354         /// <param name="commandType">命令类型</param>
    355         /// <returns></returns>
    356         public static T QueryFirst<T>(string sql, string dbConnKey, object param = null, DbType dbType = DbType.SqlServer, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    357         {
    358             using (var dbConn = DbConnectionFactory.GetConnection(dbConnKey, dbType))
    359             {
    360                 return dbConn.QueryFirstOrDefault<T>(sql, param, null, commandTimeout, commandType);
    361             }
    362         }
    363 
    364         /// <summary>
    365         /// 查询(异步版本)
    366         /// </summary>
    367         /// <typeparam name="T">返回类型</typeparam>
    368         /// <param name="sql">sql语句</param>
    369         /// <param name="dbConnKey">数据库连接</param>
    370         /// <param name="param">sql查询参数</param>
    371         /// <param name="commandTimeout">超时时间</param>
    372         /// <param name="commandType">命令类型</param>
    373         /// <returns></returns>
    374         public static async Task<T> QueryFirstAsync<T>(string sql, string dbConnKey, object param = null, DbType dbType = DbType.SqlServer, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    375         {
    376             using (var dbConn = DbConnectionFactory.GetConnection(dbConnKey, dbType))
    377             {
    378                 return await dbConn.QueryFirstOrDefaultAsync<T>(sql, param, null, commandTimeout, commandType);
    379             }
    380         }
    381 
    382 
    383         /// <summary>
    384         /// 查询
    385         /// </summary>
    386         /// <typeparam name="T">返回类型</typeparam>
    387         /// <param name="sql">sql语句</param>
    388         /// <param name="dbConnKey">数据库连接</param>
    389         /// <param name="param">sql查询参数</param>
    390         /// <param name="buffered">是否缓冲</param>
    391         /// <param name="commandTimeout">超时时间</param>
    392         /// <param name="commandType">命令类型</param>
    393         /// <returns></returns>
    394         public static IEnumerable<T> Query<T>(string sql, string dbConnKey, object param = null, DbType dbType = DbType.SqlServer, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    395         {
    396             using (var dbConn = DbConnectionFactory.GetConnection(dbConnKey, dbType))
    397             {
    398                 return dbConn.Query<T>(sql, param, null, buffered, commandTimeout, commandType);
    399             }
    400         }
    401 
    402 
    403         /// <summary>
    404         /// 查询(异步版本)
    405         /// </summary>
    406         /// <typeparam name="T">返回类型</typeparam>
    407         /// <param name="sql">sql语句</param>
    408         /// <param name="dbConnKey">数据库连接</param>
    409         /// <param name="param">sql查询参数</param>
    410         /// <param name="buffered">是否缓冲</param>
    411         /// <param name="commandTimeout">超时时间</param>
    412         /// <param name="commandType">命令类型</param>
    413         /// <returns></returns>
    414         public static async Task<IEnumerable<T>> QueryAsync<T>(string sql, string dbConnKey, object param = null, DbType dbType = DbType.SqlServer, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    415         {
    416             using (var dbConn = DbConnectionFactory.GetConnection(dbConnKey, dbType))
    417             {
    418                 return await dbConn.QueryAsync<T>(sql, param, null, commandTimeout, commandType);
    419             }
    420         }
    421 
    422 
    423 
    424         /// <summary>
    425         /// 查询返回 IDataReader
    426         /// </summary>
    427         /// <param name="sql">sql语句</param>
    428         /// <param name="dbConnKey">数据库连接</param>
    429         /// <param name="param">sql查询参数</param>
    430         /// <param name="commandTimeout">超时时间</param>
    431         /// <param name="commandType">命令类型</param>
    432         /// <returns></returns>
    433         public static IDataReader ExecuteReader(string sql, string dbConnKey, object param = null, DbType dbType = DbType.SqlServer, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    434         {
    435             using (var dbConn = DbConnectionFactory.GetConnection(dbConnKey, dbType))
    436             {
    437                 return dbConn.ExecuteReader(sql, param, null, commandTimeout, commandType);
    438             }
    439         }
    440 
    441         /// <summary>
    442         /// 查询单个返回值 
    443         /// </summary>
    444         /// <typeparam name="T">返回类型</typeparam>
    445         /// <param name="sql">sql语句</param>
    446         /// <param name="dbConnKey">数据库连接</param>
    447         /// <param name="param">sql查询参数</param>
    448         /// <param name="commandTimeout">超时时间</param>
    449         /// <param name="commandType">命令类型</param>
    450         /// <returns></returns>
    451         public static T ExecuteScalar<T>(string sql, string dbConnKey, object param = null, DbType dbType = DbType.SqlServer, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    452         {
    453             using (var dbConn = DbConnectionFactory.GetConnection(dbConnKey, dbType))
    454             {
    455                 return dbConn.ExecuteScalar<T>(sql, param, null, commandTimeout, commandType);
    456             }
    457         }
    458 
    459         #endregion
    460 
    461         #region 增删改
    462 
    463         /// <summary>
    464         /// 执行增删改sql
    465         /// </summary>
    466         /// <param name="sql">sql</param>
    467         /// <param name="dbkey">数据库连接</param>
    468         /// <param name="param">sql查询参数</param>
    469         /// <param name="commandTimeout">超时时间</param>
    470         /// <param name="commandType">命令类型</param>
    471         /// <returns></returns>
    472         public static int Execute(string sql, string dbkey, object param = null, DbType dbType = DbType.SqlServer, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    473         {
    474             using (var dbConn = DbConnectionFactory.GetConnection(dbkey, dbType))
    475             {
    476                 return dbConn.Execute(sql, param, null, commandTimeout, commandType);
    477             }
    478         }
    479 
    480         /// <summary>
    481         /// 执行增删改sql(异步版本)
    482         /// </summary>
    483         /// <param name="sql">sql</param>
    484         /// <param name="dbkey">数据库连接</param>
    485         /// <param name="param">sql查询参数</param>
    486         /// <param name="commandTimeout">超时时间</param>
    487         /// <param name="commandType">命令类型</param>
    488         /// <returns></returns>
    489         public static async Task<int> ExecuteAsync(string sql, string dbkey, object param = null, DbType dbType = DbType.SqlServer, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    490         {
    491             using (var dbConn = DbConnectionFactory.GetConnection(dbkey, dbType))
    492             {
    493                 return await dbConn.ExecuteAsync(sql, param, null, commandTimeout, commandType);
    494             }
    495         }
    496 
    497 
    498         /// <summary>
    499         /// 执行 DynamicQuery.GetInsertQuery* 方法生成的Sql 返回标识值
    500         /// </summary>
    501         /// <typeparam name="T"></typeparam>
    502         /// <param name="sql"></param>
    503         /// <param name="dbKey"></param>
    504         /// <param name="param"></param>
    505         /// <param name="commandTimeout"></param>
    506         /// <param name="commandType"></param>
    507         /// <returns></returns>
    508         public static T ExecuteInsertSql<T>(string sql, string dbKey, object param = null, DbType dbType = DbType.SqlServer, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
    509         {
    510             using (var dbConn = DbConnectionFactory.GetConnection(dbKey, dbType))
    511             {
    512                 return dbConn.QueryFirstOrDefault<T>(sql, param, null, commandTimeout, commandType);
    513             }
    514         }
    515 
    516         #endregion
    517 
    518         #endregion
    519 
    520     }
    521 }

转载于:https://www.cnblogs.com/fanqisoft/p/10963168.html