C#数据库操作通用类

基类：

using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.SQLite;
using MySql.Data.MySqlClient;

namespace DBHelper
{
    public class DataBaseHelper
    {
        private string _connStr;
        private DatabaseType _dbType;
        protected DbConnection dbSession;

        public DataBaseHelper(DbConnection session)
        {
            this.dbSession = session;
        }

        public DataBaseHelper(DbConnection session, string connStr, DatabaseType dbType)
            : this(session)
        {
            this._connStr = connStr;
            this._dbType = dbType;
        }

        private DbDataAdapter CreateDbDataAdapter(string M_str_sqlstr)
        {
            DbDataAdapter adp;
            switch (_dbType)
            {
                case DatabaseType.SqlServer:
                    adp = new SqlDataAdapter(M_str_sqlstr, dbSession as SqlConnection);
                    return adp;
                case DatabaseType.MySql:
                    adp = new MySqlDataAdapter(M_str_sqlstr, dbSession as MySqlConnection);
                    return adp;
                case DatabaseType.SQLite:
                    adp = new SQLiteDataAdapter(M_str_sqlstr, dbSession as SQLiteConnection);
                    return adp;
                case DatabaseType.OleDb:
                    adp = new OleDbDataAdapter(M_str_sqlstr, dbSession as OleDbConnection);
                    return adp;
                default:
                    throw new NotImplementedException();
            }
        }

        private DbCommand GetCommand(DbConnection conn, string M_str_sqlstr)
        {
            DbCommand command = conn.CreateCommand();
            command.CommandText = M_str_sqlstr;
            return command;
        }

        protected void OpenDataBase()
        {
            try
            {
                if (this.dbSession.State != ConnectionState.Open)
                    this.dbSession.Open();  //打开连接
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        protected void CloseDataBase()
        {
            if (this.dbSession != null)
            {
                if (this.dbSession.State == ConnectionState.Open)
                {
                    this.dbSession.Close(); //关闭连接
                }
            }
        }

        /// <summary>
        /// 增删改非查询类方法
        /// </summary>
        /// <param name="M_str_sqlstr">要执行的SQL语句</param>
        /// <param name="param">参数列表，没有参数填入null</param>
        /// <returns>返回影响行数</returns>
        public int ExecuteNonQuery(string M_str_sqlstr, params DbParameter[] param)
        {
            try
            {
                OpenDataBase();
                DbCommand cmd = GetCommand(dbSession, M_str_sqlstr);  //创建命令

                if (param != null && param.Length > 0)
                    cmd.Parameters.AddRange(param);

                return cmd.ExecuteNonQuery();               //执行命令，ExecuteNonQuery
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDataBase();
            }
        }

        /// <summary>
        /// 执行SQL语句,查询首行首列
        /// </summary>
        /// <param name="M_str_sqlstr">要执行的SQL语句</param>
        /// <param name="param">参数列表，没有参数填入null</param>
        /// <returns>返回的首行首列</returns>
        public object ExecuteScalar(string M_str_sqlstr, params DbParameter[] param)
        {
            try
            {
                this.OpenDataBase();
                DbCommand cmd = GetCommand(dbSession, M_str_sqlstr);  //创建命令

                if (param != null && param.Length > 0)
                    cmd.Parameters.AddRange(param);

                return cmd.ExecuteScalar();     //返回第一行第一列
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseDataBase();
            }
        }

        /// <summary>
        /// 查询SQL语句获取DataReader
        /// </summary>
        /// <param name="M_str_sqlstr">要执行的SQL语句</param>
        /// <param name="param">参数列表，没有参数填入null</param>
        /// <returns>查询到的DataReader（关闭该对象的时候，自动关闭连接）</returns>
        public DbDataReader ExecuteReader(string M_str_sqlstr, params DbParameter[] param)
        {
            try
            {
                this.OpenDataBase();
                DbCommand cmd = GetCommand(dbSession, M_str_sqlstr);  //创建命令

                if (param != null && param.Length > 0)
                    cmd.Parameters.AddRange(param);

                return cmd.ExecuteReader(CommandBehavior.CloseConnection);  //创建一个SqlDataReader用于读取数据
            }
            catch (Exception ex)
            {
                this.CloseDataBase();
                throw ex;
            }
        }

        /// <summary>
        /// 执行查询，返回DataTable对象
        /// </summary>
        /// <param name="M_str_sqlstr">要执行的SQL语句</param>
        /// <param name="param">参数列表，没有参数填入null</param>
        /// <returns>DataTable对象</returns>
        public DataTable ExecuteTable(string M_str_sqlstr, params DbParameter[] param)
        {
            DataTable dt = new DataTable();

            try
            {
                this.OpenDataBase();
                DbDataAdapter adp = CreateDbDataAdapter(M_str_sqlstr);

                if (param != null && param.Length > 0)
                    adp.SelectCommand.Parameters.AddRange(param);

                adp.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseDataBase();
            }
        }

        /// <summary>
        /// 执行查询，返回DataTable对象
        /// </summary>
        /// <param name="M_str_sqlstr">要执行的SQL语句</param>
        /// <param name="param">参数列表，没有参数填入null</param>
        /// <returns>DataTable对象</returns>
        public DataSet ExecuteDataSet(string M_str_sqlstr, params DbParameter[] param)
        {
            DataSet ds = new DataSet();

            try
            {
                this.OpenDataBase();
                DbDataAdapter adp = CreateDbDataAdapter(M_str_sqlstr);

                if (param != null && param.Length > 0)
                    adp.SelectCommand.Parameters.AddRange(param);

                adp.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseDataBase();
            }
        }
    }
}

继承：

