4
\$\begingroup\$

I'm mainly looking at usage of Dapper, but in general, any comments relating SQL -> C# object mapping best practices would be appreciated.

I've got two super simple objects I'm working with:

public class Subcategory
{
    public Guid Id { get; set; }
    public Guid CategoryId { get; set; }
    public string Name { get; set; }
}

public class Category
{
    public Category()
    {
        Subcategories = new List<Subcategory>();
    }

    public Guid Id { get; set; }
    public string Name { get; set; }
    public ICollection<Subcategory> Subcategories { get; set; }
}

Id for both of them is set by the db on insert via T-SQL's newsequentialid().

I'm writing repository classes for each of the objects that conform to the following interface (actually implemented via an abstract class):

public interface IMaintanceRepository<T> where T: class
{
    Guid Create(T toCreate);
    T Read(Guid id);
    T Update(T toUpdate);
    void Delete(Guid id);
}

Right now, I'm trying to write the Update method in the Category repository. I have the following written, but I'm not sure if it's the most performant Dapper code as written.

public override Category Update(Category toUpdate)
{
    var deleteRemovedSubcategories = @"
        DELETE FROM dbo.Subcategories 
        WHERE CategoryId = @Id 
            AND Id NOT IN @Ids";

    var addNewSubcategories = @"
        INSERT INTO dbo.Subcategories (CategoryId, Name)
        VALUES (@CategoryId, @Name)";

    var updateExistingSubcategories = @"
        CREATE TABLE #subcategories (
            Id uniqueidentifier NOT NULL,
            CategoryId uniqueidentifier NOT NULL,
            Name varchar(50) NOT NULL
        );

        INSERT INTO #subcategories VALUES (@Id, @CategoryId, @Name);

        UPDATE s1
        SET s1.CategoryId = s2.CategoryId, s1.Name = s2.Name
        FROM dbo.Subcategories s1
        INNER JOIN #subcategories s2
        ON s1.Id = s2.Id;

        DROP TABLE #subcategories;";

    var updateCategory = @"
        UPDATE dbo.Categories
        SET Name = @Name
        OUTPUT INSERTED.*
        WHERE Id = @Id;

        SELECT * FROM dbo.Subcategories WHERE CategoryId = @Id";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Execute(deleteRemovedSubcategories, new { Id = toUpdate.Id, Ids = toUpdate.Subcategories.Select(s => s.Id) });
        connection.Execute(addNewSubcategories, toUpdate.Subcategories.Where(s => s.Id == Guid.Empty));
        connection.Execute(updateExistingSubcategories, toUpdate.Subcategories);
        using (var multi = connection.QueryMultiple(updateCategory, toUpdate))
        {
            var category = multi.ReadSingle<Category>();
            category.Subcategories = multi.Read<Subcategory>().ToList();
            return category;
        }
    }
}

Is there any suggested improvements to doing something like this?

\$\endgroup\$
9
  • \$\begingroup\$ Also worth noting, the queries executed are extremely order dependent currently, which makes me feel a little uneasy. In particular the deleteRemovedSubcategories query would delete new Subcategory-ies added via addNewSubcategories. I'm not sure if my intuition about this is misplaced though. \$\endgroup\$ – C Smith Jan 8 '17 at 3:24
  • \$\begingroup\$ Why are you inserting into a #temp to update regular table? \$\endgroup\$ – paparazzo Jan 8 '17 at 14:25
  • \$\begingroup\$ @Paparazzi, I want to bulk load to a temp table so I can offload the update to the DB. Changes number of times I need to transfer data across the DB connection from N (number of updates to make) to 1. \$\endgroup\$ – C Smith Jan 8 '17 at 18:10
  • \$\begingroup\$ @Paparazzi, Dapper converts the VALUES (/* params */) into a bulk-load statement, per their documentation. It's not just one row, confirmed via testing. \$\endgroup\$ – C Smith Jan 8 '17 at 18:42
  • \$\begingroup\$ It's passing toUpdate.Subcategories, which is an ICollection<Subcategory> per the class in the question. Dapper supposedly bulk-loads anything that implements IEnumerable<T>, which is what you'd want. \$\endgroup\$ – C Smith Jan 8 '17 at 18:57
2
\$\begingroup\$

I was looking at what you had coded here

var deleteRemovedSubcategories = @"
    DELETE FROM dbo.Subcategories 
    WHERE CategoryId = @Id 
        AND Id NOT IN @Ids";

and it took me a minute to figure out exactly what the Where clause was filtering, what finally gave it away was the name of the C# variable deleteRemovedSubcategories. If it were possible I would change some of these column names and/or T-SQL variables so that it is more clear what you are saying here.

\$\endgroup\$
1
  • 1
    \$\begingroup\$ Useful feedback. Column IDs could definitely be prefixed with the object name. That's confused me a time or two. My question was more geared towards evaluating the usage of dapper though, not coding standards. I'll update the question to be more specific. +1 though! \$\endgroup\$ – C Smith Jan 14 '17 at 0:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.