IDbCommand VS. SqlCommand (IDbDataAdapter VS. SqlDataAdapter)

  • Thread starter Thread starter matt del vecchio
  • Start date Start date
M

matt del vecchio

Hey all,

I'm at a new programming gig, and my team here likes to use the "IDb"
data objects instead of "Sql" data objects. Such as:

IDbCommand command = conn.CreateCommand();
...
IDbDataParameter param = command.CreateParameter();
...
IDbDataAdapter myDA = conn.CreateDataAdapter(command);

....Yet, we use a SQL Server 2000 db. When I asked why we do this, it
was explained that because this way--programming to the actual
interface instead of specific SqlClient classes--we can quickly swap
to another db engine and have no worries. If we had to.

Now, all the MS .NET books I've read have said that the
"System.Data.SqlClient" classes have been optimized for SQL Server db
use.

Does anyone know for a fact whether our using the "IDb" syntax is
hurting performance?


Thanks!
matt
 
What happens is that SQLCommand is derived from IDBCOmmand, SqlDataAdapter
from IDataAdapter etc so it will work. Typically, people will declare a I
type and then instantiate a provider specific instance. If there was no
performance hit for doing this (never specifying hte specific provider type)
then it wouldn't make much sense to ever reference the specifc type. It
would be all downside (lack of portability) and no updside (Performance).
There are so many other features you give up not using specific providers
that I can't mention them all here. I'd love to see how they implement
stored procedures.....

And if you give up stored procs to be generic, you are giving up a lot. If
you use them, then any port you do to a new RDBMS is going to take some
modification. IMHO, that's a lot to give up, and unless you are planning on
switching your backend frequently, I don't see a whole lot of benefit
compared to cost.

BTW, just as a general comment....if that approach was truly 'better' why do
none of the major books suggest it? I think that answers the question
more...

Let me know if you need anythign else.

Cheers

Bill
 
William Ryan said:
There are so many other features you give up not using specific providers
that I can't mention them all here. I'd love to see how they implement
stored procedures.....
Click to expand...

Like this (verbose):


IDbCommand command = conn.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "pr_getMenuTemplateList";

//param - @iCategoryId
IDbDataParameter param = command.CreateParameter();
param.ParameterName = "@iCategoryId";
param.DbType = System.Data.DbType.Int32;
param.Value = 170;
param.Direction = ParameterDirection.Input;
command.Parameters.Add(param);

//param - @sRepositoryCode
param = command.CreateParameter();
param.ParameterName = "@sRepositoryCode";
param.DbType = System.Data.DbType.StringFixedLength;
param.Value = 2;
param.Direction = ParameterDirection.Input;
command.Parameters.Add(param);

//do it
DataSet newDS = new DataSet();
IDbDataAdapter myDA = conn.CreateDataAdapter(command);
myDA.Fill(newDS);


....in short, it's just a lot more code.
 
William Ryan said:
BTW, just as a general comment....if that approach was truly 'better'
why do none of the major books suggest it? I think that answers the
question more...
Click to expand...

The book I have does!

ADO.NET Programming, Arlen Feldman, ISBN 1-930110-29-4

Also, if you would like a presentation on "writing database independant
code", I have made a powerpoint and sample applications. See the
"generic" ADO.NET project link in my signature. The downloads page
contains a powerpoint presentation (Writing_Db_ind_code.zip). All links to
the samples are in last page of the powerpoint. The powerpoint is in
distributed format (pps), you don't need powerpoint to view it.

--
Michael Lang, MCSD
See my .NET open source projects
http://sourceforge.net/projects/colcodegen (simple code generator)
http://sourceforge.net/projects/dbobjecter (database app code generator)
http://sourceforge.net/projects/genadonet ("generic" ADO.NET)
 
I'll definitely take a look at it. My main point was that if you write
something totally independent you are going to have a performance sacrifice
over using a native provider. If you didn't, there would be no reason to
use specific providers. I've seen many implementations wherein you declare
the varialbes at the interface level, but then depending on the backend, you
instantiate everything specifically to the provider. If this isn't the
case, I'd love to find out for sure because you could preempt any future
ports to a different db and you could do it with less code and less
libraries.

As far as the procs go, I can see where you can pass them generically
between say MS SqlServer and Oracle. However, with DB implementation like
mySQL and Access, there isn't any suppport for them. And there's a lot of
interest in porting to both of these types.

