C#和SQL Server连接常用通讯方式

C#和SQL Server连接通讯

在 C# 中与 SQL Server 建立数据库连接，主要通过 ADO.NET 技术实现。以下是几种常见的连接方式及相关实践：

ADO.NET 全面指南：C# 数据库访问核心技术

ADO.NET 是 .NET Framework 中用于数据访问的核心组件，提供了一套强大的类库，使应用程序能够连接各种数据源（如 SQL Server、Oracle、MySQL 等）并与之交互。
图片展示

脚本代码

graph TD
    A[应用程序] --> 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]
    end
    
    A --> 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();
    
    // 填充 DataSet
    adapter.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;
}

最佳实践

1. 资源管理：始终使用 using 语句确保对象正确释放
2. 连接管理：保持连接打开时间最短
3. 参数化查询：防止 SQL 注入攻击
4. 错误处理：使用 try-catch 块处理数据库异常
5. 连接池：利用 ADO.NET 内置的连接池机制
6. 异步操作：在 I/O 密集型操作中使用异步方法
7. 安全存储：将连接字符串存储在配置文件中
8. 类型安全：使用 GetInt32(), GetString() 等方法而非索引器

ADO.NET vs Entity Framework

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(); // 自动管理连接
}

连接字符串关键参数说明

最佳实践

1. 始终使用 using 语句：确保连接及时关闭。

2. 敏感信息保护：连接字符串避免硬编码，使用配置文件或密钥管理服务。

3. 异步操作：高并发场景使用 OpenAsync() 和 ExecuteReaderAsync()。

4. 错误处理：用 try-catch 捕获 SqlException。

5. 连接池监控：通过性能计数器（如 NumberOfActiveConnectionPools）优化连接池。

   | 连接超时时间（秒，默认 15） |
   | Encrypt | 是否加密连接（推荐 true，配合 TrustServerCertificate 使用） |