使用oledb对数据库进行增删改查及批量插入操作
使用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(); } } } }
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。