Ado.Net

一、概述

ADO.NET是一组用于和数据源进行交互的面向对象类库。

数据源包括各种关系型数据库、文本文件、Excel和XML文件等，但是多用于数据库。

• Connection提供与数据源的连接 SqlConnection
• Command执行数据库命令的对象 SqlCommand
• DataReader从数据库中提供快速的，只读的数据流 SqlDataReader
• DataAdpater提供DataSet对象与数据源的桥梁。

主要使用到了三个类库

• System.Data：DataTable、DataSet等类
• System.Data.Common：各种数据访问类的基类和接口
• System.Data.SqlClient：对Sql Server进行操作的数据访问类。（如果是其他数据库可能需要其他的类库，并且在.NetCore环境下需要在Nuget中下载）

二、连接数据库

连接数据库需要使用到SqlConnection类，该类中有两个重要的属性：

• ConnectionString：用于连接Sql Server的字符串，是一个字符串类型。它的格式为**“Data Source=数据库IP，端口号\服务器名称; InitialCatalog=数据库名称; User Id=用户名; Password=用户密码;”，也可以在数据库的属性中查看完整的连接字符串属性。简写格式为：“server=数据库IP，端口号\服务器名称; database=数据库名称; uid=用户名; pwd=用户密码;”**
• State：表示的连接状态，是一个枚举类型。

    static void Main(string[] args)
    {
        string connectString = @"server=DESKTOP-RT0EGJF\SQLEXPRESS;database=TestDB;uid=heater;pwd=123456;";

        SqlConnection connection = new SqlConnection(connectString);

        Task task = connection.OpenAsync();//异步连接

        task.Wait();

        Console.WriteLine($"connection.State:{connection.State}");
    }

但是连接字符串的格式容易写错，所以框架提供了一个专门的类：

        static void Main(string[] args)
        {

            SqlConnectionStringBuilder connStrBuild = new SqlConnectionStringBuilder
            {
                DataSource= @"DESKTOP-RT0EGJF\SQLEXPRESS",
                InitialCatalog="TestDB",
                UserID="heater",
                Password="123456",
            };

            string connectString = connStrBuild.ConnectionString;

            SqlConnection connection = new SqlConnection(connectString);

            Task task = connection.OpenAsync();//异步连接

            task.Wait();

            Console.WriteLine($"connection.State:{connection.State}");
        }

但是，一般情况下，我们会将连接字符串存储在配置文件中，这样灵活度更高。

如果实在Framework环境下，那么可以存放在app.config文件中，如果是.netcore中，则可以放在appsettings.json文件中。下面示例在app.config文件中存储连接字符串，在app.config文件中同样也有两个位置可以存储字符串，建议存储在connectionStrings节点中。

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="sqlserver" connectionString="server=DESKTOP-RT0EGJF\SQLEXPRESS;database=TestDB;uid=heater;pwd=123456;" providerName="System.Data.SqlClient"/>
  </connectionStrings>

  <appSettings>
    <add key="ConnectionString" value="server=DESKTOP-RT0EGJF\SQLEXPRESS;database=TestDB;uid=heater;pwd=123456;"/>
  </appSettings>
</configuration>

        static void Main(string[] args)
        {
            //string connectString = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;

            string connectString = ConfigurationManager.AppSettings["ConnectionString"].ToString();

            SqlConnection connection = new SqlConnection(connectString);

            Task task = connection.OpenAsync();//异步连接

            task.Wait();

            Console.WriteLine($"connection.State:{connection.State}");
        }

值得一提的是，SqlConnection对象为非托管资源，需要手动释放。

三、连接池

连接数据库是一个非常耗时耗力的一件事，那么是否可以重复利用已有的连接呢？

连接池中存放了一定数量的与数据库服务器的物理连接，当需要的时候，可以从连接池中取出一条空闲的连接，而不是创建一条新的连接。这样就可以减少连接数据库的开销，从而提高了应用程序的性能。默认情况下是启用连接池的。

四、Command

SqlCommand类中的重要属性：

• Connection：SqlCommand对象使用的SqlConnection
• CommandText：获取或设置要执行的T-SQL语句或存储过程（使用存储过程的前提是数据库中已创建好）
• CommandType：一个枚举类型。Command.Text—执行的是一个Sql语句（默认为这个）；Command.StoreProcedure—执行的是一个存储过程
• Parameter：Sqlcommand对象的命令参数集合
• Transaction：获取或设置要在其中执行的事务

五、ExecuteNonQuery

执行T-SQL语句或存储过程，并返回受影响的行数。命令类型：插入、更新和删除。

        static void Main(string[] args)
        {
            string connectString = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;
            using (SqlConnection connection = new SqlConnection(connectString))
            {
                Task task = connection.OpenAsync();//异步连接

                task.Wait();

                Console.WriteLine($"connection.State:{connection.State}");

                DateTime date = DateTime.Today;
                string cnname = "张含韵";
                string engname = "zhanghanyun";
                decimal salary = 10000.99m;
                int proid = 2;
                //string sql = $"insert into PersonInfos values('{date}','{cnname}','{engname}','{salary}','{proid}')";
                //string sql = "delete from PersonInfos where id=6";
                string sql = "update PersonInfos set cnname = '张含韵', salary = 0 where id = 5";
                SqlCommand command = new SqlCommand(sql, connection);
                int count = command.ExecuteNonQuery();
                Console.WriteLine(count);
            }
        }

