Datagrid Web Control

An Extensive Examination of the DataGrid Web Control: Part 1

Page 1 of 1

An Extensive Examination of the DataGrid Web Control: Part 1 Introduction One of the most common tasks in classic ASP was retrieving tabular information from a database and displaying it in an HTML table. With classic ASP this would require many lines of intermixed HTML and code; the following pseudocode shows what the code would commonly look like:

Create Database Connection Populate a recordset based on some SQL query Output the HTML table header () Loop through the recordset Emit the HTML for a table row ... Emit the HTML table footer () If you're an ASP developer chances are you've written code like above more times than you'd care to admit! One of the advantages of ASP.NET is that it contains a number of Web controls. These Web controls, which emit HTML, provide a programmatic interface, allowing developers to separate code and content and treat these HTML emitting entities as objects in their code. That is, if we wanted to display some HTML content using ASP.NET we'd do the following:

<script language="vb" runat="server"> sub Page_Load(sender as Object, e as EventArgs) lblMessage.Text = "Hello, World!" end sub Here the label Web control lblMessage is placed in the HTML using HTML-like tags with the runat="server" attribute specified. Then, in the Page_Load event handler (which is run every time the page is loaded) the Text property of the lblMessage Web control is set to "Hello, World!" The use of Web controls here separates the code from the content; in classic ASP one would need to place a <%="Hello, World!"%> in the proper place within the HTML content to achieve the same effect. There are ASP.NET Web controls that are much more useful and powerful than the simple label control. The DataGrid Web control, which we'll be dissecting in this article, is one such powerful control. The DataGrid emits the needed HTML for data-bound HTML tables. As we'll soon see, binding data to a DataGrid is very easy; furthermore, with only a few slight property changes you can customize the look and feel the DataGrid's output, rendering very professional looking HTML tables (a feat when one considers my utter lack of artistic skill!). DataGrid Basics To place a DataGrid on an ASP.NET Web page you simply need to add the following code:

Here the id you choose will be the name of the DataGrid you'll use when referring to it in your server-side code. The syntax above gets us started using a DataGrid by placing it in the HTML content, but in order to have the DataGrid display anything useful we need to bind the DataGrid to some collection of information. This collection of information can be any object that supports the IEnumerable interface. This includes things like Arrays, collection classes (ArrayList, Hashtable, etc.), DataSets, DataReaders, and a number of other objects. Since we'd like to focus on displaying database information, for this article we'll focus on binding DataGrids to DataReaders, which are synonymous to forward-only, firehose cursors Recordsets in classic ADO/ASP. (For more information on reading database results into DataReaders using ADO.NET be sure to read: Efficiently Iterating Through Results from a Database Query using ADO.NET.)

An Extensive Examination of the DataGrid Web Control: Part 1

Page 2 of 2

So, how do we go about binding data to the DataGrid? It's amazingly simple. The first thing we need to do is to grab a DataReader containing some database data. For this example I am hitting the ASPFAQs.com database and bringing back the ten most popular FAQs. Once I have this data in a DataReader, to bind it to my DataGrid I just need to write two lines of code. The first line sets the DataGrid's DataSource property to the DataReader; the second line calls the DataGrid's DataBind method. That's all there is to it, as the following code snippet shows.

<% @Import Namespace="System.Data" %> <% @Import Namespace="System.Data.SqlClient" %> <script language="vb" runat="server"> Sub Page_Load(sender as Object, e as EventArgs) BindData() End Sub Sub BindData() '1. Create a connection Dim myConnection as New SqlConnection( ConfigurationSettings.AppSettings("connectionString")) '2. Create the command object, passing in the SQL string Const strSQL as String = "sp_Popularity" Dim myCommand as New SqlCommand(strSQL, myConnection) 'Set the datagrid's datasource to the datareader and databind myConnection.Open() dgPopularFAQs.DataSource = myCommand.ExecuteReader( CommandBehavior.CloseConnection) dgPopularFAQs.DataBind() End Sub [View a live demo!] First off note that the amount of code we have to write to utilize databinding is not much at all. We essentially create a connection, specify a SQL command (in this case a stored procedure, sp_Popularity), open the database connection, set the DataGrid's DataSource property to the resulting DataReader, and finally call the DataGrid's DataBind method. This approach completely isolates the code from the content; there's no mixing of HTML table and DataReader output syntax, as we would have had with classic ASP. Take a moment to view the live demo. You'll notice that the DataGrid displays the database contents in a plane-Jane HTML table - it's pretty ugly. While the main "work" has been done for us (displaying the data), there's a lot to be desired on the user-interface front. Fortunately, prettying up the DataGrid results is amazingly easy. Unfortunately, this will have to wait for the next part of this article series! Stay tuned! :-) Summary In this part of the series on DataGrid usage, we examined the very basics of DataGrids - how to plop one down on an ASP.NET Web page and display bound database results. Unfortunately the DataGrid's output is not pretty. However, as we'll soon see, prettying up the display of the DataGrid is a breeze. Additionally, we'll look at more advanced UI options (such as paging database results, sorting the DataGrid's results on the fly, and other cool stuff) in upcoming portions of this article. Happy Programming!

PART-2

An Extensive Examination of the DataGrid Web Control: Part 1

Page 3 of 3

Introduction In Part 1 we examined the elementary basics of the DataGrid, an ASP.NET Web control designed to display data in an HTML table tag. Part 1 illustrates how simple it was to bind database contents to a DataGrid: all we needed to do was populate a DataReader object with a SQL query, set the DataReader to the DataGrid's DataSource property, and then call the DataGrid's DataBind() method. All that was left was to place the DataGrid Web control in the HTML content, which could be done with:

Pretty simple. Unfortunately this simple approach renders a fairly ugly DataGrid. As we saw from this live demo, the resulting DataGrid is little more than a vanilla HTML table encapsulating all of the columns and rows present in the DataReader. What we'd like to be able to do is display only certain columns from the DataReader, and specify each column's format. Also, we'd like to be able to specify some table-wide formatting settings, such as the background color, the font, etc. Finally, it would be nice to be able to add some custom headings for each column, perhaps giving the heading a different background color or a bolded font. In this part we'll examine how to do all of these tasks! (The DataGrid can do so much more, as we'll see in future parts to this article series, such as paging through database results, allowing users to sort the data, etc. These topics are for a future article, though!) Specifying DataGrid Formatting Options We have two options on how to specify formatting options for a DataGrid. The first option is to set the setting programmatically in the server-side script block. For example, to set the background color of the DataGrid to red you could use the following server-side code:

<%@ Import Namespace="System.Drawing" %> <script runat="server"> sub Page_Load(sender as Object, e as EventArgs) ... DataGridID.BackColor = Color.Red ... end sub The other method you can use to set display properties is to specify them in the DataGrid Web control tag. For example, the following code has the same effect as the above code:

Personally I prefer the latter method. I find specifying the display properties in the Web control tag itself to be cleaner than the server-side code alternative. (For the server-side code approach note that we needed to import the System.Drawing namespace and refer to the color as Color.Red; with specifying the display properties in the Web control tag we just needed to say BackColor="Red". I find this saving of typing to be more readable.) Let's look at some of the useful formatting settings we can apply to our DataGrid. The ones I think you'll find most useful are:

 

BackColor - specifies the background color. Font - specifies the font information for the DataGrid. Font information includes things like the font family,

   

the point size, if its bold, italicized, etc. CellPadding - specifies the cellpadding of the HTML table CellSpacing - specifies the cellspacing of the HTML table Width - specifies the width of the HTML table (can be in pixels, percentages, etc.) HorizontalAlign - specifies how the table should be aligned (Left, Right, Center, NotSet)

An Extensive Examination of the DataGrid Web Control: Part 1

Page 4 of 4

An example of using these display preference properties to create a nicer looking DataGrid can be seen below. Note that the Font property of the DataGrid is, in actuality, an object reference to the FontInfo class, which contains properties like Size, Name, Bold, Italic, etc. In order to set a property of one of the classes represented by the Font we have to use a hyphen (-). This is synonymous to the "dot" in languages like VB.NET and C# when referencing an object's property.

[View a live demo!] Pretty impressive, eh? With just a few lines of text we greatly improved the UI of our DataGrid. We went from a vanilla HTML table to a centered one with a gray background color and a nice-looking font. Getting Fancy with Styles The DataGrid Web control contains a number of "styles" that you will find quite useful in customizing the look and feel of the DataGrid. These styles can support a number of stylistic properties such as: BackColor, ForeColor, Font, HorizontalAlign, and Width, to name the most useful ones. (See the docs for more information.) There are four available styles in DataGrids:

   

HeaderStyle - Specifies the style information for the header (the top row of the table that lists the column names; you must have the DataGrid's ShowHeader property set to true (the default).) FooterStyle - Specifies the style information for the footer (the bottom row of the table that lists the column names; you must have the DataGrid's ShowFooter property set to true (the default is false).) ItemStyle - Specifies the style information for each row in the table. AlternatingItemStyle - Specifies the style information for alternate rows in the table. You can set the ItemStyle and AlternatingItemStyle to different values for an easier-to-read DataGrid (see the below demo for an example).

Like other DataGrid formatting properties, the styles can be set either programmatically or through the DataGrid's Web control tags. As aforementioned, I prefer the Web control tag approach and will stick to that for this example. There are two ways to set the style properties through the Web control tag syntax. The first is similar to the way we've been specifying properties that were, in actually, objects, by using the dash (i.e., to set the BackColor for the HeaderStyle you could do: HeaderStyle.BackColor="Red") Another approach is to use special style blocks within the definition of the Web control tag, like so:

Either way is fine. I find using the embedded tags helps in readability, especially when you plan on setting a lot of properties for a lot of styles. The following example shows how to pretty-up our earlier example:



An Extensive Examination of the DataGrid Web Control: Part 1

Page 5 of 5

[View a live demo!] Now that we've examined how to integrate stylistic features and how to set global display properties for a DataGrid control, we've got one topic left for this part of the article series: how to customize the formatting and style for each specific column in the DataGrid. More on this technique in Part 2 of this article!

PART-3 Introduction In Part 1 we examined the elementary basics of the DataGrid, an ASP.NET Web control designed to display data in an HTML table tag, showing how simple it was to display database contents through an ASP.NET Web page. Part 2 examined how to customize the look of the resulting DataGrid. As we saw in a live demo, with very little programmatic code we could display database information in a very impressive format. While displaying data is nice and all, what would really be useful is if we could associate some sort of action with the DataGrid. For example, imagine that we worked for some eCommerce firm and were asked to display a DataGrid showing the data for the list of all orders. Each order might have a large amount of data associated with it, including the item purchased, the date purchased, information about the buyer (name, address, etc.), what shipping options the buyer chose, etc. Showing all of this information in a single DataGrid (for every order) would be information overload. As we saw in Part 2, we could use simply set AutoGenerateColumns to False and then use the Columns property to specify what columns from the order information we wanted to display. While this would make the data easier to digest, what if the user wanted to also have the option to view the intricate details of any particular order? Ideally we'd like to be able to have on each row in the DataGrid a button labeled "Details" that, when clicked, would display the complete information for that particular order. The live demos in this article step through a very similar application. In our previous live demos we were displaying the top 10 most popular FAQs from ASPFAQs.com. In this article we'll extend the live demo to show only the most important information for each of the 10 most popular FAQs along with a "Details" button. When the "Details" button is clicked, the user will see all of the information for the FAQ they clicked the "Details" button on. Building on a Foundation As we saw in Part 2, the DataGrid control allows for a number of BoundColumn tags in the Columns tag of the DataGrid control. Recall that each BoundColumn tag represents a column in the resulting DataGrid. To place a button in the DataGrid we can use the ButtonColumn tag much in the same way as we used the BoundColumn tags. The following source code shows how to place a button in a DataGrid:

An Extensive Examination of the DataGrid Web Control: Part 1

Page 6 of 6

[View a live demo!] Note that to get this to work we needed to place the DataGrid within a server-side form (the bolded

shown above). This is because in order to track the button that was clicked and the associated action that should occur, the ASP.NET page needs to be able to reconstruct the page and series of buttons in the DataGrid. To do this it needs the page's ViewState. A thorough discussion of ViewState is beyond the scope of this article; for more information read: Form Viewstate. Note that the button created by the live demo is a textual link button. This is the default appearance generated by the ButtonColumn class. If you want to display a standard button, you can simply specify: ButtonType="PushButton" in the ButtonColumn tag. The ButtonColumn class has a number of important properties. Two of the stylistic ones are used in the code sample above. HeaderText specifies the text that should go in the header of the DataGrid's column in which the button appears; Text specifies the textual display for each button. As with the BoundColumn tag, the ButtonColumn tag can have each button's text be the value of some column in the DataGrid's DataSource - simply omit the Text property in the ButtonColumn class and set the DataTextField property to the name of the database column whose value you wish to have displayed as the button's text. Having Something Happen When the Button is Clicked Now that we have a button in our DataGrid, we'd like to be able to associate some server-side code with the button so that, when it's clicked, some action can take place. Realize that whenever a ButtonColumn button in the DataGrid is clicked, the ItemCommand event is fired; hence, we can write a server-side event-handler for this event. This event handler must have the following definition:

Sub eventHandlerName(sender as Object, e as DataGridCommandEventArgs) ... End Sub Once you define this function in your server-side script block (or code-behind page) you can tie the actual DataGrid event to this event handler by adding the OnItemCommand property in the DataGrid's tag, like so:

... The following code demonstrates how to have an event handler run when one of the buttons in the DataGrid is clicked:

<script language="vb" runat="server"> Sub Page_Load(sender as Object, e as EventArgs) If Not Page.IsPostBack then BindData() 'Only bind the data on the first page load End If End Sub

Sub BindData() 'Make a connection to the database 'Databind the DataReader results to the DataGrid.

An Extensive Examination of the DataGrid Web Control: Part 1

Page 7 of 7

End Sub

Sub detailsClicked(sender as Object, e As DataGridCommandEventArgs) Response.Write("You clicked one of the details buttons!") End Sub ...

[View a live demo!] There is one very important thing to notice here: we are only performing our database query and databinding to the DataGrid on the first page visit. In our Page_Load event handler, which fires every time the page is loaded, we check to see if the page has been posted back. If it has not, then we know this is the first visit to the page. In this case we want to populate a DataReader with a database query, set the DataGrid's DataSource property to this DataReader, and call the DataGrid's DataBind() method. When the person clicks one of the "Details" buttons in the DataGrid, the ASP.NET Web page will perform a postback, making a round trip back to the server. Again, the Page_Load event handler will fire, but this time, since we're doing a postback, the BindData() Sub will not be called. Furthermore, the detailsClicked event handler will execute. Note that if we blindly rebind the data to the DataGrid (that is we omit the If Not Page.IsPostBack check) the detailsClicked event handler will not run, since rebinding of the DataGrid flushes out the ItemCommand event. (Please reread the above two paragraphs - chances are at some point in your experiences with the DataGrid you'll forget to do this and won't be able to get the DataGrid to fire event handler code for its buttons. Trust me, it's happened to me a couple of times! :-).) While this example and live demo illustrate how to have an event handler associated with a button click from a ButtonColumn tag, we've yet to see how one can determine what row of the DataGrid had its button clicked. This very important question will be examined in Part 2 of this article.

