R – How to write this code using SubSonic

ado.netstored-proceduressubsonic

I have some legacy code that I'm rewriting using SubSonic to help the future maintainers. For the most part it's been relatively simple, as everything makes a stored procedure call. But now I'm having some difficulty with some tightly-coupled ADO.NET code.

The code depends on the SqlDataAdapter to decide when to call an INSERT or UPDATE stored proc, which I understand. How can I rewrite this code in a SubSonic way?

public void SaveInvoice(InvoiceItems invoiceItems)
{
    // extraneous code removed
    // invoiceItems is a dataset

    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "InvoiceItem_INSERT";
    cmd.Connection = conn;

    updateCmd.CommandType = CommandType.StoredProcedure;
    updateCmd.CommandText = "InvoiceItem_UPDATE";
    updateCmd.Connection = conn;

    SqlCommandBuilder.DeriveParameters(cmd);
    SqlCommandBuilder.DeriveParameters(updateCmd);

    adapter.InsertCommand = cmd;
    adapter.UpdateCommand = updateCmd;

    adapter.Update(invoiceItems._InvoiceItemTable);
}

I'm new to SubSonic, so any help is appreciated. All helpful answers will be gleefully upvoted.

Best Answer

There isn't going to be an exact representation of that kind of code using SubSonic because the automagic insert/update is done specifically by the SqlDataAdapter.Update() method. If there somehow is, buried somewhere within the SubSonic namespace, I would also like to know how to use it!

If you're dealing with one table (e.g. InvoiceItems), you can do the test yourself (this uses SubSonic's automatically generated active record implementation):

int key = InvoiceItems.ID; // not sure what your primary key identifier is called
InvoiceItem item = new InvoiceItem(key);
if (item != null)
{
    SPs.InvoiceItem_UPDATE(param1, param2, etc).Execute();
}
else 
{
    SPs.InvoiceItem_INSERT(param1, param2, etc).Execute();
}

Hopefully this gets you started.

As a totally unrelated sidenote, don't rely on the DeepSave() method to try to save across multiple tables, because it is not implemented. I found that out the hard way...

Related Topic