I'm looking for an easy way to see what SQL llblgen generates.

I'm using SQL-server, but I cannot use profiler because you have to be sysadmin for that. Anyone knows how to use SQL-server profiler with no sysadmin permissions?

If you're using adapter, you can use the sqllogger DataAccessAdapter template from the 3rd party section on the website (customer area). Using that derived DataAccessAdapter you can log any sql statement executed and examine it.

I know I missed the deadline for new stuff, but what would really help me (and would also be something I could use to "sell" LLBLGen to fellow colleagues) would be a logging feature built into LLBLGen. Here what I want:

1. Ability to log SQL statements for both all databases (especially the "big 2").

2. Ability to be able to enable/disable this via the config file. That way, its disabled when we ship the product, but if a client has a remote issue then we can tell them to turn it back on to see what the problem is (a lot of our clients like to play with the database!!)

If I get chance, I'll play with the 3rd party SQL Server logger and see what I can do....

MattWoberts wrote:

I know I missed the deadline for new stuff, but what would really help me (and would also be something I could use to "sell" LLBLGen to fellow colleagues) would be a logging feature built into LLBLGen. Here what I want:

1. Ability to log SQL statements for both all databases (especially the "big 2").

2. Ability to be able to enable/disable this via the config file. That way, its disabled when we ship the product, but if a client has a remote issue then we can tell them to turn it back on to see what the problem is (a lot of our clients like to play with the database!!)

If I get chance, I'll play with the 3rd party SQL Server logger and see what I can do....

I hope to cram this in for the upgrade which goes hopefully in beta this week. I have to read up into tracelisteners a bit though, as it's completely clear to me if I can just log to a tracer and it is automatically picked up if a tracelistener subscribes.

To state the obvious such functionality (logging of generated SQL) would really help me also.

Otis wrote:

I hope to cram this in for the upgrade which goes hopefully in beta this week. I have to read up into tracelisteners a bit though, as it's completely clear to me if I can just log to a tracer and it is automatically picked up if a tracelistener subscribes.

That would be really cool! Quite often I get people saying "but I just want to see the SQL its generating", so this will be an excellent debugging aid !

I second the idea... Also like to know if it is going to be available both in Self servicing and adapter also. Thanks.

Adapter has already a way, selfservicing hasn't. I'll try to add logging to the DQE's, so it's equal for all templates.

Ok, I've now a traceswitch enabled in the SqlServer DQE of 1.0.2004.2, I added 4 lines to my .config file to enable the tracer level to 4 (verbose). All of a sudden in VS.NET, I see the stuff I produce in the DQE:

------ Test started: Assembly: UnitTests.TestsLibrary.Adapter.dll ------

Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LLBLGenProUnitTest].[dbo].[Address].[AddressID] AS [AddressId],[LLBLGenProUnitTest].[dbo].[Address].[StreetName] AS [StreetName],[LLBLGenProUnitTest].[dbo].[Address].[HouseNumber] AS [HouseNumber],[LLBLGenProUnitTest].[dbo].[Address].[Zipcode] AS [Zipcode],[LLBLGenProUnitTest].[dbo].[Address].[City] AS [City],[LLBLGenProUnitTest].[dbo].[Address].[Country] AS [Country],[LLBLGenProUnitTest].[dbo].[Address].[TestRunID] AS [TestRunId] FROM [LLBLGenProUnitTest].[dbo].[Address] WHERE ( [LLBLGenProUnitTest].[dbo].[Address].[AddressID] = @AddressId1)
    Parameter: @AddressId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2676. 
Method Exit: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LLBLGenProUnitTest].[dbo].[Address].[AddressID] AS [AddressId],[LLBLGenProUnitTest].[dbo].[Address].[StreetName] AS [StreetName],[LLBLGenProUnitTest].[dbo].[Address].[HouseNumber] AS [HouseNumber],[LLBLGenProUnitTest].[dbo].[Address].[Zipcode] AS [Zipcode],[LLBLGenProUnitTest].[dbo].[Address].[City] AS [City],[LLBLGenProUnitTest].[dbo].[Address].[Country] AS [Country],[LLBLGenProUnitTest].[dbo].[Address].[TestRunID] AS [TestRunId] FROM [LLBLGenProUnitTest].[dbo].[Address] WHERE ( [LLBLGenProUnitTest].[dbo].[Address].[AddressID] = @AddressId1)
    Parameter: @AddressId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2677. 
