自用数据库类升级，支持SQLITE,ACCESS,SQLSERVER，支持直接对象操作，支持类似LINQ的查询语句写法，2.0框架！
DBHelper 源码：
DBHelper 1 /// <summary> 2 /// 数据库操作类 3 /// </summary> 4 public sealed class DB : IDisposable 5 { 6 #region 数据库类型枚举 7 /// <summary> 8 /// 数据库类型 9 /// </summary> 10 public enum DBType 11 { 12 /// <summary> 13 /// SQLSERVER 14 /// </summary> 15 SQLSERVER, 16 /// <summary> 17 /// MDB 18 /// </summary> 19 MDB, 20 /// <summary> 21 /// SQLITE 22 /// </summary> 23 SQLITE 24 } 25 #endregion 26 27 #region 公共成员 28 /// <summary> 29 /// 连接字符串（可用本类的静态方法生成） 30 /// GetSQLConnectionString 31 /// GetMdbConnectionString 32 /// GetSQLiteConnectionString 33 /// </summary> 34 public string ConnectionString { get; set; } //连接字符串 35 36 DBType _DbType; 37 38 /// <summary> 39 /// 数据库类型 40 /// </summary> 41 public DBType DbType 42 { 43 get { return this._DbType; } 44 set 45 { 46 this._DbType = value; 47 switch (value) 48 { 49 case DBType.SQLSERVER: 50 Factory = DbProviderFactories.GetFactory("System.Data.SqlClient"); 51 break; 52 case DBType.MDB: 53 Factory = DbProviderFactories.GetFactory("System.Data.OleDb"); 54 break; 55 case DBType.SQLITE: 56 Factory = DbProviderFactories.GetFactory("System.Data.SQLite"); 57 break; 58 } 59 } 60 } //数据库类型 61 62 /// <summary> 63 /// 查询字符串 64 /// </summary> 65 public string CommandText { get; set; } //查询语句 66 67 #endregion 68 69 #region 私有成员 70 71 private DbParameterCollection Parameters { get; set; } //参数集合 72 73 #endregion 74 75 #region 初始成员 76 77 private DbConnection Conn = null; //连接对象 78 79 private DbProviderFactory Factory = null; //数据库工厂 80 81 private List<myTran> TranList = new List<myTran>(); //事务集合 82 83 #endregion 84 85 #region 构造函数 86 87 88 /// <summary> 89 /// 构造函数 90 /// </summary> 91 /// <param name="dbType">数据库类别，可使用DB.DBType获得</param> 92 /// <param name="connectionString"> 93 /// 连接字符串，可使用本类的静态方法获得 94 /// GetSQLConnectionString 95 /// GetMdbConnectionString 96 /// GetSQLiteConnectionString 97 /// </param> 98 public DB(DBType dbType, string connectionString) 99 {100 this.DbType = dbType;101 this.ConnectionString = connectionString;102 this.Parameters = Factory.CreateCommand().Parameters;103 }104 #endregion105 106 #region 初始化与自动释放107 /// <summary>108 /// 打开数据库109 /// </summary>110 public void Open()111 {112 try113 {114 if (Conn == null)115 {116 Conn = Factory.CreateConnection();117 Conn.ConnectionString = this.ConnectionString;118 Conn.Open();119 }120 else121 {122 if (Conn.State == ConnectionState.Closed)123 Conn.Open();124 }125 }126 catch (Exception)127 {128 throw;129 }130 }131 132 /// <summary>133 /// 关闭数据库134 /// </summary>135 public void Close()136 {137 try138 {139 if (Conn.State == ConnectionState.Open)140 Conn.Close();141 }142 catch (Exception)143 {144 throw;145 }146 }147 148 /// <summary>149 /// 自动释放资源150 /// </summary>151 public void Dispose()152 {153 try154 {155 this.CommandText = string.Empty;156 this.Parameters = null;157 this.ConnectionString = string.Empty;158 this.TranList = null;159 Close();160 }161 catch (Exception)162 {163 throw;164 }165 }166 #endregion167 168 #region 添加查询参数169 /// <summary>170 /// 添加查询参数171 /// </summary>172 /// <param name="name">参数名 如：@Name</param>173 /// <param name="value">参数值 如："Name"</param>174 public void AddParameter(string name, object value)175 {176 var pa = Factory.CreateParameter();177 pa.ParameterName = name;178 pa.Value = value;179 this.Parameters.Add(pa);180 }181 182 /// <summary>183 /// 添加对象为查询参数184 /// </summary>185 /// <typeparam name="T">对象类型</typeparam>186 /// <param name="model">对象</param>187 public void AddParameters<T>(T model) where T : class,new()188 {189 Type t = typeof(T);190 Array.ForEach<System.Reflection.PropertyInfo>(t.GetProperties(), p =>191 {192 AddParameter("@" + p.Name, p.GetValue(model, null));193 });194 }195 196 /// <summary>197 /// 顺序添加数组为查询参数198 /// </summary>199 /// <param name="names">参数名 如：{"@Name","@Age"}</param>200 /// <param name="values">参数值 如：{"Name",20}</param>201 public void AddParameters(string[] names, object[] values)202 {203 if (names.Length != values.Length)204 throw new Exception("参数名称跟参数值数量不匹配！");205 for (var i = 0; i < names.Length; i++)206 {207 var pa = Factory.CreateParameter();208 pa.ParameterName = names[i];209 pa.Value = values[i];210 this.Parameters.Add(pa);211 }212 }213 #endregion214 215 #region 创建查询参数216 /// <summary>217 /// 创建查询参数218 /// </summary>219 /// <param name="name">参数名 如："@Name"</param>220 /// <param name="value">参数值 如："Name"</param>221 /// <returns></returns>222 public DbParameter CreateParameter(string name, object value)223 {224 var pa = Factory.CreateParameter();225 pa.ParameterName = name;226 pa.Value = value;227 return pa;228 }229 230 /// <summary>231 /// 通过数组创建查询参数232 /// </summary>233 /// <param name="names">参数名 如：{"@Name","@Age"}</param>234 /// <param name="values">参数值 如：{"Name",20}</param>235 /// <returns></returns>236 public List<DbParameter> CreateParameters(string[] names, object[] values)237 {238 if (names.Length != values.Length)239 throw new Exception("参数名称跟参数值数量不匹配！");240 var parameters = new List<DbParameter>();241 for (var i = 0; i < names.Length; i++)242 {243 parameters.Add(CreateParameter(names[i], values[i]));244 }245 return parameters;246 }247 248 249 /// <summary>250 /// 通过对象创建查询参数251 /// </summary>252 /// <typeparam name="T">对象类型</typeparam>253 /// <param name="model">对象</param>254 /// <returns></returns>255 public List<DbParameter> CreateParameters<T>(T model) where T : class,new()256 {257 var parameters = new List<DbParameter>();258 Type t = typeof(T);259 Array.ForEach<System.Reflection.PropertyInfo>(t.GetProperties(), p =>260 {261 parameters.Add(CreateParameter(p.Name, p.GetValue(model, null)));262 });263 return parameters;264 }265 #endregion266 267 #region 清除查询字符串和查询参数268 /// <summary>269 /// 清除查询字符串和查询参数270 /// </summary>271 void Clear()272 {273 this.CommandText = "";274 if (this.Parameters != null)275 this.Parameters.Clear();276 }277 #endregion278 279 #region 返回一个DataTable280 /// <summary>281 /// 返回一个DataTable282 /// </summary>283 public DataTable ExecuteDataTable()284 {285 try286 {287 using (DbCommand cmd = Factory.CreateCommand())288 {289 Open();290 cmd.Connection = this.Conn;291 cmd.CommandText = this.CommandText;292 //cmd.Parameters.AddRange(this.Parameters); 293 if (this.Parameters != null)294 foreach (var para in this.Parameters)295 {296 var p = cmd.CreateParameter();297 p.ParameterName = (para as DbParameter).ParameterName;298 p.Value = (para as DbParameter).Value;299 cmd.Parameters.Add(p);300 }301 Clear();302 303 DbDataReader dr = cmd.ExecuteReader();304 DataTable dt = new DataTable();305 dt.Load(dr);306 return dt;307 }308 }309 catch (Exception)310 {311 throw;312 }313 finally314 {315 Clear();316 }317 }318 #endregion319 320 #region 执行一条更新语句321 /// <summary>322 /// 执行一条更新语句323 /// </summary> 324 public int ExecuteNonQuery()325 {326 try327 {328 using (DbCommand cmd = Factory.CreateCommand())329 {330 Open();331 cmd.Connection = this.Conn;332 cmd.CommandText = this.CommandText;333 if (this.Parameters != null)334 foreach (var para in this.Parameters)335 {336 var p = cmd.CreateParameter();337 p.ParameterName = (para as DbParameter).ParameterName;338 p.Value = (para as DbParameter).Value;339 cmd.Parameters.Add(p);340 }341 Clear();342 if (this.Conn.State == ConnectionState.Closed)343 Open();344 return cmd.ExecuteNonQuery();345 }346 }347 catch (Exception)348 {349 throw;350 }351 finally352 {353 Clear();354 }355 }356 #endregion357 358 #region 返回首行首列359 /// <summary>360 /// 返回首行首列361 /// </summary>362 /// <returns></returns>363 public object ExecuteScalar()364 {365 try366 {367 using (var cmd = Factory.CreateCommand())368 {369 Open();370 cmd.Connection = this.Conn;371 cmd.CommandText = this.CommandText;372 if (this.Parameters != null)373 foreach (var para in this.Parameters)374 {375 var p = cmd.CreateParameter();376 p.ParameterName = (para as DbParameter).ParameterName;377 p.Value = (para as DbParameter).Value;378 cmd.Parameters.Add(p);379 }380 Clear();381 if (this.Conn.State == ConnectionState.Closed)382 Open();383 return cmd.ExecuteScalar();384 }385 }386 catch (Exception)387 {388 throw;389 }390 finally391 {392 Clear();393 }394 }395 396 /// <summary>397 /// 返回首行首列398 /// </summary>399 /// <returns></returns>400 public int ExecuteScalarToInt()401 {402 try403 {404 using (var cmd = Factory.CreateCommand())405 {406 Open();407 cmd.Connection = this.Conn;408 cmd.CommandText = this.CommandText;409 if (this.Parameters != null)410 foreach (var para in this.Parameters)411 {412 var p = cmd.CreateParameter();413 p.ParameterName = (para as DbParameter).ParameterName;414 p.Value = (para as DbParameter).Value;415 cmd.Parameters.Add(p);416 }417 Clear();418 if (this.Conn.State == ConnectionState.Closed)419 Open();420 return int.Parse(cmd.ExecuteScalar().ToString());421 }422 }423 catch (Exception)424 {425 throw;426 }427 finally428 {429 Clear();430 }431 }432 #endregion433 434 #region 自定义事务类435 class myTran436 {437 public string queryString { get; set; }438 public List<DbParameter> parameters { get; set; }439 440 public myTran(string queryString, List<DbParameter> parameters)441 {442 this.queryString = queryString;443 this.parameters = parameters;444 }445 }446 #endregion447 448 #region 添加事务449 /// <summary>450 /// 添加事务451 /// </summary>452 /// <param name="queryString">查询语句</param>453 /// <param name="parameters">参数列表 可通过本类的CreateParameters方法生成</param>454 public void AddTran(string queryString, List<DbParameter> parameters)455 {456 var tran = new myTran(queryString, parameters);457 TranList.Add(tran);458 }459 460 /// <summary>461 /// 添加事务462 /// </summary>463 /// <param name="queryString">查询语句</param>464 /// <param name="parameter">参数 可通过本类的CreateParameter方法生成</param>465 public void AddTran(string queryString, DbParameter parameter)466 {467 List<DbParameter> paras = new List<DbParameter>();468 if (parameter != null)469 paras.Add(parameter);470 var tran = new myTran(queryString, paras);471 TranList.Add(tran);472 }473 #endregion474 475 #region 清除事务476 void ClearTran()477 {478 TranList.Clear();479 }480 #endregion481 482 #region 执行事务483 /// <summary>484 /// 执行事务485 /// </summary>486 public void ExecuteTran()487 {488 try489 {490 using (DbTransaction tran = Conn.BeginTransaction())491 {492 try493 {494 if (this.Conn.State == ConnectionState.Closed)495 Open();496 TranList.ForEach(m =>497 {498 using (var cmd = this.Factory.CreateCommand())499 {500 cmd.Connection = this.Conn;501 cmd.CommandText = m.queryString;502 cmd.Transaction = tran;503 m.parameters.ForEach(n =>504 {505 cmd.Parameters.Add(n);506 });507 cmd.ExecuteNonQuery();508 }509 });510 tran.Commit();511 }512 catch (Exception)513 {514 tran.Rollback();515 throw;516 }517 finally518 {519 ClearTran();520 }521 }522 }523 catch (Exception)524 {525 throw;526 }527 finally528 {529 ClearTran();530 }531 }532 #endregion533 534 #region 根据对象生成更新语句535 /// <summary>536 /// 获取更新语句537 /// </summary>538 /// <typeparam name="TResult"></typeparam>539 /// <param name="TableName">表名</param>540 /// <param name="IndexFieldName">主键ID</param>541 /// <returns></returns>542 public string GetUpdateString<TResult>(string TableName, string IndexFieldName) where TResult : class,new()543 {544 string rt = "update " + TableName + " set";545 Type t = typeof(TResult);546 Array.ForEach<System.Reflection.PropertyInfo>(t.GetProperties(), p =>547 {548 if (p.Name != IndexFieldName) rt += " " + p.Name + " = @" + p.Name + " ,";549 });550 rt = rt.Substring(0, rt.Length - 2);551 if (IndexFieldName != null)552 rt += " where " + IndexFieldName + " = @" + IndexFieldName;553 return rt;554 }555 #endregion556 557 #region 根据对象生成插入语句558 /// <summary>559 /// 获取插入语句560 /// </summary>561 /// <typeparam name="TResult"></typeparam>562 /// <param name="TableName">表名</param>563 /// <param name="IndexFieldName">主键ID</param>564 /// <returns></returns>565 public string GetInsertString<TResult>(string TableName, string IndexFieldName) where TResult : class,new()566 {567 string rt = "insert into " + TableName + " (";568 Type t = typeof(TResult);569 Array.ForEach<System.Reflection.PropertyInfo>(t.GetProperties(), p =>570 {571 if (p.Name != IndexFieldName) rt += p.Name + " , ";572 });573 rt = rt.Substring(0, rt.Length - 3);574 rt += ") values (";575 Array.ForEach<System.Reflection.PropertyInfo>(t.GetProperties(), p =>576 {577 if (p.Name != IndexFieldName)578 rt += "@" + p.Name + " , ";579 });580 rt = rt.Substring(0, rt.Length - 3);581 rt += ")";582 return rt;583 }584 #endregion585 586 #region 对象操作587 /// <summary>588 /// 将对象插入到数据库589 /// </summary>590 /// <typeparam name="T">对象类型</typeparam>591 /// <param name="model">对象</param>592 /// <param name="TableName">表名</param>593 /// <param name="IndexFieldName">主键ID</param>594 /// <returns></returns>595 public bool InsertModel<T>(T model, string TableName, string IndexFieldName) where T : class,new()596 {597 this.CommandText = GetInsertString<T>(TableName, IndexFieldName);598 this.AddParameters<T>(model);599 return this.ExecuteNonQuery() > 0;600 }601 602 /// <summary>603 /// 将对象更新到数据库604 /// </summary>605 /// <typeparam name="T">对象类型</typeparam>606 /// <param name="model">对象</param>607 /// <param name="TableName">表名</param>608 /// <param name="IndexFieldName">主键ID</param>609 /// <returns></returns>610 public bool UpdateModel<T>(T model, string TableName, string IndexFieldName) where T : class,new()611 {612 this.CommandText = GetUpdateString<T>(TableName, IndexFieldName);613 this.AddParameters<T>(model);614 return this.ExecuteNonQuery() > 0;615 }616 #endregion617 618 #region 数据库静态方法619 620 #region 生成查询字符串621 /// <summary>622 /// 返回SQLSERVER连接字符串623 /// </summary>624 /// <param name="serverIp">服务器IP</param>625 /// <param name="uid">用户名</param>626 /// <param name="pwd">密码</param>627 /// <param name="catalog">库名</param>628 /// <param name="timeout">超时时间</param>629 /// <returns></returns>630 public static string GetSQLConnectionString(string serverIp, string uid, string pwd, string catalog, int timeout)631 {632 return string.Format("Server={0};User ID={1};PWD={2};Initial Catalog={3};Connect TimeOut={4};", serverIp, uid, pwd, catalog, timeout.ToString());633 }634 635 /// <summary>636 /// 返回Mdb连接字符串637 /// </summary>638 /// <param name="filePath">数据库路径</param>639 /// <param name="password">数据库密码</param>640 /// <returns></returns>641 public static string GetMdbConnectionString(string filePath, string password)642 {643 return string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Persist Security Info=False;Jet OLEDB:Database Password={1}", filePath, password);644 }645 646 /// <summary>647 /// 返回SQLite连接字符串648 /// </summary>649 /// <param name="filePath">数据库路径</param>650 /// <returns></returns>651 public static string GetSQLiteConnectionString(string filePath)652 {653 return string.Format("Data Source={0}", filePath);654 }655 #endregion656 657 #endregion658 659 #region 类LINQ语法660 661 int valueCount = 0;662 663 public DB Select(string fieldName)664 {665 this.CommandText = "select " + fieldName;666 return this;667 }668 669 public DB Top(int count)670 {671 this.CommandText = this.CommandText.Insert(7, " top "+count.ToString());672 return this;673 }674 675 public DB From(string tableName)676 {677 this.CommandText += " from " + tableName;678 return this;679 }680 681 public DB WhereIs(string filedName, object value)682 {683 AppendWhere();684 this.CommandText += " " + filedName + " = @" + filedName;685 this.AddParameter("@" + filedName, value);686 return this;687 }688 689 public DB WhereBig(string fieldName, object value)690 {691 AppendWhere();692 this.CommandText += " " + fieldName + " > @" + fieldName;693 this.AddParameter("@" + fieldName, value);694 return this;695 }696 697 public DB Like(string fieldName, object value)698 {699 AppendWhere();700 this.CommandText += " " + fieldName + " like @" + fieldName;701 this.AddParameter(fieldName, value);702 return this;703 }704 705 public DB Bettween(string fieldName, object value1, object value2)706 {707 this.AddParameter(fieldName, value1);708 this.AddParameter(fieldName, value2);709 return this;710 }711 712 void AppendWhere()713 {714 if (this.CommandText.Contains("where"))715 {716 this.CommandText += " and";717 }718 else719 this.CommandText += " where";720 }721 #endregion722 }使用案例7个：
使用案例 1 class Test 2 { 3 #region 静态方法，获取数据库操作对象 4 private readonly DB GetDB() 5 { 6 return new DB(DB.DBType.SQLSERVER, DB.GetSQLConnectionString(".", "sa", "sa", "Post", 30)); 7 } 8 #endregion 9 10 #region 实体类 11 class Model 12 { 13 public int Id { get; set; } 14 public string PostNum { get; set; } 15 } 16 #endregion 17 18 #region 获取条目 19 public int GetCount() 20 { 21 using (var db = GetDB()) 22 { 23 return db 24 .Select("count(*)") 25 .From("PostTable") 26 .ExecuteScalarToInt(); 27 } 28 } 29 #endregion 30 31 #region 获取实体列表 32 public List<Model> GetList(int count) 33 { 34 using (var db = GetDB()) 35 { 36 var dt = db.Select("Id,PostNum").From("PostTable").Top(count).ExecuteDataTable(); 37 var list = new List<Model>(); 38 foreach (System.Data.DataRow row in dt.Rows) 39 { 40 Model model = new Model 41 { 42 Id = int.Parse(row["Id"].ToString()), 43 PostNum = row["PostNum"].ToString() 44 }; 45 list.Add(model); 46 } 47 return list; 48 } 49 } 50 #endregion 51 52 #region 插入实体方法1 53 public bool Insert(Model model) 54 { 55 using (var db = GetDB()) 56 { 57 return db.InsertModel(model, "PostTable", "Id"); 58 } 59 } 60 #endregion 61 62 #region 插入实体方法2 63 public bool Insert(int id, string postNum) 64 { 65 using (var db = GetDB()) 66 { 67 return db.Insert("PostTable", 68 new string[] { "Id", "PostNum" }, 69 new object[] { id, postNum }) 70 .ExecuteNonQuery() > 0; 71 } 72 } 73 #endregion 74 75 #region 删除 76 public bool Delete(int id) 77 { 78 using (var db = GetDB()) 79 { 80 return db.Delete("PostTable").WhereIs("Id", id).ExecuteNonQuery() > 0; 81 } 82 } 83 #endregion 84 85 #region 更新实体方法1 86 public bool Update(Model model) 87 { 88 using (var db = GetDB()) 89 { 90 return db.UpdateModel(model, "PostTable", "Id"); 91 } 92 } 93 #endregion 94 95 #region 更新实体方法2 96 public bool Update(int id, string postNum) 97 { 98 using (var db = GetDB()) 99 {100 return db101 .Update("PostTable")102 .Set("PostNum", postNum)103 .WhereIs("Id", id)104 .ExecuteNonQuery() > 0;105 }106 }107 #endregion108 109 #region 查询实体110 public Model GetModel(int id)111 {112 using (var db = GetDB())113 {114 var dt = db.Select("Id,PostNum").From("PostTable").WhereIs("Id", id).ExecuteDataTable();115 if (dt.Rows.Count > 0)116 {117 Model model = new Model118 {119 Id = int.Parse(dt.Rows[0].ToString()),120 PostNum = dt.Rows[0].ToString()121 };122 return model;123 }124 else125 return null;126 }127 }128 #endregion129 130 }简单举了几个例子，其他的大家可以自己在使用中体会！（献丑了，高手飘过哦~~呵呵！）
TAG:
