ADO.NET 连接
ADO.NET Connections 是从 C#应用程序连接到数据库的最简单方法之一。它们依赖于使用提供程序和指向数据库的连接字符串来执行查询。
通用数据提供程序类
以下许多类是常用于查询数据库及其相关名称空间的类:
SqlConnection
,SqlCommand
,SqlDataReader
来自System.Data.SqlClient
- 来自 tihuan 的
OleDbConnection
,OleDbCommand
,OleDbDataReader
- 来自 tihuan 的
MySqlConnection
,MySqlCommand
,MySqlDbDataReader
所有这些通常用于通过 C#访问数据,并且在构建以数据为中心的应用程序中通常会遇到这些数据。未提及实现相同 FooConnection
,FooCommand
,FooDataReader
类的许多其他类可以表现出相同的行为。
ADO.NET 连接的通用访问模式
通过 ADO.NET 连接访问数据时可以使用的常见模式可能如下所示:
// This scopes the connection (your specific class may vary)
using(var connection = new SqlConnection("{your-connection-string}")
{
// Build your query
var query = "SELECT * FROM YourTable WHERE Property = @property");
// Scope your command to execute
using(var command = new SqlCommand(query, connection))
{
// Open your connection
connection.Open();
// Add your parameters here if necessary
// Execute your query as a reader (again scoped with a using statement)
using(var reader = command.ExecuteReader())
{
// Iterate through your results here
}
}
}
或者,如果你只是执行简单更新而不需要阅读器,则适用相同的基本概念:
using(var connection = new SqlConnection("{your-connection-string}"))
{
var query = "UPDATE YourTable SET Property = Value WHERE Foo = @foo";
using(var command = new SqlCommand(query,connection))
{
connection.Open();
// Add parameters here
// Perform your update
command.ExecuteNonQuery();
}
}
你甚至可以针对一组通用接口进行编程,而不必担心提供者特定的类。ADO.NET 提供的核心接口是:
- IDbConnection - 用于管理数据库连接
- IDbCommand - 用于运行 SQL 命令
- IDbTransaction - 用于管理事务
- IDataReader - 用于读取命令返回的数据
- IDataAdapter - 用于将数据传入和传出数据集
var connectionString = "{your-connection-string}";
var providerName = "{System.Data.SqlClient}"; //for Oracle use "Oracle.ManagedDataAccess.Client"
//most likely you will get the above two from ConnectionStringSettings object
var factory = DbProviderFactories.GetFactory(providerName);
using(var connection = new factory.CreateConnection()) {
connection.ConnectionString = connectionString;
connection.Open();
using(var command = new connection.CreateCommand()) {
command.CommandText = "{sql-query}"; //this needs to be tailored for each database system
using(var reader = command.ExecuteReader()) {
while(reader.Read()) {
...
}
}
}
}