Blog-Mania

Why would I lay in bed awake at night tormenting over the fact that I'm currently forced to construct a giant string of text that will eventually compose a database query in the code-behind of my web page - instead of where it belongs: within the Data Access Layer class file?! Well, that's an easy one: because I'm a BIG dork with such a drama-free, mellow, and complacent life that I have nothing more severe to concern myself with. But I'm certainly not complaining about that. Anyhoot, the good news is that I toss and turn no more! Take a gander at this monster of a method...parameter anyone?

This messy, monstrosity of a method is contained within my web app's Data Access Layer class file where it programmatically builds a data query based upon the user's comprehensive, multi-optioned criteria selections. But how am I associating those parameters with the user's selections contained all the way over on the web page itself? Easy: An ObjectDataSource. I can populate my relevant parameters with the ListBox selections the user has made by adding the following to the OnInserting event handler of the ObjectDataSource:

protected void OnObjectDataSourceInserting(object sender, ObjectDataSourceSelectingEventArgs e) { e.InputParameters["selectedFinalStatuses"] = FinalStatuses.Items; e.InputParameters["selectedCurrentStatuses"] = CurrentStatuses.Items; e.InputParameters["selectedIDs"] = IDs.Items; e.InputParameters["selectedContractTypes"] = ContractTypes.Items; e.InputParameters["selectedDivisions"] = Divisions.Items; e.InputParameters["selectedProjectControllers"] = ProjectControllers.Items; e.InputParameters["selectedContractReps"] = ContractReps.Items; e.InputParameters["selectedPricers"] = Pricers.Items; e.InputParameters["selectedSubKReps"] = SubKReps.Items; e.InputParameters["selectedOpCenters"] = OpCenters.Items; }

And...Viola! Not only, do I have an elegant solution to relocating the bulk of the data query logic where it belongs: In my Data Access Layer, but I've also got built-in server-side data validation since any attempt to provide data that will not convert successfully into a DateTime object (or whatever other relevant data type you're attempting to achieve) will throw an immediate exception before any would-be hacker's sql injection attempt hits my precious database.

[Sigh]Sweet dreams tonight...

Creating a single Excel-like, user-editable, interface to represent a multi-table data relationship can be achieved by nesting GridViews within one another (or within DetailViews). It's certainly not an endeavor for the faint of heart, but with a few tricks of the trade up your sleeve, you'll be able to execute it (mostly) pain free.

First, place a non-editable version of the child GridView within the parent's applicable ItemTemplate and an editable version of the child GridView within the parent's applicable EditItemTemplate; each bound to their respective Datasources also contained within their respective ItemTemplate or EditItemTemplate. Next, selecting the data for the nested GridViews is fairly straight-forward; simply convert the identity ID column of your parent GridView to a template and then reference it's Label control's name as the select control parameter of your child GridView's DataSource. Or if you're nesting the GridView within a DetailsView parent, use the DetailView's supplying GridView.SelectedValue as the Datasource's select control parameter value.

From there, you can use the GridView's built-in Edit, Delete, and Update capabilities to perform the corresponding functions on your editable child GridView. Make sure you've defined stored procedures or queries within the editable version of your DataSource for any of the functions you require (delete, insert, and update). Then, make sure you've got controls within the EditItemTemplate(s) of your editable child GridView bound to all the necessary parameter values of your update and delete stored procedures (or queries).

Now last, but not least; how to handle your child GridView insertions since such functionality is NOT baked into the GridView (really confused as to why Microsoft made this decision, but whatever - it just gives me more potential blogging material).

Here is an example of an editable child GridView and its corresponding OnRowCommand event handler. Notice I have an Insert button within my EmptyDataTemplate AND within my FooterTemplate so that users will be able to enter records with or without the presence of prior records. Also, notice the 2 CommandName's for these buttons, 'Insert1' and 'Insert2'. By setting the editable child GridView's OnRowCommand event to your method name, and catching the appropriate CommandName's (like my Insert1 and Insert2); you can programmatically access any controls within the GridView's EmptyDateTemplate or FooterRow as necessary to set your DataSource's insert parameter(s) values for insertion. You will find this a mandated task since any attempt to declaratively reference the GridView's controls within DataSource itself will fail due to the lack of built-in binding between a GridView and its corresponding DataSource's insertion parameters (unlike the other update/delete parameters).

In addition, any special code-behind functionality that needs to occur as a result of activity within the editable child GridView can be executed via the plethora of built-in events contained within the GridView (OnRowCommand, OnRowCreated, OnRowDataBound, OnRowDeleting, OnRowDeleted, etc) or even the GridView's corresponding datasource itself (OnInserting, OnInserted, OnDeleting, OnDeleted, etc); all with handles to the relevant modified data.

Aaaand Happy Nesting!!