Or ten. But here's a condensed look at some of the changes and new features we would want to encounter while migrating from ADO.Net v1.x. Earlier we had a look at what's in store for us C# 1.x developers jumping onto the C# v2.0 bandwagon. I've so far just scratched the surface when it comes to trying out Microsoft's new baby. In this little write up, I like to take a high-level look at ADO.Net 2.0 and the little enhancements and additions that we can look forward to in helping us with our little projects and tasks and jolt down some of the pointers I've got up my mind so that even I  can come back here for a reference. Hmmm, giving it a second thought, it would be a misnomer to say that ADO.Net v2.0 has "little enhancements and additions". Playing around with some of the features and having so much more left to learn and check out, we're actually talking real huge mega changes to contend with especially if you get SQL Server 2005 into the picture.

  1. Ladies and gents, we've got CLR integration with SQL Server 2005 ( Yukon ): If you are planning to work on ADO.Net v2.0, the first thing that will automatically embed itself into your mind is that the new version offers solid support for Yukon . This you will understand once you add a reference to Microsoft.SqlServer.Server namespace in your application. You'll remember that the Java Virtual Machine (JVM) was inbuilt with Oracle (which made it a valid reason to show up in any list of "why Java is better than .Net "- clap clap). Yukon is now integrated with the .Net CLR and this is going to make a world of difference for us little developers.By including the Microsoft.SqlServer.Server namespace, you open your little code to a huge ton of functionalities for working with Yukon . Yukon even allows us to have a .dll execute as a stored procedure.

Let's say I make a database project and need to make a little .dll to validate text. So I do the C# coding part (giving a reference to Microsoft.SqlServer.Server namespace) and compile it. Compiling this little program will get my .dll file registered with Yukon . Okay cool. Now let's say I have a table with a column called UserRole which I need to check. I could easily do this by running a little select query that looks something like:

SELECT UserRole FROM Users WHERE dbo.ValidateUserRole(emailid) = 1

I found this super cool! If you find yourself more comfortable with C# coding compared to writing queries and complex logic stored procedures in SQL Server 2005, you could opt to write a C# .dll itself and try another work around. So just imagine the endless possibilities with such a powerful functionality from our database side.

  1. Asynchronous work: Thinking that your application is stuck when in reality it was actually doing some hard work retrieving around 100000 rows is BAD and for some, really irritating. No more problems on this little black spot from now on�.ie if your using ADO.Net v2.0 ie. This feature is now available to us by default. Imagine for a second, we could have multiple active result sets. Our DataReader object can have multiple active .Read()'s and no more of those ugly "Already open" errors telling us to close the DataReader connection!
  2. New ADO.Net family dude - the DataTableReader: Now we can take the contents of a DataSet and place it in an object of a new Class called the DataTableReader. So we have something similar to the DataReader except for the little enhancement (and maybe even more. I'm yet to find out or hear about it) that the DataTableReader can use the DataSet as a DataSource..
  3. DataTable Enhancements: They've tried to make the DataTable a little bit more advanced that it earlier was. Infact, you might find it compete with the DataSet since the DataTable class has a couple of features that already exist with the DataSet. Now we can do a DataTable.ReadXml, DataTable.WriteXml and even perform Webservices and remoting.
  4. Retrieving schema information: I have'nt tried this out but this is something that one of the .Net practitioners in my company mentioned. The feature being that we can now get all sorts of schema related information from our database.
  5. DBFactories and DBFactory: in ADO.Net 1.x, we had to manually create interfaces for creating a custom DAL and I think Microsoft had also given us some API but I don't quite know anything about that since I never worked on it. I just read two articles from Scot Michel (the 4guysfromrolla guy) I think. Anyway, coming back to ADO.Net v2.0, we can use DBFactories and DBFactory classes to use real Powerful DALs that are independent of the Database provider. Factory is meant for one particular provider while Factories is meant for many providers.
  6. SqlBulkCopy class: Earlier we had an .exe file called BCP.exe to load data onto the database server. Now we have this beautiful class to programmatically do what BCP.exe does. Actually this was possible earlier BUT very much restricted and we would have required some indepth knowledge of C or C++ to get this done (yes, there was an API for this). Using the SqlBuilkCopy class, you could take data from a text or XML file and load them into a table and do necessary mapping also. This would give a better performance enhancement compared to using the normal method of going through inserts. The SqlBulkCopy object would provide us with a WriteToServer method and give us another alternative to insert table. There is more to this and I really have to do a lot of playing around to find out.
  7. Transactions: Now we've got a new namespace that can help us programmatically control a good chunk (if not all) aspects of SQL transactions. Give a reference to the System.Transactions namespace and you'll actually be surprised with some of the neat stuff you can do. We can actually have the option of controlling whether a transaction gets committed or not. Or even roll back a set collection of transactions�something like a "global" rollback. You can confirm this by clicking My Computer, select Distributed Transactions and check out Transaction List.
  8. SQL Server notification Services: This is yet another great functionality. Ever wanted to see the messages your stored procedures would give? I've used to test most of my stored procedures by using a print @variable all over the place to check the output on Sql Server 2000. And then click on the messages tab in Sql Query analyzer to see what happened in my Stored Procedure. You can something similar if you wanted from your code by using this namespace. Well, that's just an example. Ever wanted to read Cache results? Keep exploring�.
  9. DataAdaper.Update: In v1.x, the DataAdapter.Update would have done a round trip for each row. Now with ADO.Net v2.0, no such thing. You could use DataAdapter.UpdateBatchSize property and forget about this earlier performance snag.
  10. DataSet Performance: Oh, did I mention that the performance boost in the new ADO.Net 2.0 DataSet is some 200% more? Well that's what Microsoft actually said so you might want to take that with a little pinch of salt.  Anyway, trial and error is the best way to find out. They say that the DataSet has a new binary remoting option enabled and therefore, this reflects into less memory and bandwidth wastage, better latency (end to end time) and some more blah blahs which I don't remember. They also claim that the DataSet is expected to be much faster when it comes to changes within a DataSet. Has anyone out here tried any experimentation on the new DataSet? I would love to hear expositions from your side and a critique would even be more welcome (I grew up anti-microsoft but eventually had to surrender thanks to my present work requirements)Well, I'm thankful that I found whatever little time I could to explore some of these new features and get a vague idea of what I can expect once I do a full fledged commitment to develop all my Microsoft Applications on .Net.

I'm pretty much a beginner when it comes to working with Microsoft's new .Net 2005. Have you gone in-depth into database programming with .Net 2005? Learning a new technology is simply amazing especially if it's a significant enrichment from the technology you loved playing around with. I would love to hear other sides of the story, or thoughts and second thoughts on any of the points that I've just mentioned here. So time to call it a day. I'm getting real drowsy and it's time to retire on my faithful bed. Oh! Speaking of sleep� I'm going to put up another post on a real inquisitive set of thoughts that is running wild within my crazy mind these days apart from the usual programming stuff.  Until then - Good night and God bless