I am using VB.NET and Adapter scenario with SQL Server. I would like to retrieve the generated SQL in the (ASP.NET) application. I understand that I can get this externally by using Profiler but I would like to re-use the actual SQL statement in the application for other purposes.

What I am looking for is exactly what SQL Server gets from the application. An example would be: exec sp_executesql N'SELECT [Northwind].[dbo].[Customers].[CompanyName] AS [CompanyName] ,[Northwind].[dbo].[Orders].[OrderDate] AS [OrderDate] ,[Northwind].[dbo].[Customers].[Country] AS [Country] ,[Northwind].[dbo].[Shippers].[CompanyName] AS [Shipper] FROM [Northwind].[dbo].[Customers] INNER JOIN [Northwind].[dbo].[Orders]
ON Customers.CustomerID = Orders.CustomerID INNER JOIN [Northwind].[dbo].[Shippers]
ON Orders.ShipVia = Shippers.ShipperID WHERE ( [Northwind].[dbo].[Customers].[Country] LIKE @Country) ORDER BY [Northwind].[dbo].[Customers].[CompanyName] ASC' , N'@Country nvarchar(40)', @Country = N'Brazil'

It seems like this should be a built-in property, after all, the DQE creates the SQL so it must know what the final SQL text is.

I have several reasons for wanting the SQL statement. The main reason is to create a dataset used for generating reports (Excel via ComponentOne C1Excel and PDF via Data Dynamics ActiveReports). I don't think I can use the business objects to populate those reports. If you've got another suggestion, I'd be happy to hear it.

I did some searching on getting the generated SQL in the forums and found:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=1159 That got me various pieces in the FROM and WHERE clauses but I want the whole statement.

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3060 Frans talked about overriding the each of the Fetch methods of the DataAccessAdapter but I haven't figured out how to implement that.

Thanks for the support. It is encouraging to see the assistance LLBLGen users are getting.

Joel Reinford Data Management Solutions LLC

jovball wrote:

I am using VB.NET and Adapter scenario with SQL Server. I would like to retrieve the generated SQL in the (ASP.NET) application. I understand that I can get this externally by using Profiler but I would like to re-use the actual SQL statement in the application for other purposes.

What I am looking for is exactly what SQL Server gets from the application. An example would be: exec sp_executesql N'SELECT [Northwind].[dbo].[Customers].[CompanyName] AS [CompanyName] ,[Northwind].[dbo].[Orders].[OrderDate] AS [OrderDate] ,[Northwind].[dbo].[Customers].[Country] AS [Country] ,[Northwind].[dbo].[Shippers].[CompanyName] AS [Shipper] FROM [Northwind].[dbo].[Customers] INNER JOIN [Northwind].[dbo].[Orders]
ON Customers.CustomerID = Orders.CustomerID INNER JOIN [Northwind].[dbo].[Shippers]
ON Orders.ShipVia = Shippers.ShipperID WHERE ( [Northwind].[dbo].[Customers].[Country] LIKE @Country) ORDER BY [Northwind].[dbo].[Customers].[CompanyName] ASC' , N'@Country nvarchar(40)', @Country = N'Brazil'

It seems like this should be a built-in property, after all, the DQE creates the SQL so it must know what the final SQL text is.

Please see the 1.0.2004.2 documentation under 'Troubleshooting and debugging' to enable tracing on various levels. You can enable tracing on the DQE for example in 2 levels, to retrieve the query text, including parameter values, which is generated. If you run your app in debug mode, you'll get the information in the output window of vs.net, otherwise you've to add a tracelistener, but that's pretty easy.

I have several reasons for wanting the SQL statement. The main reason is to create a dataset used for generating reports (Excel via ComponentOne C1Excel and PDF via Data Dynamics ActiveReports). I don't think I can use the business objects to populate those reports. If you've got another suggestion, I'd be happy to hear it.

I'm not sure if activereports can be filled by binding an EntityCollection, Excel gets indeed problematic.

Thanks for the support. It is encouraging to see the assistance LLBLGen users are getting.

My pleasure

Frans:

I am looking to use this in the actual application. As such, won't the listener trace everything in the application at all times (and thus impact performance)? I just need to grab the SQL at certain times.

It seems to me that it shouldn't be that big of a change to add a property for the DataAccessAdapter that would return the entire SQL statement. (Or, it could return the command object although I'd prefer the SQL statement)

My wishlist on this would be to have two additional properties for the DataAccessAdapter. One would be the complete SQL statement as shown in my first post above. The second would be a boolean that would determine whether a query was actually executed. If the flag was true, the SQL statement would be sent on to the database. If the flag was false, the SQL statement would be prepared (and available for use, etc) but not actually executed.

In looking at the source code, I think that if I could override the ExecuteMultiRowRetrievalQuery method, I could get what I want. However, my C# to VB skills aren't that good.