Method Exit: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LLBLGenProUnitTest].[dbo].[Customer].[CustomerID] AS [CustomerId],[LLBLGenProUnitTest].[dbo].[Customer].[CompanyName] AS [CompanyName],[LLBLGenProUnitTest].[dbo].[Customer].[CustomerSince] AS [CustomerSince],[LLBLGenProUnitTest].[dbo].[Customer].[ContactPerson] AS [ContactPerson],[LLBLGenProUnitTest].[dbo].[Customer].[CompanyEmailAddress] AS [CompanyEmailAddress],[LLBLGenProUnitTest].[dbo].[Customer].[VisitingAddressID] AS [VisitingAddressId],[LLBLGenProUnitTest].[dbo].[Customer].[BillingAddressID] AS [BillingAddressId],[LLBLGenProUnitTest].[dbo].[Customer].[TestRunID] AS [TestRunId] FROM [LLBLGenProUnitTest].[dbo].[Customer] WHERE ( [LLBLGenProUnitTest].[dbo].[Customer].[CustomerID] = @CustomerId1)
    Parameter: @CustomerId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1659. 
Method Exit: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LLBLGenProUnitTest].[dbo].[Address].[AddressID] AS [AddressId],[LLBLGenProUnitTest].[dbo].[Address].[StreetName] AS [StreetName],[LLBLGenProUnitTest].[dbo].[Address].[HouseNumber] AS [HouseNumber],[LLBLGenProUnitTest].[dbo].[Address].[Zipcode] AS [Zipcode],[LLBLGenProUnitTest].[dbo].[Address].[City] AS [City],[LLBLGenProUnitTest].[dbo].[Address].[Country] AS [Country],[LLBLGenProUnitTest].[dbo].[Address].[TestRunID] AS [TestRunId] FROM [LLBLGenProUnitTest].[dbo].[Address] WHERE ( [LLBLGenProUnitTest].[dbo].[Address].[AddressID] = @AddressId1)
    Parameter: @AddressId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2678. 
Method Exit: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LLBLGenProUnitTest].[dbo].[Address].[AddressID] AS [AddressId],[LLBLGenProUnitTest].[dbo].[Address].[StreetName] AS [StreetName],[LLBLGenProUnitTest].[dbo].[Address].[HouseNumber] AS [HouseNumber],[LLBLGenProUnitTest].[dbo].[Address].[Zipcode] AS [Zipcode],[LLBLGenProUnitTest].[dbo].[Address].[City] AS [City],[LLBLGenProUnitTest].[dbo].[Address].[Country] AS [Country],[LLBLGenProUnitTest].[dbo].[Address].[TestRunID] AS [TestRunId] FROM [LLBLGenProUnitTest].[dbo].[Address] WHERE ( [LLBLGenProUnitTest].[dbo].[Address].[AddressID] = @AddressId1)
    Parameter: @AddressId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2679. 
Method Exit: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LLBLGenProUnitTest].[dbo].[Customer].[CustomerID] AS [CustomerId],[LLBLGenProUnitTest].[dbo].[Customer].[CompanyName] AS [CompanyName],[LLBLGenProUnitTest].[dbo].[Customer].[CustomerSince] AS [CustomerSince],[LLBLGenProUnitTest].[dbo].[Customer].[ContactPerson] AS [ContactPerson],[LLBLGenProUnitTest].[dbo].[Customer].[CompanyEmailAddress] AS [CompanyEmailAddress],[LLBLGenProUnitTest].[dbo].[Customer].[VisitingAddressID] AS [VisitingAddressId],[LLBLGenProUnitTest].[dbo].[Customer].[BillingAddressID] AS [BillingAddressId],[LLBLGenProUnitTest].[dbo].[Customer].[TestRunID] AS [TestRunId] FROM [LLBLGenProUnitTest].[dbo].[Customer] WHERE ( [LLBLGenProUnitTest].[dbo].[Customer].[CustomerID] = @CustomerId1)
    Parameter: @CustomerId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1660. 
