使用db工厂DBHelper类

使用过很多版本的dbhelper无论是自己写还是用被人的但是基本方法都是大同小异，各有千秋一个偶然的机会我发现的使用db工厂来创建操作数据库过程中所需要的各种对象，

这样有一个好处就是，进一步使得数据库与程序的分离，降低他们之间的耦合，当我们由access变更成sqlser或者oracle是仅仅只需要更改配置参数，甚至工厂类微软已经帮我们写好了，不需要明白具体的含义 只需要使用 ，然后更改配置便可以在不同数据库之间进行切换。

<connectionStrings>
    <add name="ApplicationServices"
         connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
         providerName="System.Data.SqlClient" />
    <add name="DataConnection" connectionString="Data Source=.;Initial Catalog=实验4temp;Integrated Security=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

以上是config文件中的配置字符串 这里以sqlserver参数为类

线面就是  dbhelper类了  ，此类的产生是由我参考别人的，再经过自己的理解建立起来的功能不是特别完善，但基本常用都有了，可以加入自己的理解自己的想法去进一步完善。

 

在程序中不同层之间以接口进行连接  ，降低耦合也是程序更易于维护，左右闲来无事分享下吧

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.Common;
using System.Data;
using System.Data.OleDb;
//============================================================
//http://www.cnblogs.com/mxxblog/
//联系Email:446883859@qq.com
//联系qq:446883859
//============================================================

