Understanding the ADO.NET Class Hierarchy
ADO.NET defines a bewildering number of classes, but Microsoft has done a good job of arranging them into a meaningful hierarchy. In this section, I'll briefly describe the most important ADO.NET classes. You'll see them in action in the remainder of this chapter.
NpgsqlConnection
This class represents a connection to a PostgreSQL database. You really can't do much with an NpgsqlConnection object other than give it to some other object. Other ADO.NET objects will use an NpgsqlConnection object to interact with a PostgreSQL database.
The most important property defined by an NpgsqlCommand object is the ConnectionString. The ConnectionString tells Npgsql how to connect to a PostgreSQL database, and it looks similar to a typical libpq connection string. Don't be fooled; Npgsql is not based on the libpq client library. Npgsql speaks the same client/server protocol as libpq, but it interacts with the server without getting libpq involved. Instead, the Npgsql developers implemented the PostgreSQL client/server protocol using nothing but .NET managed code.
The NpgsqlCommand class contains a design-time helper dialog that can help you fill in the ConnectionString property. I'll show you how to use the helper (the Data Connection Properties dialog) in the section titled "Client 1Connecting to the Server." The Data Connection Properties dialog is useful when you know, at design time, how to connect to the database. If you don't have that information at design time, you can still use the helper dialog to try out different connection properties and learn how to assemble them into the format required by the NpgsqlCommand class.
NpgsqlCommand
An NpgsqlCommand object represents a single SQL command that you want to execute in a PostgreSQL server. Before you can execute an NpgsqlCommand object, you must link it to an NpgsqlConnection object. At design time, you can link an NpgsqlCommand to a connection by clicking the Connection property (in the Properties window) and choosing one of the NpgsqlConnection objects that you've defined. At run time, you can link an NpgsqlCommand to a connection by modifying the Connection property.
An NpgsqlCommand object holds the text of the command that you want to execute in its CommandText property. When you ask an NpgsqlCommand property to execute its command, it returns a result set.
DataTable
A DataTable (note that it's not an NpgsqlDataTablethis is an ADO.NET class) is an in-memory copy of a result set. Each DataTable contains a name (the TableName property), a collection of column descriptors, and a collection of rows. Each column descriptor describes a single column and is an object of type DataColumn. Each row contains the values for a single row in the result set and is an object of type DataRow. You typi cally find DataTable objects inside of a DataSet (another ADO.NET class), but you can also create and populate a DataTable with your own VB code.
DataRow
A DataRow is an in-memory copy of a single row. Each DataRow contains a collection of column values (one Item for each column in the table). A DataRow also contains a RowState that keeps track of whether you've modified the values in the Item collection. A DataRow can also hold a row-specific error message. You typically find DataRow objects inside of a DataTable's Row collection.
DataColumn
A DataColumn describes a column within a DataTable. Each DataColumn contains a ColumnName property, a DataType property, a MaximumLength property, and so on. DataColumns (and the DataRow that the DataColumns belong to) store the metadata for a result set.
DataSet
The DataSet class is the focus of the ADO.NET class hierarchy. In PostgreSQL terms, a DataSet holds a collection of result sets. In the simplest case, a DataSet contains a single result setboth the data and the metadata returned by a single query. A DataSet can also contain multiple data sets. One of the features that makes a DataSet so useful is that you can use it as the data source for a UI control. When you bind a DataSet to a grid control, you end up with a DataGrid that can display the values in the DataSetno programming required (well, very little anyway). You can also bind a single column within a DataSet to a ListView and display the column values to the user.
If you modify the values in a DataSet, you can ask a DataAdapter object (which I'll tell you about in a moment) to write those changes back to the database. The DataAdapter looks through each row in the data set and executes a series of INSERT, UPDATE, and DELETE commands (as appropriate) to record the changes you've made.
DataSet objects come in two flavors: typed and untyped. A typed DataSet is a class that knows the shape of the data that it holds. A typed DataSet extends the base DataSet class and adds new members (and methods) that provide direct, named access to the tables and columns in the DataSet. For example, if you create a typed DataSet subclass named DSCustomers, based on the customers table, you can write code like this:
Dim dsCustomers As DSCustomers Dim customerName As String Dim birthDate As DateTime customerName = dsCustomers.customers(3).customer_name birthDate = dsCustomers.customers(3).birthDate
A typed DataSet creates first-class objects out of the tables and columns that it contains. When you add a typed DataSet to a Visual Studio project, you can use the graphical tools (schema editors, query builders, and so on) provided by Visual Studio to manipulate the tables and columns in the DataSet. Visual Studio also integrates typed DataSets into its code-completion mechanism (IntelliSense) so it's easier to write code that actually compiles without errors.
An untyped DataSet does not provide named access to the tables and columns that it contains. Instead, each result set lives in the DataSet.Tables array. DataSet.Tables is an array of DataTable objects. A DataTable object contains (among other things) an array of Columns and an array of Rows. Each member of the Columns array is a DataColumn object that describes a single column in the result set (the column name, column data type, and so on). The rows in the result set are stored in the Rows array as a collection of DataRow objects.
In this chapter, I'll show you how to create a VB application that can create a typed DataSet for any table in your database.
A DataSet can be considerably more complex than I've described so far. If a DataSet contains more than one DataTable, you can define parent/child relationships between the tables. Each relationship is defined by a DataRelation that links a key in one table with a key in a second table. You can use the relationships with a DataSet to access the children that belong to a given parent row, or to find the parent row for a given child. You can also ask a DataSet to filter and sort the data that it contains (without getting the backing database involved).
NpgsqlDataAdapter
The NpgsqlDataAdapter class links a DataSet to the database. An NpgsqlDataAdapter object contains (references to) a SELECT command, an INSERT command, an UPDATE command, and a DELETE command. An NpgsqlDataAdapter object fills a DataSet by executing its SELECT command. If you've made changes to a DataSet, you can call the NpgsqlDataAdapter.Update method to copy the changes back to the database. The Update method executes the INSERT, UPDATE, and DELETE commands required to store your modifications in the PostgreSQL database.
The DataSet class is genericyou can use a DataSet object with any database system (you can even use a DataSet object without a database). DataAdapters, on the other hand, are database-specific. The NpgsqlDataAdapter class only works with a PostgreSQL database. An OracleDataAdapter only works with an Oracle database. That's why they are called adaptersthey adapt a database to the needs of a DataSet (or other database-neutral) object.
NpgsqlCommandBuilder
An NpgsqlCommandBuilder object builds the UPDATE, INSERT, and DELETE commands required by an NpgsqlDataAdapter object. An NpgsqlCommandBuilder object works by examining the metadata in a DataRow object that you provide. The NpgsqlCommandBuilder.GetInsertCommand() method, for example, creates an NpgsqlCommand object that INSERTs every column in the given row. You typically create an NpgsqlCommandBuilder object when you want to write DataSet modifications back to the database.
NpgsqlDataReader
The NpgsqlDataReader class provides a "raw" interface to a PostgreSQL result set. If you invoke an NpgsqlCommand's ExecuteReader method, you'll get back an NpgsqlDataReader object that you can use to read through the result set, one row at a time. (You can't skip around in a result set using an NpgsqlDataReader.) You'll rarely need to create an NpgsqlDataReader, since you can use a DataSet to hop around inside of a result set instead.
That gives you a broad overview of the most important ADO.NET classes (and the Npgsql components). ADO.NET defines a number of other classes that you may need in some applications, so be sure to read through the ADO.NET documentation at the MSDN website (msdn.microsoft.com).