Fog Creek Software
Discussion Board




ADO vs. ADO.Net

Today I spent a pleasant hour amusing myself with Chapter 13 of Bill Vaughn's ADO.NET book (http://www.amazon.com/exec/obidos/ASIN/1893115682/joelonsoftware).

From two miles up, it sounds like ADO.NET is a giant leap backwards from ADO (not that ADO was all that hot).

Then I tried playing around with it and discovered that the sheer number of concepts you need to do a simple SELECT is much greater than ever before. DataSets, DataAdapters, DataTables, ... it seems like the minimum number of things you need to memorize to be able to do your garden-variety SELECT, UPDATE, DELETE, and INSERT has gone up a lot since ADO.

So I thought I'd ask here -- am I missing the point or is it really that bad?

Are any .NET developers using the old ADO for performance reasons?

(Please, let's not let this degenerate into random .net religious chitchat. I just want to hear from developers who are experienced with ADO.NET vs. ADO in particular.)

Joel Spolsky
Wednesday, March 20, 2002

I hear ya...

When I first started using ADO.NET I was totally confused with the new set of objects.  The old ADO seemed much simpler.  But after digging in my perspective quickly changed.

What did it for me was the concept of a "strongly typed" dataset.  That sucker is slick!  I found it so much easier to work with rather than the adodb recordset. 

An XML schema is used to describe the data (Tables, Rows, Columns, relationships).  It's like having a mini-database all in one object.  An since it's strongly typed it's posssible to call properties like this:  myDataSet.Customers.Add("Tigger");

Binding is more feature rich.  Drop a datagrid on an ASPX page along with a ST dataset and you're ready to go...with full on WYSIWYG editing.

When I first started learning .NET, I started with NOTEPAD and CSC.  It was a lot of work (but I learned alot about the framework).  Now I'm using the VS.NET editor to build most of the code for me...then plug in the glue.  It became much more practical.  I'm not sure if your doing the same, but that was my experience.

(I hope that wasn't to marketing like...I'm just a little excited about the new options)

Richard Caetano
Wednesday, March 20, 2002

I find that the SQLDataReader does the job for most queries, so I don't bother with DataSets, DataAdapters and DataTables. The DataReader is a fire hose cursor so it's fast.

The IBuySpy examples seem to use the SQLDataReader for their component layer, see: http://www.ibuyspystore.com/docs/Components_ProductsDB_cs.htm

Ben
Wednesday, March 20, 2002

I'm really disappointed to hear that ADO.net requires so many "bits" to achieve something simple. I think that this fragmentation has been happening to microsoft development products generally for a few years now, and the sheer volume of stuff that I need to know to do something that used to be simple is starting to become an unpleasant aspect of developing. 

The size of the woods is the same, but there are now so many trees that its getting too hard to find the path.

Tony
Thursday, March 21, 2002

I think you'll find that ADO.Net doesn't require more bits to perform simple operations such as Select statements with firehose cursors or Update statements with command objects. Remember, just because you can use ADO.Net to say, combine mutliple tables into the one DataSet doesn't mean that you have to. You could just use a Join query with a DataReader to return the results.

Take the time to have a look at this article, which says: "I predict that the biggest source of complaints about ADO.NET will be from developers trying to come to terms with DataSets; but in truth, mastering DataSets needn’t be a big problem." - http://www.dnjonline.com/articles/essentials/iss22_essentials.html

Ben
Thursday, March 21, 2002

Yes, I think you're missing the point. If you want to reduce the number of things to memorise, while still using ADO.NET best practices, have a look at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp . Using this will allow you to use code like

myDataReader = SqlHelper.ExecuteReader( myConnectionString, "select * from authors");
while (myDataReader.Read()) { ... }

It also provides really slick access to parameterised stored procedures.

SqlHelper.ExecuteScalar( myConnectionString, "spMyProc", "first param", 1, DataTime.Now, "fourth param", ... )

I like ADO.NET. I also like the Data Access Application Block.

Isaac
Thursday, March 21, 2002

I also like the data access application block - but unfortunately, they haven't released it for the RTM version yet (it's only for .NET beta 2).

