Version 1/14.9.2006
[LBG] has an internal tracing mechanism that outputs data to Trace class using standard .net mechanism. To enable various tracing categories you have to switch one various TraceSwitches either at runtime or in config file. Here is an example:
<system.diagnostics> <switches> <add name="SqlServerDQE" value="3" /> <add name="AccessDQE" value="4" /> <add name="OracleDQE" value="4" /> <add name="FirebirdDQE" value="4" /> <add name="MySqlDQE" value="4" /> <add name="DB2DQE" value="4" /> <add name="PostgeSqlDQE" value="4" /> <add name="ORMGeneral" value="0" /> <add name="ORMStateManagement" value="0" /> <add name="ORMPersistenceExecution" value="3" /> </switches> </system.diagnostics>
Perhaps the most important switch is
SqlServerDQE switch that turns on generated sql statements output when its value is 4 (TraceLevel.Verbose). The generated select statement looks like
Generated Sql query: Query: SELECT [Betek2].[dbo].[CountryDetail].[Name] AS [CountryName] FROM [Betek2].[dbo].[CountryDetail] WHERE [Betek2].[dbo].[CountryDetail].[Id] = @Id1 Parameter: @Id1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0.
in Output Debug Window. That's great for checking sql statement and values passed to it. But what if you want to execute the same select directly in Sql Server Management Studio perhaps? The reason might be your curiosity. More valid reason would be to check the generated sql statement as it might behave poorly of badly (or not at all) if it isn't constructed properly. And finding why in a huge statement soon becomes picking a needle in haystack. Anyway, you would have to:
- Copy&paste the query part
- DECLARE and SET each parameter with values that you would copy&paste from each parameter line
- Execute the sql code
In my sample above this doesn't require a lot of work. But what if the select statement grows huge and a ton of parameters is passed (while you can copy&paste sql statement you have to work a lot for creating parameters)? It soon becomes quite annoying to repeat those steps over and over again.
So here is the innovation – trace output the proper sql code you can execute as is against a database. My solution presumes an adapter scenario and a sql server database.
1. Finding proper place to do tracing
After I did some suggestions from Frans and some reflectoring I found a right spot: overriding CreateSelectDQ method in DatabaseSpecific.DataAccessAdapter class. But there is a catch – this class is autogenerated from template code generator and you can't modify it (well, you could modify the template). Fortunately it is a partial class. Did I mention that I really love partial classes? They give you so much freedom. Back to our quest. I created another DataAccessAdapter class in another folder (because you can't have two files with same name in same folder) in DataDBSpecific project.
2. Creating sql statement output method
SqlParameter sqlParam = new SqlParameter(); StringBuilder sb = new StringBuilder(); string value = string.Empty; foreach (IDbDataParameter param in query.Parameters) { if ((param.Value == null) || (param.Value == DBNull.Value)) value = "null"; else { switch (param.DbType) { case DbType.VarNumeric: case DbType.Binary: case DbType.Object: value = "binary lob"; break; case DbType.AnsiStringFixedLength: case DbType.StringFixedLength: case DbType.String: case DbType.AnsiString: value = string.Format("\"{0}\"", param.Value.ToString()); break; case DbType.Boolean: value = (bool)param.Value ? "1" : "0"; break; case DbType.DateTime: value = "'" + ((DateTime)param.Value).ToString("yyyyMMdd HH:mm:ss") + "'"; break; default: value = string.Format(CultureInfo.InvariantCulture, "{0}", param.Value); break; } string type = "Unknown"; try { sqlParam.DbType = param.DbType; type = sqlParam.SqlDbType.ToString(); } catch { } sb.AppendFormat("DECLARE {0} {1}\r\nSET {0}={2}\r\n", param.ParameterName, type, value); } }
Above is first part. It creates a StringBuilder and adds each DECLARE/SET sql statements to it. Note, I use this trick to convert from DbType to SqlDbType. And another trick is to convert SqlDbType to string as it is properly named.
string info; if (query.Command.CommandType == CommandType.StoredProcedure) { StringBuilder spBuilder = new StringBuilder(); for (int i = 0; i < query.Command.Parameters.Count; i++) { if (i > 0) spBuilder.Append(", "); spBuilder.Append(((IDataParameter)query.Command.Parameters[i]).ParameterName); } info = "\r\n\tQuery: Stored procedure call:\r\n"; sb.AppendFormat(null, "{0}({1})\r\n", new object[] { query.Command.CommandText, spBuilder.ToString() }); } else { info = "\r\n\tQuery:\r\n"; string cmdText = query.Command.CommandText; foreach (string delimiter in new string[] { "SELECT", "FROM", "WHERE", "GROUP BY", "HAVING", "INNER JOIN", "LEFT JOIN", "RIGHT JOIN" }) { cmdText = cmdText.Replace(delimiter, "\r\n" + delimiter + "\r\n"); } sb.AppendFormat(null, "{0}\r\n", new object[] { cmdText }); } return info + sb.ToString();
This second part more or less just calls into Query.ToString() internal method that does job for us. It distinguish between stored procedures and select statements. I also took some liberty to roughly format sql statement in foreach loop (I put some keywords in new line). Entire method looks like this:
private string GetQuerySqlServerOutput(IRetrievalQuery query) { SqlParameter sqlParam = new SqlParameter(); StringBuilder sb = new StringBuilder(); string value = string.Empty; foreach (IDbDataParameter param in query.Parameters) { if ((param.Value == null) || (param.Value == DBNull.Value)) value = "null"; else { switch (param.DbType) { case DbType.VarNumeric: case DbType.Binary: case DbType.Object: value = "binary lob"; break; case DbType.AnsiStringFixedLength: case DbType.StringFixedLength: case DbType.String: case DbType.AnsiString: value = string.Format("\"{0}\"", param.Value.ToString()); break; case DbType.Boolean: value = (bool)param.Value ? "1" : "0"; break; case DbType.DateTime: value = "'" + ((DateTime)param.Value).ToString("yyyyMMdd HH:mm:ss") + "'"; break; default: value = string.Format(CultureInfo.InvariantCulture, "{0}", param.Value); break; } string type = "Unknown"; try { sqlParam.DbType = param.DbType; type = sqlParam.SqlDbType.ToString(); } catch { } sb.AppendFormat("DECLARE {0} {1}\r\nSET {0}={2}\r\n", param.ParameterName, type, value); } } string info; if (query.Command.CommandType == CommandType.StoredProcedure) { StringBuilder spBuilder = new StringBuilder(); for (int i = 0; i < query.Command.Parameters.Count; i++) { if (i > 0) spBuilder.Append(", "); spBuilder.Append(((IDataParameter)query.Command.Parameters[i]).ParameterName); } info = "\r\n\tQuery: Stored procedure call:\r\n"; sb.AppendFormat(null, "{0}({1})\r\n", new object[] { query.Command.CommandText, spBuilder.ToString() }); } else { info = "\r\n\tQuery:\r\n"; string cmdText = query.Command.CommandText; foreach (string delimiter in new string[] { "SELECT", "FROM", "WHERE", "GROUP BY", "HAVING", "INNER JOIN", "LEFT JOIN", "RIGHT JOIN" }) { cmdText = cmdText.Replace(delimiter, "\r\n" + delimiter + "\r\n"); } sb.AppendFormat(null, "{0}\r\n", new object[] { cmdText }); } return info + sb.ToString(); }
3. Outputing the string generated by GetQuerySqlServerOutput method
The final step is to output the string GetQuerySqlServerOutput generates for us.
protected override IRetrievalQuery CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, long maxNumberOfItemsToReturn, SD.LLBLGen.Pro.ORMSupportClasses.ISortExpression sortClauses, IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize) { IRetrievalQuery query = base.CreateSelectDQ(fieldsToFetch, persistenceInfoObjects, filter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, pageNumber, pageSize); TraceHelper.WriteIf(DynamicQueryEngineBase.Switch.TraceVerbose, GetQuerySqlServerOutput(query), "SqlServer ready query"); return query; }
This is done by overriding CreateSelectDQ method. First I call the base method and store the result in query variable. Then I call internal TraceHelper.WriteIf method that does output to Trace. The output is done in the same conditions when normal query output is done (see the beginning of the article) so you end up with both debug outputs – the original one and the sql friendly one.
Generated Sql query: Query: SELECT [Betek2].[dbo].[CountryDetail].[Name] AS [CountryName] FROM [Betek2].[dbo].[CountryDetail] WHERE [Betek2].[dbo].[CountryDetail].[Id] = @Id1 Parameter: @Id1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0. SqlServer ready query: Query: DECLARE @Id1 Int SET @Id1=0 SELECT [Betek2].[dbo].[CountryDetail].[Name] AS [CountryName] FROM [Betek2].[dbo].[CountryDetail] WHERE [Betek2].[dbo].[CountryDetail].[Id] = @Id1
Now you can just copy&paste the lower query output to your favorite database management tool and execute it as is.
I hope you'll find this improvement useful as I do.
You can download entire source file here.