闲暇时间自己写的DB类，支持MDB,SQLITE,SQLSERVER，支持查询、事务，对象直接插入和更新操作等！（10.2更新）
第一次把自己的东西推送到首页！大侠们请勿见笑啊！
一直做数据库，最近花了点时间把自己常用的东西封装在一起。感觉比以前方便了很多！特此拿出来分享下，如有不足指出，欢迎指出纠正和完善！
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 /// <summary> 14 /// 数据库类型 15 /// </summary> 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<myTran> TranList = new List<myTran>(); //事务集合 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>(T model) where T : class,new() 147 { 148 Type t = typeof(T); 149 Array.ForEach<System.Reflection.PropertyInfo>(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 #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<DbParameter> CreateParameters(string[] names, object[] values) 179 { 180 if (names.Length != values.Length) 181 throw new Exception("参数名称跟参数值数量不匹配！"); 182 var parameters = new List<DbParameter>(); 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<DbParameter> CreateParameters<T>(T model) where T : class,new() 191 { 192 var parameters = new List<DbParameter>(); 193 Type t = typeof(T); 194 Array.ForEach<System.Reflection.PropertyInfo>(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 /// <summary> 204 /// 清除查询字符串和查询参数 205 /// </summary> 206 void Clear() 207 { 208 this.CommandText = ""; 209 if (this.Parameters != null) 210 this.Parameters.Clear(); 211 } 212 #endregion 213 214 #region 返回一个DataTable 215 /// <summary> 216 /// 返回一个DataTable 217 /// </summary> 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 /// <summary> 257 /// 执行一条更新语句 258 /// </summary> 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<DbParameter> parameters { get; set; } 333 334 public myTran(string queryString, List<DbParameter> parameters) 335 { 336 this.queryString = queryString; 337 this.parameters = parameters; 338 } 339 } 340 #endregion 341 342 #region 添加事务 343 public void AddTran(string queryString, List<DbParameter> 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<DbParameter> paras = new List<DbParameter>(); 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 /// <summary> 417 /// 获取更新语句 418 /// </summary> 419 /// <typeparam name="TResult"></typeparam> 420 /// <param name="Result"></param> 421 /// <param name="TableName"></param> 422 /// <param name="IndexFieldName"></param> 423 /// <returns></returns> 424 public string GetUpdateString<TResult>(string TableName, string IndexFieldName) where TResult : class,new() 425 { 426 string rt = "update " + TableName + " set"; 427 Type t = typeof(TResult); 428 Array.ForEach<System.Reflection.PropertyInfo>(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 /// <summary> 441 /// 获取插入语句 442 /// </summary> 443 /// <typeparam name="TResult"></typeparam> 444 /// <param name="Result"></param> 445 /// <param name="TableName"></param> 446 /// <param name="IndexFieldName"></param> 447 /// <returns></returns> 448 public string GetInsertString<TResult>(string TableName, string IndexFieldName) where TResult : class,new() 449 { 450 string rt = "insert into " + TableName + " ("; 451 Type t = typeof(TResult); 452 Array.ForEach<System.Reflection.PropertyInfo>(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<System.Reflection.PropertyInfo>(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 /// <summary> 471 /// 将对象插入到数据库 472 /// </summary> 473 /// <typeparam name="T">对象类型</typeparam> 474 /// <param name="model">对象</param> 475 /// <param name="TableName">表名</param> 476 /// <param name="IndexFieldName">主键ID</param> 477 /// <returns></returns> 478 public bool InsertModel<T>(T model, string TableName, string IndexFieldName) where T : class,new() 479 { 480 this.CommandText = GetInsertString<T>(TableName, IndexFieldName); 481 this.AddParameters<T>(model); 482 return this.ExecuteNonQuery() > 0; 483 } 484 485 /// <summary> 486 /// 将对象更新到数据库 487 /// </summary> 488 /// <typeparam name="T">对象类型</typeparam> 489 /// <param name="model">对象</param> 490 /// <param name="TableName">表名</param> 491 /// <param name="IndexFieldName">主键ID</param> 492 /// <returns></returns> 493 public bool UpdateModel<T>(T model, string TableName, string IndexFieldName) where T : class,new() 494 { 495 this.CommandText = GetUpdateString<T>(TableName, IndexFieldName); 496 this.AddParameters<T>(model); 497 return this.ExecuteNonQuery() > 0; 498 } 499 #endregion 500 501 #region 数据库静态方法 502 503 #region 生成查询字符串 504 /// <summary> 505 /// 返回SQLSERVER连接字符串 506 /// </summary> 507 /// <param name="serverIp">服务器IP</param> 508 /// <param name="uid">用户名</param> 509 /// <param name="pwd">密码</param> 510 /// <param name="catalog">库名</param> 511 /// <param name="timeout">超时时间</param> 512 /// <returns></returns> 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 /// <summary> 519 /// 返回Mdb连接字符串 520 /// </summary> 521 /// <param name="filePath">数据库路径</param> 522 /// <param name="password">数据库密码</param> 523 /// <returns></returns> 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 /// <summary> 530 /// 返回SQLite连接字符串 531 /// </summary> 532 /// <param name="filePath">数据库路径</param> 533 /// <returns></returns> 534 public static string GetSQLiteConnectionString(string filePath) 535 { 536 return string.Format("Data Source={0}", filePath); 537 } 538 #endregion 539 540 #endregion 541 } 542 }
使用例1：
View Code
1 /// <summary> 2 /// 添加内容 3 /// </summary> 4 /// <param name="model"></param> 5 /// <returns></returns> 6 public bool Insert(Model.ContentTable model) 7 { 8 using (var db = Program.GetDB()) 9 { 10 try 11 { 12 return db.InsertModel<Model.ContentTable>(model, "ContentTable", "ID"); 13 } 14 catch (Exception) 15 { 16 throw; 17 } 18 } 19 }
使用例2：
View Code
1 /// <summary> 2 /// 修改内容 3 /// </summary> 4 /// <param name="model"></param> 5 /// <returns></returns> 6 public bool Update(Model.ContentTable model) 7 { 8 using (var db = Program.GetDB()) 9 { 10 try 11 { 12 return db.UpdateModel<Model.ContentTable>(model, "ContentTable", "ID"); 13 } 14 catch (Exception) 15 { 16 throw; 17 } 18 } 19 }
使用例3：
View Code
1 /// <summary> 2 /// 批量插入数据 3 /// </summary> 4 /// <param name="lst"></param> 5 public void InsertAll(List<Model.ContentTable> lst) 6 { 7 using (var db = Program.GetDB()) 8 { 9 try 10 { 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.Content 20 }; 21 db.AddTran(DB.GetInsertString<Model.ContentTable>("ContentTable", "ID"), db.CreateParameters(names, values)); 22 } 23 db.ExecuteTran(); 24 } 25 catch (Exception) 26 { 27 throw; 28 } 29 } 30 }
这里放上编译好的Dll文件供大家调用！点击下载