Pretty much useless until they do a full release, but by then I won't need it anymore, as I've had to do without and make my own sql helper classes.

Philip Rieck
Thursday, March 21, 2002

Three things:

1) Forget all the crap from Microsoft about ADO.NET being an evolutionary change to ADO. It is not. It is a completely new system of data access. They should have changed the name of the library and of the objects, so people wouldn't get confused. But, of course, that would have hurt the warm and fuzzy message from marketing.

2) For performance, ADO.NET beats ADO if you're hitting SQL Server data sources. Less layers of gunk in the way. This is a sneaky retreat from the promises of "universal data access" in ODBC and OLE DB, with Microsoft finally admitting that performance comes from working directly with the database API.

3) Bill Vaughn is a great author and his book rocks, but over the long term he has pooh-poohed EVERY data access library that he wasn't involved in writing himself. You need to take into account his point of view and read some other sources.

Yes, there is a bunch of new stuff to learn, but taken for what it is -- an API optimized for disconnected data, and for fast retrieval directly from SQL Server databases - ADO.NET is great stuff.

Mike Gunderloy
Thursday, March 21, 2002

Ah, thanks everyone!

I kind of got the feeling that despite the huge piles of complicated documentation, eventually I would discover the main thing I needed to memorize, e.g., how to execute a SQL SELECT statement and loop over the results!

I guess it's a matter of bad documentation (at all levels).

It didn't help that the first example I found was in an O'Reilly book (Programming ASP.NET) which actually had a bunch of unused and wrong code, and which spent a lot of time wrapping things up in objects where they could have just passed strings...

Joel Spolsky
Thursday, March 21, 2002

I have found ADO.net's great strength is in caching disconnected data.  This has been a huge problem for me in the past writing applications that required accessing the same small chunk of semi-static data over and over again.  The fact that a dataset can easily save and reconstitute multiple tables in just  a few steps is huge.

In the past I have had to build xml caching features directly into web applications by hand.  It was tedious and very error prone.  Although I admit ADO.net is much more complex than ADO, I think it is definitely a move ahead.

Oh, and I agree that Microsoft has finally conceded that universal data access is a somewhat misguided principle.  I think the idea of separate data access providers (or whatever you call them) is a better way to go.  Remove all those nasty layers.  Microsoft has decided that a universal data format (the dataset and xml) is the principle that has real merit, while the method of actual data access can just be similar--and I agree.

Scott Suchomel
Thursday, March 21, 2002

I second Mike Gunderloy's post.

Richard Caetano
Thursday, March 21, 2002

20,000 foot view:

DataSet holds tables of information, which are DataTables. That means one DataSet can hold the results from more than one query.

DataTable hold rows of information, which are DataRows.

The real power, IMO, comes from strongly typed data support (as someone earlier pointed out), but all in all, it's pretty easy once you get used to it:

1. Create an OleDbConnection to connect to the DB.
2. Create an OleDbCommand to represent your query.
3. Create a DataAdapter to execute the command and fill your DataSet.

Repeat #2 and 3 for each query to perform. Voila, DataSet full of data. It's really only a few lines of code. Using the "foreach" is very nice, because everything behaves like a collection:

