Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server
Tip/Trick

Your SQL Wrapped in Generated Code. Interesting? Original?

Rate me:
Please Sign up or sign in to vote.
4.96/5 (17 votes)
1 Jul 2016MIT4 min read 87.7K   14   51
World first implementation of the Dominic Strauss Kahn data access method!

Introduction

"In the .NET world (and beyond), data access is a cluster-f#$%ed echo chamber of half-assed rocket engineering and cargo cultism based on decade-old cathedralized thinking and corporate naval -gazing"
Rob Conery

Background

With the rise of micro-orms, folks are coming back to SQL for data access. With good reason. SQL remains the most expressive, complete, performant way of interacting with relational databases, and relational databases aren't going away anytime soon.

How, then, is it possible that we still see, all over the place, SQL queries constructed with string methods. This is a language. That can be parsed, validated and tested, but not if it's inside double quotes. How would we be using SQL in C# applications if they really wanted us to be using SQL?

You only need to ask the question to answer it: SQL should be in its own file, without exception. For performance, the file should be compiled into the application, and accessed as a manifest resource stream. Accessing query results with DataReader is painful, error prone and hampers testing, so naturally we want POCOs with proper datatypes and meaningful property names. Once we have the query, we can infer the types of the input parameters and the structure of the POCO, so generating the ADO code that takes your inputs, executes your query and fills your POCOs is monkey's work. These days, we get computers to do monkey's work...

tldr; What we want is a Visual Studio extension that takes your SQL, validates it against your DB, then generates the class that runs it, and the POCO for the results.

Glad you asked, it's over here.

Image 1 Edit your queries in a SQL window, connected to the DB. Syntax validation, Intellisense, stepwise debugging, test runs.

 

 

Image 2 Every time you save, QueryFirst (re)generates the wrapper class that runs your query and the POCO for the results.

 

 

 

 

Image 3 Executing your query is just a method call, returning a POCO. No connection, no command, no reader. No dynamics, end to end type safety. Intellisense everywhere.

 

 

Using this thing is a revelation. The reward to effort payoff is so great I have to call it a discovery more than an invention. Your SQL lives. It's validated, easy to read and easy to maintain. Visual Studio helps out with Intellisense for tables and columns, query execution plans, executing the query directly in the editor window, etc. Then, as soon as you save, your query is discoverable in code. You have Intellisense for the input parameters and the results, and all the advantages of strong typing without ever having to worry about a type. The love flows straight out of the DB and into your application. And if you change your DB schema, you can retest all queries and regenerate all wrapper classes. If a query throws errors, the wrapper class won't compile. If your app code references a column that's no longer present in the result set, the compilation error points to the line in your code that has the invalid access.

One sure sign of the poor status of SQL is that we frequently jump through hoops to do stuff in C# that's trivial if you do it directly in SQL. Dynamic parameter lists and pagination are just two common examples. Dynamic parameter lists happen when you only know at runtime which columns are to be filtered on. It's not uncommon to see this dealt with in C# with "if" statements breaking up the composition of the SQL. Nightmare. How much simpler to just do this?

SQL
select * from table1
where (col1 = @param1 or @param1 is null)
and (col2 = @param2 or @param2 is null)
and (col3 = @param3 or @param3 is null)
OPTION (RECOMPILE)

The OPTION RECOMPILE is important because SQL Server will actually optimize the query differently depending on which parameters are supplied and even what values they contain. This isn't stuff we should run away from. This method is not ideal for all cases, and there are plenty of other options here.

We should get to know SQL again, and we should be intolerant of all the stuff you have to wade through to use it.

Using the Code

Download and install the VSIX here. You will need to create a connection string, QfDefaultConnection, in your app or web.config. For the runtime datasource, create a class QfRuntimeConnection with a static method GetConnectionString(). Create your queries using the QueryFirst template, filed under "Visual C# items".

History

