. NET Core Development Log--ado. NET and SQL Server

In the. NET world, if you want to operate a database, there is no need for ADO. The familiar libraries and APIs are also inseparable from the. NET core. Here's a brief description of how SQL Server is handled differently by ADO.

System.Data.SqlClient

The first step is to see if there are any System.Data.SqlClient references in project engineering, and if not, you can install them in the following three ways:

• Visualize NuGet package management tools on Visual Studio (Manage NuGet Packages), find the appropriate class library, click the Install button
• The same is the Package Management Console (Pack Manager console) input command on Visual Studio,Install-Package System.Data.SqlClient
• Command line Interface (CLI) to use the command,dotnet add package System.Data.SqlClient

Inquire

The most basic ExecuteReader method can be used:

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"]);            }        }    }}

You can also use SqlDataAdapter and datasets later in. NET Core 2.0 to see that Microsoft has done a lot of work with backward compatibility:

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"]);    }}

Insert

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();    }}

Update

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();    }}

Delete

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();    }}

Stored Procedures

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"]);            }        }    }}

View

There is no difference between the use and the normal data table.

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"]);            }        }    }}

The database in the above code example uses AdventureWorks Databases and Scripts for SQL Server CTP3

. NET Core Development Log--ado. NET and SQL Server