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

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?

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.

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.

Filed under: softwareengineering - @ 09:55

Thẻ: .net, design-patterns

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

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

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);            

    }
}

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.

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

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

Filed under: softwareengineering - @ 09:55

Thẻ: .net, design-patterns

Thiết kế website giá rẻ

Danh mục

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

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