超实用的DbFactory之 Dbhelp

#region Copyright
/*
 * Copyright (C) 2008 WorkFlow 版權所有

 

 *
 * 檔案名:DbHelp.cs
 * 檔功能描述:數據裏處理類
 *
 * 版本：.0
 * 創建標識：苑小偉 2009-10-21
 */
#endregion
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Configuration;
using WorkFlow_Model;
using WorkFlow_Common;
using System.Web;
using System.Data.OracleClient;

namespace WorkFlow_Dal
{
    public class DbHelp
    {
        #region 私有字段及公有屬性

 

        ///
        /// 資料庫類型字符串
        ///
        private static string dbProviderName = ConfigurationManager.AppSettings["DbHelperProvider"];
        ///
        /// 資料庫連接字符串

        ///
        //private static string dbConnectionString = ConfigurationManager.AppSettings["DbHelperConnectionString"];
        private string dbConnectionString;
        ///
        /// 創建連接
        ///
        private DbConnection connection;
        ///
        /// 創建連接屬性

        public DbHelp()
        {
            dbConnectionString = GetDataBaseConnectionString();
        }

        ///
        /// 獲取數據庫連接字符串

        ///
        ///
        private string GetDataBaseConnectionString()
        {
            if (HttpContext.Current == null || HttpContext.Current.Session == null || HttpContext.Current.Session["DataBaseConnectionString"] == null || string.IsNullOrEmpty(HttpContext.Current.Session["DataBaseConnectionString"].ToString()))
                return ConfigurationManager.AppSettings["DbHelperConnectionString"];
            else
                return ConfigurationManager.AppSettings[HttpContext.Current.Session["DataBaseConnectionString"].ToString()];
        }

