CODEWITHSUNDEEP
c#asp.netparametersusing
Stuart
Stuart

Reputation: 1574

using stating for sqlcommand.executescalar

I am using a using statement for validating a customer number.

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;
    }
}

This is code previously advised to me on stackoverflow for checking preexisting records... it works great, but I would like to know if there's a way that I can use a parameter with it for the customer number since this variable is entered through the form, I want to protect it from injection. Where would I create the parameter for cmdCheck when its in a using statement like this?

Upvotes: 1

Views: 1421

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460158

Add the parameter after you've initialized the command. A convenient method is 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;
    }
}

Upvotes: 5

Related Questions