C# 与 SQL SERVER 连接及操作示例

本文还有配套的精品资源，点击获取

简介：C#是IT行业广泛应用的编程语言，特别是在Windows桌面和.NET框架应用中。SQL Server是微软的强大关系型数据库管理系统。本文通过实例演示了如何使用C#中的ADO.NET库连接和操作SQL Server数据库。包括创建连接、打开和关闭数据库、执行SQL命令、使用参数化查询、事务处理和异常处理等方面，旨在帮助开发者掌握数据驱动应用开发的基础。

1. C#与SQL Server简介

C#（发音为 "See Sharp"）是一种由微软开发的现代、类型安全的面向对象编程语言，广泛用于开发各种应用程序。它是在.NET框架中运行的，这个框架是一个跨语言和平台的环境，允许开发者构建各种类型的应用程序。SQL Server是微软公司推出的关系型数据库管理系统（RDBMS），它与C#紧密集成，为开发者提供了一个强大的后端存储和数据管理解决方案。

C#语言具有简洁、安全、面向对象的特性，加之强大的.NET框架支持，使得C#在企业级应用、游戏开发、桌面应用、Web开发和移动应用开发等领域中都扮演着重要角色。SQL Server作为企业级数据库管理系统，能够支持大规模的数据存储、高并发事务处理和复杂的数据分析。与C#结合使用时，可以借助其提供的ADO.NET技术实现高效的数据访问和管理。

接下来的章节将详细探讨C#如何通过ADO.NET技术与SQL Server数据库进行交互，包括连接数据库、执行查询、事务处理以及异常管理等方面的内容。我们将从基础开始，逐步深入到高级特性的讲解，确保即使是经验丰富的IT从业者也能从中获得新的见解和技巧。

2. 使用ADO.NET连接与操作数据库

2.1 ADO.NET简介及架构理解

2.1.1 ADO.NET的组成与特点

ADO.NET（ActiveX Data Objects for .NET）是.NET框架中的一个数据访问技术，它提供了一种机制，允许.NET应用程序访问和操作数据。与传统ADO相比，ADO.NET不仅支持传统的数据访问，而且支持新的数据访问范例，如基于XML的数据处理，以及数据集和数据视图的使用。

ADO.NET的主要特点包括： - 连接和断开连接的数据访问模型 ：允许应用程序在访问数据时连接，完成后断开连接，从而优化了性能，尤其是在分布式或Web应用程序中。 - 数据集（DataSet） ：这是一个内存中的数据存储区，可以表示整个数据表以及表之间的关系。DataSet提供了对数据的快速访问、处理和转换能力。 - 强类型化数据访问 ：通过使用数据集和数据表，可以创建类型安全的查询和处理逻辑。 - 可扩展性 ：通过使用.NET框架支持的语言，开发者可以创建可重用的组件和扩展对象模型。

2.1.2 ADO.NET在.NET框架中的作用

在.NET框架中，ADO.NET扮演了一个连接数据世界和应用程序的桥梁角色。它不仅提供了一个核心类库，让开发者可以编写独立于数据源的代码，还提供了一组专门为连接到数据库、执行命令、处理结果集设计的组件。

数据提供者（Data Providers）是ADO.NET中的一大特色。它们是一组专门为特定数据源设计的组件。对于SQL Server而言， System.Data.SqlClient 命名空间下的类库提供了一系列组件来实现与数据库的高效交互。

2.2 ADO.NET连接数据库的基本步骤

2.2.1 添加引用与命名空间

要在项目中使用ADO.NET连接SQL Server，首先需要在项目中添加相应的引用。这通常意味着需要添加对 System.Data 和 System.Data.SqlClient 的引用。这可以通过Visual Studio中的“添加引用”对话框完成，或者在项目文件（.csproj）中直接添加对应的NuGet包。

<ItemGroup>
  <PackageReference Include="System.Data.SqlClient" Version="4.8.3" />
</ItemGroup>

之后，在代码文件的顶部添加必要的命名空间引用：

using System.Data;
using System.Data.SqlClient;

2.2.2 使用SqlConnection类建立连接

在成功添加引用和命名空间之后，接下来需要创建 SqlConnection 对象，并通过构造函数传递一个有效的连接字符串。连接字符串包含了用于建立连接到SQL Server数据库所需的所有参数，例如服务器地址、数据库名称、身份验证方式等。

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    try
    {
        connection.Open();
        Console.WriteLine("Connected to the database.");
    }
    catch (Exception e)
    {
        Console.WriteLine("Unable to connect to the database.");
        Console.WriteLine(e.Message);
    }
}

