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

ConnectionTimeout=30 

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();

/* *  * 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()方法，但会自动关闭

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}");

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();// 执行

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();// 获取首行首列

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(); // 返回一个读取对象

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"); }

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"); }

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);

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);

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);

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);

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);

/**  * 为什么要使用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 = 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);

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

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

<?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

<?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

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'

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

// 读取配置文件 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("登录失败，用户名或者密码输入不正确"); }

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();

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

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();

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("nIdt姓名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

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();

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(); // 必须清除掉之前的参数 }

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(); // 数据回滚 }

create proc p_users_insert (     @nickName varchar(20),     @account varchar(20),     @pwd varchar(20) ) as begin     insert into Users values(@nickName,@account,@pwd) 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","lichongwen"),     new SqlParameter("pwd","123") }; // 批量添加 参数对象数组 至 命令对象中 cmd.Parameters.AddRange(parameters); cmd.ExecuteNonQuery();

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的值为：添加成功 

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

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();     }    }

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("nIdt姓名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();     } ​     }

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;     } }