Creating a Data Application

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 Connection

To 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.

graphics/09fig01.jpg

ADDING SERVERS TO THE SERVER EXPLORER

As you install Visual Studio, it searches your computer for data providers and adds them to the Server Explorer automatically as it finds them. You can also add additional servers to the Server Explorer at any time. To do that, select Tools, Connect to Server (or right-click the Servers node that appears at the bottom of the Server Explorer, and select Add Server). This opens the Add Server dialog box, which lets you enter new data providers identified by computer name or Internet IP address.


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.

graphics/09fig02.jpg

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.

graphics/09fig03.jpg

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.

graphics/09fig04.jpg

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.

DELETING A DATA CONNECTION

You can also delete data connections; just right-click the connection in the Server Explorer and select Delete.


Creating Connection, Data Adapter, and Dataset Objects

The 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.

graphics/09fig05.jpg

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.

graphics/09fig06.jpg

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.

graphics/09fig07.jpg

THE SQL SPECIFICATION

To work with databases in C# beyond the most basic, you have to be pretty familiar with SQL. You can get the International Organization for Standardization (ISO) documents that define SQL online. As of this writing, they're at http://www.iso.org/iso/en/CatalogueListPage.CatalogueList?ICS1=35&ICS2=60&ICS3=. This page lists the ISO's catalog for SQL documents (these documents are not free, however). In case this URL no longer works by the time you read this, go to www.iso.org, find the link for Information Technology, and then click the link for Languages Used in Information Technology.


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.

graphics/09fig08.jpg

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.

graphics/09fig09.jpg

Closing the Add Table dialog box opens the Query Builder tool, as you see in Figure 9.10.

Figure 9.10. The Query Builder.

graphics/09fig10.jpg

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.

graphics/09fig11.jpg

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.

graphics/09fig12.jpg

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.

graphics/09fig13.jpg

RECONFIGURING A DATA ADAPTER

You can right-click a data adapter object at any time and select Configure Data Adapter to change an adapter's configuration, including its internal SQL.


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.

graphics/09fig14.jpg

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.

graphics/09fig15.jpg

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.

graphics/09fig16.jpg

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.

graphics/09fig17.jpg

SHOP TALK : WEB SECURITY

There's one more issue to consider when creating data-aware Web applications. Microsoft has been taking hits for its security handling for a long time, and it's been an especially acute problem on the Web. Almost every few days it seems one reads about a new security issue with Microsoft Internet software. Accordingly, somewhere between the original release of Visual Studio .NET and Visual Studio .NET 2003, Microsoft tightened up the way Web applications can access data sources like SQL Server. This decision is a controversial one, and it surely doesn't make life easier for the programmer. Microsoft now requires you to be a qualified user to log into SQL Server from a Web application, for example. You can log in with a name and password, but it's not a good idea to hard code those items into a Web application's code. The way I prefer to solve this problem is to treat the Web application itself as a qualified user. Web applications operate with the username " ASPNET " when they run, and if you can treat ASPNET as a qualified user and maintain your security, you've solved this problem. Treating ASPNET as a qualified user means creating an account for ASPNET in the data provider. If the data provider and IIS are on the same machine, you can use Windows integrated security to log ASPNET into the data provider. In SQL Server, you create a login name of computername \ASPNET , where computername is the name of your computer.

How you create a new SQL Server login depends on the version of SQL Server you're using. For example, in SQL Server 2000, you can open the SQL Enterprise Manager, open the Security folder, right-click the Logins node, and select the New Login item to open the SQL Server Login Properties dialog box. You enter the name of the new user (such as STEVE\ASPNET for the computer named STEVE we've been using), and click the Windows Authentication radio button to enable Windows integrated security. (You don't have to use Windows integrated security if you prefer not to; you can click the SQL Server Authentication radio button and enter a password to use a SQL Server login.) Make sure the Grant Access radio button is clicked, and then click the OK button to add this new user.




Microsoft Visual C#. NET 2003 Kick Start
Microsoft Visual C#.NET 2003 Kick Start
ISBN: 0672325470
EAN: 2147483647
Year: 2002
Pages: 181

Similar book on Amazon

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