通用数据库操作辅助类DbHelper

http://www.cnblogs.com/yelaiju/archive/2010/09/22/1832916.html

 

使用方式
DbHelper db;
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "SQLite数据文件(*.db3;*.db;*.sqlite)|*.db3;*.db;*.sqlite";
if (ofd.ShowDialog() == DialogResult.OK)
{
    txtDataSource.Text = ofd.FileName;
    db = new DbHelper("data source=" + txtDataSource.Text, DbProvider.Sqlite);
}
db.ReExNum(txtSql.Text); 

接口
using System.Data;
using System.Collections.Generic;
using System.Data.Common;

/// <summary>
/// 该类用于对数据库进行操作
/// Design by 火地晋
/// </summary>
namespace System.Data
{
    public interface IDbObject
    {
        /// <summary>
        /// 定义一个DataReader的列表，已备检索
        /// </summary>
        List<DbDataReader> DataReaderList { get; set; }
        /// <summary>
        /// 
        /// </summary>
        DbConnection Connection { get; set; }
        string ConnectionString { get; set; }
        DbProvider DbProviderType { get; set; }
        
        /// <summary>
        /// 返回执行操作成功的数目，使用注意，如果是存储过程，必须在存储过程后加上：select @@ROWCOUNT
        /// </summary>
        /// <param name="strSql">存储过程名</param>
        /// <param name="parameters">参数组</param>
        /// <returns></returns>
        int ReExNum(string strSql, params DbParameter[] parameters);
        /// <summary>
        /// 返回结果的存储过程
        /// </summary>
        /// <param name="strSql">任何SQL语句</param>
        /// <param name="parameters">参数值</param>
        /// <returns></returns>
        DbDataReader ReSelectdr(string strSql, params DbParameter[] parameters);
        /// <summary>
        /// 返回dateSet
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="parameters"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        DataSet ReSelectds(string strSql, string tableName, params DbParameter[] parameters);
        DataTable ReSelectdtb(string strSql, params DbParameter[] parameters);
        /// <summary>
        /// 通過存儲過程及自定義參數組查詢返回SqlDataAdapter對象
        /// </summary>
        DbDataAdapter ReSelectdat(string strSql, params DbParameter[] parameters);

        void ExSQL(string strSql, params DbParameter[] parameters);
        /// <summary>
        ///     執行SQL查詢語句,返回記錄條數
        /// </summary>
        /// <param name="strSql">Select語句(在select语句中,使用Count(*)函数)</param>
        /// <returns>返回查詢到之記錄條數</returns>
        int ReSelectNum(string strSql, params DbParameter[] parameters);
        /// <summary>
        /// 使用SqlDataAdapter返回指定范围的数据
        /// </summary>
        /// <param name="strSql">存储过程名</param>
        /// <param name="parameters">参数名</param>
        /// <param name="start">起始行</param>
        /// <param name="maxRecord">记录数</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        DataSet ReSelectds(string strSql, DbParameter[] parameters, int start, int maxRecord, string tableName);
        /// <summary>
        /// 返回执行操作成功的数目，不关闭连接
        /// </summary>
        /// <param name="strSql">执行的查询语句或存储过程</param>
        /// <param name="parameters">参数组</param>
        /// <returns></returns>
        int ReExNumNoClose(string strSql, params DbParameter[] parameters);
        /// <summary>
        /// 返回执行操作成功的数目，不关闭连接，并返回出现的错误信息。
        /// </summary>
        /// <param name="strSql">存储过程名</param>
        /// <param name="parameters">参数组</param>
        /// <returns></returns>
        int ReExNumNoClose(string strSql, out string error, params DbParameter[] parameters);

        /// <summary>
        /// 返回执行操作成功的数目，并返回发生的错误信息
        /// </summary>
        /// <param name="strSql">存储过程名</param>
        /// <param name="parameters">参数组</param>
        /// <returns></returns>
        int ReExNum(string strSql, out string error, params DbParameter[] parameters);

        /// <summary>
        /// 返回执行操作结果的信息，如果返回为空则表示没错误，否则返回错误的信息。
        /// </summary>
        /// <param name="strSql">存储过程名</param>
        /// <param name="parameters">参数组</param>
        /// <returns></returns>
        string ReExStr(string strSql, params DbParameter[] parameters);
        /// <summary>
        /// 如果数据库连接已关闭,则打开
        /// </summary>
        /// <returns></returns>
        bool OpenConnection();
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        void CloseConnection();
    }
    public enum DbProvider
    {
        Sql=0,
        Sqlite=1,
        OleDb=2,
        Oracle=3,
        MySql=4
    }
}
 
类
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;

namespace System.Data
{
    public class DbHelper : IDbObject
    {
        #region 属性
        public List<DbDataReader> DataReaderList { get; set; }
        public DbConnection Connection { get; set; }
        public string ConnectionString { get; set; }
        public DbProvider DbProviderType { get; set; }
        private DbProviderFactory dbFactory;
        #endregion

