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:
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?
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:
Code:
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?