标签：using 数据库 cmd new connection SqlConnection Ado Net SqlParameter

1. 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 中进行资料存取。

现阶段我们以SqlServer 为案例进行讲解。

Ado.Net 优点

执行效率高。任何ORM框架（对象关系映射，理解为一种可提高访问数据库开发效率的一种框架），在Ado.net 面前都是弟弟， ado.net 作为原装的直接跟数据库打交道，直接操作数据库，没有进行额外的封装。比如我们可以直接执行sql语句，直接调用存储过程。直接操作DataSet数据集等等数据。

缺点：

开发效率偏慢
缺乏面向对象思想

2. Connection 连接对象

Connection 是数据库连接对象，用于与数据库建立连接。SqlServer 连接类是SqlConnection , MySql 的连接类是MySqlConnection, Oracle 数据库连接类是 OracleConnection ，这些类都实现了IDbConnection接口。

1. SqlConnection 类

命名空间: System.Data.SqlClient
程序集: System.Data.SqlClient.dll

通过Nuget 将 System.Data.SqlClient 程序集引用到项目中

构造方法:

SqlConnection()	初始化 SqlConnection 类的新实例。
SqlConnection(String)	如果给定包含连接字符串的字符串，则初始化 SqlConnection 类的新实例。
SqlConnection(String, SqlCredential)	在给定连接字符串的情况下，初始化 SqlConnection 类的新实例，该连接字符串不使用 `Integrated Security = true` 和包含用户 ID 和密码的 SqlCredential 对象。

属性：

属性名	描述
ConnectionString	连接字符串
State	只读，连接状态
Database	只读，当前连接的数据库
ServerVersion	只读，当前客户端连接的SQLServer的版本
ConnectionTimeout	尝试建立连接时终止尝试并生成错误之前所等待的时间（以秒为单位）,默认15秒

常用方法：

方法名	描述
Open()	使用由 ConnectionString 指定的属性设置打开一个数据库连接。
Close()	关闭与数据库之间的连接。此方法是关闭任何打开连接的首选方法。
Dispose()	执行与释放或重置非托管资源关联的应用程序定义的任务。
BeginTransaction()	开始数据库事务。
BeginTransaction(IsolationLevel)	以指定的隔离级别启动数据库事务。
BeginTransaction(IsolationLevel,string)	以指定的隔离级别和事务名称启动数据库事务。
CreateCommand()	创建并返回与 SqlConnection 关联的 SqlCommand 对象。

使用无参构造：

SqlConnection connection = new SqlConnection();
connection.ConnectionString = "server=.;uid=sa;pwd=123456;database=unit21";
connection.Open();// 打开连接

Console.WriteLine($"State={connection.State}");
Console.WriteLine($"DataBase={connection.Database}");
Console.WriteLine($"ServerVersion={connection.ServerVersion}");
Console.WriteLine($"DataSource={connection.DataSource}");
Console.WriteLine($"ConnectionTimeout={connection.ConnectionTimeout}");

connection.Close();// 一定要记得关闭连接

输出结果：

State=Open
DataBase=unit21
ServerVersion=14.00.1000
DataSource=.
ConnectionTimeout=15

有参构造

SqlConnection connection =
new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21");
connection.Open();// 打开连接

Console.WriteLine($"State={connection.State}");
Console.WriteLine($"DataBase={connection.Database}");
Console.WriteLine($"ServerVersion={connection.ServerVersion}");
Console.WriteLine($"DataSource={connection.DataSource}");
Console.WriteLine($"ConnectionTimeout={connection.ConnectionTimeout}");

connection.Close();// 一定要记得关闭连接

2. 修改超时时间

SqlConnection connection =
new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21;timeout=30;");
connection.Open();
Console.WriteLine($"ConnectionTimeout={connection.ConnectionTimeout}");
connection.Close();

输入结果：

ConnectionTimeout=30

3. SqlCredential 类

提供了更安全的方式来指定使用 SQL Server 身份验证尝试登录的密码。由用户 ID 和将用于 SQL Server 身份验证的密码构成。 SqlCredential 对象中的密码是 SecureString 类型(一种加密文本)。

如果调用ChangePassword() 方法修改SqlCredential对象时将会影响连接池，因为不对的SqlCredential 实例将使用不同的连接池，即使用户 ID 和密码相同，也是如此。

如果SqlCredential 对象非空并且连接字符串中出现如下关键字，将会引发 InvalidOperationException 异常：

Integrated Security = true
Password
User ID
Context Connection = true

winform 案例：

System.Windows.Controls.TextBox txtUserId = new System.Windows.Controls.TextBox();  
System.Windows.Controls.PasswordBox txtPwd = new System.Windows.Controls.PasswordBox();  


SqlConnection conn = new SqlConnection("server=.;database=unit21;");
SecureString pwd = txtPwd.SecurePassword;  
pwd.MakeReadOnly();  
SqlCredential cred = new SqlCredential(txtUserId.Text, pwd);  
conn.Credential = cred;  
conn.Open();  
// ...
conn.close();

3. 自动释放（推荐）

using

实现了IDispose 接口的类，都可以使用using 来实现自动释放资源功能。

