键盘敲烂,月薪过万作业不做,等于没学
当前系列: ADO&EF 修改讲义

学习前提:完成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.Close()
关闭连接。但连接仍然存在,且连接上的各种配置仍然存在,可以再次Open()
connection.Dispose()
会自动调用Close()关闭连接,且释放连接上的配置,也就无法再次Open()
connection=null
使得connection变量指向的连接对象能被垃圾回收,连接会在垃圾回收时才会被Dispose()

演示: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

如果我们要想获得OUTPUT Parameter值,就需要指定它的Direction(默认是INPUT的)
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();

和IDbTransaction一样,TransactionScope会在Dispose()时自动回滚事务(如果没有提交的话)

该事务的实现,一样不是由ADO.NET完成的,而是需要在多个SQL Server数据库上
启用MSDTC分布式事务服务——所以它不是通用的(TransactionScope上面没有什么ITransactionScope……)

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

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


作业

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

学习笔记
源栈学历
大多数人,都低估了编程学习的难度,而高估了自己的学习能力和毅力。

作业

觉得很 ,不要忘记分享哟!

任何问题,都可以直接加 QQ群:273534701

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

上一课: 已经是第一课了……

多快好省!前端后端,线上线下,名师精讲

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

更多了解 加:

QQ群:273534701

答疑解惑,远程debug……

B站 源栈-小九 的直播间

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

公众号:源栈一起帮

二维码