特色栏目： python 批处理 net编程 Javascript Php Asp Css Html5 Android seo centos

Ado封装支持多种数据库实现

来源：互联网收集：自由互联发布时间：2022-07-12

一、定义接口 1 /// summary 2 /// Ado接口类 3 /// /summary 4 public interface IAdo 5 { 6 7 /// summary 8 /// 数据库提供商工厂 9 /// /summary 10 DbProviderFactory DbProviderFactory { get ; } 11 12 /// summary 13 /// 数据库选

一、定义接口

 1 /// <summary>
 2     /// Ado接口类
 3     /// </summary>
 4     public interface IAdo
 5     {
 6 
 7         /// <summary>
 8         /// 数据库提供商工厂
 9         /// </summary>
10         DbProviderFactory DbProviderFactory { get; }
11 
12         /// <summary>
13         /// 数据库选项
14         /// </summary>
15         DbOptions DbOptions { get; }
16 
17         /// <summary>
18         /// 开启事务异步
19         /// </summary>
20         Task BeginTranAsync();
21 
22         /// <summary>
23         /// 提交事务异步
24         /// </summary>
25         Task CommitTranAsync();
26 
27         /// <summary>
28         /// 准备命令
29         /// </summary>
30         /// <param name="command">命令</param>
31         /// <param name="connection">连接</param>
32         /// <param name="transaction">事务</param>
33         /// <param name="commandType">命令类型</param>
34         /// <param name="commandText">命令文本</param>
35         /// <param name="dbParameters">数据库参数</param>
36         /// <returns></returns>
37         Task<bool> PrepareCommand(DbCommand command, DbConnection connection, DbTransaction transaction, CommandType commandType, string commandText, DbParameter[] dbParameters);
38 
39         /// <summary>
40         /// 执行非查询异步
41         /// </summary>
42         /// <param name="commandType">命令类型</param>
43         /// <param name="commandText">命令文本</param>
44         /// <param name="dbParameters">数据库参数</param>
45         /// <returns></returns>
46         Task<int> ExecuteNonQueryAsync(CommandType commandType, string commandText, DbParameter[] dbParameters = null);
47 
48         /// <summary>
49         /// 执行标量异步
50         /// </summary>
51         /// <typeparam name="T"></typeparam>
52         /// <param name="commandType">命令类型</param>
53         /// <param name="commandText">命令文本</param>
54         /// <param name="dbParameters">数据库参数</param>
55         /// <returns></returns>
56         Task<T> ExecuteScalarAsync<T>(CommandType commandType, string commandText, DbParameter[] dbParameters = null);
57 
58         /// <summary>
59         /// 执行阅读器异步
60         /// </summary>
61         /// <param name="commandType">命令类型</param>
62         /// <param name="commandText">命令文本</param>
63         /// <param name="dbParameters">数据库参数</param>
64         /// <returns></returns>
65         Task<DbDataReader> ExecuteReaderAsync(CommandType commandType, string commandText, DbParameter[] dbParameters = null);
66 
67         /// <summary>
68         /// 执行数据集
69         /// </summary>
70         /// <param name="commandType">命令类型</param>
71         /// <param name="commandText">命令文本</param>
72         /// <param name="dbParameters">数据库参数</param>
73         /// <returns></returns>
74         Task<DataSet> ExecuteDataSet(CommandType commandType, string commandText, DbParameter[] dbParameters = null);
75 
76         /// <summary>
77         /// 执行数据表格
78         /// </summary>
79         /// <param name="commandType">命令类型</param>
80         /// <param name="commandText">命令文本</param>
81         /// <param name="dbParameters">数据库参数</param>
82         /// <returns></returns>
83         Task<DataTable> ExecuteDataTable(CommandType commandType, string commandText, DbParameter[] dbParameters = null);
84 
85         /// <summary>
86         /// Sql参数构建
87         /// </summary>
88         /// <typeparam name="T"></typeparam>
89         /// <param name="entity">实体</param>
90         DbParameter[] SqlParametersBuild<T>(T entity) where T : class;
91 
92         /// <summary>
93         /// Sql参数构建
94         /// </summary>
95         /// <param name="keyValues">键值</param>
96         /// <returns></returns>
97         DbParameter[] SqlParametersBuild(Dictionary<string, object> keyValues);
98     }