Method Exit: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LLBLGenProUnitTest].[dbo].[Customer].[CustomerID] AS [CustomerId],[LLBLGenProUnitTest].[dbo].[Customer].[CompanyName] AS [CompanyName],[LLBLGenProUnitTest].[dbo].[Customer].[CustomerSince] AS [CustomerSince],[LLBLGenProUnitTest].[dbo].[Customer].[ContactPerson] AS [ContactPerson],[LLBLGenProUnitTest].[dbo].[Customer].[CompanyEmailAddress] AS [CompanyEmailAddress],[LLBLGenProUnitTest].[dbo].[Customer].[VisitingAddressID] AS [VisitingAddressId],[LLBLGenProUnitTest].[dbo].[Customer].[BillingAddressID] AS [BillingAddressId],[LLBLGenProUnitTest].[dbo].[Customer].[TestRunID] AS [TestRunId] FROM (( [LLBLGenProUnitTest].[dbo].[Address] [AV] INNER JOIN [LLBLGenProUnitTest].[dbo].[Customer] ON  [AV].[AddressID]=[LLBLGenProUnitTest].[dbo].[Customer].[VisitingAddressID]) INNER JOIN [LLBLGenProUnitTest].[dbo].[Address] [AB] ON  [AB].[AddressID]=[LLBLGenProUnitTest].[dbo].[Customer].[BillingAddressID]) WHERE ( [AV].[StreetName] = @StreetName1 And [AB].[StreetName] = @StreetName2)
    Parameter: @StreetName1 : AnsiString. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: Zeilstraat. 
    Parameter: @StreetName2 : AnsiString. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: Ankerstraat. 
Method Exit: CreateSelectDQ

Because the default tracelistener is the output window in vs.net, but you can define your own tracelistener.

I am blown away by the power of this, and how easy it is to setup. This is great, I'll add tracer code throughout the runtime libraries so calls can be traced into the code with ease and queries can be logged very easily.

Its cool when you find something that works well

Looking forward to the finished article

MattWoberts wrote:

Its cool when you find something that works well

Looking forward to the finished article

Working on it... . But I'm looking as forward to the 'done!' line as you

Ok, I have now tracing implemented in the runtime libraries and in the DQE's. I've defined switches for each DQE, and 3 switches for the ORMSupportClasses: general, statemanagement and persistenceexecution.

I already managed to find several redundant calls in the selfservicing code, for example way too many times calling Validate().

By using the switches you can tweak how verbose you want the output. Statemanagement for example in selfservicing is very verbose, for example if you're fetching a prefetch path with a 1000 rows, you'll get a lot of syncing logs , ah well, it's for troubleshooting anyway. As there are different switches, a developer can switch on just DQE query reporting for example, or just method enter/exit logs.

I still am amazed why so little people talk about this feature of .NET as IMHO this is one of the killer features of .NET and I have never heard of it in the past 3 years I'm now developing .NET code.

I haven't added a set of trace messages and switches to the generated code yet. It's a lot of work, and I first want to know if people want tracing in the generated code as well. But I assume once people start using the traces the requests for particular trace messages will be popping up soon enough.

The level of information that's provided is massive if you turn on everything. Example:

...
Method Enter: DataAccessAdapterBase.SaveEntity(4)
Active Entity Description: 
    Entity: UnitTests.Dal.Adapter.EntityClasses.CustomerEntity. ObjectID: 5d746321-8c99-49dd-87cf-f2952ec9bc8b
    PrimaryKey field: CustomerId. Type: System.Int32. Value: 0
Method Enter: DataAccessAdapterBase.SaveEntity(4)
Active Entity Description: 
    Entity: UnitTests.Dal.Adapter.EntityClasses.AddressEntity. ObjectID: c72a772e-6d94-42bb-b3d4-e6aa89ce2425
    PrimaryKey field: AddressId. Type: System.Int32. Value: 0
Method Enter: EntityBase2.Validate
Active Entity Description: 
    Entity: UnitTests.Dal.Adapter.EntityClasses.AddressEntity. ObjectID: c72a772e-6d94-42bb-b3d4-e6aa89ce2425
    PrimaryKey field: AddressId. Type: System.Int32. Value: 0
