---

---

项目地址

• 教程作者：
• 教程地址：

• 代码仓库地址：

• 所用到的框架和插件：

dbt 
airflow

一、sqlDataReader连接模式

• 该模式下，需要一直保持连接，对数据库 占用比较大，所以比较少用，了解

1.1 创建数据库连接

• 直接创建连接，每次都要写close，容易出错

SqlConnection conn = new("Server=DESKTOP-CL0GECF;Database=sql_advanced_tutorial;uid=sa;pwd=123123;Integrated Security=True");conn.Open();Console.WriteLine(conn.State);
Console.WriteLine(conn.Database);
Console.WriteLine(conn.ServerVersion);
Console.WriteLine(conn.ConnectionTimeout);conn.Close();

• 使用自动释放创建连接，自动close

SqlConnection conn;
using (conn = new("Server=DESKTOP-CL0GECF;Database=sql_advanced_tutorial;uid=sa;pwd=123123.;Integrated Security=True"))
{conn.Open();Console.WriteLine(conn.State);Console.WriteLine(conn.Database);Console.WriteLine(conn.ServerVersion);Console.WriteLine(conn.ConnectionTimeout);
} 

• 简写

using SqlConnection conn = new("Server=DESKTOP-CL0GECF;Database=sql_advanced_tutorial;uid=sa;pwd=shangxi123.");
conn.Open(); 

1.2 执行语句

1.读取首行首列cmd.ExecuteScalar();
2. 增，删查 cmd2.ExecuteNonQuery();
3. 返回所有查询（少量数据）cmd3.ExecuteReader();

using System.Data.SqlClient;//连接
using SqlConnection conn = new("Server=DESKTOP-CL0GECF;Database=AdventureWorks2017;uid=sa;pwd=123123.");
conn.Open();//执行首行首列
using SqlCommand cmd = new("SELECT count(1) FROM Person.Password", conn);
cmd.ExecuteScalar();//执行增删改
using SqlCommand cmd2 = new("delete id ", conn);
cmd2.ExecuteNonQuery();//读取数据
using SqlCommand cmd3 = new("SELECT * FROM Person.Password", conn);
cmd3.ExecuteReader();

1.3 读取数据

• 由于读取数据是不能断开连接的，尤其是大量数据的时候

//读取数据
using SqlCommand cmd3 = new("SELECT * FROM product", conn);
using SqlDataReader sqlDataReader =  cmd3.ExecuteReader();while (sqlDataReader.Read())
{var product = new Product((int)sqlDataReader["Id"],(string)sqlDataReader["Name"],(decimal)sqlDataReader["Price"]);}
//将需要读取的数据先封装成一个类
record Product(int Id,string Name, decimal Price);

二、SqlDataAdapter适配器模式—断开模式(常用)

• 一次连接取得数据后，即可断开，且都将存在内存之中，不会占用数据库资源；

2.1 使用DataTable读取数据

2.1.1 直接使用DataTable

• 将DataTable的数据存放List里

using System.Data;
using System.Data.SqlClient;//创建连接
SqlConnection conn = new("Server=DESKTOP-CL0GECF;Database=demo01;uid=sa;pwd=shangxi123.");
//sql语句
string sql = "select * from Products"; 
//创建适配器
SqlDataAdapter adapter = new(sql, conn);
//创建数据集dt
DataTable dt = new();
adapter.Fill(dt);
Console.WriteLine(dt.Rows.Count);
//创建一个空集合，用来存放数据
List<Products> list = new();
//遍历数据集dt
foreach (DataRow row in dt.Rows)
{//创建对象Products products = new(Convert.ToString(row["Name"]),Convert.ToInt32(row["Price"]));//将对象添加到集合中list.Add(products);
}record Products(string Name,int Price);

三、读取配置文件

1. 创建App.config

<?xml version="1.0" encoding="utf-8"?>
<configuration><connectionStrings><!--本机数据库连接字符串--><add name ="localString" connectionString="Server=DESKTOP-CL0GECF;Database=demo01;uid=sa;pwd=xxxxx."/><!--DevUser--><add name ="devString" connectionString="Server=DESKTOP-CL0GECF;Database=demo01;uid=sa;pwd=123123."/></connectionStrings></configuration> 

2. 创建连接

<?xml version="1.0" encoding="utf-8"?>
<configuration><connectionStrings><!--本机数据库连接字符串--><add name ="localString" connectionString="Server=DESKTOP-CL0GECF;Database=demo01;uid=sa;pwd=shangxi123."/><!--DevUser--><add name ="devString" connectionString="Server=DESKTOP-CL0GECF;Database=demo01;uid=sa;pwd=shangxi123."/></connectionStrings></configuration> 

四、数据库连接池

• 数据库连接池默认是打开的
  手动关闭连接池

<?xml version="1.0" encoding="utf-8"?>
<configuration><connectionStrings><!--本机数据库连接字符串--><add name ="localString" connectionString="Server=DESKTOP-CL0GECF;Database=demo01;uid=sa;pwd=123123.;pooling=false"/><!--DevUser--><add name ="devString" connectionString="Server=DESKTOP-CL0GECF;Database=demo01;uid=sa;pwd=123123."/></connectionStrings>
</configuration> 

• 计时器

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;string connectionString = ConfigurationManager.ConnectionStrings["localString"].ConnectionString;
Stopwatch sw = new Stopwatch();
sw.Start();
//1.创建连接
SqlConnection conn = new(connectionString);using SqlDataAdapter adapter = new("select * from Products", conn);
DataTable dt = new();
adapter.Fill(dt);
Console.WriteLine(dt.Rows.Count);
sw.Stop();
Console.WriteLine("共耗时："+ sw.ElapsedMilliseconds/1000.0);