How to debug ServiceStack Ormlite when things go wrong?

Question

For the life of me I can't save my poco's after an update, the insert works though.

Below is the code in question:

public class Campaign : IHasId<int>, IAudit
{
    public Campaign()
    {
        IsRetread = true;
        IsDuplicate = true;
    }

    [AutoIncrement]
    public int Id { get; set; } //CampaignID

    public long CreatedDate { get; set; }

    public long ModifiedDate { get; set; }

    public string ModifiedBy { get; set; }

    [Required]
    public string Name { get; set; } //CampaignName

    [Required]
    public int CampaignTypeId { get; set; } //CampaignType

    [Required]
    public int CampaignDeliveryTypeId { get; set; } //LeadDeliveryType

    public string CampaignPhone { get; set; }

    [Required]
    public int LeadProviderId { get; set; } //LeadProviderID

    public int PhoneTriggerId { get; set; }

    [Required]
    public int CampaignResponseId { get; set; } //LeadResponse 

    [Required]
    public int CampaignCostTypeId { get; set; } //CostTypeID 

    public decimal CostAmount { get; set; } //CostAmount

    public decimal FixedCost { get; set; } //FixedCost

    public string NoteMisc { get; set; } //NoteTxt

    public string NoteAgent { get; set; } //MessagetoAgents

    public bool IsDefaultCampaign { get; set; } //DefaultCampaign

    public bool IsExceptionCampaign { get; set; } //ExceptionCampaign

    public bool IsFirmOffer { get; set; } //Firm Offer

    [Reference] 
    public CampaignCreative CampaignCreative { get; set; }

    [Reference] 
    public List<CampaignRule> CampaignRules { get; set; }

    /* These really should be a collection of rules */       
    public bool IsDuplicate { get; set; } //IsDuplicate

    public bool IsRetread { get; set; } //IsRetread

    public bool IsFactorTrustLeads { get; set; } //IsFactorTrustLeads

    public bool IsFactorTrustApp { get; set; } //IsFactorTrustApp
}

Then the save, which works:

    public long SaveCampaign(Campaign campaign)
    {
        using (var db = _connectionFactory.OpenDbConnection())
        {
            var rowId = db.Insert(campaign, true);
            return rowId;
        }
    }

and the update, which never saves the changed poco:

    public long UpdateCampaign(Campaign campaign)
    {
        using (var db = _connectionFactory.OpenDbConnection())
        {
            db.Save(campaign, true);
            return campaign.Id;
        }
    }

There aren't any errors, and the only sql I see in the immediate window are select statement, no updates (I've never seen ANY other statements beside SELECTs)

Is there another way to see why this update fails?

Thank you, Stephen

Answer 1

View Last SQL Executed

The easiest approach is to just print out the last SQL Statement that was executed, i.e:

db.GetLastSql().Print();

This works well for most OrmLite API's which only execute a single SQL statement. But it wont show all SQL executed with OrmLite's higher-level API's like db.Save() which can execute multiple statements.

Use Captured OrmLite SQL Filter

Another approach is to capture the generated SQL using a Capture Exec Filter which instead of executing the SQL Statements will

using (var captured = new CaptureSqlFilter())
using (var db = _connectionFactory.OpenDbConnection())
{
    db.CreateTable<Campaign>();
    var rowId = db.Insert(campaign, true);

    var sql = string.Join(";\n", captured.SqlStatements.ToArray());
    sql.Print();
}

But as this only captures and doesn't execute the SQL it wont show the full story of API's that rely on reading the database (which will just return mocked/empty results).

Profile the DB Connection

A more comprehensive approach which is used by many SQL Profilers is to use a connection profiler which is just a wrapper around a DbConnection that in addition to executing the SQL will also capture and profile it.

Here's an example of profiling OrmLite by using ServiceStack's built-in Mini Profiler:

Container.Register<IDbConnectionFactory>(c =>
    new OrmLiteConnectionFactory(
        connectionString, SqlServerDialect.Provider) {
            ConnectionFilter = x => new ProfiledDbConnection(x, Profiler.Current)
    });

Which gets OrmLite to execute its SQL using a ProfiledDbConnection instead which can be later viewed in the Mini Profiler.