Working with different kinds of databases

  • Thread starter Thread starter Dino Buljubasic
  • Start date Start date
D

Dino Buljubasic

Hi,

How can I make my application work with SQL Server, Oracle, MySql,
etc?

_dino_
 
Use the provider independant interfaces. example, rather than using
System.Data.SqlConnection use System.Data.IDbConnection. SqlConnection
inherits from the IDbConnection interface. You could also use this to
create your own Connection class that could be provider independant.

Hope that helps,
Darren Kopp
 
Something that I have found when working with IDConnection is that SQLClient
behaves a little differently than OLEDB and ODBCDB. When using and
IDCommand object, you have to dispose it if each use, while SQLCommand is a
bit more forgiving and seems to clean itself up. It took me forever to
figure out was was going on, and I believe there is a post in here about it.

Bottom line is, when using generic objects, clean up as much as you can when
you can. It is good practice to do with all of your objects, yes. I just
was unaware of when to use Dispose.

HTH.
 
Thats a hard goal to acheive.

You can try using something similar to the Dbproviderfactory model in .NET
2.0 - but even then you are stuck with 70% of database specific code - i.e.
PLSQL vs. TSQL.

You have to segregate everything into an as thin data layer as possible and
then write different datalayers for different .NEt data providers - thats
about the only way.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
 
WHOAAA !! Here's a better rule "If you see Dispose, call it !!".

Not calling Dispose just bcause you are using SqlClient is not a recommended
practise.
 
Sahil said:
Thats a hard goal to acheive.

You can try using something similar to the Dbproviderfactory model in
.NET 2.0 - but even then you are stuck with 70% of database specific
code - i.e. PLSQL vs. TSQL.

You have to segregate everything into an as thin data layer as
possible and then write different datalayers for different .NEt data
providers - thats about the only way.
Click to expand...

naa. You can also use a tool which provides a database generic layer
for you which is usable with a lot of different databases ;) (like an
O/R mapper)

FB



--
 
naa. You can also use a tool which provides a database generic layer
for you which is usable with a lot of different databases ;) (like an
O/R mapper)
Click to expand...

Does your O/R Mapper generate db specific queries without having to do any
special treatment for different databases? If so "WOW". It is quite a hard
goal to acheive though - just that you've done all the hardwork.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
 
Sahil Malik said:
Does your O/R Mapper generate db specific queries without having to do any
special treatment for different databases?
Click to expand...

Why not? Assuming it has enough information though.
 
Sahil said:
Does your O/R Mapper generate db specific queries without having to
do any special treatment for different databases? If so "WOW". It is
quite a hard goal to acheive though - just that you've done all the
hardwork.
Click to expand...

Yes. And in the upcoming 1.0.2005.1, you can even define type
converters, so Oracle NUMBER(1,0) (or any other type, just write a few
lines of code) for example can be mapped onto bool, transparently, so
you can have a single code base targeting a set of entities, which have
for example some boolean fields, and on Sqlserver they're mapped onto
bitfields, and on Oracle they're mapped onto NUMBER(1,0). (you can map
any.NET type onto any db type )

:P. This even works cross entity actions, so fetch an entity from
sqlserver, use it and save it into oracle or vice versa (or other db).

It's not that hard really: an O/R mapper knows the meta-data, and the
object state. So with a per-db SQL engine, you can generate any SQL
targeting any db, as long as you have an SQL engine for that database.
:)

Unless you want to use stored procedures of course ;) :P

FB
- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------
------




in >> .NET 2.0 - but even then you are stuck with 70% of database
specific >> code - i.e. PLSQL vs. TSQL.data >> providers - thats about the only way.
Click to expand...

--
 
....
....
....

???

Yes. And in the upcoming 1.0.2005.1, you can even define type
converters, so Oracle NUMBER(1,0) (or any other type, just write a few
lines of code) for example can be mapped onto bool, transparently, so
you can have a single code base targeting a set of entities, which have
for example some boolean fields, and on Sqlserver they're mapped onto
bitfields, and on Oracle they're mapped onto NUMBER(1,0). (you can map
any.NET type onto any db type )

:P. This even works cross entity actions, so fetch an entity from
sqlserver, use it and save it into oracle or vice versa (or other db).

It's not that hard really: an O/R mapper knows the meta-data, and the
object state. So with a per-db SQL engine, you can generate any SQL
targeting any db, as long as you have an SQL engine for that database.
:)

Unless you want to use stored procedures of course ;) :P

FB
Click to expand...
 
Back
Top