ado .net 学习心得 -数据天堂

ado .net 学习心得

# 前端 2024-04-03 15:44 0 12 来源：数据天堂

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