二、实现接口

  1 /// <summary>
  2     /// Ado实现类
  3     /// </summary>
  4     public class AdoProvider : IAdo
  5     {
  6 
  7         /// <summary>
  8         /// 数据库提供商工厂
  9         /// </summary>
 10         public DbProviderFactory DbProviderFactory { get; }
 11 
 12         /// <summary>
 13         /// 数据库选项
 14         /// </summary>
 15         public DbOptions DbOptions { get; }
 16 
 17         /// <summary>
 18         /// 连接对象
 19         /// </summary>
 20         private readonly DbConnection conn;
 21 
 22         /// <summary>
 23         /// 执行对象
 24         /// </summary>
 25         private readonly DbCommand cmd;
 26 
 27         /// <summary>
 28         /// 构造方法
 29         /// </summary>
 30         /// <param name="dbOptions">数据库选项</param>
 31         public AdoProvider(IOptionsSnapshot<DbOptions> dbOptions) : this(dbOptions.Value)//巧妙的实现方式 不仅支持了注入还实现了代码复用
 32         {
 33         }
 34 
 35         /// <summary>
 36         /// 构造方法
 37         /// </summary>
 38         /// <param name="dbOptions">数据选项</param>
 39         public AdoProvider(DbOptions dbOptions)
 40         {
 41             DbOptions = dbOptions;
 42             DbProviderFactories.RegisterFactory(DbOptions.ProviderName, DbOptions.FactoryName);
 43             DbProviderFactory = DbProviderFactories.GetFactory(DbOptions.ProviderName);
 44             conn = DbProviderFactory.CreateConnection();
 45             cmd = conn.CreateCommand();
 46             conn.ConnectionString = DbOptions.ConnectionStrings;
 47             if (DbOptions.DbType == Models.DatabaseType.Oracle)
 48             {
 49                 cmd.GetType().GetProperty("BindByName").SetValue(cmd, true);//Oracle 数据库需要设置这个属性 否则无法绑定参数
 50             }
 51         }
 52 
 53         /// <summary>
 54         /// 开启事务异步
 55         /// </summary>
 56         public async Task BeginTranAsync()
 57         {
 58             await conn.OpenAsync();
 59             cmd.Transaction = await conn.BeginTransactionAsync();
 60         }
 61 
 62         /// <summary>
 63         /// 提交事务异步
 64         /// </summary>
 65         public async Task CommitTranAsync()
 66         {
 67             try
 68             {
 69                 await cmd.Transaction.CommitAsync();
 70             }
 71             catch
 72             {
 73                 if (cmd.Transaction != null)
 74                 {
 75                     await cmd.Transaction.RollbackAsync();
 76                 }
 77                 throw;
 78             }
 79             finally
 80             {
 81                 cmd.Transaction = null;
 82                 await conn.CloseAsync();
 83             }
 84         }
 85 
 86         /// <summary>
 87         /// 准备命令
 88         /// </summary>
 89         /// <param name="command">命令</param>
 90         /// <param name="connection">连接</param>
 91         /// <param name="transaction">事务</param>
 92         /// <param name="commandType">命令类型</param>
 93         /// <param name="commandText">命令文本</param>
 94         /// <param name="dbParameters">数据库参数</param>
 95         /// <returns></returns>
 96         public async Task<bool> PrepareCommand(DbCommand command, DbConnection connection, DbTransaction transaction, CommandType commandType, string commandText, DbParameter[] dbParameters)
 97         {
 98             var mustCloseConnection = false;
 99             if (connection.State != ConnectionState.Open)
100             {
101                 await conn.OpenAsync();
102                 mustCloseConnection = true;
103             }
104             if (transaction != null)
105             {
106                 command.Transaction = transaction;
107                 mustCloseConnection = false;
108             }
109             command.CommandType = commandType;
110             command.CommandText = commandText;
111             if (dbParameters != null && dbParameters.Length > 0)
112             {
113                 command.Parameters.AddRange(dbParameters);
114             }
115             return mustCloseConnection;
116         }
117 
118         /// <summary>
119         /// 执行非查询异步
120         /// </summary>
121         /// <param name="commandType">命令类型</param>
122         /// <param name="commandText">命令文本</param>
123         /// <param name="dbParameters">数据库参数</param>
124         /// <returns></returns>
125         public async Task<int> ExecuteNonQueryAsync(CommandType commandType, string commandText, DbParameter[] dbParameters = null)
126         {
127             var mustCloseConnection = await PrepareCommand(cmd, conn, cmd.Transaction, commandType, commandText, dbParameters?.ToArray());
128             try
129             {
130                 return await cmd.ExecuteNonQueryAsync();
131             }
132             catch
133             {
134                 if (cmd.Transaction != null)
135                 {
136                     await cmd.Transaction.RollbackAsync();
137                     mustCloseConnection = true;
138                 }
139                 throw;
140             }
141             finally
142             {
143                 cmd.Parameters.Clear();
144                 if (mustCloseConnection)
145                 {
146                     await conn.CloseAsync();
147                 }
148             }
149         }
150 
151         /// <summary>
152         /// 执行标量异步
153         /// </summary>
154         /// <typeparam name="T"></typeparam>
155         /// <param name="commandType">命令类型</param>
156         /// <param name="commandText">命令文本</param>
157         /// <param name="dbParameters">数据库参数</param>
158         /// <returns></returns>
159         public async Task<T> ExecuteScalarAsync<T>(CommandType commandType, string commandText, DbParameter[] dbParameters = null)
160         {
161             var mustCloseConnection = await PrepareCommand(cmd, conn, cmd.Transaction, commandType, commandText, dbParameters?.ToArray());
162             try
163             {
164                 return (await cmd.ExecuteScalarAsync()).ChanageType<T>();
165             }
166             catch
167             {
168                 if (cmd.Transaction != null)
169                 {
170                     await cmd.Transaction.RollbackAsync();
171                     mustCloseConnection = true;
172                 }
173                 throw;
174             }
175             finally
176             {
177                 cmd.Parameters.Clear();
178                 if (mustCloseConnection)
179                 {
180                     await conn.CloseAsync();
181                 }
182             }
183         }
184 
185         /// <summary>
186         /// 执行阅读器异步
187         /// </summary>
188         /// <param name="commandType">命令类型</param>
189         /// <param name="commandText">命令文本</param>
190         /// <param name="dbParameters">数据库参数</param>
191         /// <returns></returns>
192         public async Task<DbDataReader> ExecuteReaderAsync(CommandType commandType, string commandText, DbParameter[] dbParameters = null)
193         {
194             var mustCloseConnection = await PrepareCommand(cmd, conn, cmd.Transaction, commandType, commandText, dbParameters);
195             try
196             {
197                 if (mustCloseConnection)
198                 {
199                     return await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
200                 }
201                 else
202                 {
203                     return await cmd.ExecuteReaderAsync();
204                 }
205             }
206             catch
207             {
208                 if (cmd.Transaction != null)
209                 {
210                     await cmd.Transaction.RollbackAsync();
211                 }
212                 await conn.CloseAsync();
213                 throw;
214             }
215             finally
216             {
217                 cmd.Parameters.Clear();
218             }
219         }
220 
221         /// <summary>
222         /// 执行数据集
223         /// </summary>
224         /// <param name="commandType">命令类型</param>
225         /// <param name="commandText">命令文本</param>
226         /// <param name="dbParameters">数据库参数</param>
227         /// <returns></returns>
228         public async Task<DataSet> ExecuteDataSet(CommandType commandType, string commandText, DbParameter[] dbParameters = null)
229         {
230             var ds = new DataSet();
231             using (var adapter = DbProviderFactory.CreateDataAdapter())
232             {
233                 var mustCloseConnection = await PrepareCommand(cmd, conn, cmd.Transaction, commandType, commandText, dbParameters.ToArray());
234                 try
235                 {
236                     adapter.SelectCommand = cmd;
237                     adapter.Fill(ds);
238                 }
239                 catch
240                 {
241                     if (cmd.Transaction != null)
242                     {
243                         await cmd.Transaction.RollbackAsync();
244                         mustCloseConnection = true;
245                     }
246                     throw;
247                 }
248                 finally
249                 {
250                     cmd.Parameters.Clear();
251                     if (mustCloseConnection)
252                     {
253                         await conn.CloseAsync();
254                     }
255                 }
256             }
257             return ds;
258         }
259 
260         /// <summary>
261         /// 执行数据表格
262         /// </summary>
263         /// <param name="commandType">命令类型</param>
264         /// <param name="commandText">命令文本</param>
265         /// <param name="dbParameters">数据库参数</param>
266         /// <returns></returns>
267         public async Task<DataTable> ExecuteDataTable(CommandType commandType, string commandText, DbParameter[] dbParameters = null)
268         {
269             var ds = await ExecuteDataSet(commandType, commandText, dbParameters);
270             if (ds.Tables.Count > 0)
271             {
272                 return ds.Tables[0];
273             }
274             return new DataTable();
275         }
276 
277         /// <summary>
278         /// Sql参数构建
279         /// </summary>
280         /// <typeparam name="T"></typeparam>
281         /// <param name="entity">实体</param>
282         public DbParameter[] SqlParametersBuild<T>(T entity) where T : class
283         {
284             if (entity == null)
285             {
286                 throw new ArgumentNullException(nameof(entity));
287             }
288             List<DbParameter> dbParameters = new List<DbParameter>();
289 
290             Type type = typeof(T);
291             var propertyInfos = type.GetProperties().Where(w => !w.PropertyType.IsGenericType || w.PropertyType.IsGenericType && w.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)));
292 
293             foreach (var propertyInfo in propertyInfos)
294             {
295                 var dbParameter = DbProviderFactory.CreateParameter();
296                 dbParameter.ParameterName = $"{DbSymbolMapper.parameter[DbOptions.DbType]}{propertyInfo.Name}";
297                 dbParameter.Value = propertyInfo.GetValue(entity) ?? DBNull.Value;
298                 dbParameters.Add(dbParameter);
299             }
300             return dbParameters.ToArray();
301         }
302 
303         /// <summary>
304         /// Sql参数构建
305         /// </summary>
306         /// <param name="keyValues">键值</param>
307         /// <returns></returns>
308         public DbParameter[] SqlParametersBuild(Dictionary<string, object> keyValues)
309         {
310             var dbParameters = new List<DbParameter>();
311             foreach (var key in keyValues.Keys)
312             {
313                 var dbParameter = DbProviderFactory.CreateParameter();
314                 dbParameter.ParameterName = $"{DbSymbolMapper.parameter[DbOptions.DbType]}{key}";
315                 dbParameter.Value = keyValues[key] ?? DBNull.Value;
316                 dbParameters.Add(dbParameter);
317             }
318             return dbParameters.ToArray();
319         }
320 
321         /// <summary>
322         /// 释放方法
323         /// </summary>
324         public void Dispose()
325         {
326 
327         }
328     }