namespace DbHelper
{
    public class SqlHelperFactory
    {
        //db工厂参数
        //Provider=Microsoft.Jet.OLEDB.4.0
        //private static string connectionString = conStr;
        //providerName="System.Data.SqlClient"
        // providerName = "System.Data.OleDb";
        /// <summary>
        /// 链接字符串从Config文件中获取name为 DataConnection
        /// </summary>
        private static string connectionString = ConfigurationManager.ConnectionStrings["DataConnection"].ConnectionString;
        /// <summary>
        /// providerName
        /// </summary>
        private static string providerName = ConfigurationManager.ConnectionStrings["DataConnection"].ProviderName;
        /// <summary>
        /// DbProviderFactory  db工厂
        /// </summary>
        private static DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory(providerName);
        /// <summary>
        /// 创建数据库链接
        /// </summary>
        /// <returns></returns>
        protected static DbConnection createDbConnection()
        {
            DbConnection dbConnection = dbProviderFactory.CreateConnection();
            dbConnection.ConnectionString = connectionString;
            return dbConnection;
        }
        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="parameterName">参数名称</param>
        /// <param name="value">参数值</param>
        /// <param name="dbtype">参数类型</param>
        /// <param name="dataSize">大小</param>
        /// <param name="parameterDirection">输入输出类型</param>
        /// <returns>返回参数DbParameter</returns>
        public static DbParameter createDbParameter(string parameterName, object value, DbType dbtype, int dataSize, ParameterDirection parameterDirection)
        {
            DbParameter dbParameter = dbProviderFactory.CreateParameter();
            dbParameter.ParameterName = parameterName;
            dbParameter.Value = value;
            dbParameter.DbType = dbtype;
            dbParameter.Size = dataSize;
            dbParameter.Direction = parameterDirection;
            return dbParameter;
        }
        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="parameterName">参数名称</param>
        /// <param name="value">参数值</param>
        /// <param name="dbtype">参数类型</param>
        /// <param name="parameterDirection">输入输出类型</param>
        /// <returns>返回参数DbParameter</returns>
        public static DbParameter createDbParameter(string parameterName, object value, DbType dbtype, ParameterDirection parameterDirection)
        {
            DbParameter dbParameter = dbProviderFactory.CreateParameter();
            dbParameter.ParameterName = parameterName;
            dbParameter.Value = value;
            dbParameter.DbType = dbtype;

            dbParameter.Direction = parameterDirection;
            return dbParameter;
        }
        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="parameterName">名称</param>
        /// <param name="value">值</param>
        /// <returns>返回参数DbParameter</returns>
        public static DbParameter createDbParameter(string parameterName, object value)
        {
            DbParameter dbParameter = dbProviderFactory.CreateParameter();
            dbParameter.ParameterName = parameterName;
            dbParameter.Value = value;
            return dbParameter;
        }
        /// <summary>
        /// createAddDbParameter 添加参数到对应cmd
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        /// <param name="dbtype"></param>
        /// <param name="dataSize"></param>
        /// <param name="parameterDirection"></param>
        protected void createAddDbParameter(DbCommand cmd, string parameterName, object value, DbType dbtype, int dataSize, ParameterDirection parameterDirection)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.ParameterName = parameterName;
            dbParameter.Value = value;
            dbParameter.DbType = dbtype;
            dbParameter.Size = dataSize;
            dbParameter.Direction = parameterDirection;
            cmd.Parameters.Add(dbParameter);

        }
        /// <summary>
        /// createAddDbParameter 添加参数到对应cmd
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        /// <param name="dbtype"></param>
        /// <param name="parameterDirection"></param>
        protected static void createAddDbParameter(DbCommand cmd, string parameterName, object value, DbType dbtype, ParameterDirection parameterDirection)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.ParameterName = parameterName;
            dbParameter.Value = value;
            dbParameter.DbType = dbtype;
            dbParameter.Direction = parameterDirection;
            cmd.Parameters.Add(dbParameter);

        }
        /// <summary>
        /// createAddDbParameter 添加参数到对应cmd
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        /// <param name="parameterDirection"></param>
        protected static void createAddDbParameter(DbCommand cmd, string parameterName, object value, ParameterDirection parameterDirection)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.ParameterName = parameterName;
            dbParameter.Value = value;
            dbParameter.Direction = parameterDirection;
            cmd.Parameters.Add(dbParameter);

        }
        /// <summary>
        /// 向指定cmd添加参数
        /// </summary>
        /// <param name="cmd">cmd</param>
        /// <param name="parameterName">参数名称</param>
        /// <param name="value">参数值</param>
        protected static void createAddDbParameter(DbCommand cmd, string parameterName, object value)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.ParameterName = parameterName;
            dbParameter.Value = value;
            cmd.Parameters.Add(dbParameter);

        }
        /// <summary>
        /// RunExecuteNonQuery
        /// </summary>
        /// <param name="cmdTxt">要执行的sql语句</param>
        /// <param name="paras">值集合</param>
        /// <returns>返回受影响的行数</returns>
        public static int RunExecuteNonQuery(string cmdTxt, params  IDbDataParameter[] paras)
        {
            using (IDbConnection con = createDbConnection())
            {
                con.Open();
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandText = cmdTxt;
                    foreach (var item in paras)
                    {
                        cmd.Parameters.Add(item);
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
            // return 0;
        }
        /// <summary>
        /// RunExecuteNonQueryByPro 执行对应的存储过程
        /// </summary>
        /// <param name="proName">存储过程名称</param>
        /// <param name="paras">参数集合</param>
        /// <returns>返回受影响的行数</returns>
        public static int RunExecuteNonQueryByPro(string proName, params  IDbDataParameter[] paras)
        {
            using (IDbConnection con = createDbConnection())
            {
                con.Open();
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = proName;
                    foreach (var item in paras)
                    {
                        cmd.Parameters.Add(item);
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
            // return 0;
        }
        /// <summary>
        /// RunExecuteScalar
        /// </summary>
        /// <param name="cmdTxt">sql语句</param>
        /// <param name="paras">参数集合</param>
        /// <returns>返回首行首列</returns>
        public static object RunExecuteScalar(string cmdTxt, params IDbDataParameter[] paras)
        {
            using (IDbConnection con = createDbConnection())
            {
                con.Open();
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandText = cmdTxt;
                    foreach (var item in paras)
                    {
                        cmd.Parameters.Add(item);
                    }
                    return cmd.ExecuteScalar();
                }
            }
            // return 0;
        }
        /// <summary>
        /// RunExecuteScalarByPro
        /// </summary>
        /// <param name="proName">存储过程名称</param>
        /// <param name="paras">参数集合</param>
        /// <returns>返回首行首列</returns>
        public static object RunExecuteScalarByPro(string proName, params IDbDataParameter[] paras)
        {
            using (IDbConnection con = createDbConnection())
            {
                con.Open();
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = proName;
                    foreach (var item in paras)
                    {
                        cmd.Parameters.Add(item);
                    }
                    return cmd.ExecuteScalar();
                }
            }
            // return 0;
        }
        /// <summary>
        /// RunExecuteDataReader
        /// </summary>
        /// <param name="cmdTxt">要执行的sql语句</param>
        /// <param name="paras">参数集合</param>
        /// <returns>返回数据读取器</returns>
        public static IDataReader RunExecuteDataReader(string cmdTxt, params IDbDataParameter[] paras)
        {
            IDbConnection con = createDbConnection();
            con.Open();
            using (IDbCommand cmd = con.CreateCommand())
            {
                cmd.Connection = con;
                cmd.CommandText = cmdTxt;
                foreach (var item in paras)
                {
                    cmd.Parameters.Add(item);
                }
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }

        }
        /// <summary>
        /// RunExecuteDataTable
        /// </summary>
        /// <param name="cmdTxt">执行的sql语句</param>
        /// <param name="paras">参数集合</param>
        /// <returns>DataSet通过适配器返回DataSet</returns>
        public static DataSet RunExecuteDataTable(string cmdTxt, params IDbDataParameter[] paras)
        {
            DataSet ds = new DataSet();
            using (IDbConnection con = createDbConnection())
            {
                con.Open();
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandText = cmdTxt;
                    foreach (var item in paras)
                    {
                        cmd.Parameters.Add(item);
                    }
                    IDbDataAdapter sda = dbProviderFactory.CreateDataAdapter();
                    sda.SelectCommand = cmd;
                    sda.Fill(ds);
                    return ds;
                }
            }
        }
    }
}