Heal Your Church WebSite

Teaching, rebuking, correcting & training in righteous web design.

Generic ADO Connections for ASP.Net

So you think you want to go the Microsoft Web Server route after reading a NetCraft post entitled “ASP.NET Overtakes JSP and Java Servlets” ??? Okay, I can understand that. I mean even with CPanel and WHM, managing an Apache web server can be a bit daunting. And while MySQL is free, there are some nifty tricks SQLServer has to offer, if you’re willing to pay for it.

While personally, I feel far more secure behind a Linux-based webserver, I’m required by my day job (you know, the one that pays the bills) to do the .NET/Windows Server dance.

One of the issues you might face in making the transition from one operating system and/or webserver to another is accessing your data. This is why APIs such as the PHP-based ADOdb and Perl DBI are popular on Linux/Apache platforms.

Similarly, this is why most competent Microsoft programmers access databases with interfaces such as the ADO, and more recently, OLEdb, unless of course you’ve been asked to use the MSDE 2000 while your crack technical staff obtains a license to and installs THE Microsoft SQL Server.

Wide Variety of Standards
Yeah, I know, the above paragraph reminds me of the only joke ever told by professor Frank da Cruz in my netorking class at Columbia University, a joke who’s punchline was “a wide variety of standards.” Took me about 3 seconds to get it, but I was sure to laugh out loud when I did (yes, I know, I’m a shameless suck-up that way, but I digress.)

In case you missed the reality that made the punch line funny, there are just too many daggoned different ‘connectivity standards’ out there for databases. Probably due to the fact that there are just too many daggoned RDBMS tools out there, many of which are rotting legacy systems that you and I are now compelled to maintain and integrate. I mean, forget deciding whether or not to use VB.Net over C#, how do I seamlessly patch together an enterprise where the the CEO’s contact database is in MS-Access, an HRD system in Oracle, and the sales and tracking system is running on an MS SQL and still have code that is reusable?

One solution I read about this weekend comes in the form of a nifty GenericADO connection class in VB.Net, based upon a similar generic data access component in C# by the same author.

Need connection strings?
This generic approach works fine, once you figure out the connection string … which itself can be a black art when dealing with anything other than a Microsoft database technology. Well, that is once you download, install and then reference the ODBC .NET Data Provider (why this isn’t included in distributions of Visual Studio .NET is beyond me).

Still, ODBC wasn’t the real show-stopper when I first took-on .Net two years ago … rather it was Sybase. The biggest problem was figuring out the whole ASE thingie, an effort hampered by the fact that Sybase released a buggy version of their Studio installer.

Once over this hump, and once you’ve spent another half day discovering you need to first create an OLE DB connection object via the Sybase ASE OLE DB Administrator, then all you need to do is hack away until you find a connection string that works.

At least that’s what I had to do because at that time, there wasn’t an instructive website chock full of examples for every known .NET Data Provider Connection String available to man and geek.

Lobotomy Time
If you haven’t figured it out, this post serves some selfish reasons, such as links to websites and tips on how to dive through the flaming hoops of database connectivity in a Microsoft-based enterprise without ‘burninating’ myself into a crisp. Hopefully, some of you guys and gals will find these links useful.

And for you religious purists who not only know what a “Factory Pattern” is, but are so entirely snooty as to pooh-pooh such utility classes, here is a link to some rather hairy template connection classes that are sure to amaze and confuse your co-workers.

As always, your mileage may vary.

One Comment

  1. Mmm. The second half of that article makes me glad I use OSS. I’ve used both Access and PostgreSQL via python and PERL and never once needed to figure out what a “Data Provider” is. Even better than that, I love using Zope’s ZODB object database. No more silly object <-> RDBMS impedence mismatch 🙂