Using SqlCmdDictionary - adamfoneil/Dapper.CX GitHub Wiki

To help you build SQL INSERT and UPDATE statements for CRUD operations dynamically without model classes, Dapper.CX offers an abstract base class SqlCmdDictionary with a SQL Server implementation. 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

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 onSave optional callback 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, 
    onSave: (cmd, action) =>
    {
        switch (action)
        {
            case SaveAction.Insert:
                cmd["DateCreated"] = DateTime.Now;
                break;
            case SaveAction.Update:
                cmd["DateModified"] = DateTime.Now;
                break;
        }        
    });

Sometimes you might need to insert a SQL expression into a command dictionary instead of a runtime value. Let's say you have a scalar database function you need to use inside of a command dictionary. You'd use the SqlExpression type like this:

var cmd = new SqlServerCmd("dbo.Whatever", "Id")
{
    { "FirstName", "Adam" },
    { "LastName", "O'Neil" },
    { "DateCreated", new SqlExpression("getutcdate()") }
};

This will generate SQL INSERT and UPDATE statements like this:

INSERT INTO [dbo].[Whatever] (
    [FirstName], [LastName], [DateCreated]
) VALUES (
    @FirstName, @LastName, getutcdate()
)

UPDATE [dbo].[Whatever] SET
    [FirstName]=@FirstName,
    [LastName]=@LastName,
    [DateCreated]=getutcdate()
WHERE
    [Id]=@id

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();
    }
}

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.

Lastly, you can also initialize a SqlServerCmd from a query using this FromQueryAsync static method.