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?

View

You can use reflection to create parametar name from property name of object, of course if sql parameters names are same as property names of object. – Toxoplasmosis

Answer 1

Use single line SqlParameterCollection.AddWithValue Method

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

Micturition

oh, just what i was looking for, thanks, so if i understood correctly, i simply just loop trough it, and changing the params? – View

@Mana, I am not sure what you meant by looping ?? do you have a list of parameters you want to add to the command parameters ? – Micturition

Stop using AddWithValue. It doesn't know the column type so it has to guess. When it guesses wrong your code starts acting unpredicably. – Glossographer

Answer 2

or other variation you might try like this

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

Lascar

I got your method also working, but i think ::Habibs:: method looked the simplest, but always fun to know that there are other ways, thanks, and Cheers – View

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.

Tahoe

Answer 4

Another technique, you can use..

List<SqlParameter> lstPrm = new List<SqlParameter>();

 lstPrm.Add(new SqlParameter("@pusername", usernameValue ));
 lstPrm.Add(new SqlParameter("@pID", someidValue));
 lstPrm.Add(new SqlParameter("@pPassword", passwordValue));

Add the end you can iterate to insert the parameters in your command object

Circumrotate

Answer 5

Use my SqlBuilder class. It lets you write paramaterized queries without ever creating a parameter, or having to worry about what its called. Your code will look like this...

var bldr = new SqlBuilder( myCommand );
bldr.Append("SELECT * FROM CUSTOMERS WHERE ID = ").Value(myId);
//or
bldr.Append("SELECT * FROM CUSTOMERS NAME LIKE ").FuzzyValue(myName);
myCommand.CommandText = bldr.ToString();

Your code will be shorter and much more readable. Compared to concatenated queries, you don't even need extra lines. The class you need is here...

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

public class SqlBuilder
{
private StringBuilder _rq;
private SqlCommand _cmd;
private int _seq;
public SqlBuilder(SqlCommand cmd)
{
    _rq = new StringBuilder();
    _cmd = cmd;
    _seq = 0;
}
public SqlBuilder Append(String str)
{
    _rq.Append(str);
    return this;
}
public SqlBuilder Value(Object value)
{
    string paramName = "@SqlBuilderParam" + _seq++;
    _rq.Append(paramName);
    _cmd.Parameters.AddWithValue(paramName, value);
    return this;
}
public SqlBuilder FuzzyValue(Object value)
{
    string paramName = "@SqlBuilderParam" + _seq++;
    _rq.Append("'%' + " + paramName + " + '%'");
    _cmd.Parameters.AddWithValue(paramName, value);
    return this;
}
public override string ToString()
{
    return _rq.ToString();
}
}

Hydroponics

Interesting concept. Not really answering /this/ question, though. – Volotta

Glad you like it. The question in essence was "how do I make my SQL code look neat when I've got 14 parameters?" I think this answers the question exactly ?? – Hydroponics

Answer 6

Better still, use my shiny new Visual Studio extension. You declare your parameters in your sql, intact in its own file. My extension will run your query when you save your file, and will make you a wrapper class to call at runtime, and a results class to access your results, with intellisense all over da place. You will see your sql parameters as arguments to the Execute() methods of the wrapper class. You will never have to write another line of parameter code in C#, or reader code, or cmd, or even connection (unless you want to manage that yourself). Gone gone gone :-)

Hydroponics