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.