上面示例使用的拼接式SQl，有一个致命弱点：SQL注入。值得一提的是连接的使用原则：最晚打开，最早关闭。

六、ExecuteScalar

返回查询结果集中的第一行第一列的值（object类型），忽略其他行或列。使用与查询返回的值、记录数或数据运算而出的结果。

        static void Main(string[] args)
        {
            string connectString = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;
            using (SqlConnection connection = new SqlConnection(connectString))
            {
                Task task = connection.OpenAsync();//异步连接

                task.Wait();

                Console.WriteLine($"connection.State:{connection.State}");
                string sql = "select count(1) from PersonInfos where salary>20000";
                SqlCommand command = new SqlCommand(sql, connection);

                object o = command.ExecuteScalar();
                
                Console.WriteLine((int)o);
            }
        }

注意，统计有多少行数据的时候，可以查询一个“伪列”：1，然后计算数量，这样效率较高。

七、ExecuteReader

返回一个对象：SqlDataReader，是一个实时读取的数据流。使用于小数据量，如果大数据量需要一直连接。

        static void Main(string[] args)
        {
            string connectString = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;
            using (SqlConnection connection = new SqlConnection(connectString))
            {
                Task task = connection.OpenAsync();//异步连接

                task.Wait();

                Console.WriteLine($"connection.State:{connection.State}");

                string sql = "select * from PersonInfos";
                SqlCommand command = new SqlCommand(sql, connection);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())//是否可以前进到下一条数据
                    {
                        DateTime date = DateTime.Parse(reader["birthday"].ToString());
                        int id = int.Parse(reader["id"].ToString());
                        string name = reader["cnname"].ToString();
                        decimal salary = decimal.Parse(reader["salary"].ToString());
                        Console.WriteLine($"{date}\t{id}\t{name}\t{salary}");
                    }
                }
            }
        }

八、SqlDataAdapter

SqlDataAdapter是数据访问适配器，可以将数据一次性加载到内存中，同样也不适用于大量数据。SqlDataAdapter是对DataReader的又一次封装。

        static void Main(string[] args)
        {
            string connStr = @"server=DESKTOP-P7UORUH\SQLEXPRESS;database=TestDB;uid=heater;pwd=123456";

            using (SqlConnection connection=new SqlConnection(connStr))
            {
                connection.Open();
                Console.WriteLine($"connection.State:{connection.State}");

                string sql = "select * from PersonInfos";
                SqlCommand command = new SqlCommand(sql, connection);

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
            }
        }

九、SqlParameter

参数化Sql语句，因为拼接Sql语句太危险且不方便使用单引号。使用参数化SQL语句可以解决SQL注入和引号的问题。

    static void Main(string[] args)
    {
        string connectString = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;
        using (SqlConnection connection = new SqlConnection(connectString))
        {
            Task task = connection.OpenAsync();//异步连接

            task.Wait();

            Console.WriteLine($"connection.State:{connection.State}");

            string sql = "select avg(salary) from PersonInfos where salary>@salary";
            SqlCommand command = new SqlCommand(sql, connection);

            command.Parameters.AddWithValue("@salary",10000.00m);

            var count = command.ExecuteScalar();

            Console.WriteLine(count);
        }
    }c

Sql语句中参数名使用@标记，然后还需要给参数添加值。

下面示例为一个使用存储过程执行的命令，并且存储过程中包含输入参数和输出参数。存储过程为：

create proc GetProvinceName
@id int, --默认为输入参数
@ProName nvarchar(20) output --输出参数
as
begin
  select @ProName=ProName from ProvinceInfos where proId=(select proId from PersonInfos where id=@id )
end

        static void Main(string[] args)
        {
            string connectString = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;
            using (SqlConnection connection = new SqlConnection(connectString))
            {
                Task task = connection.OpenAsync();//异步连接

                task.Wait();

                Console.WriteLine($"connection.State:{connection.State}");


                SqlCommand command = new SqlCommand("GetProvinceName", connection)//存储过程名称
                {
                    CommandType = CommandType.StoredProcedure,
                };//使用存储过程来执行
                command.Parameters.AddWithValue("@id",2);//需要与存储过程中的输入参数名称一致。默认为输入参数。

                SqlParameter proName = new SqlParameter("@ProName", SqlDbType.NVarChar,20) 
                {
                    Direction=ParameterDirection.Output,
                };//这个参数为输出参数，需要显示指明
                command.Parameters.Add(proName);

                var count = command.ExecuteNonQuery();

                Console.WriteLine(count);
                Console.WriteLine(proName.Value.ToString());
            }
        }