CodeKicks.com
Focus on Microsoft Technologies - Tutorials, Articles, Code Samples.

Thursday, January 25, 2007

What's New in ADO.NET 2.0 - New DataTable Features

Loading Data from a DataReader with the Load Method

Typically, and with previous versions of .Net, filling a DataTable involved the use of a DataAdapter or the loading of rows into it directly. With ADO.NET 2.0, you can use a DataReader instead. Here's an example:

Why would you want to use a DataReader to load data into a DataTable as opposed to a DataAdapter? I have no idea! I found that the Load/DataReader method consumes approximately 13% more running time than the Fill/DataAdapter method - making it (the Load/DataReader method) the slower method. So, neither method is faster than the other. I have not been able to find an MSDN article or Webcast explaining the benefits of using the Load/DataReader method over the Fill/DataAdapter method. I think they added the support to simply accompany the next new feature discussed below: the CreateDataReader method.

private DataTable GetDataTableLoadedFromDataReader()
{
// Create a new instance of the DataTable class:
DataTable tbl = new DataTable("tblUsers");
// Create a new connection object:
using (SqlConnection sqlConn = new SqlConnection( "Data Source=(local);
Integrated Security=SSPI; Initial Catalog=DotNetFun1;"))
{
// Create a new command object:
using (SqlCommand sqlCmd = new SqlCommand( "SELECT * FROM tblUsers", sqlConn)) {
// Open the connection (it's required by the DataReader): sqlConn.Open();
// Execute the command and return a DataReader object:
using (SqlDataReader reader = sqlCmd.ExecuteReader()) {
// Load the table with the data returned from the DataReader: tbl.Load(reader);
}
}
}
return tbl;
}

The first available result set is read from the DataReader and loaded into the DataTable. For more information on the Load method, visit the MSDN resource.

Getting a DataReader from a DataTable with the CreateDataReader Method

You now have the ability to obtain a DataReader-like object from a DataTable through which you can iterate: the DataTableReader class. Unlike a typical DataReader, the DataTableReader is disconnected from the database. You can, however, use it just like a typical DataReader. The DataTableReader retrieves the Current rows from the DataTable and rows marked as Deleted are ignored. Here's an example of how to use the CreateDataReader method of the DataTable class:

private DataTableReader GetDataTableReader()
{ // Create a DataTable object: DataTable tbl = new DataTable("tblUsers");
// Add some columns to it: tbl.Columns.Add("FirstName"); tbl.Columns.Add("LastName");
// Create 100 rows and add them to the DataTable object:
for (int i = 1; i <= 100; i++) {
DataRow rowNew = tbl.NewRow();
rowNew["FirstName"] = "First " + i.ToString();
rowNew["LastName"] = "Last " + i.ToString();
tbl.Rows.Add(rowNew); }
// Return a DataTableReader object based on the data in the DataTable object:
return tbl.CreateDataReader();
}
private void Start()
{
DataTableReader reader = this.GetDataTableReader();
while (reader.Read())
{
Console.WriteLine(reader["FirstName"].ToString());
}
}

For more information about the CreateDataReader method, visit the MSDN resource.

DataTable Class Inherits many Features of its Parent DataSet Class

The new DataTable class in .Net 2.0 now works much like the DataSet class. Many new methods have been added that mirror those within the DataSet class:
  • GetDataTableSchema Method: This method is the equivalent to DataSet's GetDataSetSchema method. It returns a XmlSchemaSet object representing the current object's schema (metadata).
  • GetObjectData Method: This method is equivalent to DataSet's GetObjectData method. It populates an object for serialization purposes.
  • Merge Method: This method is equivalent to DataSet's Merge method(s). It's responsible for merging the current DataTable object with another DataTable object which provides the ability to add rows easily.
  • ReadXml Method: This method is equivalent to DataSet's ReadXml method(s). It's responsible for creating and merging schema and data into the current DataTable object from an XML source such as a file or string.
  • ReadXmlSchema Method: This method is equivalent to DataSet's ReadXmlSchema method(s). It's responsible for merging schema into the current DataTable object from an XML source such as a file or string. Unlike the ReadXMl method, this method can only load schema and not data.
  • WriteXml Method: This method is equivalent to DataSet's WriteXml method. It's responsible for writing the XML representation of the DataTable object's data and schema to a file, stream or string.
  • WriteXmlSchema Method: This method is equivalent to DataSet's WriteXmlSchema method. It's responsible for writing the XML representation of the DataTable object's schema to a file, stream or string. Unlike the WriteXml method(s), this method can only write schema and not data.
  • IsInitialized Property: This property is equivalent to DataSet's IsInitialized property. It's responsible for letting you know whether nor not the DataTable is ready for use.
  • RemotingFormat Property: This property is equivalent to DataSet's RemotingFormat property. It's responsible for getting or setting the remoting serialization format which can be either binary or XML.
  • Initialized Event: This event is equivalent to DataSet's Initialized event. It's responsible for letting you know when the DataTable is ready for use.

Post a Comment