using SqlParameterCollectionExtensions

Question

I have a ugly code that can't be reused. I have many similar queries. I want to rewrite it with SqlParameterCollectionExtensions or other better ways. But I don't know about SqlParameterCollectionExtensions at all.

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        SqlConnection con = new SqlConnection(strCon);
        con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "UPDATE Problem_DE SET ProbDesc = @ProbDesc, field_1 = @field_1, field_2 = @field_2, field_3 = @field_3, field_4 = @field_4, field_5 = @field_5, field_6 = @field_6, field_7 = @field_7 WHERE (ProbId = @ProbId)";
        if (e.NewValues["ProbDesc"] == null)
            cmd.Parameters.AddWithValue("@ProbDesc", DBNull.Value);
        else
            cmd.Parameters.AddWithValue("@ProbDesc", e.NewValues["ProbDesc"]);
        if (e.NewValues["field_1"] == null)
            cmd.Parameters.AddWithValue("@field_1", DBNull.Value);
        else
            cmd.Parameters.AddWithValue("@field_1", e.NewValues["field_1"]);
        if (e.NewValues["field_2"] == null)
            cmd.Parameters.AddWithValue("@field_2", DBNull.Value);
        else
            cmd.Parameters.AddWithValue("@field_2", e.NewValues["field_2"]);
        if (e.NewValues["field_3"] == null)
            cmd.Parameters.AddWithValue("@field_3", DBNull.Value);
        else
            cmd.Parameters.AddWithValue("@field_3", e.NewValues["field_3"]);

        if (e.NewValues["field_4"] == null)
            cmd.Parameters.AddWithValue("@field_4", DBNull.Value);
        else
            cmd.Parameters.AddWithValue("@field_4", e.NewValues["field_4"]);
         \\ blah blah
        cmd.ExecuteNonQuery();
        con.Close();
     }

The sql parameters come from e or textbox etc. Thanks.

Answer 1

Maybe something like this? I assume the problem is that you have a variable number of values, depending on the problem table?

private void UpdateProblem(string problemName, string problemDescription, int problemId, object[] fieldValues)
{
    SqlConnection   con = null;
    SqlCommand      cmd = new SqlCommand();
    StringBuilder   sql = new StringBuilder();
    int             fieldCounter = 1;

    // start building the sql statement
    sql.AppendFormat("UPDATE {0} SET ProbDesc = @ProbDesc", problemName);

    // add the 'description' parameter
    cmd.Parameters.Add(new SqlParameter("@ProbDesc", problemDescription));

    // add each field value to the update statement... the SqlParameter will infer the database type.
    foreach(object fieldValue in fieldValues)
    {
        // add additional SET clauses to the statement
        sql.AppendFormat(",field{0} = @field{0}", fieldCounter);

        // add the field parameter to the command's collection
        cmd.Parameters.Add(new SqlParameter(String.Format("@field{0}", fieldCounter), fieldValue));

        fieldCounter++;
    }

    // finish up the SQL statement by adding the where clause
    sql.Append(" WHERE (ProbId = @ProbId)");

    // add the 'problem ID' parameter to the command's collection
    cmd.Parameters.Add(new SqlParameter("@ProbId", problemId));

    // finally, execute the SQL
    try
    {
        con.Open();

        cmd.Connection = con;

        cmd.CommandText = sql.ToString();

        cmd.ExecuteNonQuery();
    }
    catch(SqlException ex)
    {
        // do some exception handling
    }
    finally
    {
        if(con != null)
            con.Dispose();
    }
}

An example to call this code would be:

public void UpdateProblemDe()
{
    int         problemId = FetchCurrentProblemId();
    string      field1 = e.NewValues["field_1"];
    string      field2 = e.NewValues["field_2"];
    string      field3 = ddlField3.SelectedValue;
    int         field4 = Convert.ToInt32(e.NewValues["field_4"]);
    string      field5 = txtField5.Text;
    DateTime    field6 = DateTime.Now.AddSeconds(Convert.ToInt32(ddlField6.SelectedValue));
    string      field7 = txtField7.Text;
    object[6]   fieldValues;

    if(field1 != null)
        fieldValues[0] = field1;
    else
        fieldValues[0] = DBNull.Value;

    if(field2 != null)
        fieldValues[1] = field2;
    else
        fieldValues[1] = DBNull.Value

    fieldValues[2] = field3;
    fieldValues[3] = field4;    
    fieldValues[4] = field5;
    fieldValues[5] = field6;
    fieldValues[6] = field7;

    UpdateProblem("Problem_DE", "Houston, we have a problem", problemId, fieldValues);  
}   

The example code above is obviously just a demonstration of how to make an array of objects stored from page control values and doesn't include any data validation that you will need to implement in production code.

If you won't know how big the object[] array needs to be at runtime, you can change it to be a generic List of objects and add the items dynamically.

Answer 2

Based on this answer Create new SQLCommand's or reuse the same one you could refactor your code the following way

public class DbHepler
    {
        private readonly string _connectionString;
        public DbHepler(string connectionString)
        {
            _connectionString = connectionString;
        }

        public void ExecuteNonQuery(string query)
        {
            ExecuteNonQuery(query, null);
        }

        public void ExecuteNonQuery(string query, Dictionary<string, object> parameters)
        {
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = query;

                    if (parameters != null)
                    {
                        foreach (string parameter in parameters.Keys)
                        {
                            cmd.Parameters.AddWithValue(parameter, parameters[parameter] ?? DBNull.Value);
                        }
                    }

                    cmd.ExecuteNonQuery();
                }

                conn.Close();
            }
        }
    }

your code will look something like:

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            string query = "UPDATE Problem_DE SET ProbDesc = @ProbDesc, field_1 = @field_1, field_2 = @field_2, field_3 = @field_3, field_4 = @field_4, field_5 = @field_5, field_6 = @field_6, field_7 = @field_7 WHERE (ProbId = @ProbId)";
            Dictionary<string, object> parameters = new Dictionary<string, object>();

            if (e.NewValues["ProbDesc"] == null)
                parameters.Add("@ProbDesc", null);
            else
                parameters.Add("@ProbDesc", e.NewValues["ProbDesc"]);

            //blah blah

            DbHepler dbHepler = new DbHepler("your sql connection info");
            dbHepler.ExecuteNonQuery(query, parameters);
         }