C#和SQL Server連接通訊
在 C# 中與 SQL Server 建立數據庫連接，主要通過 ADO.NET 技術實現。以下是幾種常見的連接方式及相關實踐：
ADO.NET 全面指南：C# 數據庫訪問核心技術
ADO.NET 是 .NET Framework 中用于數據訪問的核心組件，提供了一套強大的類庫，使應用程序能夠連接各種數據源（如 SQL Server、Oracle、MySQL 等）并與之交互。
圖片展示
腳本代碼
graph TDA[應用程序] --> B[數據提供程序]B --> C[SQL Server]B --> D[Oracle]B --> E[OLE DB]B --> F[ODBC]B --> G[其他數據源]subgraph ADO.NET 組件H[Connection] --> I[Command]I --> J[DataReader]I --> K[DataAdapter]K --> L[DataSet]L --> M[DataTable]L --> N[DataRelation]endA --> H
主要組件詳解
1. 數據提供程序 (Data Providers)
- SQL Server 提供程序：
System.Data.SqlClient - OLE DB 提供程序：
System.Data.OleDb - ODBC 提供程序：
System.Data.Odbc - Oracle 提供程序：
System.Data.OracleClient
2. 核心對象
- SqlConnection：管理與數據庫的連接
- SqlCommand：執行 SQL 語句或存儲過程
- SqlDataReader：提供高性能的只進只讀數據流
- SqlDataAdapter：在 DataSet 和數據庫之間架起橋梁
- DataSet：內存中的數據庫表示（斷開式數據訪問）
- DataTable：表示內存中的數據表
連接模式 vs 斷開模式
連接模式 (使用 DataReader)
using (SqlConnection connection = new SqlConnection(connectionString))
{connection.Open();string sql = "SELECT * FROM Products WHERE Price > @minPrice";using (SqlCommand command = new SqlCommand(sql, connection)){command.Parameters.AddWithValue("@minPrice", 50.00);using (SqlDataReader reader = command.ExecuteReader()){while (reader.Read()){Console.WriteLine($"Product: {reader["ProductName"]}, Price: {reader["Price"]}");}}}
}
斷開模式 (使用 DataSet/DataAdapter)
using (SqlConnection connection = new SqlConnection(connectionString))
{SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Customers", connection);DataSet dataSet = new DataSet();// 填充 DataSetadapter.Fill(dataSet, "Customers");// 處理數據（無需保持連接）DataTable customersTable = dataSet.Tables["Customers"];foreach (DataRow row in customersTable.Rows){Console.WriteLine($"Customer: {row["FirstName"]} {row["LastName"]}");}// 更新數據DataRow newRow = customersTable.NewRow();newRow["FirstName"] = "John";newRow["LastName"] = "Doe";customersTable.Rows.Add(newRow);// 將更改同步回數據庫SqlCommandBuilder builder = new SqlCommandBuilder(adapter);adapter.Update(dataSet, "Customers");
}
關鍵操作詳解
1. 參數化查詢（防止 SQL 注入）
using (SqlCommand cmd = new SqlCommand("INSERT INTO Users (Username, Email) VALUES (@username, @email)", connection))
{cmd.Parameters.Add("@username", SqlDbType.NVarChar, 50).Value = username;cmd.Parameters.Add("@email", SqlDbType.NVarChar, 100).Value = email;cmd.ExecuteNonQuery();
}
2. 執行存儲過程
using (SqlCommand cmd = new SqlCommand("GetCustomerOrders", connection))
{cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.AddWithValue("@CustomerID", customerId);using (SqlDataReader reader = cmd.ExecuteReader()){// 處理結果}
}
3. 事務處理
using (SqlConnection connection = new SqlConnection(connectionString))
{connection.Open();SqlTransaction transaction = connection.BeginTransaction();try{using (SqlCommand cmd1 = new SqlCommand("UPDATE Account SET Balance = Balance - 100 WHERE ID = 1", connection, transaction))using (SqlCommand cmd2 = new SqlCommand("UPDATE Account SET Balance = Balance + 100 WHERE ID = 2", connection, transaction)){cmd1.ExecuteNonQuery();cmd2.ExecuteNonQuery();transaction.Commit();Console.WriteLine("Transaction completed successfully.");}}catch (Exception ex){transaction.Rollback();Console.WriteLine($"Transaction rolled back: {ex.Message}");}
}
4. 異步操作
public async Task<List<Product>> GetProductsAsync()
{var products = new List<Product>();using (SqlConnection connection = new SqlConnection(connectionString)){await connection.OpenAsync();string sql = "SELECT ProductID, ProductName, UnitPrice FROM Products";using (SqlCommand command = new SqlCommand(sql, connection)){using (SqlDataReader reader = await command.ExecuteReaderAsync()){while (await reader.ReadAsync()){products.Add(new Product{ProductID = reader.GetInt32(0),ProductName = reader.GetString(1),UnitPrice = reader.GetDecimal(2)});}}}}return products;
}
最佳實踐
- 資源管理：始終使用
using語句確保對象正確釋放 - 連接管理：保持連接打開時間最短
- 參數化查詢：防止 SQL 注入攻擊
- 錯誤處理：使用 try-catch 塊處理數據庫異常
- 連接池：利用 ADO.NET 內置的連接池機制
- 異步操作：在 I/O 密集型操作中使用異步方法
- 安全存儲：將連接字符串存儲在配置文件中
- 類型安全：使用
GetInt32(),GetString()等方法而非索引器
ADO.NET vs Entity Framework
| 特性 | ADO.NET | Entity Framework |
|---|---|---|
| 抽象級別 | 低級別，直接 SQL 操作 | 高級別，面向對象 |
| 性能 | 更高（直接控制） | 良好（有優化空間） |
| 開發速度 | 較慢 | 更快（自動代碼生成） |
| 復雜性 | 需要更多代碼 | 簡化數據訪問 |
| 適用場景 | 高性能需求、復雜查詢 | 快速開發、ORM 需求 |
| 學習曲線 | 陡峭（需了解 SQL） | 較平緩（面向對象） |
ADO.NET 核心組件架構
1. 使用 SqlConnection 直接連接
核心命名空間：System.Data.SqlClient
基礎步驟
using System.Data.SqlClient;string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";using (SqlConnection connection = new SqlConnection(connectionString))
{connection.Open();// 執行數據庫操作（如 SqlCommand）using (SqlCommand cmd = new SqlCommand("SELECT * FROM Table", connection)){SqlDataReader reader = cmd.ExecuteReader();while (reader.Read()){// 處理數據}}
} // 自動關閉連接
2. 使用連接字符串構建器（SqlConnectionStringBuilder）
優勢：避免連接字符串拼寫錯誤，支持強類型屬性。
var builder = new SqlConnectionStringBuilder();
builder.DataSource = "localhost";
builder.InitialCatalog = "MyDatabase";
builder.UserID = "sa";
builder.Password = "securePassword";
builder.IntegratedSecurity = false; // 使用 SQL 身份驗證
builder.ConnectTimeout = 30; // 連接超時時間（秒）using (SqlConnection conn = new SqlConnection(builder.ConnectionString))
{conn.Open();// ... 操作數據庫
}
3. Windows 身份驗證（集成安全）
適用場景：使用當前 Windows 用戶憑據連接。
string connectionString = "Server=localhost;Database=MyDB;Integrated Security=True;";using (SqlConnection conn = new SqlConnection(connectionString))
{conn.Open();// ... 操作
}
4. 異步連接（Async/Await）
適用場景：避免阻塞 UI 線程，提高并發性能。
using (SqlConnection conn = new SqlConnection(connectionString))
{await conn.OpenAsync(); // 異步打開連接using (SqlCommand cmd = new SqlCommand("SELECT * FROM Table", conn)){using (SqlDataReader reader = await cmd.ExecuteReaderAsync()){while (await reader.ReadAsync()){// 異步讀取數據}}}
}
5. 從配置文件讀取連接字符串
步驟：
App.config / Web.config 中添加配置：
<configuration><connectionStrings><add name="MyDB" connectionString="Server=.;Database=MyDB;Integrated Security=True;" providerName="System.Data.SqlClient"/></connectionStrings>
</configuration>
C# 代碼中讀取：
using System.Configuration;string connStr = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{// ...
}
6. 依賴注入（DI）方式
適用場景：ASP.NET Core 等現代框架。
// Startup.cs 中注冊服務
services.AddScoped(_ => new SqlConnection(Configuration.GetConnectionString("DefaultConnection")));// 在 Controller 或 Service 中注入
public class MyService
{private readonly SqlConnection _connection;public MyService(SqlConnection connection){_connection = connection;}public async Task GetData(){await _connection.OpenAsync();// ... 操作}
}
7. 連接池優化
默認啟用：ADO.NET 自動管理連接池。
關鍵參數：
Max Pool Size：最大連接數（默認 100）Min Pool Size：最小保留連接數（默認 0）Pooling=True：啟用連接池（默認 true）
示例：
string connStr = "Server=.;Database=MyDB;Integrated Security=True;Max Pool Size=200;";
8. 使用 Entity Framework Core（ORM 方式）
非直接連接：通過 DbContext 抽象連接。
// 定義 DbContext
public class AppDbContext : DbContext
{protected override void OnConfiguring(DbContextOptionsBuilder options)=> options.UseSqlServer("Server=.;Database=MyDB;Integrated Security=True;");
}// 使用示例
using (var context = new AppDbContext())
{var users = context.Users.ToList(); // 自動管理連接
}
連接字符串關鍵參數說明
| 參數 | 說明 |
|---|---|
Server / Data Source | 服務器地址（如 localhost, ., 192.168.1.10） |
Database / Initial Catalog | 數據庫名 |
User Id | SQL 身份驗證用戶名 |
Password | SQL 身份驗證密碼 |
Integrated Security | 是否使用 Windows 身份驗證（true/false 或 SSPI） |
Connection Timeout | 連接超時時間（秒，默認 15） |
Encrypt | 是否加密連接（推薦 true，配合 TrustServerCertificate 使用） |
最佳實踐
-
始終使用
using語句：確保連接及時關閉。 -
敏感信息保護：連接字符串避免硬編碼，使用配置文件或密鑰管理服務。
-
異步操作：高并發場景使用
OpenAsync()和ExecuteReaderAsync()。 -
錯誤處理：用
try-catch捕獲SqlException。 -
連接池監控：通過性能計數器（如
NumberOfActiveConnectionPools）優化連接池。| 連接超時時間（秒，默認 15） |
|Encrypt| 是否加密連接（推薦true，配合TrustServerCertificate使用） |
)
![[C#] WPF - 資源URI](http://pic.xiahunao.cn/[C#] WPF - 資源URI)
