ASP.NET三层架构DAL层DBManager类适配参数化查询改造

Great question—string concatenation for SQL is a huge security risk (hello SQL injection!), so moving to parameterized queries is a smart, necessary upgrade. Let's walk through how to modify your existing DBManager class step by step, with concrete code examples.

First, you'll want to add overloads to your existing data access methods (like ExecuteNonQuery, ExecuteReader, ExecuteScalar) that accept a collection of SQL parameters. For SQL Server, we'll use SqlParameter, but you can make this database-agnostic with DbParameter if you need to support multiple databases later.

For example, if your current DBManager has:

public int ExecuteNonQuery(string sql)
{
    // Existing logic with string-concatenated SQL
}

Add an overload like this:

public int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
{
    // New logic for parameterized queries
}

Modify the new overloaded methods to create a SqlCommand with parameters instead of just passing the raw SQL string. Here's how to implement the ExecuteNonQuery overload (adjust based on your existing connection handling):

public int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
{
    int rowsAffected = 0;
    using (SqlConnection conn = new SqlConnection(YourConnectionString))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            // Add all parameters to the command
            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
            }
            rowsAffected = cmd.ExecuteNonQuery();
        }
    }
    return rowsAffected;
}

Do the same for ExecuteReader and ExecuteScalar:

public SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters)
{
    SqlConnection conn = new SqlConnection(YourConnectionString);
    try
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
            }
            // Use CommandBehavior.CloseConnection so reader closes the connection when done
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
    }
    catch
    {
        conn.Close();
        throw;
    }
}

public object ExecuteScalar(string sql, params SqlParameter[] parameters)
{
    using (SqlConnection conn = new SqlConnection(YourConnectionString))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
            }
            return cmd.ExecuteScalar();
        }
    }
}

To make usage even cleaner, add a helper method to create SqlParameter objects without repeating code:

public SqlParameter CreateSqlParameter(string parameterName, object value)
{
    return new SqlParameter(parameterName, value ?? DBNull.Value);
}

This handles null values automatically by converting them to DBNull.Value, which prevents database errors.

Instead of risky string concatenation:

// Bad: SQL injection risk!
string username = Request.Form["Username"];
string sql = $"SELECT * FROM Users WHERE Username = '{username}'";
dbManager.ExecuteReader(sql);

Use the parameterized version:

// Good: Safe from SQL injection
string username = Request.Form["Username"];
string sql = "SELECT * FROM Users WHERE Username = @Username";
SqlParameter param = dbManager.CreateSqlParameter("@Username", username);
var reader = dbManager.ExecuteReader(sql, param);

Or pass multiple parameters at once:

string sql = "INSERT INTO Users (Username, Email) VALUES (@Username, @Email)";
var parameters = new[]
{
    dbManager.CreateSqlParameter("@Username", "johndoe"),
    dbManager.CreateSqlParameter("@Email", "john@example.com")
};
dbManager.ExecuteNonQuery(sql, parameters);

• Always use parameter names that match your SQL (e.g., @Username in SQL maps to a parameter named @Username).
• Never concatenate user input directly into SQL strings—even if you "sanitize" it, parameterization is the only reliable defense against SQL injection.
• If your DBManager uses a base DbConnection/DbCommand for database agnosticism, swap SqlParameter with DbParameter and use DbProviderFactory to create parameters dynamically.

内容的提问来源于stack exchange，提问作者user8951186