Ch20

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

20

20

CHAPTER

Data in Windows Forms In this chapter, you will • Learn to bind data to the user interface • Get acquainted with displaying and updating data • Discover how to transform and filter data • Access and manipulate data from a Microsoft SQL Server database • Be introduced to handling data errors

This chapter deals with the data-specific portions of the C# Windows exam and assumes a good working knowledge of the information in Chapter 10. If you have not had a chance to review Chapter 10, which presented the concepts of the ADO.NET object model, now would be a good time. You will also find information pertinent to both the Windows and the web exam in Chapter 10. In order to prepare you for the C# Windows exam, we will now take those concepts and apply them to Windows Forms. We will access data from Microsoft SQL Server 2000 using our college application introduced in the last two chapters. In the past, Microsoft has tended to test heavily on the data side, and you can expect no less on the .NET exams. We will focus on two data-access techniques. The first takes advantage of the Visual Studio .NET support for accessing data—you will find a number of ADO.NET objects in the Toolbox that can be added to your form in order to assist with data access. The second technique involves coding using the ADO.NET object model—using objects such as Connection, Command, DataSet, and so on. These objects were introduced in Chapter 10, and we will use them in our Windows Form in this chapter.

Review of ADO.NET Object Model There are two main components in data access—data consumers, such as a Windows Form or a Web Form, and data providers, such as a DataAdapter or a DataReader. A data consumer will request data from a data provider (such as Microsoft SQL Server). Table 20-1 describes the major objects that provide data to a data consumer.

1 P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:17 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

20

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

2 Description

Connection

Command

DataAdapter

DataReader DataSet

Table 20-1

Represents a physical connection to a specified data source. After providing the necessary properties for the data provider (data source, database, login credentials, and so on), you can then use the open and close connection methods of the Connection object to establish the connection. You can also manage transactions using the Connection object. Review Chapter 10 for information on database transactions. Creates the command that is executed on the data source. This includes SQL SELECT statements or stored procedures (which are sets of instructions found at the data source). Populates a data set and restores updates with the data source. Essentially, this object provides the link between a Connection object and the resulting data set. Reads a forward-only, read-only stream of data from a data source. Represents a “disconnected” set of data. Data can be read from multiple sources (or from a single source) and represented offline as a DataSet. A DataSet is unaware of the source of the data and, in effect, is an in-memory representation of the selected data.

Common Data Objects

EXAM TIP Commit the objects in Table 20-1 to memory. You will be tested on your knowledge of each of these objects. There are a number of classes that assist with data representation, and they are independent of the source of the data (whether it be SQL Server, Oracle, or something else). These classes include DataRow and DataColumn, which as their names imply, represent rows and columns from a table (or spreadsheet), respectively. There is also a DataTable class, which represents the collection of row and column data, and a DataRelation class, which acts as a link between two DataTable objects. You can consider a DataTable to be a collection of DataRow objects, and a DataRelation to be the foreign-key representation between two DataTable objects. EXAM TIP Be sure that you understand the source-specific classes for setting up the connection to your data. These include SqlCommand and SqlConnection for specifying the SQL statements or stored procedures and the connection parameters when accessing Microsoft SQL Server 7.0 (and higher) databases. In order to connect to an OLE/DB provider other than Microsoft SQL Server 7.0 (and higher), you would use OleDbCommand and OleDbConnection. The classes specific to these two types of data stores are included in the ADO.NET object model. If you wish to access data providers other than these types, you must download the ODBC classes from Microsoft.

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:18 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

20

Chapter 20: Data in Windows Forms

3

Accessing Data Using Visual Studio .NET In this chapter we will present data-access concepts and applications using data retrieved from a Microsoft SQL Server, but you should keep in mind that Visual C# and, in particular, the .NET Framework, allows you to access data from any source. Sources include Microsoft SQL Server, Microsoft Access, Oracle databases, flat files, and so on. That data can then, in turn, be bound to objects found in ADO.NET, such as a DataSet. The data can be bound to any number of controls that support a data source property. It can also be bound to arrays and collections. The Visual Studio .NET interface includes many built-in controls that allow you to create a link to a data source and then bind controls to the data retrieved from that source. In this section, we will look at those controls and work with them to present a Windows Form that contains data from our SQL Server database—College.

Figure 20-1

The College database

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:18 PM

PART III

The College Application Our college application was developed in the last two chapters and will be expanded to include a visual presentation of the data found in our database. Figure 20-1 shows the schematic of the College database, which includes three tables—Student, Program, and Course. The Student table includes information about the students in our sample college, including the program in which they are enrolled. The program is linked to the Program table for a description and to the Course table for the list of courses that a student must take to complete the program. Using this simple database, we will be able to demonstrate the capabilities of data access, along with some relational aspects.

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

20

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

4 At the end of this chapter, you will have a workable model of this application using programmatic access to data. However, for the purposes of this section of the chapter, we will deal with the built-in controls that provide “wizard-like” assistance when connecting to the database and presenting the resulting data. You should keep in mind that, while this technique is quite powerful and certainly reduces the amount of time necessary to build easy applications, a lot of data-driven applications are created by coding the data access directly, without the benefit of prebuilt controls or visual components. In the “Accessing Data Programmatically” section of this chapter, we will work with the classes in ADO.NET programmatically and bind the controls on our Windows Form to data.

ADO.NET Objects in Visual Studio .NET Let’s start by examining the Toolbox in Visual Studio .NET. You will notice in Figure 20-2 that there are many different objects that will provide assistance when retrieving data from a data source. In order to get access to this Toolbox, click on the Data tab. Figure 20-2 The ADO.NET Toolbox

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:18 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

20

Chapter 20: Data in Windows Forms

5 Double-click on the SqlDataAdapter, which opens the Data Adapter Configuration Wizard. You will be presented with the Welcome screen, which is the first page of the wizard. The wizard allows you to graphically specify the connection information—database server, database name, authentication data, and so on. You start by selecting either a preconfigured or new connection as shown in Figure 20-3. For this example, click the New Connection button. The next wizard screen asks you to configure the data link properties (see Figure 20-4). Select the appropriate server name from the drop-down list, provide the needed logon credentials, and choose the database from the list. Notice that the database list will not appear if either the server or the credentials are incorrect.

