.Net Core 系列：2、ADO.Net 基础

因为本系列是.NET Core 系列，本文中所有叙述的是基于.NET Core 1.1版本的数据访问层接口。为什么需要强调是.Net Core 1.1呢？由于在2017年Q3发布的.NET Core 2.0中通过官网的Apis文档可以看出，在.NET Core 2.0时代已经将.NET Framework的ADO.NET整套体系完整覆盖。同时也让我十分纠结，到底要不要写这篇文章。从而造成我对整个系列的编写产生了动摇。回头想想，还是写下来吧！就当是自己的一次学习笔记的记录。

2. ADO.NET的前世今生

ADO.NET的名称起源于ADO（ActiveX Data Objects），是一个COM组件库，用于在以往的Microsoft技术中访问数据。之所以使用ADO.NET名称，是因为Microsoft希望表明，这是在NET编程环境中优先使用的数据访问接口。

ADO.NET可让开发人员以一致的方式存取资料来源（例如 SQL Server 与 XML），以及透过 OLE DB 和 ODBC 所公开的资料来源。资料共用的消费者应用程序可使用ADO.NET 来连接至这些资料来源，并且撷取、处理及更新其中所含的资料。

ADO.NETt可将资料管理的资料存取分成不连续的元件，这些元件可分开使用，也可串联使用ADO.NET也包含 .NET Framework 资料提供者，以用于连接资料库、执行命令和撷取结果。这些结果会直接处理、放入ADO.NET DataSet 物件中以便利用机器操作（Ad Hoc）的方式公开给使用者、与多个来源的资料结合，或在各层之间进行传递。DataSet 物件也可以与.NET Framework 资料提供者分开使用，以便管理应用程序本机的资料或来自 XML 的资料。

ADO.NET类别（Class）位于 System.Data.dll 中，而且会与 System.Xml.dll 中的XML 类别整合。

ADO.NET可为撰写 Managed 程式码的开发人员提供类似于ActiveX Data Objects （ADO）提供给原生元件物件模型（Component Object Model，COM）开发人员的功能。建议使用ADO.NET而非ADO来存取.NET 应用程序中的资料。

ADO .NET会提供最直接的方法，让开发人员在 .NET Framework 中进行资料存取。

------- 摘自百度百科

我什么要摘出这一段呢？因为在.NET Core 1.1里根本不存在DataSet这个类只实现了DataTable，也不存在DataAdapter。在2002-2009年这7年间，大量的应用均是使用这类适配存取的方式去编写应用。简单、高效、门槛低。2009年后各种ORM、MVC、MVP、MVVM、IoC等各种架构和技术开始流行。DataAdapter+DataSet+ASP.NET WEBFORM的套路开始走下神坛。

而.NET Core 1.1可以说是微软一个重要的云计算计划的其中一个棋子。并且微软本质的目标也并非鼓励将已有应用向.NET Core迁移。所以在.NET Core 1.1版本只实现了一个基础闭环。.NET Standard + .NET Core MVC + EntityFramework Core。毕竟步子迈得太大容易扯着蛋。

3、用基础的剑法打出一套增、删、改、查

让我们看看.NET Core 为我们提供了什么基础剑法。

增、删、改、查均是SQL语句的命令，所以只要存在能向数据库发送SQL脚本的接口则可以实现，Command，要发送脚本总要知道脚本往哪里发找到了Connection，执行完脚本数据库向我们回发结果总要有一个承载 Reader、 Record。如果执行的是多条脚本，并且需要保证脚本一次成功，我们找到了Transaction

3.1 IDbConnection

和数据库交互，必须连接它，连接帮助指明数据库服务器，数据库名字，用户名，密码和其他需要的参数。Connection会被Command对象使用，这样就能够知道是在哪个数据源上面执行命令。其实我就是我们访问数据库时最初的那个连接数据库字符串。

现在很多学习编程的新同学，都是依赖百度/谷歌去直接搜索解决方法。但是学习的本质都是从官方例子开始理解的。