/* *
 * using : 引用命名空间
 * using: 释放资源
 *
 * 1. 为什么以前我们定义的变量不需要释放资源呢？
 * GC: 垃圾回收器，这里面封装了一些算法规定了什么时候去回收资源。我们自己定义一些变量，不需要关心它有没有释放掉，因为GC会帮我们自动回收资源
 *
 * 2. 既然已经有了GC,为什么还需要手动的释放资源呢？
 * 答：GC 只能回收托管资源（由.Net CLR管理的资源），而SqlConnection是属于非托管资源
 *
 * 3. 我怎么知道它是非托管？
 * 答：如果它实现了IDisposable 接口，那么我们就可以使用using 来手动的释放资源了
 */

using (SqlConnection connection = new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21;timeout=30"))
{
    connection.Open();// 打开连接
    Console.WriteLine($"State={connection.State}");
    Console.WriteLine($"DataBase={connection.Database}");
    Console.WriteLine($"ServerVersion={connection.ServerVersion}");
    Console.WriteLine($"DataSource={connection.DataSource}");
    Console.WriteLine($"ConnectionTimeout={connection.ConnectionTimeout}");
} // 并未调用close()方法，但会自动关闭

C# 7.0 以后的语法：

using SqlConnection connection = new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21;timeout=30");
connection.Open();// 打开连接
Console.WriteLine($"State={connection.State}");
Console.WriteLine($"DataBase={connection.Database}");
Console.WriteLine($"ServerVersion={connection.ServerVersion}");
Console.WriteLine($"DataSource={connection.DataSource}");
Console.WriteLine($"ConnectionTimeout={connection.ConnectionTimeout}");

connection 会在它当前的代码块结束处（作用域的结尾）自动调用关闭连接代码。

4. Command 命令对象

Command 是数据库命令对象，它是数据库执行的一个 Transact-SQL 语句或存储过程。SqlServer 连接类是SqlCommand , MySql 的连接类是MySqlCommand, Oracle 数据库连接类是 OracleCommand ，这些类都实现了IDbCommand接口。

1. SqlCommand 类

命名空间: System.Data.SqlClient
程序集: System.Data.SqlClient.dll

构造方法：

SqlCommand()	初始化 SqlCommand 类的新实例。
SqlCommand(String)	使用查询的文本初始化 SqlCommand 类的新实例。
SqlCommand(String, SqlConnection)	使用查询的文本和 SqlConnection 初始化 SqlCommand 类的新实例。
SqlCommand(String, SqlConnection, SqlTransaction)	使用查询文本、SqlConnection 以及 SqlTransaction 初始化 SqlCommand 类的新实例。

常用属性：

CommandText	获取或设置要在数据源中执行的 Transact-SQL 语句、表名或存储过程。
CommandTimeout	获取或设置在终止尝试执行命令并生成错误之前的等待时间（以秒为单位）。
CommandType	获取或设置一个值，该值指示解释 CommandText 属性的方式。
Connection	获取或设置 SqlCommand 的此实例使用的 SqlConnection。
Parameters	获取 SqlParameterCollection。
Transaction	获取或设置要在其中执行 SqlTransaction 的 SqlCommand。

常用方法：

方法名	描述
Cancel()	尝试取消 SqlCommand 的执行。
CreateParameter()	创建 SqlParameter 对象的新实例。
Dispose()	执行与释放或重置非托管资源关联的应用程序定义的任务。(继承自 DbCommand)
Dispose(Boolean)	释放由 DbCommand 占用的非托管资源，还可以另外再释放托管资源。(继承自 DbCommand)
ExecuteNonQuery()	对连接执行 Transact-SQL 语句并返回受影响的行数。
ExecuteScalar()	执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
ExecuteReader()	将 CommandText 发送到 Connection，并生成 SqlDataReader。

案例1：

using SqlConnection connection = new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21;timeout=30");
connection.Open();
using SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandText = $"insert into product values('枸杞',18)";
cmd.ExecuteNonQuery();// 执行

案例2：

using SqlConnection connection = new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21;timeout=30");
connection.Open();
using SqlCommand cmd = new SqlCommand("select count(id) from product");
cmd.Connection = connection;
object count = cmd.ExecuteScalar();// 获取首行首列

案例3：

using SqlConnection connection = new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21;timeout=30");
connection.Open();
using SqlCommand cmd = new SqlCommand("select * from product",connection);
using SqlDataReader dataReader = cmd.ExecuteReader(); // 返回一个读取对象

5. SqlDataReader 数据读取

提供一种从 SQL Server 数据库中读取只进的行流的方式。在读取数据的过程中需要一直与数据库保持连接，适合数据量小的情况，执行效率还是可以的。我们将SqlDataReader 读取数据的方式称为连接模式。

命名空间: System.Data.SqlClient
程序集: System.Data.SqlClient.dll

属性

Connection	获取与 SqlConnection 关联的 SqlDataReader。
Depth	获取一个值，用于指示当前行的嵌套深度。
FieldCount	获取当前行中的列数。
HasRows	获取一个值，该值指示 SqlDataReader 是否包含一行还是多行。
IsClosed	检索一个布尔值，该值指示是否已关闭指定的 SqlDataReader 实例。
Item[Int32]	在给定列序号的情况下，获取指定列的以本机格式表示的值。
Item[String]	在给定列名称的情况下，获取指定列的以本机格式表示的值。
RecordsAffected	获取执行 Transact-SQL 语句所更改、插入或删除的行数。
VisibleFieldCount	获取 SqlDataReader 中未隐藏的字段的数目。

方法

Close()	关闭 DbDataReader 对象。
Dispose()	释放 DbDataReader 类的当前实例所使用的所有资源。
GetName(Int32)	获取指定列的名称。
GetOrdinal(String)	在给定列名时获取相应的列序号。
Read()	让 SqlDataReader 前进到下一条记录,返回bool。

还有很多方法，他们都是根据列序列号获取指定列的功能，如：GetInt(int index),GetFloat(int index),GetDateTime(int index),GetString(int index) .....

案例

案例1：

using SqlConnection connection = new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21;timeout=30");
connection.Open();
using SqlCommand cmd = new SqlCommand("select * from product",connection);
using SqlDataReader dataReader = cmd.ExecuteReader();

while (dataReader.Read())
{
    object id = dataReader.GetValue("id");
    object pname = dataReader.GetValue("pname");
    object price = dataReader.GetValue("price");

    Console.WriteLine($"id: {id}\t pname: {pname}\t price: {price}\t \n");
}

案例2，通过dataReader 索引读取：

using SqlConnection connection = new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21;timeout=30");
connection.Open();
using SqlCommand cmd = new SqlCommand("select * from product",connection);
using SqlDataReader dataReader = cmd.ExecuteReader();

while (dataReader.Read())
{
    // 索引下标顺序必须与数据库列顺序一致
    object id = dataReader[0];
    object pname = dataReader[1];
    object price = dataReader[2];

    Console.WriteLine($"id: {id}\t pname: {pname}\t price: {price}\t \n");
}

案例3，封装成对象:

using SqlConnection connection = new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21;timeout=30");
connection.Open();
using SqlCommand cmd = new SqlCommand("select * from product",connection);
using SqlDataReader dataReader = cmd.ExecuteReader();

while (dataReader.Read())
{
    // 索引下标顺序必须与数据库列顺序一致
    Product product = new Product(
        dataReader.GetInt32(0),
        dataReader.GetString(1),
        dataReader.GetInt32(2)
    );

    Console.WriteLine($"id: {product.Id}\t pname: {product.Name}\t price: {product.Price}\t \n");
}

record Product(int Id,string Name,int Price);

6. SqlDataAdapter 适配器

适配器详细介绍

命名空间: System.Data.SqlClient
程序集: System.Data.SqlClient.dll

表示用于填充 DataSet （表示数据的内存中的数据库，它可以由多个table组成）和更新 SQL Server 数据库的一组数据命令和一个数据库连接。适配器模式也被称为断开模式。

一次连接取得数据之后，即可断开，在用户非常多的情况下，不会占用太多的连接池资源。还有一点，就是一次性的从数据库中取得了数据之后，这些数据是存在内存中的，而不会再去操作数据库，所以你对这些数据做任何的操作，都只是修改内存，不会改变数据库中的内容。

构造方法：

SqlDataAdapter()	初始化 SqlDataAdapter 类的新实例。
SqlDataAdapter(SqlCommand)	初始化 SqlDataAdapter 类的新实例，用指定的 SqlCommand 作为 SelectCommand 的属性。
SqlDataAdapter(String, SqlConnection)	使用 SqlDataAdapter 和 SelectCommand 对象初始化 SqlConnection 类的一个新实例(用得最多)。
SqlDataAdapter(String, String)	用 SqlDataAdapter 和一个连接字符串初始化 SelectCommand 类的一个新实例。

属性：

DeleteCommand	获取或设置一个 Transact-SQL 语句或存储过程，以从数据集删除记录。
InsertCommand	获取或设置一个 Transact-SQL 语句或存储过程，以在数据源中插入新记录。
SelectCommand	获取或设置一个 Transact-SQL 语句或存储过程，用于在数据源中选择记录。
UpdateBatchSize	获取或设置每次到服务器的往返过程中处理的行数。
UpdateCommand	获取或设置一个 Transact-SQL 语句或存储过程，用于更新数据源中的记录。

案例1：

using SqlConnection connection =
    new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21");

using SqlDataAdapter adapter = new SqlDataAdapter();
using SqlCommand cmd = new SqlCommand("select * from product");
cmd.Connection = connection;
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();
adapter.Fill(ds); // 填充数据
Console.WriteLine(ds.Tables[0].Rows.Count);

案例2：

using SqlConnection connection =
    new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21");
using SqlCommand cmd = new SqlCommand("select * from product",connection);
using SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
Console.WriteLine(ds.Tables[0].Rows.Count);

案例3：

using SqlConnection connection =
    new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21");
using SqlDataAdapter adapter = new SqlDataAdapter("select * from product",connection);
DataSet ds = new DataSet();
adapter.Fill(ds);
Console.WriteLine(ds.Tables[0].Rows.Count);

案例4：

using SqlDataAdapter adapter2 = new SqlDataAdapter("select * from product",
    "server=.;uid=sa;pwd=123456;database=unit21");
DataSet ds = new DataSet();
adapter2.Fill(ds);
Console.WriteLine(ds.Tables[0].Rows.Count);

将DataTable封装成List 集合

通常我们对List操作比较多。

DataTable ：内存中的数据表。

/**
 * 为什么要使用List
 * 1. List 更符合面向对象编程思想
 * 2. row 在操作列名的时候，有可能会将列名写错
 * 3. List集合操作速度要比DataTable要快，并且属于类型安全（C#高级中会有专门一个单元，给大家讲解泛型）
 */
using SqlDataAdapter adapter2 = new SqlDataAdapter("select * from product",
    "server=.;uid=sa;pwd=123456;database=unit21");
DataTable dataTable = new DataTable();
adapter2.Fill(dataTable);

List<Product> list = new ();
foreach (DataRow row in dataTable.Rows)
{
    Product product = new();
    product.Id = Convert.ToInt32(row["id"]);
    if (row["pname"] is not DBNull) // 判断非空
    {
        product.ProductName = row["pname"].ToString();
    }
     // 数字类型的一定要记得做判断
    if (row["price"] is not DBNull)
    {
        product.Price = Convert.ToDecimal(row["price"]);
    }
    list.Add(product);
}
Console.WriteLine(list.Count);


public class Product
{
    public int Id { get; set; }
    public string? ProductName { get; set; }
    public decimal Price { get; set; }
}

构建DataTable

DataTable dataTable = new DataTable("商品表");

// 创建表头
dataTable.Columns.Add("id");
dataTable.Columns.Add("productname");
DataColumn column = new DataColumn("price");
dataTable.Columns.Add(column);

//创建行
var dataRow = dataTable.NewRow();
dataRow["id"] = 1;
dataRow["productname"]="口红";
dataRow["price"] = 269;

// 添加到DataTable中
dataTable.Rows.Add(dataRow);

7. 配置文件

随着服务器的变更、密码的修改等不确定因素，导致连接字符串很有可能会发生变化，如果将连接字符串产用硬编码的形式写入代码中，会导致项目频繁的部署，影响用户的体验。我们更推荐使用配置文件的形式进行存储连接字符串。

添加引用：

System.Configuration.ConfigurationManager

添加配置文件

在控制台项目中，配置文件名称必须叫 App.config。

读取连接字符串

在配置文件中添加连接字符串配置

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
   <connectionStrings>
      <add name="connString" connectionString="server=.;uid=sa;pwd=123456;database=unit21" providerName="System.Data.SqlClient"></add>
   </connectionStrings>
</configuration>

providerName 可不写，默认为 System.Data.SqlClient。

读取配置文件

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

// 读取配置文件
var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;

using SqlDataAdapter adapter = new SqlDataAdapter("select * from product",connectionString);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);

