C# 连接 SQL Server 数据库的基本步骤

1. 基本连接流程

1.1 引用必要的命名空间

using System.Data.SqlClient;  // .NET Framework
// 或
using Microsoft.Data.SqlClient;  // .NET Core/.NET 5+

1.2 创建连接字符串

// 标准连接字符串
string connectionString = "Server=服务器名称或IP;Database=数据库名;User Id=用户名;Password=密码;";

// 使用Windows身份验证
string connectionString = "Server=服务器名称或IP;Database=数据库名;Integrated Security=True;";

// 其他常见参数
string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;TrustServerCertificate=True;";

1.3 基本连接代码结构

using (SqlConnection connection = new SqlConnection(connectionString))
{
    try
    {
        connection.Open();
        Console.WriteLine("连接成功！");

        // 执行数据库操作...
    }
    catch (SqlException ex)
    {
        Console.WriteLine($"数据库错误: {ex.Message}");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"错误: {ex.Message}");
    }
}

2. 执行SQL命令的三种方式

2.1 执行非查询命令（INSERT/UPDATE/DELETE）

string sql = "INSERT INTO Users (Name, Age) VALUES (@Name, @Age)";

using (SqlCommand command = new SqlCommand(sql, connection))
{
    command.Parameters.AddWithValue("@Name", "张三");
    command.Parameters.AddWithValue("@Age", 30);

    int rowsAffected = command.ExecuteNonQuery();
    Console.WriteLine($"影响了 {rowsAffected} 行");
}

2.2 执行查询返回单个值

string sql = "SELECT COUNT(*) FROM Users";

using (SqlCommand command = new SqlCommand(sql, connection))
{
    int userCount = (int)command.ExecuteScalar();
    Console.WriteLine($"共有 {userCount} 个用户");
}

2.3 执行查询返回结果集

string sql = "SELECT Id, Name, Age FROM Users WHERE Age > @MinAge";

using (SqlCommand command = new SqlCommand(sql, connection))
{
    command.Parameters.AddWithValue("@MinAge", 18);

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"ID: {reader["Id"]}, 姓名: {reader["Name"]}, 年龄: {reader["Age"]}");
            // 或使用强类型访问
            // Console.WriteLine($"ID: {reader.GetInt32(0)}, 姓名: {reader.GetString(1)}, 年龄: {reader.GetInt32(2)}");
        }
    }
}

3. 参数化查询最佳实践

3.1 防止SQL注入的正确方式

// 错误做法（容易导致SQL注入）
string unsafeSql = $"SELECT * FROM Users WHERE Name = '{userInput}'";

// 正确做法
string safeSql = "SELECT * FROM Users WHERE Name = @UserName";
command.Parameters.AddWithValue("@UserName", userInput);

3.2 参数类型指定

// 明确指定参数类型
SqlParameter param = new SqlParameter("@BirthDate", SqlDbType.DateTime);
param.Value = DateTime.Now;
command.Parameters.Add(param);

4. 事务处理

4.1 基本事务示例

using (SqlTransaction transaction = connection.BeginTransaction())
{
    try
    {
        string sql1 = "UPDATE Account SET Balance = Balance - 100 WHERE Id = 1";
        string sql2 = "UPDATE Account SET Balance = Balance + 100 WHERE Id = 2";

        using (SqlCommand command = new SqlCommand(sql1, connection, transaction))
        {
            command.ExecuteNonQuery();
        }

        using (SqlCommand command = new SqlCommand(sql2, connection, transaction))
        {
            command.ExecuteNonQuery();
        }

        transaction.Commit();
        Console.WriteLine("转账成功");
    }
    catch
    {
        transaction.Rollback();
        Console.WriteLine("转账失败，已回滚");
    }
}

5. 连接池优化

5.1 连接字符串中的池配置

string connectionString = "Server=myServer;Database=myDB;Integrated Security=True;" +
                          "Pooling=true;Min Pool Size=5;Max Pool Size=100;Connection Lifetime=300;";

5.2 连接池管理建议

• 默认情况下连接池是启用的
• 不要频繁创建和销毁连接对象
• 使用using语句确保连接正确释放
• 合理设置最小和最大连接数

6. 异步操作

6.1 异步连接和查询

public async Task<List<User>> GetUsersAsync()
{
    List<User> users = new List<User>();

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();

        string sql = "SELECT Id, Name, Email FROM Users";
        using (SqlCommand command = new SqlCommand(sql, connection))
        {
            using (SqlDataReader reader = await command.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    users.Add(new User
                    {
                        Id = reader.GetInt32(0),
                        Name = reader.GetString(1),
                        Email = reader.GetString(2)
                    });
                }
            }
        }
    }

    return users;
}

7. 实用扩展方法

7.1 获取DataTable

public static DataTable ExecuteDataTable(this SqlCommand command)
{
    DataTable dt = new DataTable();
    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
    {
        adapter.Fill(dt);
    }
    return dt;
}

// 使用示例
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Products", connection))
{
    DataTable products = cmd.ExecuteDataTable();
}

7.2 安全获取值

public static T GetSafeValue<T>(this SqlDataReader reader, string columnName)
{
    int ordinal = reader.GetOrdinal(columnName);
    return reader.IsDBNull(ordinal) ? default(T) : (T)reader.GetValue(ordinal);
}

// 使用示例
int age = reader.GetSafeValue<int>("Age");
string name = reader.GetSafeValue<string>("Name");

8. 错误处理进阶

8.1 详细错误日志

try
{
    // 数据库操作
}
catch (SqlException ex)
{
    foreach (SqlError error in ex.Errors)
    {
        Console.WriteLine($"错误 #{error.Number}: {error.Message}");
        Console.WriteLine($"来自: {error.Server}");
        Console.WriteLine($"过程: {error.Procedure}, 行号: {error.LineNumber}");
    }
}
catch (Exception ex)
{
    Console.WriteLine($"非SQL错误: {ex.ToString()}");
}

9. 连接字符串配置

9.1 配置文件存储

<!-- App.config或Web.config -->
<connectionStrings>
    <add name="MyDB" 
         connectionString="Server=.;Database=MyAppDB;Integrated Security=True;"
         providerName="System.Data.SqlClient" />
</connectionStrings>

9.2 从配置读取

string connectionString = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;

10. 现代替代方案

10.1 使用Dapper微ORM

using Dapper;

public class UserRepository
{
    private readonly string _connectionString;

    public UserRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public IEnumerable<User> GetAllUsers()
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            return connection.Query<User>("SELECT * FROM Users");
        }
    }

    public int AddUser(User user)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            string sql = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email); SELECT CAST(SCOPE_IDENTITY() as int)";
            return connection.QuerySingle<int>(sql, user);
        }
    }
}

10.2 使用Entity Framework Core

public class ApplicationDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=.;Database=MyAppDB;Integrated Security=True;");
    }
}

// 使用示例
using (var context = new ApplicationDbContext())
{
    var users = context.Users.Where(u => u.Age > 18).ToList();
    context.Users.Add(new User { Name = "李四", Age = 25 });
    context.SaveChanges();
}