C#与MSSQL存储过程/参数化查询

系统管理员 2023-10-12 16:26 8阅读 0赞

存储过程和参数化查询是在数据库中执行操作时非常有用的概念和技术。它们可以提高数据库操作的性能、安全性和可维护性。下面我将详细介绍存储过程和参数化查询的概念以及在C\#中如何使用它们。(ChatGPT编写)

# **<一>存储过程：** #

*  **存储过程是在数据库中预先定义的一组SQL语句，它们可以接受输入参数、执行特定的操作，并返回结果集或输出参数。**
 *  **存储过程可以封装复杂的业务逻辑，并在需要时通过简单的调用来执行这些逻辑。**
 *  **存储过程可以被多个应用程序共享和重用，提高了代码的复用性和可维护性。**

在C\#中使用存储过程的步骤如下：

1.  创建存储过程：在数据库中使用SQL语句创建存储过程。
2.  在C\#代码中调用存储过程：使用`SqlCommand`对象设置存储过程名称，指定命令类型为`StoredProcedure`，并将参数传递给存储过程。
3.  执行存储过程：使用`ExecuteNonQuery()`方法执行存储过程，或使用`ExecuteReader()`、`ExecuteScalar()`等方法执行存储过程并获取结果。

下面是一个示例，演示如何在C\#中使用存储过程：

using System;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace StoredProcExample
    {
        class Program
        {
            static void Main(string[] args)
            {
                string connectionString = "Data Source=YourServer;Initial Catalog=YourDatabase;User ID=YourUsername;Password=YourPassword";
    
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
    
                    // 创建存储过程
                    string createProcedureQuery = @"
                        CREATE PROCEDURE GetPersons
                        AS
                        BEGIN
                            SELECT * FROM Persons;
                        END";
                    using (SqlCommand createProcedureCommand = new SqlCommand(createProcedureQuery, connection))
                    {
                        createProcedureCommand.ExecuteNonQuery();
                        Console.WriteLine("Stored procedure created successfully.");
                    }
    
                    // 调用存储过程
                    using (SqlCommand callProcedureCommand = new SqlCommand("GetPersons", connection))
                    {
                        callProcedureCommand.CommandType = CommandType.StoredProcedure;
    
                        using (SqlDataReader reader = callProcedureCommand.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                string name = reader.GetString(1);
                                int age = reader.GetInt32(2);
                                Console.WriteLine("Id: {0}, Name: {1}, Age: {2}", id, name, age);
                            }
                        }
                    }
    
                    connection.Close();
                }
    
                Console.WriteLine("Press any key to exit...");
                Console.ReadKey();
            }
        }
    }

在这个示例中，首先使用SQL语句创建了一个名为`GetPersons`的存储过程。然后，在C\#代码中通过创建`SqlCommand`对象，并将`CommandType`设置为`StoredProcedure`，指定存储过程的名称为`GetPersons`。最后，通过`ExecuteReader()`方法执行存储过程并读取结果。

\--------------------

**当涉及到复杂的数据库操作和业务逻辑时，存储过程是一种非常有用的工具。它可以将一系列SQL语句和逻辑组织在一起，并存储在数据库中，供应用程序调用和执行。以下是一些关于存储过程的进一步说明：**

1.  优点：
    
     *  提高性能：存储过程可以在数据库服务器上预编译和缓存，从而提高查询的执行速度。
     *  降低网络流量：通过调用存储过程，只需要传递参数和返回结果，而不是每次发送完整的SQL查询语句，减少了网络传输的数据量。
     *  安全性：存储过程可以通过权限控制来限制对数据库的访问，提供更高的安全性。
     *  可维护性：存储过程将业务逻辑集中在数据库中，使得维护和修改变得更加容易。
2.  参数传递：
    
     *  存储过程可以接受输入参数和输出参数。
     *  输入参数用于向存储过程传递值，供存储过程内部使用。
     *  输出参数用于存储过程执行完毕后返回的值，供应用程序使用。
