Display statement of SelectCommand

[davy_g]

Hi,

I use a selfwritten logmodule which logs data in a textfile. I also want to log the selects, inserts, updates etc which are used in the SelectCommand, UpdateCommand etc.

Is there a way where I can fetch this command in a variable? Maybe even with the parameters it's using?
In this way it will be easier to know what is wrong based on the logfile.

Below is the code I mean:

        Private Sub InitCommandCollection()
            Me._commandCollection = New System.Data.OleDb.OleDbCommand(0) {}
            Me._commandCollection(0) = New System.Data.OleDb.OleDbCommand
            Me._commandCollection(0).Connection = Me.Connection
            Me._commandCollection(0).CommandText = "SELECT     Id, Datum, Dienst, Uren, Norm"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.VisualBasic.ChrW(10)&"FROM         Uren"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.VisualBasic.ChrW(10)&"WHERE    Datum >= ?"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.VisualBasic.ChrW(10)&"AND         Datum < ?"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.VisualBasic.ChrW(10)&"ORDER BY Datum"
            Me._commandCollection(0).CommandType = System.Data.CommandType.Text
            Me._commandCollection(0).Parameters.Add(New System.Data.OleDb.OleDbParameter("Datum", System.Data.OleDb.OleDbType.[Date], 0, System.Data.ParameterDirection.Input, CType(0,Byte), CType(0,Byte), "Datum", System.Data.DataRowVersion.Current, false, Nothing))
            Me._commandCollection(0).Parameters.Add(New System.Data.OleDb.OleDbParameter("Datum1", System.Data.OleDb.OleDbType.[Date], 0, System.Data.ParameterDirection.Input, CType(0,Byte), CType(0,Byte), "Datum", System.Data.DataRowVersion.Current, false, Nothing))
        End Sub

Ripped it out of the dataset.designer.vb.

 

[davy_g]

Well I solved it but I do not know if this is the way to go.

I've set following command below the code, which I set here from dataset.designer.vb:

            Logfile_write(Me._commandCollection(0).CommandText)

logfile_write is just the name of my sub in my module.
In my normal code I already had my parameters ofcourse assigned to a variable so I could log it also.

My logfile now looks like:

18:14:41 SELECT Id, Datum, Dienst, Uren, Norm FROM Uren WHERE Datum >= ? AND Datum < ? ORDER BY Datum
18:14:45 Executed using #2007-01-01#, #2007-02-01#

Pretty cool actually

 

[davy_g]

It worked but only for a short time. Apparantly the file dataset.designer.vb was regenerated so my changes are gone.

Can't I make these statements public so I can use them in my program?

I've read something about partial classes, is that something I can solve my problem with? If yes, how do I do that?

 

[cjard]

There are no events fired when a command executes, but the most recently used select command is retained in the .SelectCommand of the relevant dataadapter that did the work..

i.e. you call FillByDatum1

and that sub does this:

Me._adapter.SelectComand = _commandCollection(0)

...blah blah fill parameters, run query etc


When it is done running, .SelectCommand is still set to what was run so you should be able to:

Right click dataset designer surface,
choose View Code

write:

Public Partial Class MyWhateverTableAdapter

  Public Sub LogMostRecentlyUsedSQL()
    Dim sb as New StringBuilder
    sb.Append(Me._adapter.SelectCommand.CommandText)

    For Each parameter in ...blah...command.Parameters
     sb.AppendFormat(", {0}={1]}", parameter.Name, parameter.Value)
    Next parameter
  
    LogWrite(sb.ToString())
  End Sub
End Class

Or you can maybe inherit the tableadapter, and put a log in each fill:

Class MyTA Inherits TheTableAdapter

   Public Overrides Sub FillByDatum1(...)
    LogWrite("FilLByDatum1 called with args" & arg1 & arg2..) 
    MyBase.FillByDatum1(...)
   End Sub

 
End class

Then use your inherited one