You also have an opportunity to change the provider using the Provider tab of the Data Link Properties window (see Figure 20-5). Take a moment to investigate the different data providers offered by default within Visual Studio .NET. Should you have need of any other data providers, you will have to download it from either Microsoft’s web site or the vendor’s web site.

Figure 20-3 Choose your data connection

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:18 PM

PART III

TIP Be sure to test the connection. The wizard provides you with a Test Connection button that will ensure that a connection can be made to the database 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

20

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

6 Figure 20-4 Set the data link properties

Figure 20-5 Set a data provider

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:19 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

20

Chapter 20: Data in Windows Forms

7 Once you have finished setting the data link properties, click Next in the Provider tab. The Choose a Query Type wizard window will be displayed (see Figure 20-6). You can choose from the following options: • Use SQL statements These are SQL statements that you code yourself, and they could be SELECT, UPDATE, DELETE, or INSERT statements. • Create new stored procedures procedures.

This lets you create your own stored

• Use existing stored procedures This allows you to use stored procedures that already reside on the database server.

• Simply type your SQL statement in the box provided. • Use the Query Builder which is a wizard-like interface for SQL assistance. • Go to the Advanced Options (see Figure 20-8) to have UPDATE, DELETE, and INSERT statements automatically generated, based on your SELECT statement. EXAM TIP You should be very comfortable with the standard SQL statements. Review Chapter 10 for SELECT, UPDATE, DELETE, and INSERT statements.

Figure 20-6 Choose a query type

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:19 PM

PART III

For this example, choose to use your own SQL statements. Click the Next button, and the wizard will ask you to type in the SQL statement (see Figure 20-7). You have three choices when presented with this wizard window:

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

20

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

8 Figure 20-7 Generate your SQL statements

For the purposes of this example, turn off the automatic generation of SQL statements offered in the Advanced options by unchecking the Generate Insert, Update, and Delete Statements check box. If you select the Query Builder, the Add Table dialog box opens (see Figure 20-9), displaying a list of the tables contained in the database that was specified in the Data Link Properties dialog box. Select the tables you wish to add (for this example, choose the Student table), and the Query Builder will provide you with a graphical interface for completing your SQL statements, as shown in Figure 20-10. The tables you add can then be used in any query string that you build through the Query Builder.

Figure 20-8 The advanced options

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:19 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

20

Chapter 20: Data in Windows Forms

9 Figure 20-9 The Add Table dialog box

PART III

Figure 20-10

The Query Builder

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:20 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

20

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

