SqlClient or OleDbClient?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

OK... I've read all the 'best practices' info and have beat things up enough to grok the deeper aspects, but am not convinced that the SqlClient is remarkably better overall than the OleDbClient given that 1) the Form wizards don't allow us to choose the SqlClient, and 2) in the event a customer wants to use a backend database other than MS SQL, it would seem easier to switch using an OleDbCient than the SqlClient.

We're about done testing, and are at the point where we need to 'fish or cut bait' on deciding which DbClient to use... all input appreciated.

App being developed - an vertical enterprise accounting system, being converted from a legacy application.
Back-end - MsSql
Front-end - C#.net

Thanks,

Les
 
Hi Les,


Les Cardwell said:
OK... I've read all the 'best practices' info and have beat things up
Click to expand...
enough to grok the deeper aspects, but am not convinced that the SqlClient
is remarkably better overall than the OleDbClient given that 1) the Form
wizards don't allow us to choose the SqlClient

Pardon?

, and 2) in the event a customer wants to use a backend database other than
MS SQL, it would seem easier to switch using an OleDbCient than the
SqlClient.

Correct. But you can also go with factory pattern (all clients supports
basic interfaces) - a bit more coding and more flexible.
I don't know. IMO if you aren't a speed freak you should go with OleDb.
On the other hand, native providers are more flexible...
Hard choice to make, indeed.
 
Hi Mark

<g>... I should have been more specific. I was referring to the "Data Form Wizard", which only utilizes the OldDbClient

I guess at this stage, I'm more interested in flexibility than speed, if the sacrifice isn't all that great. For the most part, we do all our processing/coding on the backend (stored procs, triggers), so I'm not sure if there's any real significant difference in real-world scenarios between the two clients. Our largest client on this app (legacy system) is about 200 users, 300-400 tables, and about 1,500 to 2,000 procedures. Most clients run about 1/3-1/2 that

Thx

Les
 
Les:

That's the case today, but you never know what tomorrow brings and it's not
always easy upsizing or changing code base. Also, have somewhat larger apps
record wise and the difference is more than profound. Granted there is a
lot to coding stuff that will run fast and the Provider is but one
component, but IMHO, squeeze it where you can. Also, native providers allow
you to access features that are specific to the db. Does this couple it to
the DB? Certianly, by definition it does. However, that coupling comes
with some added benefits. Considering the CLR integration with Yukon, I'm
sure the beneifts will only increase (That's just a guess, but I think it's
a safe one).

As far as the wizards go, and this is just a general observation...in most
instance I wouldn't worry about them. By dragging them on your form, you
are moving your logic to the form. Sure stored process push it back to
another layer, but you can't stick your DataAccess logic in a .dll and run
it remotely if you need to. Once again, if you will never grow, it won't be
that much of a problem, but it could be.

If possible, I'd go with the native provider if at all possible.

HTH,

Bill
Les Cardwell said:
Hi Mark,


<g>... I should have been more specific. I was referring to the "Data Form
Click to expand...
Wizard", which only utilizes the OldDbClient.
I guess at this stage, I'm more interested in flexibility than speed, if
Click to expand...
the sacrifice isn't all that great. For the most part, we do all our
processing/coding on the backend (stored procs, triggers), so I'm not sure
if there's any real significant difference in real-world scenarios between
the two clients. Our largest client on this app (legacy system) is about 200
users, 300-400 tables, and about 1,500 to 2,000 procedures. Most clients run
about 1/3-1/2 that.
 
THis is really a no brainer if you care at all about performance.

The SQLClient DB Providers are optimized to talk DIRECTLY to/with SQL
Server, thus removing a layer of communication that exists with the OLEDB DB
Providers.

There have been numerous tests performed to confirm that when going against
SQL Server, the SQLClient classes are superior. Of course, you won't see
the performance gains when doing less than "robust" data processing.

If the possibility exists that you may switch databases on the back end at
some point, it would be very easy to switch the code over to the OLEDB
classes, since their interfaces are identicle to the SQLClient classes.


