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
– Stefan, Commented Nov 5, 2017 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: stackoverflow.com/questions/293311/… — Stefan
– Stefan, Commented Nov 5, 2017 at 12:45
Thanks, the issue is I use it in more than 30 places within my project — michael marmah
– michael marmah, Commented Nov 5, 2017 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
– Stefan, Commented Nov 5, 2017 at 13:54

Zohar Peled · Accepted Answer · 2017-11-05 14:25:41Z

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.

Collectives™ on Stack Overflow

C# function to execute parameterized insert statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related