foreach( DataTable table in myDataSet )
{
  foreach( DataRow row in table )
  {
... etc. ...

I found traditional ADO very hard to ramp up on, personally, but I got ADO.net really really quickly.

Brad Wilson
Thursday, March 21, 2002

I find the following site very helpful when working with .NET. Has lots of good examples.

http://www.gotdotnet.com/

Satyadeep Musuvathy
Thursday, March 21, 2002

Sorry if this is a dumb question, but what is ADO and how should I be using it?  Is ADO the replacement for SQL Server or does it just replace MS Access?  Does ADO require C++ or can I use it from Visual Basic?  In ADO, does each user need his/her own login and password?  Finally, whats the best book for learning ADO?  I have some experience with Java (if that helps).  Or am I better of starting out with ADO by itself?  If you can explain a few of my questions here or provide a few links, I would appreciate it.

Thanks,
Tim

Tim Thompson
Thursday, March 21, 2002

ADO is a data access library, not a database. As such it doesn't replace either SQL Server or Access. It can be used to retrieve data stored in SQL Server, or Access, or many other data sources. Think of it as "plumbing" rather than "storage"

You can use ADO from any language that can call COM objects; Visual C++ and Visual Basic are both perfectly good ADO clients.

Best book? I'm prejudiced, because I've written several books on the subject. But just to get started, you don't need a book; just hop over to msdn.microsoft.com and browse around the Data Access section of the MSDN Library.

Mike Gunderloy
Thursday, March 21, 2002

I'm a bit late to this conversation, but Isaac made a good point about the Data Access Application Blocks from MS.  Anybody that is working with parameterized stored procedures and wishes to save themselves a lot of typing while at the same time forcing best practices will do well to look into it.

The Application Blocks do work with V1.  Just recompile and you're on your way.

Jeff Reese
Thursday, March 21, 2002

Hey, does anyone know why Joel's link to Amazon wouldn't be displaying properly in IE6/Win2000? All I'm seeing is:
(http://www.amazon.com/exec/obidos/ASIN/1893115682/joelonsoftw

...and only half the "w" at that. I refreshed the page and checked the source but can't find anything that would be having that affect.

Is anyone else seeing this? Just curious. Sorry to get off topic.

ian
Friday, March 22, 2002

I have the same problem - IE 6 w2k.

tekumse
Friday, March 22, 2002

I think it has to do with his bizarre use of tables on his site.

Here's a string of 150 characters, let's see how it displays.

123456789x123456789x123456789x123456789x123456789x123456789x123456789x123456789x123456789x123456789x123456789x123456789x123456789x123456789x123456789x

Mark W
Friday, March 22, 2002

See?

In IE the table respects that it's only to be x pixels wide, and if the text is longer than that it's simply hidden. disappears from site.

netscape, on the other hand, automatically expands the table - all of our right aligned names just scrolled off the screen.

Question - why do these strange questions always pop up in the middle of an otherwise good thread?

Anthropologist on Planet Joel
Friday, March 22, 2002

*disappears from sight

Anthropologist on Planet Joel
Friday, March 22, 2002

I've always been wary of overbuilt data layer components in n-tier applications.  The argument has always been that the data layer abstracts the datasource so that you can change from SQL Server to Oracle or XML file storage, or radically change your schema, or whatever, and the changes to your app will be confined to your data layer.  Your business and presentation logic need not be changed because their access to the datasource is abstracted.

My question is this.  Now that we're even more likely to use provider-specific objects to return and manipulate data (ie. the System.Data.SqlClient namespace), doesn't this further invalidate the idea of a big datalayer?  I might keep a few common functions in here, like GetSqlConnection(strSqlConnectionString) or whatever, strictly for timesaving and simplicity.  However, abstracting all access through a data layer object that doesn't really do anything seems like overkill - especially now.

I'm anxious to hear people's responses to this.  I've had this argument for several years now.  My argument goes something like this -- "...but ADO is the data layer..." :)  What do you think?

Brent Rockwood
Sunday, March 24, 2002

PS...I won't be using the old ADO, strictly because I want to reduce the number of COM interop calls in my .Net apps.  For high performance apps, the marshalling involved is likely to be expensive.  I haven't tested it yet, but this is my intuition...

Brent Rockwood
Sunday, March 24, 2002

> "...but ADO is the data layer..." :) What do you think?

When my system is big enough then I'll write my own separate data layer; I don't mind so much how low is the lower-level API to which my data layer interfaces (ADO, or whatever).

Christopher Wells
Sunday, March 24, 2002

I never understood what was the matter with ODBC - the idea of a universal access method to talk to anyone's database was very appealing.  But I guess that was too open for Microsoft, and so they had to come up with something that would give them a proprietary advantage.

As for caching, it's not too tough to write a thin C layer on top of the ODBC API to do caching for you.  Even connection pooling, like PHP does, isn't that tough to do.

Ed Carp
Monday, March 25, 2002

Has anyone played around with trying to push updated data back into a database using a DataAdapter and DataSet yet?  We've started messing about with this at my company and we're finding it a bit strange.  What's the recommended pattern for filling up a DataSet, making changes, and then pushing those changes back into the DB when your initial query used a join?  Just curious...

Ben Lowery
Monday, March 25, 2002

Sorry to be off topic but,

The reason that link didn't work is not because IE doesn't expand tables like Netscape.  It's because Joel told it not to by adding

  STYLE="table-layout: fixed"

Which tells IE the table will be exactly the size I specify which allows IE to display the table immediately instead of having to wait for the entire contents to be read in over the net before it can figure out how wide the table is.

It's a problem many comment sites have.  If the column for comments allows about 30 characters across and one user leaves a 150 character no space comment the formatting of the page gets all message up and currently neither HTML nor CSS deal with this (like by forcing a break in the content).

My site deals with it by breaking the users long words at post time.  Unfortunately that's not a perfect solution because iiiiiiii is not as wide as wwwwwwww.

Gregg Tavares
Tuesday, March 26, 2002

I am a newby, so I hope this is not a stupid question. Where does validation code go with ADO.NET?  My understanding is that the NET framework does away with COM.  Does that mean that the validation layer is the same as the UI layer? 

John Chesnut
Tuesday, March 26, 2002

Brent - I totally agree about the lack of need for a seperate data layer. I've always found that when I (or a team I've been on) attempts to abstract a seperate data layer we just end up with unncecessary complexity, a whole hunk of code that needs to be maintained and doesn't really streamline things all that much, and still have coupling at some level. I much prefer just funneling all data access through one class as a part of the business layer. Used in conjunction with stored procs (that use aliases) the data layer is as seperate as I've ever found it needed to be.

This applies to "ADO vs ADO.NET" in that I've avioded learning ADO.NET at all. I dropped the Data Access Application Block into a VB.NET project and bam! I was programming with data access in under a couple hours (you do have to figure out the Application Block).

This is how I'm currently developing all my VB.NET projects: one business layer hitting the Application Block for all data access. Haven't gotten to performance testing yet but I don't forsee any huge dificulties yet.

Jason Wellnitz
Tuesday, March 26, 2002

you might disagree but I'm using the edit view of a DataGrid for my form input.  I do a custom template of the DataSet I want to capture and call the Update function on the submit button.  All of my fields are automatically linked to a DataSet, after Validation of course, and I have a custom look and feel.  give it a try!!  you'll kick yourself for writing another form from scratch......

Benji Harrell
Tuesday, March 26, 2002

(Second try - Joel, there's still a bug somewhere that eats random postings)

John, I'm not sure what doing away with COM has to do with not using a validation layer. You can certainly create a separate validation layer to use with ADO.NET . You may not have to do so, though: the DataSet includes the ability to specify relationships between tables and other constraints, which gives you validation as soon as data is saved from the UI to the DataSet, before you write it to the back end database.

Or, of course, you can validate in the UI or the database if you prefer.

Mike Gunderloy
Tuesday, March 26, 2002

I for one am happy to see ADO heading down the road to deprecation. I struggled for quite a while to make ADO do what I wanted, and mostly that was operate in a disconnected environment. For me, ADO.Net is a vast improvement, albeit a bit of a naming screwup. They should have left ADO out of the name, 'cause it looks nuthin like ADO, and it DID take me a while to wrap my head around the concepts. I find the API to be a well thought out design, and, yes, you do need at least 2 to 4 objects collaborating to get at the data, but it IS pretty easy once you get the hang of it.

Justin E. Pitts
Thursday, March 28, 2002

In regards to the necessity of a data access layer:  I have found a huge benefit to abstracting data access for reusability in my applications.  I am a SQL-Server guy, but working for a company who has went to bed with Oracle.  So now I am faced with .Net data abstraction using the OleDb.net provider.  I have saw the benefits of the SQLClient provider and would expect in the future to see a native oracle provider for ado.net as well.  I agree that MS has finally admitted that native database support can provide benefits over UDA, but what I feel has happened is the marketing powers have overthrown users to get more buy in for SQL Server (of which I am a pro-ponent).  I am curious if anyone has abstracted the Microsoft Data Application Block for SQL Server and migrated it to Oracle?

Eddie Frederick
Thursday, March 28, 2002

Using ADO I found that with a simple database class, it was possible to achieve pretty well any data access operation that I wanted to simply by using the properties and methods of a connection object, including transaction processing.
Pretty well it was as easy as it can possibly get.
The DB class that I developed did'nt care what type of DB it was connected to and simply used SQL string parameters to get recordsets, or to do updates or inserts (Connection.Execute) etc, typically the objects contained the methods to insert or delete themselves.
If ADO is not this easy then its simply more complex and not an improvement, now I have'nt checked out ADO.NET for even a second yet, will somebody please, please tell me that its easy.
DAO, RDO, ADO, ADO.NET...blah...blah...blah...using ADO.NET is it a one line statement to execute some SQL against a DB.

i.e conn.Execute (strSQL)

Tony
Sunday, March 31, 2002

Woops sorry I posted direct to your email - serves me right for not scrolling down.

I think the point is not so much the changes to ADO API / syntax, but that at last there is a common tool that can be used for deploying an application inside the firewall AND outside it. 

Prior to this, the Microsoft solution was VB/Access for front ends on the LAN/WAN, and ASP for INTERNET (browser based) front ends.

-Regs,
PM

Paul Murphy
Sunday, March 31, 2002

Tony, no implied commands on Connection objects in ADO.NET. You need to declare things explicitly. Something like this:

Dim cnn As New SqlConnection(strConnection)
Dim cmd As New SqlCommand(cnn, strSQL)
cnn.Open()
cmd.ExecuteNonQuery()

Mike Gunderloy
Sunday, March 31, 2002

Thanks Mike, I checked it out, and I've stopped complaining.
Cheers Tony

Tony
Monday, April 01, 2002

It took me forever to find a simple example of calling a select statement in a DB looping through the data and adding it to a simple control (listbox) WITHOUT datbinding. It seems databinding is every authors pet.  I hate it.

Clint Gallon
Wednesday, April 03, 2002

For the person who asked "What's wrong with ODBC"?

1) Many ODBC implementations only allow you a subset of commands. (With ADO, I can write code to, say, create a table and a stored procedure on the fly.  Very rare you would need to do it, but when you need to, you NEED to)
2) ADO can also be used to: i) look at files or directories; ii) access LDAP iii) and on and on.
3) ADO treats the table as a Data Object - and the advantages to that for an Object-Oriented programmer are many

