Designing a Cross-Technology SQL Generator

[noobman]

Hi Guys,

I wanted to see if I could get your input on a good way to re-design this solution (ASP.NET Web App)

We currently have an application that allows users to use a web interface to build "drag-n-drop" select queries to be run against database tables or views. It supports several features like nested AND/OR construction, grouping/pivoting, and aggregation functions.

Currently this data is stored into a data structure and passed to something we call the QueryGenerator. The QueryGenerator is hard-coded to provide SQL output today, but we're getting ready to expand to multiple databases.

What I was thinking of is building several "provider" classes that inherit an Interface (which would dictate methods required to handle base select, joins, ordering, distincts, groupings, etc) and use a case statement to instantiate and generate the underlying SQL based on the database type (IE MsSqlProvider: ISqlProvider, OracleProvider:ISqlProvider, etc)

I'm thinking the code would look something like this:

public string BuildQuery(ParamObject po)
{
   string result;
   switch (o.DatabaseType)
   {
   case "MSSQL":
       var generator = new MsSqlProvider();
       result = generator.GenerateQueryFromParams(po);
       break;
   case "Oracle":
      var generator = new OracleProvider();
      result = generator.GenerateQueryFromParams(po);
      break;
   } 

   return result;
}

Interface ISqlProvider() 
{
   string GenerateQueryFromParams(ParamObject po);
   // Haven't yet reflected on how the below would actually work
   void HandleSelect();
   void HandleAggregate();
   void HandleJoin();
   void HandlePivot();
}

Can you think of a better method of implementation for this? Are there any holes you can poke in the design? Any other considerations here I might be missing? Any patterns I could be using instead?

 

[PTNL]

Your idea could be an aspect of the bigger picture implementation. I suspect something along the repository pattern or the database factory pattern would be worth reading to give some ideas of abstraction.

Some general thoughts...

1 - Think about the returned datatypes between different vendor products that you plan on supporting. You may need a DTO class to abstract the type conversions into something more consistent for the consuming methods/functions. (The dynamic structure of the final resultset does add another layer worth considering.)
2 - Would you need or allow joins/compares to be done across databases that are not the same? (i.e. join a SQL Server table against a MySQL or Oracle table) Is this even allowed from a network security/access/permissions perspective? This affects where the meshing and evaluation of data happens, and could affect realized performance of certain queries.
3 - Is Entity Framework (currently v6) a better fit for (much of) what you are trying to do? If you only plan on supporting MS SQL Server and Oracle, then perhaps it is not worth the cycles. However, if your intended pool includes many of the supported vendors, then making a wrapper class to dynamically build EF queries is worth considering.

 

[v6maro]

I would build a new drag and drop system to build linq expressions dynamically based on my data model which convieniently is generated by entity framework. I would then use these expressions at run time and pass them to my entity framework data layer and push the results back to the user.

What you're implimenting is a little 'old school' and is technically harder / more failure prone if you ask me.

Pro Tip: save your expressions to a database table, then compile/cache them at run time. Load cached assemblies on the fly if they have not been modified. Fun stuff.

 

[Chris_Lonardo]

I would probably err on the side of using LINQ, but failing that (and also failing my lack of familiarity with the Database Factory, which looks sort of interesting), I have a few ideas at a high level.

You could define a class with a string member for the piece of the query being added/removed through the user interface, as well as some other identifier to help you keep track of what goes where. Create a list of objects of this class. User adds, removes, or reshuffles the query, and you adjust the components of your list. Finally, generate the query text with StringBuilder (join the list items together in an appropriate order), and select to a DataTable using SqlDataAdapter for maximum flexibility.

 

[noobman]

Thanks for the advice! What I have so far is a connection engine that provides the tools for querying the database.

I'm interested in using the Entity Framework, but from my experience this is something that needs to be configured by the developer.

What we want to be able to do with this solution is access an administrative interface at runtime where we can add database and connection details, then have the user be able to go into their interface and see the new one with the ability to connect.

Is that possible with Entity? My understanding was that I would have to update the Entity Framework to generate the new metadata classes then recompile and re-deploy the app.

 

[PTNL]

You wouldn't be updating the EF model, you would only be updating the query executed against the database (likely as LINQ). EF would be flexed to give strongly-typed objects, with corresponding column names. (Aliases could also be used, if it helps the UI or user understanding.) Basically, the EF model would be your foundation for exposing query-building elements in the UI.

But please read through my previous post for some questions and caveats.

Edit: On a somewhat related note, this thread reminded me of LINQPad. If you aren't familiar, it's worth checking out for your own knowledge.

 

[Wiseguy2001]

You can use Entity Framework to create layered queries to build the statements, lookup fluent APi's (strangely enough the best example on that page is written in PHP ).

The trouble with SQL is that it gets very complex fast, as soon as you start adding joins and grouping into the mix. If you are going to make your own, have a look at the strategy pattern to help you, which basically knows the order to execute the commands, but doesn't doesn't know the details (such as how a where clause is constructed), this also means you can override class to handle the subtle differences between of each platform. You really do need to be careful how you design software like this, as it can get real messy, fast!

Goodluck!