PART-4 Introduction Recall that in Part 3 of this series we examined how to associate certain events, like the click of a button, with actions. For example, in a live demo we showed how to allow the user to click a "Details" button to show specific information about one of the rows in the DataGrid. In this part we'll expand on the notion of tying buttons in the DataGrid to an action to allow the end user to sort the contents of the DataGrid on the fly. Preparing a DataGrid for Sorting You'll be pleased to learn that adding support for sorting to a DataGrid is very simple, and can be broken down into the following three steps: 1. Set the AllowSorting property of the DataGrid to True. If the DataGrid's AutoGenerateColumns property is set to False, specify the SortExpression property in the BoundColumn controls that represent the rows you'd like the user to be able to sort on. 2. Create a sort event handler that repopulates the DataGrid with the sorted data, and specify the name of this sort event handler in the DataGrid's OnSortCommand event. 3. Create a function that will allow you to grab the data from your data store in sorted format. Let's tackle these steps one at a time. For the first step, you need to specify the AllowSorting property in your DataGrid, like so:

An Extensive Examination of the DataGrid Web Control: Part 1

Page 8 of 8

... If you have the AutoGenerateColumns property set to True (the default) you've completed step 1. (Recall that if the AutoGenerateColumns property set to True then the columns of the DataGrid are determined by the columns in the source you are binding to the DataGrid. If the AutoGenerateColumns property set to False, then you have to explicitly specify what columns should appear in the DataGrid via BoundColumns. See Part 2 of this series for more information on thsi topic.) If, however, you have the AutoGenerateColumns property set to False, then you will need to decide what column(s) you want to allow the user to sort on. For all the columns you want to let the user sort, you must add a SortExpression property to the BoundColumn. Note that you should give this SortExpression property the name of the column in the database:

In the above example the DataGrid will have two columns, one binding to the database column dbField and the other to dbField2. The latter column will be able to be sorted while the first will not (since the latter has a SortExpression property specified while the former does not). Creating a Sort Event Handler By adding the AllowSorting property to True, the DataGrid, when rendered, will have in its header for each column a hyperlink. When clicked, a postback occurs and the sort event is raised. When this event is raised we'd like to have a sort event handler fire that requeries the data store, getting the data in the specified sorted order, and rebinds it to the DataGrid. In order to have this happen we need to do two things: create a sort event handler and specify the event handler for the DataGrid's sort event. First, when creating the event handler you must use the following definition:

Sub SortEventHandler(sender as Object, e as DataGridSortCommandEventArgs) ... End Sub (Of course you can name the variables sender and e whatever you like - the important part is realizing that you need to have your event handler accept two variables: an Object and a DataGridSortCommandEventArgs.) Inside your event handler you can retrieve the name of the column that was sorted via: e.SortExpression. If you explicitly specified the SortExpression property in a BoundColumn, then the value of e.SortExpression is the value of the SortExpression property; if you did not specify a SortExpression property (because you had AutoGenerateColumns set to True), then the value of e.SortExpression is the value of the database column name that represents the column clicked. Once you sort event handler is created you need to associate it with the DataGrid's sort event, so that when this event is raised the proper event handler is fired. To accomplish this simply set the DataGrid's OnSortCommand event handler to the name of the sort event handler you created, like:

An Extensive Examination of the DataGrid Web Control: Part 1

Page 9 of 9

... At this point a couple of demos might be helpful. I've created two of them, one that uses AutoGenerateColumns set to True, and one that uses AutoGenerateColumns set to False. Both examples set the AllowSorting property to True and have a very simple sort event handler that does nothing but set a label Web control's Text property to the value of e.SortExpression. Note that the DataGrid is enclosed in a server-side form (

...

) - this is essential, since we are dealing with postbacks here. All that we have left to do is to create a function that retrieves the data in a specified sorted order. Once we have this function the code for our sort event handler becomes trivial - we simply need to call this function, passing in the SortExpression, and then setting its result to the DataGrid's DataSource and rebinding the DataGrid (via DataBind()). We'll look at how to accomplish this in Part 2 of this article.

PART-5 Introduction In Part 2 we examined how to customize the DataGrid's output through the use of BoundColumn controls. By setting the DataGrid's AutoGenerateColumns property to False, we could specifically indicate what database columns should appear in the DataGrid, their column's header text, and formatting and style information, all through a BoundColumn control. While this approach is very powerful and allows for very customized renderings of data, it is limiting in the sense that the output is encased in a simple TD HTML tag, making it a standard HTML table column in the DataGrid. What if you wanted more flexibility, though? Along with the DataGrid control, ASP.NET contains the DataList and Repeater Web controls, which allow you to customize the specific HTML output through the use of HeaderTemplates, ItemTemplates, and FooterTempaltes. Essentially, a template allows you to specify HTML and data bound values that appear in the header, in each item, of in the footer, respectively. The DataGrid supports this functionality on a percolumn basis. That is, in a DataGrid, in the tag, we can specify to place TemplateColumns instead of BoundColumns for a much finer grain of control in the DataGrid's HTML output. In this article we'll examine the basics of the TemplateColumn and how to use them to customize the DataGrid output. Using a TemplateColumn With the DataGrid you can specify TemplateColumns on a per-Column basis. As with BoundColumns, to use TemplateColumns you need to set the DataGrid's AutoGenerateColumns to False first. Then, you can place a TemplateColumn control in the tag for each templated column you wish to display. Note that you can also have BoundColumns in the tag as well; that is, a DataGrid may be made up of both BoundColumns and TemplateColumns, as the following example illustrates:

... The above DataGrid would have two columns for each row: one a BoundColumn (FAQID) and the other a TemplateColumn, whose content we've yet to specify. (For more information on BoundColumns be sure to read Part 2

An Extensive Examination of the DataGrid Web Control: Part 1

Page 10 of 10

