7
\$\begingroup\$

I have some code that allows for SQL reuse for C#. I'm pretty happy with it, but the fact that I can't find anything similar on the web makes me think I'm going about it the wrong way. I have a GitHub repo with a Visual Studio solution, and the code is repeated down below.

Here's the use case:

I'm working on a fairly standard C# and SQL Server project where we're generating reports. Each report is essentially the results of SQL query, displayed in HTML in the browser. I'd rather not use an ORM like Entity Framework, since we need to exactly control the SQL for performance reasons (e.g. use SQL server windowing functions, table hints, etc).

However, many of the reports are similar, just slicing and dicing by different attributes, so there's a desire for code reuse. SQL Server has stored procedures, views, and functions, but the consensus seems to be that you'll get into trouble trying to use them to create abstractions. To get around this I'm trying to use C# to reuse SQL code.

I'm essentially using Visual Studio's T4 text templates to generate SQL files at development time. I'm then passing the SQL to Stack Exchange's Dapper.NET project to handle variable substitution, issuing the query, and POCO [de]serialization.

Given a query like this, in the template file CuteAnimalsByLocation.tt:

<#@ output extension=".sql" #>
select * from animals a
where a.IsCute = 1 and a.IsFuzzy = 1 and a.Location = @location

And this file, DeadlyMachinesByLocation.tt:

<#@ output extension=".sql" #>
select * from DeadlyMachines m
where m.IsLethal = 1 and m.HasExplosives = 1 and m.Location = @location

I can reuse the above queries in a more complex query by writing the following, AnimalsInPeril.tt:

<#@ output extension=".sql" #>

WITH 
CuteAnimalsInLocation as 
( 
    <#@ include file="CuteAnimalsByLocation.tt" #>
),
DeadlyMachinesInLocation as
(
    <#@ include file="DeadlyMachinesByLocation.tt" #>
)
select a.* from CuteAnimalsInLocation a
inner join DeadlyMachinesInLocation m on a.Location = m.Location