using System;
using System.Data;

namespace IDbConnectionSample {
   class Program {
      static void Main(string[] args) {
         IDbConnection connection;

         // First use a SqlClient connection
         connection = new System.Data.SqlClient.SqlConnection(@"Server=(localdb)\V11.0");
         Console.WriteLine("SqlClient\r\n{0}", GetServerVersion(connection));
         connection = new System.Data.SqlClient.SqlConnection(@"Server=(local);Integrated Security=true");
         Console.WriteLine("SqlClient\r\n{0}", GetServerVersion(connection));

         // Call the same method using ODBC
         // NOTE: LocalDB requires the SQL Server 2012 Native Client ODBC driver
         connection = new System.Data.Odbc.OdbcConnection(@"Driver={SQL Server Native Client 11.0};Server=(localdb)\v11.0");
         Console.WriteLine("ODBC\r\n{0}", GetServerVersion(connection));
         connection = new System.Data.Odbc.OdbcConnection(@"Driver={SQL Server Native Client 11.0};Server=(local);Trusted_Connection=yes");
         Console.WriteLine("ODBC\r\n{0}", GetServerVersion(connection));

         // Call the same method using OLE DB
         connection = new System.Data.OleDb.OleDbConnection(@"Provider=SQLNCLI11;Server=(localdb)\v11.0;Trusted_Connection=yes;");
         Console.WriteLine("OLE DB\r\n{0}", GetServerVersion(connection));
         connection = new System.Data.OleDb.OleDbConnection(@"Provider=SQLNCLI11;Server=(local);Trusted_Connection=yes;");
         Console.WriteLine("OLE DB\r\n{0}", GetServerVersion(connection));
         }

      public static string GetServerVersion(IDbConnection connection) {
         // Ensure that the connection is opened (otherwise executing the command will fail)
         ConnectionState originalState = connection.State;
         if (originalState != ConnectionState.Open)
            connection.Open();
         try {
            // Create a command to get the server version
            // NOTE: The query's syntax is SQL Server specific
            IDbCommand command = connection.CreateCommand();
            command.CommandText = "SELECT @@version";
            return (string)command.ExecuteScalar();
         }
         finally {
            // Close the connection if that's how we got it
            if (originalState == ConnectionState.Closed)
               connection.Close();
         }
      }
   }
}

　　这个例子主要是演示了三种链接驱动的使用方式分别是：SqlClient、ODBC、OLE DB 但是很遗憾NET Core 1.1只提供了SqlClient，但是在官方的Apis2.0文档中是可以找ODBC、OLE DB、OracleClient。除了官方的驱动开源的驱动提供NET Core 1.1的还有Npgsql（postgresql数据库），Mysql的官方驱动尚未支持.NET Core,在nuget上可以找到Mysql.Data(8.0.8)预览版的驱动可以支持.NET Core 1.1

下面我们以Npgsql来演示一次上述例子。

using System;
using System.Data;
using Npgsql;

namespace IDbConnectionSample
{
    class Program
    {
        static void Main(string[] args)
        {
            IDbConnection connection;

            connection = new NpgsqlConnection("Server=192.168.1.41;Port=5432;User Id=postgres;Password=postgres;");
            Console.WriteLine("Npgsql:\r\n{0}", GetServerVersion(connection));
            Console.ReadKey();
        }

        public static string GetServerVersion(IDbConnection connection)
        {
            ConnectionState originalState = connection.State;
            if (originalState != ConnectionState.Open)
                connection.Open();
            try
            {
                IDbCommand command = connection.CreateCommand();
                command.CommandText = "SELECT version()";//pgsql获取版本的函数是Version()
                return (string)command.ExecuteScalar();
            }
            finally
            {
                if (originalState == ConnectionState.Closed)
                    connection.Close();
            }
        }
    }
}

3.2 IDbCommand

