dapper之连接数据库（Oracle，SQL Server，MySql）

时间：2021-12-06 16:51:31

　　因为项目需求，需要项目同时可以访问三个数据库，因此本人经过一番研究，得出以下代码。

　　

1.建立公共连接抽象类(DataBase)

  1 public abstract class DataBase
  2     {
  3         /// <summary>
  4         /// 
  5         /// </summary>
  6         public abstract string ConnectionString { get; }
  7 
  8         /// <summary>
  9         /// 
 10         /// </summary>
 11         /// <param name="cmd"></param>
 12         /// <param name="pName"></param>
 13         /// <param name="value"></param>
 14         /// <param name="type"></param>
 15         /// <returns></returns>
 16 
 17         public DbParameter CreateParameter(DbCommand cmd, String pName, Object value, System.Data.DbType type)
 18         {
 19             var p = cmd.CreateParameter();
 20             p.ParameterName = pName;
 21             p.Value = (value == null ? DBNull.Value : value);
 22             p.DbType = type;
 23             return p;
 24         }
 25         /// <summary>
 26         /// 
 27         /// </summary>
 28         /// <returns></returns>
 29         public abstract DbConnection CreateConnection(); 
 30         /// <summary>
 31         /// 返回List
 32         /// </summary>
 33         /// <typeparam name="T"></typeparam>
 34         /// <param name="sql"></param>
 35         /// <param name="paramObject"></param>
 36         /// <returns></returns>
 37         public List<T> Select<T>(string sql, Object paramObject = null)
 38         {
 39              
 40             try
 41             {
 42                 using (DbConnection conn = CreateConnection())
 43                 {
 44                     conn.Open();
 45                     var list = Dapper.SqlMapper.Query<T>(conn, sql, paramObject);
 46                     return list.ToList<T>();
 47                 }
 48              
 49             }
 50             catch (Exception ex)
 51             {
 52                 Logs.Write(LogType.Error, ex.Message,this.GetType());
 53                 return null;
 54             }
 55         }
 56         /// <summary>
 57         /// 返回List
 58         /// </summary>
 59         /// <typeparam name="T"></typeparam>
 60         /// <param name="tabName">表名</param>
 61         /// <param name="paramObject"></param>
 62         /// <returns></returns>
 63         public List<T> Select<T>()
 64         { 
 65             try
 66             {
 67                 using (DbConnection conn = CreateConnection())
 68                 {
 69                     conn.Open();
 70                     var list = Dapper.SqlMapper.Query<T>(conn, "SELECT * FROM "   typeof(T).Name, null);
 71                     return list.ToList<T>();
 72                 }
 73             }
 74             catch (Exception ex)
 75             {
 76                 Logs.Write(LogType.Error, ex.Message, this.GetType());
 77                 return null;
 78             }
 79         }
 80         public int Insert<T>(T t)
 81         {
 82             try
 83             {
 84                 using (DbConnection conn = CreateConnection())
 85                 {
 86                     conn.Open();
 87                     var id = conn.Insert(t);
 88                     return id ?? 0;
 89                 }
 90             }
 91             catch (Exception ex)
 92             {
 93                 Logs.Write(LogType.Error, ex.Message, this.GetType());
 94                 return -1;
 95             }
 96         }
 97         public int Delete<T>(T t)
 98         {
 99             try
100             {
101                 using (DbConnection conn = CreateConnection())
102                 {
103                     conn.Open();
104                     return conn.Delete(t); 
105                 }
106             }
107             catch (Exception ex)
108             {
109                 Logs.Write(LogType.Error, ex.Message, this.GetType());
110                 return -1;
111             }
112         }
113         public int Update<T>(T t)
114         {
115             try
116             {
117                 using (DbConnection conn = CreateConnection())
118                 {
119                     conn.Open();
120                     return conn.Update(t); 
121                 }
122             }
123             catch (Exception ex)
124             {
125                 Logs.Write(LogType.Error, ex.Message, this.GetType());
126                 return -1;
127             }
128         }
129         public string InsertByGuid<T>(T t)
130         {
131             try
132             {
133                 using (DbConnection conn = CreateConnection())
134                 {
135                     conn.Open();
136                     return conn.Insert<string,T>(t); 
137                 }
138             }
139             catch (Exception ex)
140             {
141                 Logs.Write(LogType.Error, ex.Message, this.GetType());
142                 return "";
143             }
144         }
145         public List<T> GetList<T>(string sql, Object paramObject = null)
146         {
147             try
148             {
149                 using (DbConnection conn = CreateConnection())
150                 {
151                     conn.Open();
152                     return conn.Query<T>(sql, paramObject).ToList();
153                 }
154             }
155             catch (Exception ex)
156             {
157                 Logs.Write(LogType.Error, ex.Message, this.GetType());
158                 return null;
159             }
160         }
161         public IEnumerable<dynamic> GetList(string sql, Object paramObject = null)
162         {
163             try
164             {
165                 using (DbConnection conn = CreateConnection())
166                 {
167                     conn.Open();
168                     return conn.Query(sql, paramObject);
169                 }
170             }
171             catch (Exception ex)
172             {
173                 Logs.Write(LogType.Error, ex.Message, this.GetType());
174                 return null;
175             }
176         }
177         /// <summary>
178         /// 
179         /// </summary>
180         /// <param name="sql"></param>
181         /// <param name="paramObject"></param>
182         /// <returns></returns>
183         public List<dynamic> Select(string sql, Object paramObject = null)
184         {
185             DbConnection conn = null;
186             try
187             {
188                 conn = CreateConnection();
189                 conn.Open();
190                 var list = Dapper.SqlMapper.Query(conn, sql, paramObject);
191                 return list.ToList<dynamic>();
192             }
193             catch (Exception ex)
194             {
195                 Logs.Write(LogType.Error, ex.Message, this.GetType());
196                 return null;
197             }
198             finally
199             {
200                 if (conn != null)
201                     conn.Close();
202             }
203         }
204 
205         /// <summary>
206         /// 获取一条数据
207         /// </summary>
208         /// <param name="sql"></param>
209         /// <param name="paramObject"></param>
210         /// <returns></returns>
211         public dynamic Single(string sql, Object paramObject = null)
212         {
213             DbConnection conn = null;
214             try
215             {
216                 conn = CreateConnection();
217                 conn.Open();
218                 var list = Dapper.SqlMapper.QuerySingleOrDefault<dynamic>(conn, sql, paramObject);
219                 return list;
220             }
221             catch (Exception ex)
222             {
223                 Logs.Write(LogType.Error, ex.Message, this.GetType());
224                 return null;
225             }
226             finally
227             {
228                 if (conn != null)
229                     conn.Close();
230             }
231         }
232 
233         /// <summary>
234         /// 获取一条数据
235         /// </summary>
236         /// <typeparam name="T"></typeparam>
237         /// <param name="sql"></param>
238         /// <param name="paramObject"></param>
239         /// <returns></returns>
240         public T Single<T>(string sql, Object paramObject = null)
241         {
242 
243             DbConnection conn = null;
244             try
245             {
246                 conn = CreateConnection();
247                 conn.Open();
248                 var list = Dapper.SqlMapper.QuerySingleOrDefault<T>(conn, sql, paramObject);
249                 return list;
250             }
251             catch (Exception ex)
252             {
253                 Logs.Write(LogType.Error, ex.Message, this.GetType());
254                 return default(T);
255             }
256             finally
257             {
258                 if (conn != null)
259                     conn.Close();
260             }
261         }
262 
263         /// <summary>
264         /// 获取一行一列
265         /// </summary>
266         /// <typeparam name="T"></typeparam>
267         /// <param name="sql"></param>
268         /// <param name="paramObject"></param>
269         /// <returns></returns>
270         public T ExecuteScalar<T>(string sql, Object paramObject = null)
271         {
272 
273             DbConnection conn = null;
274             try
275             {
276                 conn = CreateConnection();
277                 conn.Open();
278                 T t = Dapper.SqlMapper.ExecuteScalar<T>(conn, sql, paramObject);
279                 return t;
280             }
281             catch (Exception ex)
282             {
283                 Logs.Write(LogType.Error, ex.Message, this.GetType());
284                 return default(T);
285             }
286             finally
287             {
288                 if (conn != null)
289                     conn.Close();
290             }
291         }
292 
293         /// <summary>
294         /// 返回受影响行数
295         /// </summary>
296         /// <param name="sql"></param>
297         /// <param name="paramObject"></param>
298         /// <returns></returns>
299         public int Execute(string sql, Object paramObject = null)
300         {
301             DbConnection conn = null;
302             try
303             {
304                 conn = CreateConnection();
305                 conn.Open();
306                 int count = Dapper.SqlMapper.Execute(conn, sql, paramObject);
307                 return count;
308             }
309             catch (Exception ex)
310             {
311                 Logs.Write(LogType.Error, ex.Message, this.GetType());
312                 return 0;
313             }
314             finally
315             {
316                 if (conn != null)
317                     conn.Close();
318             }
319         }
320     }