    public class MySqlOperator : DataBaseHelper
    {
        /// <summary>
        /// 创建连接
        /// </summary>
        /// <param name="connStr"></param>
        public MySqlOperator(string connStr) : base(new MySqlConnection(connStr), connStr, DatabaseType.MySql) { }
    }

    public class SqlOperator : DataBaseHelper
    {
        /// <summary>
        /// 创建连接
        /// </summary>
        /// <param name="connStr"></param>
        public SqlOperator(string connStr) : base(new SqlConnection(connStr), connStr, DatabaseType.SqlServer) { }
    }

    public class OleDbOperator : DataBaseHelper
    {
        /// <summary>
        /// 创建连接
        /// </summary>
        /// <param name="connStr"></param>
        public OleDbOperator(string connStr): base(new OleDbConnection(connStr), connStr, DatabaseType.OleDb){ }
    }

    public class SQLiteOperator : DataBaseHelper
    {
        /// <summary>
        /// 创建连接
        /// </summary>
        /// <param name="connStr"></param>
        public SQLiteOperator(string connStr) : base(new SQLiteConnection(connStr), connStr, DatabaseType.SQLite) { }
    }

Demo如下：
1、数据访问层（DAL）：主要是存放对数据类的访问，即对数据库的添加、删除、修改、更新等基本操作

        /// <summary>
        /// 根据用户名和密码比对用户信息
        /// </summary>
        /// <param name="objUser">包含用户名和密码的用户对象</param>
        /// <returns>返回用户对象信息（若无用户信息则对象为null）</returns>
        public UserInfo UserLogin(UserInfo objUser)
        {
            String sql = "SELECT Name,Password FROM user where Name=@username and Password=@password";

            DbParameter[] param = new SQLiteParameter[] {
                new SQLiteParameter("@username",objUser.UserName),
                new SQLiteParameter("@password", objUser.Password)
            };

            DbDataReader objReader = sqliteHelper.ExecuteReader(sql, param);

            if (objReader.Read())
            {
                //objUser.Id = Convert.ToInt32(objReader["Id"]);
                //objUser.status = Convert.ToInt32(objReader["status"]);
            }
            else
                objUser = null;
            objReader.Close();
            return objUser;
        }

2、业务逻辑层（BLL）：对传送数据进行逻辑判断分折，并进行传送正确的值。

        public bool UserLogin(UserInfo objUser, out string messageStr)
        {
            messageStr = "";//返回界面层添加用户返回信息
            bool isSuccess = false;

            //数据验证
            if (objUser.UserName.Trim().Length != 0 && objUser.Password.Trim().Length != 0)
            {
                if (objUserDB.UserLogin(objUser) != null)//判断从传递来的username是否为空
                    isSuccess = true;
                else
                    messageStr = "<用户名>或<密码>错误！";
            }
            else
                messageStr = "<用户名>和<密码>不可为空!";

            return isSuccess;
        }

3、表现层（UI）即用户界面层

        private void btn_Confirm_Click(object sender, EventArgs e)
        {
            try
            {
                // 封装对象
                UserInfo objUser = new UserInfo
                {
                    UserName = cbo_Tester.Text.Trim(),
                    Password = txb_Password.Text.Trim()
                };

                string messageStr = "";

                if (objUserManager.UserLogin(objUser, out messageStr))
                {
                    this.DialogResult = DialogResult.OK;
                    this.Close();
                    return;
                }
                else
                {
                    lbl_Message.Text = messageStr;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("登录异常：" + ex.Message, "登录提示");
            }
        }

写的不好，欢迎指正
https://download.csdn.net/download/HelloAnzi/12038442