第一次把自己的东西推送到首页！大侠们请勿见笑啊！
一直做数据库，最近花了点时间把自己常用的东西封装在一起。感觉比以前方便了很多！特此拿出来分享下，如有不足指出，欢迎指出纠正和完善！
DBHelper 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 try113 {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 try126 {127 if (Conn.State == ConnectionState.Open)128 Conn.Close();129 }130 catch (Exception)131 {132 throw;133 }134 }135 #endregion136 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 < names.Length; 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 #endregion168 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 < names.Length; 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 #endregion201 202 #region 清除查询字符串和查询参数203 /// 204 /// 清除查询字符串和查询参数205 /// 206 void Clear()207 {208 this.CommandText = "";209 if (this.Parameters != null)210 this.Parameters.Clear();211 }212 #endregion213 214 #region 返回一个DataTable215 /// 216 /// 返回一个DataTable217 /// 218 public DataTable ExecuteDataTable()219 {220 try221 {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 finally249 {250 Clear();251 }252 }253 #endregion254 255 #region 执行一条更新语句256 /// 257 /// 执行一条更新语句258 /// 259 public int ExecuteNonQuery()260 {261 try262 {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 finally287 {288 Clear();289 }290 }291 #endregion292 293 #region 返回首行首列294 public object ExecuteScalar()295 {296 try297 {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 finally322 {323 Clear();324 }325 }326 #endregion327 328 #region 自定义事务类329 class myTran330 {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 #endregion341 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 #endregion358 359 #region 清除事务360 void ClearTran()361 {362 TranList.Clear();363 }364 #endregion365 366 #region 执行事务367 public void ExecuteTran()368 {369 try370 {371 using (DbTransaction tran = Conn.BeginTransaction())372 {373 try374 {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 finally399 {400 ClearTran();401 }402 }403 }404 catch (Exception)405 {406 throw;407 }408 finally409 {410 ClearTran();411 }412 }413 #endregion414 415 #region 根据对象生成更新语句416 /// 417 /// 获取更新语句418 /// 419 /// (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 #endregion438 439 #region 根据对象生成插入语句440 /// 441 /// 获取插入语句442 /// 443 /// (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 #endregion468 469 #region 对象操作470 /// 471 /// 将对象插入到数据库472 /// 473 /// 对象类型 474 /// 对象475 /// 表名476 /// 主键ID477 /// (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 /// 主键ID492 /// (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 #endregion500 501 #region 数据库静态方法502 503 #region 生成查询字符串504 /// 505 /// 返回SQLSERVER连接字符串506 /// 507 /// 服务器IP508 /// 用户名509 /// 密码510 /// 库名511 /// 超时时间512 /// 519 /// 返回Mdb连接字符串520 /// 521 /// 数据库路径522 /// 数据库密码523 /// 530 /// 返回SQLite连接字符串531 /// 532 /// 数据库路径533 ///
使用例1：
View Code 1 ///2 /// 添加内容 3 /// 4 /// 5 ///6 public bool Insert(Model.ContentTable model) 7 { 8 using (var db = Program.GetDB()) 9 {10 try11 {12 return db.InsertModel (model, "ContentTable", "ID");13 }14 catch (Exception)15 {16 throw;17 }18 }19 }
使用例2：
View Code 1 ///2 /// 修改内容 3 /// 4 /// 5 ///6 public bool Update(Model.ContentTable model) 7 { 8 using (var db = Program.GetDB()) 9 {10 try11 {12 return db.UpdateModel (model, "ContentTable", "ID");13 }14 catch (Exception)15 {16 throw;17 }18 }19 }
使用例3：
View Code 1 ///2 /// 批量插入数据 3 /// 4 /// 5 public void InsertAll(Listlst) 6 { 7 using (var db = Program.GetDB()) 8 { 9 try10 {11 foreach(var model in lst)12 {13 var names = new string[] { 14 "Title",15 "Content"16 };17 var values = new object[]{18 model.Title,19 model.Content20 };21 db.AddTran(DB.GetInsertString ("ContentTable", "ID"), db.CreateParameters(names, values));22 }23 db.ExecuteTran();24 }25 catch (Exception)26 { 27 throw;28 }29 }30 }
这里放上编译好的Dll文件供大家调用！