I can run all 3 of these queries like this: (assuming the POCOs Animal and DeadlyMachine exist; they're just POCOs that map to the table schemas):

using (var connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    var queryParams = new { Location = "NorthAmerica" };

    IEnumerable<Animal> animalsNeedingHelp = 
        connection.QueryFromFile<Animal>("AnimalsInPeril", queryParams);
    IEnumerable<Animal> cuteAnimals = 
        connection.QueryFromFile<Animal>("CuteAnimalsByLocation", queryParams);
    IEnumerable<DeadlyMachine> deadlyMachines = 
        connection.QueryFromFile<DeadlyMachine>("DeadlyMachinesByLocation", queryParams);
}

connection.QueryFromFile is defined as this, and depends on Dapper:

public static class DapperFileExtensions
{
    public static IEnumerable<TReturn> QueryFromFile<TReturn>(this IDbConnection cnn, string file, object params = null)
    {
        var sql = File.ReadAllText(file + ".sql");
        return cnn.Query<TReturn>(sql, params);
    }
}

Can anyone see problems with this approach? I guess the only thing I can think of is that I could add caching around the File.ReadAllText call.

\$\endgroup\$
4
  • 2
    \$\begingroup\$ Hi Will, I wonder if you're overthinking it a little to much and may just end up with unmaintainable code from a C# and an SQL perspective; if these are reports you'll probably have to endless tweak them at the whim of the users, too much reuse will prevent tweaking; having views and/or stored procedures in a database would help and make it easy to change. Going to a low level of generic abstraction may mean your code won't make any sense 6 months down the line. \$\endgroup\$
    – Phill
    Sep 2, 2015 at 16:29
  • \$\begingroup\$ Thanks @Phill, that's exactly the sort of perspective I'm looking for. If I keep this level of abstraction at the same level of abstraction of views and sprocs, can you think of downsides? I see the benefits of version control, simplified deployment, and easing the job of the RDBMS query optimizer. \$\endgroup\$
    – Will
    Sep 3, 2015 at 2:45
  • \$\begingroup\$ My team is using an old port of MyBatis to so something similar, with SQL query snippets defined in XML files. It's odd, but with a few tools written a long time ago to pre-process the XML files it's working great for us. I'd like to rewrite the tools in golang or nodejs instead of C# Console + WinForms, but other than that no complaints. \$\endgroup\$
    – yzorg
    Sep 5, 2015 at 6:34
  • 2
    \$\begingroup\$ Be careful with using query hints. You might get a boost now, but they can end up making performance worse as the data changes over time. \$\endgroup\$
    – RubberDuck
    Sep 12, 2015 at 1:58

2 Answers 2

1
\$\begingroup\$

You say performance is a concern, but you're doing this.

select a.* from

That's poor form whether or not you already have a table scan in the underlying query plan for the CTE.

I think you misunderstood the article you linked to. It is warning against doing exactly what you've done here. SQL just doesn't lend itself to code reuse in the way a "regular" programmer is accustomed to. It's a different beast that way, it takes a different mindset.

SQL is a set based query language. Code reuse comes in the form of (well tuned) views and stored procedures. If you need this kind of fine grained control over the SQL, then keep it where it belongs, in the database.

Depending on just how dynamic your queries are, you may not always be getting the benefit of query plan caching. Each time a query that the analyzer hasn't seen before is processed, it has to generate a new plan, so you'll take a performance hit.

Lastly, I left a comment, but I should mention it here too. Be careful using query hints. You may be able to outwit the analyzer now, based on the current data, but the data will change over time. When the data has changed and your hint is no longer the most efficient query plan, the analyzer won't be able to choose the most efficient plan because you've told it not to. Performance gained this way may degrade over time.

\$\endgroup\$
2
  • \$\begingroup\$ Thanks for the insight! As I understand it, the linked article is warning against using views and sprocs as generic, parameterized code reuse mechanisms, as it will make the query analyzer's job hard. I'm hoping by stepping outside of SQL, I'll get reuse (which is desirable from a maintainability standpoint, as long as it doesn't have negative performance effects) while presenting simple, straightforward SQL to the RDBMS. That's a good point about query plan caching, though. The query hint advice is great. You've given me a lot to think about, thanks. \$\endgroup\$
    – Will
    Sep 13, 2015 at 7:41
  • \$\begingroup\$ @Will no. The article is arguing that you should be using views and stored procedures (but not user defined functions). I don't know how to explain it properly, but that article really is talking about not doing what you've done here. The article is just talking about using views to do what you've done in C# here. Good luck and happy coding mate. \$\endgroup\$
    – RubberDuck
    Sep 13, 2015 at 10:47
0
\$\begingroup\$

The main problem I see with this approach is you have magical strings, which are not compile time safe.

I highly recommend you look into using an ORM like entity framework

public class AnimalsRepository : IDisposable
{
    private MyDataModel Model { get; set; }
    public AnimalsRepository()
    {
        Model = new MyDataModel();
    }
    public void Dispose()
    {
        if (this.Model != null)
            this.Model.Dispose();
    }
    public IQueryable<Animal> GetCuteAnimals(string location)
    {
        return this.Model.Animals.Where(a => a.IsCute && a.IsFuzzy && a.Location == location);
    }
    public IQueryable<DeadlyMachine> GetDeadlyMachines(string location)
    {
        return this.Model.DeadlyMachines.Where(m => m.IsLethal && m.HasExplosives && m.Location == location);
    }
    public IQueryable<Animal> GetAnimalsInPeril(string location)
    {
        return from a in this.GetCuteAnimals(location)
               join m in this.GetDeadlyMachines(location) on a.Location equals m.Location
               select a;

    }
}

See Nuget Package: https://www.nuget.org/packages/EntityFramework

\$\endgroup\$
2
  • 3
    \$\begingroup\$ From the question: "I'd rather not use an ORM like Entity Framework, since we need to exactly control the SQL for performance reasons (e.g. use SQL server windowing functions, table hints, etc)." \$\endgroup\$
    – BCdotWEB
    Sep 2, 2015 at 8:30
  • \$\begingroup\$ I love the composability and static typing of IQueryable in EF. I'm using it in my application code, but for report-style SQL it doesn't give me enough control (as @BCdotWEB pointed out). But yeah, I'm basically trying to recreate the composability that I lose when moving from EF to raw SQL. \$\endgroup\$
    – Will
    Sep 2, 2015 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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