3.  返回结果：
    
     *  存储过程可以返回一个或多个结果集，也可以返回一个或多个输出参数。
     *  结果集可以是查询语句的结果，也可以是存储过程内部逻辑计算的结果。
4.  控制流程：
    
     *  存储过程支持条件判断和循环等流程控制结构，可以根据特定的条件执行不同的SQL语句或逻辑。
     *  存储过程还支持异常处理和错误处理，可以捕获和处理数据库操作中的异常情况。
5.  示例： 下面是一个简单的存储过程示例，用于根据年龄范围查询Persons表中的数据：

CREATE PROCEDURE GetPersonsByAgeRange
        @minAge INT,
        @maxAge INT
    AS
    BEGIN
        SELECT * FROM Persons WHERE Age >= @minAge AND Age <= @maxAge;
    END

在C\#中调用这个存储过程的代码示例：

using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
    
        using (SqlCommand command = new SqlCommand("GetPersonsByAgeRange", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@minAge", 20);
            command.Parameters.AddWithValue("@maxAge", 30);
    
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    int id = reader.GetInt32(0);
                    string name = reader.GetString(1);
                    int age = reader.GetInt32(2);
                    Console.WriteLine("Id: {0}, Name: {1}, Age: {2}", id, name, age);
                }
            }
        }
    
        connection.Close();
    }

在这个示例中，存储过程接受两个输入参数`@minAge`和`@maxAge`，然后根据这些参数执行查询并返回结果集。

\--------

**存储过程是一组预编译的数据库操作语句，可以在数据库中定义和存储，并通过名称进行调用和执行。存储过程具有以下特点和优势：**

1.  预编译和优化：
    
     *  存储过程在创建时进行预编译，并生成执行计划，以提高查询的执行效率。
     *  预编译过程包括语法验证、访问权限检查和查询计划生成等步骤。
     *  这样，在每次执行存储过程时，都可以重复使用已编译和优化的执行计划，从而减少执行时间和资源消耗。
2.  提高性能：
    
     *  存储过程的预编译和优化过程可以减少数据传输和网络延迟，提高查询的执行速度。
     *  存储过程还可以减少客户端与数据库服务器之间的通信次数，从而减轻网络负担。
3.  数据库逻辑封装：
    
     *  存储过程将一系列数据库操作封装为一个单元，并将其保存在数据库中，使得逻辑和业务层面的操作更加清晰和模块化。
     *  存储过程还可以实现复杂的业务逻辑，包括条件判断、循环和错误处理等。
4.  安全性和权限控制：
    
     *  存储过程可以使用数据库的权限系统来限制用户对数据库的访问和操作。
     *  存储过程允许数据库管理员授予或撤销用户对特定存储过程的执行权限。
5.  代码重用和维护性：
    
     *  存储过程可以在不同的应用程序中重用，避免了重复编写相同的数据库操作逻辑。
     *  如果需要修改数据库操作逻辑，只需修改存储过程的定义，而不需要修改应用程序代码。

以下是一个示例存储过程，用于**插入一条记录**到Persons表中：

CREATE PROCEDURE InsertPerson
        @name VARCHAR(50),
        @age INT
    AS
    BEGIN
        INSERT INTO Persons (Name, Age) VALUES (@name, @age)
    END

通过使用存储过程，可以在C\#代码中调用该存储过程来执行插入操作：

using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
    
        using (SqlCommand command = new SqlCommand("InsertPerson", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@name", "John");
            command.Parameters.AddWithValue("@age", 30);
    
            int rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine("Rows Affected: " + rowsAffected);
        }
    
        connection.Close();
    }

在这个示例中，首先通过`SqlCommand`对象创建对存储过程的调用，并指定存储过程的名称。然后，通过`AddWithValue`方法添加存储过程的参数。最后，通过`ExecuteNonQuery`方法执行存储过程，并获取受影响的行数。