Les Cardwell said:
OK... I've read all the 'best practices' info and have beat things up
Click to expand...
enough to grok the deeper aspects, but am not convinced that the SqlClient
is remarkably better overall than the OleDbClient given that 1) the Form
wizards don't allow us to choose the SqlClient, and 2) in the event a
customer wants to use a backend database other than MS SQL, it would seem
easier to switch using an OleDbCient than the SqlClient.
We're about done testing, and are at the point where we need to 'fish or
Click to expand...
cut bait' on deciding which DbClient to use... all input appreciated.
App being developed - an vertical enterprise accounting system, being
Click to expand...
converted from a legacy application.
 
Bill

BTW..
instance I wouldn't worry about them. By dragging them on your form, yo
are moving your logic to the form. Sure stored process push it back t
another layer, but you can't stick your DataAccess logic in a .dll and ru
it remotely if you need to. Once again, if you will never grow, it won't b
that much of a problem, but it could be

....are you suggesting that all of the data-access code should exist in the module code (ie- using SQL Helper/MS Data Access Application Blocks - which is what's suggested by Best Practices ) w/Stored Procs vs. dropping the DataAdapter/DataSet objects onto the form? I assumed they were essentially the same..

Thx

Le
 
Les:

It's kind of hard to say for sure but let me elaborate. Let's say that I
knew that I had an app and all it had to do was provide a form and a grid
that would allow users to ADD/DELETE/INSERT records, kind of like the grid
view in SQL Server. If I was positive that the underlying structure would
never change, and that the app would never grow etc, I could take all kinds
of shortcuts. I could jsut use a wizard for instance, hard coding the
connectionstring etc. Now, if this app will never grow, I may not really
see any benefit by seperating dataaccess, presentation etc, (assuming that
the app would never change.) However, depending on what you anticipate your
growth to be, you'll probably want to start breaking things up. Pretend
that Stored Procs for instace were had no benefit other than they sit on the
server. Would they still be worth using? Yes. Why? B/c you can change
what they do without interrupting users. You could make modifications
without anyone ever knowing.

The same goes with everythign else. After all, if you had to send the car
back to the factory every time you got a flat tire, that would be obtrusive.
However, if you never got a flat you'd never know the difference.

Alas, "Should all the data-access code exist in the module code" Again, it
depends on your needs, but it will scale better. All shops have their
little 'rules' and we have well close to 100,000 lines of code which is
heavily related to dataaccess and never used a wizard. However, there are
probably many scenarios where they use them extensively and all is well. I
will tell you though that if we used the configuration wizards, it'd be well
advised to take advantage of attributes and config files, otherwise the next
time we changed our DomainController, we'd have an awful lot of finding and
replacing to do.

Hopefully that answer it?

Bill
Les Cardwell said:
Bill,

BTW...
most
instance I wouldn't worry about them. By dragging them on your form, you
are moving your logic to the form. Sure stored process push it back to
another layer, but you can't stick your DataAccess logic in a .dll and run
it remotely if you need to. Once again, if you will never grow, it won't be
that much of a problem, but it could be.

...are you suggesting that all of the data-access code should exist in the
Click to expand...
module code (ie- using SQL Helper/MS Data Access Application Blocks - which
is what's suggested by Best Practices ) w/Stored Procs vs. dropping the
DataAdapter/DataSet objects onto the form? I assumed they were essentially
the same...
 
Hi Scott,

If the possibility exists that you may switch databases on the back end at
some point, it would be very easy to switch the code over to the OLEDB
classes, since their interfaces are identicle to the SQLClient classes.
Click to expand...

Only if you use interfaces only and factory pattern from the beginning.
Otherwise there is a lot to rewrite/modify.
 
There are significant differences between the two.
The OleDB provider is unmanaged code. It uses COM to access legacy OLE DB
data providers. It is the last choice when choosing a data provider.

SqlClient is a native managed provider designed specifically for SQL Server.
It speaks to SS using TDS and does not have to go through any intermediate
layers. It exposes far more functionality than OleDb ever will. It is being
tuned and enhanced (I've seen evidence of this in the next release)--OleDb
is not.

No, even if you choose OleDb, switching backends is not as easy as some
would have you think. Every serious application I know uses stored
procedures as their basis. Yes, there are alternatives that use business
objects but these also use SPs behind the scenes. SQL Server stored
procedures run on SQL Server (all versions), but not anywhere else (not even
Sybase). Yes, you can abandon SPs and write a generic application or
insulate the application from the data access layers but this is very
expensive and consumes lots of programmer and CPU resources.

No, SqlClient here is a no-brainier. SQL Server? Use SqlClient.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
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.
__________________________________

Les Cardwell said:
OK... I've read all the 'best practices' info and have beat things up
Click to expand...
enough to grok the deeper aspects, but am not convinced that the SqlClient
is remarkably better overall than the OleDbClient given that 1) the Form
wizards don't allow us to choose the SqlClient, and 2) in the event a
customer wants to use a backend database other than MS SQL, it would seem
easier to switch using an OleDbCient than the SqlClient.
We're about done testing, and are at the point where we need to 'fish or
Click to expand...
cut bait' on deciding which DbClient to use... all input appreciated.
App being developed - an vertical enterprise accounting system, being
Click to expand...
converted from a legacy application.
 
Hi Bill,

Yes... answers it quite well, and is along the lines of our legacy practices as well. But importantly, it crystalizes some of the choices and methods presented in the DAAB and DALC information. Slowly, the path is clearing :)

Thx,

Les
 
et all...

I posted a few replys here last night, but they never showed up.

AAR, thanks all for your input. It definitely clarifies some of the choices we needed to make, and how they need to be implemented. I need to tear apart the DAAB source code (SqlHelper) to gain a better understanding of the 'nits', but it sounds like a small price to pay in the end.

Thx,

Les
 
No, even if you choose OleDb, switching backends is not as easy as some
would have you think. Every serious application I know uses stored
procedures as their basis. Yes, there are alternatives that use business
objects but these also use SPs behind the scenes. SQL Server stored
procedures run on SQL Server (all versions), but not anywhere else (not even
Sybase). Yes, you can abandon SPs and write a generic application or
insulate the application from the data access layers but this is very
expensive and consumes lots of programmer and CPU resources.

No, SqlClient here is a no-brainier. SQL Server? Use SqlClient.
Click to expand...

Hi Bill,

I do not totally agree. Real example (a bit simplified though): I have an
application that is written for Oracle via MS Oracle managed provider.
Now, if I want to port it into Sql server I would need to change all those
fancy objects or I had to use factory pattern.
Since the app utilizes SQL syntax that is common to both servers and both
are SP capable, porting to Sql server in the case of OleDb would be a matter
of changing connection string more or less.
Of course, the speed wouldn't be ideal, but hey, the bottle necks are mostly
complicated selects on which OleDb or managed provider have no effect.
 
Changing the object names will be the least of your worries. The data access
interfaces are just that--interfaces. In most designs the "real" work is
done on the server using server-specific queries, procedures, triggers,
methodologies and data access strategies. These are not "emulated" or
accounted for in the thin data access interfaces like ADO.NET (it's far
thinner than ADO classic). If you stick to SQL syntax that works for both
backends, you'll have to leave off many (many) cool/important/hi-performance
features that their enhanced/extended syntax supports. You'll also have to
train each developer that comes into your shop not to use these extensions.
Good luck.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
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.
__________________________________
 
William (Bill) Vaughn said:
Changing the object names will be the least of your worries. The data access
interfaces are just that--interfaces. In most designs the "real" work is
done on the server using server-specific queries, procedures, triggers,
methodologies and data access strategies. These are not "emulated" or
accounted for in the thin data access interfaces like ADO.NET (it's far
thinner than ADO classic). If you stick to SQL syntax that works for both
backends, you'll have to leave off many (many) cool/important/hi-performance
features that their enhanced/extended syntax supports. You'll also have to
train each developer that comes into your shop not to use these extensions.
Good luck.
Click to expand...

Yes, I know all this.
But it is far easier to mantain same code base for both databases (that was
my point).
There are many projects that are time-limited and customer is happy if it is
finished (as long it is fast enougth - he/she doesn't care if it uses oledb
or native providers as long as it works fast enough) :)
So, the decision isn't that easy to take.
 
Back
Top