of this article.) Also note that you could have multiple TemplateColumns - all a TemplateColumn control does is generate one column for each row in the DataGrid. The HTML output for that row is specifies by tags inside of the TemplateColumn control, as we will see shortly. So, how does one specify the HTML content for a TemplateColumn? The TemplateColumn control itself has some UIrelated properties, such as HeaderText, which specifies the HTML content to appear in the column's header, and Visible, which specifies if the column is rendered or not. More interestingly are the tags that can appear inside of the TemplateColumn. There are four such tags: 1. ItemTemplate - specifies the template for each row for the particular column the TemplateColumn represents. 2. HeaderTemplate - specifies the template for the column's header. 3. FooterTemplate - specifies the template for the column's footer. 4. EditItemTemplate - specifies the template for the a cell of a particular row that has been selected for editing. This can occur when using the DataGrid's Edit/Update/Delete features (which we've yet to cover in this article series... see this ASP.NET QuickStart example for more information). These templates can contain vanilla HTML as well as data-bound values. For example, a very simple TemplateColumn might simply output the value of a database column in bold text. (Yes, yes, a BoundColumn could be used here, of course.) To output a dynamic data value from the DataGrid's DataSource, use the following syntax:

<%# DataBinder.Eval(Container.DataItem, "ColumnName") %> This is known as a "databinding" command. Essentially it says, "Find the ColumnName column in the DataItem (the DataItem being the current row of the DataGrid's DataSource - by current I mean, the DataGrid is looping through the specified DataSource (most likely a DataReader or a DataSet), and at each loop iteration it must render a row of the DataGrid. Hence, our DataGrid's TemplateColumn would contain an ItemTemplate with a single line of code (the databinding statement shown above), which would look like:

<%# DataBinder.Eval(Container.DataItem, "ColumnName") %> The Benefits of a TemplateColumn over a BoundColumn At this point you may not fully realize the UI freedoms granted to you by the TemplateColumn. In fact, our simple example above could have easily been implemented by a BoundColumn. But what if you wanted to show two database column values in one DataGrid column? You could not do this in a BoundColumn, yet this task is painfully simple with a TemplateColumn. Let's look at a more complex example. In all of the previous demos I've included with this article series I've been showing the 10 most popular FAQs from ASPFAQs.com. Of course I've been using BoundColumns all along, so each column in the DataGrid's output has had only one specific database value. Imagine, however, that I wanted to have the DataGrid's output to have just two columns in total. The first column would list the FAQ's ID (FAQID), while the second column would contain inside of it an HTML table of its own, which would display the Category Name, Description, and ViewCount of the particular FAQ. Using TemplateColumns, this is fairly simple, as can be seen by the code below:


An Extensive Examination of the DataGrid Web Control: Part 1

Page 11 of 11

ItemStyle-HorizontalAlign="Center" HeaderText="FAQ ID" / rel="nofollow">

Description:	<%# DataBinder.Eval(Container.DataItem, "Description") %>
Category Name:	<%# DataBinder.Eval(Container.DataItem, "CatName") %>
View Count:	<%# DataBinder.Eval(Container.DataItem, "ViewCount", "{0:#,###}") %>

[View a live demo!] Note that the last data-bound statement uses an alternate form of DataBinder.Eval - it included an optional third parameter that specifies a format string for the column. The formatting string #,### specifies that ever three digits should be separated by a comma. (The {0:formatString} syntax may seem a bit confusing - it simply states that the formatting string formatString should be applied to the 0th argument (i.e., the value housed in the current DataItem's ViewCount column). Conclusions In this article we examined how to use a TemplateColumn to provide a finer grain of control over a DataGrid's cell's HTML output. Clearly the TemplateColumn control allows for a much greater degree of flexibility in HTML output than the BoundColumn control. Additionally, as we will see in future articles, the TemplateColumn control can be used to customize the look of a DataGrid row when the user opts to edit the row. Very cool stuff indeed! Happy Programming!

PART-6Introduction Hopefully the past five installments of this multipart article series has, if nothing else, convinced you that the DataGrid is a very useful Web control. In past articles we've seen how to sort the results of a DataGrid and how to apply custom formatting to the outputted HTML. You can even easily display XML data through a DataGrid. In this article we'll examine yet another useful feature of the DataGrid - inline editing. There are many situations where you'd like to be able to edit the data in a DataGrid. For example, on a data-driven site you may have a set of administrative Web pages that list the full cotents of the various database tables and allow the admin to alter the values in these database tables. Using DataGrids we can very easily display a database table's information in a graphically pleasing way (see Part 2 and Part 5 for more details); using the same control, though, we can also add support for editing the data within the DataGrid! Using the DataGrid's editing features is much more simple and requires far less code providing the same functionality in a classic ASP page. Regardless, before tackling this article it is important that you have an understanding of SQL, namely how to construct and UPDATE statement (for a refresher on SQL be sure to consult the official SQL Books Online). Also, it is imperative that you are confortable with the DataGrid and understand how events are handled in the DataGrid. To brush up on this topic be sure to consult Part 3 of this article series.

An Extensive Examination of the DataGrid Web Control: Part 1

Page 12 of 12

First Things First: Displaying the Data Before we can even concern ourselves with editing the DataGrid's data, we must first be able to display it in a DataGrid. I won't delve into lengthy explanations on this, since the means for displaying data in a DataGrid has been heavily discussed in prior parts of this article series. Note that for this example I will be using the GrocerToGo database (an Access 2000 database avaialbe in a download at the end of the article), specifically displaying (and allowing the user to edit) the data in the Products database. From the Products table the DataGrid will display the following columns: ProductID, UnitPrice, ProductName, and ProductDescription. Some UI-formatting code is also employed to make the DataGrid "look nice." The following code displays the Products table in the DataGrid:

<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script runat="server"> Sub Page_Load(sender as Object, e as EventArgs) If Not Page.IsPostBack BindData() End If End Sub

Sub BindData() '1. Create a connection Const strConnStr as String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\GrocerToGo.mdb" Dim objConn as New OleDbConnection(strConnStr) objConn.Open() '2. Create a command object for the query Const strSQL as String = "SELECT * FROM Products" Dim objCmd as New OleDbCommand(strSQL, objConn) '3. Create/Populate the DataReader Dim objDR as OleDbDataReader objDR = objCmd.ExecuteReader() dgProducts.DataSource = objDR dgProducts.DataBind() End Sub

An Extensive Examination of the DataGrid Web Control: Part 1

Page 13 of 13

This code is fairly straightforward and very similar to the code in live demos in previous installments of this article series. The BindData() function simply reads in the contents of the Products table into a DataReader and then binds that DataReader to the dgProducts DataGrid.

Notice the Page_Load Event Handler! Notice that the Page_Load event handler for the editable DataGrid only calls the BindData() subroutine when the page is first visited. That is, it is not called on subsequent postbacks. This is vital! If you change the Page_Load event handler so that it calls BindData() every time, your edited values will not be saved in the database. For a thorough explanation as to why this is the case, be sure to read the FAQ: Why Your DataGrid's Updates Don't Show Up. Now that we have the data displayed in our DataGrid we need to allow the user to edit the data. To allow for this the DataGrid provides a control called EditCommandColumn, which displays an "Edit" button next to each row in the DataGrid. We'll examine how this EditCommandColumn control enables us to allow the end user to edit a particular row in the DataGrid in Part 2 of this article.

PART-7 Introduction In Part 6 of the DataGrid article series we looked at how to use the DataGrid's built-in editing features. One could add inline editing to the DataGrid's contents by simply adding an EditCommandColumn along with event handlers for the OnEditCommand, OnCancelCommand, and OnUpdateCommand. By performing these steps, the DataGrid is enhanced to include an "Edit" button on each row. When clicked, the columns in that particular row that are not marked as readonly are transformed into TextBoxes populated with the column values. The user can then edit these values and click the "Update" button. (See this screenshot, as well as this one, for an idea of what this would look like.) These TextBoxes are the default editing interface, but you can customize this editing interface to allow for more flexibility. For example, if one of your DataGrid columns is a True/False field, you could have the DataGrid display a True/False pair of radio buttons instead of the default TextBox. Likewise, if one column was a foreign key to a lookup table, you might want to present the user with a DropDownList of applicable choices, as opposed to letting them type in a value in the TextBox. Note that to customize the DataGrid's editing interface requires a bit of extra markup in the DataGrid Web control as well as some light additional code. The code and markup, I think you'll find, is relatively simple; the difficulty, in my opinion, lies in understand what, exactly, is happening behind the scenes! Customizing the Editing Interface with EditItemTemplate In order to customize the DataGrid's editing interface you have to be using a TemplateColumn. Recall from Part 5 of the series that TemplateColumns can be added to a DataGrid to allow for additional customization of the HTML output that is rendered when the DataGrid is DataBinded to its DataSource. If you use a BoundColumn to represent a column in the DataGrid, the editing interface will be the default TextBox. In this article we will work through a real-world example. In the previous articles in this series, we've used live demos against the FAQ database over at ASPFAQs.com. This database is comprised of a number of tables, the main one being tblFAQ, which has a row for each FAQ. One of the columns in the tblFAQ table is called FAQCategoryID, and is a foreign key to the tblFAQCategory table, which has a row for each FAQ category (these categories include Array, Appilcation Object, Email, ASP.NET, Forms, etc.). Specifically, the important parts of these tables are defined as follows:

tblFAQ FAQID Description

Primary Key, integer

varchar(255) - the "question" part of the FAQ

An integer foreign key to FAQCategoryID the tblFAQCategory table

tblFAQCategory FAQCategoryID Primary Key, integer varchar(255) - the "title" of Name

the category (Array, ASP.NET, etc.)

An Extensive Examination of the DataGrid Web Control: Part 1

Page 14 of 14

Remaining rows omitted for brevity... Using the knowledge you have attained in the past 6 installments of this article series, you should be able to quickly and easily create a DataGrid that displayed a row for each FAQ, including the FAQ's category name (as opposed to an integer). The following SQL statement would be sufficient to tie in the appropriate category name for each FAQ:

SELECT FAQID, F.FAQCategoryID, Name AS CategoryName, Description FROM tblFAQ F INNER JOIN tblFAQCategory FC ON FC.FAQCategoryID = F.FAQCategoryID [View a Live Demo!] In fact, I have whipped up a simple live demo to illustrate this. Nothing too fancy here, obviously. Now, imagine that you wanted to let the user edit the DataGrid. You may think, "Well, I've read Part 6 of this article series, so I know what to do! Let me just add an EditCommandColumn and go from there!" This is definitely the correct first step, but we have more to do after this due to the undesirable editing interface the BoundColumns create when in edit mode. Check out this live demo and be sure to click on Edit for a row. What do you see? In case you're too lazy to try out the live demo, the screenshot to the right shows the result. Note that the Category column has been transformed into the default TextBox in editing mode. At first this may not seem like that bad of a proposal. If someone wanted to change the Category for FAQ #2 from Strings to Arrays, they could simply type in Arrays. Of course this is not optimal for a number of reasons. First, recall that the Category for each FAQ is stored as a foreign key to the tblFAQCategory table, not as a string (such as "Arrays"). While you could scan the tblFAQCategory for an appropriate category name, and then use that row's corresponding FAQCategoryID to update the edited tblFAQ row, that would be messy; furthermore, what if the user made a typo, typing in "String" instead of "Strings?" Would you display an error message? Assume they meant to enter Strings? Create a new row in tblFAQCategory? Clearly the ideal approach would be not to display a TextBox for the Category when the row was edited, but instead a listbox with the appropriate category values for the user to select from. To accomplish this we can customize the HTML outputted when a row is selected to be edited by specifying an EditItemIndex control in a TemplateColumn. Recall that for a DataGrid column to have a customized editing interface, the column must be a TemplateColumn, not a BoundColumn; so, the first thing we need to do is transform the BoundColumn that displays the category name into a TemplateColumn. The following simple TemplateColumn and ItemTemplate pair will do the trick (be sure to read Part 5 of this article series for more information on using TemplateColumns):

<%# DataBinder.Eval(Container.DataItem, "CategoryName") %> Here we simply display the result of the CategoryName column from the DataSource. We are not done yet; currently if the user clicks the "Edit" button there will be no special rendering for the category name column. That is, they will

An Extensive Examination of the DataGrid Web Control: Part 1

Page 15 of 15

just see the text of the category name, no TextBox, no DropDownList, etc. This is because when we don't use a BoundColumn we need to specify the HTML to use when the row is in edit mode. We do this by using the EditItemTemplate control inside the TemplateColumn control (much like the ItemTemplate control to display the HTML to render when the row is not in edit mode). Since we want to show a DropDownList for the category names in edit mode, let's start by simply adding an ASP.NET DropDownList Web control to the EditItemIndex, like so:

<%# DataBinder.Eval(Container.DataItem, "CategoryName") %> <EditItemTemplate> Since this DropDownList will contain a list of the existing FAQ categories, we will be using databinding to bind the DropDownList to a DataSource that is comprised of the contents of the tblFAQCategory table. When using a databound DropDownList, you need to specify the what column from the DataSource you wish to have displayed as the text for each DropDownList option, and what value you want tied to these textual labels. Since we want to display a DropDownList item for each row in the tblFAQCategory table, it makes sense to have the name of the category (the Name column) displayed as the textual part of the DropDownList item, and the actual FAQCategoryID as the value of the DropDownList item. (If you are utterly confused by this, I'd highly encourage you to read: Creating Databound DropDown Lists in ASP.NET.) This is the reason I set the DataValueField and DataTextFields to the given column names above. Of course, realize that while we've specified the columns to bind to the DropDownList, we've yet to specify where the DropDownList's DataSource is coming from. Essentially, we need to construct a DataSet with the rows from the tblFAQCategory table. Before I delve into the specifics on this, let's take a step back and examine what's happening behind the scenes when a DataGrid is edited in Part 2. (I highly encourage that you read the following section, but if you just want to "get it working" first and understand why later, then you may skip the next section; however, you may find yourself a little confused with latter parts of the article... Regardless of your choice, continue on to Part 2 to continue with the article!)

PART-8 Introduction In Part 6 in this article series we examined how the DataGrid could be utilized to provide in-place editing of data. In addition to allowing a user to edit data, one may want to allow the user to delete data. This can be accomplished by adding a ButtonColumn control that contains a Delete button, which will add a Delete button to each row of the DataGrid. When a user clicks the Delete button for a particular row, that row will then be deleted from the database. To accomplish this we have to perform the following tasks: 1. Create a ButtonColumn that contains a Delete button. 2. Somehow be able to determine when the Delete button has been clicked and have some server-side code ready to execute. 3. Be able to determine the primary key field value for the row whose Delete button has been clicked. We need to primary key field value so that we can issue a SQL statement to delete the selected row. In addition to examining how to accomplish the above three steps, this article will also look at how to add a client-side confirm dialog box to the Delete button. That is, when a user clicks the Delete button a client-side messagebox will

An Extensive Examination of the DataGrid Web Control: Part 1

Page 16 of 16

appear, asking the user if they are sure if they want to delete the item. If they click OK, the row will be deleted; if they click cancel, nothing will happen. Creating a Delete Button In Part 3 of this article series we examined how to add ButtonColumns to a DataGrid Web control. Recall from Part 3 that any time a DataGrid's ButtonColumn button is clicked by the user, the ASP.NET Web page performs a postback and the DataGrid's ItemCommand event is raised. While we could place our delete code in the ItemCommand event handler, the DataGrid control offers a special event handler for delete buttons: the DeleteCommand event handler. In order to create a ButtonColumn that triggers the DeleteCommand event handler you must set the ButtonColumn's CommandName property to "Delete". Once you do this, you will want to create an event handler for the DeleteCommand event. This event handler takes the form:

Sub eventHandlerName(sender as Object, e as DataGridCommandEventArgs) ... End Sub In this event handler we'll (eventually) place the code to make a database call to delete the specified DataGrid item. Finally, to complete the last piece of the puzzle we must tell the DataGrid that when the DeleteCommand event fires the event handler (eventHandlerName) should be executed. We do this by setting the DataGrid's OnDeleteCommand property to the event handler in the DataGrid's declaration like so: OnDeleteCommand="eventHandlerName". Below you can see a simple example that illustrates adding a Delete button to a DataGrid, adding an event handler for the DeleteCommand event, and wiring up this event handler to the DataGrid's DeleteCommand event:

<script language="vb" runat="server"> ... Sub dgPopularFAQs_Delete(sender As Object, e As DataGridCommandEventArgs) ' Place code to perform delete here... End Sub

[View a Live Demo!] It is important to set the ButtonColumn's CommandName property to "Delete", otherwise the DataGrid's DeleteCommand event won't fire when the command button is clicked. (Rather, just the DataGrid's ItemCommand event will fire.) Also note that in order to have the dgPopularFAQs_Delete event handler execute when the DataGrid's DeleteCommand event fires we had to specify OnDeleteCommand="dgPopularFAQs_Delete" in the DataGrid's declaration.

