一、什么是ADO.NET
简单的讲，**ADO.NET是一组允许.NET开发人员使用标准的，结构化的，甚至无连接的方式与数据交互的技术。**对于ADO.NET来说，可以处理数据源是多样的。可以是应用程序唯一使用的创建在内存中数据，也可以是与应用程序分离，存储在存储区域的数据（如文本文件、XML、关系数据库等）。
具体来说，ADO.NET 对 Microsoft SQL Server 和 XML 等数据源以及通过 OLE DB 和 XML 公开的数据源提供一致的访问。数据共享使用者应用程序可以使用 ADO.NET 来连接到这些数据源，并检索、处理和更新所包含的数据。
作为.NET框架的重要组成部分，ADO.NET 类封装在 System.Data.dll 中，并且与 System.Xml.dll 中的 XML 类集成。当编译使用 System.Data 命名空间的代码时，需要引用System.Data.dll 和 System.Xml.dll。
二、使用ADO.NET的三种方式
- 连接模式
您的代码库显式连接到基础数据存储并与之断开连接。当您以这种方式使用ADO.NET时，通常使用连接对象，命令对象和数据读取器对象与数据存储进行交互。 - 断开模式
断开连接模式允许您操作一组DataTable对象（包含在DataSet中），该对象充当外部数据的客户端副本。 调用者收到数据集后，便可以遍历和操纵内容。 如果调用者想将更改提交回数据存储，则使用数据适配器（与一组SQL语句结合）来更新数据源。 - 使用ORM
第三种方法是使用对象关系映射器(ORM)，例如NHibernate或Entity Framework。 对象关系映射器使用C＃对象以应用程序为中心的方式表示数据，并从开发人员手中提取许多数据访问代码。 EF还允许您使用强类型LINQ查询进行程序与数据库的交互，动态查询创建特定于数据库的查询。
有关命名空间：
System.Data.dll
System.Data.Common.dll
System.Data.Sql
System.Data.SqlTypes
Microsoft.SqlServer.Server
三、ADO.NET数据提供程序
ADO.NET数据提供程序(Data Provider)核心对象
Conection
Command
DataReader
DataAdapter
Parameter
Transaction
ADO.NET核心组件及用途：
微软提供的ADO.NET数据提供程序：
| Data Provider | 命名空间 | 程序集 | 作用 |
|---|---|---|---|
| OLE DB | System.Data.OleDb | System.Data.dll | 提供对使用 OLE DB 公开的数据源中数据的访问。使用 System.Data.OleDb 命名空间。 |
| Microsoft SQL Server | System.Data.SqlClient | System.Data.dll | 提供对 Microsoft SQL Server 7.0 或更高版本中数据的访问。使用 System.Data.SqlClient 命名空间。 |
| ODBC | System.Data.Odbc | System.Data.dll | 提供对使用 ODBC 公开的数据源中数据的访问。使用 System.Data.Odbc 命名空间。 |
四、使用接口抽象化DataProvider(Data Provider的动态切换)
使用Enum枚举和App.config,根据自定义枚举类型获取数据库连接对象Connection,示例代码：
using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using static System.Console;
namespace MyConnectionFactory
{
class Program
{
static void Main(string[] args)
{
WriteLine("*** 数据连接工厂 ***
");
//根据App.config配置文件获取连接类型
string dataProviderString = ConfigurationManager.AppSettings["provider"];
DataProvider dataProvider = DataProvider.None;
//根据自定义枚举类型获取数据连接对象Connection
if (Enum.IsDefined(typeof(DataProvider), dataProviderString))
{
dataProvider = (DataProvider)Enum.Parse(typeof(DataProvider), dataProviderString);
}
else
{
WriteLine("Sorry,no provider exists!");
ReadLine();
return;
}
IDbConnection myConnection = GetConnection(dataProvider);
WriteLine($"Your connection is a {myConnection.GetType().Name}");
ReadLine();
}
/// <summary>
/// //根据自定义枚举类型获取数据连接对象Connection
/// </summary>
/// <param name="dataProvider"></param>
/// <returns></returns>
private static IDbConnection GetConnection(DataProvider dataProvider)
{
IDbConnection connection = null;
switch (dataProvider)
{
case DataProvider.SqlServer:
connection = new SqlConnection();
break;
case DataProvider.OleDb:
connection = new OleDbConnection();
break;
case DataProvider.Odbc:
connection = new OleDbConnection();
break;
}
return connection;
}
enum DataProvider
{
SqlServer,
OleDb,
Odbc,
None
}
}
}
App.config文件内容：
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<!-- This key value maps to one of our enum values. -->
<add key="provider" value="SqlServer"/>
</appSettings>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
</startup>
</configuration>
五、ADO.NET数据提供程序工厂模型
.NET数据提供程序工厂模式使您可以使用通用数据访问类型来构建单个代码库。 此外，使用应用程序配置文件（和子元素），可以以声明方式获取提供程序和连接字符串，而无需重新编译或重新部署使用ADO.NET API的程序集。
DataProvider Factory示例程序：通过配置文件获取Data Provider类型和连接字符串：
using System.Configuration;
using System.Data.Common;
using System.Data.SqlClient;
using static System.Console;
namespace DataProviderFactory2
{
class Program
{
static void Main(string[] args)
{
WriteLine("*** 数据提供程序工厂 ***
");
string dataProvider = ConfigurationManager.AppSettings["provider"];
string connectionString = ConfigurationManager.ConnectionStrings["AutoLotSqlProvider"].ConnectionString;
//获取数据提供程序工厂
DbProviderFactory factory = DbProviderFactories.GetFactory(dataProvider);
using (DbConnection connection = factory.CreateConnection())
{
if (connection == null)
{
ShowError("Connection");
return;
}
WriteLine($"数据连接对象为: {connection.GetType().Name}");
connection.ConnectionString = connectionString;
connection.Open();
var sqlConnection = connection as SqlConnection;
if (sqlConnection != null)
{
WriteLine("sql server实例的版本:" + sqlConnection.ServerVersion);
}
//创建命令对象
DbCommand command = factory.CreateCommand();
if (command == null)
{
ShowError("Command");
return;
}
WriteLine($"命令对象为:{command.GetType().Name}");
command.Connection = connection;
command.CommandText = "select * from inventory";
//这里sql语句可以为多个，例如：“Select * From Inventory;Select * From Customers”;如果是多个语句查询出来的结果是依次列出，而不是并列查询;
using (DbDataReader dataReader = command.ExecuteReader())
{
WriteLine($"数据读取器对象为:{dataReader.GetType().Name}");
WriteLine("
*** 当前库存 ***");
while (dataReader.Read())
{
WriteLine($"-> Car #{dataReader["CarId"]} is a {dataReader["Make"]}.");
}
}
ReadLine();
}
}
private static void ShowError(string objectName)
{
WriteLine($"创建{objectName}出粗");
ReadLine();
}
}
}
App.config文件内容：
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="provider" value="System.Data.SqlClient"/>
</appSettings>
<connectionStrings>
<add name="AutoLotSqlProvider" connectionString="Data Source=.;Initial Catalog=AutoLot;Integrated Security=true;"/>
<add name="AutoLotOleDbProvider" connectionString="Provider=SQLNCLI11;Data Source=.;Initial Catalog=AutoLot;Integrated Security=SSPI;"/>
</connectionStrings>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
</startup>
</configuration>
六、ADO.NET连接模式
连接模式使用到Connection,Command,DataReader对象，需要显式地打开关闭数据库。
使用连接模式的操作数据库的步骤：
-
创建connection对象，配置连接字符串
-
创建command对象，构造函数参数为sql查询语句和connection对象
-
调用command对象的ExecuteReader方法获取DataReader
-
调用DataReader的Read方法读取数据
-
示例程序：使用连接模式示例，用DataReader读取数据
using System.Data.SqlClient;
using static System.Console;
namespace AutoLotDataReader
{
class Program
{
static void Main(string[] args)
{
WriteLine("***** 使用 Readers *****
");
// 通过Builder创建数据库连接字符串ConnectionString
// 也可以直接使用字符串
//var strConnect = @"Data Source=.;Initial Catalog=AutoLot;Integrated Security=true";
var cnStringBuilder = new SqlConnectionStringBuilder
{
InitialCatalog = "AutoLot",
DataSource = @".",
ConnectTimeout = 30,
IntegratedSecurity = true
};
// 创建数据库连接对象Connection
using (var connection = new SqlConnection())
{
connection.ConnectionString = cnStringBuilder.ConnectionString;
connection.Open();
ShowConnectionStatus(connection);
// 创建命令对象Command
string sql = "Select * From Inventory;Select * from Customers";
using (SqlCommand myCommand = new SqlCommand(sql, connection))
{
// 通过ExecuteReader获取DataReader对象
// Obtain a data reader a la ExecuteReader().
using (SqlDataReader myDataReader = myCommand.ExecuteReader())
{
do
{
//Read方法读取数据
while (myDataReader.Read())
{
WriteLine("***** Record *****");
for (int i = 0; i < myDataReader.FieldCount; i++)
{
WriteLine($"{myDataReader.GetName(i)} = {myDataReader.GetValue(i)}");
}
WriteLine();
}
} while (myDataReader.NextResult());
}
}
}
ReadLine();
}
/// <summary>
/// 显示数据库连接信息
/// </summary>
/// <param name="connection"></param>
private static void ShowConnectionStatus(SqlConnection connection)
{
WriteLine("**** 数据库连接信息: ***");
WriteLine($"数据库连接地址: {connection.DataSource}");
WriteLine($"数据库名称: {connection.Database}");
WriteLine($"连接超时时间: {connection.ConnectionTimeout}");
WriteLine($"连接状态:{connection.State}
");
}
}
}
- 创建、修改、删除数据
准备工作：创建DAL类库
using AutoLotDAL.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace AutoLotDAL.DataOperations
{
public class InventoryDAL
{
private readonly string connectionString;
private SqlConnection sqlConnection = null;
public InventoryDAL() : this(@"Data Source=.;Initial Catalog=AutoLot;Integrated Security=true")
{
}
public InventoryDAL(string connectionString)
{
this.connectionString = connectionString;
}
/// <summary>
/// 获取数据集合
/// </summary>
/// <returns></returns>
public List<Car> GetAllInventory()
{
OpenConnection();
List<Car> inventory = new List<Car>();
string sql = "select * from inventory";
using (SqlCommand command = new SqlCommand(sql, sqlConnection))
{
command.CommandType = CommandType.Text;
SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader.Read())
{
inventory.Add(new Car
{
CarId = (int)dataReader["CarId"],
Color = (string)dataReader["Color"],
Make = (string)dataReader["Make"],
PetName = (string)dataReader["PetName"]
});
}
dataReader.Close();
}
return inventory;
}
/// <summary>
/// 查询单个数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Car GetCar(int id)
{
OpenConnection();
Car car = null;
string sql = $"select * from inventory where carid={id}";
using (SqlCommand command = new SqlCommand(sql, sqlConnection))
{
command.CommandType = CommandType.Text;
SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader.Read())
{
car = new Car
{
CarId = (int)dataReader["CarId"],
Color = (string)dataReader["Color"],
Make = (string)dataReader["Make"],
PetName = (string)dataReader["PetName"]
};
}
dataReader.Close();
}
return car;
}
/// <summary>
/// 插入数据
/// </summary>
/// <param name="color"></param>
/// <param name="make"></param>
/// <param name="petName"></param>
public void InsertAuto(string color, string make, string petName)
{
OpenConnection();
// Format and execute SQL statement.
string sql = $"Insert Into Inventory (Make, Color, PetName) Values ('{make}', '{color}','{petName}')";
// Execute using our connection.
using (SqlCommand command = new SqlCommand(sql, sqlConnection))
{
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
CloseConnection();
}
public void InsertAuto(Car car)
{
OpenConnection();
// Format and execute SQL statement.
string sql = "Insert Into Inventory (Make, Color, PetName) Values " +
$"('{car.Make}', '{car.Color}', '{car.PetName}')";
// Execute using our connection.
using (SqlCommand command = new SqlCommand(sql, sqlConnection))
{
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
CloseConnection();
}
/// <summary>
/// 参数化sql语句
/// </summary>
/// <param name="car"></param>
public void InsertAuto2(Car car)
{
OpenConnection();
string sql = "Insert into Inventory" +
"(Make,Color,PetName) Values" +
"(@Make,@Color,@PetName)";
using (SqlCommand command = new SqlCommand(sql, sqlConnection))
{
command.Parameters.Add(new SqlParameter
{
ParameterName = "@Make",
Value = car.Make,
SqlDbType = SqlDbType.Char,
Size = 10
});
command.Parameters.Add(new SqlParameter
{
ParameterName = "@Color",
Value = car.Color,
SqlDbType = SqlDbType.Char,
Size = 10
});
command.Parameters.Add(new SqlParameter
{
ParameterName = "@PetName",
Value = car.PetName,
SqlDbType = SqlDbType.Char,
Size = 10
});
command.ExecuteNonQuery();
CloseConnection();
}
}
/// <summary>
/// 删除数据
/// </summary>
/// <param name="id"></param>
public void DeleteCar(int id)
{
OpenConnection();
// Get ID of car to delete, then do so.
string sql = $"Delete from Inventory where CarId = '{id}'";
using (SqlCommand command = new SqlCommand(sql, sqlConnection))
{
try
{
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
catch (SqlException ex)
{
Exception error = new Exception("Sorry! That car is on order!", ex);
throw error;
}
}
CloseConnection();
}
/// <summary>
/// 更新数据
/// </summary>
/// <param name="id"></param>
/// <param name="newPetName"></param>
public void UpdateCarPetName(int id, string newPetName)
{
OpenConnection();
// Get ID of car to modify the pet name.
string sql = $"Update Inventory Set PetName = '{newPetName}' Where CarId = '{id}'";
using (SqlCommand command = new SqlCommand(sql, sqlConnection))
{
command.ExecuteNonQuery();
}
CloseConnection();
}
/// <summary>
/// 使用存储过程
/// </summary>
/// <param name="carId"></param>
/// <returns></returns>
public string LookUpPetName(int carId)
{
OpenConnection();
string carPetName;
//创建存储过程命令
using (SqlCommand command = new SqlCommand("GetPetName", sqlConnection))
{
command.CommandType = CommandType.StoredProcedure;
SqlParameter parameter = new SqlParameter
{
ParameterName = "@carId",
SqlDbType = SqlDbType.Int,
Value = carId,
Direction = ParameterDirection.Input
};
command.Parameters.Add(parameter);
parameter = new SqlParameter
{
ParameterName = "@petName",
SqlDbType = SqlDbType.Char,
Size = 10,
Direction = ParameterDirection.Output
};
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
carPetName = (string)command.Parameters["@petName"].Value;
CloseConnection();
}
return carPetName;
}
/// <summary>
/// 使用事务
/// </summary>
/// <param name="throwEx"></param>
/// <param name="custId"></param>
public void ProcessCreditRisk(bool throwEx, int custId)
{
OpenConnection();
string fName;
string lName;
var cmdSelect = new SqlCommand($"Select * from Customers where CustId = {custId}", sqlConnection);
using (var dataReader = cmdSelect.ExecuteReader())
{
if (dataReader.HasRows)
{
dataReader.Read();
fName = (string)dataReader["FirstName"];
lName = (string)dataReader["LastName"];
}
else
{
CloseConnection();
return;
}
}
// Create command objects that represent each step of the operation.
var cmdRemove =
new SqlCommand($"Delete from Customers where CustId = {custId}", sqlConnection);
var cmdInsert =
new SqlCommand("Insert Into CreditRisks" + $"(FirstName, LastName) Values('{fName}', '{lName}')", sqlConnection);
SqlTransaction tx = null;
try
{
tx = sqlConnection.BeginTransaction();
cmdInsert.Transaction = tx;
cmdRemove.Transaction = tx;
cmdInsert.ExecuteNonQuery();
cmdRemove.ExecuteNonQuery();
if (throwEx)
{
throw new Exception("事务提交发生错误");
}
tx.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
tx?.Rollback();
}
finally
{
CloseConnection();
}
}
/// <summary>
/// 打开数据库
/// </summary>
private void OpenConnection()
{
sqlConnection = new SqlConnection { ConnectionString = connectionString };
sqlConnection.Open();
}
/// <summary>
/// 关闭数据库
/// </summary>
private void CloseConnection()
{
if (sqlConnection?.State != ConnectionState.Closed)
{
sqlConnection?.Close();
}
}
}
}
使用DAL处理数据：
using AutoLotDAL.DataOperations;
using AutoLotDAL.Models;
using System;
using System.Linq;
namespace AutoLotClient
{
class Program
{
static void Main(string[] args)
{
InventoryDAL dal = new InventoryDAL();
//查询数据
var list = dal.GetAllInventory();
Console.WriteLine("*** All Cars ***");
Console.WriteLine("CarId Make Color Pet Name");
foreach (var item in list)
{
Console.WriteLine($"{item.CarId} {item.Make} {item.Color} {item.PetName}");
}
Console.WriteLine();
//查询数据
var car = dal.GetCar(list.OrderBy(x => x.Color).Select(x => x.CarId).First());
Console.WriteLine("*** First Car By Color ***");
Console.WriteLine("CarId Make Color Pet Name");
Console.WriteLine($"{car.CarId} {car.Make} {car.Color} {car.PetName}");
try
{
//删除数据
dal.DeleteCar(5);
Console.WriteLine("Car deleted.");
}
catch (Exception ex)
{
Console.WriteLine($"An exception occurred: {ex.Message}");
}
//插入数据
dal.InsertAuto(new Car { Color = "Blue", Make = "Pilot", PetName = "TowMonster" });
list = dal.GetAllInventory();
var newCar = list.First(x => x.PetName == "TowMonster");
Console.WriteLine(" ************** New Car ************** ");
Console.WriteLine("CarId Make Color Pet Name");
Console.WriteLine($"{newCar.CarId} {newCar.Make} {newCar.Color} {newCar.PetName}");
dal.DeleteCar(newCar.CarId);
//使用存储过程查询数据
var petName = dal.LookUpPetName(car.CarId);
Console.WriteLine(" ************** New Car ************** ");
Console.WriteLine($"Car pet name: {petName}");
Console.Write("Press enter to continue...");
Console.ReadLine();
}
/// <summary>
/// 使用事务
/// </summary>
public static void MoveCustomer()
{
Console.WriteLine("事务示例");
bool throwEx = true;
Console.WriteLine("是否抛出事务异常:");
var userAnswer = Console.ReadLine();
if (userAnswer?.ToLower() == "n")
{
throwEx = false;
}
var dal = new InventoryDAL();
dal.ProcessCreditRisk(throwEx, 1);
Console.WriteLine("检查 CreditRisk表");
Console.ReadLine();
}
}
}
- 使用SqlBulkCopy批量插入数据
SqlBulkCopy的WriteToServer()能够批量插入数据，它的参数为DataTable,DataReader,DataRows类型。这里使用自定义DataReaer,把泛型集合转换为DataReader
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
namespace AutoLotDAL.BulkImport
{
public interface IMyDataReader<T> : IDataReader
{
List<T> Records { get; set; }
}
public class MyDataReader<T> : IMyDataReader<T>
{
private int _currentIndex = -1;
private readonly PropertyInfo[] _propertyInfos;
private readonly Dictionary<string, int> _nameDictionary;
public MyDataReader()
{
_propertyInfos = typeof(T).GetProperties();
_nameDictionary = _propertyInfos
.Select((x, index) => new { x.Name, index })
.ToDictionary(pair => pair.Name, pair => pair.index);
}
public MyDataReader(List<T> records) : this()
{
Records = records;
}
public List<T> Records { get; set; }
public void Dispose()
{
}
public bool Read()
{
if ((_currentIndex + 1) >= Records.Count) return false;
_currentIndex++;
return true;
}
public int FieldCount
=> _propertyInfos.Length;
public string GetName(int i)
=> i >= 0 && i < FieldCount ? _propertyInfos[i].Name : string.Empty;
public int GetOrdinal(string name)
=> _nameDictionary.ContainsKey(name) ? _nameDictionary[name] : -1;
public object GetValue(int i)
=> _propertyInfos[i].GetValue(Records[_currentIndex]);
//public object GetValue(int i)
//{
// Car currentRecord = Records[_currentIndex] as Car;
// switch (i)
// {
// case 0: return currentRecord.CarId;
// case 1: return currentRecord.Color;
// case 2: return currentRecord.Make;
// case 3: return currentRecord.PetName;
// default: return string.Empty;
// }
//}
public string GetDataTypeName(int i) => throw new NotImplementedException();
public Type GetFieldType(int i) => throw new NotImplementedException();
public int GetValues(object[] values) => throw new NotImplementedException();
public bool GetBoolean(int i) => throw new NotImplementedException();
public byte GetByte(int i) => throw new NotImplementedException();
public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
=> throw new NotImplementedException();
public char GetChar(int i) => throw new NotImplementedException();
public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
=> throw new NotImplementedException();
public Guid GetGuid(int i) => throw new NotImplementedException();
public short GetInt16(int i) => throw new NotImplementedException();
public int GetInt32(int i) => throw new NotImplementedException();
public long GetInt64(int i) => throw new NotImplementedException();
public float GetFloat(int i) => throw new NotImplementedException();
public double GetDouble(int i) => throw new NotImplementedException();
public string GetString(int i) => throw new NotImplementedException();
public decimal GetDecimal(int i) => throw new NotImplementedException();
public DateTime GetDateTime(int i) => throw new NotImplementedException();
public IDataReader GetData(int i) => throw new NotImplementedException();
public bool IsDBNull(int i) => throw new NotImplementedException();
object IDataRecord.this[int i] => throw new NotImplementedException();
object IDataRecord.this[string name] => throw new NotImplementedException();
public void Close() => throw new NotImplementedException();
public DataTable GetSchemaTable() => throw new NotImplementedException();
public bool NextResult() => throw new NotImplementedException();
public int Depth { get; }
public bool IsClosed { get; }
public int RecordsAffected { get; }
}
}
批量插入的方法：
public static void ExecuteBulkImport<T>(IEnumerable<T> records, string tableName)
{
OpenConnection();
using (SqlConnection conn = _sqlConnection)
{
SqlBulkCopy bc = new SqlBulkCopy(conn)
{
DestinationTableName = tableName
};
var dataReader = new MyDataReader<T>(records.ToList());
try
{
bc.WriteToServer(dataReader);
}
catch (Exception ex)
{
//Should do something here
}
finally
{
CloseConnection();
}
}
}
使用批量插入：
public static void DoBulkCopy()
{
Console.WriteLine(" ************** Do Bulk Copy ************** ");
var cars = new List<Car>
{
new Car() {Color = "Blue", Make = "Honda", PetName = "MyCar1"},
new Car() {Color = "Red", Make = "Volvo", PetName = "MyCar2"},
new Car() {Color = "White", Make = "VW", PetName = "MyCar3"},
new Car() {Color = "Yellow", Make = "Toyota", PetName = "MyCar4"}
};
ProcessBulkImport.ExecuteBulkImport(cars, "Inventory");
InventoryDAL dal = new InventoryDAL();
var list = dal.GetAllInventory();
Console.WriteLine(" ************** All Cars ************** ");
Console.WriteLine("CarId Make Color Pet Name");
foreach (var itm in list)
{
Console.WriteLine($"{itm.CarId} {itm.Make} {itm.Color} {itm.PetName}");
}
Console.WriteLine();
}