Ch16

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

16

CHAPTER

Consuming and Manipulating Data from Data Sources In this chapter, you will • Learn to consume and manipulate data • Access and manipulate data from a Microsoft SQL Server database • Access and manipulate data from a data store • Handle data errors • Display and update data • Transform and filter data • Bind data to the user interface

In Chapter 10, you were introduced to the ADO.NET object model and learned how to use it to create DataSet and DataAdapter objects. In this chapter, we are going to have a look at the process involved in getting data from a Microsoft SQL Server with error checking in a web application displaying the data using some of the data-bound controls we have at our disposal through ASP.NET. To access data through ASP.NET, we use the ADO.NET objects: xxxConnection, xxxDataAdapter, and DataSet. In Chapter 10, we looked at some of those objects and at how we could use them to connect to and use databases. These different ADO.NET objects come in three individual groupings based on the technology that is used to connect to and communicate with the database. The xxxConnection and xxxDataAdapter objects are implemented as technology-specific objects that are identified through the prefix to the class name that will replace the xxx in the names. Objects that start with the “sql” prefix are specifically tuned to work with Microsoft SQL; Server version 7 or higher; those with the “oleDb” prefix work with OLE DB providers, and the last group, with the prefix “odbc” (for use with ODBC), is not supplied with ADO.NET—the ODBC objects must be downloaded from Microsoft directly.

1 P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:44 PM

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

2 EXAM TIP Remember that the sqlConnection, sqlDataAdapter, and sqlCommand objects are specific for Microsoft SQL Server version 7 or higher. For all other database systems, use the oleDbConnection, oleDbDataAdapter, and oleDbCommand objects. Only use the ODBC objects for systems that do not have OLE DB support. We will first look at how to use the Visual Studio .NET facilities to add these objects to a form and configure them through the wizards that are supplied with the IDE. Then we will work with data from the database directly, through code, without making use of the wizards. All examples in this chapter use the Address database that is built especially for this chapter. Please see the folder for Chapter 10 on the CD that accompanies this book for instructions on how to install the database on your system.

Visual Studio .NET Support for Data The Visual Studio .NET IDE provides ADO.NET objects that can be dragged from the Toolbox and dropped onto the form. In the following illustration, you can see the default objects that support Microsoft SQL Server and OLE DB providers—the DataSet object works with either of the database technologies. To use one of the objects, you need to drag it onto the form where it will reside as an icon at the base of the form in Design view.

The best way to learn how to use the objects and wizards supplied with Visual Studio .NET is to use the wizards to work with the objects, so we’ll work through a couple of example projects.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:45 PM

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

3 Using a DataGrid Server Control As an example, we will create a Web Form that uses a DataGrid server control to display data from the Address database. To start the example, create a new project with these steps: 1. On the Start Page of Visual Studio .NET, click New Project, or select File | New | Project from the menus. The New Project dialog box will be displayed. 2. Select Visual C# Projects in the Project Type pane of the New Project dialog box. 3. Select ASP.NET Web Application in the Templates pane. 4. Enter the URL for the new application into the Location field. Use the localhost web server, and call the application AddressOne.

1. Open the Data tab in the Toolbox. 2. Drag a sqlDataAdapter object onto the form.

Figure 16-1 The start of the AddressOne application

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:45 PM

PART III

The completed New Project dialog box can be seen in Figure 16-1. Once the project is created, you can create and configure the DataSet that will be used to populate the DataGrid control that will display the data as a spreadsheet. We will use the Data Adapter Configuration Wizard, which will ask some questions and then configure the DataAdapter to access the data. Follow these steps:

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

4 3. The Data Adapter Configuration Wizard will start, as shown here. Click Next to proceed.

4. In the next wizard screen, click on New Connection to open the Data Link Properties dialog box. 5. In the Data Link Properties dialog box, expand the Select or Enter a Server Name combo box and select your server name from the list. Select the option to Use a Specific User Name and Password, and enter sa as the user name and leave the password blank. In the Select a Database File or Database Name combo box, expand and select the Address database. The finished dialog box is shown in Figure 16-2. 6. Test the connection by clicking the Test Connection button. Always test the connection to make sure it works. 7. Click Next to go to the third screen of the wizard. 8. The third screen gives you the choice of entering a SELECT statement that will be used directly, entering a SELECT statement that will be converted to a stored procedure, or selecting a stored procedure to use. Stored procedures are written in the SQL language and are stored in the database—we will look at these features later on in this chapter. Select the Use SQL Statements option, and click Next to continue to the next screen.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:45 PM

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

5 Figure 16-2 The completed Data Link Properties dialog box

PART III

9. Enter the following SQL statement in the text area: SELECT TOP 15 FirstName, LastName, EmailAddress FROM Users 10. Click Next to see the queries the wizard will generate for you. 11. Click Finish to create the DataAdapter. The Data Adapter Configuration Wizard created the two objects that can be seen at the bottom of the Web Form shown in Figure 16-3. The default names for the objects are sqlDataAdapter1 and sqlConnection1.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:45 PM

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

6

Figure 16-3

The project with the ADO.NET objects

