ado.net-All

连接数据库

方式一

SqlConnection connection = new SqlConnection();
connection.ConnectionString = "server=192.168.10.116;uid=sa;pwd=qwe20211114.;database=database_2";
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=database_2
ServervERSION=14.00.3456
DataSource=192.168.10.116
ConnectionTimeOut=15

*/

方式二

SqlConnection sqlConnection ;
 using ( sqlConnection = new SqlConnection("server=192.168.10.116;uid=sa;pwd=qwe20211114.;database=database_2"))
 {
  sqlConnection.Open();
  Console.WriteLine($"state={sqlConnection.State}");
  Console.WriteLine($"DateBase={sqlConnection.Database}");
  Console.WriteLine($"ServerVersion={sqlConnection.ServerVersion}");
  Console.WriteLine($"DataSource={sqlConnection.DataSource}");
  Console.WriteLine($"ConnectionTimeOut={sqlConnection.ConnectionTimeout}");
 }  // 这里using 释放资源
 /**
  * state=Open
DateBase=database_2
ServerVersion=14.00.3456
DataSource=192.168.10.116
ConnectionTimeOut=15

  */
Console.WriteLine($"datastate={sqlConnection.State}");

 /**
 * datastate=Closed
 */

方式三

using SqlConnection sqlConnection = new SqlConnection("server=192.168.10.116;uid=sa;pwd=qwe20211114.;database=database_2");
sqlConnection.Open();
Console.WriteLine($"state={sqlConnection.State}");
Console.WriteLine($"DataBase={sqlConnection.Database}");
Console.WriteLine($"Serversion={sqlConnection.ServerVersion}");
Console.WriteLine($"DataSource={sqlConnection.DataSource}");
Console.WriteLine($"connectionTimeOUt={sqlConnection.ConnectionTimeout}");

using

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

初始操作数据库Command

// 创建数据连接对象 并打开连接
using SqlConnection sqlConnection = new SqlConnection("server=192.168.10.116;uid=sa;pwd=qwe20211114.;database=database_2");
sqlConnection.Open();
// 创建命令执行对象
var sqlCommand = new SqlCommand();
// 绑定数据库连接
sqlCommand.Connection = sqlConnection;
// 书写sql 语句
string sqlString1 = "insert into product values ('gouqi',88)";
// 设置sql命令
sqlCommand.CommandText = sqlString1;
// 执行sql语句
sqlCommand.ExecuteNonQuery(); //增删改 执行的语句

获取首行首列

using SqlConnection sqlConnection = new SqlConnection("server=192.168.10.116;uid=sa;pwd=qwe20211114.;database=database_2");
sqlConnection.Open();
using SqlCommand sqlCommand = new SqlCommand("select count(*) from product");
sqlCommand.Connection = sqlConnection;
var executeScalar = sqlCommand.ExecuteScalar();
Console.WriteLine(executeScalar); // 获取首行首列

返回一个读取对象

using SqlConnection sqlConnection = new SqlConnection("server=192.168.10.116;uid=sa;pwd=qwe20211114.;database=database_2");
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand("select * from product",sqlConnection);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(); //返回一个读取对象
string s = sqlDataReader.ToString();
Console.WriteLine(s);

读取并展示数据

方式一

using SqlConnection sqlConnection = new SqlConnection("server=192.168.10.116;uid=sa;pwd=qwe20211114.;database=database_2");
sqlConnection.Open();
using SqlCommand sqlCommand = new SqlCommand("select * from product",sqlConnection);
using SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
    // 下标索引顺序必须和数据库顺序一直、、
    object id =sqlDataReader[0];
    object pname = sqlDataReader[1];
    object price = sqlDataReader[2];
    Console.WriteLine(id+"    "+ pname+"  "+price+"");
}

方式二

using SqlConnection sqlConnection = new SqlConnection("server=192.168.10.116;uid=sa;pwd=qwe20211114.;database=database_2");
sqlConnection.Open();
using SqlCommand sqlCommand = new SqlCommand("select * from product", sqlConnection);
using SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
    object id = sqlDataReader.GetValue(sqlDataReader.GetOrdinal("Id")); 
    object pname = sqlDataReader.GetValue(sqlDataReader.GetOrdinal("ProductName"));
    object price = sqlDataReader.GetValue(sqlDataReader.GetOrdinal("Price"));
    Console.WriteLine("    "+id+"    "+pname+"    "+price);
    
}

方式三

using SqlConnection connection = new SqlConnection("server=192.168.10.116;uid=sa;pwd=qwe20211114.;database=database_2");
connection.Open();
using SqlCommand command = new SqlCommand("select * from product", connection);
using SqlDataReader dataReader = command.ExecuteReader();

