Whats an Ideal way to pass rules/options to methods that build SQL

Question

We're trying to come up with a way to handle code that builds dynamic SQL for our application, which is very database centric. Things like Linq to SQL and Entity Framework are out of the question, so please no suggestions of that sort, our path has been defined for us.

Our main goal is seperation of the SQL from the rest of the code (business logic etc).

Right now, we're thinking of using extension methods to house the SQL, and coming up with some sort of configuration object that developers can use to define how the query should be built (dictated by business logic, outside of the Extension class)

So for example, I have some prototype code below. The developers would setup the Configuration Struct (as per business rules), and pass that to GetSQL.

public static class LocationSql2Extension
{
 public enum SelectFields
 {
 Default,
 DefaultAnd_Description_LocationSize
 }
 public enum FilterBy
 {
 Default,
 DefaultAnd_Description
 }
 public enum OrderBy
 {
 Default, 
 DefaultAnd_LocationType_LocationID_Description
 }
 public struct Configuration
 {
 public SelectFields SelectFields;
 public FilterBy FilterBy;
 public OrderBy OrderBy;
 }
 public static string GetSQL(this LocationService2 service, Configuration config)
 {
 string sqlSelectFields = string.Empty;
 string sqlWhere = string.Empty;
 string sqlOrderBy = string.Empty;
 if (config.SelectFields == SelectFields.DefaultAnd_Description_LocationSize)
 {
 sqlSelectFields = ", Description, LocationSize";
 }
 if (config.FilterBy == FilterBy.DefaultAnd_Description)
 {
 sqlWhere = "WHERE L.Description = ?";
 }
 if (config.OrderBy == OrderBy.DefaultAnd_LocationType_LocationID_Description)
 { 
 sqlOrderBy = ", L.LocationDescription";
 }
 string sql = string.Format(@"
 SELECT L.LocationType, L.LocationId
 {0}
 FROM Location AS L
 INNER JOIN GroupLoc AS GL
 ON L.LocationType = GL.LocationType 
 AND L.LocationId = GL.LocationId 
 AND GL.SecLocLevel >= 4 
 AND GL.GroupId = ?
 {1}
 ORDER BY L.LocationType||L.LocationId
 {2}
 ", sqlSelectFields, sqlWhere, sqlOrderBy);
 return sql;
 } 
}

and to call the above code, we have this:

public class LocationService2
{
 public string BuildSql()
 {
 var config = new LocationSql2Extension.Configuration();
 //TODO: Add Business Logic to drive these values
 config.SelectFields = LocationSql2Extension.SelectFields.Default;
 config.OrderBy = LocationSql2Extension.OrderBy.DefaultAnd_LocationType_LocationID_Description;
 config.FilterBy = LocationSql2Extension.FilterBy.DefaultAnd_Description;
 return this.GetSQL(config); 
 }
}

Developers would be free to setup the Enum's any way they like, and this is just a simple example. We're not comfortable with this Enum/Struct approach, and are looking for something more generic thats easy for developers to work with, but isn't so generic that it won't catch any errors at design time. This is a simple example, and we have some very large and complex queries in our system. Any suggestions?

• mgw854: I don't know that such a thing exists. If you want design-time correctness to be guaranteed, you will pay for it in a lack of flexibility. A more generic approach reduces the ability to shape the design into only correct solutions.
• Brett Emerson: I wouldn't say we want a gaurantee of design-time correctness, just that we have a preference for a design that offers as much of it as possible.
• Euphoric: You are not allowed to use EF or LINQ to SQL, so you decided to reimplement it yourself? Maybe pushing a little bit more to allow higher ups to use EF to save the time and effort would be much easier.

Answer 1

What you are saying is that you can't use a well supported ORM so you are trying to invent your own limited ORM to compensate. That isn't going to go well. Performance-wise the modern ORMs stand up very well and they are fluent enough to let you do most operations you can do with a database server in raw SQL.

That said, if you want to go down this sort of path you would be more successful letting SQL be SQL and exposing a logical API to your codebase. What that means from a practical perspective is to not build an object like your example that exposes generic data methods but rather expose query builders that are a bit more meaningful. Internally you could build some helper classes and base classes to handle some of the grunt work of sql generation but in general if you are going to hand build the SQL you might as well take advantage of the fluency of SQL and hand build the SQL. This isn't particularly scalable but if the domain is well defined you can get away with it.

For what it's worth we often take this sort of query definition approach and wrap it around an ORM so our client code is relatively unaware of the database mechanics behind the curtain.

• Brett Emerson: This is pretty much what we ended up doing. We found the design I showed had too many flaws to be practical. We built a couple of very simple abstract classes that allowed us to define "base" SQL templates and the dynamic SQL in a sort of configuration class. We then use the business logic to apply the dynamic SQL to the template. Basically, they help us organize the code a little better. We also added some logging and validation methods, which is only thing truly useful out of all this. We’re looking at integrating dapper.NET with our DAL to give us a very light ORM.

Answer 2

I see you using enums, however it would appear you'd be passing collections for things like fields/database columns.

First you will need to define a collection that makes up the view - [database name]. [schema].[table name].[column name], which is condensed as appropriate into an alias at the column name level. Your class should validate that the alias names are unique. Since this view has various rules for joins, you'll have a collection of join expressions. Your class will have to verify that these aren't redundant and validate to actual database objects.

Once your view is defined you'll have a collection of 'where' expressions, which may be nested. Therefore any given element in the collection may have it's own collection. You will have to navigate to the terminal nodes and compose the SQL in bits, then assemble them with appropriate parens as you travel back toward the root.

Your 'order by' is a collection with a forced ranking order, listing the aliases according to that order.

This is simplified, at least in the sense that you don't have embedded Selects, and you aren't using Group By or Having. In those situations you would have to make your entire structure recursive.