That's just off the top of my head, BTW.

And since you said something to the effect of "MicroSoft had to implement a new standard because that's the way MicroSoft is" - I will point out that I'm more of a Java programmer, and Java implemented JDBC levels 3 and 4 for very similar reasons to the above.  (JDBC level 2 is similar to ODBC; level 3 and level 4 are much more)

And then there is Container Managed Persistence (CMP)....

heck
Monday, April 08, 2002

We have been attempting to build a few .NET apps over the last month or so and I have to say that I am extremely frustrated and dissapointed with ADO.NET and in other ways with the entire framework itself.

Most of this may have to do with the fact that I'm writing web apps and maybe things work better when you're building desktop apps (windows forms), but what they have done with ADO makes no sense to me.

Since the database (SQL Server) stores data tabularly and your application is supposed to work with data as "objects" for a long time we have written our own objects that encapsulate the data in the database in a way that is object-oriented for consumption by the application itself.  Any need for disconnected recordsets or caching is handled at this layer.

ADO.NET forces us to write an enourmous amount of redundant code and for what benefit?  Now I can talk to XML the same way I talk to SQL server?  I could do that before and it was less confusing.

I'm also very frustrated with the type system; a GUID is still a string dammnit!

...ok, no more red bull for Jason...

Jason J. Gullickson
Wednesday, April 10, 2002

