StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Backend SQL Commands in BO

Backend SQL Commands in BO

Ross L. Rooker, Sr. — Fri, 26 Aug 2011 11:46:27 GMT

I know that this command in the programs file will show you the actual command sent to the backend: MicroFour.StrataFrame.Data.DataBasics.DataSources[0].SetDebugOn("C:\\ValetLog.html", true);

What command would I insert in my base bo to get this command when SAVE is clicked. That would get me the INSERTs and UPDATEs. Then what command would I use to get the DELETE. Lastly what method in the BO would these commands be placed.

RE: Backend SQL Commands in BO

Ross L. Rooker, Sr. — Fri, 26 Aug 2011 11:46:27 GMT

In the future if you could somehow allow this to more easily be captured would be great. I have a full Activity Log working now. Just thought it might be cool to capture the actual INSERT, UPDATE or DELETE command issued against the backend. THanks for the quick response.

RE: Backend SQL Commands in BO

Trent L. Taylor — Fri, 26 Aug 2011 11:30:19 GMT

Well, actually, now that I think about it, if you want to capture the same content as the debug mode, you will have to create your own DbDataSourceItem. The easiest thing to do would be to copy over the SqlDbDataSourceItem or Oracle, whichever you are using, and create your own in your project. The method that you will need to place your logic is in the InternalExecuteNonQuery method. This is what it looks like at the moment:

[codesnippet]
''' <summary>
        ''' Executes the given DbCommand with ExecuteNonQuery with debugging.
        ''' </summary>
        Protected Function InternalExecuteNonQuery(ByVal Command As DbCommand, ByVal IsTransactional As Boolean, ByVal TransactionKey As String) As Integer
            If Me._InternalExecuteNonQuery IsNot Nothing Then
                Return Me._InternalExecuteNonQuery(Command, IsTransactional, TransactionKey)
            Else
                '-- Show the debugging window
                Me.DebugCommand(Command)

                '-- Make sure the connection is open
                If Command.Connection.State <> ConnectionState.Open Then
                    Command.Connection.Open()
                End If

                '-- Execute the command
                Dim lnReturn As Integer = Command.ExecuteNonQuery()
                If Not IsTransactional AndAlso Me.ForceConnectionCloseOpen Then
                    Command.Connection.Close()
                    Command.Connection.Open()
                End If
                Return lnReturn
            End If
        End Function
[/codesnippet]

So to do what you are trying to do would require that you copy over the full class (since at the moment this method is not overridable) and then insert your logic in the above method around the DebugCommand logic to get what you are looking for. The reason is that this is the base location that actually builds the Command. When a BO is saved, it uses the QueryInfo class that gets handed down to the DbDataSourceItem (Data Layer) that calls the appropriate Build command to produce the actual DbCommand. This is how a BO can just swap out the back-end and connection logic, otherwise the BO would be hard coded to that data source.

I will see if we can add a potential event in a future build to capture this, but the downside is that an event would be called and could slow this process down. The best way really is to do this at the DbDataSourceItem level. The only other thing that would help is to make all of these methods overridable so that inheritance would work in this case. This much we can do without any issue or performance bottleneck.

RE: Backend SQL Commands in BO

Ross L. Rooker, Sr. — Fri, 26 Aug 2011 11:08:26 GMT

I inserted this into my BaseBO. The problem is that it does not appear as though it is being fired I am not seeing anything in the Console output window during execution.

protected override int ExecuteNonQuery(System.Data.Common.DbCommand CommandToExecute)

{

//-- You can use the CommandToExecute to pull out everything you need at this point. For example, CommandToExecute.CommandText will

// give you the command text and then you can look at the .Parameters collection to get all of the parms.

Console.WriteLine("CommandToExecute= " + CommandToExecute.CommandText.ToString());

//-- Perform the standard logic

return base.ExecuteNonQuery(CommandToExecute);

}

RE: Backend SQL Commands in BO

Trent L. Taylor — Fri, 26 Aug 2011 10:26:49 GMT

Let me see if I understand, you are wanting to capture the actual SQL command that is executed along with the parms, etc. when Save is executed on the BO, correct? If this is the case, then you will want to override the ExecuteNonQuery method in your base BO and it will give you the command that will be executed:

[codesnippet]
''' <summary>
''' Overrides the ExecuteNonQuery() method of the base class.
''' </summary>
Protected Overrides Function ExecuteNonQuery(ByVal CommandToExecute As System.Data.Common.DbCommand) As Integer
    '-- You can use the CommandToExecute to pull out everything you need at this point. For example, CommandToExecute.CommandText will
    '    give you the command text and then you can look at the .Parameters collection to get all of the parms.

    '-- Perform the standard logic
    Return MyBase.ExecuteNonQuery(CommandToExecute)
End Function
[/codesnippet]