通过学习和掌握存储过程的概念和用法，你可以在数据库层面实现更复杂的业务逻辑，并通过调用存储过程来执行这些逻辑。这将提高数据库操作的效率、可维护性和安全性。

\---------------------

# <二>参数化查询： #

*  **参数化查询是一种通过将参数值与查询语句分离来执行SQL查询的方法，以避免SQL注入攻击，并提高查询的性能和安全性。**
 *  **参数化查询使用特殊的占位符（如@parameterName）来代表参数，然后通过将参数值分配给占位符来执行查询。**

在C\#中使用参数化查询的步骤如下：

1.  创建带有参数的SQL查询：在查询语句中使用占位符来表示参数。
2.  在C\#代码中设置参数：使用`SqlParameter`对象设置参数的名称、数据类型和值，并将其添加到`SqlCommand`对象的`Parameters`集合中。
3.  执行查询：使用`ExecuteNonQuery()`、`ExecuteReader()`、`ExecuteScalar()`等方法执行查询，并获取结果。

下面是一个示例，演示如何在C\#中使用参数化查询：

using System;
    using System.Data.SqlClient;
    
    namespace ParameterizedQueryExample
    {
        class Program
        {
            static void Main(string[] args)
            {
                string connectionString = "Data Source=YourServer;Initial Catalog=YourDatabase;User ID=YourUsername;Password=YourPassword";
    
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
    
                    // 参数化查询
                    string query = "SELECT * FROM Persons WHERE Age > @age";
                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@age", 25);
    
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = reader.GetInt32(0);
                                string name = reader.GetString(1);
                                int age = reader.GetInt32(2);
                                Console.WriteLine("Id: {0}, Name: {1}, Age: {2}", id, name, age);
                            }
                        }
                    }
    
                    connection.Close();
                }
    
                Console.WriteLine("Press any key to exit...");
                Console.ReadKey();
            }
        }
    }

**参数化查询是一种通过将查询参数与SQL语句分离的技术，用于执行数据库查询操作。它的主要目的是提高查询的安全性和性能。下面是对参数化查询的进一步说明：**

1.  防止SQL注入攻击：
    
     *  SQL注入是一种常见的安全漏洞，攻击者通过在用户输入中插入恶意的SQL代码来破坏数据库的完整性和保密性。
     *  使用参数化查询可以有效地防止SQL注入攻击，因为参数值会被正确地转义和处理，而不是直接将其嵌入到SQL语句中。
2.  提高性能：
    
     *  参数化查询可以在数据库服务器上进行查询的缓存和重用，从而提高查询的执行速度。
     *  当执行多次相同的查询，只有参数值不同的情况下，参数化查询可以减少数据库服务器上的查询计划生成和编译过程。
3.  使用参数化查询的步骤： 以下是在C\#中使用参数化查询的一般步骤：
    
     *  构建带有参数的SQL查询：在SQL查询语句中使用占位符（如`@parameterName`）来表示参数。
     *  创建SqlCommand对象：使用带有占位符的SQL查询语句创建`SqlCommand`对象。
     *  设置参数：为每个占位符创建一个`SqlParameter`对象，并将其添加到`SqlCommand`对象的`Parameters`集合中，设置参数名称、数据类型和值。
     *  执行查询：使用`ExecuteNonQuery()`、`ExecuteReader()`、`ExecuteScalar()`等方法来执行查询，并获取结果。

以下是一个使用参数化查询的示例：

using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
    
        string query = "SELECT * FROM Persons WHERE Age > @age";
    
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            command.Parameters.AddWithValue("@age", 25);
    
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    int id = reader.GetInt32(0);
                    string name = reader.GetString(1);
                    int age = reader.GetInt32(2);
                    Console.WriteLine("Id: {0}, Name: {1}, Age: {2}", id, name, age);
                }
            }
        }
    
        connection.Close();
    }