10 Notice in Figure 20-10 that the Student table columns are listed in the top pane, and you can choose to use all the columns or just specific columns from that pane by checking the appropriate check boxes. When you make your choices (for this example, choose Student#, FirstName, LastName, and Program#), the SQL statement in the third pane down reflects your choice, and in this case displays the following statement: SELECT Student#, FirstName, LastName, Program# FROM Student

You can also specify the sort order and selection criteria in the second pane. To test the command, right-click in the bottom pane and select Run. The command will be run against the connection to the database, and the results will be shown in the bottom pane. In this example (shown in Figure 20-10), we have asked that LastName be sorted ascending, then FirstName sorted within LastName. We also specified that the FirstName must be equal to ‘Jim’. As you can see in the figure, this command returned only one record where the first name is ‘Jim’. The corresponding SQL statement has been generated for us: SELECT Student#, FirstName, LastName, Program# FROM Student WHERE (FirstName = 'Jim') ORDER BY LastName, FirstName

Once you have tested the command, click OK in the Query Builder, and click Next in the Data Adapter Configuration Wizard. The wizard then uses the information that you provided to generate the desired command and connection statements. You will be shown a window that verifies the success or failure of the exercise (see Figure 20-11). When you are satisfied with the results, click Finish. You will be returned to your form. Notice in Figure 20-12 that the form now has two objects associated with it— sqlDataAdapter1 and sqlConnection1. Figure 20-11 Configuration Wizard results

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:20 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

20

Chapter 20: Data in Windows Forms

11

PART III

Figure 20-12

The form with the data adapter and connection

You have now created two objects that represent the execution instructions and the physical connection to the data store. The next step is to create an object that will represent the data—a DataSet. There are two ways to accomplish this: • Select Data | Generate Dataset from the menus. • Right-click on the sqlDataAdapter1 object and select Generate Dataset. In either case the Generate Dataset dialog box will be displayed (see Figure 20-13). You can select either an existing dataset or create a new object. For this example, choose a new DataSet object and call it dsStudent to represent the table from which the data is retrieved. The new DataSet object (dsStudent1) is created and added to the form along with the DataAdapter and Connection objects (at the bottom of the screen). EXAM TIP To summarize, you use an xxxDataAdapter (where xxx represents SQL or OLE), which encapsulates Connection, Command, and DataReader objects in order to populate a DataSet. The DataSet is then used to represent the rows and columns (or a table) from the database. Using the DataAdapter, you can update the data source through the DataSet.

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:20 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

20

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

12 Figure 20-13 Creating a new DataSet object

Using the DataGrid Now that you have a DataSet, you’ll need to display the data on your form. To do this, we’ll start with a control that is built to display rows and columns of data—the DataGrid. From the Toolbox, select the DataGrid and drag it onto the form. Figure 20-14 shows a runtime DataGrid control filled with data. Although you do not need to be aware of the power of the DataGrid control for the Microsoft exams, you should take some time to explore its properties, methods, and events in order to fully appreciate it. NOTE Your output may not look exactly like Figure 20-14. As a matter of fact, unless you have read ahead, you will notice that you have no data from the database displayed. You must manually put data into the DataSet using the Fill method of the DataAdapter. We will cover this in the next section. Figure 20-14 The DataGrid control

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:21 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

20

Chapter 20: Data in Windows Forms

13 You can set two properties for the DataGrid control in order to have it display data. The first property is the DataSource, which specifies the DataSet object that represents the retrieved data. The second property is the DataMember, which can be used to specify the single table relationship. If a DataSource represents a DataSet created by combining multiple tables, the DataMember must specify the table to which the DataGrid is bound. EXAM TIP Be sure to know the difference between the DataSource and DataMember properties.

DataGridTableStyle myStyle = new DataGridTableStyle(); myStyle.GridLineStyle = DataGridLineStyel.None;

You can set the column style by using the DataGrid properties, or programmatically by using a DataGridTextBoxColumn object: DataGridTextBoxColumn myColumns = new DataGridTextBoxColumn(); myColumns.Width = 100;

You can also set properties such as Width, Alignment, HeaderText, and so on. Once the desired properties have been set, you can then add the DataGridTextBoxColumn to the GridColumnStyles collection as follows: myStyle.GridColumnStyles.Add (myColumns);

Finally, you’ll need to add the table style to the data grid: dataGrid1.TableStyles.Add (myStyle);

You can also use the AutoFormat tool to select a preset format for your DataGrid. Right-click on the DataGrid on your form, and select AutoFormat. Figure 20-15 shows the Auto Format dialog box that appears. The DataGrid control can also be used to show the relationship between two related tables. Here’s what you would do: 1. Create a DataSet that represents the related tables. 2. Add a DataControl for each related table to your form. 3. Create a DataRelation object that will represent the relationship between the tables. Tie the tables together by setting the relationship properties (table, column, and so on).

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:21 PM

PART III

There are a few important characteristics of a DataGrid control that you should know about. The first is that in order to format the display of data in a manner other than the default display shown in Figure 20-14, you need to work with the properties of the DataGrid, such as GridLineStyle, which can be set to Solid (the default) or None. This can be done programmatically by creating a DataGridTableStyle object and setting its properties to the display you need:

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

20

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

14 Figure 20-15 The Auto Format dialog box

4. Using the DataSet object’s Relation collection, add the DataRelation. 5. Bind the grids to the DataSet (use the SetDataBinding() method of the DataGrid). If you wish to experiment with this process using the DataGrid, follow the preceding steps. We will explore table relationships later in this chapter, and we’ll look at how to relate them programmatically.

Loading the Data If you have been following along and creating the sample application in this chapter, you are probably disappointed that your data does not show on the form as it did in Figure 20-14. This is because you still have to manually load the data. This may be new to you (particularly if you have programmed under any earlier ADO object models). ADO.NET does not assume that there is a full-time connection to the data store. In order to retrieve your data, you must manually load the data into the DataSet. EXAM TIP To manually load data, you use the Fill() method of the DataAdapter. To produce the output that you saw in Figure 20-14 you need to load the data into the DataSet by using the DataAdapter. Where in the application you do this is an important consideration. You don’t want to do it too early, because you will be wasting resources before they are actually needed, and you don’t want to do it too late, because the process may take some time. Take time to determine the appropriate time to access the actual data. For the purpose of demonstration only, we will add the following code to the load event of the form: private void Form1_Load(object sender, System.EventArgs e) { sqlDataAdapter1.Fill(dsStudent1); }

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:21 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

20

Chapter 20: Data in Windows Forms

15 Now when you run this application, you will see the actual data retrieved and displayed on your form (as in Figure 20-14).

Using Stored Procedures

Figure 20-16 The Create the Stored Procedures screen of the Data Adapter Configuration Wizard

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:21 PM

PART III

The C# Windows exam has as one of its objectives, “Access and manipulate data from a Microsoft SQL Server database by creating and using ad hoc queries and stored procedures.” We have covered the ad hoc queries using ADO.NET objects in Visual Studio. Let’s take time now to look at using stored procedures, which are bits of executable code that reside on the database server itself. If you need a review of the purpose and intent of using stored procedures, please refer back to Chapter 10. When you add the xxxDataAdapter to your form (as described earlier in the “ADO.NET Objects in Visual Studio .NET” section), you are taken into the Data Adapter Configuration Wizard. The third screen (shown earlier in Figure 20-6) asks whether you will be creating the SQL code or using stored procedures. For this example, we will take you through the steps of creating a new stored procedure; however, by selecting Use Existing Stored Procedures in that window, you can access code that has been created in advance and stored on the database server. To create a new stored procedure, select the Create New Stored Procedures option and click Next. You will be taken through the steps of building a query as we saw in the “ADO.NET Objects in Visual Studio .NET” section (Figures 20-9 and 20-10). Once your SQL statements are created, you will be taken to the Create the Stored Procedures screen (Figure 20-16).

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

20

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

16 As you can see from the figure, you have several choices. You should give meaningful names to the different SQL statements (SELECT, UPDATE, DELETE, INSERT). You can also ask that the wizard create the stored procedure for you, or you can specify that you will create it yourself. By clicking the Preview SQL Script button, you can see the procedure that has been created (see Figure 20-17). Once you accept your code, you will see the wizard’s Finish window, and your data adapter will have been made a stored procedure instead of it being an on the fly or ad hoc query.

What’s Next? So far we have spent very little time coding. Although this is a very attractive feature of the Visual Studio .NET environment, it may not necessarily provide you with the flexibility that you need when creating data access. However, in this section, we have covered the major topics that deal with ADO.NET objects in Visual Studio. EXAM TIP What you have seen so far covers the extent to which Microsoft will test you on Visual Studio ADO.NET objects. As any good programmer knows, though, you should spend more time investigating the power of the development environment and the many classes that have been built to assist with data retrieval and presentation. The remainder of this chapter will be devoted to programmatically retrieving and displaying data. We will discuss such features as data bindings, formatting, and parsing data. Our goal is to create the form that you see in Figure 20-18, which retrieves data from the SQL Server and presents it in text boxes that have been bound to the data. Figure 20-17 The Preview window

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:22 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

20

Chapter 20: Data in Windows Forms

17

PART III

Figure 20-18

The college application form

Data Binding Just what is data binding? Obviously, as its name implies, it is the process of linking data with something. In prior versions of Microsoft data-object models, you could “bind” data to a control that had data source, data member, and data field properties. For example, a text box could be programmed to contain data from an SQL Server data source, a SELECT statement, and a particular field from the SELECT statement. Although this technique is slightly different in .NET, there are many more possibilities with Windows Forms using the .NET Framework. The traditional data sources (such as a database) can be used, as well as any object that implements data-binding interfaces—IList, IBindingList, or IEditableObject. Fortunately, you do not need to know any of this for the Microsoft exams, but the possibilities of this new approach are virtually endless. As an example, you can create a collection of values at run time that can then be used to bind to any property of a control. EXAM TIP For the C# Windows exam, be very aware of the fact that you are not limited to binding to controls that traditionally had “bindable” properties, such as DataSource. A Windows Form control can be bound to virtually any data source. You also do not have to bind the data to a visual property (like the Text property of a TextBox).

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:22 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

20

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

18 The Theory of Data Binding Data binding involves linking properties of a control to a data source. Data can be bound to both Windows Forms controls as well as Web Forms controls. For a complete look at Web Forms and data binding, refer to Chapter 16. There are a number of objects that work together to produce data binding. Two important objects are BindingContext and CurrencyManager: • BindingContext—Every form has a BindingContext object associated with it. It acts like the manager of all CurrencyManager objects. • CurrencyManager is associated with every data source that you create. The CurrencyManager is responsible for managing all the bindings to that data source. The .NET Framework allows you to use two different techniques for data binding: • Simple binding is used when a control can bind to a single value. • Complex binding is used when the control is capable of holding multiple values. In this section, we will look at the two different kinds of binding and at setting properties both at design time and run time. EXAM TIP Microsoft will test you on your knowledge of data binding. Pay particular attention in this section to binding programmatically to a data source.

Binding Data at Design Time Binding data at design time means that you can use the integrated development environment and the Property Explorer to set the binding properties of a control.

Simple Binding at Design Time Let’s take our college application (shown in Figure 20-18) and bind the data source to the text boxes on the form. In the steps that follow, we will work with the Student # text box, but you can set the properties of every text control in a similar fashion. 1. Click on the Student # text box. 2. Right-click to display the Properties Explorer. 3. Expand the DataBindings section by clicking the plus (+) sign to the left.

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:22 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

20

Chapter 20: Data in Windows Forms

19 4. Click the ellipsis button (the button with the three dots) beside the Advanced field, and you will see the Advanced Data Binding dialog box shown in Figure 20-19. Notice that the list of properties is large—remember, it’s not just the traditional properties that can be bound to data. 5. Select the Text property and click the ellipsis button in order to select the data source and the column of data to use (see Figure 20-20). As a shortcut approach for simple binding, you can just set the DataBindings Text property in the Property Explorer. Once you close the Advanced Data Binding dialog box, you can run the application and see that the Student# field from the Student table is displayed in the correct text PART III

Figure 20-19

The Advanced Data Binding dialog box

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:23 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

20

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

20 Figure 20-20 Setting the Text property

box. You can do the same thing for all of the text boxes on the form to display all the information about the first student in the table (see Figure 20-21). TIP If you don’t see the data, don’t forget that you must manually populate the DataSet.

Complex Binding at Design Time Let’s look now at how to bind data to a more complex type of control, like the ListBox or ComboBox. Both of these controls are capable of displaying more than one record from the database. In our college application, we have a Courses tab page, which when programmed correctly should display the courses related to the student’s program. However, since we haven’t discussed table relationships yet, we will simply add a ListBox to the page and populate it with course names, as in Figure 20-22. In order to display multiple rows from a database, you should set the DataSource and DisplayMember properties of the ListBox control as follows: • DataSource: Choose the DataSet object that represents the table from which the data will be extracted. • DisplayMember: Select the field (or column) or data that should be displayed.

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:23 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

20

Chapter 20: Data in Windows Forms

21

PART III

Figure 20-21

Simple data binding

EXAM TIP Study the difference between DataSource and DisplayMember carefully. You may find a question on the exam regarding the difference between the two.

Binding Data at Run Time Although it is nice and easy to bind data at design time, the reality is that you will usually be binding data at run time. A user of your application may trigger an event or request data based on selection parameters, sort criteria, and the like. You must be prepared to display the data at a requested time rather than at your specific time (i.e. design time). In order to access and display data at run time, you will need to code using the ADO.NET objects.

Simple Binding at Run Time In our college application, we have removed the DataBindings collection’s Text property and are now ready to bind to the data dynamically. A View Students button

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:23 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

20

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

22 Figure 20-22 The college application’s Courses page

has been added to the form, and the user clicks that button to display the data (see Figure 20-23). To link our data source and the actual control, you can instantiate a Binding object, as shown here: Binding StudentBinding = new Binding ("Text", this.dsStudents1, "Student.Student#");

EXAM TIP The Binding constructor takes the control’s property as its first parameter and the data source and column as its second and third parameters. After the Binding object has been instantiated, you need to add it to the DataBindings collection of the control (in this case, the TextBox control): this.txtStudentNo.DataBindings.Add (StudentBinding);

You can save yourself programming space and typing by simply sending the Binding parameters to the Add() method of the DataBindings collection. The following code listing demonstrates how to populate each of the text boxes on the form shown in Figure 20-23. this.txtStudentNo.DataBindings.Add ("Text", this.dsStudents1, "Student.Student#"); this.txtFirstName.DataBindings.Add ("Text", this.dsStudents1, "Student.FirstName"); this.txtLastName.DataBindings.Add ("Text", this.dsStudents1, "Student.LastName");

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:24 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

20

Chapter 20: Data in Windows Forms

23 this.txtAddress.DataBindings.Add ("Text", this.dsStudents1, "Student.Address"); this.txtCity.DataBindings.Add ("Text", this.dsStudents1, "Student.City"); this.txtProvState.DataBindings.Add ("Text", this.dsStudents1, "Student.ProvState"); this.txtProgram.DataBindings.Add ("Text", this.dsStudents1, "Student.Program#"); this.txtEmail.DataBindings.Add ("Text", this.dsStudents1, "Student.Email"); this.txtPhone.DataBindings.Add ("Text", this.dsStudents1, "Student.HomePhone"); this.txtNotes.DataBindings.Add ("Text", this.dsStudents1, "Student.Notes");

EXAM TIP You do not always have to provide the table name as the third parameter of the Add() method; however, it is necessary if you have more than one DataTable object (such as a DataSet).

Complex Binding at Run Time

Figure 20-23

Displaying data dynamically using simple binding at run time

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:24 PM

PART III

The preceding section explains how to bind to controls that display a single row of data. We also need to understand how to bind data to controls that are capable of displaying multiple rows of data, like the DataGrid. As we saw in the previous “Simple Binding

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

20

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

24 at Run Time” section, the properties of interest are the DataSource and the DataMember. We can add the following code after the declaration of a list box: dgStudents.DataSource = dsStudent; dgStudents.DataMember = "FirstName";

This code will cause the list box to display all records represented by the data source, but only the FirstName column. EXAM TIP You can also use the SetDataBinding method of the DataGrid object to bind both the DataSource and DataMember properties. A DataGrid is also considered a complex control in terms of data binding. Another thing you can do when displaying data is display a value that is related to but different from the actual bound data. For example, you may wish to provide a user-friendly display of a primary key. The actual primary key value may be an automatically generated number that really means nothing except as a unique key. By using the ValueMember property of some controls, you can display the name associated with the primary key and still bind the primary key field for programmatic usage.

BindingContext and CurrencyManager So far we have discussed ways of displaying data retrieved from a data source. However, we should also explore methods of synchronizing the data with the back-end data source. Remember that a DataSet is totally unaware of the data source and therefore has no idea of the actual location of the source records. Earlier in this chapter, we said that every Windows Form has a CurrencyManager object with which it is associated. The CurrencyManager takes care of knowing the position of the source records in the data source and can inform the control hosting the data when it changes. Some of the important properties of the CurrencyManager object are listed in Table 20-2. In the college application, you can use the CurrencyManager and BindingContext objects to retrieve information. Recall that a BindingContext object exists to manage Property

Explanation

Current Position List Bindings Count

Contains the value of the current item in the data source. Returns the position of the current object in the data source. Returns the data source. Returns the collection of bindings that the CurrencyManager manages. Returns the number of rows that are being managed.

Table 20-2

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:25 PM

Properties of the CurrencyManager

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

20

Chapter 20: Data in Windows Forms

25 all the CurrencyManager objects, and there is one CurrencyManager object for every data source. Consider the following code: MessageBox.Show ("We are at position " + this.BindingContext[dsStudents1, "Student.FirstName"].Position.ToString()); CurrencyManager cm; cm = (CurrencyManager)this.BindingContext[dsStudents1, "Student.FirstName"]; MessageBox.Show ("There are " + cm.Count.ToString() + " rows.");

The output with the first MessageBox is shown in Figure 20-24. EXAM TIP

Notice that the Position property is zero-based.

Figure 20-24

A MessageBox displaying the BindingContext object’s Position property

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:25 PM

PART III

The reason this works is that the code this.txtXXX.DataBindings.Add(…) creates a new instance of the Binding object and adds it to the Bindings collection.

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

20

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

26

Exception Handling Any application that accesses data needs the ability to prevent run-time errors from happening. Consider the following scenario. You design a Windows Form like the college application, and the database server is in a remote location from the user of the Windows Forms (which is a very typical arrangement). For some reason, the connection to the database server fails. What happens to your application? If you have followed good programming practices, nothing terminal should happen. Obviously, the connection failure is a problem for the user, since the live connection to the data is now disconnected. However, your application should not terminate with some cryptic message generated by the internal exception-handling mechanism. Instead, you want to trap any exceptions and handle them properly. (Refer to Chapter 4 for the basics of exception handling.) What you need to do is enclose your code in try … catch blocks to make sure that any exceptions that are beyond your control are handled. As a reminder, here is what happens with good exception-handling code: 1. The code instructions that could potentially cause an exception to be thrown (for example, connecting to the database) are enclosed in a try … catch … finally block. 2. The try section says “try this code—if it works without an exception being thrown, move to the finally block.” 3. If an exception occurs, execution moves to the catch blocks and checks whether the exception is listed. If so, the code within that block is executed. Once the exception code has been executed, execution moves to the finally block and the code within that block is run. EXAM TIP The finally block always executes (unless the power plug has been pulled!). The type of exceptions that can occur when working with data are shown in Table 20-3. All exceptions found in ADO.NET have System.Data.DataException as their base class. Exception

Explanation

ConstraintException

This exception indicates that a constraint has been violated. For example, a primary key record was deleted when foreign key records still exist. This is the base class. This exception means database write conflicts have occurred. For example, this could happen if one user is updating the database while another is trying to.

DataException DBConcurrencyException

Table 20-3

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:25 PM

Classes Derived from System.Data.DataException

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

20

Chapter 20: Data in Windows Forms

27 Exception

Explanation

DuplicatedNameException

This exception happens when an object in the database is given a name that already exists. This exception occurs when an INSERT into the database contains a NULL value that is not allowed. These are general exceptions for the three different data providers.

NoNullAllowedException ODBCException OleDbException SqlException ReadOnlyException SyntaxErrorException

Classes Derived from System.Data.DataException (continued)

Although we have not tried to access data programmatically in our application so far, the code section found in Figure 20-25 demonstrates good practice when connecting to a database. The try block attempts to create the connection and “tests” it using a try block. If the connection cannot be made, the catch block catches the exception. Without this coding, we would receive the message shown in Figure 20-26, which forces the user to ignore the message or quit the application. If the connection fails, the code within the catch block executes (assuming an SqlException has been thrown).

Figure 20-25

Coding that handles exceptions

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:26 PM

PART III

Table 20-4

This exception means an attempt to update read-only data has occurred. This exception happens when a call to SQL instructions contains syntax errors.

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

20

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

28 Figure 20-26 Unhandled exception

Although we have done nothing exciting within the catch block in this example, in a real-life scenario you would provide code that would either prompt the user for additional credentials, or prevent the user from accessing the database, or do whatever is necessary to gracefully exit the error condition. Figures 20-27, 20-28, and 20-29 show the sequence of events when an SqlException occurs in the code shown in Figure 20-25. Notice that the finally block is executed too. In Figure 20-29, you can see that we used the exception object that is created (SqlException s), and then we displayed its Message property to provide a user-friendly message regarding the status of the exception. EXAM TIP You can create your own data-access exceptions—be sure that they inherit from System.Data.DataException. Some of the properties of the exception object are listed in Table 20-4. From this point on in our discussions about data, we will be following this convention of catching data exceptions and providing the coding to handle them.

Accessing Data Programmatically As promised, we will now move our discussion from Visual Studio .NET data controls and wizards to programmatic coding for data access. As mentioned before, accessing data using specialized and prebuilt functions is very valuable, but it may be impractical in some circumstances. For example, you may find that the limitations of a particular control stop you from providing a desired service. In that case, you will need to be able to create your own connections, commands, data adapters, and other objects.

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:26 PM

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

20

Chapter 20: Data in Windows Forms

29 Figure 20-27 The try block— the first message box

Figure 20-28 The try block— using the exception object

PART III

Figure 20-29 The finally block

Description

HelpLink InnerException Message Source StackTrace Table 20-4

Provides a link to a help file giving more information about the exception. Contains the exception object that caused the code to enter the catch block. This is used if the exception happened inside a catch block. Provides a text message to give more information about the exception to the user. Provides the name of the object that caused the exception. Provides a means of viewing the call stack (the list of methods in execution order).

Properties of the Exception Object

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:26 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

20

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

30 Creating a Connection Object The connection object allows us to connect and authenticate against a data source. As you know from Chapter 10, there are two different connection classes associated with ADO.NET—SqlConnection and OleDbConnection. In this chapter, we will deal with the SqlConnection object, since it is the source of many more questions on the Microsoft exams. EXAM TIP The SqlConnection class is used for accessing Microsoft SQL Server 7 (and higher). To access an earlier version of SQL Server, you will need to use a different data source provider class. When you instantiate an SqlConnection object, you must provide the connection parameters to the constructor. The best way to handle this is to create a string object first, and then pass the string object to the SqlConnection constructor, like this: string connString = "data source=mrlaptop;initial catalog=College;user id=sa;password;"

The connection string is very similar to those for the ADO or OLE-DB providers in version 6 of Visual C++ or Visual Basic. It consists of three components: • data source—the server location of the database. If the server was located somewhere other than on the client, the entry would be something like this: "data source=\\SQLServer" • initial catalog—the name of the database. • user id and password—the log in credentials. You could specify integrated security instead of providing the userid and password within the connection string. In that case, you would code "integrated security". The connection object can be thought of as the bridge between your application and a data provider. Connection strings vary slightly depending on the data provider (OLE-DB, ODBC, and so on). Once the string is created, you then pass it to the connection constructor as follows: SqlConnection myConnection = new SQLConnection (connString);

By using this method, you can modify the connection string at any time without touching the actual creation of the connection object. All that’s left to do now is to open the connection, which, essentially, provides us with a link to the data source. No command is being run against the data in the following code, no data is transferred—this simply creates a pipeline to the data. try { MyConnection.Open(); } catch

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:26 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

20

Chapter 20: Data in Windows Forms

31 { … Code your exception handling here }

Be sure to close the connection when you are finished with it. The garbage collector can then destroy the object and free the memory space: try { MyConnection.Close(); } catch { … Etc… }

SqlCommand MyCommand = new SqlCommand();

You can also use the connection object to create the command object. The connection object has a CreateCommand() method that you can use to instantiate your command object. comString = "SELECT * FROM Student"; SqlCommand MyCommand = MyConnection.CreateCommand();

Once the command object is created, you can use the execute methods associated with an SqlCommand object: SqlDataReader myReader = myCommand.ExecuteReader();

The execute methods include ExecuteScalar() (a command that returns a single value), ExecuteReader() (a command that returns a set of rows), ExecuteNonQuery() (a command that affects the database but only returns the number of rows affected), and ExecuteXmlReader (a command that returns XML data). EXAM TIP or later.

ExecuteXmlReader() is used for Microsoft SQL Server 7.0

Some of the properties of a command object are listed in Table 20-5.

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:27 PM

PART III

Creating a Command Object The next step is to create the object that encapsulates the code that you want executed on the data source. This could be a stored procedure on the database server or a configured SQL statement located in your code. Either way, you need a Command object to do this for you. The command object will use the connection object to access the database (or it can provide its own connection data). Once the command is executed against the data source, the data is returned to the user in the form of a stream (read by DataReader objects) or a DataSet object. The following code is used to instantiate a command object:

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

20

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

32 Property

Description

CommandType CommandText

The type can be one of StoredProcedure, Text, or TableDirect. This is the name of the stored procedure, the actual SQL statement, or the name of the table. This specifies the connection object (or information) required to access the data source. This is a programmatic name that you can use within your program to refer to the command object. These are the parameters associated with the stored procedure.

Connection Name Parameters Table 20-5

Some Properties of the Command Object

Figure 20-30 shows code that instantiates and uses a command object.

DataAdapter and DataSet We can now create a DataAdapter and a DataSet in order to get ready to bind our data to the controls on the form. Recall that a DataAdapter is a link between the data source and a DataSet. The DataSet is a representation (or local copy) of the actual data from the data source. These two objects work hand-in-hand to provide a link to the actual data. Let’s start with the DataAdapter. There are two data adapters—SqlDataAdapter and OleDbDataAdapter. Since we are connecting to Microsoft SQL Server 7.0 or higher, we will use the SqlDataAdapter. Create a new DataAdapter using the command object: SqlCommand studentCommand = new SqlCommand(); studentCommand.CommandType = CommandType.Text; studentCommand.CommandText = "SELECT * FROM Student"; SqlDataAdapter studentAdapter = new SqlDataAdapter(studentCommand);

We can then use this data adapter to create the DataSet object that will be a copy of the actual data retrieved through the command object: DataSet studentSet = new DataSet(); studentAdapter.Fill (studentSet, "FirstName");

Figure 20-27

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:27 PM

Creating a command object

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

20

Chapter 20: Data in Windows Forms

33 You can now use all of the binding techniques that you learned earlier in this chapter to bind the data from the data set to any control on your form. The following code listing shows the full code required to fill the form (Figure 20-18) with data retrieved from the Microsoft SQL Server database.

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:27 PM

PART III

using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace WindowsApplication2 { public class Form1 : System.Windows.Forms.Form { private System.Windows.Forms.Button button1; protected SqlConnection studentConnection; protected SqlCommand studentCommand; protected SqlDataAdapter studentAdapter; protected DataSet studentSet; private System.Windows.Forms.Button btnGetData; private System.Windows.Forms.TextBox txtFirstName; private System.ComponentModel.Container components = null; public Form1() { InitializeComponent(); } protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose(); } } base.Dispose( disposing ); } [STAThread] static void Main() { Application.Run(new Form1()); } private void InitializeComponent() { this.btnGetData = new System.Windows.Forms.Button(); this.txtFirstName = new System.Windows.Forms.TextBox(); this.SuspendLayout(); // // btnGetData // this.btnGetData.Location = new System.Drawing.Point(97, 124); this.btnGetData.Name = "btnGetData"; this.btnGetData.TabIndex = 0; this.btnGetData.Text = "GetData"; this.btnGetData.Click += new System.EventHandler(this.btnGetData_Click_1); // // txtFirstName

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

20

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

34 // this.txtFirstName.Location = new System.Drawing.Point(48, 60); this.txtFirstName.Name = "txtFirstName"; this.txtFirstName.Size = new System.Drawing.Size(169, 20); this.txtFirstName.TabIndex = 1; this.txtFirstName.Text = ""; // // Form1 // this.AutoScaleBaseSize = new System.Drawing.Size(5, 13); this.ClientSize = new System.Drawing.Size(292, 273); this.Controls.AddRange(new System.Windows.Forms.Control[] { this.txtFirstName, this.btnGetData}); this.Name = "Form1"; this.ResumeLayout(false); } private void btnGetData_Click_1(object sender, System.EventArgs e) { string strSQL = "user id=sa;initial catalog=College;data source=mrlaptop"; studentConnection = new SqlConnection (strSQL); try { // open the connection studentConnection.Open(); // create the command object studentCommand = studentConnection.CreateCommand(); studentCommand.CommandType = CommandType.Text; studentCommand.CommandText = "SELECT * FROM Student"; // create the Adapter object studentAdapter = new SqlDataAdapter (studentCommand); studentSet = new DataSet(); studentAdapter.Fill (studentSet, "FirstName"); // bind the data to a text box this.txtFirstName.DataBindings.Add ("Text", studentSet, "FirstName"); } catch (SqlException s) { MessageBox.Show ("Oops, something bad happened"); } finally { studentConnection.Close(); studentConnection = null; } } } }

DataReader If you are looking for an object that will give you a fast and efficient return of rows of data, the DataReader is the object for you. This object provides a stream of rows in a forward-only cursor. There is no opportunity to move forward and backward through the data, and you cannot search for records. However, if you need to quickly scroll through data, the DataReader is what you want. You can create a DataReader from the command object:

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:27 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

20

Chapter 20: Data in Windows Forms

35 SqlDataReader studentReader = StudentCommand.ExecuteReader CommandBehavior.CloseConnection); while (studentReader.Read()) { // fill a control like a list box with the data using the Items.Add() method } studentReader.Close();

EXAM TIP The DataReader provides a read-only, forward-only representation of the data.

Using Format and Parse

The Format Event Whenever data is bound from a data source to a control on a form, the Format event is triggered. It is also triggered when the data returns from the control to the data source (in the example of updating the data). Data is formatted to take it from its native form in the data source to a form that can be displayed to the user. Conversely, displayed data is formatted back to its native form when it is returned to the data source. Often you will retrieve data from a data source that has a different data type than the type of control to which it is bound. The Binding object is used to bind the data to the control, and it triggers the Format event when doing so. You can create customized formats for your data by using the Format event. The Format event will fire in these circumstances: • When the data is bound for the first time to the control • When the Position property changes (refer back in this chapter for the Position property—it is used to reposition the row indicator) • When the data is sorted or filtered Here is some sample code that demonstrates formatting: Binding studentBinding = new Binding ("Text", studentSet, "HomePhone"); studentBinding.Format += new ConvertEventHandler (this.yourFormatName);

Of course, you still need to code the yourFormatName method; but the preceding code adds the handler to the Binding object in the text box’s DataBindings collection.

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:27 PM

PART III

Another objective of the Microsoft Windows exam suggests that you need to know how to transform and filter data. To that end, we will explore two techniques—formatting and parsing. The Binding object exposes two events—Format and Parse. We will discuss these two events in the next two sections.

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

20

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

36 The Parse Event When data is returned to the data source, it triggers the Format event. However, just before the Format event is fired, the Parse event occurs. The Parse event will occur in these circumstances: • When the Position property changes. • After a Validation event. • When the EndCurrentEdit() method is called (a method of BindingManagerBase). You can use code similar to what we showed for the Format event: StudentBinding.Parse += new ConvertEventHandler (this.yourParseMethod);

EXAM TIP The Format event is triggered when the data is bound to the control, when the Position changes, or when the data is sorted or filtered. The Parse event is triggered after a Validation event, when the Position changes, or when the EndCurrentEdit() method is called.

Summary This chapter has covered the exam objectives related to data access in Windows Forms. You should pay particular attention to the sections that deal with binding data to controls (both simple and complex binding) as well as the ADO.NET objects used for retrieving data from a data store (Connection, Command, DataSet, and DataAdapter). Microsoft will also test your understanding of the various data providers (SQL, OLE-DB) and when they are used—remember that the SQL provider is used for versions of Microsoft SQL Server 7.0 and higher. In order to feel very comfortable with the concepts presented in this chapter, you should find an evaluation copy of Microsoft SQL Server 2000 and set up a simple database (as we have done with the College database). Test yourself by creating connections and commands and displaying the data. That is the one way to make sure that you are ready for Microsoft’s exam.

Test Questions 1. Which code segment will populate a DataSet? A. sqlDataProvider1.Fill (dsUsers1); B. sqlDataProvider.Fill (dataAdapter1); C. sqlDataAdapter.Fill (dsUsers1); D. sqlDataAdapter.Fill (dataAdapter1);

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:28 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

20

Chapter 20: Data in Windows Forms

37 2. What type of commands can you create? A. Text, stored procedures, and tables. B. Text, stored procedures, and TableRows. C. Text, stored procedures, and TableDirect. D. Text, stored procedures, and TableColumns. 3. Data access exceptions all derive from which base class? A. Sql.Data.Exceptions B. Sql.SqlDataExceptions C. Exception 4. You need to get access to a database that is stored on a server running Microsoft Access 2002. Which data adapter would you use? A. SqlDataAdapter B. OleDbDataAdapter C. OleDataAdapter D. ODBCDataAdapter 5. You need to get access to a database that is stored on a server running Microsoft SQL Server 2000. Which data adapter would you use? A. SqlDataAdapter B. OleDbDataAdapter C. OleDataAdapter D. ODBCDataAdapter 6. You want to return XML data from a Microsoft SQL Server 7.0 database. Which method would you execute? A. ExecuteXmlReader() B. ExecuteXmlData() C. ExecuteOleReader() D. ExecuteOldData() 7. The Parse event is triggered after which of these occurrences? A. Data is sorted. B. Data is filtered. C. Data is bound to a control. D. Data is returned to the data source. E. All of the above. F. None of the above.

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:28 PM

PART III

D. DataException

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

20

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

38 8. The Format event is triggered after which occurrences? A. Data is sorted. B. Data is filtered. C. Data is bound to a control. D. Data is returned to the data source. E. All of the above. F. None of the above. 9. What will happen when the following code is executed? Assume the connection is created properly and works fine. try { studentConnection.Open(); studentCommand = studentConnection.CreateCommand(); studentCommand.CommandType = CommandType.StoredProcedure; studentCommand.CommandText = "SELECT * FROM Student"; studentAdapter = new SqlDataAdapter (studentCommand); studentSet = new DataSet(); studentAdapter.Fill (studentSet, "FirstName"); this.txtFirstName.DataBindings.Add ("Text", studentSet, "FirstName"); } catch (SqlDbException s) { MessageBox.Show ("Oops, something bad happened"); } finally { studentConnection.Close(); studentConnection = null; } }