An Extensive Examination of the DataGrid Web Control: Part 1

Page 17 of 17

Determining the Clicked Row's Primary Key Field Value In order to issue a database command to delete the selected item from the DataGrid, we must be able to uniquely identify the selected item. Usually this takes the form of a numeric primary key field. In the live demos for this article, we're using the ASPFAQs.com database, and the primary key for each FAQ is a database field called FAQID. In Part 3 we looked at one method for retrieving a primary key field value, which involved using a hidden BoundColumn and then referencing the value of the BoundColumn programmatically in the ItemCommand event handler. We could use this approach here as well, since we already have a BoundColumn displaying the FAQID. However, let's use a more elegant approach. The DataGrid control contains a DataKeyField property. This optional property can be used to specify the primary key field for the data being displayed in the DataGrid. If this property is set, a separate DataGrid property, DataKeys (a collection), is populated with the primary key values for each row in the DataGrid. Hence, we can access this collection programmatically in our DeleteCommand event handler. To get the proper item out of the DataKeys collection, we simply reference the index that is equal to the clicked DataGrid row's ItemIndex. This concept is illustrated below:

<script language="vb" runat="server"> ... Sub dgPopularFAQs_Delete(sender As Object, e As DataGridCommandEventArgs) 'Get the FAQID of the row whose Delete button was clicked Dim SelectedFAQID as String = dgPopularFAQs.DataKeys(e.Item.ItemIndex) 'TODO: Delete the record from the database 'TODO: Rebind the DataGrid End Sub

...

[View a Live Demo!] In the live demo you can see that when clicking a Delete button we can ascertain the row's corresponding FAQID. We could have used the techniques learned in Part 3 in order to retrieve the value of the FAQID BoundColumn, but, personally, I find using the DataKeyField / DataKeys approach to be cleaner code. Now that we've examined how to add a Delete button to each row, and how to determine the primary key field value for the row whose Delete button was clicked, writing the code to make the actual database call to delete the record should be fairly simple, and therefore is left as an exercise for the reader. The one thing that is important is to remember to recompute the DataGrid's DataSource and call the DataGrid's DataBind() method. This is needed because the DataSource has changed (a row has been deleted). Before we wrap up this article, let's take a look at how to add some client-side confirmation code for the Delete button. That is, when the user clicks the Delete button for a row in the DataGrid, let's have a client-side messagebox pop up, asking the user if they're sure they want to delete the record. If they click Cancel, the record won't be deleted; if they click OK, however, the record will be deleted. We'll see how to do this in Part 2.

An Extensive Examination of the DataGrid Web Control: Part 1

Page 18 of 18

PART-9 Introduction In Part 6 of this article series we examined how to use the DataGrid to provide editing capabilities. Recall that when using the DataGrid's built-in editing capabilities each row of the DataGrid contains an "Edit" command button. When this command button is clicked, the ASP.NET Web form is posted back, the DataGrid's EditItemIndex is set, and the row whose "Edit" button was clicked is displayed in "editing mode." Editing mode adds an "Update" and "Cancel" command button pair to the row, as well as displaying some sort of editing interface for the non-readonly columns in the row. In Part 7 we looked at how to use the EditItemTemplate to customize the editing interface. By specifying an EditItemTemplate we could use a DropDownList, or a customized TextBox for the editing interfaces of each row. A common question I've seen on the DataGrid Forum on the ASP.NET Forums is along the lines of: "How can I have a TextBox in the EditItemTemplate receive focus when the row is selected for editing?" By "receiving focus" the user means the keyboard cursor immediately appearing in the TextBox when the page loads. (For an example of this, see Google's homepage; note that when the page loads, the cursor immediately appears in the search TextBox, so that you can type your query in and hit enter without having to touch the mouse at all.) In this article we will examine how to provide such functionality. Setting the Focus to a Control Before we delve into how we will set the focus of a TextBox Web control in a DataGrid, we should quickly discuss how one sets focus to a Web page input field. (By input field I mean some sort of HTML element generated by the HTML INPUT tag. For example, the following HTML markup creates a TextBox: .) First, realize that by setting the focus to a input field, such as a TextBox, the user's keyboard will be in control of that field from the get-go. By default no input field has focus - if you want to start typing into a TextBox you have to move your mouse over the TextBox and click it, or keep hitting Tab until the TextBox receives focus. To set the focus of an input field we need to use client-side script. Specifically, we call the input field's focus() method. To have this occur when the page is loaded, we can wire up the BODY tag's onload event to some client-side event handler that contains code that calls the input field's focus() method. For example, the following simple HTML page demonstrates how to accomplish this:

<script language="JavaScript"> function setFocus() { frmInfo.name.focus(); }

Name:
Age:

For some more examples of this concept, there are a ton of online resources worth checking out, such as Focus OnLoad. A Google search can yield more results... Declaring our DataGrid In order to set the focus to a TextBox in the EditItemTemplate, we first must create a DataGrid that has editing capabilities and an EditItemTemplate with a TextBox. Our example for this article, like with the other examples in this article series, will use the ASPFAQs.com database. Specifically, the DataGrid will display an "Edit" column along with the FAQID (marked readonly) and the FAQ question (the Q in FAQ). Thus our DataGrid's declaration will look like:

An Extensive Examination of the DataGrid Web Control: Part 1

Page 19 of 19

<%# DataBinder.Eval(Container.DataItem, "Description") %> <EditItemTemplate>

The important parts of the above code snippet are in boldface. You may be wondering why the ID properties of all of the server controls are bolded. As we'll see shortly, these IDs are quite important, for they will be used to identify the control in our client-side code. Also note that the EditItemTemplate contains a TextBox Web control with an ID of txtDesc. It's Text property is set, via data binding syntax, to the value of the Description field from the DataGrid's DataSource. Code very similar to this was examined in Part 7 of this article series. In order to have the EditItemTemplate's TextBox receive focus when the row is selected for editing, we'll need to conceive of some way to add the appropriate client-side script to the ASP.NET Web page. In Part 2 of this article we'll look at how to add the needed client-side code to the Web page when a row's "Edit" button is clicked.

PART-10 Introduction In the previous nine parts of this article series we have looked at a number of live demos that work with the ASPFAQs.com database. Specifically, we have been calling a stored procedure, sp_Popularity, that returns the 25 most popular FAQs. In today's article we will be changing the SQL just slightly, instead using a SQL statement to get all of the FAQs that fit into a certain category. Realize that the FAQs are broken down into categories, such as: "Arrays," "Database, Errors," "Getting Start," "Security," etc. In fact, in Part 7 of this series we discussed the ASPFAQs.com database schema, presenting the following tables:

tblFAQ FAQID Description

Primary Key, integer

varchar(255) - the "question" part of the FAQ

An integer foreign key to FAQCategoryID the tblFAQCategory table

tblFAQCategory FAQCategoryID Primary Key, integer varchar(255) - the "title" of Name

the category (Array, ASP.NET, etc.)

An Extensive Examination of the DataGrid Web Control: Part 1

Page 20 of 20

Remaining rows omitted for brevity... To retrieve only a subset of rows from the tblFAQ table corresponding to a particular FAQCategoryID, we can do something like:

