在. NET 世界, 如果想要对数据库进行操作, 总少不了 ADO.NET 的身影. 在. NET Core 里同样离不开那些熟悉的类库与 API. 这里简略地介绍下如何通过 ADO.NET 对 SQL Server 进行不同的处理.
System.Data.SqlClient
第一步先在项目工程中查看有无 System.Data.SqlClient 引用, 如果没有的话, 可以用以下三种方式安装:
Visual Studio 上的可视化 Nuget 包管理工具 (Manage NuGet Packages), 找到相应类库, 点击安装按钮
同样是 Visual Studio 上的包管理控制台 (Package Manager Console) 输入命令,
Install-Package System.Data.SqlClient
命令行界面 (CLI) 中使用命令,
dotnet add package System.Data.SqlClient
查询
可以使用最基础的 ExecuteReader 方法:
- using (var conn = new SqlConnection("Server=.;Integrated Security=true"))
- {
- conn.Open();
- using (var cmd = new SqlCommand("select top 10 * from [AdventureWorks2016CTP3].[Person].[Person]", conn))
- {
- using (var reader = cmd.ExecuteReader())
- {
- while (reader.Read())
- {
- Console.WriteLine(reader["FirstName"]);
- }
- }
- }
- }
在. NET Core 2.0 以后还可以使用 SqlDataAdapter 与 DataSet, 看得出微软在向后兼容方面做了不少工作:
- using (var conn = new SqlConnection("Server=.;Integrated Security=true"))
- {
- var adapter = new SqlDataAdapter("select top 10 * from [AdventureWorks2016CTP3].[Person].[Person]", conn);
- var dataset = new DataSet();
- adapter.Fill(dataset);
- var dt = dataset.Tables[0];
- foreach (var item in dt.Rows)
- {
- var row = item as DataRow;
- Console.WriteLine(row["FirstName"]);
- }
- }
插入
- using (var conn = new SqlConnection("Server=.;Integrated Security=true"))
- {
- conn.Open();
- using (var cmd = new SqlCommand("insert into [AdventureWorks2016CTP3].[Person].[AddressType] (Name) values(@Name)", conn))
- {
- cmd.Parameters.AddWithValue("@Name", "Test");
- cmd.ExecuteNonQuery();
- }
- }
更新
- using (var conn = new SqlConnection("Server=.;Integrated Security=true"))
- {
- conn.Open();
- using (var cmd = new SqlCommand("update [AdventureWorks2016CTP3].[Person].[AddressType] set name = @Name where name = @Criterion", conn))
- {
- cmd.Parameters.AddWithValue("@Name", "Test2");
- cmd.Parameters.AddWithValue("@Criterion", "Test");
- cmd.ExecuteNonQuery();
- }
- }
删除
- using (var conn = new SqlConnection("Server=.;Integrated Security=true"))
- {
- conn.Open();
- using (var cmd = new SqlCommand("delete [AdventureWorks2016CTP3].[Person].[AddressType] where name = @Name", conn))
- {
- cmd.Parameters.AddWithValue("@Name", "Test2");
- cmd.ExecuteNonQuery();
- }
- }
存储过程
- using (var conn = new SqlConnection("Server=.;Integrated Security=true"))
- {
- conn.Open();
- using (var cmd = new SqlCommand("[AdventureWorks2016CTP3].[dbo].[uspGetEmployeeManagers]", conn))
- {
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Parameters.AddWithValue("@BusinessEntityID", 10);
- using (var reader = cmd.ExecuteReader())
- {
- while (reader.Read())
- {
- Console.WriteLine(reader["FirstName"]);
- }
- }
- }
- }
视图
使用上与普通的数据表没有差别.
- using (var conn = new SqlConnection("Server=.;Integrated Security=true"))
- {
- conn.Open();
- using (var cmd = new SqlCommand("select top 10 * from [AdventureWorks2016CTP3].[HumanResources].[vEmployee]", conn))
- {
- using (var reader = cmd.ExecuteReader())
- {
- while (reader.Read())
- {
- Console.WriteLine(reader["FirstName"]);
- }
- }
- }
- }
以上代码例子里的数据库使用的是 AdventureWorks Databases and Scripts for SQL Server 2016 CTP3 https://www.microsoft.com/en-us/download/details.aspx?id=49502
来源: https://www.cnblogs.com/kenwoo/p/9563015.html