Click here to Skip to main content
14,689,513 members
Articles » Languages » C# » General
Tip/Trick
Posted 24 Jun 2016

Stats

54.4K views
14 bookmarked

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 2016MIT
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?

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

Share

About the Author

bbsimonbb
France France
No Biography provided

Comments and Discussions

 
GeneralRe: Merde! Someone changed my schema! Pin
RobBohn30-Jun-16 7:29
MemberRobBohn30-Jun-16 7:29 
AnswerRe: Merde! Someone changed my schema! Pin
RobBohn30-Jun-16 8:28
MemberRobBohn30-Jun-16 8:28 
GeneralRe: Merde! Someone changed my schema! Pin
bbsimonbb30-Jun-16 11:48
Memberbbsimonbb30-Jun-16 11:48 
QuestionA couple of questions Pin
IgorPLN30-Jun-16 4:24
MemberIgorPLN30-Jun-16 4:24 
AnswerRe: A couple of questions Pin
bbsimonbb30-Jun-16 5:25
Memberbbsimonbb30-Jun-16 5:25 
GeneralRe: A couple of questions Pin
IgorPLN30-Jun-16 5:43
MemberIgorPLN30-Jun-16 5:43 
Regarding placing DTO's in a different project - I am not a fan of solution-wide app-config, especially given that the same DAL project may be used by many different solutions.
Therefore specifying the name of a target project in a SQL file itself would be far more flexible.
I would also allow specification of a project folder, namespace and POCO class name;

As for the DataAnnotations - these are attributes applied to properties. As such, partial class really does not help here, as properties are generated by your tool.
GeneralRe: A couple of questions Pin
bbsimonbb30-Jun-16 7:08
Memberbbsimonbb30-Jun-16 7:08 
GeneralRe: A couple of questions Pin
IgorPLN30-Jun-16 7:43
MemberIgorPLN30-Jun-16 7:43 
GeneralRe: A couple of questions Pin
bbsimonbb30-Jun-16 12:32
Memberbbsimonbb30-Jun-16 12:32 
GeneralRe: A couple of questions Pin
IgorPLN30-Jun-16 21:48
MemberIgorPLN30-Jun-16 21:48 
GeneralRe: A couple of questions Pin
bbsimonbb30-Jun-16 23:05
Memberbbsimonbb30-Jun-16 23:05 
GeneralRe: A couple of questions Pin
IgorPLN30-Jun-16 23:21
MemberIgorPLN30-Jun-16 23:21 
GeneralRe: A couple of questions Pin
bbsimonbb30-Jun-16 23:36
Memberbbsimonbb30-Jun-16 23:36 
Questionvsix Not install in VS2013 Community Pin
Anu Prakash Iyyadurai29-Jun-16 23:49
professionalAnu Prakash Iyyadurai29-Jun-16 23:49 
AnswerRe: vsix Not install in VS2013 Community Pin
bbsimonbb30-Jun-16 3:12
Memberbbsimonbb30-Jun-16 3:12 
GeneralRe: vsix Not install in VS2013 Community Pin
Anu Prakash Iyyadurai30-Jun-16 20:48
professionalAnu Prakash Iyyadurai30-Jun-16 20:48 
GeneralThanks Pin
Anu Prakash Iyyadurai29-Jun-16 23:39
professionalAnu Prakash Iyyadurai29-Jun-16 23:39 
QuestionLooks interesting... Pin
R. Giskard Reventlov29-Jun-16 13:19
MemberR. Giskard Reventlov29-Jun-16 13:19 
QuestionDominic Strauss-Kahn? Pin
rrotstein27-Jun-16 9:15
Memberrrotstein27-Jun-16 9:15 
AnswerRe: Dominic Strauss-Kahn? Pin
bbsimonbb27-Jun-16 12:42
Memberbbsimonbb27-Jun-16 12:42 
GeneralRe: Dominic Strauss-Kahn? Pin
rrotstein27-Jun-16 18:04
Memberrrotstein27-Jun-16 18:04 
GeneralRe: Dominic Strauss-Kahn? Pin
bbsimonbb28-Jun-16 22:47
Memberbbsimonbb28-Jun-16 22:47 
GeneralMy vote of 5 Pin
r_hyde24-Jun-16 17:31
Memberr_hyde24-Jun-16 17:31 
GeneralRe: My vote of 5 Pin
bbsimonbb26-Jun-16 23:39
Memberbbsimonbb26-Jun-16 23:39 
QuestionGood, but... Pin
Dewey24-Jun-16 11:02
MemberDewey24-Jun-16 11:02 

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.