1 using System;
2 using System.Collections.Generic;
3 using System.Text;
4 using System.Data;
5 using System.Data.Common;
6 using System.Reflection;
7
8 namespace DBHelper
9 {
10 public sealed class DB : IDisposable
11 {
12 #region 数据库类型枚举
13 ///
14 /// 数据库类型
15 ///
16 public enum DBType
17 {
18 SQLSERVER,
19 MDB,
20 SQLITE
21 }
22 #endregion
23
24 #region 公共成员
25 public string ConnectionString { get; set; } //连接字符串
26
27 DBType _DbType;
28
29 public DBType DbType
30 {
31 get { return this._DbType; }
32 set
33 {
34 this._DbType = value;
35 switch (value)
36 {
37 case DBType.SQLSERVER:
38 Factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
39 break;
40 case DBType.MDB:
41 Factory = DbProviderFactories.GetFactory("System.Data.OleDb");
42 break;
43 case DBType.SQLITE:
44 Factory = DbProviderFactories.GetFactory("System.Data.SQLite");
45 break;
46 }
47 }
48 } //数据库类型
49
50 public string CommandText { get; set; } //查询语句
51
52 //public DbParameterCollection Parameters { get; set; } //参数集合
53
54
55
56 #endregion
57
58 #region 私有成员
59
60 private DbParameterCollection Parameters { get; set; } //参数集合
61
62 #endregion
63
64 #region 初始成员
65
66 private DbConnection COnn= null; //连接对象
67
68 private DbProviderFactory Factory = null; //数据库工厂
69
70 private List TranList = new List(); //事务集合
71
72 #endregion
73
74 #region 构造函数
75 public DB()
76 { }
77
78 public DB(DBType dbType, string connectionString)
79 {
80 this.DbType = dbType;
81 this.COnnectionString= connectionString;
82 this.Parameters = Factory.CreateCommand().Parameters;
83 }
84 #endregion
85
86 #region 初始化与自动释放
87
88 public void Open()
89 {
90 try
91 {
92 if (COnn== null)
93 {
94 COnn= Factory.CreateConnection();
95 Conn.COnnectionString= this.ConnectionString;
96 Conn.Open();
97 }
98 else
99 {
100 if (Conn.State == ConnectionState.Closed)
101 Conn.Open();
102 }
103 }
104 catch (Exception)
105 {
106 throw;
107 }
108 }
109
110 public void Close()
111 {
112 try
113 {
114 if (Conn.State == ConnectionState.Open)
115 Conn.Close();
116 }
117 catch (Exception)
118 {
119 throw;
120 }
121 }
122
123 public void Dispose()
124 {
125 try
126 {
127 if (Conn.State == ConnectionState.Open)
128 Conn.Close();
129 }
130 catch (Exception)
131 {
132 throw;
133 }
134 }
135 #endregion
136
137 #region 添加查询参数
138 public void AddParameter(string name, object value)
139 {
140 var pa = Factory.CreateParameter();
141 pa.ParameterName = name;
142 pa.Value = value;
143 this.Parameters.Add(pa);
144 }
145
146 public void AddParameters(T model) where T : class,new()
147 {
148 Type t = typeof(T);
149 Array.ForEach(t.GetProperties(), p =>
150 {
151 AddParameter("@" + p.Name, p.GetValue(model, null));
152 });
153 }
154
155 public void AddParameters(string[] names, object[] values)
156 {
157 if (names.Length != values.Length)
158 throw new Exception("参数名称跟参数值数量不匹配！");
159 for (var i = 0; i )
160 {
161 var pa = Factory.CreateParameter();
162 pa.ParameterName = names[i];
163 pa.Value = values[i];
164 this.Parameters.Add(pa);
165 }
166 }
167 #endregion
168
169 #region 创建查询参数
170 public DbParameter CreateParameter(string name, object value)
171 {
172 var pa = Factory.CreateParameter();
173 pa.ParameterName = name;
174 pa.Value = value;
175 return pa;
176 }
177
178 public List CreateParameters(string[] names, object[] values)
179 {
180 if (names.Length != values.Length)
181 throw new Exception("参数名称跟参数值数量不匹配！");
182 var parameters = new List();
183 for (var i = 0; i )
184 {
185 parameters.Add(CreateParameter(names[i],values[i]));
186 }
187 return parameters;
188 }
189
190 public List CreateParameters(T model) where T : class,new()
191 {
192 var parameters = new List();
193 Type t = typeof(T);
194 Array.ForEach(t.GetProperties(), p =>
195 {
196 parameters.Add(CreateParameter(p.Name, p.GetValue(model, null)));
197 });
198 return parameters;
199 }
200 #endregion
201
202 #region 清除查询字符串和查询参数
203 ///
204 /// 清除查询字符串和查询参数
205 ///
206 void Clear()
207 {
208 this.CommandText = "";
209 if (this.Parameters != null)
210 this.Parameters.Clear();
211 }
212 #endregion
213
214 #region 返回一个DataTable
215 ///
216 /// 返回一个DataTable
217 ///
218 public DataTable ExecuteDataTable()
219 {
220 try
221 {
222 using (DbCommand cmd = Factory.CreateCommand())
223 {
224 Open();
225 cmd.COnnection= this.Conn;
226 cmd.CommandText = this.CommandText;
227 //cmd.Parameters.AddRange(this.Parameters);
228 if (this.Parameters != null)
229 foreach (var para in this.Parameters)
230 {
231 var p = cmd.CreateParameter();
232 p.ParameterName = (para as DbParameter).ParameterName;
233 p.Value = (para as DbParameter).Value;
234 cmd.Parameters.Add(p);
235 }
236 Clear();
237
238 DbDataReader dr = cmd.ExecuteReader();
239 DataTable dt = new DataTable();
240 dt.Load(dr);
241 return dt;
242 }
243 }
244 catch (Exception)
245 {
246 throw;
247 }
248 finally
249 {
250 Clear();
251 }
252 }
253 #endregion
254
255 #region 执行一条更新语句
256 ///
257 /// 执行一条更新语句
258 ///
259 public int ExecuteNonQuery()
260 {
261 try
262 {
263 using (DbCommand cmd = Factory.CreateCommand())
264 {
265 Open();
266 cmd.COnnection= this.Conn;
267 cmd.CommandText = this.CommandText;
268 if (this.Parameters != null)
269 foreach (var para in this.Parameters)
270 {
271 var p = cmd.CreateParameter();
272 p.ParameterName = (para as DbParameter).ParameterName;
273 p.Value = (para as DbParameter).Value;
274 cmd.Parameters.Add(p);
275 }
276 Clear();
277 if (this.Conn.State == ConnectionState.Closed)
278 Open();
279 return cmd.ExecuteNonQuery();
280 }
281 }
282 catch (Exception)
283 {
284 throw;
285 }
286 finally
287 {
288 Clear();
289 }
290 }
291 #endregion
292
293 #region 返回首行首列
294 public object ExecuteScalar()
295 {
296 try
297 {
298 using (var cmd = Factory.CreateCommand())
299 {
300 Open();
301 cmd.COnnection= this.Conn;
302 cmd.CommandText = this.CommandText;
303 if (this.Parameters != null)
304 foreach (var para in this.Parameters)
305 {
306 var p = cmd.CreateParameter();
307 p.ParameterName = (para as DbParameter).ParameterName;
308 p.Value = (para as DbParameter).Value;
309 cmd.Parameters.Add(p);
310 }
311 Clear();
312 if (this.Conn.State == ConnectionState.Closed)
313 Open();
314 return cmd.ExecuteScalar();
315 }
316 }
317 catch (Exception)
318 {
319 throw;
320 }
321 finally
322 {
323 Clear();
324 }
325 }
326 #endregion
327
328 #region 自定义事务类
329 class myTran
330 {
331 public string queryString { get; set; }
332 public List parameters { get; set; }
333
334 public myTran(string queryString, List parameters)
335 {
336 this.queryString = queryString;
337 this.parameters = parameters;
338 }
339 }
340 #endregion
341
342 #region 添加事务
343 public void AddTran(string queryString, List parameters)
344 {
345 var tran = new myTran(queryString, parameters);
346 TranList.Add(tran);
347 }
348
349 public void AddTran(string queryString, DbParameter parameter)
350 {
351 List paras = new List();
352 if (parameter != null)
353 paras.Add(parameter);
354 var tran = new myTran(queryString, paras);
355 TranList.Add(tran);
356 }
357 #endregion
358
359 #region 清除事务
360 void ClearTran()
361 {
362 TranList.Clear();
363 }
364 #endregion
365
366 #region 执行事务
367 public void ExecuteTran()
368 {
369 try
370 {
371 using (DbTransaction tran = Conn.BeginTransaction())
372 {
373 try
374 {
375 if (this.Conn.State == ConnectionState.Closed)
376 Open();
377 TranList.ForEach(m =>
378 {
379 using (var cmd = this.Factory.CreateCommand())
380 {
381 cmd.COnnection= this.Conn;
382 cmd.CommandText = m.queryString;
383 cmd.Transaction = tran;
384 m.parameters.ForEach(n =>
385 {
386 cmd.Parameters.Add(n);
387 });
388 cmd.ExecuteNonQuery();
389 }
390 });
391 tran.Commit();
392 }
393 catch (Exception)
394 {
395 tran.Rollback();
396 throw;
397 }
398 finally
399 {
400 ClearTran();
401 }
402 }
403 }
404 catch (Exception)
405 {
406 throw;
407 }
408 finally
409 {
410 ClearTran();
411 }
412 }
413 #endregion
414
415 #region 根据对象生成更新语句
416 ///
417 /// 获取更新语句
418 ///
419 ///
420 ///
421 ///
422 ///
423 ///
424 public string GetUpdateString(string TableName, string IndexFieldName) where TResult : class,new()
425 {
426 string rt = "update " + TableName + " set";
427 Type t = typeof(TResult);
428 Array.ForEach(t.GetProperties(), p =>
429 {
430 if (p.Name != IndexFieldName) rt += " " + p.Name + " = @" + p.Name + " ,";
431 });
432 rt = rt.Substring(0, rt.Length - 2);
433 if (IndexFieldName != null)
434 rt += " where " + IndexFieldName + " = @" + IndexFieldName;
435 return rt;
436 }
437 #endregion
438
439 #region 根据对象生成插入语句
440 ///
441 /// 获取插入语句
442 ///
443 ///
444 ///
445 ///
446 ///
447 ///
448 public string GetInsertString(string TableName, string IndexFieldName) where TResult : class,new()
449 {
450 string rt = "insert into " + TableName + " (";
451 Type t = typeof(TResult);
452 Array.ForEach(t.GetProperties(), p =>
453 {
454 if (p.Name != IndexFieldName) rt += p.Name + " , ";
455 });
456 rt = rt.Substring(0, rt.Length - 3);
457 rt += ") values (";
458 Array.ForEach(t.GetProperties(), p =>
459 {
460 if (p.Name != IndexFieldName)
461 rt += "@" + p.Name + " , ";
462 });
463 rt = rt.Substring(0, rt.Length - 3);
464 rt += ")";
465 return rt;
466 }
467 #endregion
468
469 #region 对象操作
470 ///
471 /// 将对象插入到数据库
472 ///
473 /// 对象类型
474 /// 对象
475 /// 表名
476 /// 主键ID
477 ///
478 public bool InsertModel(T model, string TableName, string IndexFieldName) where T : class,new()
479 {
480 this.CommandText = GetInsertString(TableName, IndexFieldName);
481 this.AddParameters(model);
482 return this.ExecuteNonQuery() > 0;
483 }
484
485 ///
486 /// 将对象更新到数据库
487 ///
488 /// 对象类型
489 /// 对象
490 /// 表名
491 /// 主键ID
492 ///
493 public bool UpdateModel(T model, string TableName, string IndexFieldName) where T : class,new()
494 {
495 this.CommandText = GetUpdateString(TableName, IndexFieldName);
496 this.AddParameters(model);
497 return this.ExecuteNonQuery() > 0;
498 }
499 #endregion
500
501 #region 数据库静态方法
502
503 #region 生成查询字符串
504 ///
505 /// 返回SQLSERVER连接字符串
506 ///
507 /// 服务器IP
508 /// 用户名
509 /// 密码
510 /// 库名
511 /// 超时时间
512 ///
513 public static string GetSQLConnectionString(string serverIp, string uid, string pwd, string catalog, int timeout)
514 {
515 return string.Format("Server={0};User ID={1};PWD={2};Initial Catalog={3};Connect TimeOut={4};", serverIp, uid, pwd, catalog, timeout.ToString());
516 }
517
518 ///
519 /// 返回Mdb连接字符串
520 ///
521 /// 数据库路径
522 /// 数据库密码
523 ///
524 public static string GetMdbConnectionString(string filePath, string password)
525 {
526 return string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Persist Security Info=False;Jet OLEDB:Database Password={1}", filePath, password);
527 }
528
529 ///
530 /// 返回SQLite连接字符串
531 ///
532 /// 数据库路径
533 ///
534 public static string GetSQLiteConnectionString(string filePath)
535 {
536 return string.Format("Data Source={0}", filePath);
537 }
538 #endregion
539
540 #endregion
541 }
542 }
DBHelper
京公网安备 11010802041100号