在上述代码中， SqlConnection 类的对象 connection 被创建并打开。注意使用了 using 语句，这确保了连接在使用后会被正确地关闭和释放，即使在出现异常的情况下也是如此。

上述代码演示了连接到数据库的最小代码片段。在真实世界的应用程序中，连接字符串通常被存储在配置文件（如 app.config ）中，以提高代码的可维护性。连接字符串中的参数可以根据不同的需求进行调整，例如使用不同的身份验证方式或连接到不同的服务器。

3. 创建和管理数据库连接

在C#中与SQL Server数据库进行交互时，创建和管理数据库连接是基础且关键的一步。本章节将详细介绍SqlConnection对象的创建方法，以及如何有效管理这些连接，保证应用程序性能的稳定性和高效性。

3.1 创建SqlConnection对象

3.1.1 构造函数和连接字符串

在.NET中，SqlConnection对象是由System.Data.SqlClient命名空间下的SqlConnection类提供的。通过构造函数，我们能够初始化一个新的数据库连接实例。

using System.Data.SqlClient;

// 创建SqlConnection对象
SqlConnection connection = new SqlConnection("Data Source=服务器地址;Initial Catalog=数据库名;User ID=用户名;Password=密码");

连接字符串是创建SqlConnection时必不可少的参数。它包含了一系列的键值对，用于定义连接数据库时需要的各种参数。常见的参数包括：

• Data Source : 数据库服务器的位置。
• Initial Catalog : 需要连接的数据库名称。
• User ID : 用于连接数据库的用户名。
• Password : 用户密码。

3.1.2 连接池的工作机制

在.NET框架中，SqlConnection被设计为可重用对象，而连接池正是为了实现这一目的。连接池会预先分配一定数量的连接，并将它们保存在内存中。当需要新的数据库连接时，可以从连接池中获取一个已经存在的连接，而不是每次都创建一个新的连接。

使用连接池能够显著提高应用程序的性能，因为它减少了建立连接所需的时间和资源消耗。当一个连接关闭时，并不是真的销毁这个连接，而是将其返回到连接池中，等待下次使用。

// 从连接池获取连接
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // 执行数据库操作
    connection.Close();
}

在上面的代码示例中，我们使用 using 语句确保连接在使用完毕后能够正确关闭并返回到连接池。

3.2 管理数据库连接

3.2.1 打开数据库连接

创建完SqlConnection对象之后，通常需要调用Open()方法来打开一个数据库连接。在执行数据库操作之前，确保数据库连接是打开的是一种好习惯。

// 打开数据库连接
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // 执行数据库操作
    // ...
    // 关闭连接
    connection.Close();
}

3.2.2 关闭数据库连接

虽然SqlConnection对象在使用完毕后会自动关闭和释放资源，但是最佳实践是在完成数据库操作后，立即显式调用Close()或Dispose()方法来关闭连接。这有助于回收连接池中的资源。

// 显式关闭数据库连接
connection.Close();
// 或者使用using语句，它会在离开作用域时自动调用Dispose()方法

在大型应用程序中，如果不及时关闭连接，可能会导致资源耗尽，影响应用程序的性能和稳定性。因此，合理管理数据库连接的生命周期是至关重要的。

// 使用using语句管理资源
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // 执行数据库操作
}
// using语句结束时，会自动调用Dispose()方法，从而关闭连接

总结

本章节深入探讨了创建和管理数据库连接的过程，包括了解SqlConnection对象的工作原理、构造函数和连接字符串的使用方法，以及连接池机制。此外，还介绍了如何打开和关闭数据库连接，强调了合理管理数据库连接对提高应用程序性能和稳定性的重要性。

4. 执行数据库操作与查询

执行数据库操作与查询是数据库交互中的核心部分。在ADO.NET中，这一过程主要涉及两个对象： SqlCommand 和 SqlDataReader 。本章将深入探讨如何使用这两个对象进行数据的增删改查操作，并讲解如何高效地读取查询结果集。

4.1 使用SqlCommand对象

SqlCommand 对象允许我们创建SQL命令，以在数据库上执行存储过程、SQL语句或获取数据库信息。它不仅能够执行命令，还能通过参数化查询提高安全性和性能。

4.1.1 创建SQL命令

