Hello,

I am using LL version 2.6. I am using the c# adapter templates.

Is there a method somewhere in the DataAccessAdapter base to retrieve the dml that a call to FetchEntity or FetchEntityCollection would generate, before actually calling these methods to execute? kind of like .GetSQL(enum.FetchEntityCollection, overloads) that would emit the SQL that the query was going to generate?

Thanks.

Shawn S.

Please see the documentation about troubleshooting and debugging

You need to enable verbose tracing (4) on the DQE for the database you are using.

Matt

Matt,

I am trying to get the SQL before the query is executed, not after via a trace.

Shawn

Not easily. Take a look at this thread which may point you in the right direction.

Don't forget that the source code for the LLBLGen binaries is available in the customer area, so there is nothing to stop you extending it to give you the functionality you need.

Matt

Here is something that may help. It intercepts the generated queries and builds a string putting in formatted parameters at the top so you can cut and paste and run the query directly.

1) Create a partial DataAccessAdapter class 2) Add this code to it:

        #region Dump Executing SQL queries
        static void DumpQuery(IQuery query)
        {
            var dumpFormattedQuery = DynamicQueryEngineBase.Switch.TraceVerbose;
            if (!dumpFormattedQuery) DumpIfDebug(ref dumpFormattedQuery);

            if (dumpFormattedQuery)
            {
                var formattedQuery = new SqlQueryFormatter().DumpFormattedQuery(query);
                TraceHelper.WriteIf(true, formattedQuery, "SqlServer query");
                LOG.Debug(formattedQuery);
            }
        }

        [Conditional("DEBUG")]
        static void DumpIfDebug(ref bool dumpFormattedQuery)
        {
            dumpFormattedQuery = true;
        }

        public override int ExecuteActionQuery(IActionQuery queryToExecute)
        {
            DumpQuery(queryToExecute);

            return base.ExecuteActionQuery(queryToExecute);
        }

        public override bool ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo)
        {
            DumpQuery(queryToExecute);

            return base.ExecuteMultiRowDataTableRetrievalQuery(queryToExecute, dataAdapterToUse, tableToFill, fieldsPersistenceInfo);
        }

        public override void ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, bool allowDuplicates, IEntityFields2 fieldsUsedForQuery)
        {
            DumpQuery(queryToExecute);

            base.ExecuteMultiRowRetrievalQuery(queryToExecute, entityFactory, collectionToFill, fieldsPersistenceInfo, allowDuplicates, fieldsUsedForQuery);
        }

        public override object ExecuteScalarQuery(IRetrievalQuery queryToExecute)
        {
            DumpQuery(queryToExecute);

            return base.ExecuteScalarQuery(queryToExecute);
        }

        public override void ExecuteSingleRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFields2 fieldsToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo)
        {
            DumpQuery(queryToExecute);

            base.ExecuteSingleRowRetrievalQuery(queryToExecute, fieldsToFill, fieldsPersistenceInfo);
        }
        #endregion Dump Executing SQL queries

