Using a DataGrid Control to Access a Database

Using a DataGrid Control to Access a Database

In this section, you'll learn how to use a DataGrid control to access the rows in a database table. Follow these steps to create a DataGrid using VS .NET:

  1. First, select File New Project. In the New Project dialog box, select Windows Application, and enter DataGridWindowsApplication in the Name field.

  2. Click OK to continue. Your new project will contain a blank form.

  3. Add a DataGrid control to the form by selecting View Toolbox, selecting a DataGrid, and dragging it to your form. Figure 6.9 shows a form with a DataGrid. Make your DataGrid almost as big as your form by dragging the corners of your DataGrid out to the end of your form.

    click to expand
    Figure 6.9: Form with a DataGrid

Next, you'll add a SqlConnection object and a SqlDataAdapter object to your form.

Note 

You use a SqlConnection object to connect to a SQL Server database, and a SqlDataAdapter object to move rows between SQL Server and a DataSet object. You'll learn the details on how to pull rows from the database into a DataSet in Chapter 10, and how to push changes made in a DataSet to the database in Chapter 11.

You can drag a table from a SQL Server database onto your form and have the SqlConnection and SqlDataAdapter objects created in one step. You use Server Explorer for this. With databases that do not show up in Server Explorer, your choices are limited. You can use the controls in Data category of the Toolbox to drag each item to your form, and then set properties for each data object with the Properties window.

Note 

To open Server Explorer, select View Server Explorer, or press Cntl+Alt+S.

To add a SqlConnection and SqlDataAdapter object to your form, perform the following steps:

  1. Open Server Explorer.

  2. Open the connection to your SQL Server Northwind database (or create a new connection if necessary by right-clicking on the Data Connections node and selecting Add Connection, and entering the sa username and password for your Northwind database; you might need to get the password from your database administrator).

  3. Drill down to the Customers table in the Northwind database and drag it to your form. This creates a SqlConnection object named sqlConnection1 and a SqlDataAdapter object named sqlDataAdapter1, as shown in Figure 6.10.

    click to expand
    Figure 6.10: Form with SqlConnection and SqlDataAdapter objects

  4. Click your sqlConnection1 object to display the properties for this object in the Properties window.

  5. To enable sqlConnection1 to access the database, you need to set the password for the connection. To do this, add a substring containing pwd to the ConnectionString property of sqlConnection1. Go ahead and add pwd=sa (you might need to get the password for the sa user from your database administrator) to the ConnectionString property, as shown in Figure 6.11.


    Figure 6.11: Setting the ConnectionString property for the sqlConnection1 object

Next, you'll modify the SQL SELECT statement used to retrieve the rows from the Customers table:

  1. Click your sqlDataAdapter1 object to display the properties for this object.

  2. 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 (see Figure 6.12).


    Figure 6.12: SelectCommand property for the sqlDataAdapter1 object

  3. Click CommandText, and then click the button with the ellipsis to display the Query Builder, as shown in Figure 6.13.

    click to expand
    Figure 6.13: The Query Builder

  4. You use the Query Builder to define SQL statements. You can type in the SQL statement, or you can build it up visually. Make sure all the columns are selected from the Customers table using the Customers box at the top left of the Query Builder.

  5. Click OK to continue.

To check the rows returned by this SELECT statement, perform the following steps:

  1. Click the Preview Data link near the bottom of the Properties window. This displays the Data Adapter Preview dialog box.

  2. In the Data Adapter Preview dialog box, click the Fill Dataset button to run the SELECT statement, as shown in Figure 6.14.

    click to expand
    Figure 6.14: Previewing the rows retrieved by the SELECT statement

  3. Click the Close button to close the Data Adapter Preview dialog box.

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

  1. Click an area of your form outside the DataGrid.

  2. Click the Generate Dataset link near the bottom of the Properties window. This displays the Generate Dataset dialog box.

  3. Select the New radio button and make sure the field to the right of this radio button contains DataSet1, as shown in Figure 6.15.

    click to expand
    Figure 6.15: Entering the DataSet details in the Generate Dataset dialog box

  4. Click the OK button to continue. This adds a new DataSet object named dataSet11 to your form.

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, allowing the rows from your DataSet to be displayed in your DataGrid. To set the DataSource property, you perform the following steps:

  1. Click your DataGrid object and set the DataSource property to dataSet11.Customers.

  2. Now, you'll add a button that will fill sqlDataAdapter1 with the rows retrieved by your SELECT statement. Select Button from the Toolbox and drag it onto your form to a position just below your DataGrid.

  3. Set the Text property for your button to Run SELECT in the Properties window.

To populate sqlDataAdapter1 with the rows retrieved by the SELECT statement, you need to call the Fill() method for this object. You'll call this method when the button is clicked. To add the required code, perform the following steps:

  1. Double-click the button you added earlier. This opens the code window and positions the cursor in the button1_Click() method.

  2. Enter the following code in this method:

     dataSet11.Clear(); sqlDataAdapter1.Fill(dataSet11, "Customers"); 

Note 

You could also call the Fill() method in the Form1_Load event. This event occurs when the form is initially loaded.

Next, add another button that will allow you to save any changes you make to the rows in the DataGrid:

  1. Go ahead and add another button and set the Text property of this button to Update.

  2. Double-click this button and add the following statement to the button2_Click() method: sqlDataAdapter1.Update(dataSet11, "Customers");

This statement updates a row with the new column values you enter in your DataGrid.

You've now finished your form. Build the project by selecting Build Build Solution.

Finally, you're ready to run your form! Perform the following steps:

  1. Select Debug Start without Debugging to start your form.

  2. Click the Run SELECT button on your form to run your SELECT statement. This retrieves the rows from the Customers table and displays them in the DataGrid of your form.

  3. Modify the CompanyName column of the first row to Alfreds Futterkiste Shoppe and click the Update button; this commits the change you made to the row in the Customers table (see Figure 6.16).


    Figure 6.16: The running form

  4. Reset the CompanyName for the first row back to the original by removing Shoppe from the end and clicking the Update button again.

In the next section, you learn how to use the VS .NET Data Form Wizard to create a more advanced Windows application that accesses the SQL Server Northwind 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