Parameterized queries in C# using SqlParameter

The role and usage of SqlParameter in C

#In C# development, interaction with the database is one of the common tasks. In order to ensure the security and validity of data, we often need to use parameterized queries to prevent SQL injection attacks. SqlParameter is a class in C# used to build parameterized queries. It provides a safe and convenient way to handle parameters in database queries.

The role of SqlParameter
The SqlParameter class is mainly used to add parameters to SQL statements. Its main functions are as follows:

1. Prevent SQL injection attacks: By using SqlParameter, we can escape the parameter value in advance and ensure that the parameter value will not be modified when executing a database query. Interpreted as part of the SQL statement.
2. Improve performance: In database queries, query plans usually cache the query to improve performance. When using SqlParameter, the same query statement only needs to be compiled once and can then be used repeatedly.
3. Support various data types and sizes: SqlParameter supports various common data types, such as strings, integers, dates, etc., and can set the size, precision, and decimal places of parameters as needed.

Usage of SqlParameter
Below we use an example to demonstrate how to use SqlParameter to build parameterized queries.

Suppose we have a table named "Employees" that contains employee ID, name and salary information. We need to query employee information whose salary is greater than a specified amount. The following is a code example using SqlParameter:

string queryString = "SELECT EmployeeID, FirstName, LastName FROM Employees WHERE Salary > @salary";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(queryString, connection);
    command.Parameters.Add("@salary", SqlDbType.Decimal).Value = 5000; // 设置参数名称、类型和值

    connection.Open();
    SqlDataReader reader = command.ExecuteReader();

    while (reader.Read())
    {
        int employeeId = (int)reader["EmployeeID"];
        string firstName = reader["FirstName"].ToString();
        string lastName = reader["LastName"].ToString();

        Console.WriteLine($"Employee ID: {employeeId}, Name: {firstName} {lastName}");
    }

    reader.Close();
}

In the above example, we first create a query string that includes the parameter name "@salary". Then, we created a database connection and query command object using SqlConnection and SqlCommand.

Next, we add a parameter to the query command by calling the command.Parameters.Add method. Here we specify the name, type and value of the parameter. In this example, we use SqlDbType.Decimal as the parameter type and set the parameter value to 5000.

Finally, we open the database connection and execute the query command. Get the query results by calling command.ExecuteReader, and use SqlDataReader to read the results line by line. In the loop, we get the ID and name of each employee through the column name, and output it to the console.

Summary
By using SqlParameter, we can effectively build parameterized queries, thereby improving the security and performance of database queries. By setting the parameter's name, type, and value, we can easily add parameters to SQL statements and prevent potential SQL injection attacks. I hope this article will help you understand the role and usage of SqlParameter in C#.

The above is the detailed content of Parameterized queries in C# using SqlParameter. For more information, please follow other related articles on the PHP Chinese website!