Drive yourself nuts with .NET

One of my favorite geek blogs is KeithDevens.com. An accomplished coder, he provides several useful insights on the things that I face both in my day job, and when I’m coding-up a plug-in or such for my favorite church web site. Last week, Keith offered a link to a site that offered an extensive list of programmer tools — a link worth a visit to KD’s site alone.

In describing the linked site, Keith remarked that “A lot of the stuff there applies only to .NET and so don’t apply to me yet …” In response to this statement, a reader commented: “I use c#/.NET professionally at work and what i have to tell about it is just the same as about php: looks good at first but too much annoyances.” Indeed!

For those of you who don’t speak fluent geek, please forgive the code-monkey talk, but selecting a programming language can make the difference between walking tall or blowing your foot clean off. With so much hype out there over what’s hot and what’s not, I figure a venture into some gruesome reality might help.

.NET allows you to connect to your database and essentially ‘copy-off’ data into a stateless and disconnected entity called the DataSet. Think of it as a local copy of a large corporate database. Within the DataSet, you can establish DataTables and DataViews. You can also establish DataRelations, that is, you can map the associations that connect various Tables and Views together.

You can also DataBind these DataViews and DataTables to webControls. These are pre-fabbed HTML entities, when bound to a table or view, are automagically populated with data. As simply put as I can make it, you connect to the database, create a table, fill the table, bind it too something such as a DataGrid (a <table>) or a Drop Down List Box (<select>) … and voila, it is populated with the data in the table without you having to loop through each record in the DataTable/View.

dim ds As DataSet = New DataSet(“DropDownLists”)
dim dc as OleDbConnection = New OleDbConnection(“…”)
dc.Open()
Dim da As OleDbDataAdapter = New OleDbDataAdapter(“SELECT … from Parents”, dc)
da.Fill(ds, “Parents”)
Dim dv as New DataView = New DataView(ds.Tables(“Parents”))

So let’s say you have Drop Down List Boxes, in which one is the co-dependent on the other; meaning if I select an option on the first drop down, then the options in my second drop-down change according to their DataRelation. Don’t be frightened, it happens more often than you think.

Dim dr As DataRelation = _
New DataRelation(“ParentAndChild”, _
myDataSet.Tables(“Parents”).Columns(“parent_id”), _
myDataSet.Tables(“Children”).Columns(“parent_id”))DropDownList1.DataSource = dv
DropDownList1.DataTextField = “Parent_ID”
DropDownList1.DataValueField = “Parent_Name”
DropDownList1.DataBind()

Okay, so I’m all set. After jumping through all of the above flaming hoops, the only thing left to do now is to put some code in the SelectedIndexChanged event of the parent DropDownList1 in which I merely use the DataRelationship to define the data in the child DropDownList2. Of course, the trick is getting the data in the right format.

My first thought was that I would merely assign the ChildRows to the 2nd DropDownList object:

DropDownList2 = CType(myDataSet.Tables(“Parents”).Rows(i).GetChildRows(“ParentAndChild”), DataView)

No such luck. So after about an hour of reading copious documentation and usenet feeds, here’s what I finally wound up with:

DropDownList2 = myDataSet.Tables(“Parents”).DefaultView(i).CreateChildView(“ParentAndChild”)

My point here is not to dissuade you from using .NET. It is an incredibly powerful tool. But like all industrial strength power tools, there is a trade-off. Hopefully my venture into geekdom will give you a better idea of some of the real-world issues faced by someone who’s cut himself more than once with this tool. As always, your mileage may vary.