Entity Validation Result: True
Method Exit: EntityBase2.Validate
Method Enter: CreateInsertDQ
Generated Sql query: 
    Query: INSERT INTO [LLBLGenProUnitTest].[dbo].[Address] ([StreetName],[HouseNumber],[Zipcode],[City],[Country],[TestRunID]) VALUES (@StreetName,@HouseNumber,@Zipcode,@City,@Country,@TestRunId);SELECT @AddressId=SCOPE_IDENTITY()
    Parameter: @AddressId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: 0. 
    Parameter: @StreetName : AnsiString. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: Zeilstraat. 
    Parameter: @HouseNumber : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 99. 
    Parameter: @Zipcode : AnsiStringFixedLength. Length: 10. Precision: 0. Scale: 0. Direction: Input. Value: 2586 RB. 
    Parameter: @City : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: Den Haag. 
    Parameter: @Country : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: Nederland. 
    Parameter: @TestRunId : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: f3356b34-5217-4796-8fc6-06570a1da28d. 

Method Exit: CreateInsertDQ
Method Enter: DataAccessAdapterBase.ExecuteActionQuery
Method Enter: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.ExecuteActionQuery
Method Enter: Query.ReflectOutputValuesInRelatedFields
    Syncing field AddressId with parameter @AddressId.
Method Exit: Query.ReflectOutputValuesInRelatedFields
Method Enter: DataAccessAdapterBase.FetchEntity(1)
Active Entity Description: 
    Entity: UnitTests.Dal.Adapter.EntityClasses.AddressEntity. ObjectID: c72a772e-6d94-42bb-b3d4-e6aa89ce2425
    PrimaryKey field: AddressId. Type: System.Int32. Value: 2729

Method Enter: DataAccessAdapterBase.FetchEntityUsingFilter(3)
Method Enter: CreateSelectDQ (Paging)
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LLBLGenProUnitTest].[dbo].[Address].[AddressID] AS [AddressId],[LLBLGenProUnitTest].[dbo].[Address].[StreetName] AS [StreetName],[LLBLGenProUnitTest].[dbo].[Address].[HouseNumber] AS [HouseNumber],[LLBLGenProUnitTest].[dbo].[Address].[Zipcode] AS [Zipcode],[LLBLGenProUnitTest].[dbo].[Address].[City] AS [City],[LLBLGenProUnitTest].[dbo].[Address].[Country] AS [Country],[LLBLGenProUnitTest].[dbo].[Address].[TestRunID] AS [TestRunId] FROM [LLBLGenProUnitTest].[dbo].[Address] WHERE ( [LLBLGenProUnitTest].[dbo].[Address].[AddressID] = @AddressId1)
    Parameter: @AddressId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2729. 
Method Exit: CreateSelectDQ
Method Exit: CreateSelectDQ (Paging): no paging.
Method Enter: DataAccessAdapterBase.ExecuteSingleRowRetrievalQuery
Method Enter: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.OpenConnection
Method Enter: DataAccessAdapterBase.FetchOneRow
Method Enter: DataAccessAdapterBase.ReadRowIntoFields
Method Exit: DataAccessAdapterBase.ReadRowIntoFields
Method Exit: DataAccessAdapterBase.FetchOneRow
Method Exit: DataAccessAdapterBase.ExecuteSingleRowRetrievalQuery
Method Exit: DataAccessAdapterBase.FetchEntityUsingFilter
Method Exit: DataAccessAdapterBase.FetchEntity(1)
Method Enter: EntityBase2.SyncFKFields
Active Entity Description: 
    Entity: UnitTests.Dal.Adapter.EntityClasses.CustomerEntity. ObjectID: 5d746321-8c99-49dd-87cf-f2952ec9bc8b
    PrimaryKey field: CustomerId. Type: System.Int32. Value: 0
Data Supplying Entity Description: 
    Entity: UnitTests.Dal.Adapter.EntityClasses.AddressEntity. ObjectID: 5d746321-8c99-49dd-87cf-f2952ec9bc8b
    PrimaryKey field: AddressId. Type: System.Int32. Value: 2729
    Syncing FK field VisitingAddressId with PK field AddressId
Method Exit: EntityBase2.SyncFKFields
Method Exit: DataAccessAdapterBase.SaveEntity(4)
Method Enter: DataAccessAdapterBase.SaveEntity(4)
...

Needless to say, I'm very very happy with it, and it's a massive gain for all customers.

Tracing is setup easily in the .config file of your app:

...
    <system.diagnostics>
        <switches>
            <add name="SqlServerDQE" value="4" />
            <add name="AccessDQE" value="4" />
            <add name="OracleDQE" value="0" />
            <add name="FirebirdDQE" value="0" />
            <add name="MySqlDQE" value="0" />
            <add name="DB2DQE" value="0" />
            <add name="ORMGeneral" value="4" />
            <add name="ORMStateManagement" value="4" />
            <add name="ORMPersistenceExecution" value="4" />
        </switches>
    </system.diagnostics>
