分享个人的数据访问层

分享下个人的数据访问层，欢迎大家一起来探讨!

 

DBHelper.cs

namespace Zhy.DalClass.Provider
{
    using System;
    using System.Text;
    using Zhy.Factory;
    using System.Data;
    using Zhy.DalClass.DS;
    using System.Data.Common;

    public sealed class DBHelper
    {
        private static IDataBaseProvider Provider
        {
            get
            {
                return DataBase.Instance.CreateProvider();
            }
        }

        public static IDbConnection CreateConnection()
        {
            return CreateConnection(DataSource.ConnectionString);
        }

        public static IDbConnection CreateConnection(string strConn)
        {
            return Provider.CreateConnection(strConn);
        }

        private static void Open(IDbConnection cnn)
        {
            if (cnn.State != ConnectionState.Open)
            {
                cnn.Open();
            }
        }

        private static void Close(IDbConnection cnn)
        {
            if (cnn.State != ConnectionState.Closed)
            {
                cnn.Close();
            }
        }

        private static void Dispose(IDbConnection cnn)
        {
            if (cnn != null)
            {
                cnn.Dispose();
            }
        }
        private static void PrepareCommand(IDbCommand cmd, IDbConnection cnn, IDbTransaction dbTran, CommandType cmdType, string cmdText, IDbDataParameter[] cmdParams)
        {
            cmd.Connection = cnn;
            cmd.CommandText = cmdText;
            if (dbTran != null)
                cmd.Transaction = dbTran;
            cmd.CommandType = cmdType;
            if (cmdParams != null)
            {
                foreach (IDbDataParameter parm in cmdParams)
                    cmd.Parameters.Add(parm);
            }
        }

        private static void PrepareCommand(IDbCommand cmd, IDbConnection cnn, CommandType cmdType, string cmdText, IDbDataParameter[] cmdParams)
        {
            cmd.Connection = cnn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            if (cmdParams != null)
            {
                foreach (IDbDataParameter parm in cmdParams)
                    cmd.Parameters.Add(parm);
            }
        }

        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params IDbDataParameter[] cmdParams)
        {
            using (IDbConnection cnn = CreateConnection())
            {
                using (IDbCommand cmd = Provider.CreateCommand())
                {
                    PrepareCommand(cmd, cnn, cmdType, cmdText, cmdParams);
                    Open(cnn);
                    int obj = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    Close(cnn);
                    return obj;
                }
            }
        }

        public static int ExecuteNonQuery(IDbConnection cnn, CommandType cmdType, string cmdText, params IDbDataParameter[] cmdParams)
        {
            using (IDbCommand cmd = Provider.CreateCommand())
            {
                PrepareCommand(cmd, cnn, cmdType, cmdText, cmdParams);
                Open(cnn);
                int obj = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                Close(cnn);
                return obj;
            }
        }

        public static int ExecuteNonQuery(IDbTransaction dbTran, CommandType cmdType, string cmdText, params IDbDataParameter[] cmdParams)
        {
            using (IDbCommand cmd = Provider.CreateCommand())
            {
                using (IDbConnection cnn = dbTran.Connection)
                {
                    Open(cnn);
                    PrepareCommand(cmd, cnn, dbTran, cmdType, cmdText, cmdParams);
                    int obj = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    Close(cnn);
                    return obj;
                }
            }
        }

        public static IDataReader ExecuteReader(CommandType cmdType, string cmdText, params IDbDataParameter[] cmdParams)
        {
            IDbConnection cnn = CreateConnection();
            try
            {
                IDbCommand cmd = Provider.CreateCommand();
                PrepareCommand(cmd, cnn, cmdType, cmdText, cmdParams);
                Open(cnn);
                IDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return dr;
            }
            catch (Exception ex)
            {
                Close(cnn);
                throw ex;
            }
        }

        public static object ExecuteScalar(CommandType cmdType, string cmdText, params IDbDataParameter[] cmdParams)
        {
            using (IDbConnection cnn = CreateConnection())
            {
                using (IDbCommand cmd = Provider.CreateCommand())
                {
                    PrepareCommand(cmd, cnn, cmdType, cmdText, cmdParams);
                    Open(cnn);
                    object obj = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    Close(cnn);
                    return obj;
                }
            }
        }

        public static object ExecuteScalar(IDbConnection cnn, CommandType cmdType, string cmdText, params IDbDataParameter[] cmdParams)
        {
            using (IDbCommand cmd = Provider.CreateCommand())
            {
                PrepareCommand(cmd, cnn, cmdType, cmdText, cmdParams);
                Open(cnn);
                object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                Close(cnn);
                return obj;
            }
        }

        public static DataTable DataReadToDataTable(IDataReader dr)
        {
            DataTable dtTemp = null;
            try
            {
                int i = 0;
                dtTemp = new DataTable();
                DataColumn dataColumn = null;
                for (; i < dr.FieldCount; i++)
                {
                    dataColumn = new DataColumn();
                    dataColumn.ColumnName = dr.GetName(i);
                    dataColumn.DataType = dr.GetFieldType(i);
                    dtTemp.Columns.Add(dataColumn);
                }

                DataRow dataRow = null;
                while (dr.Read())
                {
                    dataRow = dtTemp.NewRow();
                    for (i = 0; i < dr.FieldCount; i++)
                    {
                        dataRow[i] = dr.GetValue(i);
                    }
                    dtTemp.Rows.Add(dataRow);
                }

                return dtTemp;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dr.Close();
            }
        }

