// 1. nuget安装程序集   引入
using System.Data.SqlClient;

// 2. 创建连接对象 
// 告诉连接对象服务器地址 用户名 密码 数据库
SqlConnection connection = new("server=10.10.11.248;uid=sa;pwd=password;database=LearnADO");

// 3. 打开数据库连接
connection.Open();
Console.WriteLine(connection.State);
Console.WriteLine(connection.Database);
Console.WriteLine(connection.ServerVersion);
Console.WriteLine(connection.ConnectionTimeout);

// 4. 关闭数据库连接
connection.Close();

using System.Data.SqlClient;

SqlConnection connection;
// 1. using 自动释放资源
using (connection = new("server=10.10.11.248;uid=sa;pwd=Ff17301576208;database=LearnADO"))
{
    // 2. 打开数据库连接
    connection.Open();
    Console.WriteLine(connection.State);

    // 3. using  {} 结束的时候会自动释放资源
}
Console.WriteLine(connection.State);


//C# 7.0以后的简写方式
// 1. connection 作用域返回最后一行进行关闭
// 2. 此方法运行结束时关闭
using SqlConnection connection = new SqlConnection("server=10.10.11.248;uid=sa;pwd=Ff17301576208;database=LearnADO");
connection.Open();
Console.WriteLine(connection.State);
Console.WriteLine(connection.State);

// 一次简单的插入操作
using SqlConnection connection = new SqlConnection("server=10.10.11.248;uid=sa;pwd=password;database=LearnADO");
connection.Open();
// 编写Sql语句
string sql = "insert into users values('Oho','123312')";

//创建Command对象
SqlCommand cmd = new();
//设置连接对象
cmd.Connection = connection;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();//执行增删改


//简写以上代码
using SqlConnection connection = new ("server=10.10.11.248;uid=sa;pwd=Ff17301576208;database=LearnADO");
connection.Open();
using SqlCommand cmd = new("select * from users", connection);
var res=Convert.ToInt32(cmd.ExecuteScalar());//获取首行首列  一般用于获取总条数
Console.WriteLine(res);

//一次读取数据的尝试
using SqlConnection connection = new ("server=10.10.11.248;uid=sa;pwd=password;database=LearnADO");
connection.Open();
using SqlCommand cmd = new("select * from users", connection);
using var sqlDataReader = cmd.ExecuteReader();

//行流
while (sqlDataReader.Read()) {
    /*var uidIndex = sqlDataReader.GetOrdinal("uid");
    var uidValue = sqlDataReader.GetValue(uidIndex);         //在此情况下 uidValue的类型为Object 被自动装箱 所以需要强制转换
    var nameValue = sqlDataReader.GetValue(1);
    var passwordValue = sqlDataReader["password"];*/         //也可以使用这种方式来获取值  但是也需要强制抓换
    var user = new Users(  
        sqlDataReader.GetInt32(0),
        sqlDataReader.GetString(1),
        sqlDataReader.GetString(2)
    );
    Console.WriteLine($"uid:{user.uid} username:{user.username} password:{user.password}");
}

//先封装一次  
record Users(int uid, string username, string password);

//一次简单的尝试
using SqlConnection connection = new ("server=10.10.11.248;uid=sa;pwd=Ff17301576208;database=LearnADO");
// 1. 此处并未 手动打开连接
// 2. 适配器
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
// 3. 创建命令对象
sqlDataAdapter.SelectCommand=new SqlCommand("select * from users",connection);

// 4. 读取数据
DataSet dataSet = new DataSet();
// 5. 填充数据到DataSet
sqlDataAdapter.Fill(dataSet);

Console.WriteLine(dataSet.Tables[0].Rows.Count);

using SqlConnection connection = new ("server=10.10.11.248;uid=sa;pwd=password;database=LearnADO");
// 1. 此处并未 手动打开连接
// 2. 适配器       数据一旦拿到了就会把连接断开 故名断开模式
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
// 3. 创建命令对象
sqlDataAdapter.SelectCommand=new SqlCommand("select * from users",connection);

// 4. 读取数据
//DataSet dataSet = new DataSet();  //由多个DataTable组成
DataTable dataTable = new DataTable();// 只有一张表 所以使用DataTable
// 5. 填充数据到Datatable
sqlDataAdapter.Fill(dataTable);


//在面向对象中 DataTable使用率并不高，因为它不符合面向对象思想
//更多使用的是List<T> 泛型集合
List<User> users = new List<User>();

//DBNull与C#中的Null不一样  DBNull 数据库中为空值会报错
foreach (DataRow row in dataTable.Rows) {
    //使用List封装
    User user = new();
    user.Uid = Convert.ToInt32(row["uid"]);
    user.Username = row["username"].ToString();
    //数字类型一定要记得 非空判断
    if (row["password"] is not DBNull) {
        //非空判断
        user.Password=row["password"].ToString();
    }
}


//输出
//1. List更符合面向对象思想
//2. row 在操作列名的时候 有可能会拼写错误
//3. List 集合操作速度比DataTable快，并且属于类型安全
foreach (User user in users) {
    Console.WriteLine($"id={user.uid}\t username={user.username}\t password={user.password}");
}
//封装
record User(int uid, string username, string password);

<?xml version="1.0" encoding="UTF-8" ?>
<configuration>
    <!--控制台的配置文件名称一定要叫 App.Config-->
    
    <!--连接字符串-->
    <connectionStrings>
        <!--10.10.11.248 服务器的连接字符串-->
        <add name="248ConnString" connectionString="server=10.10.11.248;uid=sa;pwd=password;database=LearnADO"/>
    </connectionStrings>
        
    <appSettings>
        <add key="databaseIP" value="10.10.11.248"/>
    </appSettings>
