Introduction
Executing queries at runtime is must for any application, whether it is a web application, a windows application or even a console application. Also, getting input from the application/user and passing it to your database stored procedures and user defined functions is useful if you need perform some operation on particular set value.
Assumption:Examples below assumes that you have a SqlConnection object, associated with SqlCommand object.
About SqlParameters
Using SqlParameter is a way of providing runtime values to your queries in .NET. Parameters can have any direction i.e., whether it is INPUT/OUTPUT/INPUTOUTPUT. Also datatype, length and value can be assigned to it. Various constructors are available for declaring SqlParameter variables.
The basic template for declaring parameters is:
String sqlString = “Your Query Here”;
SqlCommand sqlCommand = new SqlCommand(sqlString);
SqlParameter sqlParam = new SqlParameter("@param1", SqlDbType.VarChar, 50)
sqlParam.Value = 100;
sqlParam.Direction = ParameterDirection.Input;
sqlCommand.Parameters.Add(sqlParam);
The above template indicates that INPUT parameter having name @param1 of data type VARCHAR and size 50 is declared. It also has been assigned a value 100.
When you construct your queries dynamically, you can specify the value for the columns. The value can be in some memory variable and can be concatenated using string functions for getting your complete SELECT query.
Generating the queries at database layer or at the business layer is another topic for discussion, but for this text it is assumed that some of the queries are constructed at the business layer and executed using the SqlCommand object.
For example, it is possible to construct the queries as shown below and when executed, the value from myName variable will be replaced at runtime.
int myID = “1001”;
string sql = " SELECT * FROM MyDetails WHERE MyID = “ + myID;
SqlCommand sqlCommand = new SqlCommand(sql);
sqlCommand.ExecuteNonQuery();
An alternative method is to provide the value as Parameters:
string sql = " SELECT * FROM MyDetails WHERE MyID = @myID”;
SqlCommand sqlCommand = new SqlCommand(sql);
SqlParameter sqlParam = new SqlParameter("@myID", SqlDbType.Int);.
sqlParam.Value = “1001”;
sqlCommand.Parameters.Add(sqlParam);
sqlCommand.ExecuteNonQuery();
Why specifying the Parameters separately?
Consider the case of inserting/updating object value to your column. If you are uploading a file to your database, you are not able to concatenate it in your query; the reason will be the data is in a binary format. The solution is to pass the content of your object to your SELECT queries by specifying the object value in your parameter.
For example : Read the file contents into byte[].
byte[] fileData = new byte[lengthOfFile];
Then assign this byte[] variable to the SqlParameter by using the constructor:
SqlParameter(string ParameterName, Object value).
The code below allows to pass the contents of a file with your INSERT query.
string sql = "INSERT INTO TABLENAME (binaryColumn) VALUES (@binaryValue)";
//Create Parameter and Execute the query.
SqlCommand sqlCommand = new SqlCommand(sql);
SqlParameter sqlParam = new SqlParameter("@binaryValue", fileData);
sqlCommand.Parameters.Add(sqlParam);
sqlCommand.ExecuteNonQuery();