A. The program will not compile. B. The program will compile but will throw an exception upon execution. C. The program will compile but will not display data. D. The program will display the data and close the connection properly. 10. What will happen when the following code is executed? Assume the connection is created properly and works fine. try { studentConnection.Open(); studentCommand = studentConnection.CreateCommand(); studentCommand.CommandType = CommandType.Text; studentCommand.CommandText = "SELECT * FROM Student"; studentAdapter = new SqlDataAdapter (studentCommand); studentSet = new DataSet(); studentAdapter.Fill (studentSet, "Name"); this.txtFirstName.DataBindings.Add ("Text", studentSet, "FirstName");

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:28 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

20

Chapter 20: Data in Windows Forms

39 } catch (SqlDbException s) { MessageBox.Show ("Oops, something bad happened"); } finally { studentConnection.Close(); studentConnection = null; } }

A. The program will not compile. B. The program will compile but throws an exception upon execution. C. The program will compile but will not display data. 11. You are the consultant for HMR Inc. They have a large network that includes a Microsoft SQL Server 2000 database. You have coded a connection and command object to retrieve data from the Student database, but you keep getting an exception. What is wrong with the following code? try { studentConnection.Open(); studentCommand = studentConnection.CreateCommand(); studentCommand.CommandType = CommandType.Text; studentCommand.CommandText = "SELECT * FROM Student"; studentAdapter = new OleDbDataAdapter (studentCommand); studentSet = new DataSet(); studentAdapter.Fill (studentSet, "FirstName"); this.txtFirstName.DataBindings.Add ("Text", studentSet, "FirstName"); } catch (OleDbException s) { MessageBox.Show ("Oops, something bad happened"); } finally { studentConnection.Close(); studentConnection = null; } }

