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 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"
:)
"Mihir Solanki" <mi**********@hotmail.com> wrote in message
news:19*************************@msnews.microsoft. com...
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/de...ary/en-us/dnas
pnet/html/asp02182004.asp
Let me know if I am not clear in explain it.
Mihir Solanki
http://www.mihirsolanki.com 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
