When you work with the GridView, you must have a DataSource configured. For example, you can add the Northwind database to the App_Data folder of your project, drag a GridView object to the Web form, and then click the smart tag arrow on the top right of the GridView object to reveal the GridView Tasks window (as shown earlier in Figure 8-4). From the Choose Data Source drop-down list, you can select the New Data Source item to launch the Data Source Configuration Wizard. Select the Database icon to create a new data source. Change the Data-Source name from SqlDataSource1 to EmployeeList. You can select the Northwnd.mdf file as the connection. Notice that the connection string is configured to connect to the file, as shown in Figure 8-5.
Figure 8-5: The connection string shows that your project will connect to the Northwnd.mdf file in the App_Data folder.
The next wizard page gives you the option of storing the connection string in the application configuration (Web.config) file. Accept the default setting to save the connection string in the Web.config file as a setting called ConnectionString .
The next page prompts you for the data you want to retrieve. You will be working with the Employees table and will retrieve a simple subset of columns: EmployeedID, LastName, FirstName, BirthDate, and ReportsTo (as shown in Figure 8-6). The Photo field will be retrieved as a separate query, which I will discuss later in this chapter.
Figure 8-6: Retrieving a subset of columns in the Employees table
This page also contains an Advanced button, which displays an option to automatically generate insert, update, and delete statements that match the select statement, and an option to use optimistic concurrency. For this example, select the first option only, as shown in Figure 8-7, but note that in a production environment you should always use stored procedures instead of the generated statements.
Figure 8-7: You can select both options to create INSERT, UPDATE, and DELETE statements using optimistic concurrency.
The last page of the Configure Data Source Wizard allows you to test your query to verify that you are retrieving the correct data. After you test the query, you can click Finish to complete the configuration.
Notice that adding a database to the project and configuring the data source for the GridView object did not automatically create a typed DataSet class in your project. The data source was created to operate with stateless controls on the current Web page, so you did not need to provide the extra functionality of a typed or untyped DataSet object. If you want to use typed or untyped DataSet objects, you should use the ObjectDataSource option. The ObjectDataSource option lets you select a class in your project and then prompts you to choose a method to be executed to select, insert, update, or delete data. The select method can return a DataSet, a DataTable, a DataReader, or a typed collection.
When the GridView was dropped onto the Web form and the data source was configured, the GridView Tasks window was displayed to give you access to some of the common tasks for setting up the GridView object quickly (Figure 8-8). In this example, we selected all of the options and used the Auto Format option to select the Brown Sugar theme. We set the column headers to ID, Last Name, First Name, Birth Date, and Reports To by using the Edit Columns option in the GridView Tasks window.
Figure 8-8: The options for the GridView object in the GridView Tasks window
Note | If you don't have the ability to enable editing and deleting in the GridView Tasks window, you probably did not configure the data source to generate insert, update, and delete statements. |
In the Properties window for the GridView object, change the PageSize property from the default value of 10 to 3. You wouldn't be able to see the paging options with the default setting of 10 because the Employees table has only 9 rows.
After configuring the GridView object, you can press the F5 key to build and run the project. You can tell that paging is enabled because page numbers are displayed at the bottom of the GridView object. Because sorting is also enabled, the column headers are displayed as links that you can click to sort in ascending order or descending order.
If you click the Select link, the SelectedRow style is applied and you see a highlighted row. If you click the Edit link on a row, you see edit controls in any columns that can be modified. Figure 8-9 shows the GridView object with row 2 selected; row 3 is in edit mode.
Figure 8-9: The configured GridView object with paging and sorting enabled
As you can see, the selected row and the row being edited are independent of each other. This might be a feature, but in most cases you will probably want these to be tied together; you can accomplish this by adding a bit of code to the RowEditing event handler, as shown in the following code snippet.
Visual Basic
Protected Sub GridView1_RowEditing( _ ByVal sender As Object, _ ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) _ Handles GridView1.RowEditing GridView1.SelectedIndex = e.NewEditIndex End Sub
C#
protected void GridView1_RowEditing( object sender, GridViewEditEventArgs e) { GridView1.SelectedIndex = e.NewEditIndex; }
When the Edit link is clicked on the GridView object, this code assigns the index of the line being edited to the SelectedIndex property of the GridView object. This results in the edited row being selected, as shown in Figure 8-10.
Figure 8-10: With a bit of code, the edited row is now selected when the Edit link is clicked.
Declarative markup is the HTML-like source that is inserted into the aspx page when the GridView object is dragged to the Web form. You can see this by clicking the Source tab of the Web form. You can configure the GridView object by typing the declarative markup directly into the body of the HTML source. The following declarative markup represents the GridView object that you created in this chapter.
GridView Object's Declarative Markup
<asp:GridView runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2" DataKeyNames="EmployeeID" DataSource OnRowEditing="GridView1_RowEditing" PageSize="3"> <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" /> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" /> <asp:BoundField DataField="EmployeeID" HeaderText="ID" InsertVisible="False" ReadOnly="True" SortExpression="EmployeeID" /> <asp:BoundField DataField="LastName" HeaderText="Last Name" SortExpression="LastName"> <ControlStyle Width="100px" /> </asp:BoundField> <asp:BoundField DataField="FirstName" HeaderText="First Name" SortExpression="FirstName"> <ControlStyle Width="100px" /> </asp:BoundField> <asp:BoundField DataField="BirthDate" HeaderText="Birth Date" SortExpression="BirthDate"> <ControlStyle Width="150px" /> </asp:BoundField> <asp:BoundField DataField="ReportsTo" HeaderText="Reports To" SortExpression="ReportsTo"> <ControlStyle Width="50px" /> </asp:BoundField> </Columns> <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" /> <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" /> <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" /> <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" /> </asp:GridView>
Notice that the <asp:GridView> tag contains many general settings, such as the data source and GridView style settings. This tag also contains the wiring of events to their event handler methods, such as the RowEditing event that was configured earlier.
Between the <asp:GridView> and </asp:GridView> tags are settings for the rows and columns. The footer, which is a row, has a style setting. After that, you see the columns collection. Finally, you see the other row styles, including the selected row style, the pager style, and the header style.
The columns collection is defined between the <asp:Columns> and </asp:Columns> tags. The first column is a CommandField object, and the rest of the columns are BoundField objects.
When the GridView object was configured, a new SqlDataSource was created called Employee-List. The declarative markup for the SqlDataSource is shown in the following snippet.
SqlDataSource Object's Declarative Markup
<asp:SqlDataSource runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" DeleteCommand="DELETE FROM [Employees] WHERE [EmployeeID] = @original_EmployeeID" InsertCommand="INSERT INTO [Employees] ([LastName], [FirstName], [BirthDate], [ReportsTo]) VALUES (@LastName, @FirstName, @BirthDate, @ReportsTo)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [BirthDate], [ReportsTo] FROM [Employees]" UpdateCommand="UPDATE [Employees] SET [LastName] = @LastName, [FirstName] = @FirstName, [BirthDate] = @BirthDate, [ReportsTo] = @ReportsTo WHERE [EmployeeID] = @original_EmployeeID"> <DeleteParameters> <asp:Parameter Name="original_EmployeeID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="LastName" Type="String" /> <asp:Parameter Name="FirstName" Type="String" /> <asp:Parameter Name="BirthDate" Type="DateTime" /> <asp:Parameter Name="ReportsTo" Type="Int32" /> <asp:Parameter Name="original_EmployeeID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="LastName" Type="String" /> <asp:Parameter Name="FirstName" Type="String" /> <asp:Parameter Name="BirthDate" Type="DateTime" /> <asp:Parameter Name="ReportsTo" Type="Int32" /> </InsertParameters> </asp:SqlDataSource>
Notice that the <asp:SqlDataSource> tag contains the connection string and the SQL command information. Between the <asp:SqlDataSource> and </asp:SqlDataSource> tags are the parameters for the commands, each represented as a collection of parameters for each command.
You have seen how to drag and drop the GridView object onto a Web form. You have also seen how you can create the GridView object by typing the declarative markup into the source window of the Web form. You can also create the GridView object at runtime in the code-behind file (as you can all other Web parts). In the following example, you add a new Web form to the project called Default2.aspx and add the following code to the code-behind page to create the GridView object and the SqlDataSource object. Note that you don't add anything to the Default.aspx file.
Visual Basic
Imports System.Drawing Partial Class Default2 Inherits System.Web.UI.Page Dim WithEvents GridView1 As New GridView() Dim EmployeeList As New SqlDataSource() Protected Sub Page_Init(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Init With GridView1 .ID = "GridView1" .AllowPaging = True .AllowSorting = True .AutoGenerateColumns = False .BackColor = Color.FromArgb(&HDE, &HBA, &H84) .BorderColor = Color.FromArgb(&HDE, &HBA, &H84) .BorderStyle = BorderStyle.None .BorderWidth = New Unit("1px") .CellPadding = 3 .CellSpacing = 3 .DataKeyNames = New String() {"EmployeeID"} .DataSourceID = "EmployeeList" .PageSize = 3 .RowStyle.BackColor = Color.FromArgb(&hFF, &hF7, &hE7) .RowStyle.ForeColor = Color.FromArgb(&h8C, &h45, &h10) .SelectedRowStyle.BackColor = Color.FromArgb(&h73, &h8A, &h9C) .SelectedRowStyle.Font.Bold = True .SelectedRowStyle.ForeColor = Color.White .PagerStyle.ForeColor = Color.FromArgb(&h8C, &h45, &h10) .PagerStyle.HorizontalAlign = HorizontalAlign.Center .HeaderStyle.BackColor = Color.FromArgb(&hA5, &h51, &h29) .HeaderStyle.Font.Bold=True .HeaderStyle.ForeColor = Color.White AddHandler .RowEditing, _ New GridViewEditEventHandler(AddressOf GridView1_RowEditing) Dim cmdColumn As New CommandField() With cmdColumn .ShowDeleteButton = True .ShowEditButton = True .ShowSelectButton = True End With .Columns.Add(cmdColumn) Dim bndColumn As New BoundField() With bndColumn .DataField = "EmployeeID" .HeaderText = "ID" .InsertVisible = False .ReadOnly = True .SortExpression = "EmployeeID" End With .Columns.Add(bndColumn) bndColumn = New BoundField() With bndColumn .DataField = "LastName" .HeaderText = "Last Name" .SortExpression = "LastName" .ControlStyle.Width = New Unit("100px") End With .Columns.Add(bndColumn) bndColumn = New BoundField() With bndColumn .DataField = "FirstName" .HeaderText = "First Name" .SortExpression = "FirstName" End With .Columns.Add(bndColumn) bndColumn = New BoundField() With bndColumn .DataField = "BirthDate" .HeaderText = "Birth Date" .SortExpression = "BirthDate" .ControlStyle.Width = New Unit("150px") End With .Columns.Add(bndColumn) bndColumn = New BoundField() With bndColumn .DataField = "ReportsTo" .HeaderText = "Reports To" .SortExpression = "ReportsTo" .ControlStyle.Width = New Unit("50px") End With .Columns.Add(bndColumn) End With With EmployeeList .ID = "EmployeeList" .ConnectionString = _ ConfigurationManager.ConnectionStrings("ConnectionString").ToString() .SelectCommand = "SELECT [EmployeeID], [LastName], [FirstName], " _ + "[BirthDate], [ReportsTo] FROM [Employees]" .InsertCommand = "INSERT INTO [Employees] ([LastName], " _ + "[FirstName], [BirthDate], [ReportsTo]) " _ + "VALUES (@LastName, @FirstName, @BirthDate, @ReportsTo)" .UpdateCommand = "UPDATE [Employees] SET [LastName] = @LastName, " _ + "[FirstName] = @FirstName, [BirthDate] = @BirthDate, " _ + "[ReportsTo] = @ReportsTo " _ + "WHERE [EmployeeID] = @original_EmployeeID" .DeleteCommand = "DELETE FROM [Employees] " _ + "WHERE [EmployeeID] = @original_EmployeeID" .OldValuesParameterFormatString = "original_{0}" .DeleteParameters.Add( _ New Parameter("original_EmployeeID",System.TypeCode.Int32)) .UpdateParameters.Add( _ New Parameter("LastName",System.TypeCode.String)) .UpdateParameters.Add( _ New Parameter("FirstName",System.TypeCode.String)) .UpdateParameters.Add( _ New Parameter("BirthDate",System.TypeCode.DateTime)) .UpdateParameters.Add( _ New Parameter("ReportsTo",System.TypeCode.Int32)) .UpdateParameters.Add( _ New Parameter("original_EmployeeID",System.TypeCode.Int32)) .InsertParameters.Add( _ New Parameter("LastName",System.TypeCode.String)) .InsertParameters.Add( _ New Parameter("FirstName",System.TypeCode.String)) .InsertParameters.Add( _ New Parameter("BirthDate",System.TypeCode.DateTime)) .InsertParameters.Add( _ New Parameter("ReportsTo",System.TypeCode.Int32)) End With Dim frm As Control = Me.FindControl("form1") frm.Controls.Add(EmployeeList) frm.Controls.Add(GridView1) End Sub Protected Sub GridView1_RowEditing(_ ByVal sender As Object, _ ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) _ Handles GridView1.RowEditing GridView1.SelectedIndex = e.NewEditIndex End Sub End Class
C#
using System.Web.UI.WebControls; using System.Drawing; using System.Configuration; using System.Web.UI; public partial class Default2 : System.Web.UI.Page { GridView GridView1 = new GridView(); SqlDataSource EmployeeList = new SqlDataSource(); protected void Page_Init(object sender, System.EventArgs e) { GridView1.ID = "GridView1"; GridView1.AllowPaging = true; GridView1.AllowSorting = true; GridView1.AutoGenerateColumns = false; GridView1.BackColor = Color.FromArgb(0xDE, 0xBA, 0x84); GridView1.BorderColor = Color.FromArgb(0xDE, 0xBA, 0x84); GridView1.BorderStyle = BorderStyle.None; GridView1.BorderWidth = new Unit("1px"); GridView1.CellPadding = 3; GridView1.CellSpacing = 3; GridView1.DataKeyNames = new string[] { "EmployeeID" }; GridView1.DataSourceID = "EmployeeList"; GridView1.PageSize = 3; GridView1.RowStyle.BackColor = Color.FromArgb(0xFF, 0xF7, 0xE7); GridView1.RowStyle.ForeColor = Color.FromArgb(0x8C, 0x45, 0x10); GridView1.SelectedRowStyle.BackColor = Color.FromArgb(0x73, 0x8A, 0x9C); GridView1.SelectedRowStyle.Font.Bold = true; GridView1.SelectedRowStyle.ForeColor = Color.White; GridView1.PagerStyle.ForeColor = Color.FromArgb(0x8C, 0x45, 0x10); GridView1.PagerStyle.HorizontalAlign = HorizontalAlign.Center; GridView1.HeaderStyle.BackColor = Color.FromArgb(0xA5, 0x51, 0x29); GridView1.HeaderStyle.Font.Bold = true; GridView1.HeaderStyle.ForeColor = Color.White; GridView1.RowEditing += new GridViewEditEventHandler(GridView1_RowEditing); CommandField cmdColumn = new CommandField(); cmdColumn.ShowDeleteButton = true; cmdColumn.ShowEditButton = true; cmdColumn.ShowSelectButton = true; GridView1.Columns.Add(cmdColumn); BoundField bndColumn = new BoundField(); bndColumn.DataField = "EmployeeID"; bndColumn.HeaderText = "ID"; bndColumn.InsertVisible = false; bndColumn.ReadOnly = true; bndColumn.SortExpression = "EmployeeID"; GridView1.Columns.Add(bndColumn); bndColumn = new BoundField(); bndColumn.DataField = "LastName"; bndColumn.HeaderText = "Last Name"; bndColumn.SortExpression = "LastName"; bndColumn.ControlStyle.Width = new Unit("100px"); GridView1.Columns.Add(bndColumn); bndColumn = new BoundField(); bndColumn.DataField = "FirstName"; bndColumn.HeaderText = "First Name"; bndColumn.SortExpression = "FirstName"; GridView1.Columns.Add(bndColumn); bndColumn = new BoundField(); bndColumn.DataField = "BirthDate"; bndColumn.HeaderText = "Birth Date"; bndColumn.SortExpression = "BirthDate"; bndColumn.ControlStyle.Width = new Unit("150px"); GridView1.Columns.Add(bndColumn); bndColumn = new BoundField(); bndColumn.DataField = "ReportsTo"; bndColumn.HeaderText = "Reports To"; bndColumn.SortExpression = "ReportsTo"; bndColumn.ControlStyle.Width = new Unit("50px"); GridView1.Columns.Add(bndColumn); EmployeeList.ID = "EmployeeList"; EmployeeList.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString(); EmployeeList.SelectCommand = "SELECT [EmployeeID], [LastName], " + "[FirstName], [BirthDate], [ReportsTo] FROM [Employees]"; EmployeeList.InsertCommand = "INSERT INTO [Employees] ([LastName], " + "[FirstName], [BirthDate], [ReportsTo]) " + "VALUES (@LastName, @FirstName, @BirthDate, @ReportsTo)"; EmployeeList.UpdateCommand = "UPDATE [Employees] " + "SET [LastName] = @LastName, " + "[FirstName] = @FirstName, [BirthDate] = @BirthDate, " + "[ReportsTo] = @ReportsTo " + "WHERE [EmployeeID] = @original_EmployeeID"; EmployeeList.DeleteCommand = "DELETE FROM [Employees] " + "WHERE [EmployeeID] = @original_EmployeeID"; EmployeeList.OldValuesParameterFormatString = "original_{0}"; EmployeeList.DeleteParameters.Add( new Parameter("original_EmployeeID", System.TypeCode.Int32)); EmployeeList.UpdateParameters.Add( new Parameter("LastName", System.TypeCode.String)); EmployeeList.UpdateParameters.Add( new Parameter("FirstName", System.TypeCode.String)); EmployeeList.UpdateParameters.Add( new Parameter("BirthDate", System.TypeCode.DateTime)); EmployeeList.UpdateParameters.Add( new Parameter("ReportsTo", System.TypeCode.Int32)); EmployeeList.UpdateParameters.Add( new Parameter("original_EmployeeID", System.TypeCode.Int32)); EmployeeList.InsertParameters.Add( new Parameter("LastName", System.TypeCode.String)); EmployeeList.InsertParameters.Add( new Parameter("FirstName", System.TypeCode.String)); EmployeeList.InsertParameters.Add( new Parameter("BirthDate", System.TypeCode.DateTime)); EmployeeList.InsertParameters.Add( new Parameter("ReportsTo", System.TypeCode.Int32)); Control frm = this.FindControl("form1"); frm.Controls.Add(EmployeeList); frm.Controls.Add(GridView1); } protected void GridView1_RowEditing( object sender, GridViewEditEventArgs e) { System.Diagnostics.Debug.WriteLine("GridView1_RowEditing"); GridView1.SelectedIndex = e.NewEditIndex; } }
This code looks straightforward: the GridView object named GridView1 and the SqlDataSource object named EmployeeList are created as instance fields. You use the Page_Init event handler to initialize the properties of both objects. Finally, you add the EmployeeList and GridView1 objects to the Web form by placing the objects into the Controls collection. But notice that you can't simply place the objects into the Web form object's Controls collection. Instead, you have to search for the form1 object and put the controls into this object's Controls collection because these objects, which are WebControl objects, must be placed within a form element that is configured with the runat="server" attribute. The name of the default form element is form1, as shown in the following snippet of source HTML from Default2.aspx file.
HTML Source
<html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Untitled Page</title> </head> <body> <form runat="server"> <div> </div> </form> </body> </html>
The GridView object has significantly fewer events than the Windows DataGridView object, but they allow you to accomplish most tasks. (See Table 8-1.)
Event | Description |
---|---|
DataBinding | The GridView object's data binding expressions are to be evaluated. |
DataBound | The GridView object has been data bound. |
Disposed | The GridView object has been disposed. |
Init | The GridView object has been initialized. |
Load | The GridView object has been loaded. |
PageIndexChanged | The current page index on the GridView object has changed. |
PageIndexChanging | The current page index on the GridView object is going to change. |
PreRender | The GridView object is about to be rendered. |
RowCancelingEdit | The Cancel link has been clicked by the user to cancel editing of a row. |
RowCommand | An event has taken place within the row. |
RowCreated | A row has been created. |
RowDataBound | A row has been data bound. |
RowDeleted | The row has been deleted. |
RowDeleting | The Delete link has been clicked by the user and the row is about to be deleted. |
RowEditing | The Edit link has been clicked by the user. |
RowUpdated | The Update link has been clicked by the user and the row has been updated. |
Row Updating | The Update link has been clicked by the user and the row is about to be updated. |
SelectedIndexChanged | The Select link has been clicked by the user and the index of the selected row has been changed. |
SelectedIndexChanging | The Select link has been clicked by the user and the index of the selected row is about to be changed. |
Sorted | The user clicked a column header sort link and the sort has completed. |
Sorting | The user clicked a column header sort link and the sort is about to begin. |
Unloaded | The GridView object has been unloaded. |
You can use any of the GridView object's events by clicking the GridView object and then clicking the event lightning bolt in the Properties window (Figure 8-11). You can add event handler stub code for any of the events by simply double-clicking an event.
Figure 8-11: GridView object events
It's always helpful to understand which events will be triggered when a Web page is loaded. The following GridView events take place when this sample project is run, which causes the Default2.aspx page to be loaded and sent to the browser.
Init
Load
DataBinding
RowCreated
RowDataBound
RowCreated
RowDataBound
RowCreated
RowDataBound
RowCreated
RowDataBound
RowCreated
RowDataBound
RowCreated
RowDataBound
DataBound
PreRender
Unload
Notice that single events fire for Init, Load, and DataBinding. After that, RowCreated and Row-DataBound events fire for each row that is being created. Why did these events fire six times when only three rows are being displayed per page? In addition to the three rows of data that are being displayed, this pair of events fires for the header row, the footer row, and the pager row. These events give you access to the current row and its Cells collection. Be sure to use the RowDataBound event if you need to access the data in the cells, because the data is not yet available in the RowCreated event.
The last events to fire are the DataBound, PreRender, and Unload events. All of the data in the GridView object is available when the DataBound event fires. The PreRender event can be used for "last-minute" style changes based on business rules. This is the last event that fires before the GridView object is displayed. The UnLoad event can be used to clean up resources that are no longer required.
After the GridView object has been displayed, if the user clicks one of the links on a row, such as Edit, the following events are fired.
Init
RowCreated
RowCreated
RowCreated
RowCreated
RowCreated
RowCreated
Load
RowCommand
SelectedIndexChanging
SelectedIndexChanged
PreRender
Unload
Notice that the RowCreated events for all of the rows fire before the Load event. This means that the row objects are created so you can access them in the Load event handler. The Row-Command event fires for any of the row events, which allows you to examine the event and act on it. The SelectedIndexChanging event fires before the selected index is changed; the event arguments contain a Cancel property that can be set to true, which aborts the changing of the selected index. The SelectedIndexChanged event fires after the selected index has changed.
The GridView object has a Columns collection, which contains objects that inherit from the DataControlField class. The .NET Framework contains several column types, and you can also create your own column types. Figure 8-6, shown earlier, depicts the DataControlField class hierarchy, which shows the available column types.
The Visual Studio GridView designer lets you add and remove columns by choosing Edit Columns in the GridView Task window, which displays the Fields dialog box (Figure 8-12). This dialog box is also used to access the properties of each column. For example, you can configure the first column, which is a CommandField object, to show a header, and you can set the header text.
Figure 8-12: Choosing Edit Columns in the GridView Task menu displays the Fields dialog box, where you can add and remove columns as well as configure the column style and behavior.
You can also populate the columns collection at runtime using your own code, as shown previously in this chapter.
You'll often want a column with different behavior than the available column types provide. For example, the Employees table contains a ReportsTo field, which contains the ID of the employee's boss. Wouldn't it be better to display the boss's name? In fact, wouldn't it be nice to be able to select the boss's name from a DropDownList object when the employee is in edit mode?
You could create a new column type by inheriting from the DataControlField class or one of its subclasses. This would work if you were creating the columns through code, but the GridView designer would not recognize your new column. A simpler approach is to use a template column. By default, a template column has no user interface it simply provides the framework that the GridView object needs to create repeating rows. You have the flexibility of placing any declarative markup within each cell, which means you can include HTML, DHTML, server controls, and ASP.NET data binding code in the template.
You could create the template column by selecting the TemplateField object from the Available Fields list when you add columns to the GridView object. You can also create a template column by selecting an existing column and clicking the Convert This Field Into A TemplateField link, as shown in Figure 8-13.
Figure 8-13: Click the Convert This Field Into A TemplateField link to create a template column that can be modified.
After a field has been converted to a template, you can edit the template by clicking the Edit Templates link in the GridView Tasks window. The TemplateField object can consist of any of the following templates.
HeaderTemplate Renders as a cell at the top of the column.
FooterTemplate Renders as a cell at the bottom of the column.
ItemTemplate Repetitively renders cells for each row of data.
AlternatingItemTemplate Repetitively renders cells for every other row of data.
EditItemTemplate Renders for a cell that is in edit mode.
All of these templates are optional, but remember that if you don't supply an ItemTemplate, nothing will be rendered in the data cells. The good news is that when you convert a field to a template, the ItemTemplate will contain a default value. There is more information on modifying the template later. In addition to the preceding templates for a TemplateField object, you can provide the following templates for a GridView object.
EmptyDataTemplate Renders instead of the GridView object where there is no data to be displayed. Previous versions of Web-based data grid controls were simply invisible when there was no data, which sometimes caused confusion because you didn't know if there was an error on the page or if there simply wasn't any data to be displayed.
PagerTemplate Renders when you have paging enabled.
Before you convert the ReportsTo column to a TemplateField column that will normally display the boss's name but that in edit mode will provide a DropDownList object that you can use to select the boss, you need to set up one thing: a new SqlDataSource object that will contain the data you will use in this column. Drag a new SqlDataSource object to the Web form. Change its ID property to ReportsToList.
Note | This conversion will be done on the Default.aspx page, so you should verify that Default.aspx is set as your startup page. |
Configure the data source by selecting the same connection string that the EmployeeList has, which was called ConnectionString. Instead of selecting the Employees table with its fields, select to use a custom query because the ReportsTo field can have a null value, and we want this to be displayed as "[Unassigned]". Use the following query to get the list of employee IDs and names, along with an extra row for unassigned.
ReportsToList Select Query
SELECT EmployeeID, LastName + ', ' + FirstName as Name FROM EMPLOYEES UNION SELECT null,'[Unassigned]' ORDER BY Name ASC
Notice that this query returns only two columns: ID and Name. The name column comprises the last name, a comma and space, and then the first name. This format will work nicely in the Label object or DropDownList object that will be displayed. Because this list is read-only, there is no need for an insert, update, or delete query. On the next screen, you can test the query to see if you get the expected results.
OK, it's time to convert the ReportsTo column to a TemplateField column. Select the ReportsTo column in the Selected Fields list of the Fields dialog box, click the link shown earlier in Figure 8-13 to perform the conversion, and then click OK. The column is converted and you see the GridView Tasks window, which has a link at the bottom called Edit Templates. Click the link to see the available templates. Figure 8-14 shows the ItemTemplate for the ReportsTo column; it contains a simple Label control, but notice that the Display drop-down list includes all templates that can be edited.
Figure 8-14: The templates that can be edited in this GridView object
If you click the Label object's smart tag, you will see that you can edit the data bindings for this control. The Label control has its Text property bound to the ReportsTo field. Instead of binding directly to the ReportsTo field, this expression will be replaced with a Custom Binding Code Expression that will pass the current ID of the boss to a method that you will use to look up the boss's name in the ReportsToList data source. Replace the binding with the following code snippet (which works with Visual Basic and C#), as shown in Figure 8-15.
Figure 8-15: The data bindings can be edited for each control.
GetReportsToName(Eval("ReportsTo"))
Next you'll create the GetReportsToName method, which can be added to the code-behind page.
Visual Basic
Public Function GetReportsToName(ByVal reportsToId As Object) As String For Each row As DataRowView In _ ReportsToList.Select(DataSourceSelectArguments.Empty) If (reportsToId.Equals(row("EmployeeID"))) Then Return row("Name").ToString() End If Next Throw New ArgumentException("Employee with ID: " _ + reportsToId.ToString() + " does not exist.") End Function
C#
public string GetReportsToName(object reportsToId) { foreach(DataRowView row in ReportsToList.Select(DataSourceSelectArguments.Empty)) { if (reportsToId.Equals(row["EmployeeID"])) { return row["Name"].ToString(); } } throw new ArgumentException("Employee with ID: " + reportsToId.ToString() + " does not exist."); }
This code executes the Select statement in the data source, which does not require any parameters. Next the code loops through the list of employees until an employee with a matching ID is found. If no match is found, an ArgumentException is thrown.
If you build and run the project, you will find that the bosses' names now appear instead of their IDs. Also notice that Andrew Fuller has no boss, so this shows as unassigned. What happens if you click the Edit link on one of the employees? Figure 8-16 shows that the editing template is still configured to allow you to edit the ReportsTo based on the ID number. This must be changed to a drop-down list of employee names.
Figure 8-16: The list of employees now shows the boss's name, except when the Edit link is clicked.
If you look at the EditItemTemplate for the ReportsTo field, you will see that this template contains a TextBox control that is also bound to the ReportsTo field. You can see that this is the template that will be rendered for a cell that is in a row that is being edited. Delete the TextBox control and add a DropDownList control. Resize the DropDownList object to 150 pixels to make room for the name to be displayed.
Click the Choose DataSource link in the DropDownList Tasks window to connect the DropDownList to the ReportsToList data source that you created. Select the Name field to be displayed and select the EmployeeID field as the value field, as shown in Figure 8-17.
Figure 8-17: Set the DataSource of the DropDownList to the ReportsToList that you previously created, and configure the field to be displayed and the field to be used as the value.
Click the Edit DataBindings link and set the SelectedValue property to the ReportsTo field, as shown in Figure 8-18. If the Field Binding option is disabled, you might need to click the Refresh Schema link at the bottom of the form. Binding the value to this field causes the ReportsTo field to update with the correct ID when a different employee is selected. Further, when the cell is edited, it gets the proper name based on the current ReportsTo value.
Figure 8-18: Binding the SelectedValue property of the DropDownList object to the ReportsTo field
If you build and run the project, you will see that when you edit an employee, the ReportsTo cell becomes a DropDownList object that is populated with the employee names and the correct boss is displayed. Figure 8-19 shows the browser window with the completed conversion of a BoundField column to a TemplateField column. Notice that you can set any employee's boss to unassigned, which puts a null value into the ReportsTo field.
Figure 8-19: The completed conversion of a BoundField column to a TemplateField column
Displaying a Calendar Control for the BirthDate You have seen how to convert a BoundField column to a TemplateField column and how to use a DropDownList to display the employee names from a different data source. You can also convert the BirthDate field to use a Calendar control when in edit mode. You simply convert the BirthDate column from a BoundField to a TemplateField, and then replace the TextBox in the EditItemTemplate with a Calendar control.
In the data binding properties, you must set up two bindings. The first item to bind is the SelectedDate to the BirthDate field. Be sure that the Two-Way Databinding option is selected, to ensure that the BirthDate is updated when a new date is selected. The second item to bind is the VisibleDate to the BirthDate field. The Two-Way Databinding option should not be selected because the Calendar control will simply read the BirthDate into the VisibleDate property to ensure that the BirthDate is initially displayed when the row is edited.
Before running the project, you might also want to change the format of the Label control when the birth date is displayed normally (that is, not in edit mode). Edit the ItemTemplate, and edit the data bindings for the Label control. The Text property is bound to the BirthDate field; set its Format property to Short date - {0:d}.
If you build and run the project, you will see that the birth dates that are shown in a Label control when not editing appear with the correct formatting, but if a row is edited, you will be prompted with a Calendar control for selecting the date (Figure 8-20).
Figure 8-20: The BirthDate field has been converted to a TemplateField, and a Calendar control is displayed when a row is edited.
The ImageField column can be used to display images and icons in the GridView object. The ImageField renders as an HTML <IMG> element in each cell, and you must provide the URL to each image that is to be displayed. Theoretically, you could bind to the PhotoPath field in the Employees table, but if you look at the URLs that are currently in the PhotoPath field, you will see that they point to a location that is not available. For example, Nancy Davolio's photo is located at the following URL, which is a LAN address.
http://accweb/employees/davolio.bmp
If you want to change the URLs and use this field, add the PhotoPath field to your EmployeeList Select statement and bind the PhotoPath field to the DataImageUrlField property, and you're done.
But how can you bind to the images that are embedded in the Photo field of the Employees table? This is a bit more challenging, but before I cover this, you need a clear understanding of the <IMG> element.
A user-requested Web page typically contains HTML content, which might also include <IMG src="/books/1/379/1/html/2/MyImage1.gif"> elements. When the browser encounters the image element, it knows that the content isn't included in the Web page being received; it's in a different file, which must be retrieved in order to display the image. Figure 8-21 shows an example of the requests and responses between the browser and the Web server.
Figure 8-21: The typical communication between the browser and Web server, which is accomplished by a series of requests from the browser and responses from the server
As discussed earlier, the source (SRC) of the image element is typically a file, such as MyImage1.gif, but it doesn't have to be a file. The source can be set to a handler that locates the requested image and responds with the image. You can create a simple handler by adding another Web form to the project that has no HTML content, but the code-behind page retrieves the image from the Photo field in the Employees table and places the image into the response stream to the browser. You might think of this page as a sort of "image engine" for this project. The query string can contain the employee's ID of the image to be retrieved, and the image engine will locate and respond with the correct image, as shown in Figure 8-22.
Figure 8-22: In this scenario, the URLs of the images are the same, but the QueryString is different for each image.
Creating the image engine has many advantages, which become more apparent as more graphics are added to the site. The advantages include:
Logging All requests for images can be logged. This can be beneficial when the image is of an advertisement and you want to record how many times each advertisement is displayed.
Sizing All images can be sized the same, usually to a thumbnail, and the browser will display the smaller image. Downsizing lowers the bandwidth requirements, but you can also add links to see the full-size image.
Storage The images don't have to be stored on the file system. If they are stored in the database, the database can be easily moved as a unit, ensuring a consistent backup. Think of the situation in which the PhotoPath field points to a URL that is not accessible. Storing the image in the database solves this problem.
Building the Image Engine You should note a couple of things about the images stored in the Photo field of the Employees table. First, the images are in BMP format, so they are not considered compatible with the Web, which has standardized on formats such as GIF and JPG. The images must therefore be converted before they are sent to the browser. Second, the images were embedded into the database using Microsoft Access, which placed an OLE header on these files. This header occupies the first 78 bytes. The header must be stripped off before the image is usable. The image engine can handle both these problems.
You can build an image engine by adding a Web form to the project, called GetImage.aspx. In the code-behind page, add the following code.
Visual Basic
Imports System.IO Imports System.Drawing Imports System.Drawing.Imaging Imports System.Data.SqlClient Partial Class GetImage Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Load Const oleOffset As Integer = 78 Const oleTypeStart As Integer = 20 Const oleTypeLength As Integer = 12 Dim EmployeeID As String Dim sql As String Dim bmp As Bitmap Dim imageBytes() As Byte EmployeeID = CType(Me.Request.QueryString("EmployeeID"), String) If (EmployeeID Is Nothing) Then Return 'eliminate injection threat EmployeeID = Integer.Parse(EmployeeID).ToString() sql = "Select Photo from Employees where EmployeeConnectionString") Using cn As New SqlConnection(cnSettings.ConnectionString) Using cmd As New SqlCommand(sql, cn) cn.Open() Using dr As SqlDataReader = cmd.ExecuteReader() dr.Read() imageBytes = CType(dr.GetValue(0), Byte()) End Using End Using End Using If (imageBytes Is Nothing Or imageBytes.Length = 0) Then Return Dim tempStream As MemoryStream Dim type As String = System.Text.Encoding.ASCII.GetString( _ imageBytes, oleTypeStart, oleTypeLength) If type = "Bitmap Image" Then tempStream = New MemoryStream(imageBytes, oleOffset, _ imageBytes.Length - oleOffset) Else tempStream = New MemoryStream(imageBytes, 0, _ imageBytes.Length) End If bmp = New Bitmap(tempStream) 'if you want to resize the photos, uncomment next line and tweak... 'bmp = New Bitmap(bmp, bmp.Height / 2, bmp.Width / 2) Response.ContentType = "image/gif" bmp.Save(Response.OutputStream, ImageFormat.Gif) Response.End() End Sub End Class
C#
using System; using System.Configuration; using System.Web; using System.Drawing; using System.Data.SqlClient; using System.IO; using System.Drawing.Imaging; public partial class GetImage : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { const int oleOffset = 78; const int oleTypeStart = 20; const int oleTypeLength = 12; string EmployeeID; string sql; Bitmap bmp; byte[] imageBytes; EmployeeID = (string)this.Request.QueryString["EmployeeID"]; if (EmployeeID == null) return; //eliminate injection threat EmployeeID = int.Parse(EmployeeID).ToString(); sql = "Select Photo from Employees where EmployeeConnectionString"]; using (SqlConnection cn = new SqlConnection(cnSettings.ConnectionString)) { using (SqlCommand cmd = new SqlCommand(sql, cn)) { cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { dr.Read(); imageBytes = (byte[])dr.GetValue(0); } } } if (imageBytes == null || imageBytes.Length == 0) return; MemoryStream tempStream; string type = System.Text.Encoding.ASCII.GetString( imageBytes, oleTypeStart, oleTypeLength); if (type == "Bitmap Image") { tempStream = new MemoryStream( imageBytes, oleOffset, imageBytes.Length - oleOffset); } else { tempStream = new MemoryStream( imageBytes, 0, imageBytes.Length); } bmp = new Bitmap(tempStream); //if you want to resize the photos, uncomment next line and tweak... //bmp = new Bitmap(bmp, bmp.Height / 2, bmp.Width / 2); Response.ContentType = "image/gif"; bmp.Save(Response.OutputStream, ImageFormat.Gif); Response.End(); } }
This code retrieves the EmployeeID from the QueryString. In some cases, the EmployeeID might be null or nothing, so a check is made to see if the code should continue.
Next, to make sure the ID does not have any invalid characters, we parse (convert) the ID to a numeric value and then convert it back to a string. If the ID has non-numeric characters, an exception is thrown. This also eliminates potential SQL injection attacks, which are caused when a hacker adds malicious code after the ID number that is showing in the browser's address.
You then use the ID to create a SQL query that will retrieve the Photo that matches the employee's ID. A connection is opened, and the command is executed, returning a byte array containing the image bytes.
Next we convert the byte array to a MemoryStream object and do a test to see if an OLE header exists by looking at the bytes, starting at offset 20, to see if they contain "Bitmap Image". If so, we strip off the OLE header by specifying that the stream start reading the byte array at offset 78.
The MemoryStream object can be passed into the Bitmap object's constructor, and the image is finally rendered in memory. Notice the comment on the subsequent line that shows how you can resize the bitmap as well.
Finally, you save the Bitmap to the Response object's OutputStream. By setting the content type to image/gif, the Bitmap object is converted to GIF format. Then you close the Response object's OutputStream.
Adding the ImageField Column to Display the Photos Now that you have done the prep work, you can add the ImageField column to the GridView object. Add the ImageField column and set its properties as shown in the following table.
ImageField Property | Setting |
---|---|
HeaderText | Photo |
DataImageUrlField | EmployeeID |
DataImageUrlFormatString | GetImage.aspx?EmployeeID={0} |
Notice that the binding is set to the EmployeeID because that is what is being used to retrieve the photo. The EmployeeID is fed into the format string, replacing the placeholder. After you set the properties, build and run the project. You should see the photos of each employee as shown in Figure 8-23.
Figure 8-23: Retrieving the photos from the Photo field in the Employees table by using the GetImage.aspx Web form
Uploading New Employee Photos You have seen how to add the ImageField to the Grid-View object, but if you attempt to edit one of the rows, you will see a TextBox that contains the EmployeeID value. This serves little purpose. One option is to set the ReadOnly property of the ImageField to true, which causes the employee's photo to be displayed while you are in edit mode. A better option is to convert the ImageField to a TemplateField and place a FileUpload control into the EditItemTemplate; this gives you the ability to upload new photos. Let's do that.
First convert the ImageField to a TemplateField. Remove the TextBox control from the EditItemTemplate and add a FileUpload control. Change the ID property of the FileUpload control from FileUpload1 to PhotoUpload. Changing the ID property makes it easy to access the control later. Figure 8-24 shows what the FileUpload control looks like when it's rendered.
Figure 8-24: You can use the FileUpload control either to type the filename or to locate the file to be uploaded by clicking the Browse button.
Next add code to handle the uploaded file. When the file is uploaded to the Web server, it becomes available as a stream that can be sent directly to the database. This requires a bit of code, some of which is placed into the RowUpdated event handler; the rest is placed into a new method called UpdatePhoto. Create the RowUpdated event handler, and add the following code.
Visual Basic
Protected Sub GridView1_RowUpdated(ByVal sender As Object, _ ByVal e As System.Web.UI.WebControls.GridViewUpdatedEventArgs) _ Handles GridView1.RowUpdated Dim gridView As GridView = CType(sender, GridView) Dim employeeId As Integer = e.Keys("EmployeeID") Dim photoFile As FileUpload = _ CType(gridView.Rows(gridView.EditIndex).FindControl("PhotoUpload"), _ FileUpload) UpdatePhoto(employeeId, photoFile) End Sub Public Sub UpdatePhoto(ByVal employeeId As Integer, _ ByVal photoFile As FileUpload) If (photoFile Is Nothing) Or (photoFile.HasFile = False) Then Return Dim imageStream As Stream = photoFile.PostedFile.InputStream Dim imageLength As Integer = photoFile.PostedFile.ContentLength Dim imageType As String = photoFile.PostedFile.ContentType Dim mStream As New MemoryStream() Dim imageData(1024) As Byte Dim count As Integer = 0 count = imageStream.Read(imageData, 0, imageData.Length) While (0 < count) mStream.Write(imageData, 0, count) count = imageStream.Read(imageData, 0, imageData.Length) End While Dim cnSettings As ConnectionStringSettings = _ ConfigurationManager.ConnectionStrings("ConnectionString") Using cn As New SqlConnection(cnSettings.ConnectionString) Using cmd As New SqlCommand() cmd.CommandText = _ "Update Employees Set Photo = @Photo " + _ "WHERE EmployeeID = @EmployeeID" Dim parm As New SqlParameter() parm.ParameterName = "@EmployeeID" parm.Value = employeeId cmd.Parameters.Add(parm) parm = New SqlParameter() parm.ParameterName = "@Photo" parm.Value = mStream.GetBuffer() cmd.Parameters.Add(parm) cmd.Connection = cn cn.Open() cmd.ExecuteNonQuery() End Using End Using End Sub
C#
protected void GridView1_RowUpdated(object sender, GridViewUpdatedEventArgs e) { GridView gridView = ((GridView)sender); int employeeId = (int)e.Keys["EmployeeID"]; FileUpload photoFile = (FileUpload)gridView.Rows[gridView.EditIndex] .FindControl("PhotoUpload"); UpdatePhoto(employeeId, photoFile); } public void UpdatePhoto(int employeeId, FileUpload photoFile) { if (photoFile == null || photoFile.HasFile == false) return; Stream imageStream = photoFile.PostedFile.InputStream; int imageLength = photoFile.PostedFile.ContentLength; string imageType = photoFile.PostedFile.ContentType; MemoryStream mStream = new MemoryStream(); byte[] imageData = new byte[1024]; int count = 0; while (0 < (count = imageStream.Read(imageData, 0, imageData.Length))) { mStream.Write(imageData, 0, count); } ConnectionStringSettings cnSettings = ConfigurationManager.ConnectionStrings["ConnectionString"]; using (SqlConnection cn = new SqlConnection(cnSettings.ConnectionString)) { using (SqlCommand cmd = new SqlCommand()) { cmd.CommandText = "Update Employees Set Photo = @Photo " + "WHERE EmployeeID = @EmployeeID"; SqlParameter parm = new SqlParameter(); parm.ParameterName = "@EmployeeID"; parm.Value = employeeId; cmd.Parameters.Add(parm); parm = new SqlParameter(); parm.ParameterName = "@Photo"; parm.Value = mStream.GetBuffer(); cmd.Parameters.Add(parm); cmd.Connection = cn; cn.Open(); cmd.ExecuteNonQuery(); } } }
This RowUpdated event handler simply retrieves the current EmployeeID and the FileUpload control and passes them to the UpdatePhoto method. You use the FindControl method to get the FileUpoad control by its ID, which is PhotoUpload, because the only way you can access the row's Cell collection is by index number, and if another column were inserted ahead of the Photo column, the index number would need to be updated.
The UpdatePhoto method tests the FileUpload control to verify that it is not null or nothing and that it has a file. A loop is then executed to retrieve the contents of the file from the network stream. The contents are placed into a MemoryStream object. Finally, a SQL update command is executed, which updates the Photo field based on the EmployeeID. If you build and run the code, you will see the FileUpload control when you are in edit mode. Find a picture to upload, and click the Update link on the row. The result might look something like that shown in Figure 8-25.
Figure 8-25: The FileUpload control lets you upload a new photo that will be placed into the database.