sqlconnection vs. oledbconnection

  • Thread starter Thread starter Curtis
  • Start date Start date
C

Curtis

I have read numerous posts on this question, but would like to ask it again
for clarification or additional input.

We have a vb.net app connecting to mssql2000 primarily through stored
proceedures.

In terms of efficiency and ease of development or ?? what interface is
preferred and why?
 
Don't understand the question -- what do you mean by interface?
Development tool? User Interface? And what does "or ??" refer to?

--Mary
 
Curtis said:
I have read numerous posts on this question, but would like to ask it again
for clarification or additional input.

We have a vb.net app connecting to mssql2000 primarily through stored
proceedures.

In terms of efficiency and ease of development or ?? what interface is
preferred and why?
Click to expand...

SqlConnection is preferred because it's 100% managed code, while
OleDbConnection is COM interop.

David
 
Sorry.

inteface = how we are connecting between vb.net and mssql2000 (i.e.,
sqlconnection or oledbconnetion)
development tool = not sure we said "tool", but are interested in the
difference in "ease of development" in term of coding the application
?? = anything else in terms of the difference between sqlconnection and
oledbconnection we might not have mentioned (that is, other than "ease of
developement" and "efficiency" -- by "efficiency" we mean speed, memory
usage, etc.
 
That's not true. The SQLClient namespace classes are optimized to work with
the native data storage format of SQL Server so, when going against SQL,
these classes are preferred and will give you best performance.

The classes in the OLEDB namespace are certainly managed classes. They are
just not optimized for any particular db engine.
 
SqlClient performs better. OleDb is more flexible.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside the box!
*************************************************
 
Scott M. said:
That's not true. The SQLClient namespace classes are optimized to work
with the native data storage format of SQL Server so, when going against
SQL, these classes are preferred and will give you best performance.

The classes in the OLEDB namespace are certainly managed classes. They
are just not optimized for any particular db engine.
Click to expand...
The OleDbProvider classes are thin wrappers around OleDb objects, which, by
definition, are COM objects.

David
 
If you're working with SQL Server from your .NET app, then use
SqlClient. It will give you the best performance and allow you to tap
into more SQLS-specific functionality. As far as efficiency goes, that
depends on how you write code, not necessarily on the provider. You
can write inefficient, ugly code using any provider. In general I'd
recommend taking advantage of server-specific features like
parameterized stored procedures and stay away from command builders
and the like on the client. Also a factor is fetching too much data,
which can slow things down and hog server resources as well as
introduce concurrency issues when data cached on the client gets
stale.

--Mary
 
Let me see if I can explain this, it would be simple with graphics but not
much I can do about that.

The basic idea behind connecting to a database like Sql Server is a network
layer that sends data between the client and the server, for Sql Server in
v1.1 this is done with the Dbnetlib.dll. This layer provides the
connectivity layer through protocols like shared memory, tcp or named pipes.

On top of this protocol layer we build the actual provider, the provider
gives you an api to use this network layer, it provides services like
connection pooling etc. For Sql Server for example we have the SqlClient
managed provider and the SqlOledb native provider. Both of these providers
(in v1.1) talk directly to the Dbnetlib.dll.

When we shipped the managed providers we added OleDb and Odbc managed
_wrappers_ for native providers. In these providers you can specify a native
provider to use (again, for Sql Server you would specify Protocol=SQLOLEDB
in the connection string). These two providers do _not_ talk directly to
Dbnetlib and they do not provide Connection Pooling etc. Instead they rely
on the already implemented native implementation on the provider you
specify. So what happens when you do something like
OleDbConnection con= new OleDbConnection("Provider=SQLOLEDB;data
source=.;integrated security=sspi;");
con.Open();

The OleDbConnection does not know how to open a connection, but it does know
that you want to use the SQLOLEDB native provider. In this case it will
(through interop) call the SQLOLEDB native provider con.Open(). SqlOledb
native provider does know how to open a connection through Dbnetlib.dll so
it opens the connection and lets the managed provider know that it worked.

What I am trying to say is that the OleDb and Odbc managed providers are
just a set of wrappers for the native provider that you specify. When
talking about performance it is easy to understand that the fewer layers you
have the faster things will work.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/
 
Back
Top