1. Add this class:

    public class SqlQueryFormatter
    {
        static readonly string[] CommonKeywords = new[] {"FROM", "WHERE", "GROUP BY", "HAVING", "INNER JOIN", "LEFT JOIN", "RIGHT JOIN"};

        public string DumpFormattedQuery(IQuery query)
        {
            try
            {
                var sb = new StringBuilder();

                if (query is IActionQuery)
                {
                    if (query is BatchActionQuery)
                    {
                        DumpBatchActionQuery(sb, query as BatchActionQuery);
                    }
                    else
                    {
                        DumpActionQuery(sb, query as IActionQuery);
                    }
                }
                else if (query is IRetrievalQuery)
                {
                    if (query.Command.CommandType == CommandType.StoredProcedure)
                    {
                        DumpStoredProcedureRetrievalQuery(sb, query as IRetrievalQuery);
                    }
                    else
                    {
                        DumpRetrievalQuery(sb, query as IRetrievalQuery);
                    }
                }
                else
                {
                    sb.Append("** Unknown query type **");
                }

                return sb.ToString();
            }
            catch(Exception ex)
            {
                return "** Exception occurred during DumpFormattedQuery: " + ex.Message + "**";
            }
        }

        static void DumpParameters(StringBuilder sb, IQuery query)
        {
            var sqlParam = new SqlParameter();

            foreach(IDbDataParameter param in query.Parameters)
            {
                if ((param.Value == null) || (param.Value == DBNull.Value)) continue;

                string value;

                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);
                        break;

                    case DbType.Boolean:
                        value = Convert.ToBoolean(param.Value) ? "1" : "0";
                        break;

                    case DbType.DateTime:
                        value = "'" + ((DateTime) param.Value).ToString("yyyyMMdd HH:mm:ss") + "'";
                        break;

                    default:
                        if (param.Value is Enum)
                        {
                            var paramAsEnum = (Enum) param.Value;
                            value = string.Format("{0:d} /* {1}.{0:f} */", paramAsEnum, paramAsEnum.GetType().Name);
                            break;
                        }

                        value = string.Format(CultureInfo.InvariantCulture, "{0}", param.Value);
                        break;
                }

                var type = "Unknown";

                try
                {
                    sqlParam.DbType = param.DbType;

                    type = sqlParam.SqlDbType.ToString();
                    if (param.Size != 0)
                    {
                        type += "(" + param.Size + ")";
                    }
                }
                catch {}

                sb.AppendFormat("DECLARE {0} {1}; SET {0}={2}\r\n", param.ParameterName, type, value);
            }
        }

        static void ReplaceAnyKeywords(ref string text, string before, string after, params string[] keywords)
        {
            foreach(var keyword in keywords)
            {
                if (!text.Contains(keyword)) continue;

                text = text.Replace(keyword, before + keyword + after);
                return;
            }
        }

        static void ReplaceAllKeywords(ref string text, string before, string after, params string[] keywords)
        {
            foreach(var keyword in keywords)
            {
                if (!text.Contains(keyword)) continue;

                text = text.Replace(keyword, before + keyword + after);
            }
        }

        static void DumpStoredProcedureRetrievalQuery(StringBuilder sb, IRetrievalQuery query)
        {
            sb.Append("\r\n\r\nStored Procedure Call Query:\r\n");

            DumpParameters(sb, query);

            sb.Append(query.Command.CommandText);
            sb.Append("(");
            
            for(var i = 0; i < query.Command.Parameters.Count; i++)
            {
                if (i > 0) sb.Append(", ");

                sb.Append(((IDataParameter) query.Command.Parameters[i]).ParameterName);
            }

            sb.Append(")");
            sb.Append("\r\n");
        }

        static void DumpRetrievalQuery(StringBuilder sb, IRetrievalQuery query)
        {
            sb.Append("\r\n\r\nRetrieval Query:\r\n");
            
            DumpParameters(sb, query);

            var text = query.Command.CommandText;

            ReplaceAnyKeywords(ref text, "\r\n", "\r\n ", "SELECT DISTINCT", "SELECT");
            ReplaceAllKeywords(ref text, "\r\n", "\r\n ", CommonKeywords);
            ReplaceAllKeywords(ref text, "", "\r\n ", ",");

            sb.Append(text);
            sb.Append("\r\n");
        }

        static void DumpBatchActionQuery(StringBuilder sb, BatchActionQuery batchActionQuery)
        {
            sb.Append("\r\n\rBatchAction Query (Count=" + batchActionQuery.Count + "):\r\n");

            var part = 1;
            foreach(var actionQuery in batchActionQuery.ActionQueries)
            {
                sb.Append("\r\n Action Query #" + part++ + ":\r\n");
                
                DumpActionQueryCore(sb, actionQuery);
            }

            sb.Append("\r\n");
        }

        static void DumpActionQuery(StringBuilder sb, IActionQuery query)
        {
            sb.Append("\r\n\r\nAction Query:\r\n");

            DumpActionQueryCore(sb, query);
        }

        static void DumpActionQueryCore(StringBuilder sb, IActionQuery query)
        {
            DumpParameters(sb, query);

            var text = query.Command.CommandText;

            ReplaceAnyKeywords(ref text, "\r\n", "", "INSERT INTO", "DELETE");
            ReplaceAllKeywords(ref text, "\r\n", "\r\n ", CommonKeywords);
            ReplaceAllKeywords(ref text, "", "\r\n ", ",");

            sb.Append(text);
            sb.Append("\r\n");
        }
    }

(This code is based on code originally written by mihies, back in 2006 I think)

Cheers Simon

thanks.

Thanks guys, much appreciated.

Shawn

simmotech wrote:

Here is something that may help. It intercepts the generated queries and builds a string putting in formatted parameters at the top so you can cut and paste and run the query directly.

(This code is based on code originally written by mihies, back in 2006 I think)

Cheers Simon

This one you mean?http://cs.rthand.com/blogs/blog_with_righthand/pages/Implementing-more-useful-tracing-for-LLBLGenPro-2.0.aspx

I like, and are now using, your improvements but isn't hooking it in at CreateSelectDQ, like Miha Markic did, better(i.e catch more queries such as those from FetchProjection) than the hooks you use? e.g.

protected override IRetrievalQuery CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects,
                                                       IPredicateExpression filter, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk,
                                                       bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize)
{
      var query = base.CreateSelectDQ(fieldsToFetch, persistenceInfoObjects, filter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk,
                                      allowDuplicates, groupByClause, pageNumber, pageSize);
      DataHelper.LogQuery(query);
      return query;
}

(DataHelper is a static version of your SqlQueryFormatter)

Just curious Jeremy