        #region 构造函数
        public DbHelper()
        {
            DbHelperInstance(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"], DbProvider.Sql);
        }
        public DbHelper(string connectionString)
        {
            DbHelperInstance(connectionString, DbProvider.Sql);
        }
        public DbHelper(DbProvider dbProviderType)
        {
            DbHelperInstance(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"], dbProviderType);
        }
        public DbHelper(string connectionString, DbProvider dbProviderType)
        {
            DbHelperInstance(connectionString, dbProviderType);
        }
        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="dbProviderType"></param>
        public void DbHelperInstance(string connectionString, DbProvider dbProviderType)
        {
            this.DbProviderType = dbProviderType;
            this.ConnectionString = connectionString;
            DataReaderList = new List<DbDataReader>();
            CreateFactory();
            this.Connection = this.dbFactory.CreateConnection();
            this.Connection.ConnectionString = this.ConnectionString;
        }
        #endregion
        /// <summary>
        /// 创建数据操作工厂
        /// </summary>
        private void CreateFactory()
        {
            switch (DbProviderType)
            {
                case DbProvider.Sql:
                    this.dbFactory = System.Data.SqlClient.SqlClientFactory.Instance;
                    break;
                case DbProvider.Sqlite:
                    this.dbFactory = System.Data.SQLite.SQLiteFactory.Instance;
                    break;
                case DbProvider.OleDb:
                    this.dbFactory = System.Data.OleDb.OleDbFactory.Instance;
                    break;
                case DbProvider.Oracle:
                    this.dbFactory = System.Data.OracleClient.OracleClientFactory.Instance;
                    break;
                case DbProvider.MySql:
                    this.dbFactory = MySql.Data.MySqlClient.MySqlClientFactory.Instance;
                    break;
            }
        }
        /// <summary>
        /// 创建操作对象
        /// </summary>
        /// <param name="procNameOrExText">如果包含@,则采用CommandType.Text</param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private DbCommand BuilderQueryCommand(string procNameOrExText, params DbParameter[] parameters)
        {
            if (parameters == null || parameters.Length == 0)
            {
                DbCommand command = this.dbFactory.CreateCommand();
                command.CommandText = procNameOrExText;
                command.Connection = this.Connection;
                return command;
            }
            if (procNameOrExText.IndexOf('@') > 0)//存储过程
            {
                return BuilderQueryCommandText(procNameOrExText, parameters);
            }
            else
            {
                return BuilderQueryCommandStorPro(procNameOrExText, parameters);
            }
        }

        /// <summary>
        /// 根据存储过程名称和参数生成对应的SQL命令对象
        /// </summary>
        /// <param name="strSql">存储过程名或者</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns></returns>
        private DbCommand BuilderQueryCommandStorPro(string strSql, params DbParameter[] parameters)
        {
            DbCommand command = this.dbFactory.CreateCommand();
            command.CommandText = strSql;
            command.CommandType = CommandType.StoredProcedure;
            command.Connection = this.Connection;
            if (parameters != null)
            {
                foreach (DbParameter p in parameters)
                {
                    command.Parameters.Add(p);
                }
            }
            return command;
        }
        private DbCommand BuilderQueryCommandText(string strSql, params DbParameter[] parameters)
        {
            DbCommand command = this.dbFactory.CreateCommand();
            command.CommandText = strSql;
            command.Connection = this.Connection;
            if (parameters != null)
            {
                foreach (DbParameter p in parameters)
                {
                    command.Parameters.Add(p);
                }
            }
            return command;
        }
        public DbParameter CreateDbParameter(string parameterName)
        {
            return CreateDbParameter(parameterName, DBNull.Value, DbType.Object, 0, ParameterDirection.Input);
        }
        public DbParameter CreateDbParameter(string parameterName, object value)
        {
            return CreateDbParameter(parameterName, value, DbType.Object, 0, ParameterDirection.Input);
        }
        public DbParameter CreateDbParameter(string parameterName, object value, DbType dbType)
        {
            return CreateDbParameter(parameterName,value,dbType,0,ParameterDirection.Input);
        }
        public DbParameter CreateDbParameter(string parameterName, object value, DbType dbType, int size)
        {
            return CreateDbParameter(parameterName,value,dbType,size,ParameterDirection.Input);
        }
        public DbParameter CreateDbParameter(string parameterName, object value, DbType dbType, int size, ParameterDirection parameterDirection)
        {
            DbParameter pat = this.dbFactory.CreateParameter();
            pat.ParameterName = parameterName;
            pat.Value = value;
            pat.DbType = dbType;
            pat.Size = size;
            pat.Direction = parameterDirection;
            return pat;
        }
        /// <summary>
        /// 返回执行操作成功的数目，使用注意，如果是存储过程，必须在存储过程后加上：select @@ROWCOUNT
        /// </summary>
        /// <param name="strSql">存储过程名</param>
        /// <param name="parameters">参数组</param>
        /// <returns></returns>
        public int ReExNum(string strSql, params DbParameter[] parameters)
        {
            int effect = 0;
            if (!OpenConnection()) return -1;
            DbTransaction trans = Connection.BeginTransaction();
            try
            {
                DbCommand cmd = BuilderQueryCommand(strSql, parameters);
                cmd.Transaction = trans;
                //根据是否为存储过程来执行不同的处理
                if (cmd.CommandType == CommandType.StoredProcedure)
                {
                    object result = cmd.ExecuteScalar();
                    effect = result == null ? -1 : Convert.ToInt16(result);
                }
                else
                {
                    effect = cmd.ExecuteNonQuery();
                }
                trans.Commit();
                return effect;
            }
            catch
            {
                trans.Rollback();
                Connection.Close();
                return -1;
            }
            finally
            {
                Connection.Close();
            }
        }
        /// <summary>
        /// 返回结果的存储过程
        /// </summary>
        /// <param name="strSql">任何SQL语句</param>
        /// <param name="parameters">参数值</param>
        /// <returns></returns>
        public DbDataReader ReSelectdr(string strSql, params DbParameter[] parameters)
        {
            try
            {
                DbDataReader reader;
                if (!OpenConnection()) return null;
                DbCommand cmd = BuilderQueryCommand(strSql, parameters);
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//在dr关闭之后,就不需要进行cnn的关闭操作了
                DataReaderList.Add(reader);//添加进dr列表,已备检索
                return reader;
            }
            catch
            {
                return null;
            }
        }
        /// <summary>
        /// 返回dateSet
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="parameters"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataSet ReSelectds(string strSql, string tableName, params DbParameter[] parameters)
        {
            try
            {
                DataSet ds = new DataSet();
                if (!OpenConnection()) return null;
                DbDataAdapter myDa = this.dbFactory.CreateDataAdapter();
                myDa.SelectCommand = BuilderQueryCommand(strSql, parameters);
                myDa.Fill(ds, tableName);
                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {
                Connection.Close();
            }
        }
        public DataTable ReSelectdtb(string strSql, params DbParameter[] parameters)
        {
            try
            {
                DataTable dt = new DataTable();
                if (!OpenConnection()) return null;
                DbDataAdapter myDa = this.dbFactory.CreateDataAdapter();
                myDa.SelectCommand = BuilderQueryCommand(strSql, parameters);
                myDa.Fill(dt);
                return dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                Connection.Close();
            }
        }
        /// <summary>
        /// 通過存儲過程及自定義參數組查詢返回SqlDataAdapter對象
        /// </summary>
        public DbDataAdapter ReSelectdat(string strSql, params DbParameter[] parameters)
        {
            if (!OpenConnection()) return null;
            try
            {
                DbCommand cmd = BuilderQueryCommand(strSql, parameters);
                DbDataAdapter myDa = this.dbFactory.CreateDataAdapter();
                myDa.SelectCommand = cmd;
                return myDa;
            }
            catch
            {
                Connection.Close();
                return null;
            }
        }

        public void ExSQL(string strSql, params DbParameter[] parameters)
        {
            if (!OpenConnection()) return;
            DbTransaction trans = Connection.BeginTransaction();
            try
            {
                DbCommand cmd = BuilderQueryCommand(strSql, parameters);
                cmd.Transaction = trans;
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
                Connection.Close();
                return;
            }
            finally
            {
                Connection.Close();
            }
        }
        /// <summary>
        ///  執行SQL查詢語句,返回記錄條數
        /// </summary>
        /// <param name="strSql">Select語句(在select语句中,使用Count(*)函数)</param>
        /// <returns>返回查詢到之記錄條數</returns>
        public int ReSelectNum(string strSql, params DbParameter[] parameters)
        {
            int effect = 0;
            try
            {
                DbDataReader dr = ReSelectdr(strSql, parameters);
                if (dr.Read())
                {
                    effect = Convert.ToInt32(dr.GetValue(0));
                }
                return effect;
            }
            catch
            {
                return effect;

            }
        }
        /// <summary>
        /// 使用SqlDataAdapter返回指定范围的数据
        /// </summary>
        /// <param name="strSql">存储过程名</param>
        /// <param name="parameters">参数名</param>
        /// <param name="start">起始行</param>
        /// <param name="maxRecord">记录数</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public DataSet ReSelectds(string strSql, DbParameter[] parameters, int start, int maxRecord, string tableName)
        {
            try
            {
                DataSet ds = new DataSet();
                OpenConnection();
                DbDataAdapter myDa = this.dbFactory.CreateDataAdapter();
                myDa.SelectCommand = BuilderQueryCommand(strSql, parameters);
                myDa.Fill(ds, start, maxRecord, tableName);
                return ds;
            }
            catch
            {
                Connection.Close();
                return null;
            }
            finally
            {
                Connection.Close();
            }
        }
        /// <summary>
        /// 返回执行操作成功的数目，不关闭连接
        /// </summary>
        /// <param name="strSql">执行的查询语句或存储过程</param>
        /// <param name="parameters">参数组</param>
        /// <returns></returns>
        public int ReExNumNoClose(string strSql, params DbParameter[] parameters)
        {
            int effect = 0;
            if (!OpenConnection()) return -1;
            DbTransaction trans = Connection.BeginTransaction();
            try
            {
                DbCommand cmd = BuilderQueryCommand(strSql, parameters);
                cmd.Transaction = trans;
                //根据是否为存储过程来执行不同的处理
                if (cmd.CommandType == CommandType.StoredProcedure)
                {
                    object result = cmd.ExecuteScalar();
                    effect = result == null ? -1 : Convert.ToInt16(result);
                }
                else
                {
                    effect = cmd.ExecuteNonQuery();
                }
                trans.Commit();
                return effect;
            }
            catch
            {
                trans.Rollback();
                return effect;
            }
        }
        /// <summary>
        /// 返回执行操作成功的数目，不关闭连接，并返回出现的错误信息。
        /// </summary>
        /// <param name="strSql">存储过程名</param>
        /// <param name="parameters">参数组</param>
        /// <returns></returns>
        public int ReExNumNoClose(string strSql, out string error, params DbParameter[] parameters)
        {
            int effect = 0;
            error = "";
            if (!OpenConnection()) return -1;
            DbTransaction trans = Connection.BeginTransaction();
            try
            {
                DbCommand cmd = BuilderQueryCommand(strSql, parameters);
                cmd.Transaction = trans;
                if (cmd.CommandType == CommandType.StoredProcedure)
                {
                    object result = cmd.ExecuteScalar();
                    effect = result == null ? -1 : Convert.ToInt16(result);
                }
                else
                {
                    effect = cmd.ExecuteNonQuery();
                }
                effect = cmd.ExecuteNonQuery();
                trans.Commit();
                return effect;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                error = ex.Message;
                return effect;
            }
        }

        /// <summary>
        /// 返回执行操作成功的数目，并返回发生的错误信息
        /// </summary>
        /// <param name="strSql">存储过程名</param>
        /// <param name="parameters">参数组</param>
        /// <returns></returns>
        public int ReExNum(string strSql, out string error, params DbParameter[] parameters)
        {
            int effect = 0;
            error = "";
            if (!OpenConnection()) return -1;
            DbTransaction trans = Connection.BeginTransaction();
            try
            {
                DbCommand cmd = BuilderQueryCommand(strSql, parameters);
                cmd.Transaction = trans;
                if (cmd.CommandType == CommandType.StoredProcedure)
                {
                    object result = cmd.ExecuteScalar();
                    effect = result == null ? -1 : Convert.ToInt16(result);
                }
                else
                {
                    effect = cmd.ExecuteNonQuery();
                }
                trans.Commit();
                return effect;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                error = ex.Message;
                return effect;
            }
            finally
            {
                Connection.Close();
            }
        }

        /// <summary>
        /// 返回执行操作结果的信息，如果返回为空则表示没错误，否则返回错误的信息。
        /// </summary>
        /// <param name="strSql">存储过程名</param>
        /// <param name="parameters">参数组</param>
        /// <returns></returns>
        public string ReExStr(string strSql, params DbParameter[] parameters)
        {
            string error = string.Empty;
            int effect = 0;
            if (!OpenConnection()) return null;
            DbTransaction trans = Connection.BeginTransaction();
            try
            {
                DbCommand cmd = BuilderQueryCommand(strSql, parameters);
                cmd.Transaction = trans;
                effect = cmd.ExecuteNonQuery();
                trans.Commit();
                if (effect == 0)
                {
                    error = "操作成功记录数为0，请检查意外的错误。" + " sql语句：" + strSql;
                }
                else
                {
                    error = "";
                }
            }
            catch (Exception ex)
            {
                trans.Rollback();
                error = "sql语句：" + strSql + " 错误信息：" + ex.Message;
            }
            finally
            {
                Connection.Close();
            }
            return error;
        }
        /// <summary>
        /// 如果数据库连接已关闭,则打开
        /// </summary>
        /// <returns></returns>
        public bool OpenConnection()
        {
            if (Connection.State == ConnectionState.Closed)
            {
                try
                {
                    Connection.Open();
                }
                catch
                {
                    return false;
                }
            }
            return true;
        }
        public void CloseConnection()
        {
            Connection.Close();
        }
    }
}