c# - 对 sqlcommand.executescalar 使用声明

Question

我正在使用 using 语句来验证客户编号。

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
    connection.Open();

    using (SqlCommand cmdCheck = new SqlCommand("SELECT COUNT(CUSTOMER_NO) FROM WEBSITE_CUSTOMERS WHERE UPPER(CUSTOMER_NO) = '" + strCustomer.Trim().ToUpper() + "';", connection))
    {
        int nExists = (int)cmdCheck.ExecuteScalar();
        if (nExists > 0) 
            return true;
        else
            return false;
    }
}

这是以前在 stackoverflow 上建议我检查预先存在的记录的代码......它工作得很好，但我想知道是否有一种方法可以将参数与它一起用于客户编号，因为输入了这个变量通过表格，我想保护它免受注入(inject)。当它在这样的 using 语句中时，我应该在哪里为 cmdCheck 创建参数？

Answer 1

在初始化命令后添加参数。一个方便的方法是 AddWithValue :

const string sql = @"SELECT 
                        COUNT(CUSTOMER_NO) 
                     FROM 
                        WEBSITE_CUSTOMERS 
                     WHERE 
                        UPPER(CUSTOMER_NO) = @CUSTOMER_NO;";

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
    using (SqlCommand cmdCheck = new SqlCommand(sql, connection))
    {
        cmdCheck.Parameters.AddWithValue("@CUSTOMER_NO", strCustomer.Trim().ToUpper());
        connection.Open();
        int nExists = (int)cmdCheck.ExecuteScalar();
        return nExists > 0;
    }
}