Console.WriteLine(dataTable.Rows.Count);

读取AppSettings 内容

有时候配置文件中不仅需要配置连接字符串，还有可能需要配置其他一些可变的内容，此时我们可以使用AppSettings 节点来进行配置。

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add key="siteName" value="任我行码农场"/>
    </appSettings>
</configuration>

读取app.config 文件中的appsetting 节点：

var siteName = ConfigurationManager.AppSettings["siteName"].ToString();
Console.WriteLine(siteName);

8. 数据连接池

知道现在编程什么是最宝贵的？数据库连接，它其实是很耗费资源的。这里我们就连接池这个问题我们来一起讨论讨论吧。数据库连接池是为了更有效地利用数据库连接的最重要措施。它对于一个大型的应用系统的性能至关重要，特别是Web应用。ADO.NETData Provider（以下简称Data Provider）会帮我们管理连接池，所以有人说使用连接池就像游儿童池一样轻松。但并不是说有了Data Provider程序员就万事无忧的，不正确地使用连接池可能导致你的应用在池里淹死。

什么是ADO.net连接池？

ADO.Net连接池是Data Provider提供的一个机制，使得应用程序使用的连接保存在连接池里而避免每次都要完成建立/关闭连接的完整过程。要理解连接池，先要理解程序里SqlConnection.Open()、SqlConnection.Close()和打开/关闭一个“物理连接”的关系。