Does Feldman's book discuss going totally agnostic? I'm going to have to
check it out.

Thanks,

Bill
 
I'll definitely take a look at it. My main point was that if you
write something totally independent you are going to have a
performance sacrifice over using a native provider. If you didn't,
there would be no reason to use specific providers. I've seen many
implementations wherein you declare the varialbes at the interface
level, but then depending on the backend, you instantiate everything
specifically to the provider. If this isn't the case, I'd love to
find out for sure because you could preempt any future ports to a
different db and you could do it with less code and less libraries.

As far as the procs go, I can see where you can pass them generically
between say MS SqlServer and Oracle. However, with DB implementation
like mySQL and Access, there isn't any suppport for them. And there's
a lot of interest in porting to both of these types.

Does Feldman's book discuss going totally agnostic? I'm going to have
to check it out.
Click to expand...

Feldman also shows how to use the native providers. But most chapters
also show how to do the same thing provider independant.

I have not done benchmarks, but theoretically speaking there is not a
significant performance hit for writing database independant code. If
you used the OleDB or Odbc providers there is definitely a performance
hit! But that is not the way I propose writing database independant
code.

When you use interfaces, but instantiate that to an instance of a
provider specific object, then the provider handles all the method calls.
The only performance hit is in directing the method calls from the
interface method table to the instance method table. As far as database
interaction is concerned, this is negligible.

The presentation and sample projects discuss the specifics on how to
implement db independant code.

http://sourceforge.net/projects/genadonet

--
Michael Lang, MCSD
See my .NET open source projects
http://sourceforge.net/projects/colcodegen (simple code generator)
http://sourceforge.net/projects/dbobjecter (database app code generator)
http://sourceforge.net/projects/genadonet ("generic" ADO.NET)
 
Creating a generic application can be quite a challenge if you expect it to
be competitive. Each DBMS vendor has its own advantages and limitations and
implements "standard" features differently. For example, stored procedures
are not implemented in JET (I don't count stored queries), are implemented
in SQL Server and Oracle but using different ways to pass back data and
manage parameters. Security is handled differently, admin is different and
the list is endless. Generic applications have traditionally been a
nightmare of "exception" processing code.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
my only read beef so far is, it's so much more verbose. querystring
params used to take one line w/ the constructors i knew, now they take
6!

param = command.CreateParameter();
param.ParameterName = "@sSubCategory";
param.DbType = System.Data.DbType.String;
param.Size = 255;
param.Direction = ParameterDirection.Output;
command.Parameters.Add(param);


matt
 
(e-mail address removed) (matt del vecchio) wrote in
my only read beef so far is, it's so much more verbose. querystring
params used to take one line w/ the constructors i knew, now they take
6!

param = command.CreateParameter();
param.ParameterName = "@sSubCategory";
param.DbType = System.Data.DbType.String;
param.Size = 255;
param.Direction = ParameterDirection.Output;
command.Parameters.Add(param);


matt
Click to expand...

I don't use the CreateParameter() method of the command. I use the same
consturctors I've always used.

IDbCommand.Parameters returns an IDataParameterCollection. It does not
have an Add method defined, so you must declare the parameter as the
specific type, and use it's constructor. For this reason I've made a
generic data provider which does all the interface based programming for
you/me. You use it as you would all the other providers. You can check
this code out using the link in my signature (project "genadonet" on
SourceForge)

GenParameter = cmdSelect.Parameters.Add(...)

Here is the full list of constructors...

class GenParameterCollection
(access via GenCommand.Parameters property)
public GenParameter Add();
public GenParameter Add(string name, object parValue);
public GenParameter Add(string name, System.Data.DbType type);
public GenParameter Add(string name, System.Data.DbType type,
int size);
public GenParameter Add(string name, System.Data.DbType type,
int size, string sourceColumn);
public GenParameter Add(string name, System.Data.DbType type,
int size, ParameterDirection dir, bool isNullable,
byte precision, byte scale, string sourceColumn,
DataRowVersion version, object parValue);

--
Michael Lang, MCSD
See my .NET open source projects
http://sourceforge.net/projects/colcodegen (simple code generator)
http://sourceforge.net/projects/dbobjecter (database app code generator)
http://sourceforge.net/projects/genadonet ("generic" ADO.NET)
 
Back
Top