.Net9.0访问MSSQL数据库读取表中数据行

1.表结构与表中数据

查询记录语句：

SELECT TOP (1000) [StatusName]
      ,[StatusValue]
      ,[StatusString]
      ,[StatusTip]
      ,[StatusDescription]
      ,[SortID]
  FROM [WHQJAccountsDB].[dbo].[SystemStatusInfo]

查询总记录数语句：

  select count(SortID) as row_count from SystemStatusInfo;

 2.创建控制台应用程序，并选择目标框架为.NET9.0

添加数据库依赖项

3.实现代码：

using Microsoft.Data.SqlClient;

//连接字符串
string connectionString = "Server=.;Database=WHQJAccountsDB;User Id=sa;Password=Aa123456.!;Integrated Security=True;Pooling=true;TrustServerCertificate=true;";
//使用连接字符串连接创建数据库连接
using (SqlConnection connection = new SqlConnection(connectionString)) 
{
    //打开数据库连接
    connection.Open();
    Console.WriteLine("成功连接SQL Server");
    //表查询语句
    string query = "SELECT * FROM SystemStatusInfo";

    string query_row_count = "select count(SortID) as row_count from SystemStatusInfo";

    using (SqlCommand cmd = new SqlCommand(query_row_count, connection))
    {
        var r = cmd.ExecuteScalar().ToString();//执行并返回首行首列
        Console.WriteLine("表总记录数:"+r+"==============================\n");
    }
    //执行SQL查询
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        //查询并返回数据读取器
        using (SqlDataReader reader = command.ExecuteReader())
        {
            //遍历读取数据行
            while (reader.Read())
            {
                //显示表字段值
                Console.WriteLine("StatusTip: ->" + reader["StatusTip"].ToString());
                Console.WriteLine("StatusName: ->" + reader["StatusName"].ToString());
                Console.WriteLine("StatusValue: ->" + reader["StatusValue"].ToString());
                Console.WriteLine("StatusString: ->" + reader["StatusString"].ToString());
                Console.WriteLine("===========================");
            }
        }
    }
}
  

注意：MSSQL SERVER 2022版本要信任证书：

TrustServerCertificate=true;

//连接字符串
string connectionString = "Server=.;Database=WHQJAccountsDB;User Id=sa;Password=Aa123456.!;Integrated Security=True;Pooling=true;TrustServerCertificate=true;";

执行结果: