The Disconnected Database Scenario

One of the innovations that ADO.NET introduces is working in the disconnected manner. The DataSet class implements an in-memory lightweight database. You can fill the DataSet with the information from the database to manipulate your data inside the DataSet without being connected and propagate the changes to the database server as necessary.

When working with DataSet, you populate it with the DataTable class objects that represent tables. You may have several tables inside one DataSet. This way you may even build in-memory relational databases.

The DataSet and DataTable classes are independent of any data provider. Therefore, we need an adapter, which will mediate between our DataSet objects and database server. The SqlDataAdapter class plays the role of such mediator. All the operations that involve both DataSet class instances and the database will pass through SqlDataAdapter.

You do not have to fill the DataSet objects with information from the database. This means that you may create a temporary in-memory database for your PerlNET application if this makes sense.

DataSet and DataTable

The DataSet and DataTable classes reside in the System.Data namespace. As we mentioned before, they are not bound to any data provider. Therefore, we may work with them independently on any data source.

You instantiate DataSet objects as any other .NET class objects by calling the new method. Normally, you either pass no parameters to the constructor or pass the string with the name that you give to your DataSet object.

use namespace "System.Data";
. . .
my $ds = DataSet->new();
my $dsWithName = DataSet->new("MyDataset");

After you create a new DataSet instance, it is a good idea to populate it with some data—after all, this is why we need DataSet. We have two options: obtain the data from the database server or work with our own data. For now, we use the latter, as we have not yet learned about the data adapters.

The DataSet class exposes the Tables property that represents DataTableCollection. We may add a new table by calling one of the three versions of the Add method on the Tables property:

• DataTable Add()

• void Add(DataTable)

• DataTable Add(String)

The first and third versions will create an empty table inside the DataSet and return a reference that you may use to alter your table. The difference is that the first version creates the DataTable with the automatic name and the third version assigns to the table the name that you pass as a parameter. The second version accepts a DataTable reference to add to the Tables collection. The code may look like follows.

# Create DataSet
my $ds = DataSet->new();
# Add "Emails" Table and obtain reference to it
my $dt = DataSet->{Tables}->Add("Emails");

No matter what version we choose, we should know more about working with DataTable class objects.

DATATABLE CLASS

As we stated, you obtain a reference to the DataTable class either by instantiating a new object with the new method or as a return value of the two versions of the DataTableCollection.Add method. In any case, we obtain an empty table without defined columns. Before populating our table with rows of data, we have to define the table structure^[7] —the list of columns and their types, and optionally keys and constraints, as in any other database environment like SQL Server.

^[7] We don't have to make such definitions of the table structure when working through data adapters.

COLUMNS

We alter our table structure by adding new DataColumn objects through the Columns property, which represents DataColumnCollection. The DataColumn class has multiple properties that you may set, such as name of the column, its type, and allowance of NULL value. We modify these values according to our application requirements.

The following sample creates a DataTable object for storing emails and defines the structure of the table (two columns: ID and Email). The code resides in the DataTable folder.

#
# DataTable.pl
#
use namespace "System";
use namespace "System.Data";
use PerlNET qw(enum AUTOCALL);

# Create empty table "Emails"
my $dt = DataTable->new("Emails");
# Add new column ID to the table
my $colID = $dt->{Columns}->Add("ID");
# Alter ID column type
$colID->{DataType} = Type->GetType("System.Int32");
# Create new column Email
my $colEmail = DataColumn->new("Email");
# Alter Email column type
$colEmail->{DataType}= Type->GetType("System.String");
# Add Email column to the "Emails" table
$dt->{Columns}->Add($colEmail);

CONSTRAINTS

There often arises a need to control the data in our tables by enforcing constraints. For example, we may define that our Email column of the Emails table should have unique values for each row. Also, we may want to define a primary key for the whole table. The DataTable class exposes the Constraints property, which represents the collection of constraints. By default, this collection is empty. We may add new constraints with the Add method.

Here is how we may define the ID column as our primary key:

$dt->{Constraints}->Add("PK_ID", $colID, PerlNET::true);

This statement creates a unique constraint on our table. We specify the constraint name and the column we choose as our key, and the true value indicates that this is the primary key constraint. If the last parameter is false, then the new constraint will be unique, but not a primary key.

Note that the above form of setting a primary key allows you to choose only one column. There are many cases when the primary key consists of several columns. To resolve this situation, you may directly modify the PrimaryKey property of your DataTable. You should assign this property with an array of DataColumn objects. Suppose that our Emails table's primary key should consist of the ID and Email columns. The following fragment sets both columns to be primary key:

my $EmailsPK = "DataColumn[]"->new($colID, $colEmail);
$dt->{PrimaryKey} = $EmailsPK;

POPULATING DATATABLE

Now that we know how to define the structure of our table and add constraints, we can fill our tables with some data. We use the NextRow method on the DataTable object. It returns a reference to the DataRow class instance. Then, we may set the values of the columns. The DataRow class exposes the indexed property Item. We can access it and pass an integer or string value as indexes. In PerlNET we may use square-brackets syntax for integer index only. When working with other types, we call the set_Item method explicitly. We demonstrate both ways in the following example:

# Obtain DataRow for "Emails" table
my $row = $dt->NewRow();
# Set ID using syntactic sugar
$row->[0] = "1";
# Set Email by calling set_Item method
$row->set_Item("Email", "[email protected]");
# Add the row
$dt->{Rows}->Add($row);

DataSet and DataTable Summary

