Chapter 16 - Chapter Overview 16.1 Introduction

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Chapter Overview 16.1 Introduction ...................................................................................................................674 16.2 ADO .NET Architecture .................................................................................................674 16.3 Data Access using Server Explorer ..............................................................................676 16.4 Database Connection....................................................................................................684 16.5 Working with ADO .NET Data Adapters........................................................................687 16.6 Working with ADO .NET Datasets ................................................................................698 16.7 Other ADO .NET Objects ..............................................................................................703 16.8 Chapter Summary .........................................................................................................703 16.9 Exercises.......................................................................................................................705 674 Chapter 16. Database Connectivity 16.1 Introduction In Chapter 2, we defined Database Applications as computer programs that allow users to manipulate the data in a DBMS through a user-friendly interface. We know how to work with various VB .NET controls and add them on application forms to build user-friendly interfaces (Chapter 15). These controls become even more attractive when we can use them to display and manipulate data residing in the underlying databases. This connectivity is an essential step for building a Database Application. For example, we can use the data grid control to display the student table from the University database on Windows forms; this interface allows users to view, edit, and print the data. In this chapter, we illustrate how we can use VB .NET features to connect to a database and further use this connection to send data back-and-forth between database and controls on VB .NET forms. We use ADO .NET (ActiveX Data Objects) to bridge VB .NET controls and the underlying database. ADO .NET is the collection of classes (objects) that are designed to support data access and data manipulation. We can think of them as System-designed readymade objects that allow us to access databases. We begin this chapter by describing the architecture of an ADO .NET. We then show how we can use Server Explorer and ADO .NET objects to establish a connection with an Access Database. Further, we provide a detailed discussion of important ADO .NET objects, like Data Connections, Data Adaptors, and Datasets. We conclude the chapter with a brief discussion of other useful ADO .NET objects. 16.2 ADO .NET Architecture Our discussion of ADO .NET architecture will focus primarily on its three main objects, Data Connection, Data Adaptor, and Datasets. These three objects, shown in Figure 16.1, each allow us to access data from a database in windows forms. Figure 16.1 ADO .NET architecture This figure demonstrates how we access data from a database. We first create a connection object that contains information about the database (for example, the MS Access database file University.mdb) we want to work with. We then use the Data Adaptor object, itself composed of several ADO .NET Command objects, to query to the database through this established connection. Each Command object is an SQL query. Typically, a Data Adaptor has only one command for each of the following functions: Select, Insert, Update, and Delete. These Abhijit Pol & Ravindra Ahuja Chapter 16. Database Connectivity 675 commands correspond to Select, Insert, Update, and Delete queries, which we explored in chapters 8 and 9. Thus, we create a Data Adaptor for a given task; the corresponding commands objects are automatically created for us. For example, if we wish to work with the Student table from the University database, we must create a Data Adaptor for this table. The Data adaptor then has all the commands we may need, and uses a single object for all commands facilitating selection, insertion, updates, and deletion from this table. Once we have the Data Adaptor in place, we need some place to store the results of the query coming from the database. For example, if we use the select command to create a Data Adaptor for the Student table, we will end up with all of the part time students in the database. Dataset, another ADO .NET object, stores the output of this query. Once we have data in Datasets, we can process this data in any way allowable by our application. We may choose to bind Datasets to form controls, use them for plots and graphics, or we can use them in various algorithms for further processing and optimization tasks. As with Data Adaptors, each Dataset consists of ADO .NET objects known as Data Tables. These Data Table objects are very similar to Database tables. Because of Data Tables, object developers working with a Dataset may feel as though they are working on Database tables, which may reveal the relationships between connection, adaptor, and dataset (shown in Figure 16.2). Figure 16.2 ADO .NET architecture - detailed Before discussing these objects in more detail, we will first show how to use a Server Explorer to connect to a database. Then, we will look at ways to quickly add a Data Adaptor and Dataset objects and how to display data in a Windows form through a Data Grid control. Abhijit Pol & Ravindra Ahuja 676 Chapter 16. Database Connectivity Data Connection - Connects to the database. Data Adapter - Queries a database table using SQL statements. Dataset - Stores the data returned by a Data Adapter. Summary 16.3 Data Access using Server Explorer In this section, we illustrate how we can quickly access data using Visual Studio .NET's Server explorer. The Server explorer allows us to create data connections with various data sources, or databases. It also displays the list of tables and views (queries) for established data connections (shown in Figure 16.3). We can visually drag and drop the tables or views from the lists on the forms to automatically create a data connection and a data adapter object. We can then create a dataset object and display the table or view on a form using a data grid control. For example, we display the student table from the University database in a Windows form through the following steps. • Create a new project, C: /VbDotNet/Chapter16/Connection1. Step 1: Make a connection with a Database 1. To make a database connection, right click the Data Connection listing in the Sever Explorer and select Add Connection item from the short-cut menu (as shown in Figure 16.4). You may also choose Tools | Connect to Database from the main menu. Either option should open the Data Link Properties dialog as shown in Figure 16.5. If the Server Explorer is not already open, choose the View | Server Explorer menu from the main menu, or press Ctrl + Alt + S. 2. In the Data Link Properties dialog box, navigate to the Provider tab and select the Microsoft Jet 4.0 OLE DB Data Provider from the list of available data providers (shown in Figure 16.5). Click on next to proceed. Microsoft Jet 4.0, a data provider for an Access database, is detailed in the following section. Abhijit Pol & Ravindra Ahuja Chapter 16. Database Connectivity Figure 16.3 Server explorer Figure 16.4 677 Adding database connection 3. For the first listing, Select or Enter database name, use the build button near the text box to select the database (Figure 16.6). This should open the Select Access Database dialog box; then, browse and select the University (or desired) database. Test the connection using the Test Connection command button in the dialog box. Click on Ok to create the connection. Verify the connection and table listing for the University database in the Server Explorer by expanding the tree using the '+' sign. Abhijit Pol & Ravindra Ahuja 678 Chapter 16. Database Connectivity Figure 16.5 Selecting data provider Figure 16.6 Selecting database for connection Abhijit Pol & Ravindra Ahuja Chapter 16. Database Connectivity 679 Step 2: Create a Data Adaptor 4. We can use several methods to create a Data Adaptor. For one option, drag the tblStudent from the table listing in the Server Explorer found in Windows Form1. This visual dragging automatically creates a Data Adapter object, OleDbDataAdapter1, in the component tray (Figure 16.7). Figure 16.7 Creating a Data Adapter using Server Explorer Note that this also creates a Data Connection object, OleDbConnection1. Whenever we create the first Data Adaptor for a Form, Access automatically creates a Connection object for us. Later, we will explore other ways to create a Data adaptor. We will also look at some of its other features, including customized SQL queries and data previewing. Step 3: Create a Dataset 5. To create a dataset, first select the Data Adaptor, OleDbDataAdapter1 (or desired Adaptor), and then choose Data | Generate Dataset from the main menu. You can also right click the Data Adapter and select Generate Dataset from the shortcut menu (shown in Figure 16.8). Either of these options will display the Generate Dataset dialog box shown in Figure 16.9. 6. Select the New option in the Generate Dataset dialog box. Make sure the tblStudent (OleDbDataAdapter1) item is checked in the tables for datasets. Click on Ok to create a new dataset, Dataset1. This adds a dataset icon to the form designer's component tray. Later, we will look at Datasets in greater detail. Abhijit Pol & Ravindra Ahuja 680 Chapter 16. Database Connectivity After adding a Data Connection, Data Adapter, and Dataset, your component tray for Form 1 should look like that shown in Figure 16.10. Figure 16.8 Generating Dataset: Options Figure 16.9 Generate Dataset Dialog Abhijit Pol & Ravindra Ahuja Chapter 16. Database Connectivity 681 Figure 16.10 Component Tray Step 4: Bind the Dataset to a Control 7. Add a Data Grid control on Form 1 from a Toolbox (Windows Forms tab). Resize the Data grid to fit the form. 8. We bind the dataset to a control by manipulating control properties (for more details on data binding, see Chapter 17). Use the Property window to set the following properties for the Data grid control (If we arrange properties by categories, we can find these properties grouped under Data title). • Data Source: DataSet11 – Binds the Dataset to the Data Grid • Data Member: tblStudent – Binds the tblStudent in particular from Dataset11 to the grid control. Abhijit Pol & Ravindra Ahuja 682 Figure 16.11 Chapter 16. Database Connectivity Data Grid and its DataSource and DataMember Properties Step 5: Write two lines of VB .NET code to make things happen We are almost done with our first database connection application. In this final step, we write VB .NET code that will use the data adapter to fill the dataset with query results. The Dataset is disconnected from the database at all times; because it does not have access to the database connection, it stores its own local copy of data. The Data Adaptor acts like a bridge between Data Connection and Datasets; to import data into a Dataset, we must activate the Fill method of the Data Adaptor. We use the Form Load event of Form 1 to do this. 9. Select and double click form1 to open its code window with the default Form1_Load event subroutine. Use the following piece of code to activate the Fill method (see Figure 16.12). Abhijit Pol & Ravindra Ahuja Chapter 16. Database Connectivity 683 Figure 16.12 Code Window for Form1 1. Dataset11.Clear () 2. OleDbDataAdapter1.Fill (DataSet11) Code Explanation In line 1, we use the Clear method to clear the Dataset of its previous contents. Line 2 fills the Dataset with fresh data. Note that Fill is a method associated with a Data Adaptor, while Clear is a method associated with a Dataset. The Fill method in line 2 makes the Data Adapter use a database connection to find the result of a query, which it then stores in a Dataset specified by its argument. The Clear method in line 1 is necessary because the new data (query output) can have a potentially smaller number of rows than that needed for the previously existing data in a dataset. In this situation, new data overwrites only a portion of the old data in the Dataset. However, this creates a problem in that the Dataset now contains a mix of both old and new data, preventing an accurate display for a recently executed query. To avoid this problem, make sure to first Clear and then Fill the Dataset. 10. Run the application (Ctrl + F5) to view the output shown in Figure 16.13. In the following box, we have summarized the steps needed for database connection. This method gives us the easiest way of working with the databases. However, notice that our output Abhijit Pol & Ravindra Ahuja 684 Chapter 16. Database Connectivity (shown in Figure 16.13) displays the entire Student table; most potential applications will be more complex, and require more flexible approaches. Features such as the Data Adapter and Datasets objects give us this desired flexibility. Figure 16.13 Output in Data Gird Steps for database connectivity using Server Explorer: 6. Create a database connection using the Server Explorer. 7. Drag a table from the Server Explorer on a form to automatically create a data adapter. 8. Generate a new dataset with the Generate Dataset dialog box. (Right-click the adapter name) 9. Add a Data Grid control and bind it to the Dataset using its properties in Property Window. 10 Fill the Dataset using the Fill method of a Data Adapter in VB NET code 16.4 Database Connection As we learned in section 16.3, we must use the Server Explorer to create a database connection if we want to move data between a database and our application. The Data Link Properties dialog box (see Figure 16.5 ) used for this task first asks for a Provider, which designates the data format we want to connect to. Depending on the underlying database, we select Microsoft Jet 4.0 for Access database, Microsoft OLE DB Provider for Oracle, or SQL Abhijit Pol & Ravindra Ahuja Chapter 16. Database Connectivity 685 Server. In the second tab, we give the actual location of a database file to create a connection. With ADO .NET, we can use different data providers to create multiple connections for an application. For example, we can use three different connections to connect an application to an Access database, Oracle database, and even to an SQL server at the same time. In this section, we discuss different ways of creating database connections and examine the important properties and methods of an Ole DB connection object. 16.4.1 Creating Database Connection Object In ADO .NET, an OleDbConnection connection object (for Access databases) creates and manages a connection. There are three ways of creating an OleDbConnection object. • Access automatically creates an OleDbConnection connection object whenever we create the first Data Adapter for a form by dropping tables on a form or using Data design tools from the Toolbox (Data Adapter wizard or Data Form wizard). This connection object, which we do not explicitly need to create, is then used for all of the adapters in the Form. • We can also create a standalone connection using the Data tab of the Toolbox. If we are connecting to an Access database, we must first select the OleDbConnection object from the data tab and drag it to the form. Then, select the connection from the component tray and manipulate its Name and ConnectionString properties as explained in 16.4.2. This option is useful if we intend to set connection properties at run time or if we simply prefer setting the properties in the Properties window. • Finally, we can also create a connection programmatically in VB .NET code in a more dynamic environment. We will examine this option in more detail in the following chapter. 16.4.2 Properties of Connection Object No matter how we create the connection object, we can manipulate its properties statically using the Property Window or dynamically in VB .NET code. The two important properties related to the connection objects are: Name A name given to the connection object is used to refer to the object in VB .NET code. ConnectionString Abhijit Pol & Ravindra Ahuja 686 Chapter 16. Database Connectivity This is the primary property associated with a connection object and a string with information required to connect to a database. A typical ConnectionString property might look like the following: Provider = "Microsoft.Jet.OLEDB.4.0"; Data Source = "C:\VbDotNet\Chapter16\ Connection1\University.mdb"; The most common attributes used by OLE DB are Provider and DataSource. These properties get their values through the Data Link Property dialog box (Provider and Connection tabs respectively) we set up at the beginning. We can alter the values of these properties using the Property Window (shown in Figure 16.14). For example, we can make the path of Data Source relative to the directory of the application rather than the default absolute path (Data Source = “University.mdb”). Figure 16.14 Connection Properties 16.4.3 Methods of Connection Object There are two primary methods available for connection objects: Open and Close. The Open method uses the information in the ConnectionString property to contact the data source and establish an open connection. The Close method shuts the connection down. Closing connections is essential, because most data sources support only a limited number of open connections, and open connections take up valuable system resources. In cases when Connection objects are automatically created and we are using them with data adapters or data commands, we do not have to explicitly open and close a connection. For example, when we use the Fill method (described in Section 16.5), the method automatically Abhijit Pol & Ravindra Ahuja Chapter 16. Database Connectivity 687 opens and closes the connection for us. If we create the connection object manually using the Data tab of the Toolbox or in VB .NET code programmatically, we are responsible for opening and closing connections. Closed Connections Tips The red mark (X) over a database connection icon in the Server Explorer indicates the closed connection. We can re-open the connection by simply clicking the plus sign (+) next to the connection icon. 16.5 Working with ADO .NET Data Adapters Data Adapters are used to exchange data between a data source (database) and a dataset. They serve to bridge a database and a dataset. Data Adaptors are capable of reading data in datasets from databases (Selection) as well as writing data from datasets back to the databases (Updates). In this section, we first explore the architecture of a Data Adapter, ways of creating a Data Adapter, some essential properties and methods of this object, and features like configuring and previewing data adapter results. 16.5.1 Architecture of a Data Adapter The general architecture of a Data Adapter object is shown in Figure 16.2. Generally, adapters are configurable to allow us to specify what data to move into and out of the dataset. Often this is done with SQL statements that are invoked to read or write to a database. These SQL statements are generated with a Data Adapter object and are stored in ADO .NET command objects. Thus, a typical Data Adapter consists of a command object for a Select SQL statement (SelectCommand), a command object for an Update SQL statement (UpdateCommand), one for an Insert SQL statement (InsertCommand), and an object for a Delete SQL statement (DeleteCommand). Generally, each data adapter exchanges data between a database and a single DataTable object in the dataset. If the dataset contains multiple data table objects, the usual strategy is to have multiple data adapters feeding data to it and writing its data back to the database tables. The Data Adapter architecture works in the following manner. When we want to populate a data table object in a dataset, we call an adapter method (Fill) that executes a Select SQL statement in SelectCommand object and reads the query result data into a dataset. Similarly, when we want to update the database, we invoke an adapter method (Update) that calls an appropriate SQL statement (Update/Insert/Delete) to make the actual update in the database. Abhijit Pol & Ravindra Ahuja 688 Chapter 16. Database Connectivity We can read data from the database without having to store it in a dataset by independently using powerful Command objects. We develop this discussion of ADO .NET Command objects later in this book. 16.5.2 Creating a Data Adapter Data adapters define the means by which we can get information from one or more tables in the data source into a dataset and back again. Visual Studio .NET offers a number of ways to create and configure data adapters: using the Server Explorer, using the Data Adaptor wizard, manually through the Properties window, and programmatically in VB .NET code. Data Adapters using Server Explorer As described before, we can drag a table or a view from the Server Explorer listing on a Windows form to automatically create a Data Adapter object. For example, we dragged the tblStudent table to create OleDbDataAdapter1 with the SelectCommand, Select * From tblStudent. The appropriate Update/Insert/Delete commands are also generated at the same time. This is one of the easiest ways to send an entire table back-and-forth between database and datasets. Data Adapters using Data Adapter Wizard We can use the Data Adapter Wizard to create data adapters. A wizard is a graphical tool that guides us through a step-by-step process for creating and configuring a data adapter. We illustrate the steps of the wizard for a data adapter that will produce a list of full time students from the database. The first default option, by contrast, reproduces the entire student table. Wizard thus allows us to configure to specify what data to move into and out of the dataset using SelectCommand. Hands On # 16.1 Using Data Adapter Wizard In this section, we illustrate how to configure a Data Adapter using the Data Adapter Wizard. Create a Data Adapter with a SelectCommand that brings records of all full time students from the student table. 1. Add a new Windows form to the Connection1 project (Use Project | Add Windows Form from the main menu). Set this as a Start-up form (Use Project | Connection 1 Properties | Startup Object list). Abhijit Pol & Ravindra Ahuja Chapter 16. Database Connectivity 689 2. Click on the Data tab in the toolbox and drag the OleDbDataAdapter control to Form2. This should automatically open the Data Adapter wizard. Click next to continue on to the welcome screen of the wizard. Figure 16.15 Data Adapter Configuration Wizard – Step 2 of 5 3. In the second step of the wizard, we specify the data connection that the adapter should use to query a database. Choose the University Database connection from the list of available data connections. We can use the New Connection button, which opens a Data Link Property dialog box, to create a new connection. Refer to Figure 16.15 for this step. Figure 16.16 Data Adapter Configuration Wizard – Step 3 of 5 4. The third step of the wizard allows us to specify the query type for the data adapter. Accept the default selection, Use SQL Statement, and Click next to proceed (refer to Figure 16.16). Abhijit Pol & Ravindra Ahuja 690 Chapter 16. Database Connectivity Figure 16.17 Data Adapter Configuration Wizard – Step 4 of 5 5. The fourth step of the wizard is very crucial. We specify Select query for the data we want to move from the database into datasets. We can directly enter a Select SQL statement into the space provided or we can click on the query builder button to open an Access Query, such as the Query-by-example dialog box (refer to Figure 15.17) with the Add Table dialog box. Figure 16.18 Add Table Dialog Abhijit Pol & Ravindra Ahuja Chapter 16. Database Connectivity 691 Figure 16.19 VB .NET's Query Builder The query builder dialog box is divided into four horizontal segments. The top segment, table pane, shows and allows the addition or deletion of database tables and their relationships. Table pane holds the tables involved in a select query (which is similar to the Access query Table Pane). We can check the attributes from various tables in the table pane to move them to the second segment, Design Grid (similar to the Access query design grid). We can specify the sorting order or selection criteria for a field in the design grid. The third segment of the dialog box, the SQL view, shows the SQL statement as we build on query by example. We can manipulate SQL statements at any point, as described in the SQL chapter. The fourth segment is meant to preview query output. Once we design the query, right click in the bottom grid of the dialog and select Run from the short-cut menu. This displays the result of the query. 6. Use the following SQL statement or refer to Figure 16.19 to build the desired SelectCommand for the Hands-On section. Click Next to go to final and fifth step of wizard. SELECT Name, Email, Address FROM tblStudent WHERE Type = "Full Time" 7. The final step displays the data adapter configuration result. If all command objects are successfully configured, the details section displays messages that confirm the generation of the corresponding statements. Figure 16.20 shows the result for our adapter configuration. At least one select statement must be generated successfully before we can use the data adapter. Abhijit Pol & Ravindra Ahuja 692 Chapter 16. Database Connectivity Figure 16.20 Data Adapter Configuration Wizard – Step 5 of 5 8. We can complete this application by creating a new dataset and then binding it to a data grid control. Use steps 3-5 from Section 16.2 to complete this process. With this introduction to Data Adapters, we can now summarize the process of getting data from a database and displaying it in a Windows form in the following 5 steps. Steps for Database Connectivity 1. Create a Data Connection using the Server Explorer. Summary 2. Create a Data Adapter with the desired SQL statement. 3. Create a Dataset to hold the results. 4. Bind the Dataset to the Control using the Property Window in VB .NET code. Data Adapters using Property Window We can also manually create and configure a Data Adapter object. Abhijit Pol & Ravindra Ahuja Chapter 16. Database Connectivity 693 • Select the OleDbAdapter object from the Data tab of the Toolbox and drag it to the Windows form. • Click the cancel button for the Data Adapter Wizard. This adds an Adapter object to the component tray. • Use the Property Window of an Adapter to configure it. The properties of a Data Adapter object are discussed in the following text. See the following chapter for more on the last option for using VB .NET to create a Data Adapter. 16.5.3 Properties of a Data Adapter A Data Adaptor has several properties (shown in Figure 16.21) which allow fine control over these objects. Data Adaptor properties can be grouped into the following categories: • SelectCommand – reference to a SelectCommand object to read the rows from the database. • InsertCommand – reference to an InsertCommand object for inserting rows into the database. • UpdateCommand – reference to an UpdateCommand object for modifying rows in the database. • DeleteCommand – reference to a DeleteCommand object for deleting rows from the database. The following table summarizes some of the properties of these Data Adaptor properties. Figure 16.21 Properties of a Data Adapter Abhijit Pol & Ravindra Ahuja 694 Chapter 16. Database Connectivity Property Examples Description Name OleDbSelectCommand1, OleDbInsertCommand1 Indicate name used in code to refer to the command object. Command Text SELECT StudentID, Name, Email, Address This is the text of the FROM tblStudent WHERE (Type = 'Full Time') command, generally a SQL statement. [? denotes a INSERT INTO tblStudent (StudentID, Name, parameter in SQL query] Email, Address) VALUES (?, ?, ?, ?) CommandType Text, Stored Procedure, Table Direct (Text How to interpret option is used for the SQL statement) command text. Parameters Table 16.1 the Use the build button to invoke the Collection Specify values of editor. We can add or remove parameters parameters in a SQL query. using this dialog box (Figure 16.22). Properties of a Command Collection Figure 16.22 Collection editor We use Table Mapping, another important property, to rename tables and columns in the Dataset. By default, tables and columns in a Dataset are named as table and columns in the database. To change these default names, click the build button for the Table Mapping collection listing in the Property Window (shown in Figure 16.23). Abhijit Pol & Ravindra Ahuja Chapter 16. Database Connectivity 695 Figure 16.23 Table mapping dialog Use the Dataset table and Dataset column areas in the Table Mapping dialog box to change default names. You may want to rename dataset tables when creating multiple Data Adaptors on the same table and adding them to the same Dataset. In such situations, the Table Mapping property can resolve any table name conflict. We can manipulate all these properties in the Property window or dynamically in code. The following box gives some examples of property manipulation in VB .NET code. 'Setting the Command Object... 1. 2. 3. 4. OleDbDataAdapter1.SelectCommand.Name = "OleDbSelectCommand1" OleDbDataAdapter1.SelectCommand.CommandType = "Text" OleDbDataAdapter1.SelectCommand.CommandText = "SELECT Name ... OleDbDataAdapter1.SelectCommand.Paramters("Name").Value = "John" 16.5.4 Methods of a Data Adapter The primary purpose of the data adapter is to communicate data between a data store and a dataset. The adapter supports specific methods to move the data back and forth between the two. To read rows into a dataset from a database table, we use the Fill method on the Abhijit Pol & Ravindra Ahuja 696 Chapter 16. Database Connectivity OleDbDataAdapter object. This method, once invoked, transmits an SQL SELECT statement to the database. To transmit the updated data from the table object of the dataset to the database, we use the adapter's Update method. When we invoke this method, it executes whatever SQL INSERT, UPDATE or DELETE statements are needed, depending on whether the affected record is new, changed, or deleted. The fill method works from database tables to tables in a Dataset, while the Update method works in the opposite direction, moving from Dataset tables to database tables. Following the steps from Hands-On 16.1, we can add two command buttons, Load Data (cmdLoad) and Update Data (cmdUpdate), to Form2. We now write VB .NET code that allows us to use the Fill and Update methods of the OleDbDataAdapter whenever we click on these buttons. Private Sub cmdLoad_Click (ByVal sender As System.Object... 1. 2. DataSet21.Clear() OleDbDataAdapter1.Fill(DataSet21) End Sub Private Sub cmdUpdate_Click (ByVal sender As System.Object... 3. 4. OleDbDataAdapter1.Update(DataSet21) MessageBox.Show ("Database Updated!!") End Sub Code Explanation In line 1, we clear the Dataset of any stored data. Line 2 uses the Fill method of the Data Adapter to execute a Select SQL statement in OleDbDataAdapter1; this fills Dataset21. Line 3 illustrates the use of the Update method. Whatever changes occur in Dataset21 are sent back to the database table through this method. Note that the Data Grid allows us to change values on forms and, at the same time, make those changes to the source Dataset through data binding. 16.5.5 Configuring and Previewing Data Adapter Results Visual Studio .NET provide an easy way to reconfigure data adapters. During the design process, you may want to alter the SQL statement associated with a data adapter. To reconfigure an existing data adapter: Abhijit Pol & Ravindra Ahuja Chapter 16. Database Connectivity 697 1. Right-click Data Adapter in the component tray and select Configure Data Adapter from the short-cut menu (see Figure 16.24). 2. This should display the Data Adapter Configuration Wizard. Follow the steps from Hands-on section 16.1 to reconfigure the Data Adapter. Figure 16.24 Configuring Data Adapter Once we have created a Data Adapter, we can use the Data Adapter Preview dialog box to easily and quickly examine the data provided by the data adapter. • To open the data adapter preview dialog box, choose View | Preview Data from the main menu • You may also right-click the Data Adapter under consideration and select Preview Data from the short-cut menu. This will open the dialog box shown in Figure 16.25. Select the target dataset from the list of available datasets in a combo box and click the Fill Dataset button to preview the data. Abhijit Pol & Ravindra Ahuja 698 Chapter 16. Database Connectivity Figure 16.25 Previewing Data from Data Adapter 16.6 Working with ADO .NET Datasets We store the data necessary for our application in datasets, which act like caches, or temporary storage places, for data. Datasets are always disconnected from the database and rely on the Data Adaptor to fill them with refreshed data. In this section, we first deal with the architecture of a Dataset object. We then talk about ways to create Datasets and their properties and methods. Finally, we look into various Dataset operations which make them really powerful and attractive objects of the ADO .NET family. 16.6.1 Architecture of a Dataset The structure of a dataset is similar to that of a relational database; it exposes a hierarchical object model of tables, rows, and columns. In addition, it contains constraints and relationships defined for the dataset. Each Dataset object may contain one or more data table objects. Each data table object is composed of a fixed number of column objects and an arbitrary number of row objects. The data tables in a dataset can be related to each other using the Data relation object, which we discuss in greater detail in the next chapter. Figure 16.26 illustrates the structure of a Dataset object. Abhijit Pol & Ravindra Ahuja Chapter 16. Database Connectivity 699 Figure 16.26 Architecture of a Dataset object 16.6.2 Creating Datasets There are three ways we can create a Dataset object. • From a Data Adaptor object (as we did in Section 16.2 or Hands-On 16.1). • Using a Dataset Object in the Data tab of the Toolbox. • Programmatically in VB .NET code (see Chapter 17). In most cases, we create a Dataset object by right-clicking the Data Adaptor object and then selecting Generate Dataset from the short-cut menu. The Generate Dataset dialog box allows us to create a new Dataset object or add table objects to the existing Dataset. We can also see the Data Adaptors and their associated tables in the dialog box. We check the table(s) that we want in a Dataset object from this list. If we try to add tables from different Data Adaptors with the same name, a name conflict results. This conflict can be resolved through the Table Mapping property of a Data Adapter. In practice, we can create a Dataset for each Data Adaptor object. But if we want two related multiple tables in the dataset, then we add them in a single dataset and use the data relation object to specify a relationship within the dataset. See the following chapter for more information on this and related topics. Abhijit Pol & Ravindra Ahuja 700 16.6.3 Chapter 16. Database Connectivity Properties and Methods of a Dataset Select the Dataset in the component tray and choose Data | Dataset Properties from the main menu to view the properties of a Dataset. You can also right-click and select the Dataset Properties item from the short-cut menu. This brings up the Dataset Properties window (see Figure 16.27). This window allows us to look into the interior of a dataset. It uses a tree structure to display a list of tables contained within dataset, as well as their fields and constraints. This window facilitates the review of Dataset elements. Apart from these properties, several other properties and methods are available for a Dataset object in the code window. The following table summarizes some of these. Method Name Usage Description AcceptChanges DsTest.AcceptChnages() Commits all changes made to the dataset since loaded or last time. Clear DSTest.Clear() Clears a dataset by removing all its rows. Clone DsTest = DsCloneMe.Clone() Copies only the structure of a dataset. Copy DsTest = DsCopyMe.Copy() Copies both the structure and data of a dataset. HasChanges Num = DsTest.HasChanges() Shows the number of rows changed (inserted/deleted/updated) since last loaded. GetChanges DsNew = DsOld.GetChanges() Gets a dataset corresponding to the changes in the current dataset. Merge DsCurrent.Merge(DsMergeMe) Merges the specified dataset and its structure into the current dataset. Tables Property DsTest.Tables(“tblTest”).Rows.C Gives access to the specified table, its ount rows and columns collections. Table 16.2 Dataset methods and properties In subsequent chapters, we will elaborate on the applications of table properties in Dataset operations. Abhijit Pol & Ravindra Ahuja Chapter 16. Database Connectivity 16.6.4 701 Dataset Operations Datasets are a fundamental part of the ADO.NET architecture, providing both high-performance data access as well as scalability. There are several operations we can directly do on data in the dataset, making them attractive for application building. We discuss a few of these operations and provide the VB .NET code necessary for their execution. Sorting and Filtering After a dataset has been populated, we might need to view its data in various ways. For example, we might want to view the student records in an order based on academic departments or view filtered subsets of records, such as all male students or all first-year students. Because the dataset is disconnected from the data source, it is often impractical and resource-intensive to re-execute SQL commands to perform these actions. Instead, we can use built-in dataset features to filter and sort our data. The procedure for sorting is as follows. We use the DefaultView property of a Data Table object to gain access to its current view. We then use the Sort property of the Default View and specify the Sort expression. Typically, a sort expression is the name of the field to be sorted followed by a key word that designates the sorting order (ASC or DESC). In order to view the newly sorted Data Table, we can assign the default view as a data source of a control such as the data grid. The procedure for filtering is very similar to Sorting. We use the DefaultView.RowFilter property and specify the Filtering expression. Typically, a filtering expression is an expression with some criteria involving field names. We can view the newly filtered Data Table by assigning the default view as a data source of a control, such as the data grid. This short Hands-on exercise will allow us to practice Sorting and Filtering. 1. Add a new form Form3 to the Connection 1 application and set it as a starting form. 2. Create a Data Adapter and a Dataset to display the StudentID, Name, Sex, Type, and DeptID fields from the Student table. Bind the Dataset to the Data Grid control on the form. Write code on the Form Load event to fill the dataset. 3. Add two command buttons and a label control as shown in Figure 16.27. Use the following code box to assign code for their On Click events. Abhijit Pol & Ravindra Ahuja 702 Chapter 16. Database Connectivity Private Sub cmdSort_Click (ByVal sender As System.Object... 1. DataSet31.Tables ("tblStudent").DefaultView.Sort = "DeptID ASC" 2. DataGrid1.DataSource = DataSet31.Tables("tblStudent").DefaultView 3. lblMsg.Text = DataSet31.Tables("tblStudent").DefaultView.Count & " rows sorted" End Sub Private Sub cmdFilter_Click (ByVal sender As System.Object... 1. DataSet31.Tables ("tblStudent").DefaultView.RowFilter = "Sex = 'M'" 2. DataGrid1.DataSource = DataSet31.Tables("tblStudent").DefaultView 3. lblMsg.Text = DataSet31.Tables("tblStudent").DefaultView.Count & " rows left" End Sub Accessing Datasets cells With the Dataset Table property, we can access any data table object and, correspondingly, any cell (defined as an intersection of a data row and a data column) of the Dataset. The following code box indicates how to use the Tables property to get a count of the number of records in a Data Table, to get to the last row, and to get to any cell of the Data Table. 'Using Tables Property... 1. 2. 3. 4. numRows = Dataset31.Tables("tblTest").Rows.Count firstCellValue = Dataset31.Tables("tblTest").Rows(0).Item(0) lastRow = Dataset31.Tables ("tblTest").Rows (numRows-1) Dataset31.Tables ("tblTest").Rows (i).Item (j) = someValue Code Explanation In each line, we use the Dataset Table property indexed by the name of the table. This gives us access to the tblTest Data Table object. We then use functions related to the Rows of a Data Table. In line 1, we use the Rows.Count property to get the total number of rows. In line 2, we use Rows (#).Item (#).to access a particular row and item property. In line 3, we use the count value to get to the last row of the Data Table. Then, we assign the entire row, Rows (Count-1) to the lastRow variable. [A BIT UNCLEAR; CLARIFY WHAT YOU MEAN BY “HAS TO BE DECLARED AS DATA ROW BEFORE] Finally, in line 4 we use Rows and Item properties to change the value of a particular cell in the dataset. Abhijit Pol & Ravindra Ahuja Chapter 16. Database Connectivity 703 16.7 Other ADO .NET Objects In this section, we quickly review a few other ADO .NET objects. Data Readers Like Datasets, Data Readers objects also hold the data from the Data Adapter. The only difference is that Data Readers are read-only and forward-only. Unlike Datasets, we cannot use Data Readers to write or update a data source. Also, while Datasets allow us to navigate backand-forth, Data Readers will allow only forward navigation. Because of their read-only and forward-only properties, Data Readers increase the speed of data access and improve the overall performance of an application. Data Views Data Views allow us to customize data presentation. Data Views enable us to view data that has been filtered, sorted, or searched. Apart from this, Data Views are exactly similar to Data Reader objects. 16.8 Chapter Summary • We use ADO .NET (ActiveX Data Objects) to bridge VB .NET controls and the underlying database. ADO .NET is the collection of classes (objects) that are designed to support data access and data manipulation. • Data Connection • Data Adapter - Queries a database table using SQL statements. • Dataset - Stores the data returned by a Data Adapter. • We can quickly access data using Visual Studio .NET's Server explorer. The Server explorer allows us to create data connections with various data sources, or databases. It also displays the list of tables and views (queries) for established data connections • To make a database connection, right click the Data Connection listing in the Sever Explorer and select Add Connection item from the short-cut menu (as shown in Figure 16.4). You may also choose Tools | Connect to Database from the main menu. • We bind the dataset to a control by manipulating control properties Abhijit Pol & Ravindra Ahuja - Connects to the database. 704 Chapter 16. Database Connectivity • Data Adapters are used to exchange data between a data source (database) and a dataset. They serve to bridge a database and a dataset. Data Adaptors are capable of reading data in datasets from databases (Selection) as well as writing data from datasets back to the databases (Updates). • Generally, adapters are configurable to allow us to specify what data to move into and out of the dataset. Often this is done with SQL statements that are invoked to read or write to a database. These SQL statements are generated with a Data Adapter object and are stored in ADO .NET command objects. • We can use the Data Adapter Wizard to create data adapters. A wizard is a graphical tool that guides us through a step-by-step process for creating and configuring a data adapter. • Steps for Database Connectivity a) Create a Data Connection using the Server Explorer. b) Create a Data Adapter with the desired SQL statement. c) Create a Dataset to hold the results. d) Bind the Dataset to the Control using the Property Window in VB .NET code. • We store the data necessary for our application in datasets, which act like caches, or temporary storage places, for data. Datasets are always disconnected from the database and rely on the Data Adaptor to fill them with refreshed data. • The structure of a dataset is similar to that of a relational database; it exposes a hierarchical object model of tables, rows, and columns. In addition, it contains constraints and relationships defined for the dataset. Each Dataset object may contain one or more data table objects. Each data table object is composed of a fixed number of column objects and an arbitrary number of row objects. The data tables in a dataset can be related to each other using the Data relation object, • There are three ways we can create a Dataset object. a) From a Data Adaptor object. b) Using a Dataset Object in the Data tab of the Toolbox. c) Programmatically in VB .NET code. • Datasets are a fundamental part of the ADO.NET architecture, providing both highperformance data access as well as scalability. There are several operations we can directly do on data in the dataset, making them attractive for application building. Abhijit Pol & Ravindra Ahuja Chapter 16. Database Connectivity 705 16.9 Exercises Review Exercises 1. What is ADO .NET? 2. As discussed in the text book, what are the three main objects of ADO .NET? Describe the relations among them. 3. We mentioned in the book, different types of command objects available through the data adaptor object. What are those different commands? Describe each one of them. 4. Describe the steps of database connectivity using the “Server Explorer”. 5. The most important property of the Connection object is the ConnectionString property. What is the function provided by this property? And are its two major attributes? 6. We mentioned a couple of times in this chapter that the dataset is disconnected from the database. What do we mean by disconnected? And why this can be a good thing? 7. Dataset has a number of available methods. Two of them art clone and copy. What is the mean difference between copy and close? 8. What are the main differences between the DataReaders and the DataSet objects? 9. What are the two main methods of the Data Adaptors that handle the traffic between the physical database and the in memory image of the tables represented by the dataset? Describe. 10. What does the method Fill of the data adaptor do? 11. Describe the DefaultView property of the dataset object. Hands-on Exercises 1. A hospital asked you to create a single-form application that access their database and view the records of all the patients. Your application should print the patient’s first and last name, the patient’s disease, and the first and last name of the doctor inspecting the patient. You need not provide any other functionality. [Refer to Hospital Database] 2. MegaBucks is a banking company that is trying to automate their system. They are hiring people to fill a position for a database developer. They asked to create a simple pilot to Abhijit Pol & Ravindra Ahuja 706 Chapter 16. Database Connectivity decide whether to hire you or not. The pilot is a desktop application that access their database and print the following information about their customers: • Customer’s first and last name. • Customer’s address • Customer’s account type, interest rate, and Current balance. For the purpose of the pilot, you need only to view the required information when the application starts up and you do have to provide any other functionality. [Refer to Megabucks database] 3. A furniture company has a number of distribution centers. Each center is responsible to provide the required supplied of a number of retailers. The company keeps records of all the shipments made to the retailers through the different distribution center. You are asked to design a single-form application, that views the following information about all the shipments made through the distribution centers: [Refer to Furniture Database] • Product name, description, and cost. • Number of items in each shipment. • The retailer name and location. • The Distribution center location. • You need not provide any other functionality. 4. You are working on a project that implements a system that helps people view information about a number of books. Create a simple application that has a single form. Once the application starts up, view the following information about all the books available in the system: [Refer to Books database] • Book’s Title • Author’s first and last name • Publisher’s name You need not provide any other functionality, just view the data. 5. For the previous problem, add a feature that allows the user to pick one of the four viewed columns and then sort the results based on the chosen column. [Refer to Books database.] Abhijit Pol & Ravindra Ahuja Chapter 16. Database Connectivity 707 6. You are asked to create a simple application that views information about the instructors of a certain university and the offered courses at that university. Your application has a single form, which contains two buttons that allow the user to choose the information that he wants to view. Follow the next steps: [Refer to Schools database.] • Create two data adaptors (one for the courses and one for the instructors). • Create a dataset that has both of the tables from the two data adaptors. • Upon your choice bind the data grid to the required table of the dataset. 7. Unlimited Airlines is an airline company. They have an old system that updates the information about their customer’s reservations. They asked you to create a simple application that has the following features: [Refer to Airline database.] • The user must be able to choose a certain flight from a list of all the available flights. • The system will view all the customers’ reservations for the chosen flight. The system should give the user the ability to update any of the customer’s preferences (Meal Preference, Number of infants, payment method, and the reservation’s class). 8. Unlimited airlines, refer to the previous problem, is asking you to create another application. This time, the application should view information about all the available flights. View the following information : [Refer to Airline database] • Flight Number • Start and destination of the flight • Total number of seats • Travel time • Plane type Your application should give the user the ability to update any of that information. 9. You have access to a survey that shows the amount of money collected at a number of theatres at different dates. Design an application, that views the following information: [Refer to Movies database] • Movie Title and category. • Theatre’s name and city. • The show date and the amount of money collected that day. Abhijit Pol & Ravindra Ahuja 708 Chapter 16. Database Connectivity You need also to provide a functionality that will help view the same info but for a specific date [Hint: For choosing the date, use control DateTimePicker]. 10. You have a database that contains information about a number of movies and their actors. Your task is to implement a simple application that views all the movies and their actors. The application should give the user the flexibility to show information about a single movie, a single actor, or all the movies and their actors. For each of the previous cases, view only the actors’ first and last name and the movie title. [Refer to Movies database] Abhijit Pol & Ravindra Ahuja ...
View Full Document

This note was uploaded on 10/20/2008 for the course IST 331 taught by Professor Sikder during the Spring '08 term at Cleveland State.

Ask a homework question - tutors are online