while (dataReader.Read())
{
    Product product = new Product(dataReader.GetInt32(0),dataReader.GetString(1),dataReader.GetDecimal(2));
    Console.WriteLine(product.ToString());
}

record Product(int Id,string ProductName,decimal Price);

SqlDataAdapter 适配器

DataSet 相当于是一个容器

// 创建数据连接
using SqlConnection connection = new SqlConnection("server=192.168.10.116;uid=sa;pwd=qwe20211114.;database=database_2");
// 创建数据库命令执行器 并绑定数据库连接
using SqlCommand sqlCommand = new SqlCommand("select * from product",connection);
// 创建适配器 
using SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
// sql适配器 匹配 数据库命令执行器
sqlDataAdapter.SelectCommand = sqlCommand;
// 创建装数据的容器
DataSet dataSet = new DataSet();
// 执行sql 并把数据装入DataSet中
sqlDataAdapter.Fill(dataSet);
// 有多少行数据
Console.WriteLine(dataSet.Tables[0].Rows.Count);//2

数据由对象去接

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("select * from product", "server=192.168.10.116;uid=sa;pwd=qwe20211114.;database=database_2");

// DataSet 是由于 DataTable 组成的 DataTable是由行组成的，行是由列组成的

DataTable dataTables = new DataTable();

sqlDataAdapter.Fill(dataTables);

List<Product> products = new List<Product>();
foreach (DataRow row in dataTables.Rows)
{
    var product1 = new Product();
    product1.Id = Convert.ToInt32(row["id"]);
    if (row["price"] is not DBNull)
    {
        product1.Price = Convert.ToDecimal(row["price"]);
    }

    if (row["ProductName"] is not DBNull)
    {
        product1.ProductName = row["ProductName"].ToString();
    }
        products.Add(product1);
}
Console.WriteLine(products.Count);


public class Product
{
    public int Id { get; set; }
    public string? ProductName { get; set; }
    public decimal Price { get; set; }
    public override string ToString()
    {
        return Id+"  "+ProductName+" "+Price;
    }
}

手动创建DataTable

DataTable dataTable = new DataTable("商品表");
// 创建列名
dataTable.Columns.Add("id");
dataTable.Columns.Add("productname");
DataColumn dataColumn = new DataColumn("price");
dataTable.Columns.Add(dataColumn);
// 创建行
DataRow dataRow = dataTable.NewRow();
dataRow["Id"] = 1;
dataRow["ProductName"] = "口红";
dataRow["Price"] = 269;
// 添加到DataTable中
dataTable.Rows.Add(dataRow);
List<Product> products = new List<Product>();
foreach (DataRow row in dataTable.Rows)
{
    var product1 = new Product();
    product1.Id = Convert.ToInt32(row["id"]);
    if (row["price"] is not DBNull)
    {
        product1.Price = Convert.ToDecimal(row["price"]);
    }

    if (row["ProductName"] is not DBNull)
    {
        product1.ProductName = row["ProductName"].ToString();
    }
    products.Add(product1);
}
Console.WriteLine(products[0].ToString());


public class Product
{
    public int Id { get; set; }
    public string? ProductName { get; set; }
    public decimal Price { get; set; }
    public override string ToString()
    {
        return Id+"  "+ProductName+" "+Price;
    }
}

Rider 创建 配置文件

vs 创建配置文件

vs 添加配置文件的相关包

vs 操作 ConfigurationManager

App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<connectionStrings>
		<add name="localString" connectionString="server=192.168.10.116;uid=sa;pwd=qwe20211114.;database=database_2"/>
	</connectionStrings>
</configuration>

main

// 读取配置文件
string conString = ConfigurationManager.ConnectionStrings["localString"].ConnectionString;
using    SqlDataAdapter adapter = new SqlDataAdapter("select * from product", conString);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
Console.WriteLine("===========");
Console.WriteLine(dataTable.Rows.Count);
Console.WriteLine("===========");

配置文件中读取其他的信息

app.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
   <appSettings>
	   <add key="siteName" value="人生的意义是什么"/>
   </appSettings>
</configuration>

main

// 读取配置文件
String str = ConfigurationManager.AppSettings["siteName"].ToString();
Console.WriteLine(str);

连接池

app.config -----> pooling = true

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

设置参数对象 SqlParameter

方式一 SqlParameter() – 查询个数