        ///
        public DbConnection Connection
        {
            get
            {
                DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelp.dbProviderName);
                connection = dbfactory.CreateConnection();
                connection.ConnectionString = this.dbConnectionString;
                if (connection == null)
                    connection.Open();
                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                if (connection.State == ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }
        #endregion
        #region 私有方法
        ///
        /// 將DbParameter參數集合(參數值)分配給DbCommand命令.
        /// 這個方法將給任何一個參數分配DBNull.Value;
        /// 該操作將阻止預設值的使用.
        ///
        /// 命令名
        /// DbParameters陣列
        private void AttachParameters(DbCommand command, IList cmdParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (cmdParameters != null)
            {
                foreach (DbParameter parameter in cmdParameters)
                {
                    if (parameter != null)
                    {
                        // 檢查未分配值的輸出參數,將其分配以DBNull.Value.
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        command.Parameters.Add(parameter);
                    }
                }
            }
        }
        // private DbTransaction CreateTransaction()
        #endregion
        #region 公有方法
        ///
        /// 創建DBParameter
        ///
        /// 欄位名稱
        /// 欄位類型
        /// 欄位大小
        /// IO類型
        /// 欄位值
        /// 創建了一個欄位Parameter
        public DbParameter CreateDbParameter(string paraName, DbType dbtype, int size, ParameterDirection direction, object paraValue)
        {
            string before_para = ":";
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelp.dbProviderName);
            if (DbHelp.dbProviderName == "System.Data.SqlClient")
            {
                before_para = "@";
            }
            DbParameter para = dbfactory.CreateParameter();
            para.ParameterName = before_para + paraName;
            para.DbType = dbtype;
            para.Size = size;
            para.Direction = direction;
            if (!(direction == ParameterDirection.Output && paraValue == null))
                para.Value = paraValue;
            return para;
        }
        ///
        /// 創建DBParameter
        ///
        /// 欄位名稱
        /// 欄位類型
        /// IO類型
        /// 欄位值
        /// 創建了一個欄位Parameter
        public DbParameter CreateDbParameter(string paraName, DbType dbtype, ParameterDirection direction, object paraValue)
        {
            string before_para = ":";
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelp.dbProviderName);
            if (DbHelp.dbProviderName == "System.Data.SqlClient")
            {
                before_para = "@";
            }
            DbParameter para = dbfactory.CreateParameter();
            para.ParameterName = before_para + paraName;
            para.DbType = dbtype;
            para.Direction = direction;
            if (!(direction == ParameterDirection.Output && paraValue == null))
                para.Value = paraValue;
            return para;
        }
        public DbParameter CreateDbParameterPro(string paraName, DbType dbtype, ParameterDirection direction, object paraValue)
        {

            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelp.dbProviderName);
            DbParameter para = dbfactory.CreateParameter();
            para.ParameterName = paraName;
            para.DbType = dbtype;
            para.Direction = direction;
            if (!(direction == ParameterDirection.Output && paraValue == null))
                para.Value = paraValue;
            return para;
        }
        public DbParameter CreateDbParameterPro(string paraName, DbType dbtype, int size, ParameterDirection direction, object paraValue)
        {

            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelp.dbProviderName);
            DbParameter para = dbfactory.CreateParameter();
            para.ParameterName = paraName;
            para.DbType = dbtype;
            para.Size = size;
            para.Direction = direction;
            if (!(direction == ParameterDirection.Output && paraValue == null))
                para.Value = paraValue;
            return para;
        }

        ///
        /// 執行SQL語句或存儲過程

        ///
        /// Sql語句或存儲過程名稱
        /// 執行類型
        /// 返回結果
        public int ExecuteCommand(string cmdtext, CommandType cmdtype)
        {
            DbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = cmdtext;
            cmd.CommandType = cmdtype;
            int result = 0;
            result = cmd.ExecuteNonQuery();
            cmd.Connection.Close();
            cmd.Dispose();
            return result;
        }
        ///
        /// 執行帶參數的SQL語句或存儲過程

        ///
        /// Sql語句或存儲過程名稱
        /// 執行類型
        /// 參數列表
        /// 返回結果
        public int ExecuteCommand(string cmdtext, CommandType cmdtype, IList cmdParameters)
        {
            try
            {
                DbCommand cmd = Connection.CreateCommand();
                cmd.CommandText = cmdtext;
                cmd.CommandType = cmdtype;
                AttachParameters(cmd, cmdParameters);
                int result = 0;
                result = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmdParameters.Clear();
                cmd.Connection.Close();
                cmd.Dispose();
                return result;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        public int ExecuteOracleCommand(string cmdtext, CommandType cmdtype, IList cmdParameters)
        {
            try
            {
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = cmdtext;
                cmd.CommandType = cmdtype;
                AttachParameters(cmd, cmdParameters);
                int result = 0;
                result = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmdParameters.Clear();
                cmd.Connection.Close();
                cmd.Dispose();
                return result;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        ///
        /// 執行不帶參數的一個欄位的Sql語句或存儲過程

        ///
        /// Sql語句或存儲過程
        /// 執行類型
        /// 返回結果int
        public int ExecuteIntScalar(string cmdtext, CommandType cmdtype)
        {
            DbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = cmdtext;
            cmd.CommandType = cmdtype;
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            cmd.Connection.Close();
            cmd.Dispose();
            return result;
        }
        ///
        /// 執行帶參數的一個欄位的Sql語句或存儲過程

        ///
        /// Sql語句或存儲過程
        /// 執行類型
        /// 參數列表
        /// 返回結果int
        public int ExecuteIntScalar(string cmdtext, CommandType cmdtype, IList cmdParameters)
        {
            DbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = cmdtext;
            cmd.CommandType = cmdtype;
            AttachParameters(cmd, cmdParameters);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            cmd.Parameters.Clear();
            cmdParameters.Clear();
            cmd.Connection.Close();
            cmd.Dispose();
            return result;
        }
        ///
        /// 執行不帶參數的一個欄位的Sql語句或存儲過程

        ///
        /// Sql語句或存儲過程
        /// 執行類型
        /// 返回結果string
        public string ExecuteStringScalar(string cmdtext, CommandType cmdtype)
        {
            DbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = cmdtext;
            cmd.CommandType = cmdtype;
            string result = cmd.ExecuteScalar().ToString();
            cmd.Connection.Close();
            cmd.Dispose();
            return result;
        }
        ///
        /// 執行帶參數的一個欄位的Sql語句或存儲過程

        ///
        /// Sql語句或存儲過程
        /// 執行類型
        /// 參數列表
        /// 返回結果string
        public string ExecuteStringScalar(string cmdtext, CommandType cmdtype, IList cmdParameters)
        {
            DbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = cmdtext;
            cmd.CommandType = cmdtype;
            AttachParameters(cmd, cmdParameters);
            object execsql;
            string result;
            result = "";
            execsql = cmd.ExecuteScalar();
            if (execsql != null)
                result = execsql.ToString();
            cmd.Parameters.Clear();
            cmdParameters.Clear();
            cmd.Connection.Close();
            cmd.Dispose();
            return result;
        }
        ///
        /// 獲得Table結構的Reader數據列表（不帶參數）
        ///
        /// Sql語句
        /// 類型
        /// 返回Reader列表
        public DbDataReader ExecuteDataReader(string cmdtext, CommandType cmdtype)
        {
            DbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = cmdtext;
            cmd.CommandType = cmdtype;
            DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            cmd.Dispose();
            return reader;
        }
        ///
        /// 獲得Table結構的Reader數據列表（帶參數）

        ///
        /// Sql語句
        /// 類型
        ///
        /// 返回Reader列表
        public DbDataReader ExecuteDataReader(string cmdtext, CommandType cmdtype, IList cmdParameters)
        {
            DbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = cmdtext;
            cmd.CommandType = cmdtype;
            AttachParameters(cmd, cmdParameters);
            DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            cmdParameters.Clear();
            cmd.Dispose();
            return reader;
        }
        ///
        /// 根據Sql語句得到Table(No Parameters)
        ///
        /// Sql語句
        /// 執行類型
        /// 返回Table
        public DataTable ExecuteDataTable(string cmdtext, CommandType cmdtype)
        {
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelp.dbProviderName);
            DbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = cmdtext;
            cmd.CommandType = cmdtype;
            DbDataAdapter da = dbfactory.CreateDataAdapter();
            da.SelectCommand = cmd;
            DataTable dt = new DataTable("MyTable");
            da.Fill(dt);
            cmd.Connection.Close();
            cmd.Dispose();
            return dt;
        }
        ///
        /// 根據Sql語句得到Table(Have Parameters)
        ///
        /// Sql語句
        /// 執行類型
        /// 參數列表
        /// 返回Table
        public DataTable ExecuteDataTable(string cmdtext, CommandType cmdtype, IList cmdParameters)
        {
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelp.dbProviderName);
            DbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = cmdtext;
            cmd.CommandType = cmdtype;
            AttachParameters(cmd, cmdParameters);
            DbDataAdapter da = dbfactory.CreateDataAdapter();
            da.SelectCommand = cmd;
            DataTable dt = new DataTable("MyTable");
            da.Fill(dt);
            cmd.Parameters.Clear();
            cmdParameters.Clear();
            cmd.Connection.Close();
            cmd.Dispose();
            return dt;
        }
        ///
        /// 執行事務(不帶參數)
        ///
        /// Sql語句列表
        /// 執行結果
        public bool ExecuteTransaction(Dictionary cmdtext_parameterlist)
        {
            DbCommand cmd = Connection.CreateCommand();
            DbTransaction dtrans = null;
            try
            {
                dtrans = cmd.Connection.BeginTransaction();
                cmd.Transaction = dtrans;
                foreach (KeyValuePair item in cmdtext_parameterlist)
                {
                    cmd.CommandText = item.Value;
                    cmd.Parameters.Clear();
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                dtrans.Commit();
                cmdtext_parameterlist.Clear();
                cmd.Connection.Close();
                cmd.Dispose();
                return true;
            }
            catch (Exception e)
            {
                dtrans.Rollback();
                return false;
            }

        }
        ///
        /// 執行事務(帶參數)
        ///
        /// 字典
        /// 執行結果
        public bool ExecuteTransaction(Dictionary, string> cmdtext_parameterlist)
        {
            DbCommand cmd = Connection.CreateCommand();
            DbTransaction dtrans = null;
            try
            {
                dtrans = cmd.Connection.BeginTransaction();
                cmd.Transaction = dtrans;
                foreach (KeyValuePair, string> item in cmdtext_parameterlist)
                {
                    cmd.CommandText = item.Value;
                    cmd.Parameters.Clear();
                    cmd.CommandType = CommandType.Text;
                    AttachParameters(cmd, item.Key);
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                dtrans.Commit();
                cmdtext_parameterlist.Clear();
                cmd.Connection.Close();
                cmd.Dispose();
                return true;
            }
            catch (Exception e)
            {
                dtrans.Rollback();
                return false;
            }
        }
        ///
        /// 執行事務(帶參數)
        ///
        /// 字典
        ///
        public bool ExecuteTransaction(Dictionary> cmdtext_parameterlist)
        {
            DbCommand cmd = Connection.CreateCommand();
            DbTransaction dtrans = null;
            try
            {
                dtrans = cmd.Connection.BeginTransaction();
                cmd.Transaction = dtrans;
                foreach (KeyValuePair> item in cmdtext_parameterlist)
                {
                    cmd.CommandText = item.Key;
                    cmd.Parameters.Clear();
                    cmd.CommandType = CommandType.Text;
                    AttachParameters(cmd, item.Value);
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();

                }
                dtrans.Commit();
                cmdtext_parameterlist.Clear();
                cmd.Connection.Close();
                cmd.Dispose();
                return true;
            }
            catch (Exception e)
            {
                dtrans.Rollback();
                return false;
            }
        }

        public bool ExecuteTransaction(Dictionary cmdtext_paralist)
        {
            DbCommand cmd = Connection.CreateCommand();
            DbTransaction dtrans = null;
            try
            {
                dtrans = cmd.Connection.BeginTransaction();
                cmd.Transaction = dtrans;
                foreach (KeyValuePair item in cmdtext_paralist)
                {
                    cmd.CommandText = item.Value.Strsql;
                    cmd.Parameters.Clear();
                    if (item.Value.Commandtype == 0)
                        cmd.CommandType = CommandType.Text;
                    else
                        cmd.CommandType = item.Value.Commandtype;
                    AttachParameters(cmd, item.Value.Sqlparameters);
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                dtrans.Commit();
                cmdtext_paralist.Clear();
                cmd.Connection.Close();
                cmd.Dispose();
                return true;
            }
            catch (Exception e)
            {
                dtrans.Rollback();
                return false;
            }
        }
        #endregion

    }
}

使用到的实体

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;

namespace WorkFlow_Model
{
    public class SqlAndParameters
    {
        ///
        /// 需要執行的SQl語句
        ///
        private string strsql;
        ///
        /// 該Sql語句帶的參數
        ///
        private IList sqlparameters;

        public string Strsql
        {
            get { return strsql; }
            set { strsql = value; }
        }
        public IList Sqlparameters
        {
            get { return sqlparameters; }
            set { sqlparameters = value; }
        }
        private CommandType commandtype;

        public CommandType Commandtype
        {
            get { return commandtype; }
            set { commandtype = value; }
        }

    }
}

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24537046/viewspace-672597/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24537046/viewspace-672597/