The next step is to generate the DataSet from the DataAdapter that you just created. Here’s how to do it: 1. Right-click on sqlDataAdapter1 and select Generate DataSet, or select Data | Generate DataSet menus. 2. In the Generate Dataset dialog box, enter dsUsers as the new name for the DataSet (as shown next).

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:46 PM

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

7

PART III

3. Select the Add This Dataset to the Designer check box to create a class that wraps the DataSet. 4. Click OK to generate the DataSet. Visual Studio .NET generates the DataSet class (dsUsers) and a schema that defines the DataSet (dsUsers.xsd). You can view the schema in the Solution Explorer. To see the schema’s dependent file, you will need to click on the Show All Files toolbar button. The wizard also added the new DataSet class (dsUsers1) to the form. The data components are now completely configured. All you need now is to add the DataGrid to the form and add some code to complete the example. Follow these steps: 1. Select the Web Forms tab in the toolbox, and drag a DataGrid server control onto the Web Form. 2. Right-click the DataGrid control and select Auto Format. Look at the color schemes and select a suitable one (we selected Colorful 3). 3. In the Properties window for the DataGrid control, select the DataSource property, and select dsUsers1 as the data source. 4. Set the DataMember property of the DataGrid to Users. Setting the DataSource and DataMember properties effectively binds the control to the DataSet. There is one more step to be performed before the application will run, and that is to add some code to the form: 1. Open the Web Form’s codebehind module by double-clicking on the form.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:46 PM

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

8 2. Change the Page_Load event handler to match the following: private void Page_Load(object sender, System.EventArgs e) { if ( !IsPostBack ) { sqlDataAdapter1.Fill(dsUsers1); DataGrid1.DataBind(); } } In the preceding code, the Fill() method of the DataAdapter will populate the DataSet from the database, based on the SQL statement that was defined for the DataAdapter. The DataBind() method of the DataGrid control refreshes and updates the control. To test the web page, save and run it by pressing F5. The end result should be similar to what is shown in Figure 16-4. This results in a very presentable and functional application that will display a table of information from the Address database.

Creating and Using Stored Procedures As we promised earlier, we will now look at the different options for specifying what data you want to retrieve from the database. The third screen in the Data Adapter Configuration Wizard gave you the option to use a SELECT statement to retrieve the data, to build a stored procedure from the SELECT statement, or to specify a stored procedure already in existence in the database. In the previous example, we simply used a SELECT statement. A stored procedure is built from Transact-SQL statements (the language of Microsoft SQL Server) and stored in the database for reuse. Stored procedures are like methods in a database—they can receive parameters and then return values to the caller. There are a number of reasons for using stored procedures: • Stored procedures are stored in the procedure cache of the database server, resulting in faster execution if the stored procedure is called repeatedly. • Stored procedures are used to centralize the data-access logic on the server. • Stored procedures will minimize the use of the network for large queries. In the next example, you will see how to use a stored procedure rather than an SQL statement to retrieve the data. Here is how you do that: 1. Open the Data tab in the Toolbox. 2. Drag a sqlDataAdapter object onto the form. 3. The Data Adapter Configuration Wizard will start. Click Next to proceed.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:46 PM

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

9

PART III

Figure 16-4

The final AddressOne application

4. In the next wizard screen, click on New Connection to open the Data Link Properties dialog box. 5. In the Data Link Properties dialog box, expand the Select or Enter a Server Name combo box and select your server name from the list. Select the option to Use a Specific User Name and Password, and enter sa as the user name and leave the password blank. In the Select a Database File or Database Name combo box, expand and select the Address database. The finished dialog box is shown in Figure 16-4.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:46 PM

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

10 6. Test the connection by clicking the Test Connection button. Always test the connection to make sure it works. 7. Click Next to go to the third screen of the wizard. 8. In the Data Adapter Configuration Wizard’s third screen, select the second option to create a new stored procedure, as shown here. Click Next.

9. In the Generate the Stored Procedure screen, click on the Query Builder button. 10. Select the Addresses, PhoneNumbers, and Users tables, and add them to the Query Builder. Close the Add Table dialog box. 11. Select FirstName, LastName, and EmailAddress in the Users table by putting a checkmark in the checkbox beside the field name. (See Figure 16-5.) 12. Select Address1, City, Province, PostalCode, and Country from the Addresses table. 13. Select PhoneType and PhoneNumber from the PhoneNumbers table. 14. Right-click anywhere inside the Query Builder and select Run. Ensure that data is returned from the query.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:47 PM

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

11

PART III

Figure 16-5

Using the Query Builder

15. Click OK to exit the Query Builder. The final query when you return from the Query Builder should look like this: SELECT Users.FirstName, Users.LastName, Users.EmailAddress, Addresses.Address1, Addresses.City, Addresses.Province, Addresses.PostalCode, Addresses.Country, PhoneNumbers.PhoneType, PhoneNumbers.PhoneNumber FROM Addresses INNER JOIN Users ON Addresses.NameID = Users.NameID INNER JOIN PhoneNumbers ON Users.NameID = PhoneNumbers.NameID

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:47 PM

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

12 16. Click Next, and the Create Stored Procedure screen will be displayed, as shown in the following illustration. You can change the names of the stored procedures here, but for this example, leave the default names of the stored procedures.