// 读取配置
string connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
// 建立数据库的连接
using   SqlConnection sqlConnection = new SqlConnection(connectionString);
// 打开连接
sqlConnection.Open();
// 创建 sql 执行对象
SqlCommand sqlCommand = new SqlCommand("select count(*) from Users where Account=@account and Pwd = @pwd",sqlConnection);
// 设置参数对象
SqlParameter sqlParameter = new SqlParameter();
sqlParameter.ParameterName = "account"; // 与sql语句上的参数名 对应上 可以不写@
sqlParameter.Size = 20; // 参数大小
sqlParameter.SqlDbType = SqlDbType.VarChar;//类型
sqlParameter.Value = "zhangsan"; //传入的值

SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "pwd"; // 参数名 与sql语句上的参数名 对应上 可以不写@
parameter.Size = 20; //参数大小
parameter.SqlDbType = SqlDbType.VarChar; // 类型
parameter.Value = "123"; // 参数值

// add parameter
sqlCommand.Parameters.Add(sqlParameter);
sqlCommand.Parameters.Add(parameter);

// execute sql and return result
int count = Convert.ToInt32(sqlCommand.ExecuteScalar());
if (count > 0)
{
    Console.WriteLine("登录成功");
}
else
{
    Console.WriteLine("密码错误");   
}

方式二 参数数组 SqlParameter[] --> insert

// get configuration information
string connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
// get sql connnection
using SqlConnection scon = new SqlConnection(connectionString);
// open sql connnection
scon.Open();
// create sqlComand
SqlCommand sqlCommand = new SqlCommand("insert into Users(NickName,Account,Pwd) values (@nickName,@account,@pwd);",scon);
// create sqlParamter[]
SqlParameter[] parameters =
{
new SqlParameter("nickName","lzy"),
new SqlParameter("account","lzy"),
new SqlParameter("pwd","lzy")
};
// add more paramters
sqlCommand.Parameters.AddRange(parameters);
sqlCommand.ExecuteNonQuery();

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

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

方式三 修改 update

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

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 = "update";
parameters[1].Value = "update";
parameters[2].Value = "update";
parameters[3].Value = 1;

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

方式四 模糊查询 like

String connectionstring = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
SqlParameter[] parameters =
{
  new SqlParameter("nickName",SqlDbType.VarChar),
  new SqlParameter("pwd",SqlDbType.VarChar)
};
string name = "pdat";
parameters[0].Value = $"%{name}%";
parameters[1].Value = "update";

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("select * from Users where NickName like @nickName and Pwd=@pwd",connectionstring);
//sqlDataAdapter.InsertCommand
//sqlDataAdapter.DeleteCommand
//sqlDataAdapter.UpdateCommand
    
sqlDataAdapter.SelectCommand.Parameters.AddRange(parameters);
DataTable dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);

foreach (DataRow dataTableRow in dataTable.Rows)
{
    Users users = new Users();
    users.Id = Convert.ToInt32(dataTableRow["ID"]);
    users.NickName = dataTableRow["NickName"].ToString();
    users.Account = dataTableRow["Account"].ToString();
    users.Pwd = dataTableRow["Pwd"].ToString();
    users.ToStrings();
}


public class Users
{
    public int Id { get; set; }
    public string NickName { get; set; }
    public string Account { get; set; }
    public string Pwd { get; set; }

    public void ToStrings() {
        Console.WriteLine("  "+ Id + "  "+ NickName + "    "+ Account + "   "+ Pwd);
    }
}

方式五 单个删除 delete

string connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
using SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
using SqlCommand command =  new SqlCommand("delete from Users where Id = @id;", sqlConnection);
command.Parameters.Add(new SqlParameter("id", 1));
command.ExecuteNonQuery();

方式六 批量删除 more Delete

string connectionString = ConfigurationManager.ConnectionStrings["localString"].ConnectionString;
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand("delete from Users where Id=@id",sqlConnection);
// 模拟删除的id
int[] idList = new int[] { 2,3,4};
foreach (int id in idList)
{
    sqlCommand.Parameters.Add(new SqlParameter("id", id));
    sqlCommand.ExecuteNonQuery();
    sqlCommand.Parameters.Clear(); // 必须清除之前的参数
    
}

方式七 事务

String connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
using SqlTransaction beginTransaction = sqlConnection.BeginTransaction(); //开启事务
// 命令对象指定事务对象
using SqlCommand sqlCommand = new SqlCommand("delete from Users where Id = @id",sqlConnection,beginTransaction);
try
{
    int[] idList = { 5, 6};
    foreach (int id in idList)
    {
        sqlCommand.Parameters.Add(new SqlParameter("id",id));
        sqlCommand.ExecuteNonQuery();
        sqlCommand.Parameters.Clear();
        int a = 12;
        int b = 0;
        int zonre = a/b;
    }
}
catch(Exception e)
{
    Console.WriteLine(e.Message);
    beginTransaction.Rollback();
}

