Using the CaptureSql filter and Insert (any variant) displaying unexpected results

ServiceStack.OrmLite
1

Hey ServiceStack,

Please review the following code example. It is generating unexpected SQL statements when using the CaptureSql filter.

List<string> sqlStatements = new List<string>();

Log.Info("Seeding tables");

using (var captured = new CaptureSqlFilter())
{
    types.Select(Activator.CreateInstance).OfType<ISeededEntity>().Reverse().ToList().ForEach(table =>
    {
        table.InsertSeedData(db);
    });

    sqlStatements.AddRange(captured.SqlStatements.ToList());
}

if (sqlStatements.Count > 0)
{
    db.ExecuteSql(string.Join("\n\n", sqlStatements));
    Log.Info("Tables seeded");
    sqlStatements.Clear();
}
else
{
    Log.Info("No action required");
}

I would expect that the insert statements inside the sqlStatements collection would be fully qualified and correct. However, I receive the following:

INSERT INTO "Country"     ("Name","Alpha2Code","Alpha3Code","Digit3Code","CreatedDate","ModifiedDate","ModifiedBy") VALUES (@Name,@Alpha2Code,@Alpha3Code,@Digit3Code,@CreatedDate,@ModifiedDate,@ModifiedBy)

I modified the code to take the CaptureSql filter out:

types.Select(Activator.CreateInstance).OfType<ISeededEntity>().Reverse().ToList().ForEach(table =>
{
    table.InsertSeedData(db);
});

Any ideas would be great. This database gets recreated (when ran locally) when the data model changes. I can see in the future, or on future projects how this might be painful from an initialization / initial load perspective.

Thanks,
Ryan

2

It looks like the correct SQL to me, what do you mean by fully qualified? Note you can’t just execute the captured SQL like that, it needs the db parameters as well.

You can enable Logging to see what SQL is being executed:

LogManager.LogFactory = new ConsoleLogFactory(debugEnabled:true);

Please show what SQL gets logged.

The built-in Profiler also shows what SQL gets executed.

3

I see. I guess I was expecting it to work the way I have the create/drop working:

List<string> sqlStatements = new List<string>();

Log.Info("Dropping tables");

using (var captured = new CaptureSqlFilter())
{
    db.DropTables(types.Reverse().ToArray());
    sqlStatements.AddRange(captured.SqlStatements.ToList());
}

if (sqlStatements.Count > 0)
{
    db.ExecuteSql(string.Join("\n\n", sqlStatements));
    sqlStatements.Clear();
}
else
{
    Log.Info("Tables dropped");
}
            
Log.Info("Creating tables");

using (var captured = new CaptureSqlFilter())
{
    db.CreateTables(true, types.ToArray());
    sqlStatements.AddRange(captured.SqlStatements.ToList());
}

if (sqlStatements.Count > 0)
{
    db.ExecuteSql(string.Join("\n\n", sqlStatements));
    Log.Info("Tables created");
    sqlStatements.Clear();
}
else
{
    Log.Info("No action required");
}

I guess what confused me the most is that even though I passed in a collection of objects, filled with data, the values did not exist in the generated sql statements. Thanks for letting me know what the issue was.

Oh, and by fully qualified sql I meant the objects having the values that were passed when the collection rendered the insert statements. I was in a hurry when I wrot that, so I guess I could have used better terminology. :wink:

Thanks again,
Ryan

4

Right, but that only works because DDL Statements don’t have any DB params.

It’s not going to help when concatenating sql statements like this since it uses the same db param names, but you can inspect captured db params from captured.SqlCommandHistory[0].Parameters. See the CaptureSqlFilter class for info.

5

Understood. I’m going to change the way this is done a bit. I think it can be done more cleanly. Thanks for the info.

Ryan