Skip to content

SqlCmdDictionary: SqlServerCmd and MySqlCmd

Adam O'Neil edited this page Jul 26, 2019 · 3 revisions

To help you build SQL INSERT and UPDATE statements for CRUD operations dynamically without model classes, Postulate offers an abstract base class SqlCmdDictionary with SQL Server and MySQL implementations. This is intended for situations where you either don't have control of the underlying database schema, and need the ability to craft dynamic statements; or, for whatever reason, adding model classes would be inconvenient. SqlCmdDictionary is based on Dictionary<string, object> so you'd use these objects like any dictionary. Here's a SQL Server example:

using (var cn = GetConnection())
{
    var id = await new SqlServerCmd("dbo.Whatever", "Id")
    {
        { "FirstName", "Adam" },
        { "LastName", "O'Neil" },
        { "SomeDate", DateTime.Today },
        { "Flag", true }
    }.InsertAsync<int>(cn);
}

This is equivalent to writing an INSERT statement like this:

DECLARE @FirstName, @LastName, @SomeDate, @Flag
SET @FirstName = 'Adam'
SET @LastName = 'O''Neil'
SET @SomeDate = /* today */
SET @Flag = 1

INSERT INTO [dbo].[Whatever] (
    [FirstName], [LastName], [SomeDate], [Flag]
) VALUES (
    @FirstName, @LastName, @SomeDate, @Flag
);

SELECT SCOPE_IDENTITY();

This is intended to be easier to write than a statement in which you manually concatenate delimiters, punctuation and parameter notation, but does not require model classes. An UPDATE statement works in a similar way. The only difference is that you must pass an identity value like this:

using (var cn = GetConnection())
{
    await new SqlServerCmd("dbo.Whatever", "Id")
    {
        { "FirstName", "Whoever" },
        { "LastName", "Nobody" },
        { "SomeDate", DateTime.Today.AddDays(30) },
        { "Flag", false }
    }.UpdateAsync(cn, 23451);
}

This is the same as writing this SQL:

DECLARE @FirstName, @LastName, @SomeDate, @Flag, @Id
SET @FirstName = 'Whoever'
SET @LastName = 'Nobody'
SET @SomeDate = /* today + 30 days */
SET @Flag = 0
SET @Id = 23451

UPDATE [dbo].[Whatever] SET
    [FirstName]=@FirstName,
    [LastName]=@LastName,
    [SomeDate]=@SomeDate,
    [Flag]=@Flag
WHERE
    [Id]=@Id

Merging

In addition to InsertAsync and UpdateAsync, SqlCmdDictionary offers a MergeAsync method to automatically perform an insert or update based on whether a record exists or not. To use this, you must indicate the primary key columns in the table being update by prefixing the columns with a hash sign # like this:

using (var cn = GetConnection())
{
    var id = await new SqlServerCmd("dbo.AnotherTable", "Id")
    {
        { "#FirstName", firstName },
        { "#LastName", lastName },
        { "Phone", phone },
        { "Email", email }
    }.MergeAsync<int>(cn);
}

This will query for an existing record based on FirstName and LastName and perform an insert or update depending on whether the record exists or not.

If you need to set select columns differently for inserts and updates, use the onInsert and onUpdate optional callbacks like this. This example sets a DateCreated or DateModified column based on whether an insert or update is happening.

var cmd = new SqlServerCmd("dbo.Employee", "Id")
{
    { "#OrganizationId", 1 },
    { "#FirstName", "Adam" },
    { "#LastName", "O'Neil" },
    { "HireDate", DateTime.Today },                                
    { "IsActive", true }
};

var id = await cmd.MergeAsync<int>(cn, 
    onInsert: (cmd) =>
    {
        cmd["DateCreated"] = DateTime.Now;
    }, onUpdate: (cmd) =>
    {
        cmd["DateModified"] = DateTime.Now;
    });

Reusing Commands

Sometimes, you may want to create a command that is reused with different parameters, in a loop for example. You can create a command with column names only, then get the DbCommand object appropriate to your platform -- SQL Server or MySQL -- and use the command object with different parameters. This would look something like this:

var cmdDictionary = new SqlServerCmd("dbo.Hello", "Id", new string[] { "This", "That", "Other" });

using (var cn = GetConnection())
{
    using (var cmd = cmdDictionary.GetInsertCommand(cn))
    {
        cmd.Parameters["This"].Value = "this value";
        cmd.Parameters["That"].Value = "that value";
        cmd.Parameters["Other"].Value = "some other value";
        cmd.ExecuteNonQuery();
    }
}

SQL Server specific features

You can initialize a SqlServerCmd dictionary from the columns in an existing table with the FromTableSchemaAsync method. This will inspect the specified table for primary key and identity columns.

using (var cn = GetConnection())
{
    var cmd = await SqlServerCmd.FromTableSchemaAsync(cn, "dbo", "Whatever");
}

Note that there's an overload that lets you specify your own key columns, in case the table you're updating doesn't have a primary key that your application would assume.

You can’t perform that action at this time.