学习前提：完成C#语法和SQL

复习：持久化：ADO&JDBC：driver / 连接 / SQL注入 / 事务 / 批处理 / 结果集 / 连接池 

ADO全称：ActiveX Data Objects，是微软推出的一系列可操作数据库的对象。

其中基于.NET平台的，又被称之为ADO.NET。是 Linq to SQL 面世之前，连接数据库的惯常办法，也是 Entity Framework 的底层实现工具。

添加引用

在项目中使用的NuGet（复习）添加可连接SQL Server的：

连接不同的数据库，需要不同的package（dll类库）

演示：mysql的provider安装：

Connection对象

操作数据库，首先需要打开数据库连接：

string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=17bang;Integrated Security=True;";
//using (IDbConnection connection = new SqlConnection(connectionString))
using (IDbConnection connection = new MySqlConnection(connectionString))
{
    connection.Open();  //需要显式的Open()
                        //进行其他操作

连接字符串

通常我们不会手写连接字符串（error prone），而是从VS已连接的数据库属性中查找并复制粘贴（演示：略）

mysql的连接字符串格式为：

server=localhost;Uid=root;database=17bang;pwd=1234;Charset=utf8

继承层次

SqlConnection继承自DbConnection，DbConnection实现了IDbConnection （演示：F12查看，略）

不同的数据库，有DbConnection不同的实现。

@想一想@：

• 为什么要抽象出一个DbConnection？
• 为什么我们能使用using？

生成connection对象之后，不要忘了显式的Open()

但因为using会调用Dispose()，SqlConnection的Dispose()中已调用Close()，所以可以省略Close()调用。

#常见面试题：比较以下三种“清理”connection方式的区别#

演示：connection.State

Command对象

SqlCommand对象继承自DbCommand，实现IDbCommand，可以直接new出来：

IDbCommand command = new SqlCommand();

Command对象中必须要有依赖：

• connection对象：指定Command运行在哪个Connection上
• sql语句：指定Command的运行内容

command.Connection = connection;//connection可以是IDbConnnection声明的
command.CommandText = @$"SELECT Count([Id]) FROM [Student] WHERE [Name] = {name}";

还可以直接使用SqlCommand的构造函数：

DbCommand getUserByName = new SqlCommand(
    $"SELECT Count([Id]) FROM [Student] WHERE [Name] = N'{UserName}'",
    connection);

但connection参数只接受SqlConnection对象。

Command执行，需要调用ExcuteX()方法，具体包括：

ExecuteNonQuery()

一般用于执行非查询的SQL语句，增删改都可以：

IDbCommand enroll = new SqlCommand(
        $"UPDATE Student SET Enroll = '{DateTime.Now}' WHERE Id = 1"
    );

int rows = command.ExecuteNonQuery();

返回的是：受影响的函数。

ExecuteScalar()

用于执行查询（SELECT）的SQL语句，但只取返回结果集中第一行第一列的值，常用于取SELECT COUNT(*)等聚合函数的值：

object id = (int)getUserByName.ExecuteScalar();

返回的值会被装箱成object（复习）

@想一想@：如何区分（复习：SQL中的EXISTS）：

• 没有返回行
  
• 返回一行，列值为NULL

为此，ADO.NET引入了DBNull：代表从数据库取到了值，但是值为NULL。比较：

object result = new SqlCommand(
    queryString, (SqlConnection)connection).ExecuteScalar();

Console.WriteLine(result == null);
Console.WriteLine(result == DBNull.Value);

返回Identity值

很多时候，我们需要数据插入之后的自增长Id。

如何在Insert操作完成的同时获得该值呢？这就需要用到在SQL语句中添加

SELECT @@IDENTITY;

所以在ADO.NET中代码如下所示：

    command.CommandText =
        "INSERT Teacher([Name]) VALUES(N'源栈小飞哥');" +
        "SELECT @@IDENTITY";
    Console.WriteLine(command.ExecuteScalar());

#体会#：ExecuteX()方法的区别在于返回。

ExecuteReader()

一样是执行查询语句时使用，但它会返回一个

DataReader对象

该对象和集合的迭代器很类似：

IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(
        $"Id={reader[0]},Name={reader["Name"]}");

上述代码，通过循环调用Read()方法，读取全部结果集。

1. 每调用一次Read()，就读取一行数据
2. 其返回值
   1. 为true，表示已经读到数据；下次调用Read()方法，读取的是下一行
   2. 为false，没有读到数据，已是最后一行

而reader对象，可以通过索引器获取列值。索引值可以是列名，也可以是列的序号。

#体会#：面向接口编程，

演示：reader变量中存放的是SqlDataReader对象

需要注意：

1. 整个过程，数据库连接不能关闭。（@想一想@：connection怎么可能关闭呢？）
2. ExecuteReader()方法并不能将查询结果集直接存放到Reader中，每一次Read()方法调用，都是从数据库查询结果缓冲区（buffer）中获得的数据

Parameter对象

为避免SQL注入，不应该像之前那些拼接SQL语句。

而是应该使用参数化查询，按如下方式准备SQL语句：

IDbCommand command = new SqlCommand(
    $"UPDATE Student SET Enroll = '{DateTime.Now}' " +
    $"WHERE [Name] = @name"   //不要忘记 @ 符号
);

参数需要使用IDataParameter：

//为@name准备好值
IDataParameter pName = new SqlParameter("@name", "源栈小飞哥");
command.Parameters.Add(pName);
//还可以继续添加其他参数

Stored Procedure

可以在Command对象上指定其类型（CommandType）为StoredProcedure：（默认是Text）

command.CommandType = CommandType.StoredProcedure;

然后，其CommandText值就是Stored Procedure的名称：（不是SQL语句）

command.CommandText = "GetExcellentStudents";

• 像参数化查询一样，向command中传参数 （DEFAULT可以直接省略）
  
• 根据Stored Procedure的运行结果，选择ExecuteX()
  

但注意存储过程的结果，可以是：

OUTPUT Parameter 和 ReturnValue

IDataParameter pCount = new SqlParameter("@count", SqlDbType.Int) 
{ 
    Direction = ParameterDirection.Output
};

在运行存储过程之后，获得其Value：

Console.WriteLine($"pCount.Value = {pCount.Value}");

T-SQL中存储过程还可以有return值，但获值方式也一样要利用Parameter。

将ParameterDirection设置成ReturnValue，可以不指定parameter的Name

IDataParameter pCount = new SqlParameter
{ 
    Direction = ParameterDirection.ReturnValue
};

SELECT结果集

对存储过程的command调用ExcuteReader()方法，一样可以获得DataReader。

但这样就不能获得OUTPUT Parameter的值——这是by design的。

其实我们在写存储过程的时候，就不应该混用SELECT和OUTPUT Parameter。

Transaction

ADO.NET提供了事务支持，需要在connection上开启。

PS：ADO.NET的事务支持需要由数据库事务机制支持，ADO.NET本身没有提交/回滚的功能。

而且，需要在Command指定Transaction，在同一事务上的多个Command才在同一事务控制范围内。

connection一样，建议总是使用using包裹事务，以免其无法Dispose():

using (IDbTransaction transaction = connection.BeginTransaction())
{
    try
    {
        //共用一个command
        command.Transaction = transaction;//仍然需要指定链接 
        //能够正常执行
        command.CommandText = "UPDATE Student SET Age -= 1;";
        command.ExecuteNonQuery();

        //会违反Age>0的约束
        command.CommandText = "UPDATE Student SET Age -= 108;";
        command.ExecuteNonQuery();

        transaction.Commit();
    }
    catch (Exception)
    {
        transaction.Rollback();

在事务中，没有异常，直接提交；出现异常，予以回滚

但如果异常时只是回滚的话，可以不用catch，因为若有异常Dispsoe()时会自动回滚事务。

注意IDbTransaction是基于单个连接对象，所以无法应用于：

分布式事务

如果数据库是分布式的，即一个事务中要涉及到多个数据库连接，要想把他们控制在一个事务当中，就需要

using (TransactionScope scope = new TransactionScope())
{
    //执行基于多个connection的SQL命令
    //然后提交
    scope.Complete();

理论上，即使单个数据库也可以使用TransactionScope，但不是必须的情况，不建议引入分布式事务。因为：

1. 没有多数据库共同的接口，不利于代码迁移
2. 总是有一点额外的性能负担

作业

见：持久化：ADO&JDBC：driver / 连接 / SQL注入 / 事务 / 批处理 / 结果集 / 连接池

在当前系列 ADO&EF 中继续学习：

多快好省！前端后端，线上线下，名师精讲

• 先学习，后付费；
• 不满意，不要钱。
• 编程培训班，我就选源栈

更多了解 加：

QQ群：273534701

答疑解惑，远程debug……

B站 源栈-小九 的直播间

写代码要保持微笑 (๑•̀ㅂ•́)و✧