How to capture SQL with replaced parameters? (.NET, SqlCommand)

Question

If there is an easy way to get a complete SQL statement after changing parameters? Ie, I want to save the entire SQL log file that this program runs.

Or, if I want to do this, I just want to get rid of the parameters and execute the whole request in the old school uniform in one big line?

A simple example: I want to capture the output:

SELECT subcatId FROM EnrollmentSubCategory WHERE catid = 1

.. from this code:

Dim subCatSQL As String = "SELECT subcatId FROM EnrollmentSubCategory WHERE catid = @catId" Dim connectionString As String = "X" Dim conn As New SqlConnection(connectionString) If conn.State = ConnectionState.Closed Then conn.Open() End If Dim cmd As New SqlCommand(subCatSQL, conn) With cmd .Parameters.Add(New SqlParameter("@catId", SqlDbType.Int, 1)) End With Console.WriteLine("Before: " + cmd.CommandText) cmd.Prepare() Console.WriteLine("After: " + cmd.CommandText) 

I assumed that Prepare () will do the replacements, but apparently not.

Thoughts? Advice? Thanks in advance.

Answer 1

No, .Prepare does not do this, and actually does nothing in Command. Parameter values ​​are never replaced on the actual command line. It is sent to the database separately, which is good for several reasons:

• This allows sqlserver (or other dbs) to cache the query plan for your query and reuse it next time. When another line of command text is sent to db, the database must develop a plan every time.
• By sending parameters to db, divided into parts, there is protection against attacks such as sql injection.

If you are using a really old database (sql server 7?), .Prepare() not required and actually does nothing for you. This used to be useful since it collected the request on the server, but now it is done for you automatically. I have not used .Prepare() for a long time.

Hmmmmm. If you look here, it seems that .Prepare() does something for you anyway: if any values ​​are greater than the specified parameter length, then .Prepare () truncates the value, so that when you execute, you will not get an error and a request will be successful. cool.

Answer 2

Usually I add a utility function to my projects, which, given the DbCommand object (the parent class OracleCommand, SqlCommand, etc.), will register the request and parameter values. It might look like this:

 Public Shared Sub LogQuery(ByRef cmd As DbCommand) If cmd.CommandText Is Nothing Or cmd.CommandText.Length = 0 Then Return End If Dim logFile As CLog = New CLog("sql.log") Dim msg As New StringBuilder msg.AppendLine("*** Query ***") msg.AppendLine(cmd.CommandText) msg.AppendLine("*** End Query ***") msg.AppendLine("*** Parameters ***") For Each p As DbParameter In cmd.Parameters msg.AppendLine(String.Format("{0}: {1}", p.ParameterName, p.Value.ToString())) Next msg.AppendLine("*** End Parameters ***") logFile.WriteLog(msg.ToString() & System.Environment.NewLine) End Sub 

While I was writing this, it just seemed to me that instead of writing the parameter values ​​separately, you could create String.Replace () ing a little to substitute the parameter values ​​into the query and then put it into it:

 For Each p As DbParameter In cmd.Parameters msg = msg.Replace(p.ParameterName, p.Value.ToString()) Next 

Answer 3

Parameters are not replaced. Sql as is, and the parameters themselves are passed as parameters to the sp_executesql system stored in proc

Answer 4

What is sent to SQL Server is not a variable being replaced by a value, but a prepared statement, so it can be reused

Open the profiler in SQL Server and you will see that it is not SELECT subcatId FROM EnrollmentSubCategory WHERE catid = 1

Answer 5

It was recently asked here . As other answers indicate, parameters are sent out of range.

You probably want to use the profiler for capture if you want to do this on the server, and you can create a trace and use it as a start task .

Answer 6

You can create a wrapper around the System.Data.SqlClient Provider (* Ex. Provider registered in the configuration file as ... * providerName="System.Data.SqlClient" ). In fact, the interception proxy server, you would have access to all the information passing through the Provider, and you could filter out what you need, fill out and / or enrich it and send it to the logs. This is a bit more advanced, but it opens the door to capturing a range of information and can be inserted / replaced / deleted as a separate level of problems.

Answer 7

Try CommandAsSql NuGet