[原创].Net下的数据访问层DAL

前几天正好解决了个通用数据持久访问层，特此拿出来奉献下~~~鲜花的鲜花，鸡蛋的鸡蛋~~~嘿嘿！

     特点：

          1。适合中小企业数据访问架构，其实大架构也没问题的，毕竟数据处理都放在了DB Server；

          2。使用的数据工厂和泛型反射工厂，所以，支持目前所有的数据库系统，只要改改web.config就搞定了；

          3。使用HashTable做条件字段的传输操作，并将值一个个传递给sp；

          4。支持：获取数据集；添加数据（有返回结果）；删除数据（有返回结果）；

 

      还有哦，这些是Ez原创的，转载请声明哦~~~

note1 : 以下是第一版V0.1

using System;
using System.Data;//数据集
using System.Configuration;//连接字符串
using System.Collections;//数据字典
using System.Data.Common;//数据工厂
/**//// 
/// DBOperate 的摘要说明
/// Author:Ez
/// Date:2007-04-28
/// E-mail:zhaojun_free@hotmail.com
/// 
public class DBOperate
{
    public DBOperate()
    {
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }
    数据连接操作#region 数据连接操作
    /**//// 
    /// 连接字符串 - GetDBConnString()
    /// 
    /// 
    ConnectionStringSettings GetDBConnString()
    {
        return ConfigurationManager.ConnectionStrings["MyList"];
    }
    /**//// 
    /// 创建数据工厂 - GetDBFactory()
    /// 
    /// 
    DbProviderFactory GetDBFactory()
    {
        DbProviderFactory dbfConn = DbProviderFactories.GetFactory(this.GetDBConnString().ProviderName);
        return dbfConn;
    }
    /**//// 
    /// 创建数据连接 - GetDBConn()
    /// 
    /// 
    DbConnection GetDBConn()
    {
        DbConnection conn = this.GetDBFactory().CreateConnection();
        conn.ConnectionString = this.GetDBConnString().ConnectionString;
        return conn;
    }
    #endregion

    /**//// 
    /// 获取数据集，传入存储过程名strSPName + 条件组合htTemp
    /// 
    /// 
    /// 
    /// 
    public DataSet GetData(string strSPName, Hashtable htTemp)
    {
        DataSet dsGetData = new DataSet();
        try
        {
            using (DbConnection conn = this.GetDBConn())
            {
                DbCommand cmd = this.GetDBFactory().CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strSPName;
                if (htTemp != null)
                {
                    foreach (DictionaryEntry deTemp in htTemp)
                    {
                        DbParameter param = this.GetDBFactory().CreateParameter();
                        param.ParameterName = deTemp.Key.ToString();
                        param.Value = deTemp.Value.ToString();
                        cmd.Parameters.Add(param);
                    }
                }
                DbDataAdapter da = this.GetDBFactory().CreateDataAdapter();
                da.SelectCommand = cmd;
                da.SelectCommand.Connection = conn;
                da.Fill(dsGetData);
            }
        }
        catch (Exception e)
        {
            throw new Exception("Fail to execute query: " + strSPName + ", Error:" + e.Message);
        }
        finally
        {
            dsGetData.Dispose();
        }
        
        return dsGetData;
    }
    /**//// 
    /// 执行记录的插入、删除操作，返回值为Int
    /// 1:操作成功;0:操作失败;
    /// 
    /// 
    /// 
    /// 
    public int ExecuteSP(string strSPName,Hashtable htTemp)
    {
        try
        {
            using (DbConnection conn = this.GetDBConn())
            {
                conn.Open();
                DbCommand cmd = this.GetDBFactory().CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strSPName;
                cmd.Connection = conn;
                if (htTemp != null)
                {
                    foreach (DictionaryEntry deTemp in htTemp)
                    {
                        DbParameter param = this.GetDBFactory().CreateParameter();
                        param.ParameterName = deTemp.Key.ToString();
                        param.Value = deTemp.Value.ToString();
                        cmd.Parameters.Add(param);
                    }
                }
                int iResult = cmd.ExecuteNonQuery();
                return iResult;;
            }
        }
        catch(Exception e)
        {
            throw new Exception("Fail to Execute SP:"+strSPName+",Error:"+e.Message);
        }
    }
}

