Using Query T to perform inserts, alternative to stored procedures - adamfoneil/Dapper.QX GitHub Wiki

The use I've documented most for Query<T> is for SELECT queries, but there's actually nothing stopping you from using it with INSERT and UPDATE statements. This can be an alternative to writing stored procedures. If you need to wrap several insert or update actions in a transaction to assure consistent state, this works well also. I'll detail an example from my SqlChartify project that demonstrates this. I have a "report copy" feature for promoting reports from Test to Production that looks like this.

img

A "report" in this context refers to the SQL behind it, the database connection it's related to, its defined parameters and chart configuration -- for example, the column-series mappings, as well as legends, colors, axis titles, and so on. "Copying" a report means transferring a report from one database connection to another, preserving its parameters and configuration. At the database level, this requires a series of INSERT statements that map new identity values to preserve foreign key relationships on newly-created rows. That may sound like a mouthful, so let's pause to understand the problem. Imagine a report with the Id 438. It has parameters and configuration with ReportId 438. When "copying" the report, it gets a new Id value that isn't known until the new report row is inserted. To complete the copy, the parameters and configuration rows must be inserted as well, but what's the new ReportId? Furthermore, what happens if one of the inserts fails? We don't want partial success -- the multi-step copy operation must succeed or fail as a unit.

Dapper.QX is a good fit for this problem because it gives us a way to run parameter-driven SQL statements with concise, testable C# -- while imposing very little abstraction. If you can write the SQL, you can wrap it in a Query<T> class and call your class instead. Furthermore, we can leverage Dapper's existing support for transactions to make it easy to assure consistent state -- that is, no partial success over multi-step operations.

Post handler and Razor markup

This is what the Razor Pages submit button handler looks like. The outermost block opens the database connection. The next block within contains the transaction using block. Note how a try block is directly within it. You should follow this pattern with transactions: the last statement in the try should Commit the transaction, and the catch block should Rollback. The empty throw is up to you. I have a generic error handler at the application level AzureTableLogger, so using throw here will trigger it if something goes wrong here, and take advantage of my logging and error UI. (Error handling and logging is a separate discussion.)

Next, we'll take a look at the query classes in use here: CopyReports, GetCopiedReportIds, CopyConfig and CopyParams. Note how the txn transaction argument is passed in all the ExecuteAsync calls. This is what ensures a clean rollback in the event of an error. That argument is optional of course and used only in relatively unusual situations like this:

public async Task<RedirectResult> OnPostCopyReportsAsync(int sourceConnectionId, int destConnectionId, int[] reportIds)
{
    using (var cn = Data.GetConnection())
    {
        cn.Open();
        using (var txn = cn.BeginTransaction())
        {                    
            try
            {
                await new CopyReports()
                {
                    WorkspaceId = Data.WorkspaceId,
                    SourceConnectionId = sourceConnectionId,
                    DestConnectionId = destConnectionId,
                    LocalTime = DateTime.UtcNow,
                    UserName = User.Identity.Name,
                    ReportIds = reportIds
                }.ExecuteAsync(cn, txn);

                var copiedReports = await new GetCopiedReportIds()
                {
                    SourceConnectionId = sourceConnectionId,
                    DestConnectionId = destConnectionId,
                    ReportIds = reportIds
                }.ExecuteAsync(cn, txn);

                foreach (var copiedRpt in copiedReports)
                {
                    await new CopyConfig() { SourceReportId = copiedRpt.SourceId, DestReportId = copiedRpt.DestId, UserName = User.Identity.Name, LocalTime = DateTime.UtcNow }.ExecuteAsync(cn, txn);
                    await new CopyParams() { SourceReportId = copiedRpt.SourceId, DestReportId = copiedRpt.DestId, UserName = User.Identity.Name, LocalTime = DateTime.UtcNow }.ExecuteAsync(cn, txn);
                }
                txn.Commit();
            }
            catch
            {
                txn.Rollback();
                throw;
            }
        }                
    }

    return Redirect($"/Manage/Copy?SourceConnectionId={sourceConnectionId}&DestConnectionId={destConnectionId}");
}

Here's what the relevant razor syntax looks like:

<form method="post" asp-page-handler="CopyReports">
    <input type="hidden" name="SourceConnectionId" value="@Model.SourceConnectionId"/>
    <input type="hidden" name="DestConnectionId" value="@Model.DestConnectionId"/>
    <ul class="multi-column">
        @foreach (var rpt in Model.Reports)
        {
            <li class="no-bullet">
                <label>
                    <input type="checkbox" name="ReportIds" value="@rpt.Id" class="[email protected]"/>
                    <img src="~/images/ReportTypes/@(rpt.Type.ToString()).svg" style="width:20px;height:20px;display:inline-block"/>                                
                    <span>@rpt.Name</span>
                </label>                    
            </li>
        }
    </ul>
    <button class="btn btn-link chk-invert-selection" data-target="[email protected]" type="button">Invert Selection</button>    
    <button class="btn btn-primary" type="submit">Copy Reports</button>
</form>

Query classes walkthrough

Now let's look at the individual query classes.

CopyReports