...

I don't define any trace listeners there, but you can by just adding a few lines to the system.diagnostics section, catch all reported messages by the textwriter listener, which writes it all to the file you specify. Or define your own for example. If you don't define any trace listeners, you'll see the output of the app you're running in the vs.net output window (if you've started the app in vs.net using (cntrl-)f5.

Tracing is available in SelfServicing and Adapter.

To say that's sweeeeet doesn't do it justice!

I was inspired and started looking into trace listeners myself - nice!! Previously we went down the path of implementing our own logging, because I didnt know about this stuff

I can't wait to see this all in action!!!!

As to how much we want in the generated code, I think that what you added so far is extremely useful, and we will have to see how we get on to determine if we need extra logging.

Scott

MattWoberts wrote:

I was inspired and started looking into trace listeners myself - nice!! Previously we went down the path of implementing our own logging, because I didnt know about this stuff

heh I had the same experience. It's so easy to implement and has so much value. Of course, you can go for log4net, which offers more fine grained message control, but for debugging purposes, the tracing is great.

As to how much we want in the generated code, I think that what you added so far is extremely useful, and we will have to see how we get on to determine if we need extra logging.

Yeah I think it's pretty sufficient what's implemented now. The necessity for having tracing in the generated code can come from the need to see the complete path a call follows, however that's not that important IMHO, only for me, to see if I made a mistaken or not.

The SQL feedback is extremely valuable and should have been in the library a long time ago.

General question, but will you be able to turn the logging function on for a specific function? For example, if I have the following

public void SomeFunction() { // Do Something }

I would like to enable the traceing for just this function

public void SomeFunction() { Trace.Enable; // Do Something }

This way I can trace a single function. Sorry I don't have much experience with tracing.

Thanks

No that won't be possible, you switch on / off tracing of the lib routines. Though you can add your own trace calls to your methods and add an enter / exit Trace call, which allows you to follow what happens between the enter of the method, the runtime lib calls and the exit of the method.

Otis wrote:

Of course, you can go for log4net, which offers more fine grained message control, but for debugging purposes, the tracing is great.

We already use log4net extensively. Is there a way we could work together so that as we add log4net statements to the source code, you could include those in LLBL? Maybe there could be a compiler directive to build the libs with either dotnet trace listeners or with log4net?

We use the following log4net style in our code.

[NonSerialized] private static readonly ILog logger = LogManager.GetLogger(typeof(UserEntity));

catch (Exception ex)
{
    #region Logging
    if (logger.IsErrorEnabled)
    {
        logger.Error(ex);
    }
    #endregion
    //do other normal stuff...
}

I think Log4net supports trace listeners as well, so that shouldn't be a problem, or doesn't it support for trace listeners? (i.e.: I thought log4net could subscribe to output from tracers)

I opted for the .net tracers, which don't require an extra library and can be enabled by just adding a few lines to the config file to get output to a textfile for example, so you don't have to update a running application with new dlls which are special build with log4net. Especially the non reliance on a 3rd party library for this is important, as it should be as easy as possible to switch on tracing for troubleshooting for example.

Config files are EVIL!!!

God I hate them, nothing like putting stuff in a file that any joe blow can change with notepad, then complain it doesn't work, or it performs badly etc.

For server side stuff I don't have anything against them, but the never ending trend to put stuff there just drive me friggin nuts, even the config string for LLBLGen, the default constructor using it drive me nuts as this exposed a surface area for our 2000 some users to screw up.

Microsoft Application blocks are the same way everything running in a config file, the config file soon becomes more complicated than the actual application and exposes huge sections of your app to user stupidity.

Just my 2cents. =)

John

heh I hear ya

The thing is though that sometimes, they can be ok . For example in a web-app which doesn't behave as expected. To simply add some lines to the config file to make it spit out log information is very helpful.

How to log Generated SQL to a FILE ON HARDDISK in LLBLGen version 2.

Regards

Kaksss

If you are using SQL Server, you may use the SQL Profiler to log in the activities you want.

I would like to save it to Text file so easy to read and look.

Regards

Kaksss

Then add a tracelistener definition for the textfile tracelistener to the .config file of your application. Please check MSDN documentation on TextWriterTraceListener for an example how to do that.