1.ado.net 概述
ado.net用于c#程序中的关系数据库的访问。本节代码下载地址：打开网页www.wrox.com/go/professioncsharp6 单击download code选项卡下载第37章代码。本节使用AdventureWork2014数据库。这个数据库可以从https://msftdbprodsamples.codeplex.com/中下载。另外本节代码还需引入nuget包：Microsoft.Extensions.Configuration.Json和Microsoft.Extensions.Configuration还有System.Data.SqlClient包。还有使用下述命名空间：
using Microsoft.Extensions.Configuration;using System;using System.Data;using System.Data.SqlClient;using static System.Console;using System.Threading.Tasks;
2.使用数据库连接
a.直接使用连接串连接
???????public static void OpenConnection() ???????{ ???????????string connectionString = @"server=localhost\SQLEXPRESS;" + ???????????????????????????"integrated security=SSPI;" + ???????????????????????????"database=AdventureWorks2014"; ???????????var connection = new SqlConnection(connectionString); ???????????connection.Open(); ???????????// Do something useful ???????????WriteLine("connection opened"); ???????????connection.Close(); ???????} ???
b.从配置文件中获取连接串
???????public static string GetConnectionString() ???????{ ???????????var configurationBuilder = new ConfigurationBuilder().AddJsonFile("config.json"); ???????????IConfiguration config = configurationBuilder.Build(); ???????????string connectionString = config["Data:DefaultConnection:ConnectionString"]; ???????????return connectionString; ???????}json文件内容如下：
{"Data": { ???"DefaultConnection": { ?????"ConnectionString": "server=localhost\\SQLEXPRESS;Database=AdventureWorks2014;Trusted_Connection=True;" ???}}}c.数据库连接类sqlconnnection重要属性：InfoMessage和StateChange
每次从sqlserver 返回一个信息或警告消息，就会触发Info Message。连接状态改变时就会触发StateChange事件，代码如下：
???????public static void ConnectionInformation() ???????{ ???????????using (var connection = new SqlConnection(GetConnectionString())) ???????????{ ???????????????connection.InfoMessage += (sender, e) => ???????????????{ ???????????????????WriteLine($"warning or info {e.Message}"); ???????????????}; ???????????????connection.StateChange += (sender, e) => ???????????????{ ???????????????????WriteLine($"current state: {e.CurrentState}, before: {e.OriginalState}"); ???????????????}; ???????????????connection.Open(); ???????????????WriteLine("connection opened"); ???????????????// Do something useful ???????????} ???????}3.command 命令
a,新建command命令有几种方式：
???????????????????//方法一 ???????????????????var command = new SqlCommand(sql, connection); ???????????????????//方法二 ???????????????????var command = connection.CreateCommand(); ???????????????????command.CommandText = sql; ???????????????????//方法三 ???????????????????var command = new SqlCommand(); ??????????????????????????????????????command.CommandText = sql; ???????????????????command.Connection = connection;
其中方法二三添加一行代码可用于存储结构的执行：
private static void StoredProcedure(int entityId) ???????{ ???????????using (var connection = new SqlConnection(GetConnectionString())) ???????????{ ???????????????SqlCommand command = connection.CreateCommand(); ???????????????command.CommandText = "[dbo].[uspGetEmployeeManagers]"; ???????????????command.CommandType = CommandType.StoredProcedure; ???????????????SqlParameter p1 = command.CreateParameter(); ???????????????p1.SqlDbType = SqlDbType.Int; ???????????????p1.ParameterName = "@BusinessEntityID"; ???????????????p1.Value = entityId; ???????????????command.Parameters.Add(p1); ???????????????connection.Open(); ???????????????using (SqlDataReader reader = command.ExecuteReader()) ???????????????{ ???????????????????while (reader.Read()) ???????????????????{ ???????????????????????int recursionLevel = (int)reader["RecursionLevel"]; ???????????????????????int businessEntityId = (int)reader["BusinessEntityID"]; ???????????????????????string firstName = (string)reader["FirstName"]; ???????????????????????string lastName = (string)reader["LastName"]; ???????????????????????WriteLine($"{recursionLevel} {businessEntityId} {firstName} {lastName}"); ???????????????????} ???????????????} ???????????} ???????}以及事务的执行：
private static async Task TransactionSample() ???????{ ???????????using (var connection = new SqlConnection(GetConnectionString())) ???????????{ ???????????????await connection.OpenAsync(); ???????????????SqlTransaction tx = connection.BeginTransaction(); ???????????????try ???????????????{ ???????????????????string sql = "INSERT INTO Sales.CreditCard (CardType, CardNumber, ExpMonth, ExpYear)" + ???????????????????????"VALUES (@CardType, @CardNumber, @ExpMonth, @ExpYear); " + ???????????????????????"SELECT SCOPE_IDENTITY()";
//构造函数里赋值 //var command=new sqlCommand（sql,connection,tx）;
//在外面赋值 ???????????????????var command =connection.CreateCommand();// new SqlCommand(); ???????????????????command.CommandText = sql; ???????????????????//执行事务
command.Transaction = tx; ???????????????????var p1 = new SqlParameter("CardType", SqlDbType.NVarChar, 50); ???????????????????var p2 = new SqlParameter("CardNumber", SqlDbType.NVarChar, 25); ???????????????????var p3 = new SqlParameter("ExpMonth", SqlDbType.TinyInt); ???????????????????var p4 = new SqlParameter("ExpYear", SqlDbType.SmallInt); ???????????????????command.Parameters.AddRange(new SqlParameter[] { p1, p2, p3, p4 }); ???????????????????command.Parameters["CardType"].Value = "MegaWoosh"; ???????????????????command.Parameters["CardNumber"].Value = "08154711123"; ???????????????????command.Parameters["ExpMonth"].Value = 4; ???????????????????command.Parameters["ExpYear"].Value = 2019; ???????????????????//异步可以获取最大id ???????????????????object id = await command.ExecuteScalarAsync(); ???????????????????WriteLine($"record added with id: {id}"); ???????????????????command.Parameters["CardType"].Value = "NeverLimits"; ???????????????????command.Parameters["CardNumber"].Value = "987654321015"; ???????????????????command.Parameters["ExpMonth"].Value = 12; ???????????????????command.Parameters["ExpYear"].Value = 2025; ???????????????????id = await command.ExecuteScalarAsync(); ???????????????????WriteLine($"record added with id: {id}"); ???????????????????// throw new Exception("abort"); ???????????????????tx.Commit(); ???????????????} ???????????????catch (Exception ex) ???????????????{ ???????????????????WriteLine($"error {ex.Message}, rolling back"); ???????????????????tx.Rollback(); ???????????????} ???????????} ???????}b.command 需要传递参数时的几种方式：
switch (method) { ???????????????case 1: ???????????????????var productIdParamter = new SqlParameter("ProductId", SqlDbType.Int); ???????????????????productIdParamter.Value = productId; ???????????????????command.Parameters.Add(productIdParamter); ???????????????????break; ???????????????case 2: ???????????????????command.Parameters.AddWithValue("ProductId", productId); ???????????????????break; ???????????????case 3: ???????????????????command.Parameters.Add("ProductId", SqlDbType.Int); ???????????????????command.Parameters["ProductId"].Value = productId; ???????????????????break; ???????????????case 4: ???????????????????var p1 = command.CreateParameter(); ???????????????????p1.SqlDbType = SqlDbType.Int; ???????????????????p1.ParameterName = "ProductId"; ???????????????????p1.Value = productId; ???????????????????command.Parameters.Add(p1); ???????????????????break; ???????????????case 5: ???????????????????var p2 = new SqlParameter("ProductId", SqlDbType.Int); ???????????????????command.Parameters.Add(p2); ???????????????????command.Parameters["ProductId"].Value = productId; ???????????????????break; ???????????????case 6: ???????????????????command.Parameters.Add(new SqlParameter("ProductId", productId)); ???????????????????break; ???????????????default: ???????????????????command.Parameters.AddWithValue("ProdictId",productId); ???????????????????break; ??????????????????????????????}sqlParameter()常见使用的构造函数如下：
3.三种command执行方法：
a.ExecuteNonQuery 返回当前操作的影响行数，可用于insert delete update
public static void ExecuteNonQuery() ???????{ ???????????try ???????????{ ???????????????using (var connection = new SqlConnection(GetConnectionString())) ???????????????{ ???????????????????string sql = "INSERT INTO [Sales].[SalesTerritory] ([Name], [CountryRegionCode], [Group]) " + ???????????????????????"VALUES (@Name, @CountryRegionCode, @Group)"; ???????????????????//方法一 ???????????????????//var command = new SqlCommand(sql, connection); ???????????????????//方法二 ???????????????????//var command = connection.CreateCommand(); ???????????????????//command.CommandText = sql; ???????????????????//方法三 ???????????????????var command = new SqlCommand(); ??????????????????????????????????????command.CommandText = sql; ???????????????????command.Connection = connection; ???????????????????command.Parameters.AddWithValue("Name", "Austria16"); ???????????????????command.Parameters.AddWithValue("CountryRegionCode", "AT"); ???????????????????command.Parameters.AddWithValue("Group", "Europe1"); ???????????????????connection.Open(); ???????????????????object records = command.ExecuteNonQuery(); ???????????????????WriteLine($"{records} inserted"); ???????????????} ???????????} ???????????catch (SqlException ex) ???????????{ ???????????????WriteLine(ex.Message); ???????????} ???????}b.ExecuteScalar 返回一行一列的信息
???????private static void ExecuteScalar() ???????{ ???????????using (var connection = new SqlConnection(GetConnectionString())) ???????????{ ???????????????string sql = "SELECT COUNT(*) FROM Production.Product"; ???????????????SqlCommand command = connection.CreateCommand(); ???????????????command.CommandText = sql; ???????????????connection.Open(); ???????????????object count = command.ExecuteScalar(); ???????????????WriteLine($"counted {count} product records"); ???????????} ???????}c.ExecuteReader()方法：用于读取数据库表的多行信息。注 ：1.CommandBehavior.CloseConnection表示当command的结束对应的connection也随之关闭。2.
reader.GetInt32(0)就相当于(int)reader[0]或者（int）reader["id"]3.当reader为null时GetInt32等函数会报错
private static string GetProductInformationSQL() =>
???????????"SELECT Prod.ProductID, Prod.Name, Prod.StandardCost, Prod.ListPrice, CostHistory.StartDate, CostHistory.EndDate, CostHistory.StandardCost " +
???????????????"FROM Production.ProductCostHistory AS CostHistory ?" +
???????????????"INNER JOIN Production.Product AS Prod ON CostHistory.ProductId = Prod.ProductId " +
?????????????????"WHERE Prod.ProductId = @ProductId";
public static void ExecuteReader(int productId) ???????{ ???????????var connection = new SqlConnection(GetConnectionString()); ???????????string sql = GetProductInformationSQL(); ???????????var command = new SqlCommand(sql, connection); ???????????SqlParameter p1 = command.CreateParameter(); ???????????p1.SqlDbType = SqlDbType.Int; ???????????p1.ParameterName = "@ProductId"; ???????????p1.Value = productId; ???????????command.Parameters.Add(p1); ???????????connection.Open(); ???????????using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection)) ???????????{ ???????????????while (reader.Read()) ???????????????{ ???????????????????int id = reader.GetInt32(0); ???????????????????string name = reader.GetString(1); ???????????????????DateTime from = reader.GetDateTime(4); ???????????????????DateTime? to = reader.IsDBNull(5) ? (DateTime?)null : reader.GetDateTime(5); ???????????????????decimal standardPrice = reader.GetDecimal(6); ???????????????????WriteLine($"{id} {name} from: {from:d} to: {to:d}; price: {standardPrice}"); ???????????????} ???????????} ???????}
4.异步执行办法：如通过 ReadAsync(714).wait()方式调用
???????public static async Task ReadAsync(int productId) ???????{ ???????????var connection = new SqlConnection(GetConnectionString()); ???????????string sql = GetProductInformationSQL(); ???????????var command = new SqlCommand(sql, connection); ???????????var productIdParameter = new SqlParameter("ProductId", SqlDbType.Int); ???????????productIdParameter.Value = productId; ???????????command.Parameters.Add(productIdParameter); ???????????await connection.OpenAsync(); ???????????using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection)) ???????????{ ???????????????while (await reader.ReadAsync()) ???????????????{ ???????????????????int id = reader.GetInt32(0); ???????????????????string name = reader.GetString(1); ???????????????????DateTime from = reader.GetDateTime(4); ???????????????????DateTime? to = reader.IsDBNull(5) ? (DateTime?)null : reader.GetDateTime(5); ???????????????????decimal standardPrice = reader.GetDecimal(6); ???????????????????WriteLine($"{id} {name} from: {from:d} to: {to:d}; price: {standardPrice}"); ???????????????} ???????????} ???????}ado .net 学习心得
原文地址：https://www.cnblogs.com/luanjie/p/10263196.html