application design problem

  • Thread starter Thread starter Saso Zagoranski
  • Start date Start date
S

Saso Zagoranski

hi,

this is not actually a C# problem but since this is the only newsgroup I
follow I decided
to post my question here (please tell me where to post this next time if you
think this post shouldn't be here).

I have two design questions:
1. what is the correct (or best) way to include database queries into the
code if you plan on
supporting multiple databases. Let's say I have an application that will
support SQL server 2000 and Oracle (and maybe even some other database).
Where would I store these queries (there could be a LOT of them throughout
the entire application)? The queries could be simple, complex, with
parameters, INSERT, UPDATE, DELETE statements.... you get the picture :)

2. How to design an application with an extendable user interface? Here is a
possible scenario:
I make an application for 3 people. After a while, one is really happy with
the application and would like to have an upgrade and add just a simple text
box (the upgrade could also require another field in the database) but the
other
2 clients don't want that textbox. Instead, one of those 2 clients wants 2
different fields on the form.
How to design the application from the beginning to be prepared for these
kinds of changes in the future?
For a simple application I could make different copies of the application
but even for this simple example I would
have 3 different applications - so this solution sound REALLY bad and would
probably turn into a maintainance nightmare in a few months.
I could also have this:
if ( client == "client1") { // show this textbox };
but again this doesn't seems very good - it would lead to a lot of ifs in
the code.

Any ideas?

thanks,
saso
 
Hi,

for your second question

I dont think its a good idea to put all your client spcific logic in UI.
If tomorrow you get new client then again you have to change your code and
soon it will be big headache to maintain that kind of code.

Instead I will suggest that you create different Usercontrol for different
client. By doing that all your Client specific logic will be in UserControl
only. then at runtime you check which client is active and u load that particual
UsreControl.

By doing that your Form will be clean and neat. If you get new client you
just create new Usercontrol and that all.

now you can also simply this problem. Because soon you will have N number
clients and N*N1 number of Usercontrols in your assembly and there is no
need of giving X customer, Y cusomer's Usercontrol

so you put all your X Customer UserControls in One Assembly. Y cusomer's
UserControl in another Assembly and pass them only requried Assemblies. Doing
that you dont give them what they dont need.


If you have heard about Provider Model Pattern then you can use that Pattern
to solve this kind of problem. Will make your UI easy to maintain and will
be very extensible.


You can use similar logic for your First question as well. Use different
assemblies for different Databases. have common Interface so all of them
implements same methods.

Have a Look at Provider Model Pattern. Basically its a pattern for this kind
of situations

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspnet/html/asp02182004.asp


Let me know if I am not clear in explain it.


Mihir Solanki
http://www.mihirsolanki.com
 
Thanks for the reply Mihir!

I've looked at the provider model.

I do have one more question.
Let's say I have a Form with two buttons:
When I would click Button1 this sql would execute: "select * from table1"
and when I would click Button2: "select * from table"
(these are just simple examples, equal on Oracle and SQL server. More
complex examples would
have a different syntax!)

I would now have a global Provider object:
Provider p = new SqlProvider();
or
Provider p = new OracleProvider();

in button1_Click I would have this code:
string sql = p.GetStatement(?????);

Here is my question. How and where would I store the sql statements. Have a
table of all SQL statements
in each provider and access them by some form of id?
And write:
string sql = p.GetStatement("Form.button1_Click"); ??

What do you think?

thanks,
saso

ps: I also have questions about my 2. question (regarding different user
controls) but I'll ask that later, when we solve this "problem" :)
 
yes,good question. There are couple of way

1. You create a Resource file .resx (i.e sqlserver.queries.resx and oracle.queries.resx)
and store all the stored procedures in it
for each stored procedure you use Handle something like

for SQL Server
GetEmployee = "Select * from Employees";
GetCustomer = "Select * from Customers";
GetEmployeesById = "Select * from Employees WHERE EmployeeId = @EmployeeID"

For Oracle (with any changes required to work for oracle)
GetEmployee = "Select * from Employees";
GetCustomer = "Select * from Customers";
GetEmployeesById = "Select * from Employees WHERE EmployeeId = @EmployeeID"

and then in button click you simple call

Provider p = new (Whatever provider);
p.GetEmployees(ResourceManager.GetResourceString("GetEmployees") (check exact
syntax of Resource Manager)

something liek that
so for each data provider you have resource file with queries specific to
that provider

2. There is another way (which we are using is in my company)
Create a Wrapper Class which provides static methods

it will have something like this

class DataManager{
Provider provider = null;

public DataManager(){
// in constructor you assign provider variable a default data provider
}
public static DataTable GetEmployees(){
// you call provider's method
return provider.GetEmployees();

}

}

your provider abstract class will be something like this
class abstract DataProvider{
public abstract DataTable GetEmployees();
public abstract DataTable GetEmployeeById(int id);

}

and your sql server provider will be something like this

class SqlManager{


public SqlManager(){

}
public static DataTable GetEmployees(){
// you call provider's method
DataSet ds = new DataSet();
// write code to fill this dataset
// you can use hardcoded Queries here
// or use resource file to get queries
// or use stored procedures
// and then return datatable
return ds.Table[0];
}
public static DataTable GetEmployeeById(int id){
// somecode to get data
}
}

your oracle provider will be something like this

class oracleManager{

public OralceManager(){

}
public static DataTable GetEmployees(){
// you call provider's method
DataSet ds = new DataSet();
// write code to fill this dataset
// you can use hardcoded Queries here
// or use resource file to get queries
// or use stored procedures
// and then return datatable
return ds.Table[0];
}
public static DataTable GetEmployeeById(int id){
// somecode to get data
}

}


here you can see in button click you just simpllyt calling DataManager.GetEmployees()
method which doesnt not need to know anythig about query. Your datamanager
is just a wrapper, again that class doesnt need to know anythigtn about how
to call database. then you got your speecific provider which know how to
call that particular database and know how it is going to get stored procedured.
OracleManager and SqlManager doesnt need to have same thing. sqlManager can
have stored procedure and oracle can have hardcoded queries. ..I mean they
can have seprare implementation.

basically by doing so your UI doens need to know anything about your database
and database layer doent need to know about rendering UI. Easy to maintain
and extend.

hope u got my point

again if I am not clear. .. let me know

Mihir Solanki
http://www.mihirsolanki.com
 
ahh .. I forogt something

SqlManager and Oracle manager both derive from DataProvider class. so code
is something like this

class abstract DataProvider{
public abstract DataTable GetEmployees();
public abstract DataTable GetEmployeeById(int id);

}

class SqlManager : DataProvider {

public SqlManager(){

}
public static DataTable GetEmployees(){
// you call provider's method
DataSet ds = new DataSet();
// write code to fill this dataset
// you can use hardcoded Queries here
// or use resource file to get queries
// or use stored procedures
// and then return datatable
return ds.Table[0];
}
public static DataTable GetEmployeeById(int id){
// somecode to get data
}
}

your oracle provider will be something like this

class oracleManager : DataProvider {

public OralceManager(){

}
public static DataTable GetEmployees(){
// you call provider's method
DataSet ds = new DataSet();
// write code to fill this dataset
// you can use hardcoded Queries here
// or use resource file to get queries
// or use stored procedures
// and then return datatable
return ds.Table[0];
}
public static DataTable GetEmployeeById(int id){
// somecode to get data
}

}

I am preparing a small demo for u of it. .. will send u soon

Mihir Solanki
http://www.mihirsolanki.com
 
You also could place the stored procedures into the database itself, make
specific sp's for each type of database but call them the same. Than your
data layer only needs to know how to connect to the different databases and
execute the stored procedure. Just have your installer execute a different
sql script for the different databases. Also nice is that now your databases
knows what kind of query's you want it to excecute and so it can optimize
for that.

-Mark
 
thanks again for all the help...

here's my question about the way that your companies uses this model.

Doesn't your DataProvider abstract class have LOTS of methods?
The method seems very appealing but it seems that I would soon get lost in
all the methods.

What do you think?
 
Yes ofcourse there is lots of methods in that dataprovider class but that
is the only way you can seperate your UI and Data access layer.

Yes in starting you may find that for a single one method you are implementing
4 different methods but after sometime you will really feel that your life
became easy by doing such thing. Easy to maintain, easy to write code (2
developers can work at same time on 2 different model. i.e one for sql and
one for oracle), easy to extend (i.e if somebody come tomorrow and say I
want support for MySql, then you will say its half a day job as you will
have working UI and you just need to implement MySqlDataProvider, just few
methods and most of them copy and paste from your existing providers.

Not only my company but there are lots of companies they hvae similar models.
Even look at microsoft Asp.net 2.0. It is full of provider model.



Mihir Solanki
http://www.mihirsolanki.com
 
Ok... I understand.

And the other statements would be the same?
e.g. INSERT into table X (col1,col2) values (...)

so you would have
DataProvider.InsertIntoSomeTable(string value1, string value2)
?
 
Thats right,

UI or DataProvider doesnt need to know about database or queries or anything.
they just provide functionality. Actual action is depends on your Provider
which provide service for a particual database or data store.

ya so u need something like that
DataProvider.InsertIntoSomeTable(string value1, string value2)
or update
DataProvider.UpdateSomeTable(and required parameter inputs)
or delete
DataProvider.DeleteFromSomeTable(int id (or required inputs))



Mihir Solanki
http://www.mihirsolanki.com
 
Back
Top