17. Click Next to generate the stored procedures. The warnings you will see are normal when the SELECT statement is based on a JOIN. The wizard cannot produce INSERT, UPDATE, or DELETE stored procedures from a JOIN statement—those stored procedures must be created by the database administrator. 18. Click Finish to complete the Data Adapter Configuration Wizard. The procedure from this point on is the same as for the previous example. Create a DataSet from the DataAdapter, bind the DataSet to the DataGrid control, and test it. When you are using a database that already has existing stored procedures, you can select Use Existing Stored Procedures from the Data Adapter Configuration Wizard’s third screen. Figure 16-6 shows the resulting screen, from which the stored procedures can be selected. The previous two examples built a Web Form using the DataGrid server control and the objects supplied from Visual Studio .NET. Now we will look at how you can build a form that lets you bind a DataSet to a group of controls and implement navigation and editing in the interface.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:47 PM

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

13 Figure 16-6 Using existing stored procedures

PART III

Data Binding In the previous sections, we used data binding between a DataSet and the DataGrid control, but now we will take a closer look. Data binding is a negotiated handshake between a control and the DataSet. If the DataSet is updated from the server, the control is updated; and conversely, if the data in the control is changed, the DataSet is notified about that change. Data binding can be implemented both at design time and at run time by setting the properties of the controls. To bind a control to a data source, you use the DataBindings property, as shown in Figure 16-7. Click on the ellipsis button (...) beside the DataBindings property to open the Data Bindings dialog box shown in Figure 16-8. This dialog box is used to bind data to any of the properties of a control, either by using a simple binding and providing formatting as shown in Figure 16-8, or by specifying a custom binding expression, as shown in Figure 16-9. When you enter a binding expression in the Custom Binding Expression field, you will describe the binding using the DataBinder object’s Eval method (this is a static method). As parameters, the method takes the name of the DataSet, a string that describes the data member, and a formatting string. In the example in Figure 16-9, the DataSet name is dataSet11, the data member is "Tables[SP_SelectUsers]

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:48 PM

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

14 Figure 16-7 The DataBindings property

.DefaultView.[0].LastName", and the formatting string is "{0}". The preceding data member translates as follows: • The member table in the Tables collection defined by the name SP_SelectUsers. • The DefaultView that defines the columns of the returned data. • The first (zero-based) rowset. The term rowset is used to describe the rows of data that the table object represents. • The LastName column in that rowset.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:48 PM

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

15 Figure 16-8 The Data Bindings dialog box

PART III

By using a custom binding expression, you can specify virtually any data binding. Any part of the user interface can be bound to data in this fashion, making it possible to configure the UI from a data source.

Figure 16-9 The Data Bindings dialog box with a custom binding expression

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:48 PM

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

16

Error Handling So far, we have omitted the error-handling code in this chapter to keep the code listings more focused on the use of ADO.NET in Web Forms, rather than on error-handling techniques. However, database access can create problems for a web site because the database will, in most cases, be located on a database server that is remote from the web server. When there are connection problems between the database server and the web server, ADO.NET will fail with an exception, and if you don’t catch the exception, it will bubble through to the Page object and terminate the processing of your Web Form. This is not the best behavior for any application. You should always enclose your database access code in try … catch blocks to ensure that you handle any errors in such a way that you can communicate the condition to the user in plain language. In Chapter 4, you were introduced to the exception-handling structures used in C# programs. The try ... catch ... finally structure gives you a controlled way of executing statements in a try block and, if there are any exceptions, to match up the proper catch block to deal with the exception. The finally block will always execute irrespective of whether try or catch blocks completed. The exceptions that relate to the database, and that can be raised in an ADO.NET application, are located in the System.Data namespace. The most common exceptions are listed in Table 16-1. Exception

Description

SqlException

This is the general exception class for an SQL managed provider. This is the general exception class for an OLE DB managed provider. This is the general exception class for the ODBC managed provider. This is the base class for most other exceptions in the System.Data namespace. This exception is raised when a constraint has been violated. This exception is raised when an attempt to write to the database is detected while the database is being updated by another client. This exception is raised when an attempt is made to define an object in the database with a name that already exists. This exception is thrown when an attempt is made to modify read-only data. This exception is raised when an attempt is made to insert data into a table without supplying data for a column that is set to not allow NULL values. This exception is raised when an SQL statement with syntax errors is passed to the provider.

OleDbException ODBCException DataException ConstraintException DBConcurrencyException

DuplicatedNameException ReadOnlyException NoNullAllowedException

SyntaxErrorException

Table 16-1

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:48 PM

ADO.NET Exceptions Related to Databases

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

17 The ADO.NET exceptions are hierarchal, with System.Data.DataException as the base class—all the other exceptions inherit from it. That means that you can build a structure in your exception handling that treats special exceptions in a custom way, or you can write a catch-all type of exception-handling structure. The following code segment illustrates the hierarchical nature of the exceptions—only the first catch statement that matches the exception will be executed.

NOTE The exception class is always called e; this has become the convention.

The exception processing proceeds from the top down through the catch statement. If a base exception class is higher in the listing than a child exception class, the child exception will never be caught. EXAM TIP The DataException is the base exception for all the ADO.NET exceptions. You will see this structure in use in the rest of this chapter as we start to use data sources programmatically in the Web Forms.