创建SQL命令通常涉及指定要执行的SQL文本以及可选的参数。以下是创建一个简单的SQL命令对象的步骤：

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("SELECT * FROM Products WHERE ProductId = @ProductId", conn);
    cmd.Parameters.AddWithValue("@ProductId", 1);
    // ...
}

在上述代码中，我们首先创建了一个 SqlConnection 对象，然后使用该连接创建了一个 SqlCommand 对象。 SqlCommand 的构造函数接受一个SQL命令字符串和一个 SqlConnection 对象作为参数。此外，我们还添加了一个参数化查询，这是防止SQL注入攻击的重要实践。

4.1.2 执行SQL语句

执行SQL语句分为几种类型，包括执行返回单个值的命令（ ExecuteScalar ）、执行更新、插入或删除操作（ ExecuteNonQuery ）以及执行返回结果集的查询（ ExecuteReader ）。

执行返回单个值的命令

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Products", conn);
    conn.Open();
    object count = cmd.ExecuteScalar();
    Console.WriteLine($"Total products: {count}");
}

执行更新、插入或删除操作

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = @CategoryId", conn);
    cmd.Parameters.AddWithValue("@CategoryId", 2);
    conn.Open();
    int affectedRows = cmd.ExecuteNonQuery();
    Console.WriteLine($"{affectedRows} products updated.");
}

执行返回结果集的查询

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("SELECT * FROM Products WHERE CategoryId = @CategoryId", conn);
    cmd.Parameters.AddWithValue("@CategoryId", 3);
    conn.Open();
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"Product: {reader["ProductName"]}, Price: {reader["Price"]}");
        }
    }
}

在上述示例中，我们演示了如何使用 ExecuteReader 方法来执行一个查询，并通过 SqlDataReader 遍历结果集。

4.2 使用SqlDataReader读取数据

SqlDataReader 是一个强大的对象，用于从数据库中读取数据。它提供了一个快速、仅向前的只读访问数据流。使用 SqlDataReader 时，应当注意其与 SqlCommand 对象的关系，并且要确保在数据读取完成之后关闭 SqlDataReader 以及相应的 SqlConnection 。

4.2.1 遍历结果集的方法

通过 SqlDataReader 的 Read 方法可以逐行读取结果集中的数据。每次调用 Read 方法， SqlDataReader 都会移动到结果集的下一行。

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("SELECT * FROM Products", conn);
    conn.Open();
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"Id: {reader["ProductId"]}, Name: {reader["ProductName"]}");
        }
    }
}

4.2.2 避免常见的数据读取错误

在使用 SqlDataReader 时，开发者可能会犯一些常见的错误。以下是一些最佳实践，帮助开发者避免这些错误：

• 在尝试读取数据之前，请确认 SqlDataReader 有数据可读。可以使用 HasRows 属性来确认。
• 确保在退出 using 块之前调用 reader.Close() 和 conn.Close() 方法，以释放资源。
• 当处理大型数据集时，考虑使用批处理或分页数据以减少内存消耗。

using (SqlConnection conn = new SqlConnection(connectionString))
{
    // ...
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                // Read your data.
            }
        }
    }
    // The reader and connection are automatically closed when the using blocks are exited.
}

在本章节中，我们详细介绍了如何使用 SqlCommand 对象创建和执行SQL命令，并探讨了如何通过 SqlDataReader 对象高效地读取和遍历数据库查询结果集。通过遵循本章提供的实践，开发者可以更加安全和高效地在应用程序中实现数据库操作。接下来，我们将进一步深入高级数据库操作，包括参数化查询和事务处理等内容。

5. 高级数据库操作和异常处理

5.1 参数化查询与SQL注入防护

5.1.1 参数化查询的原理

参数化查询是一种安全的数据访问技术，它强制将SQL语句的输入参数与执行逻辑分开处理，极大地降低了SQL注入攻击的风险。在参数化查询中，SQL命令和输入参数通过预定义的参数对象来传递，而不是将输入直接嵌入SQL语句中。这样，即使输入数据中含有恶意SQL代码，也不会被数据库执行，因为数据库引擎会将这些输入作为普通字符串处理，从而有效避免了SQL注入。

5.1.2 实现参数化查询的方法

要实现参数化查询，最常见的方式是使用SqlCommand对象配合SqlParameters。以下是使用参数化查询的示例代码：

using System.Data.SqlClient;

// 创建数据库连接字符串
string connectionString = "Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True";

