19.4. DataSets
DataSets provide a disconnected in-memory representation of a structure that closely mimics the underlying RDBMS tables. A DataSet object can be used to load data and save data to an RDBMS, and it's even powerful enough to programmatically generate an entire database complete with table, view, and constraint definitions. DataSet is conceptually similar to JDBC's RowSet and CachedRowSet classes.
19.4.1. Loading Data into a DataSet
Listing 19.8 shows how to load data into a DataSet object.
Listing 19.8. Loading Data into a DataSet (C#)
using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
class Test {
public static void Main(string[] args) {
ReadDataSet();
}
private static void ReadDataSet() {
SqlConnection conn = GetConnection();
DataSet ds = new DataSet("Test");
try {
Fill(conn, ds, "Products", "SELECT PRODUCTID,
PRODUCTNAME FROM PRODUCTS ");
Fill(conn, ds, "Customers", "SELECT CUSTOMERID,
CONTACTNAME FROM CUSTOMERS
WHERE UPPER(COUNTRY) ='USA'" );
} finally {
conn.Close();
}
Browse(ds);
}
private static void Fill(SqlConnection conn, DataSet ds,
String tableName, String sql) {
SqlDataAdapter adapter = new SqlDataAdapter ();
adapter.TableMappings.Add("Table", tableName);
SqlCommand command = new SqlCommand(sql, conn);
adapter.SelectCommand = command;
adapter.Fill(ds);
}
private static void Browse(DataSet ds) {
foreach (DataTable table in ds.Tables) {
BrowseTable(table);
}
}
private static void BrowseTable(DataTable table) {
Console.WriteLine("Table name "+table.TableName+"
");
Console.Write("Column Name,Column Type,Ordinal
");
foreach (DataColumn column in table.Columns) {
Console.Write(column.ColumnName);
Console.Write(",");
Console.Write(column.DataType.FullName);
Console.Write(",");
Console.Write(column.Ordinal);
Console.Write("
");
}
}
private static SqlConnection GetConnection() {
SqlConnection conn = new SqlConnection ();
conn.ConnectionString = "Initial Catalog=Northwind;Data
Source=localhost;Integrated Security=SSPI;";
conn.Open();
return conn;
}
}
|
The output of Listing 19.8 is as follows:
Table name Products Column Name,Column Type,Ordinal PRODUCTID,System.Int32,0 PRODUCTNAME,System.String,1 Table name Customers Column Name,Column Type,Ordinal CUSTOMERID,System.String,0 CONTACTNAME,System.String,1
The program starts by creating a DataSet:
DataSet ds = new DataSet("Test");
The program then signals to fill the DataSet with the results obtained by running two SELECT queries:
Fill(conn, ds, "Products", "SELECT PRODUCTID, PRODUCTNAME FROM
PRODUCTS ");
Fill(conn, ds, "Customers", "SELECT CUSTOMERID, CONTACTNAME
FROM CUSTOMERS WHERE UPPER(COUNTRY) ='USA'" );
To populate the DataSet we need to use the appropriate adapter object, SqlAdapter:
SqlDataAdapter adapter = new SqlDataAdapter ();
adapter.TableMappings.Add("Table", tableName);
SqlCommand command = new SqlCommand(sql, conn);
adapter.SelectCommand = command;
adapter.Fill(ds);
The last line fills the dataset with the contents of the query:
adapter.Fill(ds);
Next, we browse the contents of the DataSet. We browse through the tables and the columns. Note that the returned columns contain only the columns that were returned by the queries and not all the columns of that table.
In our example we close the connection in the finally block just before browsing the DataSet. Because a DataSet is a disconnected in-memory representation of whatever data we obtained, the connection does not have to be open for us to browse through the DataSet.
19.4.2. Saving Data to the RDBMS Using a DataSet
Listing 19.9 shows how to insert, update, and delete rows using a DataSet.
Listing 19.9. Saving Data Using a DataSet (C#)
using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
class Test {
public static void Main(string[] args) {
SqlConnection conn = GetConnection();
try {
SqlDataAdapter adapter = new SqlDataAdapter("
SELECT * FROM CUSTOMERS", conn);
DataSet ds = new DataSet();
//Set the MissingSchemaAction property to AddWithKey
//because
//Fill will not cause primary
//key & unique key information to be retrieved unless
//AddWithKey is specified.
adapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
adapter.Fill(ds, "Customers");
//Insert a row
DataRow row = ds.Tables["Customers"].NewRow();
row["CustomerId"] = "2ldID";
row["ContactName"] = "2ld name";
row["CompanyName"] = "2ld company";
ds.Tables["Customers"].Rows.Add(row);
//Find a row with primary key ALFKI and
//update its ContactName
row = ds.Tables["Customers"].Rows.Find("ALFKI");
row["ContactName"] = "Updated name";
SqlCommandBuilder builder = new
SqlCommandBuilder(adapter);
adapter.Update(ds, "Customers");
//Delete a row from the order detail
//The row has OrderId of 1 and Product Id = 06897
adapter = new SqlDataAdapter("SELECT * FROM
EmployeeTerritories", conn);
adapter.MissingSchemaAction = MissingSchemaAction.Add
WithKey;
adapter.Fill(ds, "EmployeeTerritories");
row = ds.Tables["EmployeeTerritories"].Rows.Find(new
object[] {1, 06897} );
row.Delete();
adapter.Update(ds, "EmployeeTerritories");
} finally {
conn.Close();
}
}
private static SqlConnection GetConnection() {
SqlConnection conn = new SqlConnection ();
conn.ConnectionString = "Initial Catalog=Northwind;Data
Source=localhost;Integrated Security=SSPI;";
conn.Open();
return conn;
}
}
|
Listing 19.9 updates and inserts rows in the Customers table and deletes a row from the EmployeeTerritories table. The program starts by creating a SqlDataAdapter and a DataSet. The DataSet is then filled with the Customers data:
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM
CUSTOMERS", conn);
DataSet ds = new DataSet();
adapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
adapter.Fill(ds, "Customers");
Note that we must specify the following because without that, the Fill method will not retrieve primary key and unique key information about the table:
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
Inserting a row in the dataset is trivial:
DataRow row = ds.Tables["Customers"].NewRow(); row["CustomerId"] = "2ldID"; row["ContactName"] = "2ld name"; row["CompanyName"] = "2ld company"; ds.Tables["Customers"].Rows.Add(row);
Here, we create a row, fill out its relevant information, and add it to the row collection of the Customers table. To update a row of the table we use the following:
row = ds.Tables["Customers"].Rows.Find("ALFKI");
row["ContactName"] = "Updated name";
The first line finds the row whose primary key is ALFKI. We modify the ContactName column of the row by changing its value to "UPdated name".
The DataSet is now ready to be persisted to the underlying tables. The following two lines take the DataSet changes and persist them to the database:
SqlCommandBuilder builder = new SqlCommandBuilder(adapter); adapter.Update(ds, "Customers");
SqlCommandBuilder is a utility for generating trivial SQL statements automatically from a DataSet.
In JDBC, java.sql.DatabaseMetaData and java.sql.ResultSetMetaData give a read-only view of the details of the database structure and the table structure, respectively. The DataSet object has methods to browse a database or table structure and also to create new databases complete with tables, views, constraints, and relationships between the tables. We leave it to you to explore these methods of the DataSet object that help define the database metadata.
The .NET DataSet is a flexible class that can be used for using Data Modification Language (DML) and Data Definition Language (DDL) on the RDBMS. In a large code base, it becomes tedious to repeat code for doing DML on objects in different places. What is needed is an abstraction that is more intuitive to the object-oriented programmer. Listings 19.10 and 19.11 show how to encapsulate DataSet manipulation in an abstract object so that the logic can be reused for any custom object. The end user simply builds the custom object, and loading and saving of the object become automatic.
First, Listing 19.10 shows the abstract SqlPersistentObject.
Listing 19.10. An Abstract Class for Encapsulating DataSet Manipulation (C#)
namespace DietPersistentFrameWork {
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
public abstract class SqlPersistentObject {
public enum STATE {NEW, LOAD, DELETE, DELETED};
STATE state;
SqlDataAdapter adapter;
DataSet ds;
object pk;
Hashtable columns = new Hashtable();
public SqlPersistentObject() {
state = STATE.NEW;
}
public void save(SqlConnection conn) {
if (state == STATE.NEW) {
Insert(conn);
} else if (state == STATE.LOAD) {
Update(conn);
} else if (state == STATE.DELETE) {
Delete(conn);
}
}
public object GetField(string columnName) {
if (state != STATE.NEW) {
DataRow row =
ds.Tables[GetTableName()].Rows.Find((string)pk);
return row[columnName];
}else {
return columns[columnName];
}
}
public void markDelete() {
if (state == STATE.LOAD) {
state = STATE.DELETE;
}
}
//Sets the value of the field of the object
public void SetField(string columnName, object val) {
if (state != STATE.NEW ) {
DataRow row =
ds.Tables[GetTableName()].Rows.Find((string)pk);
row[columnName] = val;
} else {
columns.Remove(columnName);
columns.Add(columnName, val);
}
}
//Delete the object from the DB
protected virtual void Delete(SqlConnection conn) {
object pk = GetField(GetPrimaryKey());
try {
string query = "DELETE * FROM "+GetTableName() +"
WHERE
"+GetPrimaryKey()+" = '"+pk+"'";
adapter.DeleteCommand = new SqlCommand(query, conn);
DataRow row =
ds.Tables[GetTableName()].Rows.Find((string)pk);
row.Delete();
adapter.Update(ds, GetTableName());
adapter = null;
ds = null;
state = STATE.DELETED;
} catch (Exception e) {
Console.WriteLine(e.StackTrace);
}
}
public static void Load(SqlConnection conn,
SqlPersistentObject obj, string pk) {
try {
string query = "SELECT * FROM "+obj.GetTableName() +"
WHERE "+obj.GetPrimaryKey()+" = '"+pk+"'";
obj.adapter = new SqlDataAdapter (query, conn);
obj.ds = new DataSet(obj.GetTableName());
obj.adapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
obj.adapter.Fill(obj.ds, obj.GetTableName());
obj.state = STATE.LOAD;
obj.pk = pk;
} catch (Exception e) {
Console.WriteLine(e.StackTrace);
}
}
//Update the object
protected virtual void Update(SqlConnection conn) {
try {
if (ds != null && adapter != null) {
SqlCommandBuilder builder = new
SqlCommandBuilder(adapter);
adapter.Update(ds, GetTableName());
}
} catch (Exception e) {
Console.WriteLine(e.StackTrace);
}
}
protected void Insert(SqlConnection conn) {
try {
string query = "SELECT * FROM "+GetTableName();
adapter = new SqlDataAdapter (query, conn);
ds = new DataSet();
adapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
adapter.Fill(ds, GetTableName());
DataRow row = ds.Tables[GetTableName()].NewRow();
foreach (object key in columns.Keys) {
row[key.ToString()] = columns[key];
}
ds.Tables[GetTableName()].Rows.Add(row);
SqlCommandBuilder builder = new
SqlCommandBuilder(adapter);
adapter.Update(ds, GetTableName());
state = STATE.LOAD;
} catch (Exception e) {
ds = null;
adapter = null;
Console.WriteLine(e.ToString());
}
}
//Abstract methods
public abstract string GetTableName();
public abstract string GetPrimaryKey();
}
}
|
The key assumption behind the PersistentObject class is that it maps to a single table in the RDBMS. When objects are mapped to an RDBMS, it is customary to assume that a single row of the table maps to a single instance of the object and that the primary key value of that row becomes the object ID of that persistent object. The object ID serves to identify the object (say, in a cache of objects) just as the primary key helps identify a row in a table. For simplicity we assume that the primary key or object ID of the object is a simple single-column entity.
The PersistentObject class leverages the information stored in the DataSet and the SqlDataAdapter objects and gives an object-oriented API for manipulating them.
We start by defining the states of the object:
public enum STATE {NEW, LOAD, DELETE, DELETED};
When the object is first constructed, its state is NEW. The object is in the LOAD state when it is loaded from the database. The object is in the DELETE state when it is marked for deletion, and it is in the DELETED state when it is deleted. The save method of the object drives the main flow of the object:
public void save(SqlConnection conn) {
if (state == STATE.NEW) {
Insert(conn);
} else if (state == STATE.LOAD) {
Update(conn);
} else if (state == STATE.DELETE) {
Delete(conn);
}
}
This logic is fairly intuitive. Any DML executed on an object in the NEW state is interpreted as an insert; on an object in the LOAD state, it is interpreted as an update. The Insert and Update methods set the appropriate state of the object upon their successful completion. The Update method merely updates the changes made to the DataSet via the SetField method:
protected virtual void Update(SqlConnection conn) {
try {
if (ds != null && adapter != null) {
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.Update(ds, GetTableName());
}
}catch (Exception e) {
Console.WriteLine(e.StackTrace);
}
}
The SetField method is interesting because if the object is in the NEW state, SetField merely adds the columnNames and values to the Hashtable. Note that in C#, for a Hashtable, having the key and value will not overwrite the old value (if any exists) for that key. To simulate this Java-like behavior we remove the key and then add it back:
columns.Remove(columnName); columns.Add(columnName, val);
If the object is not in the NEW state, the value is set to that of an appropriate row/column combination of the DataSet. Remember that the row this object corresponds to is obtained by the object ID or primary key.
DataRow row = ds.Tables[GetTableName()].Rows.Find((string)pk); row[columnName] = val;
When the object is loaded using the Load static method, it flags the object's state to LOAD. Once loaded, the object caches the DataSet, the SqlDataAdapter, and the primary key value. It is fairly expensive to retrieve these every time, and therefore it makes sense to cache them the first time the object is loaded.
obj.adapter = new SqlDataAdapter (query, conn); obj.ds = new DataSet(obj.GetTableName()); obj.pk = pk;
For the insertion of the object, you will need a Hashtable of column names to column values. This is then used to create the DataSet and SqlDataAdapter in the Insert method. If the insert is successful, Hashtable can reuse the DataSet and SqlDataAdapter for future updates.
string query = "SELECT * FROM "+GetTableName();
adapter = new SqlDataAdapter (query, conn);
ds = new DataSet();
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.Fill(ds, GetTableName());
DataRow row = ds.Tables[GetTableName()].NewRow();
foreach (object key in columns.Keys) {
row[key.ToString()] = columns[key];
}
ds.Tables[GetTableName()].Rows.Add(row);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.Update(ds, GetTableName());
state = STATE.LOAD;
Note how the Insert method sets the state of the object to LOAD.
Listing 19.11 shows a subclass that maps to the Customers table and shows how to use the SqlPersistentObject to load and save objects.
Listing 19.11. Subclass of SqlPersistentObject from Listing 19.10 (C#)
namespace DietPersistentFrameWork {
using System;
using System.Data;
using System.Data.SqlClient;
public class Customer : SqlPersistentObject {
public override string GetTableName() { return "CUSTOMERS"; }
public override string GetPrimaryKey(){ return
"CUSTOMERID"; }
public static void Main(string[] args) {
SqlConnection conn = GetConnection();
try {
//LOAD CUSTOMER
Customer cust = new Customer();
SqlPersistentObject.Load(conn, cust, "ANATR");
Console.WriteLine(cust.GetField("CustomerId"));
//UPDATE CUSTOMER
SqlPersistentObject.Load(conn, cust, "ANATR");
cust.SetField("ContactTitle", "CTO");
cust.save(conn);
//INSERT NEW CUSTOMER
cust = new Customer();
cust.SetField("CustomerID", "C3P0");
cust.SetField("CompanyName", "ACME");
cust.SetField("ContactTitle", "Super Owner");
cust.save(GetConnection());
} finally {
conn.Close();
}
}
public static SqlConnection GetConnection() {
SqlConnection conn = new SqlConnection ();
conn.ConnectionString = "Initial Catalog=Northwind;Data
Source=localhost;Integrated Security=SSPI;";
conn.Open();
return conn;
}
}
}
|
Because most of the complexity of persisting and loading the object is taken care of by the superclass, the subclass specification becomes trivial. In fact the Customer object has only two methods, which return the table name and the name of the primary key column:
public void SetField(string columnName, object val) {
if (state != STATE.NEW ) {
DataRow row = ds.Tables[GetTableName()].Rows.Find((
string)pk);
row[columnName] = val;
} else {
columns.Remove(columnName);
columns.Add(columnName, val);
}
}
If the object is not in the NEW state, the value of the appropriate row/column combination of the DataSet is returned.