I am  wondering if anyone has looked at modifying the Data Access Application Block...

http://msdn.microsoft.com/library/default.asp? url=/library/en-us/dnbda/html/daab-rm.asp
(remove space)

to utilise strongly typed datasets? The technique outlined in the example in the following document...

http://msdn.microsoft.com/library/default.asp? url=/library/en-us/dndotnet/html/buildntierapp.asp
(remove space)

demonstrated the advantages of strongly typed datasets in Visual Studio. However they seem to have been forgotten by the developers of the Data Access Application Block.

I would also be interested to hear from anyone who had ported the application block to Oracle.

Scott Munro
Friday, May 03, 2002

Looks like the problem with truncation of long 'words' has been resolved.

Scott Munro
Friday, May 03, 2002

I too am very interested to see if anyone has made a port of the Data Access Application Block over to Oracle.  I began the process quite some time back and haven't had a chance to finish it.  One disadvantage is there is no native way to dynamically discover the parameters for a stored procedure in Oracle.  I had to manually build a stored procedure in the database and use that call to do the dynamic discovery.  I also ripped out all of the XMLReader code due to Oracles lack of native XML support in 8.i.  If anyone has had great success with porting the DAB to Oracle please let me know!

Eddie Frederick
Thursday, May 16, 2002

Hey there!

