Hi

If I enable DQE diagnostics, like so:

    <switches>
        <add name="SqlServerDQE" value="4"/>
    </switches>

I get the generated SQL written to the Output window, which is great. However, the most common thing I need to do with this is copy it into to a query editor and run it directly against the db. Unfortunately because the output is listed with the parameter names injected into the query and the values listed seperately I usually have to spend a good ten minutes or so replacing all the parameters with their associated values. It's also especially annoying how enum parameters are written out by name rather than integer value as I need to find the enum definition somewhere in our code so I can replace it with the right integer.

Does anyone know a more effective way I could be dealing with this?

Thanks,

Chris.

Not at the moment. We're developing a profiler which will solve this for you (out later this year), but till then the option you have is using a trace listener (or use the output to vs.net) or alter the ormsupportclasses sourcecode, namely the ToString() method in Query.cs

Ok thanks Franz,

I'll take a look at query.cs. Will the profiler be version 3 (free upgrade)?

Chris.

siegemos wrote:

Ok thanks Franz,

I'll take a look at query.cs. Will the profiler be version 3 (free upgrade)?

Chris.

The profiler is a separate product, and will work with v3.x, EF, Linq to sql, dapper.net, massive and all other dbproviderfactory using data-access layers. It's not expensive and llblgen pro customers get a huge discount

The feature you requested was build in today, so it's definitely in v1 query as-is, beautified: (forum tab formatting makes it less formatted )

SELECT [LLBLGenProUnitTest].[dbo].[Address].[AddressID] AS [AddressId],
       [LLBLGenProUnitTest].[dbo].[Address].[City],
       [LLBLGenProUnitTest].[dbo].[Address].[Country],
       [LLBLGenProUnitTest].[dbo].[Address].[HouseNumber],
       [LLBLGenProUnitTest].[dbo].[Address].[StreetName],
       [LLBLGenProUnitTest].[dbo].[Address].[TestRunID] AS [TestRunId],
       [LLBLGenProUnitTest].[dbo].[Address].[Zipcode]
FROM   [LLBLGenProUnitTest].[dbo].[Address]
WHERE  (([LLBLGenProUnitTest].[dbo].[Address].[AddressID] = @p1)) 

query with inlined parameters

SELECT [LLBLGenProUnitTest].[dbo].[Address].[AddressID] AS [AddressId],
       [LLBLGenProUnitTest].[dbo].[Address].[City],
       [LLBLGenProUnitTest].[dbo].[Address].[Country],
       [LLBLGenProUnitTest].[dbo].[Address].[HouseNumber],
       [LLBLGenProUnitTest].[dbo].[Address].[StreetName],
       [LLBLGenProUnitTest].[dbo].[Address].[TestRunID] AS [TestRunId],
       [LLBLGenProUnitTest].[dbo].[Address].[Zipcode]
FROM   [LLBLGenProUnitTest].[dbo].[Address]
WHERE  (([LLBLGenProUnitTest].[dbo].[Address].[AddressID] = 18636 /* @p1 */))

works with all queries / parameters.

A note with Query.cs's ToString() method, it doesn't convert byte arrays to hex, nor does it wrap guids with ''. This does:

/// <summary>
/// Converts a byte array to hexadecimal string. From http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/3928b8cb-3703-4672-8ccd-33718148d1e3/
/// </summary>
/// <param name="byteArray">The byte array.</param>
/// <returns></returns>
/// <remarks>Doesn't include a 0x prefix. 8 times faster than BitConverter</remarks>
public static string ByteArrayToHex(byte[] byteArray)
{
    if(byteArray == null)
    {
        return "0";
    }
    char[] charArray = new char[byteArray.Length * 2];
    byte b;
    for(int i = 0; i < byteArray.Length; ++i)
    {
        b = ((byte)(byteArray[i] >> 4));
        charArray[i * 2] = (char)(b > 9 ? b + 0x37 : b + 0x30);
        b = ((byte)(byteArray[i] & 0xF));
        charArray[i * 2 + 1] = (char)(b > 9 ? b + 0x37 : b + 0x30);
    }

    return new string(charArray);
}
        


/// <summary>
/// Converts the parameter value to string.
/// </summary>
/// <param name="parameter">The parameter.</param>
/// <param name="quoteChar">The quote char.</param>
/// <returns></returns>
private static string ConvertParameterValueToString(Parameter parameter, string quoteChar)
{
    string toReturn = string.Empty;
    if((parameter.ValueBeforeExecution == null) || (parameter.ValueBeforeExecution == DBNull.Value))
    {
        toReturn = "NULL";
    }
    else
    {
        switch(parameter.DbType)
        {
            case DbType.Binary:
                toReturn = string.Format("0x{0}", ByteArrayToHex(parameter.ValueBeforeExecution as byte[]));
                break;
            case DbType.Object:
                // the value can't be displayed
                toReturn = "/* Type: OBJECT. */";
                break;
            case DbType.Guid:
                toReturn = string.Format("{0}{1}{0}", quoteChar, parameter.ValueBeforeExecution.ToString().ToUpperInvariant());
                break;
            case DbType.AnsiString:
            case DbType.AnsiStringFixedLength:
            case DbType.String:
            case DbType.StringFixedLength:
                toReturn = string.Format("{0}{1}{0}", quoteChar, parameter.ValueBeforeExecution);
                break;
            default:
                toReturn = parameter.ValueBeforeExecution.ToString();
                break;
        }
    }
    return toReturn;
}

where quoteChar is ' for all databases except mysql where it's `. 'Parameter' here is a parameter object, you should use DbParameter there.

Franz,

Excellent. Looking forward to the profiler app. Thanks for the help.

Chris.