// 创建SqlConnection对象
using (SqlConnection connection = new SqlConnection(connectionString))
{
    // 创建SqlCommand对象并指定存储过程名称和连接
    SqlCommand command = new SqlCommand("usp_CheckUser", connection);
    command.CommandType = CommandType.StoredProcedure;

    // 添加参数
    command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = "admin";
    command.Parameters.Add("@Password", SqlDbType.VarChar).Value = "securePassword";

    // 打开连接并执行命令
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        // 处理结果集
    }
    reader.Close();
}

在这个例子中，我们首先创建了一个SqlConnection对象，并通过SqlCommand对象执行了名为"usp_CheckUser"的存储过程。通过 Parameters.Add 方法添加了两个参数，并将它们的值分别设置为用户名和密码。这种方法确保了即使用户输入的密码中包含了SQL代码，也不会被数据库执行。

5.2 事务处理与异常处理

5.2.1 SqlTransaction的应用场景

SqlTransaction对象用于在执行多个数据库操作时保持数据的一致性。它允许将多个命令组合为一个单元来执行。如果在事务中的任何命令执行失败，整个事务可以回滚到初始状态，保持数据的一致性。一个典型的事务处理场景包括银行转账操作，其中涉及两个账户的存款和取款操作，这两个操作必须同时成功或失败。

using System.Data.SqlClient;

// 创建数据库连接字符串
string connectionString = "Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True";

// 创建SqlConnection对象
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // 开始事务
    SqlTransaction transaction = connection.BeginTransaction();

    try
    {
        // 创建SqlCommand对象
        SqlCommand command1 = new SqlCommand("UPDATE Account SET Balance = Balance - 100 WHERE AccountId = 1", connection, transaction);
        SqlCommand command2 = new SqlCommand("UPDATE Account SET Balance = Balance + 100 WHERE AccountId = 2", connection, transaction);

        // 执行命令
        command1.ExecuteNonQuery();
        command2.ExecuteNonQuery();

        // 提交事务
        transaction.Commit();
    }
    catch (Exception ex)
    {
        // 如果出现异常，回滚事务
        transaction.Rollback();
        Console.WriteLine("Transaction rolled back.");
    }
}

5.2.2 异常处理机制与最佳实践

异常处理是程序健壮性的重要组成部分。在.NET中，异常通常通过try-catch块来处理。良好的异常处理能够保证程序在遇到错误时不会立即崩溃，而是可以提供错误信息、执行必要的清理工作或尝试其他操作。

以下是处理异常的最佳实践：

• 捕获特定异常 ：只捕获你预期可能出现的异常类型，而不是使用一个空的catch块捕获所有异常。
• 记录异常信息 ：当异常发生时，记录错误信息是很重要的，它可以帮助开发者在生产环境中快速定位和解决问题。
• 异常链 ：在捕获和重新抛出异常时，不要忘记保留原始异常信息。这在调试和日志记录中非常有用。
• 不处理异常 ：如果无法对异常进行任何有意义的操作，则不应该捕获它。让异常向上传播，可能由上层的异常处理器来处理。

try
{
    // 执行可能引发异常的操作
}
catch (SqlException sqlEx)
{
    // SQL特定异常处理
    LogError(sqlEx);
    throw new CustomDatabaseException("An error occurred with the database", sqlEx);
}
catch (Exception ex)
{
    // 其他异常处理
    LogError(ex);
    throw new CustomException("An unexpected error occurred", ex);
}

在上面的代码中，我们首先捕获了SqlException，这是一个针对SQL Server特定的异常类型。如果捕获到此类异常，我们会记录异常信息，并抛出一个自定义的异常，同时保留原始异常的信息。这样做可以在不隐藏错误信息的前提下，为用户提供更清晰的错误描述。如果遇到非SqlException的其他异常，则用一个通用的异常处理来捕获。通过这种方式，我们确保了异常处理既细致又全面。

本文还有配套的精品资源，点击获取

简介：C#是IT行业广泛应用的编程语言，特别是在Windows桌面和.NET框架应用中。SQL Server是微软的强大关系型数据库管理系统。本文通过实例演示了如何使用C#中的ADO.NET库连接和操作SQL Server数据库。包括创建连接、打开和关闭数据库、执行SQL命令、使用参数化查询、事务处理和异常处理等方面，旨在帮助开发者掌握数据驱动应用开发的基础。

本文还有配套的精品资源，点击获取