        public static DataTable DataReadToEmptyDataTable(IDataReader dr)
        {
            DataTable dtTemp = null;
            try
            {
                dtTemp = new DataTable();
                DataColumn dataColumn = null;
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    dataColumn = new DataColumn();
                    dataColumn.ColumnName = dr.GetName(i);
                    dataColumn.DataType = dr.GetFieldType(i);
                    dtTemp.Columns.Add(dataColumn);
                }

                return dtTemp;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dr.Close();
            }
        }

        public static DataSet Query(CommandType cmdType, string cmdText, params IDbDataParameter[] cmdParams)
        {
            using (IDbConnection cnn = CreateConnection())
            {
                using (IDbCommand cmd = Provider.CreateCommand())
                {
                    PrepareCommand(cmd, cnn, cmdType, cmdText, cmdParams);
                    using (DbDataAdapter adapter = Provider.CreateDbDataAdapter(cmd))
                    {
                        try
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds);
                            cmd.Parameters.Clear();
                            return ds;
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                        finally
                        {
                            Close(cnn);
                        }
                    }
                }
            }
        }
    }
}

 

 

 IDataBaseProvider.cs

namespace Zhy.DalClass
{
    using System.Data;
    using System.Data.Common;
    public interface IDataBaseProvider
    {
        IDbConnection CreateConnection();
        IDbConnection CreateConnection(string strConn);
        IDbCommand CreateCommand();
        IDbCommand CreateCommand(IDbConnection cnn);
        IDbCommand CreateCommand(string strSql, IDbConnection cnn);
        IDbCommand CreateCommand(string strSql, IDbConnection cnn, IDbTransaction trans);
        DbDataAdapter CreateDbDataAdapter();
        DbDataAdapter CreateDbDataAdapter(IDbCommand cmd);
        DbDataAdapter CreateDbDataAdapter(string strSql, IDbConnection cnn);
        DbDataAdapter CreateDbDataAdapter(string strSql, string strConn);
        IDbDataAdapter CreateDataAdapter();
        IDbDataAdapter CreateDataAdapter(IDbCommand cmd);
        IDbDataAdapter CreateDataAdapter(string strSql, IDbConnection cnn);
        IDbDataAdapter CreateDataAdapter(string strSql, string strConn);
        IDataReader CreateDataReader(IDbCommand cmd);
        IDbTransaction CreateTransaction(IDbConnection cnn);
    }
}

 

 IDBProvider.cs

namespace Zhy.DalClass
{
    public interface IDBProvider
    {
        IDataBaseProvider CreateProvider();
    }
}

 

DataBaseProvider.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
namespace Zhy.DalClass.MsSQL
{
    public class DataBaseProvider : Zhy.DalClass.IDataBaseProvider
    {
        public IDbConnection CreateConnection()
        {
            return new SqlConnection();
        }
        public IDbConnection CreateConnection(string strConn)
        {
            return new SqlConnection(strConn);
        }

        public IDbCommand CreateCommand()
        {
            return new SqlCommand();
        }
        public IDbCommand CreateCommand(IDbConnection cnn)
        {
            return (SqlCommand)cnn.CreateCommand();
        }

        public IDbCommand CreateCommand(string strSql, IDbConnection cnn)
        {
            return new SqlCommand(strSql, (SqlConnection)cnn);
        }

        public IDbCommand CreateCommand(string strSql, IDbConnection cnn, IDbTransaction trans)
        {
            SqlCommand cmd = new SqlCommand(strSql, (SqlConnection)cnn);
            cmd.Transaction = (SqlTransaction)trans;
            return cmd;
        }
        public DbDataAdapter CreateDbDataAdapter()
        {
            return new SqlDataAdapter();
        }

        public DbDataAdapter CreateDbDataAdapter(IDbCommand cmd)
        {
            return new SqlDataAdapter((SqlCommand)cmd);
        }

        public DbDataAdapter CreateDbDataAdapter(string strSql, IDbConnection cnn)
        {
            return new SqlDataAdapter(strSql, (SqlConnection)cnn);
        }

        public DbDataAdapter CreateDbDataAdapter(string strSql, string strConn)
        {
            return new SqlDataAdapter(strSql, new SqlConnection(strConn));
        }

        public IDbDataAdapter CreateDataAdapter()
        {
            return new SqlDataAdapter();
        }

        public IDbDataAdapter CreateDataAdapter(IDbCommand cmd)
        {
            return new SqlDataAdapter((SqlCommand)cmd);
        }

        public IDbDataAdapter CreateDataAdapter(string strSql, IDbConnection cnn)
        {
            return new SqlDataAdapter(strSql, (SqlConnection)cnn);
        }
        public IDbDataAdapter CreateDataAdapter(string strSql, string strConn)
        {
            return new SqlDataAdapter(strSql, new SqlConnection(strConn));
        }
        public IDataReader CreateDataReader(IDbCommand cmd)
        {
            return (SqlDataReader)cmd.ExecuteReader();
        }

        public IDbTransaction CreateTransaction(IDbConnection cnn)
        {
            return (SqlTransaction)cnn.BeginTransaction();
        }
    }
}

 

DBProvider.cs

using System;
namespace Zhy.DalClass.MsSQL
{
    public class DBProvider : Zhy.DalClass.IDBProvider
    {
        public Zhy.DalClass.IDataBaseProvider CreateProvider()
        {
            return new DataBaseProvider();
        }
    }
}

 

点此：下载地址