成功与数据建立连接后,就可以用Command对象来执行查询、修改、插入、删除等命令；Command对象常用的方法有ExecuteReader（）方法、ExecuteScalar（）方法和ExecuteNonQuery（）方法；插入数据可用ExecuteNonQuery（）方法来执行插入命令。

看例子：

using System;
using System.Data;
using Npgsql;
using System.Collections.Generic;

namespace IDbConnectionSample
{
    class Program
    {
        static void Main(string[] args)
        {
            IDbConnection connection;

            connection = new NpgsqlConnection("Server=192.168.1.41;Port=5432;User Id=postgres;Password=postgres;Database=test");
            Console.WriteLine("Npgsql:\r\n{0}", GetServerVersion(connection));

            Console.WriteLine("InsertUser:\r\n{0}", InsertUser(connection, "InsertUser" + DateTime.Now.Ticks));
            
            foreach (var item in AllUserName(connection))
            {
                Console.WriteLine("AllUserName:\r\n{0}", item);
            }
            Console.ReadKey();
        }

        /// <summary>
        /// 查单值
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public static string GetServerVersion(IDbConnection connection)
        {
            ConnectionState originalState = connection.State;
            if (originalState != ConnectionState.Open)
                connection.Open();
            try
            {
                IDbCommand command = connection.CreateCommand();
                command.CommandText = "SELECT version()";
                return (string)command.ExecuteScalar();
            }
            finally
            {
                if (originalState == ConnectionState.Closed)
                    connection.Close();
            }
        }

        /// <summary>
        /// 增加一个用户
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public static bool InsertUser(IDbConnection connection, string userName)
        {
            ConnectionState originalState = connection.State;
            if (originalState != ConnectionState.Open)
                connection.Open();
            try
            {
                IDbCommand command = connection.CreateCommand();
                //实际项目不要使用这种方式。仅演示使用，为什么？自己探索。
                //自定义的user表需要加上架构限定名
                command.CommandText = "INSERT INTO public.user(name) VALUES ('" + userName + "')";
                return command.ExecuteNonQuery() > 0;
            }
            finally
            {
                if (originalState == ConnectionState.Closed)
                    connection.Close();
            }
        }

        /// <summary>
        /// 查找所有用户的名称
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public static IList<string> AllUserName(IDbConnection connection)
        {
            IList<string> allUserName = new List<string>();
            ConnectionState originalState = connection.State;
            if (originalState != ConnectionState.Open)
                connection.Open();
            try
            {
                IDbCommand command = connection.CreateCommand();
                //自定义的user表需要加上架构限定名
                command.CommandText = "select name from public.user";
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    //根据select的语句中第0位是name
                    allUserName.Add(reader.GetString(0));
                }

            }
            finally
            {
                if (originalState == ConnectionState.Closed)
                    connection.Close();
            }

            return allUserName;
        }
    }
}

执行结果：

3.3 IDataReader

许多数据操作要求开发人员只是读取一串数据。DataReader对象允许开发人员获得从Command对象的SELECT语句得到的结果。考虑性能的因素，从DataReader返回的数据都是快速的且只是“向前”的数据流。这意味着开发人员只能按照一定的顺序从数据流中取出数据。这对于速度来说是有好处的，但是如果开发人员需要操作数据，并且这些数据含有复杂逻辑，最好还是先将数据转化为DataTable（1.1没有DataSet）、结构体或简单失血模型。因为DataReader和数据库间产生着持续的链接，知道度完后才会关闭连接。如果在read的过程中进行复杂的逻辑操作，那么对于并发来说那将是一个灾难。

从上图中没有找到任何读取数据列的操作，主要是因为IDataReader是继承IDataRecord，主要的读取操作均实现于IDataRecord

4、实现一个简单的SqlHelp类

我们定义一个简单的ISqlHelper接口，为什么要定义接口，主要是出于考虑到不同数据库可以扩展出不同的实现。

using System;
using System.Collections.Generic;
using System.Data;

