.NET for Visual FoxPro Developers : Data Access with ADO.NET

To a Visual FoxPro developer, one of the most important aspects of software development is data access. This chapter shows you how to use Microsoft’s new universal data access technology, ADO.NET, to access and manipulate a wide variety of data—including VFP data. Because there are a number of different ways to access data from ADO.NET, this chapter offers information on the best practices for data access.

If there’s one thing that a Visual FoxPro developer is proud of, it’s VFP’s data access capabilities. From accessing FoxPro data, to client-server data, and XML, VFP is a great tool for building data-centric applications. Based on this, I’m sure most VFP developers are very interested in .NET’s data access model.

This chapter does not attempt to cover all the idiosyncrasies of data access with ADO.NET—there are already plenty of books on the market that do this. Rather, to help you get up and running quickly, this chapter provides an overview of ADO.NET’s capabilities.

However, before diving directly into ADO.NET, the following section gives a brief history of data access from the perspective of FoxPro, because it’s easier to see where you’re going if you know where you’ve been. If you are already familiar with this information, I recommend you skip ahead to the “Introducing ADO.NET” section.

A brief history of data access

Unlike most other software development tools, FoxPro is both a language and a database—you do not need any external technology to access native FoxPro data. This differs from other tools such as Visual Basic where you must rely on external data access technology to retrieve and manipulate data. However, when you need to access non-FoxPro data (such as SQL Server and Oracle) from within FoxPro, you do need external technology to connect to, access, and manipulate that data.

In FoxPro terminology, FoxPro data accessed natively is referred to as “local data”. Data accessed in any other way is known as “remote data”.

Initially, the technology used to access remote data was Microsoft’s Open Database Connection (ODBC).

ODBC—A first attempt at universal data access

ODBC was one of Microsoft’s first attempts at creating a uniform way to access data from FoxPro and other programming tools. To access a particular type of data from within FoxPro (such as SQL Server, Oracle, or Sybase), you had to use an ODBC driver specifically designed to access that type of data. As long as the driver was installed on your computer, you could easily access remote data using remote views or SQL pass through. ODBC was a great tool for accessing just about any type of relational data.

OLE DB and ADO—The successors to ODBC

A few years back, Microsoft introduced OLE DB as its new-and-improved solution to universal data access and as the successor to ODBC. While ODBC allowed you to access most types of relational data, OLE DB went a step further and provided a way to access both relational and non-relational data in a standard way using a COM (Component Object Model) programming interface.

COM defines a software interface standard that specifies, at the binary level, how software components interact. COM acts as a common layer through which all COM-capable tools can communicate.

In OLE DB, the equivalent to ODBC drivers is OLE DB data providers. Again, you need to use an OLE DB data provider designed to access a particular type of data. Visual FoxPro 7 supplied an OLE DB provider that allows non-Fox clients to access Visual FoxPro data.

Microsoft designed OLE DB with C++ in mind, trading ease-of-use for performance. However, in order to make OLE DB accessible to the masses, Microsoft created ActiveX Data Objects (ADO). ADO supplied a high-level COM interface, built on top of OLE DB, and easily used by languages such as Visual FoxPro and Visual Basic.

Rather than using cursors (as in Visual FoxPro) ADO provided data access through a set of COM objects—the object most frequently used being the Recordset. A good number of VFP developers tried their hand at data access using ADO. Although it was relatively easy to retrieve data using ADO, there was a huge performance hit when you tried to “scan” through the records in a Recordset. In addition, it was difficult to integrate an ADO Recordset into a Visual FoxPro application with Visual FoxPro’s data model and user interface based on cursors, rather than data objects. In order to make an ADO Recordset available to the user for viewing and manipulating in a VFP application, you either had to convert it to a VFP cursor, or bind the Recordset to an ActiveX grid.

All of this said, ADO was a big step in the right direction—it did provide universal access to both relational and non-relational data. However, it also had its limitations—especially with n-tier architectures.

Data access and the n-tier architecture model

So, what is n-tier architecture and what impact does it have on data access?

Before answering this question, you should first understand single tier system architecture. In a single tier architecture, all of the application processing takes place on a single computer—this includes user interface, business logic, and data processing. For example, when you have a Visual FoxPro desktop application access FoxPro data (see Figure 1), whether the data is located on your local machine or a machine across your network, all the processing takes place on your workstation. This is because the VFP data engine resides on your workstation.

Figure 1. In a single-tier architecture, all processing occurs on a single computer

In a two-tier system architecture (see Figure 2), the database engine moves from the workstation to a database server. A good example of this is accessing a client-server database such as SQL Server from a Visual FoxPro desktop application. In this scenario, the workstation running the VFP application sends requests to SQL Server. The data processing occurs on the server and the results return back to the VFP application. ADO was originally designed for this client-server, two-tier architecture—where a workstation is continuously connected to a back end data server.

Figure 2. In a two-tier architecture, the database engine moves to a different computer. This is the client-server model.

In a three-tier system architecture (see Figure 3), the application’s business logic is separated from the user interface and placed in business components, creating three unique tiers—user interface (tier 1), business logic (tier 2), and data (tier 3). In this context, business logic includes all application code that performs processing and retrieves or manipulates data. For a more detailed explanation, see Chapter 8, “.NET Business Objects” for details.

Figure 3. In a three-tier architecture, both the data processing and the business logic are separated from the user interface.

So, where does the phrase n-tier architecture come from? A three-tier architecture can be broken down into additional sub-tiers. However, in the final analysis, n-tier architecture is really the same as three-tier architecture.

Passing data between tiers

In an n-tier architecture, data must be passed between each tier of the application—from the data tier to the business tier to the user interface tier and back again. In a distributed Internet environment there are inherent difficulties with passing ADO Recordsets between tiers.

For example, COM technology used to return an ADO Recordset from a web server relies on Remote Procedure Calls (RPCs), which make a server vulnerable to security breaches including denial-of-service attacks. One solution is converting the ADO Recordset to XML, which easily passes through firewalls. Although this is possible, ADO’s XML capabilities are somewhat limited.

