parameterized sql query - asp.net / c#

Question

So I recently learned that I should absolutely be using parametrized query's to avoid security issues such as SQL injection. That's all fine and all, I got it working.

This code shows some of the code how I do it:

param1 = new SqlParameter();
param1.ParameterName = "@username";
param1.Value = username.Text;
cmd = new SqlCommand(str, sqlConn);
cmd.Parameters.Add(param1);

//and so on

But the problem is, I have over 14 variables that needs to be saved to the db, it's like a registration form. And it would look really messy if I have to write those lines 14 times to parametrize each variable. Is there a more dynamic way of doing this? Like using a for loop or something and parametrizing every variable in the loop somehow?

Answer 1

Use single line SqlParameterCollection.AddWithValue Method

cmd.Parameters.AddWithValue("@username",username.Text);

Answer 2

or other variation you might try like this

command.Parameters.Add(new SqlParameter("Name", dogName));

Answer 3

Here you go... via dapper:

connextion.Execute(sql, new {
    username = username.Text,
    id = 123, // theses are all invented, obviously
    foo = "abc",
    when = DateTime.UtcNow
});

that maps to ExecuteNonQuery, but there are other methods, such as Query<T> (binds the data very efficiently by name into objects of type T per row), Query (like Query<T>, but uses dynamic), and a few others (binding multiple grids or multiple objects, etc). All ridiculously optimized (IL-level meta-programming) to be as fast as possible.