Best Practices for Data Access

  • Thread starter Thread starter KA Kueh
  • Start date Start date
K

KA Kueh

Dear all,

I need to design an smart client application that can run on mulitple and
plugable backend like MS SQL, Oracle or MySQL. Also the smart client is
accessing the database from the internet. What is the best approach?
Thanks.

Regards,
Kueh.
 
Hi kueh,
I need to design an smart client application that can run on mulitple and
plugable backend like MS SQL, Oracle or MySQL. Also the smart client is
accessing the database from the internet. What is the best approach?
Click to expand...

Maybe you can hire a consultant, however don't expect to get a cheap answer
on this in a newsgroup message and for sure you won't get the overal Best
Practice. That simply does not exist.

Cor
 
I don't know if there is a "best" approach, but I'd look at using a
DBProviderFactory in a separated data layer to keep the data layer as
independent of the data as possible.
 
Since you want multiple backends, you will want to set up a factory pattern
over the actual data access, so you can use configuration to set the
database. If you want to simplify things, you can use sprocs with the same
name in each "product" of your factory method, but this will not work for
all databi you may try (yes, I know databi is not a real word), as some do
not support sprocs.

As for how to design it, it can depend on the nature of the application. For
maintainability and extensibility, dofactory.com has a reference
architecture with their framework (pretty inexpensive). There are cases
where you might alter the pattern, however.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************
 
While Greg is right, IMHO this is a tough nut to crack. Any attempt at this
will yield a VERY complex application and programming regimens. Frankly, I
have not seen many (very few in fact) sucessful implementations of this
approach. Most end in failure--overcome by the differences between the
various backends. Consider that you would have to write your applications to
be totally DBMS agnostic--ignoring the features that make one better/more
useful than another. This approach also means you would have N backup
strategies, N management strategies, N sets of stored procedures that cannot
leverage the features of the backend--assuming the backend even supported
SPs. While Oracle and SQL Server both support SPs, they work very
differently. While both are "ANSI-compliant" there are significant
differences. Yes, it's theoretically possible to implement but IMHO probably
N squared more complex to implement than similar but separate applications.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
 
Back
Top