how useful DbCommon namespace?

  • Thread starter Thread starter ZenRhapsody
  • Start date Start date
Z

ZenRhapsody

I am working on an application where the end user will select the type of
datastorage to use (SQL, Oracle, Access, even Excel for some readonly data,
and maybe more providers )
The application reads/writes data and even must be able to create tables in
databases (or even create databases) as needed.

I am trying to use the new DbCommon namespace in ADO 2.0 to keep the
database code as generic as possible. However, as we all know, there are
many nuances between the common data providers that make this difficult.
For example date formats in queries are different (JET, at least before
Access 2003, requires '#' delimiters), cultural settings can work
differently with Access/Excel JET (m/d/y or d/m/y). Syntax for creating
tables are also different (nvarchar(1000) crashes in JET because text fields
in JET are limited to 255 characters so you must use MEMO).

So, while I can write a lot of DB independent code, I need specifics in many
cases. While working to this end, I can't even find a simple way to query
a connection to see who it's driver is!! I've used if (conn is
System.Data.SqlClient.SqlConnection) ,is there a better way? I've also
written query formatters for date parameters.

Who out there has a little more experience to share? Some tricks, pitfalls,
links to good provider comparision imformation, anything would be helpful!
 
Hi,

First, you should use parameters and half of your problems would go away
(cultural settings).
Next, you should really look into an ORM product that abstracts the database
for you (i.e. check out http://www.llblgen.com/) and helps you in other
areas, too.
 
Thanks...
You are correct about parameters taking care of cultural settings. However,
going to parameters opens another can of worms... such as how to mark and
name parameters? That is different for every data provider as well...


Miha Markic said:
Hi,

First, you should use parameters and half of your problems would go away
(cultural settings).
Next, you should really look into an ORM product that abstracts the
database for you (i.e. check out http://www.llblgen.com/) and helps you in
other areas, too.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

ZenRhapsody said:
I am working on an application where the end user will select the type of
datastorage to use (SQL, Oracle, Access, even Excel for some readonly
data, and maybe more providers )
The application reads/writes data and even must be able to create tables
in databases (or even create databases) as needed.

I am trying to use the new DbCommon namespace in ADO 2.0 to keep the
database code as generic as possible. However, as we all know, there are
many nuances between the common data providers that make this difficult.
For example date formats in queries are different (JET, at least before
Access 2003, requires '#' delimiters), cultural settings can work
differently with Access/Excel JET (m/d/y or d/m/y). Syntax for creating
tables are also different (nvarchar(1000) crashes in JET because text
fields in JET are limited to 255 characters so you must use MEMO).

So, while I can write a lot of DB independent code, I need specifics in
many cases. While working to this end, I can't even find a simple way
to query a connection to see who it's driver is!! I've used if (conn is
System.Data.SqlClient.SqlConnection) ,is there a better way? I've also
written query formatters for date parameters.

Who out there has a little more experience to share? Some tricks,
pitfalls, links to good provider comparision imformation, anything would
be helpful!
Click to expand...
Click to expand...
 
ZenRhapsody said:
Thanks...
You are correct about parameters taking care of cultural settings.
However, going to parameters opens another can of worms... such as how to
mark and name parameters? That is different for every data provider as
well...
Click to expand...

Yep but even sql syntax might be different among different databases.
So, your best bet are ORMs and I recommend you to take a look at them...
 
Back
Top