C#连接SQL Server数据库完整指南

目录

一、环境准备

1. 安装必要软件

2. 创建测试数据库

二、方式1：ADO.NET原生连接封装类

步骤1：添加引用

步骤2：编写连接字符串

1. 封装类

2. 使用示例

三、方式2：Entity Framework Core封装类

步骤1：安装NuGet包

步骤2：创建实体类和DbContext

步骤3：执行CRUD操作

1. 封装类

2. 使用示例

四、最佳实践与扩展功能说明

1. 连接字符串管理

2. 异常处理

3. 日志记录

4. 性能优化

5. 扩展功能

五、总结

---

C#连接SQL Server数据库完整指南，整合了ADO.NET原生连接与Entity Framework Core两种实现方式，包含详细的代码注释和实现过程说明。

一、环境准备

1. 安装必要软件

• Visual Studio（社区版免费）：下载地址
• SQL Server（Developer Edition免费）：下载地址
• SQL Server Management Studio (SSMS)：下载地址

2. 创建测试数据库

-- 创建数据库,数据库名TestDB
CREATE DATABASE TestDB;
GO


USE TestDB;
GO
 
-- 创建用户表
CREATE TABLE Users (
    UserID INT PRIMARY KEY IDENTITY(1,1),
    Username NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) UNIQUE
);
GO
 
-- 插入测试数据
INSERT INTO Users (Username, Email) VALUES 
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com');
GO

二、方式1：ADO.NET原生连接封装类

步骤1：添加引用

• 在Visual Studio中右键项目 → 管理NuGet程序包 → 搜索并安装：

Install-Package System.Data.SqlClient

步骤2：编写连接字符串

• 在app.config或web.config中添加：

<connectionStrings>
  <add name="TestDBConnection" 
       connectionString="Server=.;Database=TestDB;Integrated Security=True;" 
       providerName="System.Data.SqlClient"/>
</connectionStrings>

• 连接字符串参数说明：
  • Server=.：本地默认实例（.\SQLEXPRESS表示命名实例）。
  • Database=TestDB：目标数据库名。
  • Integrated Security=True：使用Windows身份验证（若用SQL账号，改为User ID=sa;Password=你的密码）。

1. 封装类

csharp

using System.Data;
using System.Data.SqlClient;
using Microsoft.Extensions.Configuration;
 
// 插入数据（参数化查询防SQL注入）
   /*    using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            string insertQuery = "INSERT INTO Users (Username, Email) VALUES (@name, @email)";
            using (SqlCommand cmd = new SqlCommand(insertQuery, conn))
            {
                cmd.Parameters.AddWithValue("@name", "Charlie");
                cmd.Parameters.AddWithValue("@email", "charlie@example.com");
                cmd.ExecuteNonQuery();
            }
        }*/




/// <summary>
/// ADO.NET数据库操作封装类
/// </summary>
public class AdoNetDbHelper
{
    private readonly string _connectionString;
 
    /// <summary>
    /// 构造函数：从配置文件读取连接字符串
    /// </summary>
    /// <param name="config">配置对象</param>
    /// <param name="connectionStringName">连接字符串名称（默认TestDB）</param>
    public AdoNetDbHelper(IConfiguration config, string connectionStringName = "TestDB")
    {
        _connectionString = config.GetConnectionString(connectionStringName) 
                            ?? throw new InvalidOperationException("未找到指定连接字符串");
    }
 