Data Provider在收到连接请求时建立连接的完整过程是：先连接池里建立新的连接（即“逻辑连接”），然后建立该“逻辑连接”对应的“物理连接”。建立“逻辑连接”一定伴随着建立“物理连接”。Data Provider关闭一个连接的完整过程是先关闭“逻辑连接”对应的“物理连接”然后销毁“逻辑连接”。销毁“逻辑连接”一定伴随着关闭“物理连接”。SqlConnection.Open()是向Data Provider请求一个连接，Data Provider不一定需要完成建立连接的完整过程，可能只需要从连接池里取出一个可用的连接就可以；SqlConnection.Close()是请求关闭一个连接，Data Provider不一定需要完成关闭连接的完整过程，可能只需要把连接释放回连接池就可以。

性能对比

非连接池下取数据

在连接字符串中加入配置,关闭数据连接池：pooling=false

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
   <connectionStrings>
      <add name="connString" connectionString="server=.;uid=sa;pwd=123456;database=unit21;pooling=false;"></add>
   </connectionStrings>
</configuration>

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
// 读取配置文件
var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
for (int i = 0; i < 10000; i++)
{
     using SqlConnection connection = new SqlConnection(connectionString);
     connection.Open();
}

stopwatch.Stop();
Console.WriteLine("总耗时(s):"+stopwatch.ElapsedMilliseconds/1000.0);


// 输出结果：
总耗时(s):11.897

数据连接池取数据

在连接字符串中加入配置,打开数据连接池：pooling=true。默认情况下，数据连接池是打开的。

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
   <connectionStrings>
      <add name="connString" connectionString="server=.;uid=sa;pwd=123456;database=unit21;pooling=true;"></add>
   </connectionStrings>
</configuration>

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
// 读取配置文件
var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
for (int i = 0; i < 10000; i++)
{
     using SqlConnection connection = new SqlConnection(connectionString);
     connection.Open();
}

