I hope everyone got to catch the ADO.NET 2.0 (What’s New Overview) webcast that Mark Dunn and Glen Gordon did yesterday. If not, look for it to show up in the on-demand webcasts soon.

Tomorrow at 12pm EST I will be doing another ADO.NET 2.0 webcast with Glen, specifically on the topic of MARS (Multiple Active Result Sets). This is a great feature of SQL Server 2005 that is leveraged by ADO.NET, but before you use it, you should understand what it does, where you will benefit from it, where you will be better off without it and some potential booby traps to avoid.

MARS will let you do some really interesting stuff with transactions as well as help you write some cleaner code in your ADO.NET.

Register here for the MARS webcast.

http://www.AcehAid.org

I am going to be doing a webcast with DCC Glen Gordon on the topic of MARS – Multiple Active Resultsets – on May 25th. This is one of a pair of ADO.NET 2 talks that Glen and Mark Dunn are doing together, but I’ll be filling in for Mark on this one. MARS is a topic that I have been digging into for almost the past year as part of my explorations of ADO.NET 2.0, so I’m looking forward to the webcast which will be full of how-to’s and watch-out’s!

The first webcast is an ADO.NET 2.0 overview that Mark & Glen will be doing on May 23rd.

Glen has a deep level of experience with SQL Server, so I am really looking forward to his perspective on MARS and am happy that Mark thought of me when he realized he had a difficult scheduling conflict.

You can register here for the MARS webcast.

http://www.AcehAid.org

SqlDependency is really easy to get working if you are an admin, but when using it in a web app (whether via the System.Data.SqlDependency or System.Web. Caching.SqlCacheDependency) it is not so easy when you are not running under an admin account, which is what you should be doing (although maybe Don Kiely and Andrew Duthie will make exceptions for Betas!) When  you deploy your apps to a web server, you will need the Network Services account to the SqlDependency work.

In addition to enabling the service broker, I had to do two additional steps when working as a non-admin.

Grant Notification Permissions to specific database
  GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [MyDomain\MyGroup]

Grant NotificationErrorsQueue Perms to user (this is for all of SQL Server, so do it from Master)
GRANT RECEIVE ON dbo.[QueryNotificationErrorsQueue] TO [MyDomain\MyGroup]

But this still didn’t do the trick. In Beta2 there is still a problem when SQL Server is trying to send notifications to the web server that requires opening up ports. There is a wide range of ports to open up. I tried a few but still was unable to get through. This is a known issue and will not persist through to the release mode. I will keep at it and blog the key to success when I find it.

During my presentation at the MAD Code Camp, when I got to this demo, I had to log in as admin to run it.

Thanks to everyone who attended my Web Services Security and ADO.NET 2.0 sessions. I have had so much awesome feedback on the security session which I really appreciate.  The ADO.NET 2 talk is also one of my favorites. I could go on for days with that one as well.

If you are looking for the slides for the sessions and the demos from the ADO.NET talk, they are all posted on the MAD Code Camp site (that Andrew created using the ASP.NET 2.0 Clubs starter kit!!) for download as well as on my own site, on the presentations page.

Keep spreading the word that security does not have to be a mystery!!!

http://www.AcehAid.org

One of the killer problems I originally had with SqlDependency was that I was trying to use it against an old pubs database. Finally I discovered the database property called “Database Compatibility Level” and it was set to Version80. When I changed it to Version90 the SqlDependency worked again.Well, I didn’t have to do that this time and the “Database Compatibility Level” property is nowhere to be found. Well, at least *I* can’t find it anymore. So that’s one less thing to worry about with SqlDependency. Here is all that I had to do to get things working with the latest bits of SQL Server 2005.

http://www.AcehAid.org

I have looked at the benefits of the Load method on DataTables and DataSets that is in ADO.NET 2.0 in an article and a presentation that I have been having fun doing lately. Bill Vaughn gets really granular with the feature and proposes and interesting idea…

http://www.AcehAid.org

Wally and I have been keen on ensuring that SqlDependency – which was pretty tricky to get working when I was first learning about it- still works. Of course it does! 🙂 I have a handful of SqlDependency demos in my ADO.NET 2.0 talk that worked just great with the Feb CTP bits (after a lot of figgerin’ and head scratchin’). I only happen to have fired one of them up so far with Beta2.

The only change I noticed so far was that the OnChanged event is now called OnChange. (You will get a compile error message that says OnChanged is not a member of SqlClient.)

So other than that, my code that worked in Beta1 Feb CTP Bits remains the same and works charmingly.

In SQL Server 2005 the Service Broker is still not enabled out of the box on databases. So you have to remember to enable it with: ALTER DATABASE AdventureWorks SET ENABLE_BROKER

I also noticed something funny but then it went away. In SQL Server Management Studio, my tables first were listed as Contact (Person), Employee (HumanResources), etc. But then after closing it and opening it again, they went back to Person.Contact and HumanResources.Employee. I don’t think I changed anything, so I’m not sure what was up with that but will keep an eye out for it.

http://www.AcehAid.org

I had to get a copy of one of my clients sql server db tables onto my local sql server. For some reason DTS was failing on a particular record and I couldn’t identify the problem or the record or the error.

I finally gave up and went over to my whidbey box, set up an ADO.NET bulk copy and streamed the data from the remote sql server into the table on my local server.

(Without having to learn how to do a bulk copy in SQL Server)

What a great feeling!

http://www.AcehAid.org

I have posted my powerpoint and demos from my ADO.NET 2.0 session. I loved doing this session. I even got to do it twice. I had lots of rave reviews from attendees. A big win-win. I am hopefully going to do it again at the Mid Atlantic Code Camp when I go to D.C. in May and again at my user group. It is a great session. I hope I find more venues to do it at!

You will find the powerpoints and demos on this page under “What’s New in ADO.NET 2.0”. The slant of the talk was to ASP.NET developers, but it’s all back end goo, so it’s for everyone. As Alex Homer points out, yes, I have working SQL Query Notification demos in here!!

This is the same talk I did at Code Camp III in Waltham on March 12th. Thanks to all of those attendees for being my guinea pigs. I was able to improve the talk a lot thanks to your feedback!

http://www.AcehAid.org

I am starting to realize that this tighter integration of ADO.NET and SQL Server means that I am going to have to get much more knowledgeable about SQL Server. I have been (to coin a great book title) a “reluctant DBA” for a long long time. But now that we can do things like BulkCopy in ADO.NET 2.0, something I have NEVER done in SQL and know nothing about, I am in danger of crossing lines that I don’t know even exist. I had a long talk about this with Gert Drapers while I was at DevConnections. Just with the Bulk Copy alone, I have to be mindful of record locking etc etc. I would never have known that and created problems by misusing the bulk copy class in ADO.NET 2.0.

Gert also told me something that made me happy happy happy. As I have been learning and teaching about ADO.NET 2.0, I find myself having to list (or point to) many caveats with MARS since it is on by default. I have wished it would be off by default and Gert tells me that by RTM it will be. That is a good thing. Here is a great MARS FAQ post by Angel Saenz-Badillos of the ADO.NET team.