beginTransaction.Commit();

方式八 存储程序 没有返回值

sql

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

c#

string connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
 using  SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
using SqlCommand sqlCommand = new SqlCommand("p_users_insert",sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParameters =
{
    
    new SqlParameter("nickName","lisi"),
    new SqlParameter("account","lichongwen"),
    new SqlParameter("pwd","123")
};

sqlCommand.Parameters.AddRange(sqlParameters);
sqlCommand.ExecuteNonQuery();

方式九 有返回值

sql

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

c#

string connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
 using  SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
using SqlCommand sqlCommand = new SqlCommand("p_users_insert",sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParameters =
{
    
    new SqlParameter("nickName","lisi"),
    new SqlParameter("account","lichongwen"),
    new SqlParameter("pwd","123"),
    new SqlParameter("code",SqlDbType.Int,4), // 不给输出参数设置值
    new SqlParameter("msg",SqlDbType.VarChar,20)
};

sqlParameters[3].Direction = ParameterDirection.Output;
sqlParameters[4].Direction = ParameterDirection.Output;


sqlCommand.Parameters.AddRange(sqlParameters);
sqlCommand.ExecuteNonQuery();
// 执行命令后才能够查询
Console.WriteLine("code的值为： "+sqlParameters[3].Value);
Console.WriteLine("msg的值为： "+sqlParameters[4].Value);

rider 创建mvc项目

配置文件填写位置

读取配置的插件

加上数据库连接

断点调试

MVC

appsetting
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "sqlServer": "server=192.168.10.116;uid=sa;pwd=qwe20211114.;database=database_2;pooling=true"
  }
}

public class HomeController : Controller
{
    private readonly ILogger<HomeController> _logger;

    private readonly IConfiguration _configuration;

    private readonly string _connectionString;

    public HomeController(ILogger<HomeController> logger, IConfiguration configuration)
    {
        _logger = logger;
        _configuration = configuration;
        _connectionString = _configuration.GetConnectionString("sqlServer");
    }

    public IActionResult Index()
    {
     using   SqlConnection sqlConnection = new SqlConnection(_connectionString);
     using    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("select * from Users",sqlConnection);
     DataTable dataTable = new DataTable();
     sqlDataAdapter.Fill(dataTable);
     return View(dataTable);
    }
}

Views-Home-Index.cshtml

@using System.Data
@model System.Data.DataTable

@{
    ViewData["Title"] = "Home Page";
}

<div class="text-center">
    <h1 class="display-4">Welcome</h1>
    <p>Learn about <a href="https://docs.microsoft.com/aspnet/core">building Web apps with ASP.NET Core</a>.</p>
</div>

<table class="table table-hover">
    <thead>
    <tr>
        <th>id</th>
        <th>名称</th>
        <th>价格</th>
    </tr>
    </thead>
    <tbody>
    @foreach (DataRow row in Model.Rows)
    {
        <tr>
            <th>@row["Id"]</th>
            <th>@row["NickName"]</th>
            <th>@row["Account"]</th>
            <th>@row["Pwd"]</th>
        </tr>
    }
    </tbody>
</table>

DBHelper

public class DbHelper
{
    private static string? _connString;

    static DbHelper()
    {
        ConfigurationBuilder configurationBuilder = new ConfigurationBuilder();
        var config = configurationBuilder.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 connection,string sql,CommandType cmdType,params SqlParameter[]? parameters)
    {
      using  SqlCommand sqlCommand = new SqlCommand(sql,connection);
      sqlCommand.CommandType = cmdType;
      if (parameters != null && parameters.Length > 0)
      {
          sqlCommand.Parameters.AddRange(parameters);
      }

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

    private static SqlConnection GetSqlConnection()
    {
        using SqlConnection comm = new SqlConnection(_connString);
        comm.Open();
        return comm;

    }
    
    // 查询个数
    public static int ExecuteScalar(string sql,CommandType cmdType = CommandType.Text,params  SqlParameter[] parameters)
    {
        
        return Convert.ToInt32(PrepareCommand(GetSqlConnection(), sql, cmdType, parameters).ExecuteScalar());
    }
    
    // 查询数据
    public static DataTable GetDataTable(string sql,CommandType cmdType = CommandType.Text,params SqlParameter[]? parameters)
    {
        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql,new SqlConnection(_connString));
        sqlDataAdapter.SelectCommand.CommandType = cmdType;

        if (parameters != null && parameters.Length > 0)
        {
            sqlDataAdapter.SelectCommand.Parameters.AddRange(parameters);
        }

        DataTable dataTable = new DataTable();
        sqlDataAdapter.Fill(dataTable);
        return dataTable;
    }
    
}