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

最新推荐文章于 2018-08-24 13:07:00 发布

原创最新推荐文章于 2018-08-24 13:07:00 发布 · 2.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#.net #sql #数据

C# 专栏收录该内容

2 篇文章

订阅专栏

本文分享了一个.NET项目中自定义的数据访问层实现，通过简单的SQL语句或存储过程即可进行增删改查操作，提供了丰富的参数处理方法，支持多种数据类型。

当年.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
    }
}