ADO.NET connection

  • Thread starter Thread starter VBM
  • Start date Start date
V

VBM

I have multiple databases for my application. I want to write generic
ADO.NET code which will connect to all databases. Can somebody tell me
simple example for conection object and Opening connection to SQL Server,
Oracle, SYBASE. Just one function should do all the tasks.

I am confused SQLOLEDB, SQLclient, OldDB ??? etc

thanks
 
For the building general code, you should use OleDB class.

SQLOLEDB is the name of a provider that is used to connect MS SQL Using OleDB

SQLClient is a class name that is used to connect to MS SQL but Via SQL
Client.

If you use OleDB, the code will be general, but the performance might suck.
Still you will get the some database related problems: such as: dbNull,
different string concatenate syntax, call stored procedure and pass
paramters. multi-sql statements.
different table join syntax, etc.

You may have no way to reach such a point to make sure no source code change
to switch one type database server to another.

Bangh
 
Thanks for the help.

Now I am using I
IDbConnection, IDbCommand, IDbDataParameter to worte more generic code to
support SYbase ASA and SQL Server. SQL Server runs fine. But in Sybase ASA
My store procedure dowsnt work. Says parameter is wrong

the code is something like this

IDbDataParameter parameterPortalAlias = DBUtils.CreateDataParameter();
parameterPortalAlias.ParameterName = "Param1";
parameterPortalAlias.DbType = System.Data.DbType.String;
parameterPortalAlias.Size = 50;
parameterPortalAlias.Value = "ABC";

In the store proc the parameter is varchar(50). Store proc works fine I had
debugged it. What could be wrong.

Code is failing after this statement

IDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection); ;
 
You should write your code in much simple way.

in VB
IDbCommand = New OleDbCommand( "storedProcName", IDbConnection)
IDbCommand .CommandType = CommandType.StoredProcedure
IDbCommand.Parameters.Add( "@Param1", "ABC")

Note.: For passing parameter in this way, the name Param1 is not important but
the order.

e.g. in your proce definition you defined two paras in order of para1 and
para2, when you pass paras you should put your code in the same order as:
IDbCommand.Parameters.Add( "@Param1", "ABC")
IDbCommand.Parameters.Add( "@Param2", "ABC")

Bangh
 
That works, but is less performant than native providers.

I've started a generic data provider that uses the native .NET providers for
each database. At initialization of a "DbContext" class you specifyt which
database you are connecting to, and which ADO.NET provider you want to use.
It delegates method calls for you. You just use the generic provider
(GenCommand, GenConnection, etc...). It is still in progress, but has alot
of promise. See both links in my signature. The "Gen" provider (2.0) will
be released on the site below in a few days. meanwhile there is a
prereleased version available (1.1) Example can be found on the code
generator site.
 
Back
Top