stopwatch.Stop();
Console.WriteLine("总耗时(s):"+stopwatch.ElapsedMilliseconds/1000.0);


// 输出结果：
总耗时(s):0.28

9. 参数化查询

create table Users
(
    Id int primary key identity,
    NickName varchar(20),
    Account varchar(20),
    Pwd varchar(20)
);

insert into Users values('张三','zhangsan','123');
insert into Users values('李四','lisi','123');
insert into Users values('王五','wangwu','123');
insert into Users values('任我行','renwoxing','123');
insert into Users values('关莹莹','guanyingying','123');

正常的你正在登录一个网站：

select * from Users where Account='admin' and Pwd = '123'

程序员的你：

select * from Users where Account='admin' and Pwd = '' or 1='1'

SQL注入攻击是互联网常用的一种攻击手段，如何防止？

解决手段：

存储过程
参数化

所谓的参数化，即：将要传递的sql参数不通过拼接的形式进行组装sql语句，而是通过sql变量的形式进行传递。

select count(*) from Users where Account=@account and Pwd = @pwd

SqlParameter 对象

表示 SqlCommand 的参数，参数名称不区分大小写。

构造函数

SqlParameter()	初始化 SqlParameter 类的新实例。
SqlParameter(String, Object)	初始化 SqlParameter 类的新实例，该类使用参数名称和新 SqlParameter 的值。
SqlParameter(String, SqlDbType)	使用提供的参数名称和数据类型初始化 SqlParameter 类的新实例。
SqlParameter(String, SqlDbType, Int32)	使用参数名称、SqlDbType 和大小初始化 SqlParameter 类的新实例。
SqlParameter(String, SqlDbType, Int32, ParameterDirection, Boolean, Byte, Byte, String, DataRowVersion, Object)	使用提供的参数名称、参数类型、参数大小、ParameterDirection、参数精度、参数小数位数、源列、要使用的 DataRowVersion 以及参数值初始化 SqlParameter 类的新实例。
SqlParameter(String, SqlDbType, Int32, ParameterDirection, Byte, Byte, String, DataRowVersion, Boolean, Object, String, String, String)	使用以下信息初始化 SqlParameter 类的新实例：参数名、参数类型、参数长度、参数传递方向、参数精度、参数的范围、源列名、DataRowVersion 的一个值、源列映射的布尔值、`SqlParameter` 的值、此 XML 实例的架构集所在的数据库名称、此 XML 实例的架构集所在的关系型架构，以及此参数的架构集的名称。
SqlParameter(String, SqlDbType, Int32, String)	使用提供的参数名称、SqlDbType、大小和源列名初始化 SqlParameter 类的新实例。

属性

CompareInfo	获取或设置 CompareInfo 对象，它定义应如何对此参数执行字符串比较。
DbType	获取或设置参数的 SqlDbType。
Direction	获取或设置一个值，该值指示参数是只可输入的参数、只可输出的参数、双向参数还是存储过程返回值参数。
IsNullable	获取或设置一个值，该值指示参数是否接受 null 值。 IsNullable 不用于验证参数的值，并且在执行命令时不会阻止发送或接收 null 值。
LocaleId	获取或设置确定某一特定区域的约定和语言设置的区域设置标识符。
Offset	获取或设置 Value 属性的偏移量。
ParameterName	获取或设置 SqlParameter 的名称。
Precision	获取或设置用于表示 Value 属性的最大位数。
Scale	获取或设置所解析的 Value 的小数位数。
Size	获取或设置列中数据的最大大小（字节）。
SourceColumn	获取或设置源列的名称，该源列映射到 DataSet 并用于加载或返回 Value。
SourceColumnNullMapping	获取或设置一个值，该值指示源列是否可以为 null。通过此操作，SqlCommandBuilder 能够为可以为 null 的列正确地生成 Update 语句。
SourceVersion	获取或设置在加载 DataRowVersion 时使用的 Value。
SqlDbType	获取或设置参数的 SqlDbType。
SqlValue	获取或设置作为 SQL 类型的参数的值。
TypeName	获取或设置表值参数的类型名称。
UdtTypeName	获取或设置作为参数的表示用户定义类型的 `string`。
Value	获取或设置参数的值。

详细案例

案例1--登录

// 读取配置文件
var connectionString = ConfigurationManager.ConnectionStrings["localString"].ConnectionString;

using SqlConnection conn = new(connectionString);
conn.Open();
// 一定要记得加@符号
using SqlCommand cmd = new SqlCommand("select count(*) from Users where Account=@account and Pwd = @pwd",conn);

SqlParameter parameter1 = new SqlParameter();
parameter1.ParameterName = "account"; // 参数名,要与 上面的sql语句中的参数名要对应上，@符号可省略
parameter1.Size = 20; // 参数大小
parameter1.SqlDbType = SqlDbType.VarChar; // 类型
parameter1.Value = "zhangsan"; // 参数值

SqlParameter parameter2 = new SqlParameter();
parameter2.ParameterName = "pwd"; // 参数名,要与 上面的sql语句中的参数名要对应上，@符号可省略
parameter2.Size = 20; // 参数大小
parameter2.SqlDbType = SqlDbType.VarChar; // 类型
parameter2.Value = "123"; // 参数值


// 将参数添加至命令对象中
cmd.Parameters.Add(parameter1);
cmd.Parameters.Add(parameter2);