namespace IDbConnectionSample
{
    /// <summary>
    /// Sql操作助手
    /// </summary>
    public interface ISqlHelper
    {
        /// <summary>
        /// 当前连接字符串
        /// </summary>
        IDbConnection Connection { get; set; }

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="sqlText">Sql文本</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="parms">参数</param>
        /// <returns>返回泛型的实体对象</returns>
        IEnumerable<T> Query<T>(string sqlText, CommandType commandType = CommandType.Text, params IDbDataParameter[] parms) where T :class;

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="sqlText">Sql文本</param>
        /// <param name="func">返回IDbDataParameter[]的委托</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>返回泛型的实体对象</returns>
        IEnumerable<T> Query<T>(string sqlText, Func<IDbDataParameter[]> func, CommandType commandType = CommandType.Text) where T : class;

        /// <summary>
        /// 查询首行首列
        /// </summary>
        /// <param name="sqlText">Sql文本</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="parms">参数</param>
        /// <returns>首行首列的值</returns>
        object Scalar(string sqlText, CommandType commandType = CommandType.Text, params IDbDataParameter[] parms);

        /// <summary>
        /// 查询首行首列
        /// </summary>
        /// <param name="sqlText">Sql文本</param>
        /// <param name="func">返回IDbDataParameter[]的委托</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>首行首列的值</returns>
        object Scalar(string sqlText, Func<IDbDataParameter[]> func, CommandType commandType = CommandType.Text);

        /// <summary>
        /// 查询首行首列
        /// </summary>
        /// <typeparam name="T">仅支持基础类型</typeparam>
        /// <param name="sqlText">Sql文本</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="parms">参数</param>
        /// <returns></returns>
        T Scalar<T>(string sqlText, CommandType commandType = CommandType.Text, params IDbDataParameter[] parms) where T : struct;

        /// <summary>
        /// 查询首行首列
        /// </summary>
        /// <typeparam name="T">仅支持基础类型</typeparam>
        /// <param name="sqlText">Sql文本</param>
        /// <param name="func">返回IDbDataParameter[]的委托</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>首行首列的值</returns>
        T Scalar<T>(string sqlText, Func<IDbDataParameter[]> func, CommandType commandType = CommandType.Text) where T : struct;


        /// <summary>
        /// 执行脚本
        /// </summary>
        /// <param name="sqlText">Sql文本</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="parms">参数</param>
        /// <returns>返回影响行数</returns>
        int Execute(string sqlText, CommandType commandType = CommandType.Text, params IDbDataParameter[] parms);


        /// <summary>
        /// 执行脚本
        /// </summary>
        /// <param name="sqlText">Sql文本</param>
        /// <param name="func">返回IDbDataParameter[]的委托</param>
        /// <param name="commandType">命令类型</param>
        /// <returns></returns>
        int Execute(string sqlText, Func<IDbDataParameter[]> func, CommandType commandType = CommandType.Text);
    }
}

//SqlHelper.cs
using System;
using System.Collections.Generic;
using System.Data;

namespace IDbConnectionSample
{
    public class SqlHelper : ISqlHelper, IDisposable
    {
        public SqlHelper(IDbConnection connection)
        {
            Connection = connection;
        }

        public IDbConnection Connection { get; set; }

        public void Dispose()
        {
            Connection.Dispose();
        }


        private IDbCommand buildCommand(string sqlText, CommandType commandType, params IDbDataParameter[] parms)
        {
            if (Connection == null)
                throw new Exception("IDbConnection is NULL");

            IDbCommand command = Connection.CreateCommand();
            command.CommandText = sqlText;
            command.CommandType = commandType;
            if (parms != null && parms.Length > 0)
            {
                foreach (var item in parms)
                {
                    command.Parameters.Add(item);
                }
            }

            return command;
        }