Connected and disconnected data

It’s often desirable for n-tier applications to operate in a disconnected fashion. Rather than maintaining an active connection to the back end data, each new data access causes the application to create a new connection, retrieve or update data, then disconnect. This helps free up valuable resources on the data server. Maintaining many active connections consumes a tremendous amount of resources on a data server and bogs down your application’s performance. Freeing up a connection as soon as a transaction completes allows your database to serve many more users.

The architecture of the Internet is inherently disconnected. As you surf the Internet, each request you make from your browser (navigating to a different site, requesting data, sending data, and so on) makes a brand new connection to the web server, rather than maintaining a continuous connection. Although ADO can work in a disconnected architecture, ADO was primarily designed for connected data access.

Introducing ADO.NET

Based on the needs of more modern n-tier and Internet architectures, Microsoft completely reworked ADO for its .NET initiative, and introduced ADO.NET.

When accessing data from Visual FoxPro, you should still use either ODBC or ADO, because VFP is not a .NET language. When accessing data from a .NET application, you can still use ADO, but it’s far better to use ADO.NET.  If you are using C++, you can bypass the overhead of both ADO and ADO.NET and use OLE DB directly.

Conceptually, ADO and ADO.NET are similar. They both provide universal access to relational and non-relational data, however ADO.NET does a much better job! ADO.NET is ADO supercharged—in short, it allows you to work with any software component on any platform that understands XML.

.NET data providers

.NET data providers are not a single class—they are comprised of a set of classes in the .NET Framework optimized for connecting to specific types of data. As of this writing, there are four main data providers in .NET:

·         SQL Server Managed Data Provider– designed for accessing SQL Server 7.0 or later. The classes in this provider are all located in the System.Data.SqlClient namespace.

·         Oracle Managed Data Provider – designed for accessing Oracle data. The classes in this provider are all located in the System.Data.OracleClient namespace.

·         OLE DB Managed Data Provider – designed for accessing any data source available by means of a classic OLE DB provider. The classes in this provider are all located in the System.Data.OleDbClient namespace.

·         ODBC Managed Data Provider – designed for accessing native ODBC drivers. The classes in this provider are all located in the System.Data.Odbc namespace.

The SQL Server Managed data provider delivers fast performance because it bypasses the overhead imposed by both ADO.NET and OLE DB (See Figure 4). It does this by using the native protocol of SQL Server—Tabular Data Stream (TDS). If your application accesses data in SQL Server 7.0 or later, you should definitely use these classes rather than the generic OLE DB classes.

The Oracle Managed data provider delivers fast access to Oracle data using the Oracle Call Interface (OCI). To use this provider, you must have Oracle 8i Release 3 (8.1.7) Client or later installed. If your application accesses Oracle data, you should use the Oracle Managed Provider rather than the OLE DB Managed provider. This data provider was made available after the release of .NET, so you may need to download it from Microsoft’s Web site.

The OLE DB Managed data provider is used to access any other type of data available using a classic OLE DB provider. As shown in Figure 4, using the OLE DB managed provider is slower because an extra level of translation occurs between the data source and the client. This is the way you access Visual FoxPro data from .NET. The .NET client program uses the OLE DB Managed Provider, which in turn accesses data through the Visual FoxPro classic OLE DB Provider.

The ODBC Managed Data Provider provides access to native ODBC drivers. This is useful in situations where you work with legacy data with no OLE DB Provider available. This data provider was made available after the official release of .NET, so if you need to use this provider, you need to download it from Microsoft’s web site.

Although SQL Server is currently the only data-specific managed provider for .NET, other data-specific managed providers will be written to access other popular databases such as Oracle.

Figure 4.  The SQL Server Managed Provider provides fast, direct access to SQL Server 7.0 and higher data. The OLE DB Managed Provider allows you to access any data source available using a classic OLE DB provider.

Although these classes will be discussed later in this chapter, for now, all you need to know is the classic OLE DB data providers return data as COM objects, which can be translated into the higher level ADO Recordset. .NET managed data providers return data as an XML stream, which provides many benefits over COM.

Disconnected DataSets

In ADO.NET, the DataSet is analogous to the ADO Recordset. Unlike the ADO Recordset, the ADO.NET DataSet is designed for disconnected data access (no active connection to a database), which is optimal for distributed, Internet applications.

ADO.NET and XML

In contrast to ADO that had some basic XML functionality, ADO.NET was built from the ground up with advanced XML capabilities.

As mentioned previously, XML is a great data format for passing data between layers of an n-tier application—especially when a web server and firewall are involved—and ADO.NET does this with ease. In addition to passing XML between tiers, the ADO.NET DataSet also allows you to read XML data from a wide variety of data sources as easily as you can read data from a database.

ADO.NET’s XML integration also improves its ability to work with non-relational data. While ADO touted the capability to work with both relational and non-relational data, in reality it was not very straightforward. In contrast, ADO.NET allows you to read XML as easily as reading data from a database, making it much easier to work with non-relational data. You can think of it this way—ADO is designed to be database-centric, working best with data stored in databases. ADO.NET is data-centric, designed to work well with data regardless of where it is stored.

ADO.NET performance

Another improvement ADO.NET gives you is greater performance. ADO is based on COM, which incurs an inherent performance hit. This is because your application communicates with ADO in a non-native way through COM interfaces, adding extra overhead. In addition, because COM objects are not native to the language you’re using, performance suffers when converting native data types in your programming language of choice to COM’s data types.

Because .NET managed data providers are written on top of the Common Language Runtime and Common Type System (see Chapter 1 “Introducing .NET” for details), the need for conversion between data types disappears, providing a great improvement in speed. This is the case with the SQL Server managed data provider (which was also tuned to work specifically with SQL Server data, offering even greater performance). However, in scenarios that use the classic OLE DB Provider, as with the OLE DB Managed Data Provider, this conversion between data types must still occur.

The .NET data classes

The .NET Framework provides a wide variety of classes for accessing data. These can be found in the following namespaces:

