OLEDBCommand vs. SQLCommand

  • Thread starter Thread starter Anil Gupte
  • Start date Start date
A

Anil Gupte

When does one use one or the other? And why? I cannot imagine too many
innstances where you would not want to use SQL statements when you
communicating with a database - even on a read-only basis. Plus there is
the ODBCCommand class too - I understand that is an older implementation and
is not used in .Net much, correct?

Thanx for any clarifications and perhaps web links to discussion on this.
 
I might be wrong here, but someone will surely correct me if I am:

As far as SQL statements go, all three commands use them to talk to the
database, the prefix before "Command" or "Connection" is an indicator
of what it can access, not how. With that said, the OleDbCommand's are
for communicating with any OLEDB providing databases, like Oracle,
Access, SQL Server, Excel, etc. However the SQLCommand is created
especially for Microsoft's SQL Server databases, providing some SQL
server specific features and better performance. Finally ODBC is used
for ODBC complaint databases (almost all databases are). However, ODBC
requires that you set up a DSN (data source name) before using it - or
at least it used to - and is less efficient than using OleDbCommands or
SQLCommands, but may be your only choice depending on the database.

Does that help?

Thanks,

Seth Rowe
 
rowe_newsgroups ha scritto:
I might be wrong here, but someone will surely correct me if I am:

As far as SQL statements go, all three commands use them to talk to the
database, the prefix before "Command" or "Connection" is an indicator
of what it can access, not how. With that said, the OleDbCommand's are
for communicating with any OLEDB providing databases, like Oracle,
Access, SQL Server, Excel, etc. However the SQLCommand is created
especially for Microsoft's SQL Server databases, providing some SQL
server specific features and better performance.
Click to expand...

I have been always wondering about that. I have been using the OLEDB
against SQL server and it is fast. Is there any reason why , for
instance,
a query sent by the SQLCommand should be faster than the same query
sent via an OLEDBcommand ? And id the sql reader faster than the OLEdb
reader ?

I do not have this feeling. Has anyone some test about that?

I think that using OLEDB may give the advantage that if you change
the underlying DB you do not have to change the code. And this seems to
be a huge
reason to use it whenever possible.
Also the possibility to access the GUIDs is fundamental. And this is
another huge reason.

Actually I am not clear why the need sort of specialized connectors. If
there is a form of a specialization a generic connector should be smart
enough to use specialized function when connected to specific DBMS.

What not make all the oledb way? What do you guys think ??


Finally ODBC is used
 
Personally, I almost always use the OleDb classes. This is mainly
because when I wrote my "superclass" I wrote it to be compatible with
all the servers my company uses, meaning either OleDb or ODBC (and I
hate ODBC). If all we had were SQL Server databases I might change my
story but until then I'll stick with Oledb. I did a few searches for
the differences and one post from Bill Vaughn said the the OleDb class
realies on COM interop while the SQLClient is a truely native to SQL
Server versions 7 and up.

Hopefully someone will post a link to the differences between the two.

Thanks,

Seth Rowe
 
I think under certain circumstances, the SqlClient namespace classes are
going to provide you with better performance. They may also provide you with
more access to SQL Server specific functionality.

If your application does not need any of this functionality, and what you
are doing is not something that the SqlClient class can optimize for you,
then I suppose you can use OleDb so that you can swap out different types of
databases. If you are always using SQL Server though, then I see no reason
to not just always use the classes in SqlClient.
 
Thanx Seth & Pamela, that does help. I also realized that MS Acesss is not
just a scaled down SQL server, but accorind to my book it has a different
(JET) database engine. It is just an assumption that I made, thinking
Access is SQL Server lite.
 
Thanx for the clarification. By the way, it surprises me that Oracle is not
SQL compliant - assuming that SQL is a general specification, not something
invented by MS.
 
Sorry, I don't know what you mean. Most of the things that run on SQL Server
are going to run on Oracle too. But every database system has its own
functions or additional syntax.
 
