Click here to Skip to main content
15,867,594 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

 
QuestionODBC Support Pin
AdrianKole31-Oct-18 15:03
AdrianKole31-Oct-18 15:03 
SuggestionSource Size Pin
aodennison25-Aug-17 4:03
aodennison25-Aug-17 4:03 
BugNot working - Codebase faulty? Pin
pt140130-Jun-16 12:17
pt140130-Jun-16 12:17 
GeneralRe: Not working - Codebase faulty? Pin
bbsimonbb30-Jun-16 19:55
bbsimonbb30-Jun-16 19:55 
GeneralRe: Not working - Codebase faulty? Pin
bbsimonbb30-Jun-16 22:08
bbsimonbb30-Jun-16 22:08 
GeneralRe: Not working - Codebase faulty? Pin
pt140130-Jun-16 23:42
pt140130-Jun-16 23:42 
No problem, it turns out that the .sql file was missing the final few lines where QfNamespace and QfClassName are declared. I'm not sure if I'd deleted them accidentally, but rebuilding & reinstalling the extension fixed it.

There are a couple of other issues though:-
* In QueryFirstTemplate.vsTemplate, you specify
C#
Microsoft.VisualStudio.Web.Application, Version=11.0.0.0
. This is (I believe) the VS2012? specific version, and causes runtime errors when VS2012 is not installed (I have VS2015). You should remove the Version=11.0.0.0 from the line, it's then OK.
  • There is an issue in the generated code with the use of
    C#
    yield return
    in the
    C#
    Execute(string search, SqlConnection conn)
    method.
    Because the data is lazy-loaded, the connection has already been disposed by the time the data is read from the database, causing runtime errors.

GeneralRe: Not working - Codebase faulty? Pin
bbsimonbb1-Jul-16 0:00
bbsimonbb1-Jul-16 0:00 
GeneralRe: Not working - Codebase faulty? Pin
pt14011-Jul-16 0:14
pt14011-Jul-16 0:14 
GeneralRe: Not working - Codebase faulty? Pin
bbsimonbb1-Jul-16 3:07
bbsimonbb1-Jul-16 3:07 
GeneralRe: Not working - Codebase faulty? Pin
bbsimonbb5-Jul-16 5:18
bbsimonbb5-Jul-16 5:18 
GeneralRe: Not working - Codebase faulty? Pin
pt14017-Jul-16 5:38
pt14017-Jul-16 5:38 
GeneralRe: Not working - Codebase faulty? Pin
pt14017-Jul-16 9:44
pt14017-Jul-16 9:44 
GeneralRe: Not working - Codebase faulty? Pin
bbsimonbb7-Jul-16 12:23
bbsimonbb7-Jul-16 12:23 
GeneralRe: Not working - Codebase faulty? Pin
pt14017-Jul-16 22:28
pt14017-Jul-16 22:28 
GeneralPull request Pin
pt140110-Jul-16 8:46
pt140110-Jul-16 8:46 
GeneralRe: Pull request Pin
bbsimonbb10-Jul-16 22:10
bbsimonbb10-Jul-16 22:10 
GeneralRe: Pull request Pin
bbsimonbb10-Jul-16 22:22
bbsimonbb10-Jul-16 22:22 
GeneralRe: Pull request Pin
pt140110-Jul-16 22:44
pt140110-Jul-16 22:44 
GeneralRe: Pull request Pin
bbsimonbb10-Jul-16 23:25
bbsimonbb10-Jul-16 23:25 
GeneralRe: Pull request Pin
pt140111-Jul-16 4:40
pt140111-Jul-16 4:40 
Suggestionuse "using" for disposable objects Pin
sx200830-Jun-16 8:46
sx200830-Jun-16 8:46 
GeneralRe: use "using" for disposable objects Pin
bbsimonbb30-Jun-16 11:34
bbsimonbb30-Jun-16 11:34 
GeneralGreat idea, well executed Pin
pt140130-Jun-16 6:57
pt140130-Jun-16 6:57 
QuestionMerde! Someone changed my schema! Pin
RobBohn30-Jun-16 5:20
RobBohn30-Jun-16 5:20 
AnswerRe: Merde! Someone changed my schema! Pin
bbsimonbb30-Jun-16 6:10
bbsimonbb30-Jun-16 6:10 

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.