Using a DataGrid Control to Access a Database

Using a DataGrid Control to Access a Database

A DataGrid allows you to access rows in a database table. In the following sections, you'll learn how to create an ASP.NET Web application that uses a DataGrid control to access the rows in a database table. The DataGrid you'll create will display the rows from the Products table of the Northwind database.

Creating the Web Application

Perform the following steps:

  1. To create the new project, select File New Project in VS .NET. Select Visual C# Projects from the Project Types area on the left of the New Project dialog box, and select ASP.NET Web Application from the Templates area on the right. Enter http://localhost/DataGrid-WebApplication in the Location field. Click OK to continue. Your new project will contain a blank form.

  2. Next, you'll add a DataGrid control to your form. To do this, select the DataGrid from the Toolbox and drag it to your form. Figure 15.7 shows the form with the DataGrid.

    click to expand
    Figure 15.7: Form with a DataGrid

  3. Next, you'll add a SqlConnection object and a SqlDataAdapter object to your form. To add these objects, select the Products table in Server Explorer and drag it to your form. (Adding a SqlConnection object to a form was discussed in Chapter 6, "Introducing Windows Applications and ADO.NET," and in Chapter 7, "Connecting to a Database.")

    Note 

    To display Server Explorer, select View Server Explorer, or press Ctrl+Alt+S on your keyboard.

  4. After you drag the Products table to your form, VS .NET creates a SqlConnection object named sqlConnection1 and a SqlDataAdapter object named sqlDataAdapter1. Click your sqlConnection1 object to display the properties for this object in the Properties window. To enable sqlConnection1 to access the database, you need to set the password for the connection. To do this, you need to add a substring containing pwd to the ConnectionString property of sqlConnection1. Add pwd=sa; to the ConnectionString property.

    Note 

    If you don't have the password for the sa user, you'll need to get it from your database administrator.

  5. Next, you'll modify the SQL SELECT statement used to retrieve the rows from the Products table. Click the sqlDataAdapter1 object to display the properties for this object. Click the addition icon to the left of the SelectCommand property to display the dynamic properties. One of the dynamic properties is the CommandText property, which contains the SELECT statement.

  6. Click CommandText and then click the ellipsis button to display the Query Builder. You use Query Builder to define SQL statements. You can type in the SQL statement, or you can build it visually. Uncheck all the columns except the following: ProductID, ProductName, QuantityPerUnit, and UnitPrice. This results in the SQL SELECT statement being set to the following:

     SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice FROM Products 

  7. Click the OK button to save your SELECT statement and close Query Builder.

