Question about UI layer
I'm designing a very data oriented application where there will only be two physical tiers: the user's machine (with maybe up to 50 users) and a database server. Most of our customers will be using SQL 2005, but some have said they would like to use their existing Oracle db. Therefore, the UI was going to consist of two layers: a Windows.Forms layer and then a .dll containing classes that will marshal data to and fro the db, and take care of any business logic. By having the objects in the .dll implement interfaces, I can basically swap in different .dlls for different backend databases.
The problem is this: our user interface is HIGHLY interactive and customizeable. For example, the Customer List form features your basic databound grid, but we allow the user to select which columns to view, and depending on what columns are shown the resulting SQL query can look very different. In our previous VB6/Access version of this app, I generated SQL queries dynamically depending on what columns were selected, and queried the database right from the form code. Also, in order to save the users "filter", I would simply save the WHERE clause from my generated query.
In the new app, I was hoping to keep all SQL out of the UI part of the application. I was hoping that by keeping all query generation in my data access .dlls, my UI application would be completely db vendor agnostic. But, how do I solve the above mentioned problem? Depending on the user's wishes, I could be joining against one table or seven tables. I could try to write my own, proprietary protocol that the UI can pass to my data access .dlls, and then they parse through it and generate db-specific SQL for, but this seems like a lotta work. Or, I could just include raw SQL in my UI code after all, and just pass the resulting strings to my data access .dlls and have them retrieve the data. The problem with that is that all the tables and field names must be exact across all supported backend databases.
Any and all thoughts would be welcome.
[2063 byte] By [
0to60] at [2007-12-25]
Hi;
I do not see a big problem in what you are saying. The ADO .Net can be used in a way in which it hides the underlaying actual Database engine.
For example, the "OracleConnection" and "SQLConnection" objects both inherits from "DBConnection" object and implement the interface "IDBConnection". Same applies for the "DBCommand" and the "OracleCommand" and "SQLCommand".
Since you need to create your Queries dynamically, you need to consider also the "DBCommandBuilder" which has "SQLCommandBuilder" and "OracleCommandBuilder" as 2 child classes.
How can you use the above libraries to meet your needs?!! Use the Abstract Factory and Facade patterns to create objects of the type you want and isolate your UI layer from the DB Layer. Look at the following code example...
public
abstract class DBObjectsFactory{
protected string strConnection;DbConnection CreateConnection(string strConnection);DbCommand BuildCommand(string strCommand);DataSet ExecuteDataSet(DbCommand command);}
public class SQLObjectsFactory : DBObjectsFactory{
public DbConnection CreateConnection(string strConnection){
this.strConnection = strConnection;return new SqlConnection(strConnection);}
public DbCommand BuildCommand(string strCommand){
return new SqlCommand(strCommand);}
public DataSet ExecuteDataSet(DbCommand command){
// figure something outreturn null;}
}
public class OracleObjectsFactory : DBObjectsFactory{
public DbConnection CreateConnection(string strConnection){
this.strConnection = strConnection;return new OracleConnection(strConnection);}
public DbCommand BuildCommand(string strCommand){
return new OracleCommand(strCommand);}
public DataSet ExecuteDataSet(DbCommand command){
// figure something outreturn null;}
}
public class MyFacadeCLient{
private IDBObjectsFactory factory;public MyFacadeCLient(bool bSql, string strConnection){
if (bSql)factory =
new SQLObjectsFactory();elsefactory =
new SQLObjectsFactory();}
public DataSet GetUsers(){
DbCommand command = factory.BuildCommand("Select * from users");return factory.ExecuteDataSet(command);}
}
Now when you use your "FacadeClient", your client only needs upon initialization which type of Database it will use. After that everything is behind the scene for your client application.
Using the CommandBuilder will help you creating more dynamic queries as in your problem statment. Now you need to design your DBObject factory to fullfill your query needs. Everything then goes to the "FacadeClient" which use abstract types and know nothing about the actual engine it's working with.
A final note, I suggest that you look at the application blocks. Microsoft did provide greate effort in making the library hides the Database engine. They have abstract class called Database from which OracleDatabase and SqlDatabase classes inherit. The library is really useful and will cut-down your effort needed. Take a look and download it at ...http://www.microsoft.com/downloads/details.aspx?FamilyId=5A14E870-406B-4F2A-B723-97BA84AE80B5&displaylang=en
I hope that this helps.
Have you taken a look at using an O/RM tool to abstract away your database calls? For instance nHibernate does an excellent job at what you're talking about. Without a line of SQL in your code, you can create filters, complex joins, and a lot of other constructs that would be difficult to code by hand. In addition nHibernate has it's own custom-made query language called HQL, that does what you spoke of.
nHibernate currently works with SQL 2005 and Oracle (among other DBMSes) and can be swapped on the fly using a configuration file.
The one downside to nHibernate is that it is somewhat tedious to configure the mapping between database and object model by hand. There are graphic tools that help alleviate your pain there. Codus is a O/R generation tool that you can point at a database and it will generate nHibernate mappings and the object model for you (it also supports it's own data mapping technology).
Finally, you definitely want to keep your eye on LINQ (specifically DLINQ), it is how data will be done in the next release of Visual Studio.
from your description it looks like a small Business intelligence tool .. like report generation ....
For db vendor independent thing , u can use the ADO.Nets dbconnection , dbcommand... if there is going to be very less business logic and much of the code is going to just show different query data in the UI .. you can consider the DTO pattern .
Have a DataSet , put all the data from the query and bind it to the grid .. Since the UI data grid is mapped exactly with dataset ,After dataset is filled based on the query..it can be used,.......
DTO pattern is considered antipattern by some ... But its very useful when properly used.....
I dont recommend the following solution but still just check it out - using Anemic Domain Model with a Visitor pattern ... But the Anemic Domain Model is also considered an antipattern But if used properly this can still be a solution .... I know a ten year old application which uses an anemic model.. still going fine.....
thanks
vinothkumar - "every Pattern can become a Antipattern and vice versa , its only how we use it "
