Get the generated SQL statement from a SqlCommand object?
Whilst not perfect, here's something I knocked up for TSQL - could be easily tweaked for other flavors... If nothing else it will give you a start point for your own improvements :)
This does an OK job on data types and output parameters etc similar to using "execute stored procedure" in SSMS. We mostly used SPs so the "text" command doesn't account for parameters etc
public static String ParameterValueForSQL(this SqlParameter sp)
{
String retval = "";
switch (sp.SqlDbType)
{
case SqlDbType.Char:
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.Text:
case SqlDbType.Time:
case SqlDbType.VarChar:
case SqlDbType.Xml:
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.DateTimeOffset:
retval = "'" + sp.Value.ToString().Replace("'", "''") + "'";
break;
case SqlDbType.Bit:
retval = (sp.Value.ToBooleanOrDefault(false)) ? "1" : "0";
break;
default:
retval = sp.Value.ToString().Replace("'", "''");
break;
}
return retval;
}
public static String CommandAsSql(this SqlCommand sc)
{
StringBuilder sql = new StringBuilder();
Boolean FirstParam = true;
sql.AppendLine("use " + sc.Connection.Database + ";");
switch (sc.CommandType)
{
case CommandType.StoredProcedure:
sql.AppendLine("declare @return_value int;");
foreach (SqlParameter sp in sc.Parameters)
{
if ((sp.Direction == ParameterDirection.InputOutput) || (sp.Direction == ParameterDirection.Output))
{
sql.Append("declare " + sp.ParameterName + "\t" + sp.SqlDbType.ToString() + "\t= ");
sql.AppendLine(((sp.Direction == ParameterDirection.Output) ? "null" : sp.ParameterValueForSQL()) + ";");
}
}
sql.AppendLine("exec [" + sc.CommandText + "]");
foreach (SqlParameter sp in sc.Parameters)
{
if (sp.Direction != ParameterDirection.ReturnValue)
{
sql.Append((FirstParam) ? "\t" : "\t, ");
if (FirstParam) FirstParam = false;
if (sp.Direction == ParameterDirection.Input)
sql.AppendLine(sp.ParameterName + " = " + sp.ParameterValueForSQL());
else
sql.AppendLine(sp.ParameterName + " = " + sp.ParameterName + " output");
}
}
sql.AppendLine(";");
sql.AppendLine("select 'Return Value' = convert(varchar, @return_value);");
foreach (SqlParameter sp in sc.Parameters)
{
if ((sp.Direction == ParameterDirection.InputOutput) || (sp.Direction == ParameterDirection.Output))
{
sql.AppendLine("select '" + sp.ParameterName + "' = convert(varchar, " + sp.ParameterName + ");");
}
}
break;
case CommandType.Text:
sql.AppendLine(sc.CommandText);
break;
}
return sql.ToString();
}
this generates output along these lines...
use dbMyDatabase;
declare @return_value int;
declare @OutTotalRows BigInt = null;
exec [spMyStoredProc]
@InEmployeeID = 1000686
, @InPageSize = 20
, @InPage = 1
, @OutTotalRows = @OutTotalRows output
;
select 'Return Value' = convert(varchar, @return_value);
select '@OutTotalRows' = convert(varchar, @OutTotalRows);
What SQL is being sent from a SqlCommand object
You need to use the SQL Server Profiler to watch what comes from the application. I believe it can show you the SQL and the parameters, which you will need to see.
From .NET can I get the full SQL string generated by a SqlCommand object (with SQL Parameters)?
A simple loop replacing all the parameter names with their values will provide you with something similar to what the end result is, but there are several problems.
- Since the SQL is never actually rebuilt using the parameter values, things like newlines and quotes don't need to be considered
- Parameter names in comments are never actually processed for their value, but left as-is
With those in place, and taking into account parameter names that starts with the same characters, like @NAME
and @NAME_FULL
, we can replace all the parameter names with the value that would be in the place of that parameter:
string query = cmd.CommandText;
foreach (SqlParameter p in cmd.Parameters.OrderByDescending(p => p.ParameterName.Length))
{
query = query.Replace(p.ParameterName, p.Value.ToString());
}
there is one problem left with this, however, and that is if a parameter is a string, then the SQL that initially looks like this:
SELECT * FROM yourtable WHERE table_code = @CODE
will look like this:
SELECT * FROM yourtable WHERE table_code = SOME CODE WITH SPACES
This is clearly not legal SQL, so we need to account for some parameter-types as well:
DbType[] quotedParameterTypes = new DbType[] {
DbType.AnsiString, DbType.Date,
DbType.DateTime, DbType.Guid, DbType.String,
DbType.AnsiStringFixedLength, DbType.StringFixedLength
};
string query = cmd.CommandText;
var arrParams = new SqlParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(arrParams, 0);
foreach (SqlParameter p in arrParams.OrderByDescending(p => p.ParameterName.Length))
{
string value = p.Value.ToString();
if (quotedParameterTypes.Contains(p.DbType))
value = "'" + value + "'";
query = query.Replace(p.ParameterName, value);
}
How do I view the SQL generated by the Entity Framework?
You can do the following:
IQueryable query = from x in appEntities
where x.id == 32
select x;
var sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString();
or in EF6:
var sql = ((System.Data.Entity.Core.Objects.ObjectQuery)query)
.ToTraceString();
or in EF6.3+:
var sql = ((dynamic)flooringStoresProducts).Sql;
That will give you the SQL that was generated.
A way to see query after parameters are applied?
There's no guarantee that there is such a thing as "the query with the parameters applied". I would hope that a driver would simply send down the command as SQL and the parameters in an appropriate form to represent each value. Why go to the bother of escaping values etc, only for the query processor to unescape them and parse them at the other side? It's more efficient and less risky to just pass the data in a binary format of some description.
You should regard it as some code (the SQL) which uses some data (the parameters) and keep the two concepts very separate in your mind. If you need to log what's going on, I would log it as the parameterized SQL and the parameter values separately.
How to get the executing SQL query from a table adapter?
You can use this workaround with replace ParameterName with its value
but it not good because you need to manage value formatting by your own, and it can be slow
and you will need to get parameters after Insert is executed
code:
var usersTableAdapter = new testDataSetTableAdapters.usersTableAdapter();
usersTableAdapter.Insert(4, "home", "origin", "email@host.com", "realname", "spec", 1, "username", "usernick", "whereform");
var cmd = usersTableAdapter.Adapter.InsertCommand;
var text = cmd.CommandText;
var sb = new StringBuilder(text);
foreach (SqlParameter cmdParam in cmd.Parameters)
{
if (cmdParam.Value is string)
sb.Replace(cmdParam.ParameterName, string.Format("'{0}'", cmdParam.Value));
else
sb.Replace(cmdParam.ParameterName, cmdParam.Value.ToString());
}
Console.WriteLine(sb.ToString());
Related Topics
Show a Form Without Stealing Focus
Reading Email Using Pop3 in C#
Cannot Delete Directory with Directory.Delete(Path, True)
Why Is It Bad to Use an Iteration Variable in a Lambda Expression
Getting the Size of a Field in Bytes with C#
Default Visibility for C# Classes and Members (Fields, Methods, etc.)
Reading PDF Content Using Itextsharp in C#
ASP.NET MVC Binding to a Dictionary
How to Protect My .Net Assemblies from Decompilation
Captured Closure (Loop Variable) in C# 5.0
Reading a C/C++ Data Structure in C# from a Byte Array
Get a Screenshot of a Specific Application
Compiling/Executing a C# Source File in Command Prompt
Can't Get Czech Characters While Generating a PDF
How to Know If a Process Is Running
"The Remote Certificate Is Invalid According to the Validation Procedure." Using Gmail Smtp Server
Does Using "New" on a Struct Allocate It on the Heap or Stack
How to Close a Login Form and Show the Main Form Without My Application Closing