In the previous subsections you acquired the techniques of working with the DataSet and DataTable classes. We showed how to fill them with the data that is independent of any external data source. However, the best way to utilize these classes is to work against database servers in the disconnected mode. This is done with the help of Data Adapters, discussed next.

Data Adapters

Each data provider (OLE DB, SQL Server, etc.) defines its own data provider class (OleDbDataAdapter, SqlDataAdapter, etc.) to communicate with data sources. Our samples are SQL Server data provider-oriented. The transition to OLE DB can be made by correcting the appropriate class name's prefixes.

FILLING DATASETS

To retrieve some data from the data source and fill in the dataset, we first create a SqlDataAdapter object. Then, we set the SelectCommand property, assigning to it the SqlCommand object. Finally, we call the Fill method on the data adapter, specifying to it the dataset to fill. The code fragment below populates the dataset, selecting all the rows from the People table.

my $conn = SqlConnection->new($connstr);
my $pplAdapter = SqlDataAdapter->new();
my $SqlCmd = SqlCommand->new("select * from People",
                                              $conn);
$pplAdapter->{SelectCommand} = $SqlCmd;
my $pplDataset = DataSet->new();
$pplAdapter->Fill($pplDataset, "People");

The SqlDataAdapter class has properties associated with it for selecting, inserting, updating, and deleting data from a data source. Here the SqlCommand instance is associated with the SelectCommand property of the SqlDataAdapter instead of being executed independently through one of its own execute methods.

The Fill method of the SqlDataAdapter is then used to execute the select command and fill the DataSet with information to be put in a table whose name is supplied as an argument. If the database connection was closed when the Fill method was executed, it will be opened. When finished, the Fill method leaves the connection in the same state as it was before Fill was called.

At this point the connection to the database could be closed. You now can work with the DataSet and its contained data independently of the connection to the database.

SqlDataAdapter is implemented with the SqlDataReader class, so you can expect better performance with the latter. The SqlDataReader might also be more memory efficient, depending on how your application is structured. If you do not need the features of the DataSet, there is no point incurring the overhead. If you are doing expensive processing, you can free up the database connection by using a DataSet. You may get better scalability by loading the data into the DataSet, freeing the associated database resources and doing the processing against the DataSet.

DataSet Collections

To access the tables that the data adapter stored inside the dataset, you may use the Tables collection. Inside a specific table, the data resides in rows and columns as in regular database tables. The Rows and Columns collections of the DataTable class provide the access to the rows and columns of the table respectively.

With the following fragment, we output the table contents that were placed into the dataset, selecting all the rows from the People table in the PerlNET database.

my $t = $pplDataset->{Tables}->get_Item("People");
if (!defined $t)
{
   die "Table People was not retrieved";
}

foreach my $c (in $t->{Columns})
{
   print $c->{ColumnName}, "	";
}
print "
";
foreach my $r (in $t->{Rows})
{
   for (my $i = 0; $i < $t->{Columns}->{Count}; $i++)
   {
      print $r->[$i], "	";
   }
   print "
";
}
print "
";

The Tables collection includes all the DataTable instances in the DataSet. In this particular case there is only one, so there is no need to iterate through that collection. The program iterates through all the columns in the table and sets them up as headers for the data that will be printed out. After the headers have been set up, all the rows in the table are iterated through.

Since the data from the People table resides in the DataSet object, we may add new rows to the Rows collection exactly in the same manner as we showed in the “Populating DataTable” subsection.

my $t = $pplDataset->{Tables}->get_Item("People");
# Obtain DataRow for "People" table
my $row = $t->NewRow();
# Set ID
$row->set_Item("ID", "5");
# Set Name
$row->set_Item("Name", "Alex");
# Add the row
$t->{Rows}->Add($row);

Updating DataSources

Changes to the DataSet are placed back based on the InsertCommand, UpdateCommand, and DeleteCommand properties of the SqlDataAdapter class. Each of these properties takes a SqlCommand instance that can be parameterized to relate the variables in the program to the parts of the related SQL statement. We will show how to add a new row to the People table working with the data set.

A SqlCommand instance is created to represent the parameterized SQL statement that will be used when the SqlAdapter.Update command is invoked to add a new row to the database. At that point, the actual values will be substituted for the parameters.

my $cmd = SqlCommand->new(q(insert into People(ID,
                         Name)values (@id, @name)), $conn);

We used the q( ) function to enclose the insert statement, since in double-quotes the parameter name following the @ character is interpreted as a Perl array name.

The parameters have to be associated with the appropriate columns in a DataRow. In the People table, columns were referenced by the column names: ID and Name. Notice how they are related to the SQL statement parameters @id and @name in the SqlParameter constructor. This last argument sets the Source property of the SqlParameter. The Source property sets the DataSet column to which the parameter corresponds. The Add method places the parameter in the Parameters collection associated with the SqlCommand instance.

my $paramID = SqlParameter->new(q(@id), enum("SqlDbType.Int"), 9, "ID");
my $paramName = SqlParameter->new(q(@name), enum("SqlDbType.Char"), 20, "Name");

$cmd->{Parameters}->Add($_) for $paramID, $paramName;

Finally, the SqlAdapter's InsertCommand property is set to the SqlCommand instance. Now this command will be used whenever the adapter has to insert a new row in the database.

$pplAdapter->{InsertCommand} = $cmd;

The same way, we set the UpdateCommand and DeleteCommand properties to be used whenever a row has to be updated or deleted. Whatever changes you have made to the rows in the DataSet will be propagated to the database when SqlDataAdapter.Update is executed.

$pplAdapter->Update($pplDataset, "People");