As far as the Excel export goes, I feel like I have that one covered. ComponentOne sells a component that produces true Excel format files (not XML, not HTML tables but actual BIFF .

It doesn't do quite as much as I'd like (surprise there, right? ) but it covers what I really need. It's a good thing to have for ad-hoc reporting. This is sample code for anyone else who has a similar need:

Dim dst As DataSet
Dim dt As DataTable
Dim drw As DataRow
Dim dc As DataColumn
Dim r, c, dtIndex As Integer
Dim header As String
Dim sheet As XLSheet
Dim row As XLRow
Dim cell As XLCell


'create a new workbook
Dim book As New C1XLBook
Dim reportFolder As String = Config.reportOutputFolder
Dim fileName As String = Guid.NewGuid.ToString & ".xls"

'create styles for header, odd and even rows
Dim styleHeader As New XLStyle(book)

With styleHeader
    .Font = New Font("Tahoma", 12, FontStyle.Bold)
    .ForeColor = Color.Black
    .BackColor = Color.Gray
End With

'note that the header is row 1 
'so the first data row will be the even style
Dim styleEven As New XLStyle(book)
With styleEven
    .Font = New Font("Tahoma", 9)
    .ForeColor = Color.Red
End With


Dim styleOdd As New XLStyle(book)
With styleOdd
    .Font = New Font("Tahoma", 9)
    .ForeColor = Color.Blue
End With


For Each dt In dst.Tables
    book.Sheets.Add()
Next

For Each dt In dst.Tables
    dtIndex = dst.Tables.IndexOf(dt)
    sheet = book.Sheets(dtIndex)
    sheet.Name = dt.TableName
    row = sheet.Rows(0)

    'get the column names and create column headers
    For c = 0 To dt.Columns.Count - 1
        header = dt.Columns(c).ColumnName
        cell = sheet(0, c)
        cell.Value = header
        row = sheet.Rows(0)
        row.Style = styleHeader
    Next 'column

    'now load the data starting with row 2 on the worksheet
    For r = 0 To dt.Rows.Count - 1
        drw = dt.Rows(r)
        row = sheet.Rows(r + 1)
        If r Mod 2 = 0 Then
            row.Style = styleEven
        Else
            row.Style = styleOdd
        End If
        For c = 0 To dt.Columns.Count - 1 'colCount
            cell = sheet(r + 1, c)
            If IsDate(drw(c)) Then
                Dim d As Date
                d = Convert.ToDateTime(drw(c))
                cell.Value = String.Format("{0:d}", d)
            Else
                cell.Value = drw(c) '.ToString
            End If
        Next 'column
    Next 'row
Next 'datatable

'save the file
book.Save(reportFolder & fileName)

Joel Reinford Data Management Solutions LLC

jovball wrote:

Frans:

I am looking to use this in the actual application. As such, won't the listener trace everything in the application at all times (and thus impact performance)? I just need to grab the SQL at certain times.

Yes, but that's why it's for troubleshooting in production. In production, you don't want to recompile a complete app, you want to set a config setting and get a trace. Which is exactly what you get. It is slower, but there for troubleshooting. I don't know why you would grab every query in production if it wasn't for troubleshooting.

In development, you can write a small piece of code which runs your routine, or test a particular page/form, while tracing is enabled, thus getting the information you want.

It seems to me that it shouldn't be that big of a change to add a property for the DataAccessAdapter that would return the entire SQL statement. (Or, it could return the command object although I'd prefer the SQL statement)

You can do that yourself if you want to: derive a class from DataAccessAdapter and override OnSaveEntity/OnFetchEntity etc. You'll receive an IActionQuery or IRetrievalQuery object which contains a IDbCommand object with the query. In the 3rd party section there's even a template which does that for you.

Though, for development purposes, the tracing is well suited and for production purposes, it's enough and often preferable, because you can define in the config file where the trace information is send to: event log, file, or your own trace listener.

My wishlist on this would be to have two additional properties for the DataAccessAdapter. One would be the complete SQL statement as shown in my first post above. The second would be a boolean that would determine whether a query was actually executed. If the flag was true, the SQL statement would be sent on to the database. If the flag was false, the SQL statement would be prepared (and available for use, etc) but not actually executed.

Having a flag which forms the SQL query but not executing it won't work, as in a graph save, you need fk/pk syncing, for example with autonumber values being retrieved from the db.

I also fail to see the actual purpose for it. Could you please give me a proper real-world example for this? I know every new feature 'can be handy', but my experience is that if there's no real-world example in which this can be really useful (and existing features fail), the feature will probably never be used.

In looking at the source code, I think that if I could override the ExecuteMultiRowRetrievalQuery method, I could get what I want. However, my C# to VB skills aren't that good.

No, don't override that one. Override OnSaveEntity etc. That is, if you want to have the query being generated. If you want to stop execution, override the Execute... methods, but I recommend you to override only the methods which are added for this purpose: the On.. methods. Some other methods are marked overridable/virtual but that's done for advanced purposes, like adding your own execution core/object fetcher.