SELECT Columns FROM tblFAQ WHERE FAQCategoryID = SomeFAQCategoryID In this article we will examine how to allow the user to decide what category of FAQs they wish to have displayed. This article will touch upon techniques discussed in the earlier parts of this article series, as well as information contained in the articles Dynamic Controls in ASP.NET and Working with Dynamically Created Controls. If you have not read the earlier parts to this article series, or have yet to read the other two articles, you are highly encouraged to do so before tackling this article. Changing the BindData Subroutine to Handle Filtering If you have worked through the live demos throughout this article series you will have noted that each demo has a BindData() subroutine that is responsible for binding the appropriate data to the DataGrid. Specifically, this subroutine performs the following tasks: 1. Retrieves the data to display in the DataGrid from the SQL database that houses the ASPFAQs.com database, placing this data in a SqlDataReader. 2. Specifies that the DataGrid's DataSource property should be assigned the SqlDataReader from step (1). 3. Calls the DataGrid's DataBind() method. In our previous demos the SQL query performed by the BindData() subroutine was typically a call to the sp_Popularity stored procedure. In order to have the DataGrid display only those FAQs that belong to a certain FAQ category, we will need to have a parameterized SQL query of the following form:

SELECT Columns FROM tblFAQ WHERE FAQCategoryID = @FAQCategoryIDParameter where the @FAQCategoryIDParameter is a SqlParameter whose value will be set programmatically. Since we already have this handy BindData() subroutine, let's concentrate on slightly altering this subroutine to provide the above SQL form (as opposed to writing a new subroutine from scratch). Since we want to only display FAQs from a particular FAQ category, let's alter the BindData() subroutine so that it accepts an input parameter: FilterOnFAQCategoryID, which will be an integer that signifies the FAQ category whose FAQs should be displayed in the DataGrid. After this small change, all that's left to do is adjust the SQL query from a constant that always issues the sp_Popularity stored procedure to one that uses a parameterized SQL query whose parameter is based on the FilterOnFAQCategoryID input parameter. The code below shows the new BindData(FilterOnFAQCategoryID) subroutine:

Sub BindData(FilterOnFAQCategoryID as Integer) '2. Create the command object, passing in the SQL string Dim strSQL as String strSQL = "SELECT FAQID, F.FAQCategoryID, F.Description, " & _ "FC.Name AS CategoryName " & _ "FROM tblFAQ F " & _ "INNER JOIN tblFAQCategory FC ON " & _ "F.FAQCategoryID = FC.FAQCategoryID " & _

An Extensive Examination of the DataGrid Web Control: Part 1

Page 21 of 21

"WHERE F.FAQCategoryID = @FAQCatID " & _ "ORDER BY FAQID" 'Set the datagrid's datasource to the datareader and databind Dim myConnection as New _ SqlConnection(ConfigurationSettings.AppSettings("connectionString")) Dim myCommand as SqlCommand = New SqlCommand(strSQL, myConnection) Dim FAQCatIDParam as New SqlParameter("@FAQCatID", SqlDbType.Int, 4) FAQCatIDParam.Value = FilterOnFAQCategoryID myCommand.Parameters.Add(FAQCatIDParam) myConnection.Open() dgPopularFAQs.DataSource = myCommand.ExecuteReader() dgPopularFAQs.DataBind() myConnection.Close() End Sub The germane pieces of the code above are bolded. With this change to the BindData(FilterOnFAQCategoryID) subroutine, we can have the DataGrid display a list of FAQs belonging to FAQ category n by calling: BindData(n). Ok, so we have a nifty BindData(FilterOnFAQCategoryID) subroutine that, when called, will display FAQs of a particular FAQ category in the DataGrid. However, we need some way for the user to choose which FAQ category's FAQs she wishes to view. One user interface that would allow for this would be to list, at the top of the page, each of the FAQ categories as a hyperlink or button. If the user clicks one of these FAQ category hyperlinks or buttons, the ASP.NET page should be posted back and the DataGrid should be redisplayed, but this time with a list of the FAQs from the particular category whose hyperlink or button was clicked by the user. In Part 2 of this article we'll examine just how to do this!

PART-11 Introduction There are many real-world situations in which you might like to create a DataGrid that has a column of radio buttons that serve to allow the user to select a particular row. For example, if you wanted to create some sort of online voting application, you might want to use a DataGrid to display the voting options with a radio button next to each option, so that the user can choose one and only one option. Many developers who want to add this sort of functionality think that it is a trivial task, involving merely the addition of a TemplateColumn whose ItemTemplate has a RadioButton Web control. As we will see in this article, the task is not nearly this simple. Specifically, we will look at why using a TemplateColumn with a RadioButton Web control won't cut the mustard, and then we'll examine a free custom DataGrid Column control from Andy Smith's MetaBuilders.com Web site, which easily allows for adding a column of radio buttons to a DataGrid.

An Extensive Examination of the DataGrid Web Control: Part 1

Page 22 of 22

Why the TemplateColumn Approach Won't Work The intuitive way to provide a radio button column in a DataGrid is to use a TemplateColumn where the ItemTemplate has a RadioButton control. (We examined the TemplateColumn in Part 5.) This could be accomplished using the following DataGrid markup:

...

[View a Live Demo!] The above code will create a column that has a radio button in each row, yes, but due to the way that the DataGrid names its containing controls, the user will be able to select multiple radio buttons. (Don't believe me? Try it out in the live demo.) Clearly, being able to select multiple radio buttons breaks the semantics of the radio button metaphor. When the Web browser is confronted with a series of radio buttons, it considers radio buttons with the same name attribute to be of the same group of radio buttons. That is, if you have a number of radio buttons on a Web page with the same name attribute, the user will only be allowed to select one of those same named radio buttons. The ASP.NET RadioButton Web control provides a GroupName property, that specifically sets the resulting radio button's HTML markup to have the name attribute specified by this property. Therefore, if you need to plop down a related series of RadioButton Web controls, you can simply give all of the RadioButton Web controls the same GroupName property value, which will result in radio button HTML markup where each radio button has the same name attribute, which will result in the user only being able to select one of the related radio buttons. However, as you can see in the DataGrid example I provided, even though the GroupName is the same for all RadioButton Web controls in the column, the user can still select more than one distinct radio button. To see why this is, let's take a look at the HTML markup produced by the ASP.NET Web page when the above DataGrid example is used (you can take my word that the below HTML is the HTML markup produced, or you can visit the live demo and do a View/Source):

... The above HTML markup has been shortened considerable for brevity, and is only showing a small subset of the resulting HTML table rows produced by the DataGrid. However, the important part is still noticeable from this sample, and that is that the name property of each of the radio buttons is unique, even though we set the GroupName property to the same value (ThisDoesntWork)! This is because the DataGrid is being built up row-by-row it prepends the rows ID property to the GroupName property, using a colon as a delimiter. Then, it prepends the DataGrid's ID property, and this is what the radio button's name attribute is assigned. As you can see, the name for the first FAQ is: dgPopularFAQs:_ctl2:ThisDoesntWork, which, more generally, is: DataGrid ID:DataGridItem ID:GroupName. Ensuring that Each Radio Button's name Attribute is Identical So how can we make sure that each radio button's name attribute is assigned the same value when the DataGrid prepends the DataGridItem and DataGrid ID properties to the name attribute? The only way that I have found to accomplish this is to use a custom DataGrid column class from which you can explicitly override the naming of the name attribute. (If you know of an easier way, please do let me know.)

What is a Custom DataGrid Column Class? The DataGrid has a number of built-in columns, many of which we have examined throughout this article series. Some of these include the BoundColumn, the TemplateColumn, and the ButtonColumn. A custom DataGrid column class is one that you create yourself. They're fairly easy to create, especially if you have a development tool like Visual Studio .NET (although this isn't a requirement). For more information on creating your own custom DataGrid column class, be sure to read John Dyer's Creating a Custom DataGridColumn Class. Rather than writing our own custom DataGrid column class to perform this renaming, let's use one that is already written, is 100% free, and includes complete source code. I am talking about Andy Smith's excellent RowSelectorColumn control, which you can try out and download from http://metabuilders.com/Tools/RowSelectorColumn.aspx. Once you download the control, copy the control (the DLL file in the bin\Release directory of the downloaded ZIP file) to your Web application's /bin directory. Then, you can use Andy's control by simply adding:

<%@ Register TagPrefix="prefix" Namespace="MetaBuilders.WebControls" Assembly="MetaBuilders.WebControls.RowSelectorColumn" %> to the top of your ASP.NET Web pages that use the control. Let's now turn our attention to using the RowSelectorColumn in an ASP.NET Web page! We'll look at creating a column of radio buttons, as well as a column of checkboxes, using the RowSelectorColumn in Part 2!

PART-12

An Extensive Examination of the DataGrid Web Control: Part 1

Page 24 of 24

Introduction In Part 4 of this article series we looked at how to provide sorting capabilities for the DataGrid Web control. However, the sorting only allowed sorting of each column in one direction. That is, when a user clicked on a DataGrid column's header, the DataGrid's data was redisplayed, being sorted by the column whose header was clicked. Unfortunately, the sorting could only be applied in one direction. For example, if we created a DataGrid that had two columns, a list of products in one column, and their prices in another, we might want to let the user sort the list of products by their price. Using the techniques in Part 4 we could make it so that the user could sort the contents by their price in either ascending order (from cheapest to most expensive) or descending order (from most expensive to cheapest). However, with what we learned in Part 4, we could not provide a means for the user to sort it in either way. One of the most common user interface requirements is to be able to click on a column header and have the data toggle between being sorted in ascending order and descending order. In this article we will examine how to extend the DataGrid so that it can remember the "current sort state" of each column. Then, when the user clicks on a column the current sort order can be detected and toggled. The example used in the article displays attributes from the authors table in the pubs database. This article is divided into the following sections: 1. HTML code declaring the DataGrid used for displaying authors details 2. The subroutine used for populating the DataGrid 3. The sort handler which remembers the sort state and sorts the DataGrid in the requisite order.

For More Information on Bi-Directional Sortable DataGrids... This part of the article series looks at one means to create a bi-directional sortable DataGrid. This same topic is re-addressed in Part 18 of this article series, examining (in my opinion) a better way to accomplish bi-directional sorting. Additionally, Part 18 shows how to gussy up the bi-directional sortable DataGrid, adding an up or down arrow to the column the data is sorted by. Declaring the DataGrid and Displaying the Contents of the authors Table The first order of business is to create an ASP.NET Web page that displays a DataGrid that contains the rows from the authors table of the pubs database. This first task should be fairly straightforward for you, the reader. If it is not, I would encourage you to start with Part 1 and work up toward this installment. The following code creates the DataGrid and displays the appropriate data:

<%@ Import Namespace = "System.Data" %> <%@ Import Namespace = "System.Data.SQLClient" %> <script language="VB" runat="server"> Sub Page_Load(sender As Object, e As EventArgs) ' Only bind the data on the first visit to the page If Not Page.IsPostBack GetAuthors("au_fname asc") End If End Sub Sub GetAuthors(sSortStr as string) Dim cn as SQLConnection Dim cmd as SQLCommand Dim rdr as SQLDataReader Dim sConnectString as String = "server=localhost;database=pubs;user id=sa" Dim sSql as String = "SELECT au_fname, au_lname, city, state, zip " & _ "from authors order by " & sSortStr ' Connect to the database cn = New SQLConnection(sConnectString)

An Extensive Examination of the DataGrid Web Control: Part 1

Page 25 of 25

