This code converts SqlParameters of a SqlCommand object into their literal value.
For instance, if you have a SqlCommand which runs a query which has parameters, you will never actually see the query that your database engine runs.
Using this method, the SqlCommand query is converted so that you see exactly what is run on your database engine.
This works with SqlParameters of type VarChar, Bit and Int, however all are supported in some way.
//**************************************
// for :A Method to convert a SqlCommand with paramaters to string value
//**************************************
Copyright Andrew Buchan, www.andrewbuchan.co.uk 2010
code: Can't Copy and Paste this? Click here for a copy-and-paste friendly version of this code!
Terms of Agreement:
By using this code, you agree to the following terms...
You may use
this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
You may link to this code from another website, but ONLY if it is not wrapped in a frame.
You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
//**************************************
// Name: A Method to convert a SqlCommand with paramaters to string value
// Description:This code converts SqlParameters of a SqlCommand object into their literal value.
For instance, if you have a SqlCommand which runs a query which has parameters, you will never actually see the query that your database engine runs.
Using this method, the SqlCommand query is converted so that you see exactly what is run on your database engine.
This works with SqlParameters of type VarChar, Bit and Int, however all are supported in some way.
// By: Andrew Buchan
//
// Inputs:SqlCommand object
//
// Returns:String version of the input SqlCommand with its parameters values
//
// Assumes:The SqlCommand must be initialsed before use.
To call this method:
SqlCommand cmd = new SqlCommand("SELECT Col1, Col2, Col3 FROM TableName WHERE (Col1 = @Prm1) AND (Col2 = @Prm2) AND (Col3 = @Prm3)");
cmd.Parameters.Add("@Prm1", SqlDbType.VarChar).Value = "000";
cmd.Parameters.Add("@Prm2", SqlDbType.Int).Value = 500;
cmd.Parameters.Add("@Prm3", SqlDbType.Bit).Value = false;
ConvertCommandParamatersToLiteralValues(cmd);
//
//This code is copyrighted and has// limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=7760&lngWId=10//for details.//**************************************
private static string ConvertCommandParamatersToLiteralValues(SqlCommand cmd)
{
string query = cmd.CommandText;
foreach (SqlParameter prm in cmd.Parameters)
{
switch (prm.SqlDbType)
{
case SqlDbType.Bit:
int boolToInt = (bool)prm.Value ? 1 : 0;
query = query.Replace(prm.ParameterName, string.Format("{0}", (bool)prm.Value ? 1 : 0));
break;
case SqlDbType.Int:
query = query.Replace(prm.ParameterName, string.Format("{0}", prm.Value));
break;
case SqlDbType.VarChar:
query = query.Replace(prm.ParameterName, string.Format("'{0}'", prm.Value));
break;
default:
query = query.Replace(prm.ParameterName, string.Format("'{0}'", prm.Value));
break;
}
}
return query;
}
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular code, please
click here instead.)
