how useful DbCommon namespace?

[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!

 

[Miha Markic [MVP C#]]

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.

 

[ZenRhapsody]

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...

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/

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!

 

[Miha Markic [MVP C#]]

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...

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...