cn.open() ' execute the SQL cmd = New SQLCommand(sSQL, cn) rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection) ' Associate the data grid with the data DispAuthors.DataSource = rdr DispAuthors.DataBind() End Sub ' this method is called when user clicks on any of the column headings Sub SortAuthors(sender As Object, e As DataGridSortCommandEventArgs) '... Code coming soon! :-) ... End Sub ... [View a Live Demo!] The HTML code defines a datagrid, DispAuthors, that displays authors information from the pubs database. The columns shown are author's first and last names, city, state and zip code. Two important things to notice with the above source code: 1. SortAuthors is the event handler invoked when user clicks on a column header. The source code for this event handler has yet to be provided - we'll be examining the needed code soon. 2. Only the first name, last name and state columns have their SortExpression property specified. This means that only these columns are sortable. Also note that the SortExpression property, along with the column name to sort, also contains information on the sort order (i.e., the First Name column's SortExpression property is au_fname ASC). The GetAuthors() subroutine retrieves the data to be displayed in the DataGrid. This method takes in a string parameter, sSortStr, which specifies how the data from the authors table should be sorted. For example, to sort the results by the au_fname column in ascending order, the argument to GetAuthors() would be au_fname asc. Notice that when the page is first visited, the Page_Load event handler calls GetAuthors() passing in au_fname asc; hence, when the page is first visited, the data will be displayed alphabetically by the authors' first names. Writing the Code for the DataGrid's Sort Event Handler Whenever one of the DataGrid's sortable column headers is clicked, the ASP.NET Web page is posted back and the DataGrid's SortCommand event fires. (Which causes the specified event handler, SortAuthors, to execute.) In order

An Extensive Examination of the DataGrid Web Control: Part 1

Page 26 of 26

to be able to toggle between sorting a column in ascending and descending order, we need to be able to remember how the column was last sorted. To accomplish this we have a couple of options. One way is to store a bit of information in the ViewState for each sortable column, indicating if the column has last been sorted in ascending or descending order. Another way, and the way we will be doing it in this article, is storing this extra information in the column's SortExpression property. In the previous code example, you may have noticed that the SortExpression property for the First Name column indicated the order of the sorting. Specifically, the First Name column's SortExpression property was set to au_fname ASC. This demonstrates how the SortExpression property can be set so that it not only conveys the column to sort, but also the sort direction. Let's take a look at the code for the SortAuthors event handler, after which we'll discuss what, specifically, the code does.

Sub SortAuthors(sender As Object, e As DataGridSortCommandEventArgs) Dim SortExprs() As String Dim CurrentSearchMode As String, NewSearchMode As String Dim ColumnToSort As String, NewSortExpr as String ' Parse the sort expression - delimiter space SortExprs = Split(e.SortExpression, " ") ColumnToSort = SortExprs(0) ' If a sort order is specified get it, else default is descending If SortExprs.Length() > 1 Then CurrentSearchMode = SortExprs(1).ToUpper() If CurrentSearchMode = "ASC" Then NewSearchMode = "Desc" Else NewSearchMode = "Asc" End If Else ' If no mode specified, Default is descending NewSearchMode = "Desc" End If ' Derive the new sort expression. NewSortExpr = ColumnToSort & " " & NewSearchMode ' Figure out the column index Dim iIndex As Integer Select Case ColumnToSort.toUpper() case "AU_FNAME" iIndex = 0 case "AU_LNAME" iIndex = 1 case "STATE" iIndex = 3 End Select ' alter the column's sort expression DispAuthors.Columns(iIndex).SortExpression = NewSortExpr ' Sort the data in new order GetAuthors(NewSortExpr) End Sub [View a Live Demo!]

An Extensive Examination of the DataGrid Web Control: Part 1

Page 27 of 27

The SortAuthors event handler starts by splitting the SortExpression property on a space. This returns a string array with either one or two elements - one element if the SortExpression property has just the column name to sort on, like au_lname, and two elements if the SortExpression property has both the column name and the direction, like au_fname ASC. In the one-element case, the one element contains the name of the column to sort on; in the two-element case, the first element contains the column to sort on, while the second element specifies the direction. Next, the second element (if it exists) is examined and then toggled. This has the effect of if the column was sorted in ascending order the previous time, this time it will be sorted in descending order, and vice-a-versa. This toggled value becomes the new SortExpression property for the column that was clicked. Finally, a call to GetAuthors(), passing in the new sort expression, displays the sorted data.

One Caveat... One thing you need to be aware of here is the Select Case statement, which is used to determine what DataGrid column's sortable header was clicked. Since this Select Case statement contains the various column named hard-coded in there, if you add a new sortable column to the DataGrid, or change the order of the sortable columns in the DataGrid, you will need to update this code. An alternative approach, using the ViewState, alleviates this dependence. For more information on using the ViewState to allow for a DataGrid that provides ascending and descending sortable columns, pick up a copy of my (Scott Mitchell) book: ASP.NET Data Web Controls Kick Start. This ViewState approach is also discussed in more detail in Part 18 of this article series. Conclusion In this article we saw how to enhance the DataGrid's sorting capabilities so that the DataGrid's columns could be toggled from being sorted in ascending order to being sorted in descending order. To accomplish this we adjusted the SortExpression property of the clicked DataGrid column in the DataGrid's SortCommand event handler.

PART-13 Introduction Throughout this article series we have been looking at an example that displays information about the ASPFAQs.com FAQs. Specifically, the data displayed has been the results from a stored procedure that returns the 10 most-viewed FAQs. (For example, this live demo shows the data returned by the stored procedure.) One piece of data returned by the stored procedure is a column called ViewCount, which returns the number of times a particular FAQ has been viewed. What would be nice, though, is to not only see the views for each of the 10 most popular FAQs, but to also be shown the sum of these views at the bottom of the DataGrid. (To see the desired output, check out this live demo.) In this article we will see how to compute the sum of a DataGrid column and how to display this sum in the DataGrid's footer. Computing the Sum of a Column There are a couple of ways to compute the sum of a DataGrid column. One way is to not actually compute the sum of the DataGrid column, per se, but to compute the sum of the data through a SQL query. For example, to compute the sum of all ASPFAQs.com FAQs, the following SQL statement could be used:

SELECT SUM(ViewCount) FROM tblFAQs The results of the SQL query, however, must be retrieved in a DataReader or DataSet other than the one that's being bound to the DataGrid - this means two round trips to the database server. Also, if you have, say, three DataGrid column that you want to compute sums for, then you'll need four separate SQL queries - one for all the data and then one for the sum of each of the three columns. A simpler way to compute the sum of a DataGrid column is to do it on the fly, as the DataGrid is being constructed. In Part 8 of this article series we saw that when the DataGrid's DataBind() method is called, the DataSource object is

An Extensive Examination of the DataGrid Web Control: Part 1

Page 28 of 28

enumerated over. For each record in the DataGrid's DataSource, a new DataGridItem is created. After the DataGridItem is created and the DataSource's is bound to the row, the ItemDataBound event is fired. Again, this happens for each row added to the DataGrid. We can provide an event handler for the DataGrid's ItemDataBound event. In this event handler, all that we need to do is determine the value of the column that we want to sum and add it to a variable that holds the running total. The code for this isn't particularly difficult, and can be seen below:

<script language="VB" runat="server"> Dim viewCountSum as Integer = 0 Sub ComputeSum(sender As Object, e As DataGridItemEventArgs) 'First, make sure we are dealing with an Item or AlternatingItem If e.Item.ItemType = ListItemType.Item OR _ e.Item.ItemType = ListItemType.AlternatingItem then 'Snip out the ViewCount Dim viewCount as Integer = _ Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "ViewCount")) viewCountSum += viewCount End If End Sub The important lines of the code sample are displayed in bold. First, note that a variable outside of all event handlers, subroutines, and functions - viewCountSum - has been declared. This is the variable that will hold the running total. Next, in the DataGrid, the ItemDataBound event is wired up to the ComputeSum event handler. The ComputeSum event handler is fairly straightforward. It first makes sure that the row we are dealing with is an Item or AlternatingItem row. Realize that the ItemDataBound event handler fires for all DataGrid rows, including the header and footer. (If we omitted the check to ensure that we were working with an Item or AlternatingItem row, we'd get a runtime error when trying to access e.Item.DataItem for the header and footer rows.) After ensuring that we are working with an Item or AlternatingItem row, the ViewCount item from the DataSource's current DataItem is read and stored into a variable viewCount. This variable is then used to increment viewCountSum, the running total. Displaying the Sum in the DataGrid's Footer Now that the viewCountSum variable stores the running sum, how do we display the running sum in the DataGrid's footer? Recall that the ItemDataBound event handler fires for all DataGrid rows. This includes not only the DataGrid's Item and AlternatingItem rows, but also the header and... footer. Since the footer is created last, by the time the footer's ItemDataBound event fires, all of the ItemDataBound events for the databound rows would have fired, meaning that when the footer is created viewCountSum has already been computed.

An Extensive Examination of the DataGrid Web Control: Part 1

Page 29 of 29

All that remains is to emit the value of viewCountSum into the proper footer cell. This can be accomplished by altering the ComputeSum event handler to contain the following code:

Sub ComputeSum(sender As Object, e As DataGridItemEventArgs) 'First, make sure we are dealing with an Item or AlternatingItem If e.Item.ItemType = ListItemType.Item OR _ e.Item.ItemType = ListItemType.AlternatingItem then 'Snip out the ViewCount Dim viewCount as Integer = _ Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "ViewCount")) viewCountSum += viewCount ElseIf e.Item.ItemType = ListItemType.Footer then e.Item.Cells(1).Text = "Total: " & String.Format("{0:#,###}", viewCountSum) End If End Sub [View a Live Demo!] Note that the ComputeSum event handler has been updated to now also check to see if the ItemType being added is a Footer. If it is, then the 2nd column's Text property is set to the value of viewCountSum (with some formatting thrown in for aesthetics). As the code illustrates, the Footer contains a column for each column in the DataGrid, and to access the ith column, simply use: e.Item.Cells(i). The one thing to remember is that the Cells collection is zero-based, so Cells(1) accesses the second column. (The reason the second column's footer displays the total is because the DataGrid's second column lists the number of page views for each FAQ.) To complete this code, there's one very important step you must take. By default, the DataGrid does not show the footer, so in order to display a running sum in the footer, you must indicate that the footer should be displayed. To do this, set the ShowFooter DataGrid property to True. You can do this in the DataGrid's declaration, like so:

... Conclusion In this article we examined how to use a DataGrid to display the sum of a column in the DataGrid's footer. This was accomplished by using the ItemDataBound event to store the sum of the viewCountSum variable and the value of a particular column for each row. Then, when the footer's ItemDataBound event fired, the value of the viewCountSum variable was displayed in the footer. Happy Programming!

PART-14 Introduction A common relationship in database design is a one-to-many relationship. With a one-to-many relationship, a record in one table has zero to many related items, which are typically represented as records in a related table. For example, the ASPFAQs.com database has two tables: tblFAQ and tblFAQCategory. The tblFAQCategory table has a row for each of the FAQ categories, such as: Arrays, Strings, Dates and Times, Database Errors, and so on. The tblFAQ table contains a record for each individual FAQ, and contains columns like Description, Question, ViewCount, and so on.

An Extensive Examination of the DataGrid Web Control: Part 1

Page 30 of 30