This is the top-most query in the feature. Note I used Query<int> as the base class. Since this is an multi-row insert statement, I'm ignoring the return value, so I use int merely as a required placeholder. The query has mostly required parameters, but note the ReportIds array property passed through the {andWhere} token. I use an array property because you can copy multiple reports at once. Note that when you use IN-list criteria with Dapper, you omit the enclosing parentheses around the parameter name. I'm not completely sure why Dapper does this, but I think this is how they protect against SQL injection in this special case. SQL does not support arrays natively, so Dapper does a little magic to make them work. Note also how my test cases use negative numbers for SourceConnectionId, DestConnectionId and so on. This is because the unit test project actually executes the query to verify the SQL. I don't want the test to generate actual rows, so using Id values I'm certain don't exist will prevent actual insertions, while still asserting correct SQL.

public class CopyReports : Query<int>, ITestableQuery
{
    public CopyReports() : base(
        @"INSERT INTO [dbo].[Report] (
            [WorkspaceId], [Type], [Name], [ConnectionId], [IsEnabled], [Sql], [DateCreated], [CreatedBy]
        ) SELECT
            [WorkspaceId], [Type], [Name], @destConnectionId, [IsEnabled], [Sql], @localTime, @userName
        FROM
            [dbo].[Report] [rpt]
        WHERE
            [WorkspaceId]=@workspaceId AND
            [ConnectionId]=@sourceConnectionId {andWhere}")
    {
    }

    public int WorkspaceId { get; set; }
    public int SourceConnectionId { get; set; }
    public int DestConnectionId { get; set; }
    public DateTime LocalTime { get; set; }
    public string UserName { get; set; }

    [Where("[rpt].[Id] IN @reportIds")]
    public int[] ReportIds { get; set; }

    public IEnumerable<ITestableQuery> GetTestCases()
    {
        yield return new CopyReports() 
        { 
            SourceConnectionId = -1, DestConnectionId = -1, WorkspaceId = -1, ReportIds = new int[] { 1, 2, 3 },
            UserName = "adamo",
            LocalTime = DateTime.Now
        };
    }

    public IEnumerable<dynamic> TestExecute(IDbConnection connection)
    {
        return TestExecuteHelper(connection);
    }
}

GetCopiedReportIds

The CopyReports query generates new Report rows, but what are actual new Id values? I need these when copying config and parameter info on subsequent queries. Although you can use something like SCOPE_IDENTITY() or the OUTPUT clause to get the Id value from a single row insert, there's no built-in way in SQL Server to get all the Id values from a multi-row insert. My workaround for this to define a second query that gets the new Id values with a join. By querying for the original report Ids and self-joining to the Report table by Name and Type, I can get the mappings of old to new Id values -- as SourceId and DestId respectively. You will need something like this in a similar situation. I've actually done a lot of work on this problem at scale in my SqlMigrator thingy, and I briefly considered using that here. But that would've been overkill and complicated. You'll note there's no ITestableQuery implementation here, because I am actually a terrible human being.

public class GetCopiedReportIdsResult
{
    public int SourceId { get; set; }
    public int DestId { get; set; }
}

public class GetCopiedReportIds : Query<GetCopiedReportIdsResult>
{
    public GetCopiedReportIds() : base(
        @"SELECT 
            [src].[Id] AS [SourceId], [dst].[Id] AS [DestId]
        FROM 
            [dbo].[Report] [dst]
            INNER JOIN [dbo].[Report] [src] ON [dst].[Type]=[src].[Type] AND [dst].[Name]=[src].[Name]
        WHERE
            [dst].[ConnectionId]=@destConnectionId AND 
            [src].[ConnectionId]=@sourceConnectionId AND
            [src].[Id] IN @reportIds")
    {
    }

    public int SourceConnectionId { get; set; }
    public int DestConnectionId { get; set; }
    public int[] ReportIds { get; set; }
}

CopyConfig

Remember that when copying a Report, I have to include its related parameters and configuration. These are the easiest queries to write, but they rely on having the report SourceId and DestId values given by GetCopiedReportIds. If you look back at the Post handler code above, you'll see that this and CopyParams are run inside of a foreach loop -- something I almost always caution against. That caution applies when you have an unknown number of iterations. In this case, a report has literally one or two configuration rows, and just a couple of parameters. The foreach is safe to use in this case. Again, note the use of Query<int> as the base class because this is an insert statement where I'm ignoring the return value.

public class CopyConfig : Query<int>
{
    public CopyConfig() : base(
        @"INSERT INTO [dbo].[ReportConfig] (
            [ReportId], [Type], [Json], [DateCreated], [CreatedBy]
        ) SELECT
            @destReportId, [Type], [Json], @localTime, @userName
        FROM
            [dbo].[ReportConfig] [cfg]
        WHERE
            [cfg].[ReportId]=@sourceReportId")
    {
    }

    public int SourceReportId { get; set; }
    public int DestReportId { get; set; }
    public DateTime LocalTime { get; set; }
    public string UserName { get; set; }
}

CopyParams

This is very similar to CopyConfig but I include for completeness sake.

public class CopyParams : Query<int>
{
    public CopyParams() : base(
        @"INSERT INTO [dbo].[Parameter] (
            [ReportId], [Name], [Value], [DateCreated], [CreatedBy]
        ) SELECT
            @destReportId, [Name], [Value], @localTime, @userName
        FROM
            [dbo].[Parameter]
        WHERE
            [ReportId]=@sourceReportId")
    {
    }

    public int SourceReportId { get; set; }
    public int DestReportId { get; set; }
    public DateTime LocalTime { get; set; }
    public string UserName { get; set; }
}
⚠️ **GitHub.com Fallback** ⚠️