C# function to execute parameterized insert statement

Question

I have the below method that saves data into a SQL Server database

public static int ExecuteNonQuery(string cmdText)
{
    if(con.State == ConnectionState.Closed)
        con.Open();

    SqlCommand SC = new SqlCommand(cmdText, con);

    return SC.ExecuteNonQuery();
}

My issue is: this is prone to SQL injection, so the best way out is to use parameters and also if I want to save special characters like single quote, that is not possible so the parameter option to me is the best.

My question now is how do I handle that when am using a method since I can't determine the number of parameters a SQL statement would need

This is how I call it

return Database.ExecuteNonQuery("INSERT INTO UPR00112(EMPLOYID, UNIVERSITY, DEGREE, MAJOR, GPA, GPABASE, GRADUATIONYEAR, SUPID, NOTE, ATTACHMENT, USERDEF1, STATUS, TYP) VALUES('" + EmpID + "','" + Uni + "','" + Degree + "','" + Major + "','" + GPA + "','" + GPABase + "','" + GYear + "'," + SupID + ",'" + Note  + "','" + Attach  + "','" + UserName + "','" + Status + "','" + Typ + "')");

This seems to be a wrapper around the `SqlCommand.ExecuteNonQuery`. So by itself this function adds not very much. Which is a good thing in your case. You should check ho `cmdText` is constructed, and where this function is used. That way you'll know the impact of any changes. — Stefan, Nov 05 '17 at 12:14
If that's the only place you are calling it, it would be easily converted to a parametered sql command. You could create a function: `DataBase.InsertUpr(employId, university, degree... etc);` See for example: https://stackoverflow.com/questions/293311/whats-the-best-method-to-pass-parameters-to-sqlcommand — Stefan, Nov 05 '17 at 12:45
Thanks, the issue is I use it in more than 30 places within my project — michael marmah, Nov 05 '17 at 13:33
... even if it where a 100, that's doable. The benefit is that you can create a function per operation. I think it will be less than 100 and that is is a good programming exercise ;-) I think it can be done within an hour. — Stefan, Nov 05 '17 at 13:54

Zohar Peled · Answer 1 · 2017-11-05T14:25:41.417

You can use the params keyword like I did for ADONETHelper:

public static int ExecuteNonQuery(string sql, CommandType commandType, params SqlParameter[] parameters)
{
    using (var con = new SqlConnection("ConnectionString"))
    {
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.CommandType = commandType;
            foreach (var parameter in parameters)
            {
                cmd.Parameters.Add(parameter);
            }
            con.Open();
            return cmd.ExecuteNonQuery();
        }
    }
}

Note that this method uses a local variable for the SqlConnection object, so that it can be disposed as soon as you are done using it and return to the connection pool.

Use this function like this:

var params = new SQLParameter[]
{
    new SqlParameter("@P1", SqlDbType.Int).Value = intValue,
    new SqlParameter("@P2", SqlDbType.NVarChar).Value = stringValue,
    new SqlParameter("@P3", SqlDbType.DateTime).Value = dateTimeValue
};

return Database.ExecuteNonQuery("INSERT INTO .... VALUES (@P1, @P2, @P3)", params);

In ADONETHelper I've created a generic method like this that also takes a Func<IDbCommand, T> as a parameter and returns T. This allowed me to use it for executing inserts, updates, deletes and selects - it supports ExecuteNonQuery(), ExecuteScalar(), ExecuteReader() and also using a DataAdapter to fill a DataSet or a DataTable, all using the same Execute method.

C# function to execute parameterized insert statement

1 Answers1