本文环境 Microsoft Visual Studio 2022 .Net6 SQLServer2019

一、基本环境、配置文件

在解决方案管理器右键添加相关的包，如下两个包
Microsoft.Data.SqlClient SQLServer SQLServer数据库
Microsoft.Extensions.Configuration.Json json配置文件

添加配置文件 appsettings.json，选始终复制

添加json配置文件内容如下

{

  "ConnectionStrings": {
    "DefaultConnection": "Data Source=.;Initial Catalog=Demo20220707;Integrated Security=true; pooling=false;TrustServerCertificate=true"
  },
  "AppSettings": {
    "DefaultTable": "UserData2"
  }
}

DefaultConnection 根据自己的情况改

二、创建测试表与数据

创建测试的表与数据，如下图

SQL 语句代码如下

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'UserData2') AND type in (N'U'))
DROP TABLE UserData2;

CREATE TABLE UserData2(
	ID int IDENTITY(1,1) PRIMARY KEY ,
	LoginName nvarchar(50) UNIQUE,
	Password nvarchar(50),
	DisplayName nvarchar(50),
	CreateDate datetime,
);

insert into UserData2(LoginName,Password,DisplayName,CreateDate) values('admin','pwtest','管理员',getdate());

注：真做系统尽量加密密码，这样不安全

没SQLServer 到官网下载个开发版即可

三、写代码调试

然后打开 Program.cs 开始写代码，图下图

注意：.net 6 的 Program.cs 默认是看不见 Program 类的这是我后改的为了写列子方便，代码如下

using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Data.Common;
using System.Reflection;

internal class Program
{
	static void Main(string[] args)
	{
		//一、读取配置文件
		var appDir = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) ?? String.Empty;
		var cfgPath = Path.Combine(appDir, "appsettings.json");

		IConfigurationRoot? config = new ConfigurationBuilder()
			.AddJsonFile(cfgPath)
			.Build();	
		

		var defconnstring = config.GetConnectionString("DefaultConnection");
		var defTable = config.GetSection("AppSettings:DefaultTable").Value;

		Console.WriteLine($"{defconnstring}\t{defTable}\n\n");
		

		//二、数据库操作

		using SqlConnection conn = new SqlConnection(defconnstring);
		conn.ConnectionString = defconnstring;
		conn.Open();

		var sql = $"SELECT * FROM {defTable} where id < @id";
		using (var cmdSelect = conn.CreateCommand())
		{
			cmdSelect.CommandText = sql;
			//where条件没意义就是为了演示 Parameter 的使用			
			var parm1 = cmdSelect.CreateParameter();
			parm1.ParameterName = "id";
			parm1.Value = 10000;
			cmdSelect.Parameters.Add(parm1);

			using (var red = cmdSelect.ExecuteReader())
			{
				var cloms = red.GetColumnSchema();
				var clocount = cloms.Count;
				foreach (var clo in cloms)
				{
					Console.Write("{0}\t", clo.ColumnName);
				}
				Console.WriteLine("");

				while (red.Read())
				{
					for (int i = 0; i < clocount; i++)
					{
						Console.Write("{0}\t", red[i]);
					}
					Console.WriteLine("");
				}
			}


		}


	}
}

四、最终运行效果

Ctrl+F5 即可（运行不调试）
F5 调试

本文完