·         System.Data – the ADO.NET data access classes.

·         System.Data.Common – common classes shared by all .NET data providers.

·         System.XML – XML processing classes.

·         System.Data.OleDb – classes comprising the .NET data provider for OLE DB-compatible data sources.

·         System.Data.SqlClient – classes comprising the .NET data provider for SQL Server 7.0 and later.

·         System.Data.OracleClient – classes comprising the .NET data provider for Oracle.

·         System.Data.SqlTypes – classes representing SQL Server native data types.

·         System.Data.Odbc – classes comprising the .NET data provider for ODBC.

In the sections that follow, you will learn more about the classes found in each of
these namespaces.

Data-specific classes

Table 1 lists data classes used when working with specific kinds of data. The classes in the first column are used when accessing SQL Server 7.0 and higher. The classes in the second column are used when accessing any type of data by means of OLE DB (the Oracle and ODBC data provider classes are not listed in this table). Each of the classes is described in detail throughout this chapter.

Table 1. This table contains a list of .NET classes used for a specific type of data.

SQL Server classes	Generic OLE DB classes	Description
SqlCommand	OleDbCommand	Represents a SQL statement or stored procedure
SqlCommandBuilder	OleDbCommandBuilder	Automatically generates single-table SQL statements from a SELECT clause for updating, inserting and deleting data
SqlConnection	OleDbConnection	Represents an open connection to a database
SqlDataAdapter	OleDbDataAdapter	Represents a set of data commands (select, update, insert, delete) and a database connection used to fill a DataSet and update the data source
SqlDataReader	OleDbDataReader	Reads a forward-only stream of data from a data source
SqlParameter	OleDbParameter	Represents a parameter to a SQL statement or stored procedure
SqlTransaction	OleDbTransaction	Represents a database transaction

Accessing data with ADO.NET

It’s important to understand that ADO.NET provides two main ways to access data:

1.       Using a DataReader and a data command object

2.       Using a DataSet

In either case, the ADO.NET data access model assumes you connect to the database, retrieve data (or perform an operation), and then close the connection.

You might think that constantly creating, opening, and closing connections would slap a huge performance penalty on your application—after all, it does take time to create new connections. Fortunately, behind the scenes, ADO.NET manages a pool of connections from which new connection requests can be tapped. This significantly increases the performance and scalability of your applications.

Choosing between DataReaders and DataSets

Deciding whether to use a DataReader or a DataSet for accessing data depends on a number of factors. There is a great topic in the .NET Help file titled “Recommendation for Data Access Strategies” to help you decide which methodology to use in a wide variety of situations. The following sections give you the quick lowdown.

Choosing DataReaders and data command objects

When using a DataReader, you first work with a data command object that executes a SQL SELECT statement or stored procedure and returns a DataReader object. You then use the DataReader to retrieve records in the result set.

DataReaders provide forward-only, read-only access to data. They are “forward-only” in the sense that only one record is present in the DataReader at any given time. Each time you read the next record, it replaces the previous record, and you can’t go back to the previous record. Based on this, DataReaders work in a connected fashion—you need to explicitly close the DataReader and the connection after you finish retrieving data.

DataReaders have a performance advantage over DataSets because they communicate directly with the data source, incurring less overhead (in contrast with a DataSet where you manipulate a disconnected “image” of data). This makes a big difference in Web applications. In addition, command objects give you a little more control over SQL SELECT and stored procedure execution. DataSets sacrifice a measure of control for ease-of-use.

If you need to perform tasks such as creating, editing and removing tables, and executing stored procedures, you can’t use a DataSet—you must use a data command object.

One downside of DataReaders is you can’t bind a Windows Forms DataGrid directly to a DataReader object—you can only do this with a DataSet (although you can bind DataReaders directly to a Web Forms DataGrid). Although you can’t bind directly to a DataReader, you can programmatically load data from a DataReader into the DataGrid.

Choosing DataSets

In a number of ways, DataSets are more capable than DataReaders. DataSets can range from containing data from a single table to representing an in-memory database containing multiple tables, relationships, and constraints.

Figure 5 shows a conceptual model of the DataSet. As you can see, a DataSet contains tables that in turn contain DataRows and DataColumns. The DataRow is equivalent to a Visual FoxPro record and a DataColumn is equivalent to a Visual FoxPro field. The DataSet also allows you to create relationships between the DataTables and constraints on the DataColumns.

Figure 5. A DataSet can represent an in-memory database that includes multiple tables, relations, and even constraints.

DataSets are connectionless by default—an initial connection is opened, all requested
data is retrieved from the data source, and then the connection is automatically closed. When you are ready to save changes, you reconnect the DataSet, update the data source with changes you have made, and the DataSet gets updated with changes made by others. Exception (error) handling is an important consideration when creating connections to the data source. For
more information on handling exceptions see Chapter 13, “Error Handling and Debugging
in .NET”.

As discussed in the .NET Help file, DataSets are good for situations where you are working with multiple tables, multiple data sources, moving data between application tiers, exchanging data with other applications, and binding to user interface controls.

Accessing data with a DataReader

As mentioned earlier, when you use a DataReader object, you first work with a data command object that returns a DataReader to you.

Depending on the type of data you’re working with, there are three main .NET data command classes to choose from—the SqlCommand class (for accessing SQL Server data), the OracleCommand class (for accessing Oracle data), and the OleDbCommand class (for accessing OLE DB data sources).

The basic steps for accessing data using this a DataReader and Command object are:

1.       Create a connection to the data source.

2.       Configure a data command object with a SQL statement or the name of a stored procedure to be executed.

3.       Execute the data command.

4.       If the command returns a DataReader, use it to retrieve the result set.

5.       Close the DataReader and the connection.

The following sections provide details for each of these steps. Rather than giving samples using SQL Server, Oracle, and OLE DB data sources, the sample code in the following sections just demonstrates accessing SQL Server. To translate the samples into code that works with OLE DB, you simply replace the “Sql” prefix of all class names with “OleDb” instead. To translate the samples into code that works with Oracle, substitute the “Sql” prefix with “Oracle”.

