The C# Version of Our ASP.NET AJAX CascadingDropDown VB Database Post

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.