//************************************** // for :A Method to convert a SqlCommand with paramaters to string value //************************************** Copyright Andrew Buchan, www.andrewbuchan.co.uk 2010 //************************************** // 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 (from psc cd) // // // 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); // //Side Effects:None //************************************** 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; }