Location>code7788 >text

Summary of SQL binding variable methods

Popularity：764 ℃/2025-03-16 07:52:44

Recently encountered several problems on the project, about SQL binding variables

Let’s summarize and share it with everyone.

1. UseSqlParameter(Recommended method to prevent SQL injection)

supplySqlParameterto bind variables to improve security and performance.

  • Prevent SQL injection attacks.
  • Supports various data types to avoid recompilation of SQL parser.
using System;
using ;
using ;

class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string";
        string query = "SELECT * FROM Users WHERE Username = @Username";

        using (SqlConnection conn = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            (new SqlParameter("@Username", ) { Value = "test_user" });

            ();
            using (SqlDataReader reader = ())
            {
                while (())
                {
                    (reader["Username"]);
                }
            }
        }
    }
}

2. UseAddWithValuemethod

If you do not need to specify the parameter type explicitly, you can useAddWithValueDirect value transfer:

("@Username", "test_user");

There are two issues to note:

  • AddWithValueMay lead to implicit conversions that affect performance (e.g.intpassnvarchar）。
  • Suitable for simple cases, but not recommended for complex queries.

3. Stored Procedure + Bind Variables

Bind variables can also be used in stored procedures to improve security and code reusability.

  • Improve SQL reusability and execution efficiency (cache execution plan).
  • More secure, avoiding SQL injection.

SQL Server side (create stored procedures):

CREATE PROCEDURE GetUserByUsername
    @Username NVARCHAR(50)
AS
BEGIN
    SELECT * FROM Users WHERE Username = @Username
END

C# call code

 = ;
 = "GetUserByUsername";
(new SqlParameter("@Username", ) { Value = "test_user" });

4. Batch binding variables (Table-Valued Parameter, TVP)

If you need to pass multiple values ​​to SQL queries, you can use TVP to bind variables to improve the performance of batch operations.

  • Suitable for batch query or batch insertion to improve performance.
  • Avoid the overhead of looping execution of SQL statements.

SQL Server side (create TVP type):

CREATE TYPE UserTableType AS TABLE 
(
    UserId INT
)

C# code (passing multiple UserIds):

DataTable userTable = new DataTable();
("UserId", typeof(int));
(1);
(2);

using (SqlCommand cmd = new SqlCommand("SELECT * FROM Users WHERE UserId IN (SELECT UserId FROM @UserTable)", conn))
{
    (new SqlParameter("@UserTable", ) { TypeName = "UserTableType", Value = userTable });
}

The above are several common patterns and sample codes for C# binding variables.

 

Zhou Guoqing

2025/3/16

<Previous Next>