I am developing an aplication that connects this way:
ADO -> Oracle oledb provider -> Oracle8i.

And that is because I need to return recordsets via REF Cursors from stored procedures. (only last oledb provider versions support this)

Does anyone know whether I have this functionality on ADO.NET or has anybody tried it out?

I appreciate any info. Thanks in advance!

Dario Liberman
Thursday, June 06, 2002

We have used ADO for years and have developed a effective methology for large integrated software packages.  As we are examining ADO.net, all we see is web ASP techniques.  It appears that using ADO.net in Windows applications will add addional sysntax that was not needed with ADO.  All we want to do is SELECT, INSERT, UPDATE,and DELETE.  The rest appear to be just work arounds to make ADO.net work on the web so big companies can create web server applications for reoccuring revenue fees.  What happend to the KISS principle?

Rob Mayes
Tuesday, December 24, 2002

For anyone who is still looking for the Oracle version of SqlHelper, it's included as part of the Nile demo application available from the MSDN downloads page.

Cheers,

Liam
Sunday, January 19, 2003

Last year we finished an enterprise app in VB6. It's datastore is SQL Server 2K. We had most of the datalayer running in COM+ on a Win2K Advanced Server 4-processsor machine to take advantage of object pooling, blah blah blah. The data layer returned objects containing many collections of other objects, for the most part, to the VB6 client that requested them. The client mostly interacted with the objects, very few recordsets were returned.
The VB6 version with it's datalayer running in COM+ on the server, not the database server, had fantastic scalibility. The execution time of a large request was less than 10 seconds max and 3 seconds average, even from accross the country! (We have a frame-relay based network that stretches from NYC to CA, Florida to Alaska. Some branches have T1 connections, and some share a 56K connection!)

Okay, now along comes VS.NET. We are currently rewriting this app in C# and some VB.NET and are using the same database, same optimized stored procedures, same overall object model. But performance testing thus far is dismal at best. 46 seconds on average request and 72 seconds on max request. This is tested from several locations nationwide.
We have tried running the datalayer on the client and access times slowed down! Running the layer as a web service on another IIS machine halved the above times but it's still FAR TO SLOW. The only thing left to try is using .NET remoting and using the binary formatters for tranfer. That may be next.
.NET data access does not support COM+, transactions accross multiple systems or object pooling and is, in my opinion, NOT YET READY FOR THE BIG TIME! It well may be someday, but not today.

Motto: ADO transfered data in strictly binary format. Result? Lighting fast speed but cant pass over http or firewalls. ADO.NET converts everthing to XML and tranfers that as text. Result? SLOW speed but can pass thru firewalls and over http.

So, in the context of our needs for this app, ADO wins hands down!

Steven Killick
Wednesday, January 22, 2003

We developed a data access driver to native query result tables to .NET. Everything went quite fine. DataTable and DataView were quite useful for most purposes. "Most purposes" are always creating rich reports based on those results, not updating anything in the original database.

But then we wanted to join two DataTables.

Uh oh.

Daniel Daranas
Friday, March 07, 2003