Frans:

Operating on the principal that an example is worth much more than explanations, take a look at http://www.train4tech.com/northwind/products.aspx

This is what I am trying to accomplish. It is a basic reporting form that gives a few options and gets back data based on the criteria. LLBLGen is a great tool for these kinds of things. Previously I had constructed a command builder class to do parameterized query building but it was not on the same level as your DQE.

The idea is to give an onscreen preview of the data in a report. If the user is satisfied with the results of their query, they can then choose to download the report in various formats/levels of detail. In my application, these choices are usually in Excel or PDF format, although I also have RTF and others.

The reports are populated by datasets/tables/views, not by business objects. I am using ActiveReports for some of the reports and I think that can use business objects but some of the other reports need a dataset.

The grid/search is accomplished with a Typed View. My goal is to get the SQL statement (or the SQL Command object). when the search is first run. If they choose to get one of the reports, I want to get the statement/command to create the report.

For this example, I sub-classed the DataAccessAdapter and created a public property for the command object. However, the command object isn't available unless I actually execute a Fetch. (This is why I had mentioned overriding the Execute method). So that forces me to run the query twice when I want the report. Once to get the command object and then again with the command object to get the dataset for the report.

I'm open to any suggestions on how to do this another way.

Joel Reinford Data Management Solutions LLC

Frans:

I forgot to add that I was getting the command object by overriding the OnFetch... methods and assigning the value of the selectQuery.Command to a property value in the sub-class. This example was using OnFetchTypedView.

Joel Reinford Data Management Solutions LLC

I see now why you want to have the statement. The thing I wonder about is: after the preview, you have to requery IMHO to get the full report, or am I mistaken? so you have to query for the preview and then again for the full report?

Anyway, I think you can override the Execute*Query methods in your derived class, and in those overrides call the base' method if some flag isn't set. That flag is a boolean you add to your DataAccessAdapter subclass. You then create an instance of that class, set the flag and call the query. Your OnFetch... methods grab the query and it never gets executed. As this is for selects only, you're fine, it won't affect recursive saves etc.

Frans:

Yes, I will have to run the query twice, once to get the grid and again to run the report. At the moment I am running it THREE times. Once to get the "preview" grid (1) . Then if they choose a report, I am running the Fetch... to get the command object (2). The results of that Fetch are discarded. Then I execute the command object (3) to generate the report. I'm trying to combine 2 & 3.

Again, there might be a better way and if so I'd like to know it.

As a side comment. I'm also toying with the possiblity of listing the report parameters on the actual report so that the user/person reading the report would have the criteria that the report was based on. I'd probably do it in the PDF report footer and on a separate sheet for the Excel download.

jovball wrote:

Frans:

Yes, I will have to run the query twice, once to get the grid and again to run the report. At the moment I am running it THREE times. Once to get the "preview" grid (1) . Then if they choose a report, I am running the Fetch... to get the command object (2). The results of that Fetch are discarded. Then I execute the command object (3) to generate the report. I'm trying to combine 2 & 3.

You could of course re-use the values fetched in 2. You fetch a collection there? You can convert a collection into a datatable with a few lines of code: - create the datacolumns using the info in the fields collection of the first entity - per entity, create a new datarow and read every field of that entity into the datarow.

I'm not sure how expensive the query is, but it could be faster to convert it in memory. But with the example I gave in an earlier posting, you can make option 2 not execute on the db.

OK, I'm feeling a bit stupid now. All of the reports I am using will be based on typed views. I had not caught on to the fact that typed views/lists are always returned as datatables and not as a collection. (Correct me if I am wrong about this). As such, I can use the actual output of the FetchTypedView method for my reports.

To summarize, here is what I will be doing to achieve my goals.

1) Retrieve the datatable using FetchTypedView. Use the datatable to either load the datagrid or run the Excel/PDF report.

2 & 3 are optional but I like to do them for report logging/troubleshooting purposes.

2) Create a sub-class for the DataAccessAdapter. Create a property for the command object and override the OnFetchTypedView method to get it.

    
    Private m_cmd As SqlCommand

    Property cmd() As SqlCommand
        Get
            Return m_cmd
        End Get
        Set(ByVal Value As SqlCommand)
            m_cmd = Value
        End Set
    End Property 

    Public Overrides Sub OnFetchTypedView( _
            ByVal selectQuery As IRetrievalQuery, _
            ByVal fieldCollectionToFetch As IEntityFields2, _
            ByVal dataTableToFill As System.data.DataTable _
                )

        cmd = DirectCast(selectQuery.Command, SqlCommand)


    End Sub

3) Use the command object property to show report criteria. I just create a datatable and add the SQL text and the parameters to it.

Any other thoughts are welcome but I'm OK with this one.

Joel Reinford Data Management Solutions LLC

Looks ok