Because you haven’t learned about error handling yet, (and to make the samples easier to read), I haven’t included any error handling code—which you should definitely include in your real-world data access code. For details on error handling, see Chapter 13, “Error Handling and Debugging in .NET”.

In all of the sample code throughout this chapter, you need to specify you are using classes from the following namespaces:

In C#:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Windows.Forms;

And in Visual Basic .NET:

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Why are there fewer namespaces listed for Visual Basic .NET than for C#? When you build a new Visual Basic .NET Windows application using VS .NET, it automatically imports several namespaces at the project level, including the System, System.Data, and System.Windows.Forms namespaces. This means you don’t have to explicitly import these in your code files. For more information, check out the “Namespaces” section in Chapter 4, “Introduction to Visual Basic .NET”.

Creating a connection to the data source

The first step in accessing data with a command object is creating a connection to the data. You use the .NET SqlConnection class to make this connection to SQL Server data. The following C# code shows how to connect to the SQL Server Northwind database:

The following code specifies a connection to the SQL Server Northwind database, with a user id of “sa” and no password. If you have SQL Server set up differently (a machine other than your local machine with a different user id and password), you need to adjust the connection string values accordingly.

// Build the connection string

string ConnectionString = "server=(local);"+

   "uid=sa;pwd=;"+

   "database=Northwind";

// Create and open the connection

SqlConnection Connection = new SqlConnection(ConnectionString);

Connection.Open();

And here’s the code in Visual Basic .NET:

' Build the connection string

Dim ConnectionString As String = "server=(local);" + _

  "uid=sa;pwd=;" + _

  "database=Northwind"

' Create and open the connection

Dim Connection As SqlConnection = New SqlConnection(ConnectionString)

Connection.Open()

Configuring a data command object

Once you create and open a connection, you define and execute commands against the back end data. For example, the following C# code configures the data command object with a SQL SELECT command for retrieving the first name, last name, and title from the Northwind database Employees table (you actually execute the data command in the next step):

// Configure the data command object

string SelectCmd = "SELECT TOP 3 LastName, FirstName, Title FROM Employees";

SqlCommand Command = new SqlCommand(SelectCmd, Connection);

And here’s the code in Visual Basic .NET:

' Configure the data command object

Dim SelectCmd As String = "SELECT TOP 3 LastName, FirstName, Title FROM Employees"

Dim Command As SqlCommand = New SqlCommand(SelectCmd, Connection)

Executing a data command

Once you have configured the data command object, there are a few methods you can call
on the object to actually execute the command associated with the object. The method you
choose is based on the type of command you are executing. Here are the primary methods you can call:

·         ExecuteReader – Used to execute a command and return a DataReader object (i.e. SqlDataReader, OleDbDataReader). This method is normally used to execute SQL SELECT statements or stored procedures that return data.

·         ExecuteNonQuery – Used to execute a SQL statement and return the number of rows affected. Often used for UPDATE, INSERT, or DELETE commands.

·         ExecuteScalar – Used to execute a command and return a single value (such as a record count).

·         ExecuteXmlReader (available only on the Sql Provider) – Used to execute a command and return an XmlReader object

For this example, I use the ExecuteReader method to retrieve records from the data source. Here’s the code in C#:

// Execute the command which returns a SqlDataReader object

SqlDataReader Reader = Command.ExecuteReader();

And in Visual Basic .NET:

' Execute the command which returns a SqlDataReader object

Dim Reader As SqlDataReader = Command.ExecuteReader()

Processing the return value

As mentioned in the previous section, different types of values are returned depending on the type of command executed. In the following example, executing the command returns a DataReader object.

Unlike a DataSet, which returns all requested records from the data source, a DataReader object only contains one row at a time. You could compare it to a cursor with a single row of data. When a data command object first returns a DataReader, it doesn’t contain any data. You must call the DataReader’s Read method to get the first record in the result set, call it again to get the second row, and so on. Each time you call the Read method, the row previously stored in the DataReader is replaced by the new row.

The following C# code cycles through the rows in the result set and displays them in a message box. In this example, I am using the DataReader’s GetString method to access the three columns (0 = LastName, 1 = FirstName, and 2 = Title):

string Employees = "";

// Get the next record

while (Reader.Read())

{

  // Access the data in the current row

  Employees += Reader.GetString(0) + ", " +

    Reader.GetString(1) + " - " + Reader.GetString(2) + "\n";

}

MessageBox.Show(Employees, "DataReader Demo");

The code in Visual Basic .NET:

Dim Employees As String = ""

' Get the next record

While Reader.Read()

' Access the data in the current row

Employees += Reader.GetString(0) + ", " + _

  Reader.GetString(1) + " - " + Reader.GetString(2) + Chr(13) + Chr(10)

End While

MessageBox.Show(Employees, "DataReader Demo")

Rather than using the DataReader’s GetString method to retrieve the column values, you could simply access each column by using the syntax Reader[0], Reader[1], and Reader[2]. However, if you know the type of the data in a particular column, you’ll get better performance retrieving the data by using the DataReader method that corresponds to the value type of the column. The DataReader class has a number of additional type-specific “Get” methods such as GetBoolean, GetByte, GetDateTime, and GetDecimal for this purpose.

In Chapter 9, “Building .NET Windows Forms Applications” and Chapter 10, “Building Web Applications with ASP.NET”, you will see a more meaningful use for the data returned from a DataReader object.

Closing the connection