Next, you need to create a DataSet object. You use a DataSet object to store a local copy of the information stored in the database. A DataSet object can represent database structures such as tables, rows, and columns. In the example in this section, you'll use a DataSet object to store the rows from the Products table.

  1. Click an area of your form outside the DataGrid. Next, click the Generate Dataset link near the bottom of the Properties window. This displays the Generate Dataset dialog box. Select the New radio button and make sure the text field to the right of this radio button contains DataSet1. Also, make sure the Add This Dataset To The Designer checkbox is checked. Click the OK button to continue. This adds a new DataSet object named dataSet11 to your form.

  2. Next, you'll need to set the DataSource property of your DataGrid to your DataSet object. This sets the source of the data for your DataGrid and allows the rows from your DataSet to be displayed in your DataGrid. To set the DataSource property, click your DataGrid object and set the DataSource property to dataSet11. Also, set the DataMember property to Products; this is the table with rows that are to be displayed by your DataGrid.

  3. Next, you'll need to add code to populate sqlDataAdapter1 with the rows retrieved by your SELECT statement. Typically, the best place to place this code is in the Page_Load() method of your form. The Page_Load() method is called when the Web page containing your form is initially loaded or refreshed. The IsPostBack property of a page is false the first time the page is loaded and true when the submit button of a form is pressed. For performance, you'll generally want to retrieve rows only when the IsPostBack property is false; otherwise you might needlessly reload the rows from the database. To view the code for your form, open the code for your form by selecting View Code or by pressing F7 on your keyboard. Set your Page_Load() method to the following:

     private void Page_Load(object sender, System.EventArgs e) {   // Put user code to initialize the page here   if (!this.IsPostBack)   {     sqlDataAdapter1.Fill(dataSet11, "Products");     this.DataBind();   } } 

The Fill() method retrieves the rows from the Products table and populates dataSet11 with those rows. The DataBind() method then fills the Products DataTable in dataSet11 with the rows retrieved from the Products table. This causes the rows to be displayed in the DataGrid of your form.

To run your form, select Debug Start Without Debugging, or press Ctl+F5 on your keyboard (see Figure 15.8).

click to expand
Figure 15.8: The running form

As you can see, a vertical scroll bar is displayed because of the number of rows retrieved from the Products table. In the next section, you'll learn how to customize your DataGrid. You'll see how you can control the number of rows displayed in your DataGrid so that no scroll bar appears, as well as control other aspects of your DataGrid.

Customizing the DataGrid

You customize your DataGrid by first selecting the DataGrid control and then clicking the Property Builder link at the bottom of the Properties window. This displays the Properties dialog box for your DataGrid. The Properties dialog box is divided into five areas: General, Columns, Paging, Format, and Borders.

General Properties

You use the General properties to set the data source for your DataGrid and whether you want a header and footer to displayed, among other properties. Set your General properties as shown in Figure 15.9.

click to expand
Figure 15.9: The General properties

The General properties are as follows:

  • DataSource The DataSource is the source of the data for your DataGrid. In this example, the DataSource is dataSet11.

  • DataMember The DataMember is the name of the table to which your DataGrid is bound. In this example, the DataMember is Products.

  • Data Key Field The Data Key Field is the name of a column or expression that is associated with each row in your DataGrid but isn't actually shown. You typically use it to specify the primary key.

  • Header and Footer The header displays the name of the columns at the top of the DataGrid. Select Show Header and Show Footer.

  • Behavior You can sort columns in the header of your DataGrid. Select Allow Sorting so that your columns can be sorted.

Columns Properties

You use the Columns properties to select the columns to be displayed in your DataGrid and the header and footer text to be displayed for each column, among other properties. Click the Columns link of the Properties dialog box and set your Columns properties as shown in Figure 15.10.

click to expand
Figure 15.10: The Columns properties

The Columns properties are as follows:

  • Create Columns Automatically At Run Time The Create Columns Automatically At Run Time check box specifies whether to automatically include all the columns for the DataSet in your DataGrid. When this check box is unselected, you can then set the other properties for each column individually. Unselect this check box.

  • Column List The Column List allows you to select columns from your DataSet for display in your DataGrid. You select columns from the Available Columns area on the left and add them to Selected Columns area on the right using the button containing the right arrow. Select (All Fields) from Available Columns, and add them to the Selected Columns.

  • BoundColumn Properties The BoundColumn properties allow you to set the properties for each column. You select the column you want to set in the Selected Columns area, and then you set the properties for that column. The fields you can set for each column are as follows:

    • Header Text The text you want to display in the header for a column.

    • Footer Text The text you want to display in the footer for a column.

    • Header Image The image you want to display in the header for a column.

    • Sort Expression The column or expression you want to use to sort the column by. Select UnitPrice as the Sort expression.

    • Data Field The name of column.

    • Data Formatting Expression Allows you to format a column value. You can use a formatting expression to format dates and numbers, among others. For example, {0:$##.00} formats a number, adds a dollar sign at the front, and displays two digits after the decimal point; thus, 19 is formatted as $19.00. Set the formatting expression for the UnitPrice column to {0:$##.00}.

Paging Properties

Next, click on the Paging link of the Properties dialog box. Normally, all the rows retrieved by a SELECT statement are displayed on a single page for the DataGrid. You can use the Paging properties to split all the rows into separate pages, with a fixed number of rows on each page in your DataGrid. You can then select the buttons to navigate between these pages of rows. You'll set your page size to five rows with Next and Previous buttons to navigate between the pages of rows. Set your Paging properties as shown in Figure 15.11.

click to expand
Figure 15.11: The Paging properties

The Paging properties are as follows:

  • Allow Paging Controls whether paging is enabled. Check the Allow Paging box.

  • Page Size Controls the number of rows displayed on each page. Set your Page Size to 5.

  • Show Navigation Buttons The Show Navigation Buttons check box controls whether navigation buttons are displayed. These buttons allow you to navigate between pages of rows. Check the Show Navigation Buttons box.

  • Position Allows you to set the position of the navigation buttons. Set the Position to Bottom.

  • Mode Controls the type of navigation buttons displayed. You can use Next and Previous buttons or page numbers to navigate between pages. Set the Mode to Next, Previous Buttons.

  • Next Page Button Text Sets the text displayed on the Next page button. Leave this as > so that a greater-than character (>) is displayed.

  • Previous Page Button Text Sets the text displayed on the Previous page button. Leave this as &lt; so that a less-than character (<) is displayed.

  • Numeric Buttons Control whether numbers are displayed for each page when you set the Mode to Page Numbers. For example, 1 navigates to the first page, 2 to the second page, and so on.

In addition to enabling paging, you'll also need to add some code to your DataGrid to make navigation work, and you'll do this shortly.

Format Properties

Next, click the Format link of the Properties dialog box. You use the Format properties to control how each element on your DataGrid appears. You can set features such as the color of your DataGrid, as well as the font. You can also set the display properties of each column. You'll set the foreground and background color to black and white, respectively. You'll also set the font of the text displayed in your DataGrid to Arial. Set your Format properties as shown in Figure 15.12.

click to expand
Figure 15.12: The Format properties

The Format properties are as follows:

  • Forecolor The Forecolor option specifies the text color. Set the Forecolor to Black.

  • Back Color The Back Color option specifies the color behind the text. Set the Back Color to White.

  • Font Name The Font Name option specifies the font used to display the text. Set the Font Name to Arial.

  • Font Size The Font Size option controls the size of the font used to display the text.

  • Bold, Italic, Underline, Strikeout, Overline The Bold, Italic, Underline, Strikeout, and Overline options control the character formatting for the text.

  • Horizontal Alignment The Horizontal Alignment option specifies the position of the text in the cell.

Borders Properties

Next, click on the Borders link of the Properties dialog box. You use the Borders properties to control the padding, spacing, and appearance of the grid lines in your DataGrid. You'll set the border color of the grid lines in your DataGrid to blue. Set your Borders properties as shown in Figure 15.13.

click to expand
Figure 15.13: The Borders properties

The Borders properties are as follows:

  • Cell Padding Controls the amount of space (in pixels) between the edge of a cell and the cell contents in your DataGrid.

  • Cell Spacing Controls the amount of space (in pixels) between each element in your DataGrid.

  • Grid Lines Specifies the direction of the grid lines in your DataGrid.

  • Border Color Specifies the color of the grid lines in your DataGrid. Set this to blue.

  • Border Width Controls the width and units of the grid lines in your DataGrid.

Once you've set your properties, click the OK button to continue. Next, you'll code the PageIndexChanged() event handler to allow navigation of the rows in your DataGrid.

Coding the PageIndexChanged() Event Handler

As mentioned earlier, in addition to enabling paging in the Paging properties window, you'll also need to add some code to your DataGrid, specifically, to the PageIndexChanged() event handler method. This method is called whenever you change the page in the DataGrid on your running Web page. Before you add the required code, you first select your DataGrid, and then you click the Events button in the Properties window to display the events for your DataGrid, as shown in Figure 15.14.


Figure 15.14: Displaying the DataGrid events

Double-click the PageIndexChanged event and set your DataGrid1_PageIndexChanged() method as follows:

 private void DataGrid1_PageIndexChanged(   object source,   System.Web.UI.WebControls.DataGridPageChangedEventArgs e) {   DataGrid1.CurrentPageIndex = e.NewPageIndex;   sqlDataAdapter1.Fill(dataSet11, "Products");   DataGrid1.DataBind(); } 

The first statement inside the method body is as follows:

 DataGrid1.CurrentPageIndex = e.NewPageIndex; 

This statement sets the current page displayed in DataGrid1 to the new page that is selected using the navigation buttons in the running form. You set the current page for DataGrid1 using the CurrentPageIndex property, and you get the new page from the NewPageIndex property of the DataGridPageChangedEventArgs object. By setting DataGrid1.CurrentPageIndex equal to e.NewPageIndex, the navigation to the new page of rows is performed.

The second statement is as follows:

 sqlDataAdapter1.Fill(dataSet11, "Products"); 

This statement calls the Fill() method of sqlDataAdapter1 to populate dataSet11 with the next set of rows from the Products table.

The third statement is as follows:

 DataGrid1.DataBind(); 

This statement calls the DataBind() method of DataGrid1, causing the new set of rows to be displayed.

Note 

With VS .NET, you can also go to the code view and use the top drop-down menus to create the signature for events. This applies to any events that you add. Of course, double-clicking on events to get the "default" event is easier, but there are other events for each control.

Run your form by pressing Ctrl+F5 on your keyboard. Figure 15.15 shows the running form.

click to expand
Figure 15.15: The running form

Use the navigation buttons to move between pages of rows. Once you've finished running your form, close it and return to the VS .NET form designer. Click the HTML link to view the code for your form. Listing 15.4 shows the WebForm1.aspx file for the form. You'll notice that this file contains a DataGrid control with the appropriate columns.

Listing 15.4: THE WebForm1.aspx FILE

start example
 <%@ Page language="c#" Codebehind="WebForm1.aspx.cs"  AutoEventWireup="false"  Inherits="DataGridWebApplication.WebForm1" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <HTML>   <HEAD>     <title>WebForm1</title>     <meta content="Microsoft Visual Studio 7.0" name="GENERATOR">     <meta content="C#" name="CODE_LANGUAGE">     <meta content="JavaScript" name="vs_defaultClientScript">     <meta content="http://schemas.microsoft.com/intellisense/ie5"      name="vs_targetSchema">   </HEAD>   <body MS_POSITIONING="GridLayout">     <form  method="post" runat="server">       <asp:datagrid id=DataGrid1 style="Z-INDEX: 101; LEFT: 16px;        POSITION: absolute; TOP: 11px" runat="server"        AutoGenerateColumns="False" BorderColor="Blue" Font-Bold="True"        Font-Names="Arial" ForeColor="Black" BackColor="White"        AllowPaging="True" PageSize="5" ShowFooter="True"        DataMember="Products" AllowSorting="True"        DataSource="<%# dataSet11 %>" Height="333px" Width="352px">         <Columns>           <asp:BoundColumn            DataField="ProductID" HeaderText="ProductID">           </asp:BoundColumn>           <asp:BoundColumn DataField="ProductName"            HeaderText="ProductName">           </asp:BoundColumn>           <asp:BoundColumn DataField="QuantityPerUnit"            HeaderText="QuantityPerUnit">           </asp:BoundColumn>           <asp:BoundColumn DataField="UnitPrice"            SortExpression="UnitPrice" HeaderText="UnitPrice"            DataFormatString="{0:$##.00}">           </asp:BoundColumn>         </Columns>       </asp:datagrid></form>   </body> </HTML> 
end example

In the next section, you'll learn how to use a DataList control to access a database.




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net