MS SQL

One of our most popular posts on this site, at least in terms of search engine traffic, is our Using ASP.NET AJAX CascadingDropDown With Visual Basic and a Database article from February 2008.  Long story short, in that article I broke down a Microsoft ASP.NET AJAX tutorial, translating their C# code into Visual Basic and used a MS SQL database for the datasource.  Good times.

Seems that some folks out there, people more inclined to use C#, actually like OUR example better and have spent time translating our code back into that language.  Oh, how the tides have turned.

One of those C#ers, a Mr. Bradley Hall, was kind enough to post his C# version of that sample code in the comments of that post.  With his permission, I'm throwing it up here, too.  Thanks again, Bradley.

This morning, I had the chance to play around with another ASP.NET AJAX control:  the CascadingDropDown control.  Like the name implies, it's a nifty little AJAX-enabled control that dynamically enables and populates a DropDownList based on the value selected in a previous list.  Kinda like the coding version of those old skool "Choose Your Own Adventure" books.

You've seen this kind of thing in action plenty of times, I'm sure.  Remember the last time you filled out a registration form that had you pick a country, then you suddenly had a "State/Province" list that only included regions within that country?  Well, this is Microsoft's implementation.  Check it out:  http://www.asp.net/AJAX/AjaxControlToolkit/Samples/CascadingDropDown/CascadingDropDown.aspx

As with most of the ASP.NET AJAX stuff, there's also a great "How Do I..." video (http://www.asp.net/learn/ajax-videos/video-77.aspx) from Joe Stagner (http://www.joeon.net).  Thing is, though, that the video uses an XML file for its data source.  Not real helpful for me, seeing that my data was coming from an actual SQL database. 

In our last SQL post, we talked about the three basic types of JOINs enterprising young SQL junkies might use to gather data from different tables in their databases.

"Enterprising..." Oh, how I crack myself up some times. Seriously. Don't make me explain why that's funny.

Anyway, in between Star Trek-related examples, we blabbed on and on about INNER JOINs, three kinds of OUTER JOINs, and the almost completely useless CROSS JOIN. You might remember, though, that every one of those brilliant examples involved querying existing data with SELECT statements. That's fine and dandy. After all, that's also how you'll use those JOINs about 98% of the time. However, JOINs aren't just confined to SELECT statements...

No, sir. You can use JOINs in your INSERT, UPDATE, and DELETE statements, too. Sit back and watch.
First, let's resurrect our sample tables from the last post. If you'll recall, we had a STARSHIP table that contained the following data:

And an OFFICER table that looked a little something like this:

When you've been playing around with SQL for awhile, you start to take your knowledge of JOINs for granted. When someone asks you about JOINing data from two or more tables together, you get this stunned look on your face... You know, the same one you get when you actually hear someone mutter the words "I've never seen 'Star Wars'." You forget that you, too, once didn't know a JOIN from a Jedi, and that everyone has to start somewhere.

So let's talk about JOINs. Like the name implies, a JOIN is simply a method for connecting two tables in a database, usually through some piece of data common to records in both tables.

And they are pretty simple once you figure them out. Before you bother to start writing queries, you need to ask yourself two questions: what kind of data do you want from each table, and how are those two tables related?

The first question will help you figure out which type of JOIN you're going to use. Do you only care about records in each table when they have a matching result in the other table or do you want all records from one or both tables, regardless of matches found?

And once you've answered that one, you can start thinking about that second question. SQL doesn't know exactly how those two tables are related on its own, so your well-designed database will likely take advantage of some foreign keys or other data that somehow links the tables together.

How 'bout an example? Let's say you're a DBA working in Star Fleet Command (hey, the site's called "Nerdliness," isn't it?). You store information about specific vessels in the STARSHIP table and info about different people manning those ships is in the OFFICER table. Occasionally, some admiral or another asks you to pull information about the ships and their crews. Should be pretty clear by now what SQL operation we'll be performing, eh?

Couple of weeks ago, I was doing some work on a personal Nerdly project on one of my laptops. That particular machine has a copy of SQL Server 2005 Express Edition already installed, and I often use that for my development needs. Has a good number of the same features that the full-blown SQL Server 2005 editions carry, but it's free to use, distribute, etc. Hard to beat that.

Anyway, using Microsoft's SQL is all well and good for most of my work projects, but this personal project is going to live on my webhost's boxes, and my cheap webhosting account doesn't include MS SQL databases. Needed a way to get the data from my MS SQL 2005 Express Edition database into my MySQL database.

The Express Edition can only create MSSQL backup files out of the box. You can download a watered down version of the Import/Export Wizard for some simple importing/exporting, and that might have worked if I could connect to the host's MySQL database remotely using an ODBC driver. Unfortunately, their server wouldn't allow connections from outside their subnet.

Way I figured, that gave me three options: