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.


using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using AjaxControlToolkit;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class nbaplayers : System.Web.Services.WebService {

[WebMethod]
public CascadingDropDownNameValue[] GetConferences(String knownCategoryValues, String category)
{
String strConnection = ConfigurationManager.ConnectionStrings["nerdlinessConnection"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(strConnection);
String strConfQuery = "SELECT * FROM CONFERENCE";
SqlCommand cmdFetchConf = new SqlCommand(strConfQuery, sqlConn);

SqlDataReader dtrConf;
List myConferences = new List();

sqlConn.Open();
dtrConf = cmdFetchConf.ExecuteReader();

while(dtrConf.Read())
{
String strConfName = dtrConf["conf_name"].ToString();
String strConfId = dtrConf["conf_id"].ToString();

myConferences.Add(new CascadingDropDownNameValue(strConfName, strConfId));
}

// Cleanup objects.
cmdFetchConf.Dispose();
dtrConf.Close();
dtrConf.Dispose();
sqlConn.Close();
sqlConn.Dispose();

return myConferences.ToArray();
}

[WebMethod]
public CascadingDropDownNameValue[] GetTeams(String knownCategoryValues, String category)
{
String strConnection = ConfigurationManager.ConnectionStrings["nerdlinessConnection"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(strConnection);
String strTeamQuery = "SELECT * FROM TEAM WHERE conf_id = @confid";
SqlCommand cmdFetchTeam = new SqlCommand(strTeamQuery, sqlConn);

SqlDataReader dtrTeam;
StringDictionary kvTeam = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);

Int32 intConfId;

if (!kvTeam.ContainsKey("Conference") || !Int32.TryParse(kvTeam["Conference"], out intConfId))
{
return null;
}

cmdFetchTeam.Parameters.AddWithValue("@confid", intConfId);

List myTeams = new List();

sqlConn.Open();
dtrTeam = cmdFetchTeam.ExecuteReader();

while (dtrTeam.Read())
{
String strTeamName = dtrTeam["team_name"].ToString();
String strTeamId = dtrTeam["team_id"].ToString();

myTeams.Add(new CascadingDropDownNameValue(strTeamName, strTeamId));
}

// Cleanup objects.
cmdFetchTeam.Dispose();
dtrTeam.Close();
dtrTeam.Dispose();
sqlConn.Close();
sqlConn.Dispose();

return myTeams.ToArray();
}

[WebMethod]
public CascadingDropDownNameValue[] GetPlayers(String knownCategoryValues, String category)
{
String strConnection = ConfigurationManager.ConnectionStrings["nerdlinessConnection"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(strConnection);
String strPlayerQuery = "SELECT * FROM PLAYER WHERE team_id = @teamid";
SqlCommand cmdFetchPlayer = new SqlCommand(strPlayerQuery, sqlConn);

SqlDataReader dtrPlayer;
StringDictionary kvPlayer = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);

Int32 intTeamId;

if ((!kvPlayer.ContainsKey("Team")) || (!Int32.TryParse(kvPlayer["Team"], out intTeamId)))
{
return null;
}

cmdFetchPlayer.Parameters.AddWithValue("@teamid", intTeamId);

List myPlayers = new List();

sqlConn.Open();
dtrPlayer = cmdFetchPlayer.ExecuteReader();

while (dtrPlayer.Read())
{
String strPlayerName = dtrPlayer["player_name"].ToString();
String strPlayerId = dtrPlayer["player_id"].ToString();

myPlayers.Add(new CascadingDropDownNameValue(strPlayerName, strPlayerId));
}

// Cleanup objects.
cmdFetchPlayer.Dispose();
dtrPlayer.Close();
dtrPlayer.Dispose();
sqlConn.Close();
sqlConn.Dispose();

return myPlayers.ToArray();
}

}

Now I hate to insult Bradley in any way, but I should point out that this is his code, not ours, and that if you aren't a C# aficionado you'll have to trust him that it's does what you'd expect. And, of course, use it at your own risk.

Disclaimer aside, it looks great by my limited C# experience, and again...  thanks, 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. 

The sample website has another page that claims to show how to use the CascadingDropDown with a database backend (http://www.asp.net/AJAX/AjaxControlToolkit/Samples/Walkthrough/CCDWithDB.aspx), but I frankly find it wanting.  First of all, it's not really a "tutorial" in the sense that it doesn't really walk you through how to do what I wanted to do step-by-step.  It assumes you know what you're doing to a certain extent, an assumption which we here at Nerdliness.com have proven time and time again can be fatal.

The second problem with it is that the sample uses C#, with no Visual Basic equivalent.  Now, I'm not a real developer.  I don't even play one on TV.  Hell, I'd be lucky to be the dude they bring in to double for the developer-on-TV when they need to adjust the lighting and all that.  Sure, maybe I could be the key grip on the developer show but...  Ok, you get the point.  I'm sticking with VB.NET.

So let's show the newbs how it's done.  We're going to create three DropDownLists and, using the CascadingDropDown control, populate the second list based on what was selected in the first, and the third based on what's selected in the second.

In this example, we're asking people to pick their favorite NBA player (for you hardcore nerdlings, the "NBA" is the National Basketball Association...  you know, that sport with all the really tall guys throwing a ball into a hoop).  In the first DropDownList, we'll ask them to pick the conference (Eastern or Western).  The second DropDownList will be populated with teams in that conference.  Once they pick a team, they'll be able to choose from a couple of players on that team using the third DropDownList.

The data will be stored in three tables called, amazingly enough, CONFERENCE, TEAM, and PLAYER.  This E-R diagram should give you an idea of what those relationships look like:

As for the data in each, well, I'm lazy, so I'm not going to include all the teams and all the players.  Just a representative example to show how it works (see attached for a dump of the tables).

(Sure, it's West-heavy.  But is it my fault that's where all the talent is?)

That's all stored in a local SQL Server 2005 Express instance, but that doesn't really matter.  You could be pulling it from SQL 2000, Oracle, MySQL, etc., just as long as you configure the connection in your web service appropriately.  Obviously, I can't really tell you how your connection will look, not knowing anything about your setup.

Finally, a last assumption...  I'm using Visual Studio 2008 for this example, but from what I've seen I don't think there's much difference between it and previous versions (as far as this example is concerned, that is).  If you're using a previous version, make sure you've gone through all the getting-ready-to-use-ASP.NET-AJAX stuff in videos 1 and 2 here:  http://www.asp.net/learn/ajax-videos/

Let's get to it.   First, I have a simple default.aspx file with the aforementioned DropDownLists.  Then, I've added one CascadingDropDown control for each of the DropDownLists in our little group.  Of course, all that's in addition to the stuff we need to make ASP.NET AJAX work at all (registering the AjaxControlTookit assembly at the top, and adding a ScriptManager control).

The properties of the CascadingDropDown controls are all pretty straightforward and you can probably get the idea of what each does just by the name (see http://www.asp.net/AJAX/AjaxControlToolkit/Samples/CascadingDropDown/CascadingDropDown.aspx for more detailed info).  Only things that really need to be pointed out here are the TargetControlId property (shows the relationship between our CascadingDropDown controls and their matching DropDownList controls), the ServiceMethod property (the method we'll soon create in our web service), and the ServicePath property (the path to our soon-to-be-created web service).  Also note the ParentControlId on the last two, pointing to the DropDownList above it in our little heirarchy.  Without them, there's no way for our little controls to know how they're related.

One other thing about our .aspx file...  According to the ASP.NET AJAX sample site, you need to turn off EventValidation for this whole thing to work:

Finally, in order for the values to be submitted, EventValidation needs to be disabled for the page. EventValidation ensures that the values in each control match the values that were present when the page was rendered, but since these drop downs are populating on the client side, this is never true. Were hoping to find a way to resolve this issue but please ensure that you understand the potential risks of this and validate the data appropriately in your post back when using this control.

You'll notice I did just that in the <%@ Page %> directive (EnableEventValidation="false"). 

This whole file is really just our presentation layer.  Nothing really interesting happening here.  The good stuff goes down inside our web service.  It's also where the ASP.NET AJAX sample page gets tricky.

I mentioned before that the sample page (http://www.asp.net/AJAX/AjaxControlToolkit/Samples/Walkthrough/CCDWithDB.aspx) makes a couple of assumptions I didn't like.  First, they assume you're using C# and don't include a VB snippet.  Bad form, I say.  Worse, though, is the fact that they make the assumption that you've already created methods for pulling the data from your database, and they use arbitrary names for methods that don't usually exist through their example:

So imagine you've got a database with tables for the Make (manufacturer), Model, and Color information, and you're accessing that database through a DataSet to which you've added methods for getting each set of values.

But if you're a newb, or a strict VB dev guy (sure, maybe that's redundant), you might have a hard time making heads or tails out of their code.  Trying typing in Cars.ColorDataTable colorTable = adapter.GetColorsForModel(modelId); and see what IntelliSense does.  Trust me, you'll have lots of colorful squiggly lines and errors in your future.

So here's how we roll at Nerdliness.com.  In our web service, we're going to use a SqlDataReader to pull the info we want from the previously defined CONFERENCE, TEAM, and PLAYER tables instead of using a DataSet with methods defined behind the scenes.  Frankly, I think this is a bit more straight forward and easier to read, especially for someone new to coding.

And here's the full codebehind file:

nbaplayers.vb

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Collections
Imports System.Collections.Generic
Imports System.Collections.Specialized
Imports AjaxControlToolkit
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
_
_
_
_
Public Class nbaplayers
Inherits System.Web.Services.WebService

_
Public Function GetConferences(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
Dim strConnection As String = ConfigurationManager.ConnectionStrings("nerdlinessConnection").ConnectionString
Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
Dim strConfQuery As String = "SELECT * FROM CONFERENCE"
Dim cmdFetchConf As SqlCommand = New SqlCommand(strConfQuery, sqlConn)

Dim dtrConf As SqlDataReader
Dim myConferences As New List(Of CascadingDropDownNameValue)

sqlConn.Open()
dtrConf = cmdFetchConf.ExecuteReader

While dtrConf.Read()
Dim strConfName As String = dtrConf("conf_name").ToString
Dim strConfId As String = dtrConf("conf_id").ToString

myConferences.Add(New CascadingDropDownNameValue(strConfName, strConfId))
End While

Return myConferences.ToArray
End Function

_
Public Function GetTeams(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
Dim strConnection As String = ConfigurationManager.ConnectionStrings("nerdlinessConnection").ConnectionString
Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
Dim strTeamQuery As String = "SELECT * FROM TEAM WHERE conf_id = @confid"
Dim cmdFetchTeam As SqlCommand = New SqlCommand(strTeamQuery, sqlConn)

Dim dtrTeam As SqlDataReader
Dim kvTeam As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)

Dim intConfId As Integer

If Not kvTeam.ContainsKey("Conference") Or Not Int32.TryParse(kvTeam("Conference"), intConfId) Then
Return Nothing
End If

cmdFetchTeam.Parameters.AddWithValue("@confid", intConfId)
Dim myTeams As New List(Of CascadingDropDownNameValue)

sqlConn.Open()
dtrTeam = cmdFetchTeam.ExecuteReader

While dtrTeam.Read()
Dim strTeamName As String = dtrTeam("team_name").ToString
Dim strTeamId As String = dtrTeam("team_id").ToString

myTeams.Add(New CascadingDropDownNameValue(strTeamName, strTeamId))
End While

Return myTeams.ToArray
End Function

_
Public Function GetPlayers(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
Dim strConnection As String = ConfigurationManager.ConnectionStrings("nerdlinessConnection").ConnectionString
Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
Dim strPlayerQuery As String = "SELECT * FROM PLAYER WHERE team_id = @teamid"
Dim cmdFetchPlayer As SqlCommand = New SqlCommand(strPlayerQuery, sqlConn)

Dim dtrPlayer As SqlDataReader
Dim kvPlayer As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)

Dim intTeamId As Integer

If Not kvPlayer.ContainsKey("Team") Or Not Int32.TryParse(kvPlayer("Team"), intTeamId) Then
Return Nothing
End If

cmdFetchPlayer.Parameters.AddWithValue("@teamid", intTeamId)

Dim myPlayers As New List(Of CascadingDropDownNameValue)

sqlConn.Open()
dtrPlayer = cmdFetchPlayer.ExecuteReader

While dtrPlayer.Read()
Dim strPlayerName As String = dtrPlayer("player_name").ToString
Dim strPlayerId As String = dtrPlayer("player_id").ToString

myPlayers.Add(New CascadingDropDownNameValue(strPlayerName, strPlayerId))
End While

Return myPlayers.ToArray
End Function

End Class
Ok, so first thing to note are the different namespaces we imported at the top of the file.  Make sure you do the same.

Second, notice that we've uncommented the "<System.Web.Script.Services.ScriptService()> _" line.  In Visual Studio 2008, that line's already sitting in the new web service when you first add it to your project, but in previous versions you have to add it yourself. 

Now the meat of the web service comes in the form of our three WebMethods:  GetConferences, GetTeams, and GetPlayers.  You might recall those names from the properties of our CascadingDropDown controls in our default.aspx file.  These are the methods we're using to gather the data from our tables and send it back to our controls. 

The first method, GetConferences, is a little simpler than the other two.  Since we're populating that right away and without caring what other DropDownLists contain, we don't need a lot of the logic.

Most of the logic in that method is dedicated to connecting to our database, creating our DataReader, and pulling data.  We loop through the results returned by our "SELECT * FROM CONFERENCE" query and, for each line returned, add a line to the myConferences collection.  We then convert myConferences to an array and send that result back to our CascadingDropDown control.

The only non-standard issue stuff here involves that CascadingDropDownNameValue bit.  Like the sample site says (typo and all):

Note that the values are returned as an array of CascadaingDropDownNameValue structures. This structure also includes an isDefaultValue boolean field that allows the specification of which value should be selected when the list is first displayed.

So we create a list of CascadingDropDownNameValue pairs in our WHILE loop, then pass that list back to the control.  Control takes it, populates our list, then feeds that information into the web method used by the next DropDownList in our chain whenever you select a value.  Pretty easy.

The other two methods are nearly identical, save for the fact that one takes CONFERENCE input and gives TEAM output, and the other takes TEAM input and gives PLAYER output.  Let's just look at the GetTeams method, then.

You'll notice that a good portion of this is the same as the first method.  That is, we create a SqlDataReader and populate it based on our parameterized "SELECT * FROM TEAM WHERE conf_id = @confid" query.  That @confid parameter will be supplied by the previous DropDownList.  So if someone selects "Eastern" from the first list, we only want TEAMS that are in the Eastern conference, right?

So what's the difference between this method and the previous?  This bit here:

Dim kvTeam As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)

    Dim intConfId As Integer

    If Not kvTeam.ContainsKey("Conference") Or Not Int32.TryParse(kvTeam("Conference"), intConfId) Then
        Return Nothing
    End If
Like the sample site says:

The knownCategoryValues parameter will return a string containing the currently selected category values, as well as the category to retrieve values for.

and...

The CascadingDropDown class has a helper method for unpacking the category values...  This method will return a StringDictionary containing the name/value pairs of the currently selected values.

 
In English?  We're going to look at knownCategoryValues, make sure it contains a key called Conference, and pull out of it the conf_id associated with the selected item.  We then use that conf_id finish dynamically creating our SQL SELECT query:

cmdFetchTeam.Parameters.AddWithValue("@confid", intConfId)
Execute that query and feed it into our DataReader, loop through the results, add the appropriate info into another list of CascadingDropDownNameValues, and send that back to the CascadingDropDown control to enable and populate our ddlTeam DropDownList.

And that's pretty much it.  Like I mentioned, the second and third methods are pretty much identical, so adding a fourth or fiftieth DropDownList to the chain is a snap.

Hope this clears up some things for any other poor saps out there looking to populate their CascadingDropDowns with a database backend and using Visual Basic.

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:

So let's have some fun. First, we're going to add another record to our OFFICER table.

Let's say that we have a dashing new OFFICER who we want to assign to the USS Justinian. Star Fleet HR gave us the info we need for the firstname, lastname, and rank fields (Justin, Stanley, and Grand Poobah, respectively), but we still need to know the right value to insert into the currentshipid field.

One way to go about that would be to simply look it up first by throwing a SELECT statement at our STARSHIP table, writing down the shipid value that corresponds to the USS Justinian, then manually adding that to our normal INSERT statement:

INSERT INTO officer 

    (firstname, lastname, rank, currentshipid)

VALUES

    ('Justin', 'Stanley', 'Grand Poobah', 4)

Obviously, that works just fine and you'll probably end up doing that most of the time. But damn it, Jim! This post is about using JOINs (or will be eventually...), so that's what we're bloody hell gonna do. Before we get to that part, though, we need to get familiar with the INSERT...SELECT syntax.

So let's write that query. The first part looks like your regular ol' INSERT statement. You'll notice, though, that the syntax changes a bit right after we specify the column names of the destination table:

INSERT INTO officer

    (firstname, lastname, rank, currentshipid)

SELECT 'Justin', 'Stanley', 'Grand Poobah', s.shipid

    FROM starship s

    WHERE s.name = 'Justinian'

(Note: I didn't need to specify a value for the officerid column because we set it up as an IDENTITY field that'll autoincrement when new records are added when the table was initially created.)

Almost seems to overcomplicate things, doesn't it? Maybe you thought that you could get away with something like this instead:

INSERT INTO officer

    (firstname, lastname, rank, currentshipid)

VALUES

    ('Justin', 'Stanley', 'Grand Poobah', 

        (SELECT shipid FROM starship WHERE name = 'Justinian')

    )

A lot of people probably try that one first then get pissed when it doesn't work. I'm one of them. Thing you have to remember is that the SELECT statement doesn't necessarily return a scalar value, but that's what the INSERT statement (written in this format) requires. Even if you write the SELECT part so that it only returns one record (using TOP, for instance), this query will still poo itself.

And that brings us to a big warning: when you're using the INSERT...SELECT statement, be 100% certain that the SELECT part only returns a single row. Otherwise, there's a pretty good chance that you'll end up INSERTing more rows than you intended.

For example, what if I was trying to assign this new officer to Picard's Enterprise instead? If I just changed the WHERE statement in my query to 'WHERE s.name = 'Enterprise',' I'd be in for a rude awakening. That query would return results for both ships named Enterprise in my table and, therefore, would insert two rows into the OFFICER table with my new guy's info, one with shipid = 1 and another with shipid = 2 (the shipid's for the two ships named 'Enterprise' in my STARSHIP table).

So let's get to the JOIN example. Let's say that we have another new OFFICER to add to the table. Again, Star Fleet HR gives us his firstname, lastname, and rank ('Luke', 'Skoubo', 'Junior Lackey'), but this time they also tell us that he's to serve under Grand Poobah Stanley.

Well, again, we could just use a SELECT statement to find the currentshipid for Stanley's record and use that to create your INSERT. Boring. We're going to use a JOIN so we only have to write one query and don't have to bother fishing through our desk for a pen and Post-It Note:

INSERT INTO officer

    (firstname, lastname, rank, currentshipid)

SELECT 'Luke', 'Skoubo', 'Junior Lackey', s.shipid

    FROM starship s

    INNER JOIN officer o

    ON s.shipid = o.currentshipid

    WHERE o.lastname = 'Stanley'

Simple, eh? Again, though, just have to make sure that the SELECT part only pulls a single record so you don't end up INSERTing more than you expected.

UPDATEs work pretty much the same way and, frankly, I can think of many more situations in which I've used a JOIN in UPDATEs than INSERTs.

For example, what if Star Fleet decided to implement some cost-cutting measures by decommissioning the old Constitution class Enterprise and transferring all it's personnel over to... oh, what's it called? That one ship that Grand Poobah Stanley runs?

As the Star Fleet DBA, we need to ensure that the data in the OFFICER table reflects that change and adjust our currentshipid fields accordingly:

UPDATE o

SET o.currentshipid = 

    (SELECT currentshipid 

    FROM officer 

    WHERE lastname = 'Stanley')

FROM starship s  

INNER JOIN officer o

    ON s.shipid = o.currentshipid

WHERE s.registry = 'NCC-1701'

Now Kirk and Spock are Stanley's minions. Like the INSERT example, I still had to be careful that the subquery I used to find Stanley's currentshipid only returned on row. However, unlike that previous query, wacky things won't happen if my subquery returns multiple results. Instead, it'll just fail altogether with a warning that it can only return a single record if I expect things to actually work.

Finally, a DELETE example. It's very similar to the others, but I find it a little counter-intuitive in its redudancy. With the DELETE...JOIN statement, you have to list the table twice using the FROM statement to begin the JOIN. Check out the example.

Let's say that the Reliant is destroyed while Chekov and Khaaaaaan are both still on board. Star Fleet, anxious to sweep that dirty little hijacking under the rug, asks you to delete all records of OFFICERs assigned to that boat at the time:

DELETE officer

FROM officer o

INNER JOIN starship s

    ON s.shipid = o.currentshipid

WHERE s.name = 'Reliant'

You might try something like "DELETE FROM officer INNER JOIN...", but you'll faily miserably. Sorry, but I didn't write the language or standards.

Well, I don't know about you, but that's about all the JOINing I can take for awhile. As always, happy to hear any questions, comments, or suggestions.

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?

We're going to make the tables pretty simple for this example. Our STARSHIP table looks something like this:

And our OFFICER table looks like this:

First, no. I didn't know all the information off the top of my head. Only about 95% of it...

And second, for all the Trekkers and Trekkies out there, in this example, Ryker's had a spat with Picard and is no longer assigned to his (or any other) ship. And, because Star Fleet's a government organization and, therefore, probably dirty as all Hell, they've put a positive spin on the whole oh-snap-this -300-year-old-super-dude-just-hijacked-one-of-our-ships thing by issuing a press release stating that Khan was actually given command of the Reliant and all records were forged to jive with the party line.

Oh, and the USS Justinian was just finished, but Star Fleet has yet to find a captain worthy of sitting at the helm of such a fantastic machine.

Anyway...

So there are three basic types of JOINs:

1. INNER JOIN: An INNER JOIN will return all rows from both tables involved so long as there's a matching row in each table. For example, if I did a straight INNER JOIN of my two tables, the query would probably look something like this:

SELECT * FROM officer o

INNER JOIN starship s

ON o.currentshipid = s.shipid

And my output:

That might be useful if I wanted to find the names of all the crew members for all this ships. Or, if I just wanted to know about a specific ship, I could use the WHERE statement to slim down the result set (WHERE s.registry = 'NCC-1707', for example).

Notice a couple of things... First, I didn't specify columns, so my result set contains every piece of data from both tables. In all likelihood, I'd probably only need some of that info and would adjust my query accordingly in real Star Fleet life.

Second, the INNER keyword is actually optional. I like to use it for clarity's sake, but you'll end up with the same results if you leave it out. In other words, the default JOIN with MS SQL is the INNER JOIN.

Finally, see what's missing? Because I used an INNER JOIN, Ryker and the USS Justinian are missing from the result set. I only asked SQL to give me the records that met the "o.currentshipid = s.shipid" criteria, and they didn't fit the bill. And if I actually did want to see all of the records from a table, even if there wasn't a match? In that case, I'd need to use an...

2. OUTER JOIN: Unlike an INNER JOIN, the OUTER JOIN will return all the records from one (or more) of the tables involved. Let's say I wanted to know the names of all current officers and their currently assigned ships. However, unlike with the INNER JOIN, I also want them listed even if they aren't on a ship. That might look like this:

SELECT o.firstname, o.lastname, s.registry, s.name FROM officer o

LEFT OUTER JOIN starship s

ON o.currentshipid = s.shipid

The result?

Suddenly, Ryker matters. We pulled his info from the OFFICE table like we wanted, giving us the complete list of personnel. And for those people assigned a ship, we have all the data we asked for from the STARSHIP table.

Notice that the two columns from the STARSHIP table are both NULL for Ryker. That'll happen for every record that doesn't have a matching result from your OUTER JOINed table.

Also note that the USS Justinian still isn't listed anywhere. The way we structured our query, we only asked SQL for all the records in one of the two tables involved (the one on the LEFT of the equals sign). Guess what word we'd replace LEFT with if we wanted all the records in the table on the RIGHT side? Yep. RIGHT. So if we instead wanted our list to include all the STARSHIP records and, when possible, the assigned officers, we could use the exact same query, only changing that one word:

SELECT o.firstname, o.lastname, s.registry, s.name FROM officer o

RIGHT OUTER JOIN starship s

ON o.currentshipid = s.shipid

Gives us this:

No more Ryker, but now the Justinian shows up. Imagine that. SQL did what we told it to do. And, again, we see NULL values when there aren't any matching records from the JOINed table.

I know your next question: "What if I want everything from both columns, regardless of matches?" Easy. You raise up and get your FULL OUTER JOIN on:

SELECT o.firstname, o.lastname, s.registry, s.name FROM officer o

FULL OUTER JOIN starship s

ON o.currentshipid = s.shipid

And...

Piece of cake, eh? All records from both tables, matching when you can, NULL values where you can't.

Like the "INNER" in "INNER JOIN," the word "OUTER" is actually optional in each of the above queries. You'll see the exact same results whether you type "LEFT JOIN" or "LEFT OUTER JOIN." Again, I think using the full syntax makes your queries more readable and that you're better off with it than without (especially while you're still learning).

Ok, so you've got your INNER JOIN and your OUTER JOIN, and they pretty much cover 99.999999% of the JOINs you'll ever write. However, I promised you a third JOIN when we started this adventure, and I'm a nerd who delivers. I bring you...

3. CROSS JOIN: The CROSS JOIN gives you every possible combination of results from the two tables involved, and in all honesty, I've never ever had to use it in real life. I only mention it thoroughness's sake and I can't even think of a situation in which it might be useful. If you've got one, please please let me know about it. Dying to hear.

Syntax for the CROSS JOIN is a little different. Because you don't really care if any records from table A match anything in table B, you omit the ON keyword and everything after it:

SELECT o.firstname, o.lastname, s.registry, s.name FROM officer o

CROSS JOIN starship s

I'm not going to bother with the result set. With CROSS JOINs, they get really big, really fast, returning the number of records equal to the total in table A multiplied by the total in table B. For instance, our example with six guys in our OFFICER table and four clunkers in the STARSHIP table, the result set of our CROSS JOIN yields 24 records. Now imagine a CROSS JOIN between a table with 100,000 customers and 200,000 orders. For you folks playing along at home, that little query would give you 20,000,000,001 problems (the 20 billion records returned, plus one pissed off DBA).

And that's where our story ends, at least for today. Next time, in a much shorter post, maybe we'll talk about how you might use JOINs in UPDATE and INSERT statements. Or maybe I'll just bust our "Wrath of Khan" and ignore this place for awhile. Who knows.

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:

1. I could download and install MySQL locally, move my data from MSSQL to MySQL with the aforementioned watered down Import/Export Wizard, dump the contents from the local MySQL, and import into the hosted one with PHPMyAdmin or from the command line.
2. I could use the SQL Import/Export Wizard to export each table into a CSV, get my Find-and-Replace on, modify that output to create an INSERT statement for each line, then use PHPMyAdmin to Import the data.
3. I could script something to do Option 2 for me.

I went with Option 3. Already had Visual Studio 2003 installed on my workstation, so I figured writing some VB.NET code to do what I needed would be pretty easy. Take into account that I didn't feel like installing yet another database on this workstation and couple it with the fact that the kind of tedium in Option 2 would have likely caused me to stab out my own eyes and, well, it's a no-brainer.

So here's the setup... I have a simple Windows form with a single Text Field called txtTableName and a button called btnExport. The procedure that handles btnExport's click event is attached, if you want it, but it's really pretty simple. You give it the name of the table, it loops through the names of the columns in that table, and in constructs a list of SQL INSERT statements for each record in that table.

To me, the coolest part about this is that you don't need to know the names of all the columns in the table in order for it to create the output file. Instead, it uses Visual Basic's GetSchemaTable method to find that info for you.

With GetSchemaTable, you can find out all sorts of good info about a table's schema, from the column names and ordinals to whether it accepts NULLs or if it's an identity column. Each column in the table you're curious about ends up being a single record in the DataTable returned by GetSchemaTable, so it's pretty easy to just loop through all those rows to find out the information you need.

In this snippet, I loop through all the rows in the DataTable just to find the column name. I then put that column name into an array that I use later to both query the table I'm interested in and generate the INSERT lines.

For example, let's say I have a NERD table with three columns: NAME, AGE, and NERDLINESS. The DataTable returned by the GetSchemaTable method on that table would have three rows, one for each column. As I loop through the rows, I find the ColumnName column and insert it's value into my array (around line 19 below).

Finding the column names is the hardest part. After that, it's just a matter of querying the original table, putting the results from each row into another array, then spitting all of it out with the appropriate SQL keywords. Line 82 shows you how the INSERT statements are created.

Just a couple of things to keep in mind if you want to use this. First, you need to change your connection string to match your own database. That's on line 21. You'll also want to change line 5 to a path that actually exists on your machine.

Finally, if I really wanted to make this cool, I'd make it a little more user-friendly. For instance, maybe having a dropdown list of all the table names instead of having the user enter one manually. Or having the option to have it pull all data from all tables into one file at once. Both those would be pretty trivial at this point, but this did what I needed it to do. Still, let me know if you found this useful at all.

   1:      Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click

   2:  

   3:          'Declaring a couple of strings we'll use later.

   4:  

   5:          Dim strOutputPath As String = "c:\nerds\"

   6:          Dim strSelectQuery As String = "SELECT * FROM "

   7:  

   8:  

   9:          'Require the user to enter a name of some kind.

  10:  

  11:          If txtTableName.Text = "" Then

  12:              MessageBox.Show("Please enter table name.")

  13:              Exit Sub

  14:          Else

  15:              strSelectQuery &= txtTableName.Text 'Finish our SELECT statement to use the tablename specified.

  16:              strOutputPath &= txtTableName.Text & ".sql"

  17:          End If

  18:  

  19:          'Create our SQLConnection.  I used a locally hosted SQL 2005 Express DB.  Your results will vary.

  20:          Dim sqlConn As New SqlConnection

  21:          sqlConn.ConnectionString = "workstation id=(local);packet size=4096;integrated security=SSPI;data source=nerdcomputer;" & _

  22:          "persist security info=False;initial catalog=nerddb"

  23:  

  24:          'Create our SQLCommand now that we have the query built.

  25:          Dim sqlSelectCommand As New SqlCommand(strSelectQuery, sqlConn)

  26:  

  27:          sqlConn.Open()

  28:  

  29:          Try

  30:              'First need to find out all the column names.  Executing this reader to get

  31:              'that info and put the resulting data into a DataTable.

  32:              Dim dtrReader As SqlDataReader = sqlSelectCommand.ExecuteReader(CommandBehavior.KeyInfo)

  33:              Dim tblSchema As DataTable = dtrReader.GetSchemaTable

  34:              Dim rowSchema As DataRow

  35:  

  36:  

  37:              'Each column in our table is represented as a row returned by GetSchemaTable.

  38:              'The array holding the column names is the same length as the number of rows in the table.

  39:              Dim intRowCount As Integer = tblSchema.Rows.Count

  40:              Dim strColumnNamesArray(intRowCount - 1) As String

  41:              Dim strColumnNames As String

  42:              Dim intColumnCounter As Integer

  43:              Dim intRowsWritten As Integer

  44:  

  45:  

  46:              'Putting the names of all the columns into an array for future use.

  47:              For Each rowSchema In tblSchema.Rows

  48:                  strColumnNamesArray(intColumnCounter) = rowSchema("ColumnName").ToString

  49:                  intColumnCounter += 1

  50:              Next

  51:              dtrReader.Close() 'Closing the reader used to get the schema info.

  52:  

  53:              'Joining the array into one string to construct our output line.

  54:              strColumnNames = Join(strColumnNamesArray, ",")

  55:  

  56:              'Now that we have the names of the columns, need the values for each row.

  57:              Dim strColumnNameValue As String

  58:              Dim strColumnOutput As String

  59:  

  60:              'Opening our reader, this time looking for the data in the table rather than the

  61:              'table schema.

  62:              dtrReader = sqlSelectCommand.ExecuteReader

  63:  

  64:              'Getting ready to write to our output file.

  65:              Dim wtrOutput As StreamWriter = New StreamWriter(strOutputPath)

  66:              While dtrReader.Read

  67:  

  68:                  'Building another part of our output string.

  69:                  'In each returned row, we loop through the column names and add the associated

  70:                  'value to our string.

  71:                  For Each strColumnNameValue In strColumnNamesArray

  72:                      strColumnOutput &= "'" & dtrReader.Item(strColumnNameValue).ToString.Trim & "',"

  73:                  Next

  74:  

  75:                  'The above loop adds a final comma.  Stripping that off.

  76:                  strColumnOutput = strColumnOutput.Substring(0, strColumnOutput.Length - 1)

  77:  

  78:                  'Constructing the actual INSERT query that will be written to the output file.

  79:                  'strColumnNames is the comma separated column names we made earlier.

  80:                  'strColumOutput is the string with the values contained in those columns.

  81:  

  82:                  Dim strOutputLine As String = "INSERT INTO " & txtTableName.Text & " " _

  83:                  & "(" & strColumnNames & ") " _

  84:                  & "VALUES (" & strColumnOutput & ");"

  85:  

  86:                  'Write that INSERT statement to our text file.

  87:                  wtrOutput.WriteLine(strOutputLine)

  88:  

  89:                  intRowsWritten += 1

  90:  

  91:                  'Clear the string names.  If skipped, additional lines are appended to

  92:                  'the previous line instead of being a line of their own.

  93:                  strColumnOutput = ""

  94:                  strOutputLine = ""

  95:  

  96:              End While

  97:  

  98:              'Close our file and our SQL connections.

  99:              wtrOutput.Close()

 100:              sqlConn.Close()

 101:  

 102:              'I like to know when it's done.

 103:              MessageBox.Show(intRowsWritten.ToString & " rows were written.")

 104:  

 105:  

 106:          Catch ex As Exception

 107:              MessageBox.Show(ex.Message)

 108:  

 109:          End Try

 110:  

 111:      End Sub