    /// <summary>
    /// 执行查询操作，返回DataTable
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="commandType">命令类型（默认Text）</param>
    /// <param name="parameters">SQL参数</param>
    /// <returns>查询结果DataTable</returns>
    public DataTable ExecuteQuery(string sql, CommandType commandType = CommandType.Text, SqlParameter[] parameters = null)
    {
        // 使用using语句确保资源释放
        using (SqlConnection conn = new SqlConnection(_connectionString))
        {
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                cmd.CommandType = commandType;
                
                // 添加参数（防SQL注入）
                if (parameters != null) 
                {
                    cmd.Parameters.AddRange(parameters);
                }
 
                // 填充数据到DataTable
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
        }
    }

 
    /// <summary>
    /// 执行非查询操作（INSERT/UPDATE/DELETE），返回受影响行数
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="commandType">命令类型（默认Text）</param>
    /// <param name="parameters">SQL参数</param>
    /// <returns>受影响行数</returns>
    public int ExecuteNonQuery(string sql, CommandType commandType = CommandType.Text, SqlParameter[] parameters = null)
    {
        using (SqlConnection conn = new SqlConnection(_connectionString))
        {
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                cmd.CommandType = commandType;
                
                if (parameters != null) 
                {
                    cmd.Parameters.AddRange(parameters);
                }
 
                conn.Open(); // 显式打开连接
                return cmd.ExecuteNonQuery();
            }
        }
    }
 

    /// <summary>
    /// 执行事务处理（含重试逻辑）
    /// </summary>
    /// <param name="action">事务操作委托</param>
    /// <param name="maxRetries">最大重试次数（默认3次）</param>
    /// <param name="retryDelayMs">重试间隔（毫秒，默认1000）</param>
    /// <returns>成功返回1，失败抛出异常</returns>
    public int ExecuteTransaction(Action<SqlConnection, SqlTransaction> action, int maxRetries = 3, int retryDelayMs = 1000)
    {
        int retryCount = 0;
        while (retryCount <= maxRetries)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(_connectionString))
                {
                    conn.Open();
                    using (SqlTransaction transaction = conn.BeginTransaction())
                    {
                        try
                        {
                            // 执行事务操作
                            action(conn, transaction);
                            
                            // 提交事务
                            transaction.Commit();
                            return 1; // 返回成功标识
                        }
                        catch
                        {
                            // 回滚事务
                            transaction.Rollback();
                            throw; // 重新抛出异常
                        }
                    }
                }
            }
            catch (SqlException ex) when (retryCount < maxRetries)
            {
                // 记录重试日志（实际项目需集成日志框架）
                Console.WriteLine($"事务执行失败，重试次数: {retryCount + 1}/{maxRetries}");
                retryCount++;
                Thread.Sleep(retryDelayMs);
            }
        }
 
        throw new DbOperationException("达到最大重试次数，事务执行失败");
    }
}
 
/// <summary>
/// 自定义数据库操作异常
/// </summary>
public class DbOperationException : Exception
{
    public DbOperationException(string message, Exception innerException) 
        : base(message, innerException) { }
}

2. 使用示例

csharp

// 配置读取（appsettings.json）
{
  "ConnectionStrings": {
    "TestDB": "Server=.;Database=TestDB;Integrated Security=True;"
  }
}
 
// 程序入口（Program.cs）
var builder = new ConfigurationBuilder().AddJsonFile("appsettings.json");
IConfiguration config = builder.Build();
 
// 创建数据库操作助手实例
var dbHelper = new AdoNetDbHelper(config);
 
// 示例1：查询所有用户
DataTable usersTable = dbHelper.ExecuteQuery("SELECT * FROM Users");
foreach (DataRow row in usersTable.Rows)
{
    Console.WriteLine($"ID: {row["UserID"]}, 用户名: {row["Username"]}, 邮箱: {row["Email"]}");
}
 
// 示例2：插入新用户（参数化查询防注入）
string insertSql = "INSERT INTO Users (Username, Email) VALUES (@name, @email)";
SqlParameter[] insertParams = {
    new SqlParameter("@name", "Eve"),
    new SqlParameter("@email", "eve@example.com")
};
dbHelper.ExecuteNonQuery(insertSql, parameters: insertParams);
 
// 示例3：事务处理（转账场景）
string updateSql1 = "UPDATE Accounts SET Balance = Balance - 100 WHERE UserID = 1";
string updateSql2 = "UPDATE Accounts SET Balance = Balance + 100 WHERE UserID = 2";
 
try
{
    dbHelper.ExecuteTransaction((conn, trans) =>
    {
        // 在事务中执行多个操作
        using (SqlCommand cmd1 = new SqlCommand(updateSql1, conn, trans))
        {
            cmd1.ExecuteNonQuery();
        }
 
        using (SqlCommand cmd2 = new SqlCommand(updateSql2, conn, trans))
        {
            cmd2.ExecuteNonQuery();
        }
 
        // 模拟业务逻辑异常（用于测试事务回滚）
        // throw new InvalidOperationException("模拟业务异常");
    });
 
    Console.WriteLine("事务执行成功！");
}
catch (DbOperationException ex)
{
    Console.WriteLine($"事务执行失败: {ex.Message}");
}

三、方式2：Entity Framework Core封装类

步骤1：安装NuGet包

• 在项目中安装以下包：

bash

Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools

步骤2：创建实体类和DbContext

csharp

using Microsoft.EntityFrameworkCore;
 
// 实体类
public class User
{
    public int UserID { get; set; }
    public string Username { get; set; }
    public string Email { get; set; }
}
 
// DbContext
public class AppDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
 
    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlServer("Server=.;Database=TestDB;Integrated Security=True;");
}

步骤3：执行CRUD操作

csharp

class Program
{
    static void Main()
    {
        // 查询数据
        using (var db = new AppDbContext())
        {
            var users = db.Users.ToList();
            foreach (var user in users)
            {
                Console.WriteLine($"ID: {user.UserID}, Name: {user.Username}");
            }
        }
 
        // 插入数据
        using (var db = new AppDbContext())
        {
            var newUser = new User { Username = "David", Email = "david@example.com" };
            db.Users.Add(newUser);
            db.SaveChanges();
        }
 
        // 更新数据
        using (var db = new AppDbContext())
        {
            var user = db.Users.FirstOrDefault(u => u.Username == "Alice");
            if (user != null)
            {
                user.Email = "alice_new@example.com";
                db.SaveChanges();
            }
        }
 
        // 删除数据
        using (var db = new AppDbContext())
        {
            var user = db.Users.FirstOrDefault(u => u.Username == "Bob");
            if (user != null)
            {
                db.Users.Remove(user);
                db.SaveChanges();
            }
        }
    }
}

注意：

1. 连接字符串安全：

   • 避免在代码中硬编码密码，使用app.config或环境变量存储敏感信息。

   • 生产环境建议使用加密连接字符串（如aspnet_regiis工具）。

2. 资源释放：

   • 始终使用using语句确保SqlConnection、SqlCommand等对象被正确释放。

3. 异常处理：

csharp

try
{
    // 数据库操作代码
}
catch (SqlException ex)
{
    Console.WriteLine($"SQL错误: {ex.Message}");
}
catch (Exception ex)
{
    Console.WriteLine($"通用错误: {ex.Message}");
}

1. 封装类

csharp

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.Extensions.Logging;
 
/// <summary>
/// Entity Framework Core数据库操作封装类
/// </summary>
/// <typeparam name="TContext">DbContext类型</typeparam>
public class EfCoreDbHelper<TContext> where TContext : DbContext
{
    private readonly TContext _context;
    private readonly ILogger<EfCoreDbHelper<TContext>> _logger;
 
    /// <summary>
    /// 构造函数
    /// </summary>
    /// <param name="context">DbContext实例</param>
    /// <param name="logger">日志记录器</param>
    public EfCoreDbHelper(TContext context, ILogger<EfCoreDbHelper<TContext>> logger)
    {
        _context = context ?? throw new ArgumentNullException(nameof(context));
        _logger = logger;
    }
 
    /// <summary>
    /// 获取所有实体数据
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <returns>实体集合</returns>
    public List<T> GetAll<T>() where T : class
    {
        _logger.LogInformation("正在执行GetAll<{0}>操作", typeof(T).Name);
        return _context.Set<T>().ToList();
    }
 
    /// <summary>
    /// 根据条件查找单个实体
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <param name="predicate">查询条件</param>
    /// <returns>匹配的实体或null</returns>
    public T Find<T>(Func<T, bool> predicate) where T : class
    {
        _logger.LogInformation("正在执行Find<{0}>操作", typeof(T).Name);
        return _context.Set<T>().FirstOrDefault(predicate);
    }
 
    /// <summary>
    /// 添加新实体
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <param name="entity">要添加的实体</param>
    public void Add<T>(T entity) where T : class
    {
        _logger.LogInformation("正在执行Add<{0}>操作", typeof(T).Name);
        _context.Set<T>().Add(entity);
        _context.SaveChanges();
    }
 
    /// <summary>
    /// 更新实体
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <param name="entity">要更新的实体（需处于已跟踪状态）</param>
    public void Update<T>(T entity) where T : class
    {
        _logger.LogInformation("正在执行Update<{0}>操作", typeof(T).Name);
        _context.Entry(entity).State = EntityState.Modified;
        _context.SaveChanges();
    }
 
    /// <summary>
    /// 删除实体
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <param name="entity">要删除的实体</param>
    public void Delete<T>(T entity) where T : class
    {
        _logger.LogInformation("正在执行Delete<{0}>操作", typeof(T).Name);
        _context.Set<T>().Remove(entity);
        _context.SaveChanges();
    }
 
    /// <summary>
    /// 执行原始SQL查询
    /// </summary>
    /// <typeparam name="T">映射实体类型</typeparam>
    /// <param name="sql">SQL语句</param>
    /// <param name="parameters">SQL参数</param>
    /// <returns>查询结果集合</returns>
    public List<T> FromSqlRaw<T>(string sql, params object[] parameters) where T : class
    {
        _logger.LogInformation("执行原始SQL: {Sql}，参数: {Params}", sql, parameters);
        return _context.Set<T>().FromSqlRaw(sql, parameters).ToList();
    }
 
    /// <summary>
    /// 异步获取所有实体数据
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <returns>Task<List<T>></returns>
    public async Task<List<T>> GetAllAsync<T>() where T : class
    {
        _logger.LogInformation("正在执行异步GetAll<{0}>操作", typeof(T).Name);
        return await _context.Set<T>().ToListAsync();
    }
}
 
/// <summary>
/// 用户实体类（对应数据库表）
/// </summary>
public class User
{
    public int UserID { get; set; }
    public string Username { get; set; }
    public string Email { get; set; }
}
 
