Using a DataGrid Control to Access a Database

Using a DataGrid Control to Access a Database In this section, youll learn how to use a DataGrid control to access the rows
Nội dung trích xuất từ tài liệu:
Using a DataGrid Control to Access a DatabaseUsing a DataGrid Control to Access a DatabaseIn this section, youll learn how to use a DataGrid control to access the rows in a databasetable. 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. Figure 6.9: Form with a DataGridNext, youll 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. Youll 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 theSqlConnection and SqlDataAdapter objects created in one step. You use Server Explorerfor 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 followingsteps: 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. 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 objectNext, youll modify the SQL SELECT statement used to retrieve the rows from theCustomers 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 object3. Click CommandText, and then click the button with the ellipsis to display the Query Builder, as shown in Figure 6.13. 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. 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 copyof the information stored in the database. A DataSet object can represent databasestructures such as tables, rows, and columns, among others. In this example, youll use aDataSet object to store the rows from the Customers table: 1. Click an area of your form outside the DataGrid. 2. Click t ...