To relate the tblFAQ and tblFAQCategory tables, a foreign key relationship is used. The tblFAQCategory table's primary key is FAQCategoryID, and therefore serves as a unique identifier for each FAQ category. The tblFAQ table contains a foreign key FAQCategoryID, which assigns each FAQ to a particular FAQ category. A master/detail report is a report type that Web developers for data-driven Web sites are commonly asked to produce. A master/detail report is one that lists each highlevel item - such as each FAQ category - and beneath each high-level item lists the subitems that belong to that particular item - such as all the FAQs of a particular category. In this article we'll examine how to create such a master/detail report using the DataGrid. (An example master/detail report can be seen in the screenshot on the right.) Listing the FAQ Categories Before we concern ourselves with how to list the various FAQs for each FAQ category, let's get the easy part of this task out of the way first - creating a DataGrid that lists each FAQ category. This is a fairly simple task, one that we've examined in previous installments of this article series. The following code lists the categories from the tblFAQCategory table. Note that our SQL query retrieves both the primary key FAQCategoryID column as well as the Name column, although only the Name column is displayed in the DataGrid. This is because later, when we want to include the FAQs for the particular FAQ category, we'll need to know the FAQCategoryID column value.

<%@ Import Namespace = "System.Data" %> <%@ Import Namespace = "System.Data.SQLClient" %> <script language="VB" runat="server"> Sub Page_Load(sender as Object, e as EventArgs) BindData() End Sub Sub BindData() '1. Create a connection Dim myConnection as New _ SqlConnection(ConfigurationSettings.AppSettings("connectionString")) '2. Create the command object, passing in the SQL string Dim strSQL as String = _ "SELECT FAQCategoryID, Name FROM tblFAQCategory ORDER BY Name" Dim myCommand as New SqlCommand(strSQL, myConnection) 'Set the datagrid's datasource to the datareader and databind myConnection.Open() dgMasterDetail.DataSource = _ myCommand.ExecuteReader(CommandBehavior.CloseConnection) dgMasterDetail.DataBind() myConnection.Close() End Sub The FAQs for the FAQCategory will go here...

An Extensive Examination of the DataGrid Web Control: Part 1

Page 31 of 31

[View a Live Demo!] Notice that the DataGrid has two columns, one for the FAQ category's name, and one for the FAQs for the particular category. In this second TemplateColumn we'll be programmatically adding a DataGrid that displays the FAQs for the particular FAQ category. Displaying the FAQs for the Particular FAQ Category Now that we have our DataGrid that displays the various FAQ categories, we need to enhance it so that the FAQ for each FAQ category is also displayed. To accomplish this we need to be able to programmatically add a DataGrid with the appropriate FAQs for the FAQ category. This can be accomplished by creating an event handler for the DataGrid's ItemDataBound event. As discussed in Part 13 of this article series, the ItemDataBound event fires after each new row in the DataGrid is created and has been bounded to the appropriate row in the DataSource. At this point, we can programmatically inspect the data bound to the row (which will contain the FAQCategoryID information we need), and alter the actual row's contents. To add an event handler for the ItemDataBound event, we simply need to create an appropriate event handler and wire it up to the DataGrid's event. The following code demonstrates this:

<script language="VB" runat="server"> ... Sub buildFAQsDataGrid(sender as Object, e as DataGridItemEventArgs) If e.Item.ItemType = ListItemType.Item OR _ e.Item.ItemType = ListItemType.AlternatingItem then 'Build the DataGrid Dim dg as New DataGrid() 'Find out the CategoryID Dim CatID as Integer = e.Item.DataItem("FAQCategoryID") 'Bind the Data to the DataGrid dg.DataSource = GetFAQsByCategoryID(CatID) dg.DataBind() 'Add the DataGrid to the 2nd Column e.Item.Cells(1).Controls.Add(dg) End If End Sub ... The above code shows a couple things, and has a couple holes in it. First, note that the DataGrid declaration includes: OnItemDataBound="buildFAQsDataGrid". This wires up the DataGrid's ItemDataBound event to the buildFAQsDataGrid event handler.

An Extensive Examination of the DataGrid Web Control: Part 1

Page 32 of 32

The buildFAQsDataGrid event handler starts by checking to see the type of item being created. Note that the ItemDataBound event fires when the DataGrid header and footers are being created as well. Since we are only interested in the actual items and alternating items, we check to make sure that the new DataGridItem's ItemType is either an Item or AlternatingItem. If we are dealing with an item or alternating item, we next create a new DataGrid, dg. This DataGrid will be used to display the FAQs for the particular category. Following this, we create a local variable named CatID, and assign to it the value of the FAQCategoryID value from the DataSource. Next, we call a function - which is not shown - called GetFAQsByCategoryID(CategoryID), which presumably returns the FAQs for a particular category. We then call the DataBind() method and add the DataGrid to the TemplateColumn. The above code, with the addition of a GetFAQsByCategoryID() function, will do the trick. However, it's horribly inefficient (assuming that the GetFAQsByCategoryID() function makes a database call that retrieves the FAQs for the particular category). Its inefficiency springs from the fact that for each FAQ category, an entire new database access must transpire. That is, if there are 15 FAQ categories, there will be 15 separate database queries. While each database query will only return the needed records, it would be much more efficient to do one large database query at the beginning, getting all of the FAQs, and then only binding the correct ones for each FAQ category row. (The reason this approach is more efficient than the former approach is because the majority of the "cost" of a database query is in setting up and tearing down the connection. We can amortize this building up/tearing down cost by doing just one large query at the beginning.) Of course, the problem we are now faced with is how to bind only a selective chunk of data to a DataGrid. We'll tackle this in Part 2 of this article.

PART-15 Introduction As discussed in previous articles, the DataGrid makes displaying database data an absolute breeze. All it takes is configuring the DataGrid's properties and writing two lines of code. While this simple approach works great for displaying relatively small amounts of data, displaying large amounts of data can result in exceptionally large DataGrids. Such large DataGrids have two main disadvantages: 1. Readers cannot be expected to be able to digest such large blocks of information, and 2. Dumping large amounts of data in one Web page leads to large Web page sizes, which can take unbearably long to download on dial-up connections. Focusing on the first disadvantage, realize that when presenting your users with information, you want to do so in small, manageable chunks. Blasting all of the data to the screen at once can be overwhelming for the person visiting the Web page. For example, searching Google for the term "DataGrid" yields roughly 217,000 results. Thankfully, though, Google only presents 10 of them at a time! In this article we will see how to use the DataGrid's built-in paging facilities in order to display database data in manageable chunks. Paging Support in the DataGrid The DataGrid supports two kinds of paging: 1. Default paging, and 2. Custom paging Default paging, which this article focuses on, is the simpler of the two to setup and configure. As we will see, getting default paging to work only requires a few minor modifications to the standard process of displaying data in a DataGrid. The major difference between default paging and custom paging is this: with default paging, each time you want to display a page of data in the DataGrid, you need to grab all of the data from the underlying data source. Then, the

An Extensive Examination of the DataGrid Web Control: Part 1

Page 33 of 33

DataGrid only selectively displays part of the entire set of data, based on what page you want to display and how many records per page to display. The key thing to understand here is that everytime the page is loaded, the entire data result is retrieved. For example, imagine that you work at an eCommerce company and you want to allow the user to page through a list of the 150 products your company sells. Specifically, you want to display 10 records per page. Now, when a user visits the Web page, you will access all 150 records, and the DataGrid will display the first 10 products (products 1 to 10). Next, imagine that the user navigates to the next page of data. This will cause a postback, at which point you'll rerequest all 150 records, but this time the DataGrid will display the second set of 10 (products 11 to 20). With custom paging, you, the developer, have to do a bit more work. Rather than just being able to blindly bind database data to the DataGrid, you have to selectively retrieve only those records that should be shown for the particular page. The benefit of this is that when displaying the first page of data, you can use a SQL statement that only retrieves products 1 through 10, rather than all 150 records. However, your SQL statement has to be "clever" enough to be able to know how to just snip out the right subset of records from the 150.

The Performance Edge of Custom Paging Realize that custom paging provides better performance than default paging because only those database records that need to be displayed are retrieved. In our products example, we assumed there were 150 products, showing 10 per page. With custom paging, if the user stepped through all 15 pages of data, precisely 150 records would have been queried from the database. With default paging, however, for each page of data, 150 records would have been accessed, leading to a total number of retrieved records of 15 times 150, or 2,250! While custom paging exhibits better performance, default paging is much easier to use. Therefore, I would encourage you to use default paging if the data you are paging through is relatively small and/or the database server is not heavily trafficked. If you have thousands of tens of thousands of records you are paging through, by all means use custom paging. However, for paging through something like the ASPFAQs.com database, which only has, currently, ~200 FAQs, default paging is sufficient. Understanding How the DataGrid Implements Paging Before we examine how to add paging support, it's imperative that we take a moment to discuss how the DataGrid implements paging. The DataGrid contains a handful of properties designed to aid in paging:



CurrentPageIndex - indicates what page of data to display. This property is indexed at zero, meaning to

 

display the first page of data, this property's value should be 0; to display the second page of data, this property's value should be 1; and so on. (This property's default value is 0, so by default the first page of data will be displayed.) PageSize - indicates how many records to show per page. PageCount - indicates how many total pages of data there are. This value is computed by the ceiling of the number of total records divided by the number of records per page. AllowPaging - this Boolean property indicates whether or not the DataGrid is pageable. In order to use the DataGrid's built-in paging features, you need to set this to True.



There are some additional paging properties that are germane to custom paging; however, this article will be focusing on default paging. Pageable DataGrid's automatically display a paging interface. This paging interface is customizable but, by default, displays a left and right arrow hyperlink for navigating to the previous and next pages. When a user clicks on one of these navigational hyperlinks, the ASP.NET Web page is posted back and the DataGrid's PageIndexChanged event fires. Essentially, to provide paging support you need to add an event handler that responds to this event. Specifically, the event handler needs to correctly adjust the DataGrid's CurrentPageIndex property and rebind the DataGrid. We'll see how, exactly, to accomplish this in Part 2!

PART-16

An Extensive Examination of the DataGrid Web Control: Part 1

Page 34 of 34

Introduction In Part 6 of this article series we looked at how to build an editable DataGrid, which allows the end user to edit the grid's data one row at a time. When a row is edited, each BoundColumn turns into a TextBox, allowing the end user to modify the value. This works well if the data bound to the DataGrid consists of only text data, but is insufficient if the DataGrid has lookup columns. For example, imagine that we needed to display information about products for sale. Each product likely belongs to a category of products, and the association between products and product categories is implemented as a foreign key in the products table. When editing a product, we don't want the end user to have to type in the name of the category, but rather select a category from a list of applicable categories. Thankfully, the editing interface for the DataGrid can be customized by using TemplateColumns. In Part 7 we saw precisely how to use a TemplateColumn to display a DropDownList of values. (See this live demo for an example of an editable DataGrid that uses a DropDownList in its editable row.) Another common question I am asked is how to create an editable DataGrid where the editing interface provides a number of data-dependent DropDownLists. Returning to our products example, imagine that there were thousands of potential product categories, organized into various categories and subcategories. For example, a book on ASP.NET being sold at Amazon.com might belong in the Books > Computer and Technical > Web Development > ASP.NET category. When editing this information, rather than present all of the categories, it might make sense to first have the user choose the top-level category (Books, Electronics, DVDs, etc.), then, based on that selection, the secondlevel selection would be populated: Non-Fiction, Romance, Computer and Technical, Mystery, etc.; and so on, until the user has selected the most distinct category. In this article we'll examine how to create an editable DataGrid that has two columns with data-dependent values. When a row is edited, these two columns will display DropDownLists, and we'll see how the user's choice in the first DropDownList affects the choices in the second. (If you haven't yet read Part 7, please take a minute to do so, as this article assumes you are familiar with the basics of creating an editable DataGrid with a DropDownList.) Examining a Sample Data Model Before we look at the specifics of displaying an editable DataGrid with two data-dependent DropDownLists, let's first take a look at a data model that could be used in such a scenario. Imagine that there exists a Employees table, which includes a foreign key to the Departments table, which lists all of the departments in the company, and associates each employee with a single department. Now, since there may be hundreds of departments in a large company, the company is arranged into a number of divisions. Each division is comprised of a number of departments. This is modeled in the database via the Divisions table and the foreign key from Departments to Divisions.