/// <summary>
/// 应用程序DbContext（数据库上下文）
/// </summary>
public class AppDbContext : DbContext
{
    /// <summary>
    /// 用户表数据集
    /// </summary>
    public DbSet<User> Users { get; set; }
 
    /// <summary>
    /// 构造函数
    /// </summary>
    /// <param name="options">DbContext配置选项</param>
    public AppDbContext(DbContextOptions<AppDbContext> options) 
        : base(options) 
    {
    }
 
    /// <summary>
    /// 配置模型构建（可选，用于高级配置）
    /// </summary>
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // 配置表映射（示例）
        modelBuilder.Entity<User>()
            .ToTable("Users")
            .HasKey(u => u.UserID);
    }
}

2. 使用示例

csharp

// 程序入口（Program.cs）
var builder = WebApplication.CreateBuilder(args);
 
// 配置数据库连接（从appsettings.json）
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("TestDB")));
 
// 注册EF Core助手类（依赖注入）
builder.Services.AddScoped<EfCoreDbHelper<AppDbContext>>();
 
// 配置日志记录（控制台输出）
builder.Services.AddLogging(configure => 
    configure.AddConsole().SetMinimumLevel(LogLevel.Information));
 
var app = builder.Build();
 
// 获取数据库操作助手实例
var efHelper = app.Services.GetRequiredService<EfCoreDbHelper<AppDbContext>>();
 
// 示例1：查询所有用户
var users = efHelper.GetAll<User>();
foreach (var user in users)
{
    Console.WriteLine($"ID: {user.UserID}, 用户名: {user.Username}, 邮箱: {user.Email}");
}
 
// 示例2：添加新用户
var newUser = new User { Username = "Frank", Email = "frank@example.com" };
efHelper.Add(newUser);
 
// 示例3：更新用户邮箱
var userToUpdate = efHelper.Find<User>(u => u.Username == "Eve");
if (userToUpdate != null)
{
    userToUpdate.Email = "eve_new@example.com";
    efHelper.Update(userToUpdate);
}
 
// 示例4：执行原始SQL查询
var rawQueryUsers = efHelper.FromSqlRaw<User>("SELECT * FROM Users WHERE UserID > @id", 2);
foreach (var user in rawQueryUsers)
{
    Console.WriteLine($"ID: {user.UserID}, 用户名: {user.Username}");
}
 
// 示例5：异步查询（需async/await）
app.MapGet("/users", async () =>
{
    var asyncUsers = await efHelper.GetAllAsync<User>();
    return Results.Ok(asyncUsers);
});
 
app.Run();

四、最佳实践与扩展功能说明

1. 连接字符串管理

• 配置文件：
  • 使用appsettings.json统一管理连接字符串，支持多环境配置（开发/测试/生产）。

• 参数化查询：

  • 始终使用cmd.Parameters.AddWithValue()防止SQL注入攻击。
• 加密敏感信息：
  • 生产环境建议使用Azure Key Vault或类似服务加密存储密码等敏感信息。

2. 异常处理

• 自定义异常：
  • 通过DbOperationException封装数据库操作异常，包含更详细的错误上下文。
• 全局异常处理：
  • 在ASP.NET Core中配置全局异常处理中间件，统一记录和处理数据库异常。

3. 日志记录

• 集成日志框架：使用Serilog、NLog等日志框架记录SQL语句、执行时间、错误详情。
• 日志级别控制：开发环境记录详细日志，生产环境仅记录关键错误。

4. 性能优化

• 连接池：ADO.NET默认启用连接池，无需额外配置即可提升性能。
• 查询优化：使用EF Core的ToQueryString()方法查看生成的SQL，分析执行计划。
• 异步操作：优先使用异步方法（如GetAllAsync）避免线程阻塞。

5. 扩展功能

• 缓存支持：集成MemoryCache或Redis减少数据库压力，对频繁访问且不常变化的数据进行缓存。
• 审计日志：通过数据库触发器或EF Core的SaveChanges拦截器记录数据变更历史。
• 多数据库支持：定义IDbHelper接口，通过依赖注入实现不同数据库（MySQL、PostgreSQL）的切换。

五、总结

特性	ADO.NET原生连接	Entity Framework Core
学习曲线	较低，直接操作SQL	较高，需理解LINQ和ORM概念
性能	更高（直接SQL）	略低（ORM开销）
开发效率	较低（手动编写SQL）	更高（LINQ自动生成SQL）
复杂查询	完全控制SQL	受限于LINQ表达能力
适用场景	高性能要求、复杂存储过程	快速开发、对象映射优先

根据项目需求选择合适的技术栈：

• ADO.NET：适合需要精细控制SQL、追求极致性能的场景。
• EF Core：适合快速开发、需要对象关系映射的场景。

通过封装类统一管理数据库操作，结合依赖注入和日志记录等最佳实践，可显著提升代码的可维护性和健壮性。