Retrieve SqlCommand/OleDbCommand query after inserting parameters?

Question

Is there any way to access the prepared statement as sent to the SQL server?

            Dim params(1) As OleDb.OleDbParameter
            params(0) = New OleDb.OleDbParameter("@ID", OleDb.OleDbType.VarChar, 50)
            params(0).Value = guiItemsGridView.Rows(e.RowIndex).Cells("ID").Value
            params(1) = New OleDb.OleDbParameter("@SavedValue", OleDb.OleDbType.VarChar, 50)
            params(1).Value = guiItemsGridView.Rows(e.RowIndex).Cells("Saved Value").Value

            Dim QueryString As String = "UPDATE Items SET [Saved Value]=@SavedValue WHERE ID=@ID"
            myDatabase.objAdapter.UpdateCommand = New OleDb.OleDbCommand(QueryString, DatabaseConnection)

            myDatabase.objAdapter.UpdateCommand.Parameters.AddRange(params)
            myDatabase.objAdapter.UpdateCommand.Prepare()

            Debug.Print(myDatabase.objAdapter.UpdateCommand.CommandText)

I'm looking for a way to access the prepared query after the parameters have been added. After preparing the command, CommandText still contains the base query UPDATE Items SET [Saved Value]=@SavedValue WHERE ID=@ID even though the parameters have been passed.

I would find it useful to write a log of queries preformed by this chunk of code for debugging other areas of this application.

Greg Buehler · Accepted Answer · 2009-12-30 15:19:25Z

1

I never found a way to log the query using the parameters functionality. I ended up reverting to creating the query in a string, then consuming said string:

QueryString = String.Format("UPDATE Items SET [Saved Value]={0} WHERE ID={1}", SavedValue, ID)

I then consumed that string for both the query and logging functions.

answered Dec 30, 2009 at 15:19

Greg Buehler

3,8943 gold badges34 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

slm · Accepted Answer · 2013-04-09 14:56:42Z

1

I needed something like that. I've built a method that can do the trick, it's not perfect, but it does help:

VB .NET:

Public Sub LogSQL(ByVal cmd As OleDb.OleDbCommand)
    Dim pars As String = ""
    If cmd.Parameters.Count > 0 Then
        pars = " ["
        Dim a As Integer
        For a = 0 To cmd.Parameters.Count - 1
            pars = pars + cmd.Parameters(a).Value.ToString + ","
        Next
        pars = Left(pars, Len(pars) - 1) + "]"
    End If
    Log("SQL=" + cmd.CommandText + pars, LogNivel.INFO)
End Sub

This gives something like:

SQL=SELECT f1,f2 FROM table WHERE fx = ? AND fy = ? [20,35]

edited Apr 9, 2013 at 14:56

slm

16.6k13 gold badges119 silver badges131 bronze badges

answered Apr 9, 2013 at 14:36

ljeremias

112 bronze badges

Comments

Dostee · Accepted Answer · 2009-12-15 23:28:45Z

I tried to search around for a while and couldn't find to use that library to get a sql statement returned that had the parameters inserted. The best I was able to come up with was the SQL Server Profiler in sql server. If you turn it on and check the option SQL:StmtCompleted under the TSQL menu and SP:StmtCompleted under the Stored Procedures menu you will see event textdata columns like this:

SQL:StmtCompleted

EXEC    @return_value = [dbo].[ap_test]
        @User_ID = N'testuser'

SP:Completed

SELECT *
FROM Table1
WHERE User_ID = @User_ID

Since you were looking for a way to log your queries and their respective input parameters this might be useful- however, it would definitely be better if UpdateCommand.CommandText captured the inserted parameters. I will continue to see if a there is a solution out there as i have wanted this functionality several times myself.

Dmitry · Accepted Answer · 2020-09-24 23:21:04Z

0

string commandString = UpdateCommand.CommandText;
foreach (OleDbParameter parameter in UpdateCommand.Parameters)
    commandString = commandString.Replace("@" + parameter.ParameterName.ToString(), parameter.Value.ToString());

answered Sep 24, 2020 at 23:21

Dmitry

11 bronze badge

1 Comment

Stefan Over a year ago

To improve your answer try adding some explanation.

Collectives™ on Stack Overflow

Retrieve SqlCommand/OleDbCommand query after inserting parameters?

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related