No known issues, but this is an alpha. Your comments are keenly sought!

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
France France
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionA couple of questions Pin
IgorPLN30-Jun-16 3:24
IgorPLN30-Jun-16 3:24 
AnswerRe: A couple of questions Pin
bbsimonbb30-Jun-16 4:25
bbsimonbb30-Jun-16 4:25 
GeneralRe: A couple of questions Pin
IgorPLN30-Jun-16 4:43
IgorPLN30-Jun-16 4:43 
GeneralRe: A couple of questions Pin
bbsimonbb30-Jun-16 6:08
bbsimonbb30-Jun-16 6:08 
GeneralRe: A couple of questions Pin
IgorPLN30-Jun-16 6:43
IgorPLN30-Jun-16 6:43 
GeneralRe: A couple of questions Pin
bbsimonbb30-Jun-16 11:32
bbsimonbb30-Jun-16 11:32 
GeneralRe: A couple of questions Pin
IgorPLN30-Jun-16 20:48
IgorPLN30-Jun-16 20:48 
GeneralRe: A couple of questions Pin
bbsimonbb30-Jun-16 22:05
bbsimonbb30-Jun-16 22:05 
It all depends on how complicated things are going to get. The whole point of my tool is to try and achieve a clean separation of concerns. Putting c# concerns into an untested, unvalidated string in the sql is starting out down the same bad road, but then these border areas are always going to be tricky. To start with, it will feel terrific though!

Another option might be to not generate properties that are already defined in the partial class. So a dev could cut a property from the generated class, paste it into the partial, and add the annotations. The annotations would then be syntax validated n all. There's the risk that the property might not be filled if the query changes, but that might be acceptable?
GeneralRe: A couple of questions Pin
IgorPLN30-Jun-16 22:21
IgorPLN30-Jun-16 22:21 
GeneralRe: A couple of questions Pin
bbsimonbb30-Jun-16 22:36
bbsimonbb30-Jun-16 22:36 
Questionvsix Not install in VS2013 Community Pin
Anu Prakash Iyyadurai29-Jun-16 22:49
professionalAnu Prakash Iyyadurai29-Jun-16 22:49 
AnswerRe: vsix Not install in VS2013 Community Pin
bbsimonbb30-Jun-16 2:12
bbsimonbb30-Jun-16 2:12 
GeneralRe: vsix Not install in VS2013 Community Pin
Anu Prakash Iyyadurai30-Jun-16 19:48
professionalAnu Prakash Iyyadurai30-Jun-16 19:48 
GeneralThanks Pin
Anu Prakash Iyyadurai29-Jun-16 22:39
professionalAnu Prakash Iyyadurai29-Jun-16 22:39 
QuestionLooks interesting... Pin
R. Giskard Reventlov29-Jun-16 12:19
R. Giskard Reventlov29-Jun-16 12:19 
QuestionDominic Strauss-Kahn? Pin
rrotstein27-Jun-16 8:15
rrotstein27-Jun-16 8:15 
AnswerRe: Dominic Strauss-Kahn? Pin
bbsimonbb27-Jun-16 11:42
bbsimonbb27-Jun-16 11:42 
GeneralRe: Dominic Strauss-Kahn? Pin
rrotstein27-Jun-16 17:04
rrotstein27-Jun-16 17:04 
GeneralRe: Dominic Strauss-Kahn? Pin
bbsimonbb28-Jun-16 21:47
bbsimonbb28-Jun-16 21:47 
GeneralMy vote of 5 Pin
r_hyde24-Jun-16 16:31
r_hyde24-Jun-16 16:31 
GeneralRe: My vote of 5 Pin
bbsimonbb26-Jun-16 22:39
bbsimonbb26-Jun-16 22:39 
QuestionGood, but... Pin
Dewey24-Jun-16 10:02
Dewey24-Jun-16 10:02 
AnswerRe: Good, but... Pin
bbsimonbb26-Jun-16 22:32
bbsimonbb26-Jun-16 22:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.