note 2: 以下是第二版 2007.10.17
            增加了事务处理;
            增加ExecuteScalr查询单个结果;
            支持命令文本执行方式；
            支持存储过程调用方式；
            支持命令文本+存储过程混合调用方式（不提倡）；

using System;
using System.Data;//数据集
using System.Configuration;//连接字符串
using System.Collections;//数据字典,Hashtable
using System.Data.Common;//数据工厂

/**//// DBHelper 的摘要说明
/// Author:Ez.Zhao
/// Date:2007-04-28
/// Updated: Ez.Zhao(2007.-10.4),Ez.Zhao(2007.-10.15)
/// Blog:http://ez.zhao.cnblogs.com/
/// E-mail:zhaojun_free@hotmail.com
namespace CompanyName.DBHelper
{
    public class DBHelper
    {
        // 创建数据工厂、数据链接
        数据连接操作#region 数据连接操作
        // 1 连接字符串 - GetDBConnString()
        ConnectionStringSettings GetDBConnString()
        {
            return ConfigurationManager.ConnectionStrings["DBSource"];
        }

        // 2 创建数据工厂 - GetDBFactory()
        DbProviderFactory GetDBFactory()
        {
            DbProviderFactory dbfConn = DbProviderFactories.GetFactory(this.GetDBConnString().ProviderName);
            return dbfConn;
        }

        // 3 创建数据链接 - GetDBConn()
        DbConnection GetDBConn()
        {
            DbConnection conn = this.GetDBFactory().CreateConnection();
            conn.ConnectionString = this.GetDBConnString().ConnectionString;
            return conn;
        }
        #endregion

        // 获取数据集
        /**//// <summary>
        /// if htTemp == null, then Execute Text
        /// if (htTemp != null && htTemp.Count>0), then SP will add Parameters, else no Parameters for SP
        /// </summary>
        /// <param name="strExecuteString"></param>
        /// <param name="htTemp"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string strExecuteString, Hashtable htTemp)
        {
            DataSet dsGetData = new DataSet();
            try
            {
                using (DbConnection conn = this.GetDBConn())
                {
                    DbCommand cmd = this.GetDBFactory().CreateCommand();
                    cmd.CommandText = strExecuteString;

                    if (htTemp != null)
                    {
                        // Execute SP
                        cmd.CommandType = CommandType.StoredProcedure;
                        foreach (DictionaryEntry deTemp in htTemp)
                        {
                            DbParameter param = this.GetDBFactory().CreateParameter();
                            param.ParameterName = deTemp.Key.ToString();
                            param.Value = deTemp.Value.ToString();
                            cmd.Parameters.Add(param);
                        }
                    }
                    else
                    {
                        // Execute Text String
                        cmd.CommandType = CommandType.Text; 
                    }
                    DbDataAdapter da = this.GetDBFactory().CreateDataAdapter();
                    da.SelectCommand = cmd;
                    da.SelectCommand.Connection = conn;
                    da.Fill(dsGetData);
                }
            }
            catch (Exception e)
            {
                throw new Exception("Fail to execute query: " + strExecuteString + ", Error:" + e.Message);
            }
            finally
            {
                if (dsGetData != null)
                    dsGetData.Dispose();
            }
            return dsGetData;
        }

        // ExecuteScalar: 获取第一行第一列object对象 传入操作命令字符串 
        /**//// <summary>
        /// if htTemp == null, then Execute Text
        /// if (htTemp != null && htTemp.Count>0), then SP will add Parameters, else no Parameters for SP
        /// </summary>
        /// <param name="strExecuteString"></param>
        /// <param name="htTemp"></param>
        /// <returns></returns>
        public object ExecuteScalarByString(string strExecuteString,Hashtable htTemp)
        {
            object obj;
            try
            {
                using (DbConnection conn = this.GetDBConn())
                {
                    conn.Open();

                    DbCommand cmd = this.GetDBFactory().CreateCommand();
                    cmd.CommandText = strExecuteString;
                    cmd.Connection = conn;

                    if (htTemp != null)
                    {
                        // Execute SP
                        cmd.CommandType = CommandType.StoredProcedure;
                        foreach (DictionaryEntry deTemp in htTemp)
                        {
                            DbParameter param = this.GetDBFactory().CreateParameter();
                            param.ParameterName = deTemp.Key.ToString();
                            param.Value = deTemp.Value.ToString();
                            cmd.Parameters.Add(param);
                        }
                    }
                    else
                    {
                        // Execute Text String
                        cmd.CommandType = CommandType.Text;
                    }

                    obj = cmd.ExecuteScalar();
                    conn.Close();

                    return obj;
                }
            }
            catch (Exception e)
            {
                throw new Exception("Fail to execute string query: " + strExecuteString + ", Error:" + e.Message);
            }
        }

