One possible solution would be to pass the parameters name and value using a Dictionary object. A Dictionary object holds a collection of keys and values, and this is exactly what a single SqlParameter is - a single Key/Value container in the form:
Key = @Value
A collection can hold an arbitrary count of items, for example:
new Dictionary<String, Object>
{
{ "@Name", "Anonymous" },
{ "@Age", 25 },
{ "@Street", "Highway" },
{ "@Number", "1001" },
{ "@City", "NoName" }
}
In the example above, the key is of type String and the value of type Object. Object allows parameter values of arbitrary types (the explanation comes later in the code examples).
One possibility to create dynamic SQL statements would be:
- extract the repetitive tasks, for example the creation of the SqlCommand and the passing of the parameters to the SqlCommand variable
- create parametrised dynamic SQL query string for the wanted SQL command
The code can look like this:
// extract all repetitive tasks
// Create an array of SqlParameters from the given Dictionary object.
// The parameter value is of type Object in order to allow parameter values of arbitrary types!
// The .NET Framework converts the value automatically to the correct DB type.
// MSDN info: http://msdn.microsoft.com/en-us/library/0881fz2y%28v=vs.110%29.aspx
private SqlParameter[] dictionaryToSqlParameterArray(Dictionary<string, object> parameters)
{
var sqlParameterCollection = new List<SqlParameter>();
foreach (var parameter in parameters)
{
sqlParameterCollection.Add(new SqlParameter(parameter.Key, parameter.Value));
}
return sqlParameterCollection.ToArray();
}
// sqlQuery is the complete parametrised query
// for example like: INSERT INTO People(Name, Age, Street) VALUES(@Name, @Age, @Street)
private SqlCommand createSqlCommand(String sqlQuery, Dictionary<String, object> parameters)
{
SqlCommand command = new SqlCommand(sqlQuery);
command.Parameters.AddRange(dictionaryToSqlParameterArray(parameters));
return command;
}
Now a call with dynamic count of parameters will look like this:
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
string sqlQuery = "SELECT email, passwd, login_id, full_name FROM members WHERE email = @email AND name = @name";
// using the newly created method instead of adding/writing every single parameter manually
SqlCommand command = createSqlCommand(sqlQuery, new Dictionary<String, Object>
{
{ "@email", "[email protected]" },
{ "@name", "test" }
});
SqlDataReader reader = command.ExecuteReader();
It is a good start, but this is still not very dynamic, since we need to write the complete query every time (we save only the typing of command.Parameters.Add). Let's change this in order to type even less:
// create a parametrised SQL insert command with arbitrary count of parameters for the given table
private SqlCommand createSqlInsert(String tableName, Dictionary<String, object> parameters)
{
// the sql insert command pattern
var insertQuery = @"INSERT INTO {0}({1}) VALUES({2})";
// comma separated column names like: Column1, Column2, Column3, etc.
var columnNames = parameters.Select (p => p.Key.Substring(1)).Aggregate ((h, t) => String.Format("{0}, {1}", h, t));
// comma separated parameter names like: @Parameter1, @Parameter2, etc.
var parameterNames = parameters.Select (p => p.Key).Aggregate ((h, t) => String.Format("{0}, {1}", h, t));
// build the complete query
var sqlQuery = String.Format(insertQuery, tableName, columnNames, parameterNames);
// debug
Console.WriteLine(sqlQuery);
// return the new dynamic query
return createSqlCommand(sqlQuery, parameters);
}
// create a parametrised SQL select/where command with arbitrary count of parameters for the given table
private SqlCommand createSqlWhere(String tableName, Dictionary<String, object> parameters)
{
// the sql select command pattern
var whereQuery = @"SELECT * FROM {0} WHERE {1}";
// sql where condition like: Column1 = @Parameter1 AND Column2 = @Parameter2 etc.
var whereCondition = parameters.Select (p => String.Format("{0} = {1}", p.Key.Substring(1), p.Key)).Aggregate ((h, t) => String.Format("{0} AND {1}", h, t));
// build the complete condition
var sqlQuery = String.Format(whereQuery, tableName, whereCondition);
// debug
Console.WriteLine(sqlQuery);
// return the new dynamic query
return createSqlCommand(sqlQuery, parameters);
}
Now the creation of a SELECT command will look like this:
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
// specify only table name and arbitrary count of parameters
var getPersonSqlCommand = createSqlWhere("People", new Dictionary<String, Object>
{
{ "@Name", "J.D." },
{ "@Age", 30 }
});
SqlDataReader reader = getPersonSqlCommand.ExecuteReader();
The createSqlWhere method will return an initialized SqlCommand with the query:
SELECT * FROM People WHERE Name = @Name AND Age = @Age
The INSERT will also be short:
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
// specify only table name and arbitrary count of parameters
var addPersonSqlCommand = createSqlInsert("People", new Dictionary<String, Object>
{
{ "@Name", "Anonymous" },
{ "@Age", 25 },
{ "@Street", "Test" }
});
addPersonSqlCommand.ExecuteNonQuery();
The corresponding SQL command will look like this:
INSERT INTO People(Name, Age, Street) VALUES(@Name, @Age, @Street)
Other SQL commands like DELETE, UPDATE and so on can be created in the same way. New parameters should be added only in one place - in the dictionary.
Admittedly the initial effort is more than just writing one method, but it will pay off if the new methods are used more than once (a couple of times) - for example five parametrised selects and/or inserts on five different tables with different parameters, which is certainly always the case in small and average sized software projects.
whereclause anyway) ?SELECT * FROM members WHERE email = @email