This is really a very good stuff for understanding what are the pros and cons of ADO.NET, Thanks for all the info

Adimulam Srinivasa Rao
Tuesday, April 01, 2003

An update on ADO.NET w/ Oracle.  Since my last post I have a very generic, functional data access application block that is a spin off of the MS example DAAB for SQL Server.  It is provider independent and makes heavy use of the object interfaces (i.e. IDbDataParameter, IDataConnection, etc...).  It is very flexible and I have had great success with it.  Now that I have a native provider for Oracle I have incorporated the dynamic discovery of stored proc params and it performs great.  I realized after spending many hours on developing a DAL that moving away from provider specific code to a more generic approach allows me to achieve optimal performance and keep my business logic generic enough to port to almost any data provider with only changing code (stored procedures, etc) in the database.  I also use a web service that I developed to discover connection information based on a set of <appSettings> that I include in my web.config or app.config and that is working as well.  During my port to Oracle for the DAAB I noticed a couple of major issues:

There is a bug in the .NET Oracle provider for SP parameters of direction output, and type number.  I have a virtual function in a base class that does the work that can be called (if overridden) to custom massage any parameters at the provider level. 

The caching mechanism in the DAAB example has a memory bug.  The DataHelper class that manages caching has the potential for allowing parameters cached to be updated inside the cache (specifically the parameter values) and needs a bit of tuning to clean up. 

Other than that, e-mail me if you wish to obtain a copy of my provider independant data access application block.  It currently seamlessly supports Oracle, Sql Server, ODBC and OLE DB providers.  Has been debugged quite well and is in production in several applications I have written at my job.  I also use it on a couple of websites that I operate independently.

Eddie Frederick
Thursday, April 10, 2003

I am ready to give ADO.NET. Performance is unacceptable. Simple “add new record” operation on form with 2 related data tables takes 8- 20 seconds. Bottleneck is dataset “Merge” and dataset “AcceptChanges” methods.

Pavel Ivanov
Wednesday, February 04, 2004

This may be off subject, but is there an Error prevention in ADO.Net

Try{}
Catch{}
End Try

I'm writing VBScript, ADO and really need this error protection.

On another note just as a question, what is required to be installed on the database server to connect using ADO.Net as opposed to ADO, and is there a simple way to convert pages already programmed from ADO to ADO.Net. =P

Jason Bogh
Wednesday, March 10, 2004

these are great comments ... really.  i'm about 3 weeks into ado.net and still feel the pain of each and every newbie who's written above.

aside from this, as an old-school ado developer, what i want to know is how to apply each returned database value to a variable.

i.e. ... strAddress =

where strAddress is the variable, and rsCity("fldLocationAddress") is the field, "fldLocationAddress" that was included in my recordset called, rsCity.

unless someone can tell me a reason to do otherwise, i prefer to assign each of my returned recordset values to a meaningfully named variable.

great thread!

Dave
Thursday, April 15, 2004

my example above was cut off ... here it is again:

Address = rsCity ("fldLocationAddress")

just omit the space between rsCity and the first bracket.

cheers.

d.

Dave
Thursday, April 15, 2004

Response to:
strAddress = rsCity ("fldLocationAddress")
message

I don't know if this is the best way but this is how I do it, comments welcome

1. Create SqlConnection for your database
2. Create SqlCommand with T-SQL query
3. Open SqlConnection
4. Create a SqlDataReader by using SqlCommand.ExecuteReader(CommandBehavior.SchemaOnly)
5. Assign an int variable to hold column ordinal for SqlDataReader.GetOrdinal("fldName") (say, co_LocationAddress)
6. Grab the actual data by calling SqlCommand.ExecuteReader()
7. Loop with SqlDataReader.Read()
8. Pull value of field with SqlDataReader.GetString(co_LocationAddress) or SqlDataReader[co_LocationAddress]

Note: I'm using C# and referencing instances by their class name (instead of SqlDataReader.Read() you actually would use dr, or whatever you called it, dr.Read()

max
Thursday, July 15, 2004

*  Recent Topics

*  Fog Creek Home