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
• 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
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
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
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 object to read the rows from the
database. • InsertCommand
InsertCommand object for inserting rows into the
database. • UpdateCommand – reference to an
UpdateCommand object for modifying rows in
the database. • DeleteCommand
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
option is used for the SQL statement)
Parameters Table 16.1 the Use the build button to invoke the Collection Specify
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...
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
Private Sub cmdLoad_Click (ByVal sender As System.Object...
OleDbDataAdapter1.Fill(DataSet21) End Sub
Private Sub cmdUpdate_Click (ByVal sender As System.Object...
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
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"
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...
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
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?
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
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
- Spring '08
- ........., Ravindra Ahuja, Abhijit Pol