Accessing Data Programmatically Even though we can quickly build a Web Form with very little coding, the end result will, in most cases, be an application that we still want to tune and control a bit more. This can be done by using the ADO.NET objects directly in code. In this section, we will look closer at how we can work with databases programmatically.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:49 PM

PART III

try { // do something with the database } catch (ConstraintException e) { // handle the ConstraintException } catch (ReadOnlyException e) { // handle the ReadOnlyException } catch (DataException e) { // handle any other exceptions } finally { // this code will always run, put the close statements here }

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

18 First, Table 16-2 gives a refresher of the objects from Chapter 10 and what they are used for. The prefix xxx used in the table identifies the three object variations for the three managed data providers—SQL, OLE DB, and ODBC. The prefixes are sql, oleDb, and odbc. EXAM TIP Remember that the SQL managed provider only works with Microsoft SQL Server 7 and above.

Connecting to a Data Source To connect to a data source, we need to select the appropriate managed provider. For the rest of this chapter, we will assume (as does Microsoft) that we are using Microsoft SQL Server 2000 as the data source. When you create the connection object, you need to supply a connection string that defines, among other things, the name of the database and how you authenticate. A sample connection string looks like this: data source=KENSNABBEN; initial catalog=Address; user id=sren; password="123";

The data source parameter defines the server name, the initial catalog is the name of the database you want to connect to, the user id is the name you want to connect as, and the password is the password for the user id. The connection string can be built by hand, or you can use the Data Link Properties dialog box (shown earlier in Figure 16-2) to build it. If you use the Data Link dialog box, the connection string will be saved by Visual Studio .NET so you can retrieve the string easily in other projects. The Data Link dialog box was described in the “Using a DataGrid Server Control” section, earlier in this chapter.

Object

Description

xxxConnection xxxDataAdapter

Defines the way we find and authenticate with a data source. Defines four commands that are used to SELECT, INSERT, UPDATE, and DELETE data from the data source. The DataAdapter is commonly used to create a DataSet that is then used for data processing. Creates an object that defines an SQL statement or a stored procedure that will be reused throughout the application. Provides a disconnected representation of the data. It contains multiple tables that can be edited, displayed, or used. Provides a connected, read-only, forward-only way of manipulating the data.

xxxCommand DataSet xxxDataReader Table 16-2

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:49 PM

The .NET Data Provider Objects

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

19 The recommended procedure for creating a connection to a data source is as follows: • Create a global string to hold the connection string, and use the Application object to store the string. This way; if the string needs to be changed, you only need to perform the change once, and because all connections to a particular database use the same string, the Connection Manager can take advantage of connection pooling.

The following code segment illustrates how to connect to a database. In this example, the connection is made to the Address database on a Microsoft SQL Server 2000 using the name KENSNABBEN. ... string strSQL = "user id=sa;password;initial catalog=Address;" + "data source=KENSNABBEN"; SqlConnection addressConnection = new SqlConnection(strSQL); try { addressConnection.Open(); // perform the data processing ... } catch (SqlException e) { // we will get here if the connection failed to open // process the error by, for example, throwing an exception to the caller // to try another connection. } finally { // close the connection and set the reference to null addressConnection.Close(); addressConnection = null; }

EXAM TIP Instantiate the Connection object outside the try structure, perform all operations that can have failures inside the try block, and close everything in the finally block.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:49 PM

PART III

• Create the connection as a local object, and explicitly destroy the connection when it is no longer needed. If the procedure where the object was created goes out of scope, the connection should be destroyed by the garbage collector. That will not happen directly, nor can it be predicted—the connection will be active until the garbage collector runs. The connections that are held open on the database server will effectively limit the performance of the server.

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

20 Creating and Configuring a Command Object The next step is to create a Command object. The CreateCommand() method of the Connection object does that. The following line shows how to create the object: sqlCommand addressCommand = addressConnection.CreateCommand();

The Command object needs to have some properties set to be able to retrieve the data from the data source. We will use a stored procedure (SP_SelectUsers) to read the data. The CommandText property defines the text of the SELECT statement or the stored procedure, and the CommandType property declares what the content of the CommandText property is. The choices are listed in Table 16-3. The following code lines declare and configure the Command object to run the SP_SelectUsers stored procedure on the database. addressCommand.CommandText = "SP_SelectUsers"; addressCommand.CommandType = CommandType.StoredProcedure;

Populate the DataSet with the Data from the Command Now we can create a SqlDataAdapter to represent the data from the command. The SqlDataAdapter is used to connect data sources and DataSet objects. You build it by passing the command to the constructor of the DataAdapter, like this: SqlDataAdapter addressAdapter = new SqlDataAdapter(addressCommand);

The next step is to create the DataSet and associate it with the DataAdapter: DataSet addressSet = new DataSet(); addressAdapter.Fill(addressSet, "Addresses");

Once you have access to the DataSet, you can bind it to some of the controls on the Web Form. For this code example, the form has a DataGrid control called DataGrid1. To bind the data to this control, you need to add the following code to the Web Form: // Bind to a DataGrid. DataGrid1.DataSource = addressSet; DataGrid1.DataBind();

CommandText Property

Description

Test

Indicates that the content of CommandText is a text command, and there will be no optimization of the command. This is the default. Declares that the CommandText property contains the name of a stored procedure. The command will optimize the use of the cache on the server, if possible. Indicates that the CommandText property holds the name of a table in the database that will be returned by the command.

StoredProcedure

TableDirect Table 16-3

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:49 PM

Settings for the CommandText Property

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

21 The Completed Web Form The entire application consists of one form with a Button control named Button1 and a DataGrid control named DataGrid1. The following code is the WebForm1.aspx.cs file that defines the codebehind module for the application. The code presented in bold has been added from the previous code samples. System; System.Collections; System.ComponentModel; System.Data; System.Data.SqlClient; System.Drawing; System.Web; System.Web.SessionState; System.Web.UI; System.Web.UI.WebControls; System.Web.UI.HtmlControls;

namespace WebData7 { /// <summary> /// Summary description for WebForm1. /// public class WebForm1 : System.Web.UI.Page { protected System.Web.UI.WebControls.Button Button1; protected SqlConnection addressConnection; protected SqlCommand addressCommand; protected SqlDataAdapter addressAdapter; protected DataSet addressSet; protected System.Web.UI.WebControls.DataGrid DataGrid1; private void Page_Load(object sender, System.EventArgs e) { // Put user code to initialize the page here } ... private void Button1_Click(object sender, System.EventArgs e) { string strSQL = "user id=sa;initial catalog=Address;" + "data source=KENSNABBEN"; addressConnection = new SqlConnection(strSQL); try { addressConnection.Open(); // perform the data processing addressCommand = addressConnection.CreateCommand(); addressCommand.CommandText = "SP_SelectUsers"; addressCommand.CommandType = CommandType.StoredProcedure; addressAdapter = new SqlDataAdapter(addressCommand); addressSet = new DataSet(); addressAdapter.Fill(addressSet, "Addresses"); // Bind to a DataGrid. DataGrid1.DataSource = addressSet; DataGrid1.DataBind();

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:50 PM

PART III

using using using using using using using using using using using

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

22 } catch (SqlException ex) { // we will get here if the connection failed to open // process the error by for example throwing an exception to the caller // to try another connection. Response.Write(ex.ToString); } finally { // close the connection and set the reference to null addressConnection.Close(); addressConnection = null; } } } }

Building a Data Form The data-bound form is one of the common forms you will build—it is a form that displays detailed information about a record in a database. The form can be read-only, or it can be used for data entry and editing, as well. In this section, we will build a form that displays information about an entry in the Address database and that allows us to modify the information. We will implement the form as a data entry form.

Creating the SQL Statement The first thing to consider when building a data form is the SQL statement that will be used to select the data. In this example, we want to build SQL sentences to retrieve the user’s name and e-mail information. We will use a second SQL sentence to view the user’s address information. The following SQL statement will retrieve the user’s name and e-mail address: SELECT NameID, FirstName, LastName, EmailAddress FROM Users;

To select the address information for the user, we will use the following SQL sentence: SELECT Address1, Address2, City, Province, PostalCode, Country, NameID FROM Addresses

Building the Form To start building the application, we need to create a project, and Figure 16-10 shows the settings for the project. Call the ASP.NET web application AddressForm, and use the localhost server.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:50 PM

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

23 Figure 16-10 Starting the AddressForm project

PART III

The form we will build is shown in Figure 16-11. Rename the form as Address.aspx. The properties for the controls are listed in the following code segment that was generated when the controls were added to the Web Form. <%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false" Inherits="AddressForm.WebForm1" %> <meta name="GENERATOR" Content="Microsoft Visual Studio 7.0"> <meta name="CODE_LANGUAGE" Content="C#"> <meta name="vs_defaultClientScript" content="JavaScript"> <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:50 PM

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

24

Next you need to add an SqlDataAdapter to the form. To do so, open the Data tab in the Toolbox, and drag the sqlDataAdapter onto the form. The Data Adapter Configuration Wizard will start, and in the second screen of the wizard, select the same connection you used in the “Connecting to a Data Source” section, earlier in the chapter (use the Address database and connect as user id = sa). Figure 16-11 The data form

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:50 PM

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

25 In the third wizard screen, choose to use an SQL statement. The following illustration shows the statement that should be used.

PART III

Click Finish to generate the DataAdapter. Select Data | Generate DataSet, and name the DataSet as usersDS (as shown here).

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:51 PM

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

26 Create a second sqlDataAdapter using the SQL statement shown next.

Generate the DataSet from sqlDataAdtapter2 and combine it with the usersDS generated earlier, as shown here.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:51 PM

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

27 Now you need to bind the data from usersDS to the controls on the form. To perform the data binding, follow these steps: 1. Select Design view for the Web Form. 2. Select the txtFirstName control. 3. In the properties of the control, select the DataBindings setting and click the ellipsis button (...). The DataBindings dialog box will open. 4. In the Simple Binding box, expand usersDS.Users.DefaultView .DefaultView[0] to display the fields. Select the FirstName field. 5. Select the default formatting for the field, as shown here. PART III

6. Click OK Repeat the preceding steps for all the text box controls on the form, using the information in Table 16-4. To actually populate the controls, we need some extra code in the Page_Load event handler for the Web Form. First, we need to populate the DataSet by calling the Fill() method on the two DataAdapter objects, as shown here: sqlDataAdapter1.Fill(usersDS1, "Users"); sqlDataAdapter2.Fill(usersDS1, "Addresses");

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:51 PM

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

28 Table 16-4 The Settings for the Data Binding

Control

Table

Field

txtFirstName txtLastName txtEmailAddress txtAddress1 txtAddress2 txtCity txtProvince txtPostalCode txtCountry

Users Users Users Addresses Addresses Addresses Addresses Addresses Addresses

FirstName LastName EmailAddress Address1 Address2 City Province PostalCode Country

The Fill() method populates the DataSet (usersDS1) and the named table. The next step is to create a relationship between the two tables. For this, we create a Relations object, as shown in this code segment: usersDS1.Relations.Add("UserAddress", usersDS1.Tables["Users"].Columns["NameID"], usersDS1.Tables["Addresses"].Columns["NameID"]);

The Relations object will maintain the join between the two tables in the DataSet. This way, we only need to navigate the Users table and be able to retrieve the related address information from the Addresses table. Once the DataSet is created, the data binding can be finalized by calling the DataBind() method on the controls. The final version of the Page_Load() event handler is shown in the following code. private void Page_Load(object sender, System.EventArgs e) { // Put user code to initialize the page here sqlDataAdapter1.Fill(usersDS1, "Users"); sqlDataAdapter2.Fill(usersDS1, "Addresses"); usersDS1.Relations.Add("UserAddress", usersDS1.Tables["Users"].Columns["NameID"], usersDS1.Tables["Addresses"].Columns["NameID"]); txtFirstName.DataBind(); txtLastName.DataBind(); txtEmailAddress.DataBind(); txtAddress1.DataBind(); txtAddress2.DataBind(); txtCity.DataBind(); txtProvince.DataBind(); txtPostalCode.DataBind(); txtCountry.DataBind(); }

The final Web Form is shown in Figure 16-12.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:51 PM

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

29

PART III

Figure 16-12

The final AddressForm application

EXAM TIP The steps for binding controls to data are to use the Fill() method to populate the DataSet, and then call the DataBind() method on the controls that are bound to the data source.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:52 PM

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

30

Retrieving Real-Time Data In Chapter 12, we built an address book application that retrieved static information hard-coded into a switch statement. Now we will retrieve that data in real time through a sqlDataReader. Open up the AddressBook application from Chapter 12, or if you need a clean starter for this exercise, copy the AddressBook folder from the Chapter16 folder on the accompanying CD-ROM. The folder must be copied into the root folder of the web server, c:\InetPub\wwwroot. Open the project by double-clicking on the AddressBook .csproj file in the AddressBook folder. If you are prompted to save the AddressBook.sln file when you save the project, accept the suggested location and name of the file. The modifications that are needed to bind the application to a database are as follows: 1. In the codebehind module (WebForm1.aspx.cs) delete the entire switch statement from the getName() method. The resulting method should look like this: private void getName(char a) { clearList(); } 2. Import the System.Data and System.Data.SqlClient namespaces to the code module. 3. Declare an SqlConnection object as a local variable in the getName() method, and name it addrCN. 4. Declare a SqlCommand object as a local variable in the getName() method, and name it addrCom. 5. Declare a sqlDataReader object as a local variable in the getName() method, and name it addrDR. 6. Declare a string, call it strSQL, and make it a local variable in the getName() method. Initialize the strSQL string to the connection string for the database: string strSQL = "user id=sa;initial catalog=Address;" + "data source=KENSNABBEN"; 7. Declare a string, call it strCom, and make it a local variable in the getName() method. Initialize the strCom string to the following SQL statement: string strCom = "SELECT FirstName, LastName, NameID from Users WHERE LastName LIKE ";.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:52 PM

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

31 8. Instantiate the connection using the strSQL string. 9. Open the connection inside a try...catch ... finally block. 10. Declare a string called strCom1 that is the concatenation of strCom and the character that is the parameter to the method: strCom1 = strCom + "'" + a + "%'" The purpose is to create an SQL string that is delimited by single quotes and has the SQL wildcard character (%). 11. Instantiate the addrCom object using the strCom1 string and the addrCN connection.

13. Create a while loop that evaluates the addrDR.Read() method to get the next row of data. 14. In the loop, use the Add() method of the ListItems collection of the lstAddress ListBox control to insert a ListItem that concatenates the FirstName and LastName of the data row. Use the NameID as the value of the ListItem: new ListItem(addrDR.GetString(0)+ " " + addrDR.GetString(1), addrDR.GetInt32(2).ToString()) 15. Close all the objects in the finally block. The code for the getName() method should look like the following. private void getName(char a) { clearList(); SqlConnection addrCN; SqlCommand addrCom; SqlDataReader addrDR; string strSQL = "user id=sa;initial catalog=Address;" + "data source=KENSNABBEN"; string strCom = "SELECT FirstName, LastName, NameID from Users WHERE LastName LIKE "; addrCN = new SqlConnection(strSQL); try { addrCN.Open(); string strCom1 = strCom + "'" + a + "%'"; addrCom = new SqlCommand(strCom1, addrCN); addrDR = addrCom.ExecuteReader(); // The data population commands will go here. while (addrDR.Read())

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:52 PM

PART III

12. Create the sqlDataReader by calling the ExecuteReader() method of the addrCom object.

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

32 { lstAddress.Items.Add(new ListItem(addrDR.GetString(0)+ " " + addrDR.GetString(1), addrDR.GetInt32(2).ToString())); } } catch (DataException ex) { Response.Write(ex.ToString); } finally { addrDR.Close(); addrCN.Close(); addrDR = null; addrCom = null; addrCN = null; }

The project can now be executed by pressing F5. The window shown in Figure 16-13 appears. When you select one of the alphabet buttons, the content of the list box changes. At present, the same address is used for all names, as you can see if you select some names in the list box. We will attend to that next.

Figure 16-13

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:52 PM

The Address Book application

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

33 Looking Up the Address Info The final piece of the AddressBook application to attend to is looking up the address and phone number of a name when the name is selected in the list box. When we use two sqlDataReader objects with one connection, as we do in this example, we must close the first one before creating the second. Connections can only have one xxxDataReader object defined at one time. Follow these steps: 1. Delete the getAddress() method. 2. Delete the call to getAddress() in the lstAddress_SelectedIndexChanged() event handler.

4. Initialize the intIndex variable to the value from the SelectedItem lstAddress control. Use the Int32.Parse() method to convert the string into an int. This value will be used to retrieve the additional information for the selected name. 5. Declare one SqlConnection object, two SqlCommand objects, and two SqlDataReader objects. Call them addrCN, addrCom, phoneCom, addrDR, and phoneDR, respectively. 6. Declare a string and call it strSQL. Initialize strSQL to the connection string for the database: string strSQL = "user id=sa;initial catalog=Address;" + "data source=KENSNABBEN"; 7. Declare a string and call it strCom. Initialize the strCom string to the following SQL statement: string strCom = "SELECT Address1, Address2, City, Province, PostalCode, Country from Addresses WHERE NameID = " + intIndex; 8. Declare a string and call it strCom1. Initialize strCom1 to the following SQL statement: string strCom1 = "SELECT PhoneNumber FROM PhoneNumbers WHERE NameID = " + intIndex + " AND Type = 'Home'"; 9. Instantiate the connection using the strSQL string. 10. Open the connection inside a try ...catch ... finally block. 11. Instantiate the addrCom object using the strCom string and the addrCN connection.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:52 PM

PART III

3. Declare an int variable in lstAddress_SelectedIndexChanged(), and call it intIndex.

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

34 12. Instantiate the phoneCom object using the strCom1 string and the addrCN connection. 13. Create the sqlDataReader by calling the ExecuteReader() method of the addrCom object. 14. Call the Read() method of the sqlDataReader. 15. Populate the text boxes with the data returned from the sqlDataReader. 16. Close the addrDR sqlDataReader. 17. Create the sqlDataReader by calling the ExecuteReader() method of the phoneCom object. 18. Call the Read() method of the sqlDataReader. 19. Populate the phone number text box from the sqlDataReader. 20. Close the sqlDataReader. The final code for the lstAddress_SelectedIndexChanged() event handler should look like the following code segment. private void lstAddress_SelectedIndexChanged(object sender, System.EventArgs e) { txtName.Text=lstAddress.SelectedItem.Text; int lstIndex = Int32.Parse(lstAddress.SelectedItem.Value); SqlConnection addrCN; SqlCommand addrCom; SqlCommand phoneCom; SqlDataReader addrDR; SqlDataReader phoneDR; string strSQL = "user id=sa;initial catalog=Address;" + "data source=KENSNABBEN\\KEN"; string strCom = "SELECT Address1, Address2, City, Province, PostalCode, Country " + "FROM Addresses WHERE NameID = " + intIndex; string strCom1 = "SELECT PhoneNumber FROM PhoneNumbers WHERE NameID = " + intIndex + "AND Type = 'Home'"; addrCN = new SqlConnection(strSQL); try { addrCN.Open(); addrCom = new SqlCommand(strCom, addrCN); phoneCom = new SqlCommand(strCom1, addrCN); addrDR = addrCom.ExecuteReader(); addrDR.Read(); // The data population commands will go here. txtAddress1.Text = addrDR.GetString(0); txtAddress2.Text = addrDR.GetString(1); txtCity.Text = addrDR.GetString(2); txtProvince.Text = addrDR.GetString(3);

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:52 PM

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

35 txtPostalCode.Text = addrDR.GetString(4); txtCountry.Text = addrDR.GetString(5); addrDR.Close(); phoneDR = phoneCom.ExecuteReader(); phoneDR.Read(); txtPhoneNumber.Text = phoneDR.GetString(0); phoneDR.Close();

}

The final application is shown in Figure 16-14.

Figure 16-14

The final AddressBook application

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:53 PM

PART III

} catch (DataException ex) { Response.Write(ex.ToString()); } finally { addrCN.Close(); phoneDR = null; addrDR = null; addrCom = null; addrCN = null; }

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

36

Summary This chapter has worked with a number of objects from ADO.NET that provide connectivity between data sources and web applications. The basis for working with all data sources is the Connection object that abstracts the potential network and database technology so you do not have to have minute knowledge of how to build the connection. Once the connection is in place, the DataAdapter helps to build the SQL statements needed to build the DataSet objects that bind to such server controls as the DataGrid. The DataSet is a disconnected object, so once the DataSet is populated, the connection to the data source can be removed, making for less load on the server. The xxxDataReader is the connected version—it maintains the connection to the data source through the whole process. One thing to remember for the exam is that the SQL .NET Data Provider objects are used for Microsoft SQL Server 7 or higher, and that the OLE DB .NET Data Provider objects are used for any data source that has an OLE DB provider (but not for Microsoft SQL Server). Also, the try... catch... finally structure is tested in the exam.

Test Questions 1. What is the SQL equivalent of the DataSet relation object? A. XOR JOIN B. CROSS JOIN C. OUTER JOIN D. INNER JOIN 2. Why should you close all database objects and set them to NULL before leaving the method where the objects were created? A. To ensure that the object’s destructors are called. B. To ensure that the connection to the database is closed as soon as possible. C. To ensure that the objects can be reused. D. Good coding practice. 3. What is the purpose of the following code segment? if ( !IsPostBack ) { sqlDataAdapter1.Fill(dsUsers1); DataGrid1.DataBind(); }

A. To populate the DataAdapter the first time the web page is displayed. B. To populate the DataSet every time the web page is displayed. C. To populate the DataAdapter every time the web page is displayed. D. To populate the DataSet the first time the web page is displayed.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:53 PM

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

37 4. Where are stored procedures saved? A. The GAC. B. The web server. C. The database server. D. The central store. 5. What is the root exception for the ADO.NET objects? A. DataException B. ADOException C. Exception 6. What best describes the xxxDataReader? A. A disconnected collection of tables. B. A manager that manages the connection to a data source, using four SQL statements. C. A connected, read-only, forward-only representation of the data source. D. The component that encapsulates the database driver. 7. You are the developer of a Web Form, and you need to display data from a Microsoft SQL Server 6.5 in a DataGrid on your form. What DataAdapter is the most appropriate? A. sqlDataAdapter B. oleDbDataAdapter C. odbcDataAdapter D. adoDataAdapter 8. What is the purpose of the last string ("{0}") in the following code segment? DataBinder.Eval(dS1, "Tables[SP_SelUsers].DefaultView.[0].LastName", "{0}");

A. It is the formatting string for the bound data. B. It is the default value that will be used when the data is NULL. C. It is the parameter sent to the stored procedure SP_SelUsers. D. It is the placeholder for a DataBinding object. 9. What is the correct namespace for use with the SQL .NET Data Provider objects? A. System.SQL B. System.Data.SqlConnections C. System.Data.SqlClient D. System.SqlConections

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:53 PM

PART III

D. DBException

Color profile: Generic CMYK printer profile Composite Default All-In-One screen / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide

38 10. What is the correct statement to use for declaring that an xxxCommand object is used with a table? A. aCmd.CommandType = CommandType.Table; B. aCmd.CommandType = Table; C. aCmd.CommandType = "Table"; D. aCmd.CommandType = "CommandType.Table"; 11. How many sqlDataReader objects can be open on one Connection at one time? A. 4 B. 3 C. 2 D. 1 12. What is the SQL wildcard character? A. * B. % C. & D. @ 13. You need to connect to a Microsoft SQL Server version 6.5. What Connection object is the best choice? A. sqlConnection B. oleDbConnection C. ODBCConnection D. You must upgrade; there is no connection object for this database. 14. You are using the try... catch block seen in the following code segment, but no exceptions are ever caught by the catch block. What is the problem? sqlConnection cn =new sqlConnection(strSQL); sqlDataSet ds; try { cn.Open(); //perform the data processing steps ... }catch(OleDbException e){ ... }

A. The exception class is wrong; it should be SqlErrors. B. The exception class is wrong; it should be SqlSyntaxExceptions. C. The exception class is wrong; it should be SqlExceptions. D. The exception class is wrong; it should be SQLExcptions.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:53 PM

Color profile: Generic CMYK printer profile Composite Default screen All-In-One / MCAD/MCSD Visual C# .NET Certification All-in-One Exam Guide / Rempel & Lind / 222443-6 / Chapter

16

Chapter 16: Consuming and Manipulating Data from Data Sources

39 15. You are designing a Web Form that needs to have data available for as long as eight hours at a time. Your manager has raised some concern that the database server will be unable to provide services to a large number of connected users. What object in the ADO.NET architecture will you bring to your manager’s attention as a possible solution? A. SQL disconnected recordsets. B. oleDbDataReader C. ODBCRecordSet D. oleDbDataSet

1. D. 2. B. 3. D. 4. C. 5. A. 6. C. 7. B. 8. A. 9. C. 10. A. 11. D. 12. B. 13. B. 14. C. 15. D.

P:\010Comp\All-in-1\443-6\ch16.vp Friday, August 23, 2002 5:00:53 PM

PART III

Test Answers