Introduction
This code drop is part of a smash and grab series. If you're in a rush, you can just grab this code and insert it into your application, no understanding is required. When you have some time (ya, right) you may want to review the source code.
Background
I was presented with a project to convert an Access single-user application into a web based multi-user SQL Server application. The Access application had about 10 tables that needed to be managed through an Add/Edit/Delete interface. This table maintenance was to be done by a single individual, so concurrency issues would not be a problem. I decided that with some tweaking, the GridView could perform what needed to be done. Out of the box, the GridView will not do row inserts, nor will it provide DropDownLists where the data to be edited is really an ID representing a row in another table. There is another CodeProject article GridViewRedux, which will show you how to do that. You must read that article first or this article will make no sense.
This article will show you how to code inline detail records just below a selected master record - just like Access does. For this article, I have used tables in the Northwind database.
Using the code
Download the project, unzip it to somewhere on your hard disk, and then create a new web project from an existing folder. Build the project, and test drive it so that you can see what it does. There are a few key files:
- GridViewHelper.js which does all the DHTML heavy lifting.
- Master.aspx contains the master record
GridView.
- Details.aspx contains the detail records.
Please download the source files and follow along as I explain what you need to insert.
Master.aspx
Insert this code in the <head> section of the file:
<script src="GridViewHelper.js" type="text/javascript"></script>
<link href="GridViewHelper.css" rel="STYLESHEET" type="text/css" />
<link href="AppStyle.css" rel="STYLESHEET" type="text/css" />
Insert this code following the </form> element:
<script type="text/javascript">
GridViewHelper.Init(document.all.GridView1, 0, 0);
var ToolTips = new Array("", "??", "??", "??");
GridViewHelper.AddToolTips(document.all.GridView1, ToolTips);
</script>
- Since this is a master page and not a detail page (the code detects that it is not running in an
IFRAME), the Init function resizes the width of the page to the required width so that no columns wrap. If you don't want this functionality, don't call Init in the master page. The other parameters are required but ignored.
- The
GridView control does not support tooltips on the column headers, so GridViewHelper has a method AddToolTips which takes a reference to the grid and an array of tooltips that it applies to the column headers. Change "??" to whatever you want.
Master.aspx.cs
Most of the code in here is just boiler plate, just cut and paste. Two lines of code are kind of interesting:
string RowID = Convert.ToString(
System.Web.UI.DataBinder.Eval(e.Row.DataItem, "CategoryID"));
string Url = "Details.aspx?ID=" + RowID;
When you call Details.aspx, you must tell it which category the details are for. This is how you do it.
Details.aspx
Notice that the <body> has been modified. This is to force the details to "snug" up against the master record and offset to the right by 40 pixels to allow the details table to appear indented. Also, scrolling has been turned off. You can safely turn scrolling off because the master page has been widened. Again, this is to "snug" up the detail records:
<body leftmargin="40" topmargin="0" scroll="no">
Insert this code in the <head> section of the file:
<script src="GridViewHelper.js" type="text/javascript"></script>
<link href="GridViewHelper.css" rel="STYLESHEET" type="text/css" />
<link href="AppStyle.css" rel="STYLESHEET" type="text/css" />
Notice that the <form> has been modified. More "snugging":
<form id="form1" runat="server" style="overflow: hidden;">
Notice that the <div> has been modified. More "snugging":
<div style="overflow: hidden;">
And now the magic. Insert this code following the </form> element:
<script type="text/javascript">
GridViewHelper.Init(document.all.GridView1,
ExtraWidth=100, ExtraHeight=0);
var ToolTips = new Array("", "Product name",
"Supplier", "Quantity per unit",
"Unit price", "Units on order",
"Reorder level", "Discontinued");
GridViewHelper.AddToolTips(document.all.GridView1, ToolTips);
</script>
This causes the IFRAME, where the details are placed in, to be only as big as necessary. The ultimate "snugging". Notice the ExtraWidth=100, ExtraHeight=0 parameters above. When scroll in the <body> is set to no, you have nothing to do as these parameter values should suffice. If you set scroll="yes", then scroll bars may appear and you have to make room for them. This is where you experiment with changing the ExtraWidth/ExtraHeight parameters to larger numbers so that the detail records look correct (as few scroll bars as necessary). You never need a vertical scroll bar because the code sizes to the correct height. If you change ExtraHeight=25, you should have enough room for a horizontal scroll bar without the need for a vertical scroll bar.
Take a look at the DataSource lines:
SelectCommand="SELECT '0' as [ProductID], '' as [ProductName],
'' as [CompanyName], '0' as SupplierID, '0' as [QuantityPerUnit],
'0.00' as [UnitPrice], '0' as [UnitsInStock], '0' as [UnitsOnOrder],
'0' as [ReorderLevel], '0' as [Discontinued]
UNION SELECT [ProductID], [ProductName], Suppliers.CompanyName,
Suppliers.SupplierID, [QuantityPerUnit],
convert(varchar(20), [UnitPrice], 0) as [UnitPrice],
[UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]
FROM [Products]
left join Suppliers on Suppliers.Supplierid=Products.SupplierID
WHERE ([CategoryID] = @CategoryID)
ORDER BY [ProductName]
Notice that the SELECT statement is expecting a QueryString parameter to insert into the WHERE clause. If you click on the Smart Tag for the DataSource, and select Configure Datasource, and then click Next once, you will see a WHERE button. Click on this to see how this clause was created. Also notice that I have converted the UnitPrice to a string so that it will only display two decimal places.
Take a look at the DataSource lines:
InsertCommand="INSERT INTO [Products] ([ProductName],
[SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice],
[UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued])
VALUES (@ProductName, @SupplierID, @CategoryID,
@QuantityPerUnit, convert(money,@UnitPrice),
@UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued)
I had to convert the field back to money so the insertion would not fail with a "conversion from varchar to money error".
Details.aspx.cs
Most of the code in here is just boiler plate, just cut and paste. There is really only one trick in here and that has to do with inserting new records:
p = new System.Data.SqlClient.SqlParameter(
"@CategoryID", Request.QueryString["ID"]);
c.Parameters.Add(p);
The insert requires a CategoryID and you can get it from the QueryString.
Points of interest
An interesting side effect of this technique is that detail records can have their own detail records ad infinitum. When you expand a detail block the first time, a request to the server is made to populate it. If you collapse the details block, the block is just hidden, so that the next time you request for expansion, there is no server hit, the block is just made visible again.
Notice: No AJAX was maimed or killed in the creation of this article. In fact, the newest kid on the block wasn't even required. I've got nothing against AJAX (in fact some of my friends use AJAX), but it's got its time and place (probably in my next project ;-).
Smash and Grab / Redux series
I have recently started two series of articles here at CodeProject. Smash and Grab is intended as a series of short articles on one specific code technique. Redux is intended as a series of longer articles which attempt to reduce a complicated topic (like GridView) into its basic constituent parts and show that once you have all the information, it isn't really that hard after all. To find Smash and Grab articles, search for the keyword SmashGrab. To find Redux articles, search for the keyword Redux. I welcome any contributions to either series, but please follow the guidelines when submitting articles to either.
Conclusions
Pretty easy, eh! (I'm Canadian). If you have the time, take a look at the source, there is some cool DHTML going on.
