使用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<T> ReieveList<T>() 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<T>(dt);

                return list;
            }
            catch (Exception e)
            {

                MessageBox.Show(e.Message);
                return null;
            }

        }

        //同上，根据条件，查询，返回list实体类列表

        public List<T> ReieveList<T>(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<T>(dt);

                return list;
            }
            catch (Exception e)
            {

                MessageBox.Show(e.Message);
                return null;
            }

        }

        //插入一条数据
        public bool Insert<T>(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>(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<T>(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<T>(List<T> 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();
            }

        }

    }
  

}