// 执行sql命令
var count = Convert.ToInt32(cmd.ExecuteScalar());
if (count > 0)
{
    Console.WriteLine("登录成功");
}
else
{
    Console.WriteLine("登录失败，用户名或者密码输入不正确");
}

案例2--添加

var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;

using SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
using SqlCommand cmd = new SqlCommand("insert into Users values(@nickName,@account,@pwd);",conn);

// 创建参数数组对象
SqlParameter[] parameters =
{
    new SqlParameter("nickName","李重文"),
    new SqlParameter("account","lichongwen"),
    new SqlParameter("pwd","123")
};
// 批量添加 参数对象数组 至 命令对象中
cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();

数据类型如果 合适，Precision则从参数的值dbType推断出大小。

在参数中value指定时Object，SqlDbType将从 Microsoft .NET Framework 类型的Object推断。

使用此构造函数重 SqlParameter 载指定整数参数值时，请谨慎使用。因为此重载接受 Object 类型的 value，所以当此值为零时，必须将整数值转换为 Object 类型，如下面的 C# 示例所示。

Parameter = new SqlParameter("@pname", (object)0);

如果不执行此转换，编译器假定你正在尝试调用 SqlParameter(String, SqlDbType) 构造函数重载。

案例3--修改

var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
using SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
using SqlCommand cmd = new SqlCommand("update Users set Account=@account,NickName=@nickName,Pwd=@pwd where Id=@id",conn);

// 创建参数数组对象
SqlParameter[] parameters =
{
    new SqlParameter("nickName",SqlDbType.VarChar,20),
    new SqlParameter("account",SqlDbType.VarChar,20),
    new SqlParameter("pwd",SqlDbType.VarChar,20),
    new SqlParameter("id",SqlDbType.Int,4)
};
parameters[0].Value = "张三丰";
parameters[1].Value = "zhangsanfeng";
parameters[2].Value = "123456";
parameters[3].Value = 1;

// 批量添加 参数对象数组 至 命令对象中
cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();

如果未在参数中size显式设置，则从参数的值dbType推断大小。

参数

parameterName String

要映射的参数的名称。
dbType SqlDbType

SqlDbType 值之一。
size Int32

参数的长度。

案例4--查询

var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
// 创建参数数组对象
SqlParameter[] parameters =
{
    new SqlParameter("nickName",SqlDbType.VarChar),
    new SqlParameter("pwd",SqlDbType.VarChar)
};
Console.WriteLine("请输入需要查询的姓名：");
string? nickName = Console.ReadLine();
parameters[0].Value = $"%{nickName}%"; // 模糊查询要加%
parameters[1].Value = "123456";

SqlDataAdapter adapter = new("select * from Users where NickName like @nickName and Pwd=@pwd",connectionString);
// 批量添加参数对象
adapter.SelectCommand.Parameters.AddRange(parameters);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);

Console.WriteLine("\nId\t姓名\t账号 \t \t密码");
Console.WriteLine("------------------------------------------------------");
foreach (DataRow row in dataTable.Rows)
{
    Console.WriteLine($"{row["id"]}\t{row["nickname"]}\t{row["account"]}\t{row["pwd"]}");
}

输入结果：

请输入需要查询的姓名：
张

Id      姓名    账号            密码
------------------------------------------------------
1       张三丰  zhangsanfeng    123456

如果未在参数中size显式设置，则从参数的值dbType推断大小。

案例5--删除

var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;

using SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
using SqlCommand cmd = new SqlCommand("delete from Users where id=@id;",conn);

// 指添加 参数对象 命令对象中
cmd.Parameters.Add(new SqlParameter("id",1));
cmd.ExecuteNonQuery();

案例6--批量删除

批量删除的sql：

delete from Users where id in(1,2)

但如果使用参数化来实现批量功能，只能通过循环单删操作实现。

var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;

using SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
using SqlCommand cmd = new SqlCommand("delete from Users where id=@id;",conn);

// 需要删除的数据
int[] idList = {1, 2, 3};
foreach (var id in idList)
{
    // 指添加 参数对象数组 至 命令对象中
    cmd.Parameters.Add(new SqlParameter("id",id));
    cmd.ExecuteNonQuery();
    cmd.Parameters.Clear(); // 必须清除掉之前的参数
}

案例6--事务

var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;

using SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
using SqlTransaction tran = conn.BeginTransaction(); // 开启事务
// 命令对象指定事务对象
using SqlCommand cmd = new SqlCommand("delete from Users where id=@id;",conn,tran);

try
{
    // 需要删除的数据
    int[] idList = {4, 5, 6};
    foreach (var id in idList)
    {
        // 指添加 参数对象数组 至 命令对象中
        cmd.Parameters.Add(new SqlParameter("id",id));
        cmd.ExecuteNonQuery();
        cmd.Parameters.Clear(); // 必须清除掉之前的参数
    }
    tran.Commit();// 提交事务
}
catch (Exception e)
{
    Console.WriteLine(e.Message);
    tran.Rollback(); // 数据回滚
}

案例7--存储过程

存储过程：

create proc p_users_insert
(
    @nickName varchar(20),
    @account varchar(20),
    @pwd varchar(20)
) as
begin
    insert into Users values(@nickName,@account,@pwd)
end

Ado.net 调用存储过程

var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;

using SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
// 这里要写存储过程的名称
using SqlCommand cmd = new SqlCommand("p_users_insert", conn);
cmd.CommandType = CommandType.StoredProcedure;// 指定为存储过程类型
SqlParameter[] parameters =
{
    new SqlParameter("nickName","李重文"),
    new SqlParameter("account","lichongwen"),
    new SqlParameter("pwd","123")
};
// 批量添加 参数对象数组 至 命令对象中
cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();

案例8--输出参数

修改存储过程，添加输出参数

alter proc p_users_insert
(
    @nickName varchar(20),
    @account varchar(20),
    @pwd varchar(20),
    @code int output,
    @msg varchar(20) output -- 输出参数
) as
begin
    insert into Users values(@nickName,@account,@pwd);
    set @msg = '添加成功';
    set @code=1;
end

调用带有输出参数的存储过程

var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;

using SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
// 这里要写存储过程的名称
using SqlCommand cmd = new SqlCommand("p_users_insert", conn);
cmd.CommandType = CommandType.StoredProcedure;// 指定为存储过程类型
SqlParameter[] parameters =
{
    new SqlParameter("nickName","孙婷婷"),
    new SqlParameter("account","suntingting"),
    new SqlParameter("pwd","123"),
    new SqlParameter("code",SqlDbType.Int,4), // 不给输出参数设置值
    new SqlParameter("msg",SqlDbType.VarChar,20)
};
parameters[3].Direction = ParameterDirection.Output; // 第4个参数设置为输出参数
parameters[4].Direction = ParameterDirection.Output; // 第5个参数设置为输出参数

// 批量添加 参数对象数组 至 命令对象中
cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();
Console.WriteLine("code的值为："+ parameters[3].Value); // 获取输出参数返回的值
Console.WriteLine("msg的值为："+ parameters[4].Value);

输出结果是：

code的值为：1
msg的值为：添加成功

10. Web项目操作Ado

Web 项目是目前的开发主流，Ado.net 凭借的高效的运行效率，也占得了一席之地。

appsetting.json 配置文件：

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    // 连接字符串
    "SqlServer": "server=.;uid=sa;pwd=123456;database=unit21;pooling=true;"
  }
}

IConfiguration 接口

命名空间: Microsoft.Extensions.Configuration
程序集: Microsoft.Extensions.Configuration.Abstractions.dll

表示一组键/值应用程序配置属性。
派生

Microsoft.Extensions.Configuration.ConfigurationManager

Microsoft.Extensions.Configuration.ConfigurationRoot

Microsoft.Extensions.Configuration.ConfigurationSection

Microsoft.Extensions.Configuration.IConfigurationRoot

Microsoft.Extensions.Configuration.IConfigurationSection

在 .net web 项目中，IConfiguration 接口在项目启动中已经被注册到IOC容器中（Asp.net Mvc 高级阶段会进行讲解），所以我们只需要在需要用到的地方注入，即可拿到对象。

读取连接字符串

public class HomeController : Controller
{
    // 注入配置对象（系统在启动时，已经在IOC容器中注册好了）
    private readonly IConfiguration _configuration;

    public HomeController(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    public IActionResult Index()
    {
        // 读取连接字符串
        string connString = _configuration.GetConnectionString("SqlServer");
        return View();
    }   
}

操作Ado.net

通过nuget 安装程序集：System.Data.SqlClient

读取数据

public class HomeController : Controller
{
    // 注入配置对象（系统在启动时，已经在IOC容器中注册好了）
    private readonly IConfiguration _configuration;

    public HomeController(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    public IActionResult Index()
    {
        // 读取连接字符串
        string connString = _configuration.GetConnectionString("SqlServer");
        // 创建参数数组对象
        SqlParameter[] parameters =
        {
            new SqlParameter("nickName",SqlDbType.VarChar),
            new SqlParameter("pwd",SqlDbType.VarChar)
        };
        Console.WriteLine("请输入需要查询的姓名：");
        string? nickName = Console.ReadLine();
        parameters[0].Value = $"%{nickName}%"; // 模糊查询要加%
        parameters[1].Value = "123456";

        SqlDataAdapter adapter = new("select * from Users where NickName like @nickName and Pwd=@pwd",connString);
// 批量添加参数对象
        adapter.SelectCommand.Parameters.AddRange(parameters);
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        Console.WriteLine("\nId\t姓名\t账号 \t \t密码");
        Console.WriteLine("------------------------------------------------------");
        foreach (DataRow row in dataTable.Rows)
        {
            Console.WriteLine($"{row["id"]}\t{row["nickname"]}\t{row["account"]}\t{row["pwd"]}");
        }
        return View();
    }

   
}

11 .封装DbHelper

在上述案例中，存在太多的重复代码了，开发过程是绝不能容忍如此多的重复代码。上述代码中，除了sql 与字段参数有可能不一样以外，其他创建连接等代码都是重复的，我们可以将上述操作分为3类。

读取
写入
Count计数

如此，我们可以将上述3类封装为三个方法，将sql语句与字段参数当作方法参数传入，达到重用作用。

需要安装的包：

Microsoft.Extensions.Configuration 6.0
Microsoft.Extensions.Configuration.Json 6.0

using System.Data;
using System.Data.SqlClient;
using System.Reflection;

namespace WebApplication1;

public class DbHelper
{
    private static string? _connString;

    // 静态构造方法,
    // 第一次访问 这个的类的时候，会执行一次静态构造