三、Mapper 类的定义

 1 /// <summary>
 2     /// 数据库符号映射
 3     /// </summary>
 4     public static class DbSymbolMapper
 5     {
 6 
 7         /// <summary>
 8         /// 参数
 9         /// </summary>
10         public readonly static Dictionary<DatabaseType, string> parameter;
11 
12         /// <summary>
13         /// 识别
14         /// </summary>
15         public readonly static Dictionary<DatabaseType, string> identify;
16 
17         /// <summary>
18         /// 构造方法
19         /// </summary>
20         static DbSymbolMapper()
21         {
22             parameter = new Dictionary<DatabaseType, string>()
23             {
24                 { DatabaseType.SQLServer,"@"},
25                 { DatabaseType.MySQL,"@"},
26                 { DatabaseType.Oracle,":"},
27                 { DatabaseType.PostgreSQL,"@"},
28                 { DatabaseType.SQLite,"@"}
29             };
30             identify = new Dictionary<DatabaseType, string>()
31             {
32                 { DatabaseType.SQLServer,"[]"},
33                 { DatabaseType.MySQL,"``"},
34                 { DatabaseType.Oracle,"\"\""},
35                 { DatabaseType.PostgreSQL,"\"\""},
36                 { DatabaseType.SQLite,"[]"}
37             };
38         }
39     }

搜索

复制

上一篇：思诺维特×JNPF：构建智慧物联生态体系，赋能产业智能化转型
下一篇：没有了

Ado封装 支持多种数据库实现

相关文章

Ado封装支持多种数据库实现