Employees

Departments Divisions

EmployeeID

int

DepartmentID int

Name

varchar(50)

Name

varchar(50)

DivisionID

int (FK)

DivisionID int Name DepartmentID int (FK) ...

...

varchar(50) ...

For example, the company might have three divisions: I.T., Sales, and Marketing. Each division would have multiple departments, and each department would have multiple employees.



Division: I.T. o Department: Internet Team  Scott  Sam  Jisun o Department: eCommerce Security Team  Ben  Dave  Chris  Bruce

An Extensive Examination of the DataGrid Web Control: Part 1



Page 35 of 35

Division: Sales o ...

(At the end of this article you'll find a download that contains a Microsoft Access database with the Employees, Departments, and Divisions tables, along with an ASP.NET Web page that illustrates these concepts.) Displaying Employee Information in a DataGrid Our next task is to create a DataGrid that displays information about the employees in the company. Each row of the DataGrid will need to contain information about a single employee, including the division and department they work for. To get all employees, along with their division and department names, we'll need to use a SQL query that performs an inner join, connecting the correct rows from the Departments and Divisions table for each employee. The SQL query might look something like:

SELECT EmployeeID, e.Name, d.DepartmentID, d.Name as DeptName, dv.DivisionID, dv.Name as DivName FROM (Employees e INNER JOIN Departments d ON d.DepartmentID = e.DepartmentID) INNER JOIN Divisions dv ON dv.DivisionID = d.DividsionID ORDER BY e.Name

-- Employee info -- Department info -- Division info

Since we'll need to use a DropDownList when editing the division and department columns in the DataGrid, we'll need to use TemplateColumns rather than BoundColumns for these two columns. The column displaying the employee's name, however, can be a BoundColumn. The following shows what the DataGrid's declarative syntax might look like (we'll look at adding the EditItemTemplate portions of the two TemplateColumns shortly).

<%# DataBinder.Eval(Container.DataItem, "DivName") %> <%# DataBinder.Eval(Container.DataItem, "DeptName") %> Such a DataGrid would have output like: Name Edit Ben

Division Department I.T.

eCommerce Security Team

Edit Charles Sales

Sales Force Management Team

Edit Chris

I.T.

eCommerce Security Team

Edit Scott

I.T.

Internet Team

...

An Extensive Examination of the DataGrid Web Control: Part 1

Page 36 of 36

The next challenge is to create the EditItemTemplate for the division and department columns. We'll accomplish this is Part 2 of this article.

PART-17 Introduction Along with sorting and paging features, the DataGrid control provides functionality for editing its data, one row at a time. (See Part 6 of this article series for more on the DataGrid's editing capabilities.) While the editing features of the DataGrid are relatively easy to implement, the fact that it constrains the user to editing one row at a time can lead to excessive clicks for scenarios where the user needs to update large amounts of data on the DataGrid. That is, if a user needs to make numerous changes, having to click the Edit button, change the values, and then click Update for each row that needs to be updated can be tedious and time consuming. A better approach for such situations would be to make the entire DataGrid editable, with a single Button at the bottom that, when clicked, would update the entire contents of the DataGrid. Making an entire DataGrid editable is not exceptionally difficult, especially if you're already familiar with how to customize the editing interface through TemplateColumns, a technique discussed in Part 7 of this article series. Crafting a fully-editable DataGrid merely requires two steps:

1. Using a TemplateColumn for each editable DataGrid column and adding the editable user interface in the s for each TemplateColumn, and 2. Creating an "Update All Records" Button Web control that, when clicked, iterates through the DataGrid's items, sending an UPDATE statement to the database for each record. In this 17th installment of the article series, we'll step through the process of creating a fully-editable DataGrid. Read on to learn more! Making the Entire DataGrid Editable The first step in creating a fully-editable DataGrid is having the DataGrid render all of its rows as editable. When a DataGrid is rendered in HTML markup each row in the DataGrid has its columns rendered depending on what type of DataGridColumn specified: 

For BoundColumns, the output is simply the value of the specified DataSource field. BoundColumns can be specified in one of two ways: explicitly or implicitly. Explicitly specified BoundColumns are ones added in the section of the DataGrid's declarative syntax, like: ...







BoundColumns can also be added implicitly. If you do not set the DataGrid's AutoGenerateColumns property to False, then for each field in the DataGrid's DataSource, there will be a corresponding BoundColumn. If a particular DataGrid record is marked as editable, then a BoundColumn is rendered as a TextBox Web control with the DataSource's value as the Text property of the TextBox. For TemplateColumns, the content in the is rendered for non-editable rows; for the editable row, the <EditItemTemplate> (if provided) is used instead. TemplateColumns can only be added explicitly.

An Extensive Examination of the DataGrid Web Control: Part 1

Page 37 of 37

A row in the DataGrid is marked as editable only if its index matches up to the DataGrid's EditItemIndex property. When creating an editable DataGrid - one that's editable one row at a time - you typically create an event handler for the DataGrid's EditCommand event, which fires when the user clicks the Edit button for a particular row. In this event handler, you'll update the DataGrid's EditItemIndex property to the index of the row whose Edit button was clicked. (Refer back to Part 6 for more information on creating an editable DataGrid.) When creating a fully-editable DataGrid, we need all records to be editable. Rather than use the DataGrid's default editing capabilities, we need to make each record display its editable interface. We can accomplish this by making each editable column in the DataGrid a TemplateColumn, defining the editable interface in the . That is, in a DataGrid that utilizes the standard editing technique, whatever markup you'd put in a TemplateColumn's <EditItemTemplate> should instead be placed in the for our fully-editable DataGrid. The following code and live demo illustrates how to accomplish the task of creating a fully-editable DataGrid user interface. The DataGrid shown here displays data from the ASPFAQs.com database, and includes information about the first several FAQs. It lists each of these FAQ's ID, Category, Question, and who submitted the question/answer. Note that the ID and category here are created as BoundColumns, thereby making them read-only. The Question and Submitted By columns, however, are editable for each record since they are implemented as TemplateColumns with the appropriate editing interface defined in their s. [View a Live Demo!] At this point we've seen how to create a DataGrid that, when displayed, has all rows in a specified column rendered as editable columns. All that remains is providing the user with a means to save their changes en masse. We'll see how to accomplish this in Part 2 of this article.

PART-18

An Extensive Examination of the DataGrid Web Control: Part 1

Page 38 of 38

Introduction Along with sorting and paging features, the DataGrid control provides functionality for editing its data, one row at a time. (See Part 6 of this article series for more on the DataGrid's editing capabilities.) While the editing features of the DataGrid are relatively easy to implement, the fact that it constrains the user to editing one row at a time can lead to excessive clicks for scenarios where the user needs to update large amounts of data on the DataGrid. That is, if a user needs to make numerous changes, having to click the Edit button, change the values, and then click Update for each row that needs to be updated can be tedious and time consuming. A better approach for such situations would be to make the entire DataGrid editable, with a single Button at the bottom that, when clicked, would update the entire contents of the DataGrid. Making an entire DataGrid editable is not exceptionally difficult, especially if you're already familiar with how to customize the editing interface through TemplateColumns, a technique discussed in Part 7 of this article series. Crafting a fully-editable DataGrid merely requires two steps:

1. Using a TemplateColumn for each editable DataGrid column and adding the editable user interface in the s for each TemplateColumn, and 2. Creating an "Update All Records" Button Web control that, when clicked, iterates through the DataGrid's items, sending an UPDATE statement to the database for each record. In this 17th installment of the article series, we'll step through the process of creating a fully-editable DataGrid. Read on to learn more! Making the Entire DataGrid Editable The first step in creating a fully-editable DataGrid is having the DataGrid render all of its rows as editable. When a DataGrid is rendered in HTML markup each row in the DataGrid has its columns rendered depending on what type of DataGridColumn specified: 

For BoundColumns, the output is simply the value of the specified DataSource field. BoundColumns can be specified in one of two ways: explicitly or implicitly. Explicitly specified BoundColumns are ones added in the section of the DataGrid's declarative syntax, like: ...







BoundColumns can also be added implicitly. If you do not set the DataGrid's AutoGenerateColumns property to False, then for each field in the DataGrid's DataSource, there will be a corresponding BoundColumn. If a particular DataGrid record is marked as editable, then a BoundColumn is rendered as a TextBox Web control with the DataSource's value as the Text property of the TextBox. For TemplateColumns, the content in the is rendered for non-editable rows; for the editable row, the <EditItemTemplate> (if provided) is used instead. TemplateColumns can only be added explicitly.

A row in the DataGrid is marked as editable only if its index matches up to the DataGrid's EditItemIndex property. When creating an editable DataGrid - one that's editable one row at a time - you typically create an event handler for the DataGrid's EditCommand event, which fires when the user clicks the Edit button for a particular row. In this event handler, you'll update the DataGrid's EditItemIndex property to the index of the row whose Edit button was clicked. (Refer back to Part 6 for more information on creating an editable DataGrid.)

An Extensive Examination of the DataGrid Web Control: Part 1

Page 39 of 39

When creating a fully-editable DataGrid, we need all records to be editable. Rather than use the DataGrid's default editing capabilities, we need to make each record display its editable interface. We can accomplish this by making each editable column in the DataGrid a TemplateColumn, defining the editable interface in the . That is, in a DataGrid that utilizes the standard editing technique, whatever markup you'd put in a TemplateColumn's <EditItemTemplate> should instead be placed in the for our fully-editable DataGrid. The following code and live demo illustrates how to accomplish the task of creating a fully-editable DataGrid user interface. The DataGrid shown here displays data from the ASPFAQs.com database, and includes information about the first several FAQs. It lists each of these FAQ's ID, Category, Question, and who submitted the question/answer. Note that the ID and category here are created as BoundColumns, thereby making them read-only. The Question and Submitted By columns, however, are editable for each record since they are implemented as TemplateColumns with the appropriate editing interface defined in their s. [View a Live Demo!] At this point we've seen how to create a DataGrid that, when displayed, has all rows in a specified column rendered as editable columns. All that remains is providing the user with a means to save their changes en masse. We'll see how to accomplish this in Part 2 of this article.

---

Related Documents

Datagrid Web Control

November 2019 13

Datagrid

April 2020 13

Datagrid

November 2019 14

Creating Datagrid Control In Asp

November 2019 4

Color Datagrid

October 2019 16

Control De Servidor Web

July 2020 7

Copyright © 2024 PDFCOKE.

	FAQ ID	FAQ Description
	144	Where can I host my ASP Web ...
An Extensive Examination of the DataGrid Web Control: Part 1 Page 23 of 23 name="dgPopularFAQs:_ctl3:ThisDoesntWork" value="NeitherDoesThis" />	115	I am using Access and ...
	161	How can I convert a Recordset ...