        // 单命令执行：插入、删除、更新 操作，返回值为Int,传入strExecuteString + 条件组合htTemp
        /**//// <summary>
        /// if htTemp == null, then Execute Text
        /// if (htTemp != null && htTemp.Count>0), then SP will add Parameters, else no Parameters for SP
        /// </summary>
        /// <param name="strExecuteString"></param>
        /// <param name="htTemp"></param>
        /// <returns></returns>
        public int ExecuteWithoutTran(string strExecuteString, Hashtable htTemp)
        {
            try
            {
                using (DbConnection conn = this.GetDBConn())
                {
                    conn.Open();
                    DbCommand cmd = this.GetDBFactory().CreateCommand();
                    cmd.CommandText = strExecuteString;
                    cmd.Connection = conn;
                    if (htTemp != null)
                    {
                        // Execute Procedure
                        cmd.CommandType = CommandType.StoredProcedure;
                        foreach (DictionaryEntry deTemp in htTemp)
                        {
                            DbParameter param = this.GetDBFactory().CreateParameter();
                            param.ParameterName = deTemp.Key.ToString();
                            param.Value = deTemp.Value.ToString();
                            cmd.Parameters.Add(param);
                        }
                    }
                    else
                    {
                        // Execute Text Execute
                        cmd.CommandType = CommandType.Text;
                    }
                    int iResult = cmd.ExecuteNonQuery();
                    conn.Close();
                    return iResult; ;
                }
            }
            catch (Exception e)
            {
                throw new Exception("Fail to Execute SP:" + strExecuteString + ",Error:" + e.Message);
            }
        }

        // 组合命令执行：事务操作 插入、删除、更新 操作，返回值为Int,传入操作字符htTemp
        /**//// <summary>
        /// if htTemp == null, then Execute Text
        /// if (htTemp != null && htTemp.Count>0), then SP will add Parameters, else no Parameters for SP
        /// </summary>
        /// <param name="htTemp"></param>
        /// <returns></returns>
        public int ExecteWithTran(Hashtable htTemp)
        {
            using (DbConnection conn = this.GetDBConn())
            {
                conn.Open();
                DbTransaction tran = conn.BeginTransaction();
                DbCommand cmd = this.GetDBFactory().CreateCommand();
                cmd.Transaction = tran;
                cmd.Connection = conn;
                int iResult = 0;
                string strResult = string.Empty;

                Hashtable htSPNameValue = null;

                try
                {
                    if (htTemp != null)
                    {
                        foreach (DictionaryEntry de in htTemp)
                        {
                            if (de.Value!=null)
                            {
                                //Execute SP
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.CommandText =Convert.ToString(de.Key);
                                if (!de.Value.Equals(string.Empty))
                                {
                                    // got some params
                                    htSPNameValue = (Hashtable)(de.Value);
                                    foreach (DictionaryEntry deSP in htSPNameValue)
                                    {
                                        DbParameter param = this.GetDBFactory().CreateParameter();
                                        param.ParameterName = deSP.Key.ToString();
                                        param.Value = deSP.Value.ToString();
                                        cmd.Parameters.Add(param);
                                    }
                                }
                                iResult = cmd.ExecuteNonQuery();
                                strResult = strResult + "the affected rows count is: " + iResult + "||||||||||";

                             }
                            else
                            {
                                // Execute Text
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = Convert.ToString(de.Key);
                                iResult = cmd.ExecuteNonQuery();
                                strResult = strResult + "the affected rows count is: " + iResult + "||||||||||";
                            }
                        }
                    }
                    else
                    {
                        strResult = " no input";
                    }
                    tran.Commit();
                }
                catch (Exception e)
                {
                    tran.Rollback();
                    throw new Exception("Fail to Execute String Command:" + ",Error:" + e.Message);
                }

                conn.Close();
                return iResult;
            }
        }
    }
}