        public int Execute(string sqlText, CommandType commandType = CommandType.Text, params IDbDataParameter[] parms)
        {
            if (Connection == null)
                throw new Exception("IDbConnection is NULL");

            ConnectionState originalState = Connection.State;

            if (originalState != ConnectionState.Open)
                Connection.Open();
            try
            {
                return buildCommand(sqlText, commandType, parms).ExecuteNonQuery();
            }
            finally
            {
                if (originalState == ConnectionState.Closed)
                    Connection.Close();
            }
        }



        public int Execute(string sqlText, Func<IDbDataParameter[]> func, CommandType commandType = CommandType.Text)
        {
            return Execute(sqlText, commandType, func());
        }

        public IEnumerable<T> Query<T>(string sqlText, CommandType commandType = CommandType.Text, params IDbDataParameter[] parms) where T : class
        {
            if (Connection == null)
                throw new Exception("IDbConnection is NULL");

            ConnectionState originalState = Connection.State;

            if (originalState != ConnectionState.Open)
                Connection.Open();
            try
            {
                var reader = buildCommand(sqlText, commandType, parms).ExecuteReader();
                while (reader.Read())
                    yield return EntityBuilder<T>.GenerateByDataRecord(reader);
            }
            finally
            {
                if (originalState == ConnectionState.Closed)
                    Connection.Close();
            }
        }

        public IEnumerable<T> Query<T>(string sqlText, Func<IDbDataParameter[]> func, CommandType commandType = CommandType.Text) where T : class
        {
            return Query<T>(sqlText, commandType, func());
        }

        public object Scalar(string sqlText, CommandType commandType = CommandType.Text, params IDbDataParameter[] parms)
        {
            if (Connection == null)
                throw new Exception("IDbConnection is NULL");

            ConnectionState originalState = Connection.State;

            if (originalState != ConnectionState.Open)
                Connection.Open();
            try
            {
                return buildCommand(sqlText, commandType, parms).ExecuteScalar();
            }
            finally
            {
                if (originalState == ConnectionState.Closed)
                    Connection.Close();
            }
        }

        public object Scalar(string sqlText, Func<IDbDataParameter[]> func, CommandType commandType = CommandType.Text)
        {
            return Scalar(sqlText, commandType, func());
        }

        public T Scalar<T>(string sqlText, CommandType commandType = CommandType.Text, params IDbDataParameter[] parms) where T : struct
        {
            return (T)Scalar(sqlText, commandType, parms);
        }

        public T Scalar<T>(string sqlText, Func<IDbDataParameter[]> func, CommandType commandType = CommandType.Text) where T : struct
        {
            return Scalar<T>(sqlText, commandType, func());
        }
    }
}

//PGSqlHelper.cs
using Npgsql;

namespace IDbConnectionSample
{
    public class PGSqlHelper : SqlHelper
    {
        public PGSqlHelper(string connectionString) : base(new NpgsqlConnection(connectionString)) { }
    }
}

//MSSqlHelper.cs
namespace IDbConnectionSample
{
   public class MSSqlHelper: SqlHelper
    {
        public MSSqlHelper(string connectionString) : base(new System.Data.SqlClient.SqlConnection(connectionString)) { }
    }
}

通过接口，基类、子类，则实现了基础的SqlHelper，在ORM大行其道的时候是否简单的SqlHelper就没有用武之地呢？其实不是，在日常的维护中我们通常需要编写大量的小工具去跨越权限修复各种因为测试遗漏的数据错误。这种情况下我们需要的是快狠准，通过简单的子类重载则可以完成不同数据库的Helper。SqlHelper的威力就体现出来了。

5、总结

无论技术怎么发展其实都是从最基础的技能出发，掌握基础技能很重要。不忘初心,方得始终

posted @ 2017-07-26 11:08 梁乔峰阅读(9638) 评论(3) 编辑收藏举报

刷新页面返回顶部

编程,源于动手;天才,来自努力;

C#，NET，NET Core，JavaScript，Docker , Linux

.Net Core 系列：2、ADO.Net 基础

目录：

公告