使用oledb操作數據庫工具類,可使用泛型統一操作
using System; using System.Collections.Generic; using System.Data; using System.Data.OleDb; using System.Linq; using System.Reflection; using System.Text; using System.Windows.Forms; namespace CommonUtil { public class DataBaseUtil { //傳遞數據庫文件路徑,這裡使用的是access2007數據庫 public DataBaseUtil(string path) { Path = path; ConnStr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False; ", path); conn = new OleDbConnection(ConnStr); } public string Path; public static string ConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\\numdb.accdb;Persist Security Info=False; "; private OleDbConnection conn= new OleDbConnection(ConnStr);//創建一個connection對象 //使用泛型,獲取所有的實體類對象,返回list public List ReieveList() where T : class,new() { try { var className = (typeof(T)).Name; var sql = string.Format("SELECT * FROM {0}", className); OleDbDataAdapter da = new OleDbDataAdapter(sql, ConnStr); DataSet ds = new DataSet(); da.Fill(ds); var dt = ds.Tables[0]; var list = ConverterUtil.ConvertDataTableToList(dt); return list; } catch (Exception e) { MessageBox.Show(e.Message); return null; } } //同上,根據條件,查詢,返回list實體類列表 public List ReieveList(string where) where T : class,new() { try { var className = (typeof(T)).Name; var sql = string.Format("SELECT * FROM {0} {1}", className,where); OleDbDataAdapter da = new OleDbDataAdapter(sql, ConnStr); DataSet ds = new DataSet(); da.Fill(ds); var dt = ds.Tables[0]; var list = ConverterUtil.ConvertDataTableToList(dt); return list; } catch (Exception e) { MessageBox.Show(e.Message); return null; } } //插入一條數據 public bool Insert(T entity) where T : class,new() { try { var type = typeof (T); var className = type.Name; var fields = ""; var values = ""; foreach (var property in type.GetProperties()) { if (property.Name.Equals("ID")) continue; fields += "," + property.Name; var isNumStr = (property.PropertyType == typeof (double) || property.PropertyType == typeof (int)) ? "" : "'"; values += "," + isNumStr + property.GetValue(entity, null) + isNumStr; } fields = fields.Substring(1); values = values.Substring(1); var sql = string.Format("insert into {0}({1}) values ({2}) ", className, fields, values); OleDbDataAdapter da = new OleDbDataAdapter(); da.InsertCommand = new OleDbCommand(sql, conn); da.InsertCommand.CommandText = sql; conn.Open(); da.InsertCommand.ExecuteNonQuery(); conn.Close(); return true; } catch (Exception e) { MessageBox.Show(e.Message); return false; } finally { conn.Close(); } } //更新實體類 public bool Update(T entity) where T : class,new() { try { var type = typeof(T); var className = type.Name; var values = ""; var id = ""; foreach (var property in type.GetProperties()) { if (property.Name.Equals("ID")) { id = " where ID="+ property.GetValue(entity, null).ToString(); continue; } var isNumStr = (property.PropertyType == typeof(double) || property.PropertyType == typeof(int)) ? "" : "'"; values += "," +property.Name +"="+ isNumStr + property.GetValue(entity, null) + isNumStr; } values = values.Substring(1); var sql = string.Format("update {0} set {1} {2}", className, values,id); OleDbDataAdapter da = new OleDbDataAdapter(); da.UpdateCommand = new OleDbCommand(sql, conn); da.UpdateCommand.CommandText = sql; conn.Open(); da.UpdateCommand.ExecuteNonQuery(); conn.Close(); return true; } catch (Exception e) { MessageBox.Show(e.Message); return false; } finally { conn.Close(); } } //根據條件刪除數據 public bool Delete(string where) { try { var type = typeof(T); var className = type.Name; var sql = string.Format("delete from {0} {1}", className, where); OleDbDataAdapter da = new OleDbDataAdapter(); da.DeleteCommand = new OleDbCommand(sql, conn); da.DeleteCommand.CommandText = sql; conn.Open(); da.DeleteCommand.ExecuteNonQuery(); conn.Close(); return true; } catch (Exception e) { MessageBox.Show(e.Message); return false; } finally { conn.Close(); } } //批量插入數據 public bool InsertList(List entitysList) where T : class,new() { try { var type = typeof (T); var className = type.Name; var fields = ""; var values = ""; foreach (var property in type.GetProperties()) { if (property.Name.Equals("ID")) continue; fields += "," + property.Name; var isNumStr = (property.PropertyType == typeof (double) || property.PropertyType == typeof (int)) ? "" : "'"; values += ",?" ; } fields = fields.Substring(1); values = values.Substring(1); var sql = string.Format("insert into {0}({1}) values ({2}) ", className, fields, values); OleDbDataAdapter da = new OleDbDataAdapter(); da.InsertCommand = new OleDbCommand(sql, conn); da.InsertCommand.CommandText = sql; foreach (var property in type.GetProperties()) { if (property.Name.Equals("ID")) continue; var oleType = (property.PropertyType == typeof(double) || property.PropertyType == typeof(int)) ? OleDbType.Integer : OleDbType.VarChar; da.InsertCommand.Parameters.Add(property.Name, oleType, int.MaxValue, property.Name); fields += "," + property.Name; values += ",?"; } var table = ConverterUtil.ConvertListToDataTable(entitysList); table.TableName = className; da.Update(table); return true; } catch (Exception e) { MessageBox.Show(e.Message); return false; } finally { conn.Close(); } } //這個方法是用來執行無返回結果的插入語句,如 insert select public bool ExecuteInsertSql(string sql) { try { OleDbDataAdapter da = new OleDbDataAdapter(); da.InsertCommand = new OleDbCommand(sql, conn); da.InsertCommand.CommandText = sql; conn.Open(); da.InsertCommand.ExecuteNonQuery(); conn.Close(); return true; } catch (Exception e) { MessageBox.Show(e.Message); return false; } finally { conn.Close(); } } } }