It's time to use these objects now, putting them to work in an example to see how things fit together. The C# IDE lets you work with the objects we've seen in a way that's relatively easy, simply using visual tools. We'll see how they work in the first example for this chapter, named ch09_01 in the code for this book. This example fetches the data in the authors table of the pubs example database that comes with SQL Server, and displays it in a C# data grid control. To start, create the Windows application named Ch09_01 now. Creating a Data ConnectionTo work with the pubs example database in SQL Server, you first need a connection to that database. In the IDE, you use a tool called the Server Explorer to establish connections to databases. You can see the Server Explorer at left in Figure 9.1 in the IDE. Usually, the Server Explorer is docked to the left edge of the IDE. You can open it by letting the mouse pointer move over the Server Explorer tab. To display the Server Explorer if it's not already visible, choose View, Server Explorer, or press Ctrl+Alt+S. Figure 9.1. The Server Explorer.
The Server Explorer lets you create and examine data connections , including connections to data providers in the Web. It's important to realize that a data connection is not the same as a connection object . A data connection is not specific to any C# application. You can use a data connection from the Server Explorer with any application; they're always available in the Server Explorer. To work with a data provider, you need a data connection, and in the IDE, those data connections are managed by the Server Explorer. Using a data connection, you can create a connection object in an application, and those connection objects are specific to one particular application (they appear in a form's component tray). Using that connection object, you can create a data adapter, and using that data adapter, you can create a dataset. In the Ch09_01 example, we're going to display the data from the authors table in the Microsoft SQL Server's pubs sample database, so we'll need a data connection to that database. To create that data connection, right-click the Data Connections icon in the Server Explorer and select the Add Connection item, or choose Tools, Connect to Database. Doing so opens the Data Link Properties dialog box you see in Figure 9.2. Figure 9.2. The Data Link Properties dialog box.
You use the Data Link Properties dialog box to create a new data connection. You can enter the name of the server you want to work with, as well as your login name and password, if they apply. We'll use Windows NT integrated security to connect to the database, because SQL Server is on the same machine as Visual Studio in this case, but you can choose a server name and enter a username and password in the Data Link Properties dialog box if you prefer. In the Data Link Properties dialog box, you can choose a database on the server with the Select the Database on the Server option, or another database with the Attach a Database File as a Database Name option. In this case, we'll use the pubs example database that comes with SQL Server, so select the first option and choose the pubs database, as you see in Figure 9.2. When you create a data connection, the default data provider is SQL Server, but you can specify a data provider other than SQL Server, of course. To do that, you click the Provider tab in the Data Link Properties dialog box, as you see in Figure 9.3, which displays a list of the data protocols supported. You select from the list data protocols that match the data provider you're working with, and then click the Connection tab and select the database you want to work with. Figure 9.3. Selecting a data provider.
To test the new data connection, select the Connection tab and click the Test Connection button you see in Figure 9.2. If the data connection works, you'll see a message box with the message Test connection succeeded as you see in Figure 9.4. If the data connection isn't working, you'll see a message box explaining what's wrong. Figure 9.4. Testing a data connection.
If the data connection test was successful, click the OK button in the Data Link Properties dialog box to complete creating the new data connection. The SQL Server on the machine this example is on is named STEVE , so the connection to the pubs database is named STEVE.pubs.dbo , as you see in the Server Explorer in Figure 9.1. You can open the new connection using the + icon in front of the connection in the Server Explorer, which displays the tables in the pubs database, as you see in the figure. You can see that the table we plan to work with, the authors table, is visible in the figure. We can now access that table in our applications. At this point, the data connection you've created is part of your programming environment; it's not part of any specific application. The next step is to put this new data connection to work, and we'll do that by creating connection and data adapter objects.
Creating Connection, Data Adapter, and Dataset ObjectsThe IDE lets you create a connection object and a data adapter at the same timeall you have to do is drag a data adapter object from the toolbox onto a form. The toolbox has its own tab for data objects, the Data tab. Click that tab now and drag a SqlDataAdapter object from the toolbox to Form1 in the Ch09_01 application. When you drop the data adapter onto the form, the IDE opens the Data Adapter Configuration Wizard that you see in Figure 9.5. This is the Wizard that lets you customize your data adapter, and that usually means creating the SQL statement this adapter will use. Figure 9.5. The Data Adapter Configuration Wizard.
Click the Next button in the Data Adapter Configuration Wizard to choose the data connection you want to use, as you see in Figure 9.6. Here, you can use an existing data connection, like the one we've already created, or click the New Connection button to create a new data connection (clicking the New Connection button opens the Data Link Properties dialog box which we've used to create a new data connection). In this example, we'll use the connection we've made to the pubs database, as you see in Figure 9.6. Figure 9.6. Selecting a data connection.
Click Next to move to the next pane in the Data Adapter Configuration Wizard. In this pane, you choose a query type for the new data adapter, as you see in Figure 9.7. In this case, we're going to create a SQL statement, as you see in the figure, but notice that you can create new or use existing stored SQL procedures. Figure 9.7. Choosing a query type.
Clicking Next again displays the dialog box you see in Figure 9.8. This is the dialog box used to create the SQL statement we'll use in this data adapter to get data from the data provider. Figure 9.8. Generating a SQL statement.
You can use a visual tool to make writing the SQL easier; just click the Query Builder button you see in Figure 9.8. This displays the Add Table dialog box that you see in Figure 9.9. In the Add Table dialog box, you select the table(s) you want to work with and click the Add button. When you've selected all the tables you want to work with in this way, click the Close button. In this example, we're just going to display a few fields from the authors table, so select that table and click Add in the Add Table dialog box, and then click Close to close the Add Table dialog box. Figure 9.9. The Query Builder's opening dialog box.
Closing the Add Table dialog box opens the Query Builder tool, as you see in Figure 9.10. Figure 9.10. The Query Builder.
You can see a window displaying the fields in the authors table at the top of Figure 9.10. You add a field to the SQL statement you're creating by clicking the field's check box in a table's window. You must select at least one field when creating the SQL for a data adapter, or the Query Builder won't create working SQL for you. In Figure 9.10, for example, we've checked the au_id , au_lname , and au_fname fields, which means our SQL statement will fetch the data for those fields. You can select all fields in a table by checking the check box labeled with an asterisk ( * ). Clicking OK in the Query Builder closes that tool and you see the resulting SQL in the Data Adapter Configuration Wizard in Figure 9.11. Figure 9.11. A created SQL statement.
Here's what that SQL statement looks like. Note that we're selecting the au_id , au_lname , and au_fname fields of records in the authors table: SELECT au_id, au_lname, au_fname FROM authors In this way, you've been able to create the SQL this data adapter will use to retrieve data from the database. Clicking Next in the Data Adapter Configuration Wizard makes the wizard configure the data adapter and reports its results, as you see in Figure 9.12. In this case, the Data Adapter Configuration Wizard knows what data we want to work with, and has generated a SQL SELECT statement to fetch that data. It's also generated SQL INSERT , UPDATE , and DELETE statements to manipulate that data if needed. And that's it; click the Finish button to close the Data Adapter Configuration Wizard and complete the creation of the data adapter. Figure 9.12. Configuring a data adapter.
Closing the Data Adapter Configuration Wizard creates both the connection object, sqlConnection1 , and the data adapter we'll need, sqlDataAdapter1 . Both these objects appear in the application's component tray, as you see in Figure 9.13. The connection object has been configured automatically with a connection string , which tells this object what to connect to; in this case, that connection string is "workstation id=STEVE;packet size=4096;integrated security=SSPI;initial catalog=pubs;persist security info =False" . In the next chapter, we'll be connecting to databases in code, and it can be difficult to know how to write these connection strings for various data providers. One way to solve the problem is to let the visual tools we're using in this chapter write those strings for us. Figure 9.13. New connection and data adapter objects.
The next step is to create a new dataset using this data adapter. You do that by selecting Data, Generate Dataset in the IDE, which opens the Generate Dataset dialog box you see in Figure 9.14. Figure 9.14. The Generate Dataset dialog box.
This dialog box lists the data available in the various data adapters in your application, and will create a new dataset class, which will be named DataSet1 by default. In this case, the data available is only the authors table in the data adapter sqlDataAdapter1 . Make sure that table is selected, and click OK to create the new dataset object, dataSet11 (that is, the first object of the DataSet1 class), which you can see in the component tray in Figure 9.15. Figure 9.15. A new dataset object.
The next step is to display the data in the dataset. To do that, drag a new data grid control, which is designed to display entire data tables, to the Windows form in this application. To bind this data grid to the data in dataSet11 , set the data grid's DataSource property to dataSet11 , and its DataMember property to authors . Binding a control to a data source makes it display data from that source automatically. Because your application is disconnected from the data source, you'll also need to use the data adapter to connect to that data source and fetch the data. For that reason, let's add a Get Data button to this application, using this code to fill the dataset with data from the data adapter. (Note that if you prefer, you can put this code into the Form_Load event handler so the dataset is filled automatically when the form loads.) private void button1_Click(object sender, System.EventArgs e) { dataSet11.Clear(); sqlDataAdapter1.Fill(dataSet11); } Now the users can load data into the data grid when they want to by clicking the Get Data button. The user can also edit the data in the data grid, thereby changing that data. To update the data stored in the database, you can call the data adapter's Update method. In this example, we'll do that with a new button called Save Data. Here's the code for this button: private void button2_Click(object sender, System.EventArgs e) { sqlDataAdapter1.Update(dataSet11); } The data grid and the dataset are bound, which means that when the user edits the data in the data grid control, that control automatically updates the dataset. To send the new data back to the database, you can use the data adapter's Update method, as we're doing here. And that's it. Now we have two buttons in our applicationLoad Data and Save Data, as you see in Figure 9.16. They allow users to load and save data on demand. To implement these buttons , we've used the data adapter's Fill and Update methods . As you can see in Figure 9.16, each record displays the fields we configured the dataset to display. Figure 9.16. Running a data application.
In Web applications, the process is similar, but here you also have to explicitly call the DataBind method of any controls bound to the dataset to refresh the data binding each time the page is loaded. For example, here's how that might look when you've bound a data grid to dataSet11 in a Web application, and refresh the data binding in the Page_Load event: private void Page_Load(object sender, System.EventArgs e) { dataSet11.Clear(); sqlDataAdapter1.Fill(dataSet11); DataGrid1.DataBind(); } This refreshes the data in the bound control each time the page loads. You don't have to do this in Windows applications, because there that connection is "live". Otherwise, working with databases in Web applications is very similar to working with databases in Windows applications. You can see a data Web application displaying the authors table in a Web form data grid in Figure 9.17. Figure 9.17. A data Web application.
|