2.建立3个不同数据库连接类（OracleDataBase、SqlDataBase、MySqlDataBase）继承(DataBase)类

 1 public class OracleDataBase : DataBase
 2     {
 3         public override string ConnectionString
 4         {
 5             get
 6             {
 7                 return System.Configuration.ConfigurationManager.ConnectionStrings["OracleConnection"].ToString();
 8             }
 9         }
10         /// <summary>
11         /// 常用
12         /// </summary>
13         /// <returns></returns>
14         public override DbConnection CreateConnection()
15         {
16             Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(ConnectionString);
17             conn.ConnectionString = ConnectionString;
18             return null;
19         }
20     }
 1 public class SqlDataBase : DataBase
 2     {
 3         /// <summary>
 4         /// 
 5         /// </summary>
 6         public override string ConnectionString
 7         {
 8             get
 9             {
10                 return System.Configuration.ConfigurationManager.ConnectionStrings["SqlConnection"].ToString();
11             }
12         }
13         /// <summary>
14         /// 
15         /// </summary>
16         /// <returns></returns>
17         public override DbConnection CreateConnection()
18         {
19             SqlConnection conn = new SqlConnection(ConnectionString);
20             conn.ConnectionString = ConnectionString;
21             return conn;
22         }
23         
24     }
 1 public  class MySqlDataBase : DataBase
 2     {
 3         /// <summary>
 4         /// 
 5         /// </summary>
 6         public override string ConnectionString
 7         {
 8             get
 9             {
10                 return System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnection"].ToString();
11             }
12         }
13         /// <summary>
14         /// 常用
15         /// </summary>
16         /// <returns></returns>
17         public override DbConnection CreateConnection()
18         {
19             MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString); 
20             return conn;
21         }
22     }

3.访问不同的连接

 1  public List<T> GetList<T>()
 2         {
 3             var db = new SqlDataBase();
 4             return db.Select<T>();
 5         }
 6         public List<T> GetList<T>()
 7         {
 8             var db = new OracleDataBase();
 9             return db.Select<T>();
10         }
11         public List<T> GetList<T>()
12         {
13             var db = new MySqlDataBase();
14             return db.Select<T>();
15         }

以上代码就是访问不同的数据库的列表信息，其他增删改和上面的差不多写法，如果还有其他问题，请评论！

相关文章