Thanx for the clarification. By the way, it surprises me that Oracle is not
SQL compliant - assuming that SQL is a general specification, not something
invented by MS.
Click to expand...

How do you mean SQL Compliant?

All vendors support a standard SQL dialect, and then each vendor extends
this in their own way.
 
Thanx for the clarification. By the way, it surprises me that Oracle is not
SQL compliant - assuming that SQL is a general specification, not something
invented by MS.
Click to expand...

I think you're slightly confused. "SQL" stands for structured query
language and is used by almost all types of databases. Each database
molds the language in order to fit their product better, but all
"flavors" closely resemble each other. For example, Microsoft's SQL
Server uses T-SQL (transact SQL) to execute queries against it's
tables, while Oracle uses "Oracle SQL" to execute their queries. An
example of one of these changes in T-SQL is the "Inner Join" and "TOP
#" statements, these are not supported in Oracle, but Oracle can do the
same thing with just a few changes:

i.e.

//T-SQL Command

SELECT TOP 10 *
FROM tbl_Whatever
INNER JOIN tbl_Something ON tbl_Something.Somefield =
tbl_Whatever.SomeField

//The Same Command in Oracle

SELECT *
FROM tbl_Whatever, tbl_Something
WHERE tbl_Something.Somefield = tbl_Whatever.SomeField
AND rownum <= 10

Both of the above are considered to be SQL statements and both do the
same thing in there respective enviroments. The SQLClient classes are
just optimized to work with SQLServer, and will only work with SQL
Server. If you want to connect to Oracle database you have to use the
OleDb class. You can send SQL statements through the OleDb class to sn
Oracle database without any trouble - as long as they are valid SQL
statements (see above - if you pass the first SQL statement to Oracle
it will throw an error since it doesn't support Transact SQL)

In summary:

You can use either the SQLClient class, OleDb class, or ODBC class to
pass SQL statements to Microsoft SQL Server

You can use either the OleDb class or ODBC class to pass SQL statements
to any OleDb or ODBC complaint database (like Oracle, Access, Excel,
etc)

You can use the ODBC class to pass SQL statements to a ODBC compliant
database (Mimer, Firebird, etc)

Does that help explain it?

Thanks,

Seth Rowe
 
One clarification. You do not *have* to use OleDb for Oracle. Microsoft
ships an oracle proider with the 2.0 framework, and Oracle also has its own
which you can download from their site.
 
im not positive that ODBC is slower

I've seen a half dozen situations where using ODBC is faster than OLE
DB... so in other words; MS has waffled on OLE DB; and VB.net is a
complete market failure.

So you'd have the best performance by going back to Access97


-Aaron
 
One clarification. You do not *have* to use OleDb for Oracle. Microsoft
ships an oracle proider with the 2.0 framework, and Oracle also has its own
which you can download from their site.
Click to expand...

From what I gather the Oracle one works better with the dizzying array of
things Oracle does ... might be a better bet for an Oracle application
 
Right, that is what I have heard too.

However, if one does not really want to deal with downloading and installing
it, then at least there is the one that Microsoft ships.
 
Anil,

The normal advice is
Use a for a Database special made provider if that is available they are
mostly better scaled for that than OleDB.

If that is not, than use OleDB as long as you have the change to create the
connection (like password etc).

If it is completely closed than you can use ODBC.

To overcome the problems with different types of databases there is now the
factory.

What I have seen in this thread as confusion.

If there would be a price for giving the worst names to products, Microsoft
would win in my idea the first the second and the thirth place.

SQL server is just a name for a database server. It uses the SQL transact
language to process that in the same way as most DataBase servers as Oracle
do.

Cor
 
That is what I thought - SQL is a standard. But like every other standard,
I guess each vendor has their own version of it. My confusion was thinking
that SQL is the standard connection to use (not OLEDB) since SQL would be a
standard.

Anyway, I get it now.

Thanx (everyone) for your input.
 
Back
Top