Disconnected Database Access
In previous examples you saw how to connect to the database, how to add, delete, modify, and query the data stored in. All the examples you've seen so far share a common pattern. The program opens a connection to the database, does what it needs to do with the data, and closes the connection. The connection is kept open all along the process. This may not be desirable in the following situations:
- The time to process the data is considerably longer. When you're using
DataReader, extended processing time will tie up the database connection.
- A batch operation is desired. For example, you would like to query all the Shippers information, present it for users to change, and update all the changes in one shot.
- You would like to take the result set of a query offline, and possibly work on it in-memory. For example, a presentation of a result set where the user can sort on different columns interactively.
- Manipulation of data from multiple sources is desired.
- You would like to pass the result set between architectural layers, say to a remote client as a managed object or to a Web Service as a serialized document.
The answer to this in ADO.Net is DataSets, which enable you get the result offline and in-memory, so the database connection need not kept opened all the time. Think of a DataSet as a container of collections. A commonly used collection is the DataTableCollection. The DataTableCollection is a collection of DataTable objects. You can access this collection via the Tables property of the DataSet object, which is the in-memory representation of a relational table. Apart from containing object collections for rows and columns, DataTable contains the DefaultView property, which is used to sort and filter the information in the corresponding DataTable.
To fill data into the DataTable in a DataSet, ADO.NET provides the DataAdapter object. The DataAdapter object is instantiated as
my $myAdapter = SqlDataAdapter->new(
Where $conn is the Connection string to the Data Source and $sql is a SQL query to fetch data. A given instance of DataSet, ($ds) is filled using the DataAdapter's Fill method.
The following example shows how to iterate through the DataTable. Given a table name that is present in the database this program fetches all the data in that table and prints on the standard output.
The above program is compiled as follows (see Listing 4
plc showdata.pl --reference=System.Data.Dll
Running ShowData.exe with Shippers as the parameter produces the following output.
ShipperID CompanyName Phone
1 Speedy Express (503) 555-9831
2 United Package (503) 555-3199
3 Federal Shipping (503) 555-9931
Let's discuss the program step-by-step. Line 11 through 14 make sure that a command line argument is provided. Lines 16 and 17 specify the connection string to connect to the database. In this case, the program will connect to the NorthWind database in the localhost server. The Connection object is created in Line 20. Line 22 creates the SQLDataAdapter object by passing a SQL SELECT statement and the Connection object just created. Lines 25 and 27 create and populate an instance of the DataSet class. The Fill method accepts a second parameter, the table name, which is assigned to a DataTable object and assigned to the DataSet's Tables collection. The Fill method also opens the connection automatically if it was closed before filling the DataSet object. After filling the dataset it returns the connection's state to its original setting. By design, in some sense, DataAdapter and DataSet are disjointed. This allows you to fill a given DataSet with data from different data sources; the connection is assigned only to the DataAdapeter object and not the DataSet object directly, thus enabling disconnected databases.
Line 28 accesses the Table in the collection and successively gets all the column names by accessing the Columns collection of the DataTable objects in Lines 31 through 34. Line 36 gets the number of columns in the given table, by the Count property of the Columns collection. Lines 38 through 45 iterate through the Rows collections and accesses the value stored in each column for that row by referring the Column index to each Row object in the Rows collection.
Disconnected database access is crucial in the Web Applications world. In the rest of the article we'll see how to develop Web Applications and Web services using PerlNET.