.net中的数据访问层的封装

当年.net项目中自己封装的数据访问层，个人感觉使用比较方便，供大家参考
在dao层访问时可以如下调用：

public class PersonDao{    SqlDataProvider sql;    public PersonDao()    {        sql = new SqlDataProvider();    }    //insert    public void addPerson(Person person){        sql.AddSqlComm(            "INSERT INTO Person(UserName,Age)"            + " SELECT @userName,@age"            );        sql.AddParameters("@userName", person.getUserName());        sql.AddParameters("@age", person.getAge());        sql.ExecuteNonQuery();    }    //update    public void modPerson(Person person){        sql.AddSqlComm(            "UPDATE Person "            + " SET UserName = @userName AND Age = @age"            + " WHERE Id = @id"            );        sql.AddParameters("@userName", person.getUserName());        sql.AddParameters("@age", person.getAge());        sql.AddParameters("@id", person.getId());        sql.ExecuteNonQuery();    }    //delete    public void rmPerson(Person person){        sql.AddSqlComm(            "DELETE FROM Person "            + " WHERE Id = @id"            );        sql.AddParameters("@id", person.getId());        sql.ExecuteNonQuery();    }    //select    public DataTable getPersonById(int id){        sql.AddSqlComm(                "SELECT Id, UserName, Age"                + " FROM Person WITH(NOLOCK)"                + " where id= @id"                );            sql.AddParameters("@id", id);               return sql.ExecuteDataTable();    }    public DataSet getPeople(){        sql.AddSqlComm(                "SELECT Id, UserName, Age"                + " FROM Person WITH(NOLOCK)"                );            return sql.ExecuteDataSet();    }    //sp    public void init(int id){        sql.AddSqlComm("usp_init_person");            sql.CommandType = CommandType.StoredProcedure;            sql.AddParameters("@id", id);            sql.ExecuteNonQuery();    }}

是不是很方便，只要传入简单的SQL语句或者存储过程，就可以调用数据访问层。现在，我们开看一下这个数据访问层的代码实现：

1. 先看文件架构

我们以访问SQL Server数据库为例介绍

2.IDataProvider

using System;using System.Collections.Generic;using System.Linq;using System.Text;namespace DataProvider{    interface IDataProvider    {        void AddParameters(string parname, Guid value);        void AddParameters(string parname, long value);        void AddParameters(string parname, string value);        void AddParameters(string parname, string value, DataProvider.StringFamily dateType);        void AddParameters(string parname, string value, DataProvider.StringFamily dateType, int size);        void AddParameters(string parname, float value);        void AddParameters(string parname, decimal value);        void AddParameters(string parname, DateTime value, DataProvider.DateFamily dateType);        void AddParameters(string parname, int value);        void AddParameters(string parname, object value);        void AddParameters(string parname, byte[] value, DataProvider.ByteArrayFamily dateType);        void AddParameters(string parname, bool value);        void AddParameters(string parname, short value);        void AddParameters(string parname, byte value);        System.Data.CommandType CommandType { get; set; }        string ConnectionString { get; }        System.Data.DataSet ExecuteDataSet();        System.Data.DataTable ExecuteDataTable();        void ExecuteReader(ReadData readData);        int ExecuteNonQuery();        object ExecuteScalar();        string SQL { get; set; }    }    public delegate void ReadData(System.Data.IDataReader dataReadre);}

3.SqlDataProvider

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SqlClient;using System.Configuration;namespace DataProvider.SqlDataProvider{    /// <summary>    /// SQL数据提供者的实现    /// </summary>    public class SqlDataProvider : IDataProvider    {        //数据库连接字符串        string connstr;        private static System.Data.SqlClient.SqlConnection conn;        private System.Data.SqlClient.SqlCommand cmd;        public SqlDataProvider()        {            connstr = ConfigurationManager.ConnectionStrings["SQLCONN"].ToString();        }        public SqlDataProvider(string userDefinedConnStr)        {            connstr = ConfigurationManager.ConnectionStrings[userDefinedConnStr].ToString();         }        public SqlDataProvider(string connstr, string sql)        {            conn = new System.Data.SqlClient.SqlConnection(connstr);            cmd = new System.Data.SqlClient.SqlCommand();            cmd.Connection = conn;            cmd.CommandText = sql;            cmd.CommandTimeout = 0;        }        public void SetConn(String conn)        {            connstr = ConfigurationManager.ConnectionStrings[conn].ToString();         }        /// <summary>        /// 当工厂生产好数据访问对象后，用这个方法向对象输入sql语句        /// </summary>        /// <param name="sql"></param>        public void AddSqlComm(String sql)        {            conn = new System.Data.SqlClient.SqlConnection(connstr);            cmd = new System.Data.SqlClient.SqlCommand();            cmd.Connection = conn;            cmd.CommandText = sql;            cmd.CommandTimeout = 0;        }        /// <summary>        /// 需要执行的SQL命令        /// </summary>        public string SQL        {            set            {                cmd.CommandText = value;            }            get            {                return cmd.CommandText;            }        }        /// <summary>        /// 当前的连接字符串        /// </summary>        public string ConnectionString        {            get            {                return conn.ConnectionString;            }        }        /// <summary>        /// 设置命令的类型        /// </summary>        public System.Data.CommandType CommandType        {            set            {                cmd.CommandType = value;            }            get            {                return cmd.CommandType;            }        }        /// <summary>        /// 添加一个Variant类型数据        /// </summary>        /// <param name="parname"></param>        /// <param name="value"></param>        public void AddParameters(string parname, object value)        {            cmd.Parameters.Add(parname, System.Data.SqlDbType.Variant).Value = value;        }        /// <summary>        /// 添加一个Bit类型数据        /// </summary>        /// <param name="parname"></param>        /// <param name="value"></param>        public void AddParameters(string parname, bool value)        {            cmd.Parameters.Add(parname, System.Data.SqlDbType.Bit).Value = value;        }        /// <summary>        /// 添加一个TinyInt类型数据        /// </summary>        /// <param name="parname"></param>        /// <param name="value"></param>        public void AddParameters(string parname, byte value)        {            cmd.Parameters.Add(parname, System.Data.SqlDbType.TinyInt).Value = value;        }        /// <summary>        /// 添加一个SmallInt类型数据        /// </summary>        /// <param name="parname"></param>        /// <param name="value"></param>        public void AddParameters(string parname, short value)        {            cmd.Parameters.Add(parname, System.Data.SqlDbType.SmallInt).Value = value;        }        /// <summary>        /// 添加一个Int类型数据        /// </summary>        /// <param name="parname"></param>        /// <param name="value"></param>        public void AddParameters(string parname, int value)        {            cmd.Parameters.Add(parname, System.Data.SqlDbType.Int).Value = value;        }        /// <summary>        /// 添加一个Int类型数据输出参数        /// </summary>        /// <param name="parname"></param>        /// <param name="value"></param>        public void AddOutputParameters(string parname)        {            cmd.Parameters.Add(parname, System.Data.SqlDbType.Int).Direction = ParameterDirection.Output;        }        /// <summary>        /// 添加一个BigInt类型数据        /// </summary>        /// <param name="parname"></param>        /// <param name="value"></param>        public void AddParameters(string parname, long value)        {            cmd.Parameters.Add(parname, System.Data.SqlDbType.BigInt).Value = value;        }        /// <summary>        /// 添加一个字节数组族类型数据        /// </summary>        /// <param name="parname"></param>        /// <param name="value"></param>        /// <param name="dateType"></param>        public void AddParameters(string parname, byte[] value, ByteArrayFamily dateType)        {            cmd.Parameters.Add(parname, DataTypeAdapter.ConvertSqlDbType(dateType)).Value = value;        }        /// <summary>        /// 添加一个字符类型数据，默认是NVarChar，长度是value.Length        /// </summary>        /// <param name="parname"></param>        /// <param name="value"></param>        public void AddParameters(string parname, string value)        {            AddParameters(parname, value, StringFamily.NVarChar, value.Length);        }        /// <summary>        ///         /// </summary>        /// <param name="parname"></param>        /// <param name="value"></param>        /// <param name="length"></param>        public void AddParameters(string parname, string value, int size)        {            AddParameters(parname, value, StringFamily.NVarChar, size);        }        /// <summary>        /// 添加一个字符族类型数据        /// </summary>        /// <param name="parname"></param>        /// <param name="value"></param>        /// <param name="dateType"></param>        /// <param name="length"></param>        public void AddParameters(string parname, string value, StringFamily dateType)        {            AddParameters(parname, value, dateType, value.Length);        }        /// <summary>        /// 添加一个字符族类型数据        /// </summary>        /// <param name="parname"></param>        /// <param name="value"></param>        /// <param name="dateType"></param>        /// <param name="size"></param>        public void AddParameters(string parname, string value, StringFamily dateType, int size)        {            cmd.Parameters.Add(parname, DataTypeAdapter.ConvertSqlDbType(dateType), size).Value = value;        }        /// <summary>        /// 添加一个日期族类型数据        /// </summary>        /// <param name="parname"></param>        /// <param name="value"></param>        /// <param name="dateType"></param>        public void AddParameters(string parname, DateTime value, DateFamily dateType)        {            cmd.Parameters.Add(parname, DataTypeAdapter.ConvertSqlDbType(dateType)).Value = value;        }        /// <summary>        /// 添加一个Decimal类型数据        /// </summary>        /// <param name="parname"></param>        /// <param name="value"></param>        public void AddParameters(string parname, decimal value)        {            cmd.Parameters.Add(parname, System.Data.SqlDbType.Decimal).Value = value;        }        /// <summary>        /// 添加Float类型数据        /// </summary>        /// <param name="parname"></param>        /// <param name="value"></param>        public void AddParameters(string parname, float value)        {            cmd.Parameters.Add(parname, System.Data.SqlDbType.Float).Value = value;        }        /// <summary>        /// 添加一个UniqueIdentifier类型数据        /// </summary>        /// <param name="parname"></param>        /// <param name="value"></param>        public void AddParameters(string parname, System.Guid value)        {            cmd.Parameters.Add(parname, System.Data.SqlDbType.UniqueIdentifier).Value = value;        }        /// <summary>        /// 将SqlDataReader提交给具体的委托器处理        /// </summary>        /// <param name="readData"></param>        public void ExecuteReader(ReadData readData)        {            using (conn)            {                conn.Open();                System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();                readData(dr);                conn.Close();            }        }        /// <summary>        /// 对连接执行 Transact-SQL 语句并返回受影响的行数        /// </summary>        /// <returns></returns>        public int ExecuteNonQuery()        {            int result = -1;            using (conn)            {                conn.Open();                result = cmd.ExecuteNonQuery();                conn.Close();            }            return result;        }        /// <summary>        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行        /// </summary>        /// <returns></returns>        public object ExecuteScalar()        {            object result = null;            using (conn)            {                conn.Open();                result = cmd.ExecuteScalar();                conn.Close();            }            return result;        }        /// <summary>        /// 执行查询，并返回查询的DataSet        /// </summary>        /// <returns></returns>        public System.Data.DataSet ExecuteDataSet()        {            System.Data.DataSet datadet = new System.Data.DataSet();            using (conn)            {                System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();                adapter.SelectCommand = cmd;                conn.Open();                adapter.Fill(datadet);                conn.Close();            }            return datadet;        }        /// <summary>        /// 执行查询，并返回查询的Table        /// </summary>        /// <param name="tableIndex"></param>        /// <returns></returns>        public System.Data.DataTable ExecuteDataSet(int tableIndex)        {            System.Data.DataSet datadet = ExecuteDataSet();            if (datadet.Tables.Count > 0 && tableIndex < datadet.Tables.Count)            {                return datadet.Tables[tableIndex];            }            else            {                return null;            }        }        /// <summary>        /// 执行查询，并返回查询的Table        /// </summary>        /// <returns></returns>        public System.Data.DataTable ExecuteDataTable()        {            System.Data.DataTable table = new System.Data.DataTable();            using (conn)            {                System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();                adapter.SelectCommand = cmd;                conn.Open();                adapter.Fill(table);                conn.Close();            }            return table;        }        /// <summary>        /// 带事务的执行        /// </summary>        /// <param name="sqlDataHelper">>需要执行的一组SqlDataProvider</param>        /// <param name="errorDataProviderIndex">执行中有错误的SqlDataProvider对象索引</param>        public static void ExecuteTransaction(SqlDataProvider[] sqlDataHelpers, out int errorDataProviderIndex)        {            ExecuteTransaction(sqlDataHelpers, out errorDataProviderIndex, false);        }        /// <summary>        /// 带事务的执行        /// </summary>        /// <param name="sqlDataHelper">需要执行的一组SqlDataProvider</param>        /// <param name="errorDataProviderIndex">执行中有错误的SqlDataProvider对象索引</param>        /// <param name="strict">是否要严格计算执行返回行</param>        public static void ExecuteTransaction(SqlDataProvider[] sqlDataHelpers, out int errorDataProviderIndex, bool strict)        {            errorDataProviderIndex = -1;            using (conn)            {                conn.Open();                //默认的事物不隔离，安全                System.Data.SqlClient.SqlTransaction transaction = conn.BeginTransaction();                for (int i = 0; i <= sqlDataHelpers.Length - 1; i++)                {                    //设置事务，只有在这个内部才可以设置                    sqlDataHelpers[i].cmd.Transaction = transaction;                    sqlDataHelpers[i].cmd.Connection = conn;                    try                    {                        if (strict && sqlDataHelpers[i].ExecuteNonQuery() == 0)//需要严格执行                        {                            errorDataProviderIndex = i;//错误的执行索引                            throw new System.Exception("有执行语句没有返回有效行数");                        }                    }                    catch (System.Exception e)                    {                        transaction.Rollback();                        throw new System.Exception("语句执行中有意外错误", e);                    }                    finally                    {                    }                }                conn.Close();                transaction.Commit();            }        }    }}

4.DataTypeAdapter

using System;using System.Collections.Generic;using System.Linq;using System.Text;namespace DataProvider.SqlDataProvider{    /// <summary>    /// SqlDbType数据类型和.NET Framework数据类型的适配器    /// </summary>    public static class DataTypeAdapter    {        /// <summary>        /// 将.NET Framework数据类型适配为SqlDbType数据类型        /// </summary>        /// <param name="data"></param>        /// <returns></returns>        public static System.Data.SqlDbType ConvertSqlDbType(StringFamily data)        {            switch (data)            {                case StringFamily.Char:                    return System.Data.SqlDbType.Char;                case StringFamily.NChar:                    return System.Data.SqlDbType.NChar;                case StringFamily.NText:                    return System.Data.SqlDbType.NText;                case StringFamily.NVarChar:                    return System.Data.SqlDbType.NVarChar;                case StringFamily.Text:                    return System.Data.SqlDbType.Text;                default:                    return System.Data.SqlDbType.VarChar;            }        }        /// <summary>        /// 将.NET Framework数据类型适配为SqlDbType数据类型        /// </summary>        /// <param name="data"></param>        /// <returns></returns>        public static System.Data.SqlDbType ConvertSqlDbType(DateFamily data)        {            switch (data)            {                case DateFamily.Date:                    return System.Data.SqlDbType.Date;                case DateFamily.DateTime:                    return System.Data.SqlDbType.DateTime;                case DateFamily.DateTime2:                    return System.Data.SqlDbType.DateTime2;                case DateFamily.DateTimeOffset:                    return System.Data.SqlDbType.DateTimeOffset;                case DateFamily.SmallDateTime:                    return System.Data.SqlDbType.SmallDateTime;                default:                    return System.Data.SqlDbType.Time;            }        }        /// <summary>        /// 将.NET Framework数据类型适配为SqlDbType数据类型        /// </summary>        /// <param name="data"></param>        /// <returns></returns>        public static System.Data.SqlDbType ConvertSqlDbType(ByteArrayFamily data)        {            switch (data)            {                case ByteArrayFamily.Binary:                    return System.Data.SqlDbType.Binary;                case ByteArrayFamily.Image:                    return System.Data.SqlDbType.Image;                case ByteArrayFamily.Timestamp:                    return System.Data.SqlDbType.Timestamp;                default:                    return System.Data.SqlDbType.VarBinary;            }        }    }}

5.DataFamily

using System;using System.Collections.Generic;using System.Linq;using System.Text;namespace DataProvider{    /// <summary>    /// C#对于的SQL类型    /// </summary>    public enum StringFamily    {        Char,        NChar,        NText,        NVarChar,        Text,        VarChar    }    /// <summary>    /// C#对于的SQL类型    /// </summary>    public enum DateFamily    {        DateTime,        SmallDateTime,        Date,        Time,        DateTime2,        DateTimeOffset    }    /// <summary>    /// C#对于的SQL类型    /// </summary>    public enum ByteArrayFamily    {        Binary,        Image,        Timestamp,        VarBinary    }}