A. The connection cannot be closed in the finally block. B. You are using the wrong data adapter. C. You are using the wrong data field. D. You are using the wrong exception object. E. Both A and C. F. Both B and D.

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:28 PM

PART III

D. The program will display the data and close the connection properly.

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

20

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

40 12. Which of the following object types allow you to view read-only, forward-only data? A. DataAdapter B. DataSet C. DataReader D. DataCommand 13. Which of the following statements is correct? A. The DataSource property refers to the dataset object, and the DisplayMember refers to the field. B. The DataMember property refers to the dataset object, and the DataSource refers to the field. C. The DataMember property refers to the field, and the DataSource refers to the dataset object. D. The DisplayMember property refers to the dataset object, and the DataSource refers to the field. 14. Why does the data not display using the following code? studentConnection.Open(); studentCommand = studentConnection.CreateCommand(); studentCommand.CommandType = CommandType.Text; studentCommand.CommandText = "SELECT * FROM Student"; studentAdapter = new SqlDataAdapter (studentCommand); studentSet = new DataSet(); this.txtFirstName.DataBindings.Add ("Text", studentSet, "FirstName");

A. The command object is instantiated incorrectly. B. The dataset object is instantiated incorrectly. C. The data binding is done incorrectly. D. The dataset has not been populated. 15. What have you forgotten to do if you see the following dialog box when running your program?

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:28 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

20

Chapter 20: Data in Windows Forms

41 A. Provide the correct database credentials. B. Handle data exceptions. C. Populate the dataset. D. Read Chapter 20.

Test Answers 1. C. 2. C. 3. D. 5. A. 6. A. 7. D. 8. E. 9. B. The command type is Text not StoredProcedure. 10. B. The exception is caused by the column named Name. 11. F. 12. C. 13. A. 14. D. 15. B. (Well, it could be D!!)

P:\010Comp\All-in-1\443-6\ch20.vp Friday, August 23, 2002 5:03:28 PM

PART III

4. B.