在这个示例中，查询语句中使用了一个占位符`@age`来表示参数。然后，使用`AddWithValue`方法创建一个`SqlParameter`对象，将参数名称设置为`@age`，并将参数值设置为`25`。最后，通过`ExecuteReader()`方法执行查询，并读取结果。

通过学习和应用参数化查询的概念和技术，你可以提高数据库查询的安全性，同时优化查询的性能和可维护性。

**当使用参数化查询时，需要注意以下几点：**

1.  使用适当的数据类型：
    
     *  确保使用与数据库中列的数据类型相匹配的参数类型。例如，如果在数据库中的列是整数类型（INT），则参数的数据类型应为`SqlDbType.Int`。
     *  使用正确的数据类型可以确保查询的准确性，并防止出现数据类型不匹配的错误。
2.  明确指定参数的大小：
    
     *  对于字符串类型的参数，需要明确指定其大小，以便与数据库中的列匹配。
     *  如果参数的值超过了指定的大小，将会发生截断或溢出。
3.  显式指定参数的方向：
    
     *  参数可以是输入参数、输出参数或输入输出参数。
     *  对于输入参数，将值传递给查询。对于输出参数，查询将返回一个值给应用程序。而对于输入输出参数，既可以将值传递给查询，又可以从查询中获取返回值。
     *  在创建参数时，使用`SqlParameter`对象的`Direction`属性显式指定参数的方向。

下面是一个使用参数化查询的示例，演示如何插入一条记录到Persons表中：

using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
    
        string query = "INSERT INTO Persons (Name, Age) VALUES (@name, @age)";
    
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            command.Parameters.AddWithValue("@name", "John");
            command.Parameters.AddWithValue("@age", 30);
    
            int rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine("Rows Affected: " + rowsAffected);
        }
    
        connection.Close();
    }

在这个示例中，使用参数化查询将一条记录插入到Persons表中。通过`AddWithValue`方法创建了两个参数：`@name`和`@age`。然后，通过`ExecuteNonQuery`方法执行插入操作，并获取受影响的行数。

参数化查询是一种安全且可靠的方法，可以防止SQL注入攻击，并提高数据库查询的性能和可维护性。通过掌握参数化查询的使用方法和注意事项，可以更好地保护数据库和应用程序的数据。

**当使用参数化查询时，以下是一些进一步的解释和注意事项：**

1.  避免拼接字符串：
    
     *  参数化查询是一种避免拼接字符串的方法，以减少语法错误和SQL注入攻击的风险。
     *  不要将用户输入直接拼接到SQL查询字符串中，而是使用参数来代替用户输入的值。
2.  显式指定参数的数据类型：
    
     *  在创建参数时，应显式指定参数的数据类型，以确保与数据库中的列类型匹配。
     *  可以使用`SqlParameter`对象的`SqlDbType`属性或`DbType`属性来设置参数的数据类型。
3.  使用命名参数：
    
     *  命名参数是一种通过名称引用参数的方法，而不是通过位置。
     *  在SQL查询中使用命名参数，可以提高查询的可读性和可维护性。
     *  在C\#中，使用`@parameterName`的形式来表示命名参数。
4.  参数化查询的性能：
    
     *  参数化查询可以提高查询的性能，特别是在需要多次执行相同查询的情况下。
     *  参数化查询可以减少数据库服务器上的查询计划生成和编译过程，从而提高查询的执行效率。
5.  重用SqlCommand对象：
    
     *  在执行多个参数化查询时，可以重用同一个`SqlCommand`对象，只需更新参数的值即可。
     *  这样可以减少对象的创建和销毁开销，提高代码的性能和效率。

总结起来，参数化查询是一种安全和可靠的数据库查询方法，可以防止SQL注入攻击，提高性能，并增加代码的可读性和可维护性。通过熟悉参数化查询的使用方法，并遵循最佳实践，你可以更好地保护数据库和应用程序的数据，并提高查询操作的效率。