</configuration>

string connectionString = ConfigurationManager.ConnectionStrings["248ConnString"].ConnectionString;
var s = ConfigurationManager.AppSettings["databaseIP"].ToString();

select * from users where Account=@account and Pwd=@pwd;

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


//读取配置文件
string connectionString = ConfigurationManager.ConnectionStrings["248ConnString"].ConnectionString;
using SqlConnection conn=new(connectionString);

conn.Open();
SqlCommand cmd = new SqlCommand("select count(*) from users where username=@username and password=@password",conn);

SqlParameter parameter1=new SqlParameter();
parameter1.ParameterName="username";// 参数名
parameter1.Value="Aealen"; //参数值
parameter1.Size=20; //参数大小
parameter1.SqlDbType=SqlDbType.VarChar; //参数类型

SqlParameter parameter2=new ();
parameter2.ParameterName="password";
parameter2.Value="1221222";
parameter2.Size=20;
parameter2.SqlDbType=SqlDbType.VarChar;

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

int cmdRes = Convert.ToInt32(cmd.ExecuteScalar());

if (cmdRes > 0) {
    Console.WriteLine("登陆成功");
}
else {
    Console.WriteLine("登陆失败");
}

//读取配置文件
string connectionString = ConfigurationManager.ConnectionStrings["248ConnString"].ConnectionString;
using SqlConnection conn=new(connectionString);

conn.Open();
using SqlCommand cmd = new SqlCommand("Insert into users values(@username,@password)",conn);


SqlParameter[] parameters = {
    new SqlParameter("username","AowuBaka"), //指定参数名与参数值  ，那么参数类型与大小呢？  A:如果类型合适，会自动推断类型和大小
    new SqlParameter("password","2212331"),  //如果要使用此重载传入 0  需要使用 Object(0)
};

//创建参数对象
cmd.Parameters.AddRange(parameters);

using SqlCommand cmd = new SqlCommand("update users set password=@newPwd where uid=@uid",conn);

SqlParameter[] parameters= {
    new ("newPwd",SqlDbType.VarChar,20),
    new SqlParameter("uid",SqlDbType.Int,4)
};

parameters[0].Value = "newpassword";
parameters[1].Value = 3;

cmd.Parameters.AddRange(parameters);

cmd.ExecuteNonQuery();

string connectionString = ConfigurationManager.ConnectionStrings["248ConnString"].ConnectionString;

using SqlDataAdapter sqlDataAdapter = new("select * from users where username like @username", new SqlConnection(connectionString));

SqlParameter sqlParameter = new("username", SqlDbType.VarChar);
sqlParameter.Value = "%" + "e" + "%";
sqlDataAdapter.SelectCommand.Parameters.Add(sqlParameter);

DataTable dataTable = new DataTable();

sqlDataAdapter.Fill(dataTable);

//读取配置文件
string connectionString = ConfigurationManager.ConnectionStrings["248ConnString"].ConnectionString;

SqlConnection connection=new (connectionString);
connection.Open();
//打开事务    并设置隔离级别
SqlTransaction sqlTransaction = connection.BeginTransaction();
try {

    var sqlCommand = new SqlCommand("delete  from users where uid=@uid", connection, sqlTransaction); //以事务方式执行
    int[] arr = { 3, 7, 8, 9, 10, 11, 12 };
    foreach (int item in arr) {
        sqlCommand.Parameters.Add(new("uid", item));
        sqlCommand.ExecuteNonQuery();
        sqlCommand.Parameters.Clear(); //清除已经执行过的参数。
    }

    sqlTransaction.Commit(); //提交事务
}
catch (Exception e) {
    Console.WriteLine(e);
    sqlTransaction.Rollback();
}

create proc p_users_insert(
    @username varchar(20),
    @password varchar(20),
    @code int output,
    @msg varchar(20) output
)as 
begin 
    insert into users values(@username,@password);
    set @msg='插入成功';
    set @code=200;
endxxxxxxxxxx18 1alter proc p_users_insert(2    @username varchar(20),3    @password varchar(20),4    @code int output,5    @msg varchar(20) output6)as 7begin 8    insert into users values(@username,@password);9    set @msg='插入成功';10    set @code=200;11endcreate proc p_users_insert12(13    @username varchar(20),14    @password varchar(20)15) as 16begin 17    insert into users values(@username,@password) 18end

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


//读取配置文件
string connectionString = ConfigurationManager.ConnectionStrings["248ConnString"].ConnectionString;

SqlConnection connection=new (connectionString);
connection.Open();

//填写存储过程名称
SqlCommand cmd = new SqlCommand("p_users_insert", connection);
cmd.CommandType = CommandType.StoredProcedure;//一定要设置 cmd类型  默认为Text

SqlParameter[] parameters = {
    new("username", SqlDbType.VarChar, 20),
    new("password", SqlDbType.VarChar, 20),
    new("code",SqlDbType.Int,4),  //输出参数 int
    new("msg",SqlDbType.VarChar,20) //输出参数 string
};
parameters[0].Value = "ProcOut2";
parameters[1].Value = "ssss";

parameters[2].Direction = ParameterDirection.Output;//设置为输出参数
parameters[3].Direction = ParameterDirection.Output;

cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();

//一定要在执行完存储过程之后才能获取输出参数的值
Console.WriteLine(parameters[2].Value);
Console.WriteLine(parameters[3].Value);