Because the DataReader is a connected object, you need to wait until you are done with the DataReader before closing the connection. When you’re ready, close the DataReader first, then close the connection (this code is the same in C# and Visual Basic .NET):

Reader.Close()

Connection.Close()

Pulling it all together

So you can get a sense for the entire flow, here’s all the code snippets pulled together.  Here’s the C# code:

// Build the connection string

string ConnectionString = "server=(local);"+

   "uid=sa;pwd=;"+

   "database=Northwind";

// Create the connection

SqlConnection Connection = new SqlConnection(ConnectionString);

// Open the connection

Connection.Open();

// Configure the data command object

string SelectCmd = "SELECT TOP 3 LastName, FirstName, Title FROM Employees";

SqlCommand Command = new SqlCommand(SelectCmd, Connection);

// Execute the command which returns a SqlDataReader object

SqlDataReader Reader = Command.ExecuteReader();

// Display the records

string Employees = "";

// Get the next record

while (Reader.Read())

{

  // Access the data in the current row

  Employees += Reader.GetString(0) + ", " +

    Reader.GetString(1) + " - " + Reader.GetString(2) + "\n";

}

MessageBox.Show(Employees, "DataReader Demo");

Reader.Close()

Connection.Close()

And in Visual Basic .NET:

' Build the connection string

Dim ConnectionString As String = "server=(local);" + _

  "uid=sa;pwd=;" + _

  "database=Northwind"

' Create the connection

Dim Connection As SqlConnection = New SqlConnection(ConnectionString)

' Open the connection

Connection.Open()

' Configure the data command object

Dim SelectCmd As String = "SELECT TOP 3 LastName, FirstName, Title FROM Employees"

Dim Command As SqlCommand = New SqlCommand(SelectCmd, Connection)

' Execute the command which returns a SqlDataReader object

Dim Reader As SqlDataReader = Command.ExecuteReader()

' Display the records

Dim Employees As String = ""

' Get the next record

While Reader.Read()

' Access the data in the current row

Employees += Reader.GetString(0) + ", " + _

  Reader.GetString(1) + " - " + Reader.GetString(2) + Chr(13) + Chr(10)

End While

MessageBox.Show(Employees, "DataReader Demo")

Reader.Close()

Connection.Close()

Accessing data using DataSets

There are two main ways to insert data into a DataSet—you either use a data adapter to
fill the DataSet or you read XML directly into the DataSet. First, I will demonstrate using a data adapter.

Loading a DataSet with a data adapter

The basic steps for loading data using a data adapter are:

1.       Configure a data adapter with a command string and a connection string

2.       Fill the DataSet using the data adapter

Configuring a data adapter

A data adapter serves as a conduit between the DataSet and the data source. Its primary purpose is to populate the DataSet with data retrieved from the data source and return changed data back to the data source. Using a data adapter prevents the DataSet from being coupled too tightly to a specific type of database.

Here is the code in C# for configuring a data adapter with a command string and a connection string:

// Build the connection string

string ConnectionString = "server=(local);"+

   "uid=sa;pwd=;"+

   "database=Northwind";

// Build the command string

string CommandString = "SELECT TOP 3 LastName, FirstName, Title FROM Employees";

// Create the data adapter, passing the command and connection strings

SqlDataAdapter DataAdapter = new SqlDataAdapter(CommandString, ConnectionString);

And in Visual Basic .NET:

' Build the connection string

Dim ConnectionString As String = "server=(local);" + _

  "uid=sa;pwd=;" + _

  "database=Northwind"

' Build the command string

Dim CommandString As String = "SELECT TOP 3 LastName, FirstName, Title FROM Employees"

' Create the data adapter, passing the command and connection strings

   Dim DataAdapter As SqlDataAdapter = New SqlDataAdapter(CommandString, ConnectionString)

At this point the data adapter is configured, but no data has been read from the back
end yet.

Fill the DataSet using the data adapter

In this next step, a DataSet object is instantiated, and then the data adapter’s Fill method is called. When Fill is executed, the data adapter converts the SQL SELECT string into a SqlCommand object, creates a SqlConnection from the connection string you specified, executes the command, and fills the DataSet with records returned in the result set.

// Instantiate a DataSet object

DataSet ds = new DataSet();

// Fill the DataSet

DataAdapter.Fill(ds);

And in Visual Basic .NET:

' Instantiate a DataSet object

Dim ds As DataSet = New DataSet()

' Fill the DataSet

DataAdapter.Fill(ds)

Pulling it all together

Here is all the code pulled together in one place, with the addition of code that displays the result set in a message box:

// Build the connection string

string ConnectionString = "server=(local);"+

   "uid=sa;pwd=;"+

   "database=Northwind";

// Build the command string

string CommandString = "SELECT TOP 3 LastName, FirstName, Title FROM Employees";

// Create the data adapter, passing the command and connection strings

SqlDataAdapter DataAdapter = new SqlDataAdapter(CommandString, ConnectionString);

// Instantiate a DataSet object

DataSet ds = new DataSet();

// Fill the DataSet

DataAdapter.Fill(ds);

//  Get the table from the DataSet

DataTable DTable = ds.Tables[0];

// Display the records

string Employees = "";

foreach (DataRow DRow in DTable.Rows)

{

  Employees += DRow[0] + ", " +

       DRow[1] + " – " + DRow[2] + "\n";

}

MessageBox.Show(Employees, "DataSet Demo");

Here is the Visual Basic .NET equivalent:

Dim ConnectionString As String = "server=(local);" + _

  "uid=sa;pwd=;" + _

  "database=Northwind"

' Build the command string

Dim CommandString As String = "SELECT TOP 3 LastName, FirstName, Title FROM Employees"

' Create the data adapter, passing the command and connection strings

Dim DataAdapter As SqlDataAdapter = New SqlDataAdapter(CommandString, ConnectionString)

' Instantiate a DataSet object

Dim ds As DataSet = New DataSet()

' Fill the DataSet

DataAdapter.Fill(ds)

'  Get the table from the DataSet

Dim DTable As DataTable = ds.Tables(0)

' Display the records

Dim Employees As String = ""

Dim DRow As DataRow

For Each DRow In DTable.Rows

  Employees += DRow(0) + ", " + _

  DRow(1) + " – " + DRow(2) + Chr(13) + Chr(10)

Next

MessageBox.Show(Employees, "DataSet Demo")

As you can see in this code, once you fill a DataSet with data, you access the data using the different DataSet collections. For example, you can access tables in a DataSet by using the Tables collection. In the previous example, there was only one table in the DataSet and I referenced it using the following syntax (where ds references a DataSet):

 ds.Tables[0]

The sample code accesses DataRows in the DataSet by using the foreach command. You can also reference a DataRow by means of the DataTable’s Rows collection. For example, you access the first row in a DataTable this way (where dt references a DataTable):

dt.Rows[0]

You can also reference the different DataSet elements by using “friendly” names. For example, if you want to access a table named “Employee” within a DataSet, you use the syntax:

ds.Tables["Employees"]

If you want to access a DataColumn named “Address”, you use the following syntax (where dt references a DataTable):

dt.Rows[0]["Address"]

Although friendly names are easier to read, using them in your applications incurs a measurable performance hit due to the extra lookup the DataSet needs to do to convert the friendly name into an index. For an alternate way to access data using friendly names, see the “Typed DataSets” section later in this chapter.

Loading XML into a DataSet

The DataSet’s overloaded ReadXml method makes it very easy to read native XML into a DataSet from a wide variety of sources. The following sample shows how to use the DataSet to read XML from a file on disk and afterwards display the data in a message box.

In C#:

DataSet ds = new DataSet();

ds.ReadXml("chapters.xml");

DataTable DTable = ds.Tables[0];

// Display the records

string Chapters = "";

foreach (DataRow DRow in DTable.Rows)

{

  Chapters += DRow[1] + "\n";

}

MessageBox.Show(Chapters, "DataSet XML Demo");

In Visual Basic .NET:

Dim ds As DataSet = New DataSet()

ds.ReadXml("chapters.xml")

Dim DTable As DataTable = ds.Tables(0)

' Display the records

Dim Chapters As String = ""

Dim DRow As DataRow

For Each DRow In DTable.Rows

  Chapters += DRow(1) + Chr(13) + Chr(10)

Next

MessageBox.Show(Chapters, "DataSet XML Demo")

The chapters.xml file is actually used in this book’s sample application to data drive the combo box on the Samples form.

Invoking stored procedures

In most database applications, you need to execute stored procedures. For example, you may use a stored procedure to generate primary keys for your tables. You may also use stored procedures to insert, update, and delete data. In .NET, you can execute stored procedures by means of a data command object. This section shows you a simple example of how to execute a stored procedure. Here are the basic steps:

·         Create and open a connection to the data source.

·         Create a data command object, passing the name of the stored procedure and the connection object.

·         Configure the data command object.

·         Execute the command.

Here is an example of code you can use to create a connection and a data command object. Again, the example uses the SQL Server Northwind database.

First, you need to build the connection string. Here it is in C#:

string ConnectionString = "server=(local);"+

   "uid=sa;pwd=;"+

   "database=Northwind";

And in Visual Basic .NET:

Dim ConnectionString As String = "server=(local);" + _

  "uid=sa;pwd=;" + _

  "database=Northwind"

Next, you instantiate a SqlConnection object, passing the connection string to the constructor. Afterwards, you can open the connection.

In C#:

SqlConnection Connection = new SqlConnection(ConnectionString);

Connection.Open();

In Visual Basic .NET:

Dim Connection As SqlConnection = New SqlConnection(ConnectionString)

Connection.Open()

The next step involves instantiating a SqlCommand object and passing the name of the stored procedure to be executed (in this case, CustOrderHist) and the connection object.

In C#:

SqlCommand Command = new SqlCommand("CustOrderHist", Connection);

In Visual Basic .NET:

Dim Command As SqlCommand = New SqlCommand("CustOrderHist", Connection)

You also need to tell the data command object that the type of command to be executed is a stored procedure. You do this by setting its CommandType property to the enumeration value CommandType.StoredProcedure.

Command.CommandType = CommandType.StoredProcedure;

If the stored procedure you are executing accepts parameters, you need to tell the data command object details about each one. In this case, the CustOrderHist stored procedure accepts a single parameter named CustomerID, whose type is NChar and size is 5.

Command.Parameters.Add(new SqlParameter ("@CustomerID",

  SqlDbType.NChar,

  5));

Next, you need to set the value that you want to pass in the parameter to the stored procedure. In the example, I have hard-coded the customer ID value of “ALFKI”, the ID of the first customer in the Northwind database (typically, you store a user-specified value in the Value property instead)

Command.Parameters[0].Value = "ALFKI";

Finally, you are ready to execute the command. As mentioned previously in the “Executing a data command” section, there are a few different methods that can be called on the data command object, depending on the type of command you are executing. In this case, I call the ExecuteReader method because I want to return a data reader object I can use to view the result set. Alternately, I could have filled a DataSet with the result set by assigning the data command object to a data adapter and running its Fill method.

In C#:

SqlDataReader DataReader = Command.ExecuteReader();

And in Visual Basic .NET

Dim DataReader As SqlDataReader = Command.ExecuteReader()

The following code simply iterates through the result set and displays it in a message box. Afterwards, the DataReader and Connection are closed.

Pulling it all together

Here is all the C# code used to execute this stored procedure. I have added code at the end
to display the result set stored in the DataReader, as well as code to close the DataReader
and the Connection.

// Build the connection string

string ConnectionString = "server=(local);"+

  "uid=sa;pwd=;"+

  "database=Northwind";

// Open the connection

SqlConnection Connection = new SqlConnection(ConnectionString);

Connection.Open();

// Create a data command object passing the name

// of the stored procedure and the connection object

SqlCommand Command = new SqlCommand("CustOrderHist", Connection);

// Specify that the command type is a stored procedure

Command.CommandType = CommandType.StoredProcedure;

// Specify a single stored procedure parameter

// named CustomerID, of type NChar and size of 5

Command.Parameters.Add(new SqlParameter ("@CustomerID",

  SqlDbType.NChar,

  5));

// Specify the value of the parameter

Command.Parameters[0].Value = ALFKI;

// Execute the command

SqlDataReader DataReader = Command.ExecuteReader();

// Get the next record in the result set

string OrderHist = "";

while (DataReader.Read())

{

  // Access the data in the current row

  OrderHist += DataReader.GetString(0) + ", " +

       DataReader.GetInt32(1) + "\n";

}

// Display the order history`

MessageBox.Show("Order History for customer " +

  customerID + "\n\n" +

  OrderHist, "Stored Procedure Demo");

DataReader.Close();

Connection.Close();

And here it is in Visual Basic .NET:

' Build the connection string

Dim ConnectionString As String = "server=(local);" + _

  "uid=sa;pwd=;" + _

  "database=Northwind"

' Open the connection

Dim Connection As SqlConnection = New SqlConnection(ConnectionString)

Connection.Open()

' Create a data command object passing the name

' of the stored procedure and the connection object

Dim Command As SqlCommand = New SqlCommand("CustOrderHist", Connection)

' Specify that the command type is a stored procedure

Command.CommandType = CommandType.StoredProcedure

' Specify a single stored procedure parameter

' named CustomerID, of type NChar and size of 5

Command.Parameters.Add(New SqlParameter("@CustomerID", _

  SqlDbType.NChar, _

  5))

' Specify the value of the parameter

Command.Parameters(0).Value = customerID

' Execute the command

Dim DataReader As SqlDataReader = Command.ExecuteReader()

' Command.Exec

' Get the next record in the result set

Dim OrderHist As String = ""

While DataReader.Read()

  ' Access the data in the current row

  DataReader.GetInt32(1).ToString() + Chr(13) + Chr(10)

End While

' Display the order history`

MessageBox.Show("Order History for customer " + _

  customerID + Chr(13) + Chr(10) + Chr(13) + Chr(10) + _

  OrderHist, "Stored Procedure Demo")

DataReader.Close()

Connection.Close()

In addition to using stored procedures to retrieve data, you can also create and execute stored procedures that perform functions such as inserting, updating, and deleting records. In the next section, you will see how to perform these tasks using a DataSet.

Updating Data Using DataSets

In the “Accessing data using DataSets” section, you saw how a DataSet works in conjunction with a data adapter to retrieve data. This same sort of relationship is used when updating data. To understand how this works, you need to learn more about data adapters.

Understanding data adapters

Data adapters need to be given specific instructions on how to handle selects, updates, inserts, and deletes. The data adapter has four properties that allow you to do this:

·         SelectCommand

·         UpdateCommand

·         InsertCommand

·         DeleteCommand

You configure and store in each of these properties an instance of a data command object that tells the data adapter how to carry out each type of data manipulation (update, insert, and delete). Previously, in the examples on accessing data using DataSets, you used the data adapter to retrieve data. The sample code passed a command string and connection string to the constructor of the data adapter. For example in C#:

SqlDataAdapter DataAdapter = new SqlDataAdapter(CommandString, ConnectionString);

And in Visual Basic .NET:

Dim DataAdapter As SqlDataAdapter = New SqlDataAdapter(CommandString, ConnectionString)

The data adapter takes the command string, automatically creates a data command object, and stores it in the SelectCommand property.

To update data using a data adapter, you should set the other three properties. Technically, you don’t need to set all three (for example, if there are no new records, you wouldn’t need to specify an InsertCommand object). However, because you may not know if a user has inserted, deleted, or updated records, you simply set all three properties.

If you use stored procedures for updating, inserting, and deleting data, you can manually configure data command objects to execute these stored procedures as demonstrated in the “Invoking stored procedures” section. This can be quite an involved process! However, given the right conditions, you can use a command builder to create the data command objects for you, rather than using stored procedures.

Using command builders

The .NET Framework’s SqlCommandBuilder or OleDbCommandBuilder are command objects you instantiate and use to automatically create update, insert, and delete command objects. However, a command builder can only be used in the following situations:

·         The DataSet being updated must only contain data from a single source table.

·         You must specify at least one primary key or unique value column in the source table.

·         The DataSet table must contain the primary key or unique value column.

·         There can be no special characters in the table name.

Once you verify that all these conditions are true, it’s very easy to use the command builder. The following code demonstrates retrieving a record from the Northwind database’s Supplier table, changing the supplier name, and then updating the data.

I’ve shown the retrieval and update code as two methods contained within a Supplier class. The GetSupplierID method should be familiar because you’ve already seen similar code for filling a DataSet with SQL Server data. The Save method contains the new code that implements a command builder. As you can see, all you need to do is instantiate a command builder, call its GetDeleteCommand, GetUpdateCommand and GetInsertCommand methods, and then store the returned command object in the corresponding data adapter’s properties. Afterwards, you simply call the data adapter’s Update method and the data is persisted back to SQL Server. Here’s the C# code:

public class Supplier

{

  protected SqlDataAdapter DataAdapter;

  public DataSet GetSupplierByID(int supplierID)

  {

    // Build the connection string

    string ConnectionString = "server=(local);"+

       "uid=sa;pwd=;"+

       "database=Northwind";

    // Build the command string

    string CommandString = "SELECT supplierid, CompanyName" +

       " FROM Suppliers WHERE supplierid = " + supplierID;

    // Create the data adapter, passing the command and connection strings

    DataAdapter = new SqlDataAdapter(CommandString, ConnectionString);

    // Instantiate a DataSet object

    DataSet ds = new DataSet();

     // Fill the DataSet

    DataAdapter.Fill(ds);

    return ds;

  }

  /// Save the specified DataSet

  public int Save(DataSet ds)

  {

    // Create a Command Builder and build the delete, update and insert commands

    SqlCommandBuilder CommandBuilder = new SqlCommandBuilder(DataAdapter);

    DataAdapter.DeleteCommand = CommandBuilder.GetDeleteCommand();

    DataAdapter.UpdateCommand = CommandBuilder.GetUpdateCommand();

    DataAdapter.InsertCommand = CommandBuilder.GetInsertCommand();

    // Update the data in thet DataSet

    int RowsUpdated = DataAdapter.Update(ds, ds.Tables[0].ToString());

    return RowsUpdated;

  }

}

And here it is in Visual Basic .NET:

Public Class Supplier

  Protected DataAdapter As SqlDataAdapter

  Function GetSupplierByID(ByVal supplierID As Integer) As DataSet

       ' Build the connection string

       Dim ConnectionString As String = "server=(local);" + _

          "uid=sa;pwd=;" + _

          "database=Northwind"

       ' Build the command string

       Dim CommandString As String = "SELECT supplierid, CompanyName" + _

          " FROM Suppliers WHERE supplierid = " + supplierID.ToString()

       ' Create the data adapter, passing the command and connection strings

       DataAdapter = New SqlDataAdapter(CommandString, ConnectionString)

       ' Instantiate a DataSet object

       Dim ds As DataSet = New DataSet()

       ' Fill the DataSet

       DataAdapter.Fill(ds)

       Return ds

  End Function

  '/ <summary>

  '/ Save the specified DataSet

  '/ </summary>

  '/ <param name="ds"></param>

  Public Function Save(ByVal ds As DataSet) As Integer

       ' Create a Command Builder and build the delete, update and insert commands

       Dim CommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(DataAdapter)

       DataAdapter.DeleteCommand = CommandBuilder.GetDeleteCommand()

       DataAdapter.UpdateCommand = CommandBuilder.GetUpdateCommand()

       DataAdapter.InsertCommand = CommandBuilder.GetInsertCommand()

       ' Update the data in thet DataSet

       Dim RowsUpdated As Integer = DataAdapter.Update(ds, ds.Tables(0).ToString())

       Return RowsUpdated

  End Function

End Class

The data adapter returns the number of rows that were updated, including deleted, updated, and inserted records.

Typed DataSets

There are two main types of DataSets in .NET—typed and untyped DataSets. This chapter has only looked at untyped DataSets created on the fly by executing SQL SELECT statements. You can also create typed DataSets whose structures are defined by using Visual Studio .NET design tools. Based on the fact that typed DataSets make coding much easier, they may be your best choice for data access.

For more information on typed DataSets, see Chapter 11, “.NET XML” for details.

Deploying a .NET application with data access

When deploying a .NET application that accesses data, you must be sure to have Microsoft Data Access Components (MDAC) version 2.7 or higher installed on the target machine.

You do this by adding a launch condition to your .NET project in the Solution Explorer. For more information, see the .NET Help topic “Adding a Launch Condition for Microsoft Data Access Components”.

Accessing Visual FoxPro Data from ADO.NET

This chapter wouldn’t be complete without an overview of how to access Visual FoxPro data using ADO.NET.

To access VFP data, you need to use the OLE DB Managed Data Provider (vfpoledb.dll). Using this provider you can retrieve and update data, as well as run stored procedures. The following example demonstrates how to load VFP data into a Data Reader. I’ve taken the code used previously to load SQL Server data into a DataReader and adapted it for Visual FoxPro.

The first change I made was adding a reference to the System.Data.OleDb namespace, the namespace of the OLE DB Data Provider classes. The next change I made was to search for class names beginning with the string “Sql” and replace it with “OleDb”. The next obvious change was to the connection string. The OLE DB connection string is slightly different than that of SQL Server. For Visual FoxPro data, you simply need to specify the Provider (vfpoledb.1) and the Data Source (in this case, samples.dbc).

There was one other subtle change that the SELECT statement needed. In Visual FoxPro, when you use the TOP keyword, you must specify an ORDER, so I added “ORDER BY employeeid” to the end of the SQL SELECT string. These changes demonstrate the need to be aware of the idiosyncrasies of each type of data used by your application.

Here’s the C# code in its entirety:

using System.Data.OleDb;

/// Build the connection string

string ConnectionString = "Provider=vfpoledb.1;Data Source=samples.dbc";

/// Create the connection

OleDbConnection Connection = new OleDbConnection(ConnectionString);

/// Open the connection, access the data and close the connection

Connection.Open();

string SelectCmd = "SELECT TOP 3 LastName, FirstName, Title FROM Employees

  ORDER BY employeeid";

OleDbCommand Command = new OleDbCommand(SelectCmd, Connection);

OleDbDataReader Reader = Command.ExecuteReader();

// Get the next record in the result set

string Employees = "";

while (Reader.Read())

{

  // Access the data in the current row

  Employees += Reader.GetString(0) + ", " +

       Reader.GetString(1) + " - " + Reader.GetString(2) + "\n";

}

MessageBox.Show(Employees, "Data Reader Demo");

Reader.Close();

Connection.Close();

And here’s the Visual Basic .NET code:

Imports System.Data.OleDb

'/ Build the connection string

Dim ConnectionString As String = "Provider=vfpoledb.1;Data Source=samples.dbc"

'/ Create the connection

Dim Connection As OleDbConnection = New OleDbConnection(ConnectionString)

'/ Open the connection, access the data and close the connection

Connection.Open()

Dim SelectCmd As String = "SELECT TOP 3 LastName, FirstName, Title FROM Employees ORDER BY employeeid"

Dim Command As OleDbCommand = New OleDbCommand(SelectCmd, Connection)

Dim Reader As OleDbDataReader = Command.ExecuteReader()

' Get the next record in the result set

Dim Employees As String = ""

While Reader.Read()

  ' Access the data in the current row

  Employees += Reader.GetString(0) + ", " + _

  Reader.GetString(1) + " - " + Reader.GetString(2) + Chr(13) + Chr(10)

End While

MessageBox.Show(Employees, "Data Reader Demo")

Reader.Close()

Connection.Close()

As of the writing of this chapter, the Visual FoxPro OLE DB Provider does not work properly with the Command Builder. The Fox team was made aware of the problem, so there should hopefully be an updated version of the provider available by the time you read this.

Conclusion

As you can see, accessing data with ADO.NET is quite a bit more complex than using Visual FoxPro! There are a variety of ways available for accessing data—primarily by using DataSets or DataReaders and command objects. Be sure to evaluate your needs carefully before deciding which data access method to use in any given situation.