    static DbHelper()
    {
        ConfigurationBuilder configuration = new ConfigurationBuilder(); //读取配置文件
        var config = configuration.SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile(file =>
        {
            file.Path = "/appsettings.json";
            file.Optional = false;
            file.ReloadOnChange = true;
        }).Build();
        _connString = config.GetConnectionString("SqlServer");
    }

    private static SqlCommand PrepareCommand(SqlConnection conn, string sql, CommandType cmdType,
        params SqlParameter[]? parameters)
    {
        using SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.CommandType = cmdType;
        if (parameters != null && parameters.Length > 0)
        {
            cmd.Parameters.AddRange(parameters);
        }

        return cmd;
    }


    /**
     * 执行增删改的操作
     */
    
    public static int ExecuteNonQuery(string sql,params SqlParameter[]? parameters)
    {
        using SqlConnection conn = new(_connString);
        conn.Open();
        return PrepareCommand(conn, sql, CommandType.Text, parameters).ExecuteNonQuery();
    }
    
    public static int ExecuteNonQuery(string sql, CommandType cmdType = CommandType.Text,
        params SqlParameter[]? parameters)
    {
        using SqlConnection conn = new(_connString);
        conn.Open();
        return PrepareCommand(conn, sql, cmdType, parameters).ExecuteNonQuery();
    }

    public static int ExecuteScalar(string sql,params SqlParameter[]? parameters)
    {
        using SqlConnection conn = new(_connString);
        conn.Open();
        return Convert.ToInt32(PrepareCommand(conn, sql, CommandType.Text, parameters).ExecuteScalar());
    }
    
    public static int ExecuteScalar(string sql, CommandType cmdType = CommandType.Text,
        params SqlParameter[]? parameters)
    {
        using SqlConnection conn = new(_connString);
        conn.Open();
        return Convert.ToInt32(PrepareCommand(conn, sql, cmdType, parameters).ExecuteScalar());
    }

    public static List<T> GetList<T>(string sql, CommandType cmdType = CommandType.Text,
        params SqlParameter[]? parameters)  where T : class, new()
    {
        using SqlDataAdapter adapter = new(PrepareCommand(
            new (_connString),sql, cmdType, parameters));

        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        return ToList<T>(dataTable);
    }
    
    public static List<T> GetList<T>(string sql, params SqlParameter[]? parameters)  where T : class, new()
    {
        using SqlDataAdapter adapter = new(PrepareCommand(
            new (_connString),sql, CommandType.Text, parameters));

        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        return ToList<T>(dataTable);
    }

    private static List<T> ToList<T>(DataTable dt) where T : class, new()
    {
        Type t = typeof(T);
        PropertyInfo[] propertys = t.GetProperties();
        List<T> lst = new List<T>();
        string typeName = string.Empty;

        foreach (DataRow dr in dt.Rows)
        {
            T entity = new T();
            foreach (PropertyInfo pi in propertys)
            {
                typeName = pi.Name;
                if (dt.Columns.Contains(typeName))
                {
                    if (!pi.CanWrite) continue;
                    object value = dr[typeName];
                    if (value == DBNull.Value) continue;
                    if (pi.PropertyType == typeof(string))
                    {
                        pi.SetValue(entity, value.ToString(), null);
                    }
                    else if (pi.PropertyType == typeof(int) || pi.PropertyType == typeof(int?))
                    {
                        pi.SetValue(entity, int.Parse(value.ToString()), null);
                    }
                    else if (pi.PropertyType == typeof(DateTime?) || pi.PropertyType == typeof(DateTime))
                    {
                        pi.SetValue(entity, DateTime.Parse(value.ToString()), null);
                    }
                    else if (pi.PropertyType == typeof(float))
                    {
                        pi.SetValue(entity, float.Parse(value.ToString()), null);
                    }
                    else if (pi.PropertyType == typeof(double))
                    {
                        pi.SetValue(entity, double.Parse(value.ToString()), null);
                    }
                    else
                    {
                        pi.SetValue(entity, value, null);
                    }
                }
            }

            lst.Add(entity);
        }

        return lst;
    }
}

配套视频：
【ado.net访问数据库技术】 Net 6 开发系列(已完结)_哔哩哔哩_bilibili

标签：using,数据库,cmd,new,connection,SqlConnection,Ado,Net,SqlParameter
From： https://www.cnblogs.com/xuyubing/p/17445868.html

Ado.Net 数据库访问技术(.Net 6版本)

1. ADO.NET的前世今生

Ado.Net 优点

2. Connection 连接对象

1. SqlConnection 类

2. 修改超时时间

3. SqlCredential 类

3. 自动释放（推荐）

using

4. Command 命令对象

1. SqlCommand 类

5. SqlDataReader 数据读取

属性

方法

案例

6. SqlDataAdapter 适配器

适配器详细介绍

将DataTable封装成List 集合

构建DataTable

7. 配置文件

读取连接字符串

读取AppSettings 内容

8. 数据连接池

什么是ADO.net连接池？

性能对比

数据连接池取数据

9. 参数化查询

SqlParameter 对象

构造函数

属性

详细案例

案例1--登录

案例2--添加

案例3--修改

案例4--查询

案例5--删除

案例6--批量删除

案例6--事务

案例7--存储过程

案例8--输出参数

10. Web项目操作Ado

IConfiguration 接口

操作Ado.net

11 .封装DbHelper

相关文章

赞助商

阅读排行