|
|
|
|
|
 |
|
Navigation |
|
>> Home |
Persisting DataViews as DataTables
Written by: Christoph Wille Accessing data via ADO.NET is very simple - data is stored as a DataSet and data can then be read from the DataTables. And if we need another sorting or filter, why go to the server? This can be done client side using the DataView class. The only drawback under .NET 1.x is that we can not simply create a new DataTable from the new View (DataView with .Sort or .RowFilter Criteria). Now imagine the following situation: you select all clients from the famous Northwind database. You use a DataView to only display those customers who have their company residence in Buenos Aires. No problem with that - except when you want to pass on those clients (and only those) as a DataTable. There is no method on DataView for performing this task. Therefore: write the necessary methods yourself for performing this task. Now the question is how to best do this? A new class? Static or instance methods? What is the method supposed to be able to do? As I work with .NET 2.0 as a 'sideline' which offers this functionality - due to loud enough cries by enough customers - there is nothing more obvious than porting this functionality back to have as little troubles as possible later when switching over? This is of course the best solution and we obviously encapsulate the normal DataView class in the new one, just adding the necessary methods:
public class DataViewEx : DataView
{
public DataTable ToTable();
public DataTable ToTable(bool isDistinct, string[] columnNames);
}
The first implementation of ToTable calls the second with false, null parameters and does not contain any functionality itself. We now only have to look at the second one to determine what it is to do. The first parameter serves to exclude duplicate rows in the DataTable which comes in handy when there will be no index columns in the target DataTable. The second paramter is also useful: with this we can specify that only a subset of the original columns is to be taken over by the target DataTable. Let us start at the beginning - the class DataViewEx and its constructors. Conveniently the constructor signatures are identical to those of the DataView class and redirect to the implementations of those:
using System;
using System.Data;
using System.Text;
using System.Collections;
namespace DotNetGerman
{
public class DataViewEx : DataView
{
#region Mapped Constructors
public DataViewEx() : base()
{
}
public DataViewEx(DataTable table) : base(table)
{
}
public DataViewEx(DataTable table, string RowFilter, string Sort,
DataViewRowState RowState) : base(table, RowFilter, Sort, RowState)
{
}
With this we have met all requirements to be able to use our new class instead of the original DataView class without noticeable differences. Before we now delve into the implementation of the 'real' ToTable method I want to show you a ToTable method trimmed down to the absolute minimum so that you can easily see the classes and methods used:
public DataTable ToTable()
{
// short circuiting out here
int nRowCount = this.Count;
if (0 == nRowCount) return null;
// #1: clone the schema
DataTable tableNew = Table.Clone();
// #2: get the column count, we need it repeatedly
int nColumnCount = tableNew.Columns.Count;
// #3: copy the values to the new table
for (int iRow = 0; iRow < nRowCount; iRow++)
{
DataRow rowNew = tableNew.NewRow();
for (int iColumn=0; iColumn < nColumnCount; iColumn++)
{
rowNew[iColumn] = this[iRow][iColumn];
}
tableNew.Rows.Add(rowNew);
}
return tableNew;
}
When copying all columns, implementation is really simple: under #1 a schema clone of the DataTable class underlying the DataView is created. This clone is empty except for the column definitions, it does not contain any data. These are copied using a loop over the rows and columns under #3 (restricted by the .Sort and .RowFilter criteria). And with this we already are done and can return the DataTable containing only the desired filtered data. Now we will modify this method so that it will meet our requirements for DISTINCT row and column subsets. First the ToTable() method without parameters:
public DataTable ToTable()
{
return ToTable(false, null);
}
As stated, this only refers to our parametrized ToTable method. This starts off with some 'information gathering':
public DataTable ToTable(bool isDistinct, string[] columnNames)
{
// short circuiting out here
int nRowCount = this.Count;
if (0 == nRowCount) return null;
// get the column count, we need it repeatedly
int nColumnCount = Table.Columns.Count;
int nTargetColumnCount = nColumnCount;
// if second param == null, we copy the entire table
if (null != columnNames) nTargetColumnCount = columnNames.Length;
string[] tableColumnNames = new string[nColumnCount];
for (int iColumn = 0; iColumn < nColumnCount; iColumn++)
tableColumnNames[iColumn] = Table.Columns[iColumn].ColumnName;
What occurs in the code again and again is controlling whether we will copy all or only certain columns. To this end the array is filled with column names, we need this later on to access columns that have not been copied. And on we go:
bool[] keepColumn = new bool[nColumnCount];
int[] tableColumnIndexes = new int[nTargetColumnCount];
int[] newtableColumnIndexes = new int[nTargetColumnCount];
// check to see if the selected columns actually exist & map indexes
if (null != columnNames)
{
for (int i=0; i < columnNames.Length; i++)
{
if (Table.Columns.Contains(columnNames[i]))
{
int colIndex = Table.Columns.IndexOf(columnNames[i]);
tableColumnIndexes[i] = colIndex;
keepColumn[colIndex] = true;
}
else
{
throw new ArgumentException("Column does not exist in base table");
}
}
}
else
{
for (int i=0; i < nColumnCount; i++)
{
tableColumnIndexes[i] = i;
newtableColumnIndexes[i] = i;
keepColumn[i] = true;
}
}
Here we map the columns to be copied onto the index and obviously we check whether the programmer did not unintentionally choose a nonexistent column. Additionally we track which columns are not to be copied. The else branch handles the case of copying all columns. With this we have all the info to create the DataTable with the corresponding columns:
// clone the schema and remove unnecessary columns
DataTable tableNew = Table.Clone();
// now we can build the final table... all we need to do is map the
// string[] to the column indexes
// in the new table that was now created
if (null != columnNames)
{
// remove columns we no longer need
for (int k = 0; k < nColumnCount; k++)
{
if (keepColumn[k] == false)
tableNew.Columns.Remove(tableColumnNames[k]);
}
// map column names to column indexes
for (int i=0; i < columnNames.Length; i++)
{
int colIndex = tableNew.Columns.IndexOf(columnNames[i]);
newtableColumnIndexes[i] = colIndex;
}
}
Admittedly, I started out from the 'wrong end': first I cloned the DataTable and then removed everything the programmer did not want in the target Table. What I then have left to do is to get the mapping of the indices of the remaining columns to make the copying work:
// both variables used for determining duplicate rows
StringBuilder stb = new StringBuilder();
Hashtable ht = new Hashtable();
// copy the values to the new table
for (int iRow = 0; iRow < nRowCount; iRow++)
{
DataRow rowNew = tableNew.NewRow();
if (isDistinct)
stb.Remove(0, stb.Length);
for (int iColumn=0; iColumn < tableColumnIndexes.Length; iColumn++)
{
object currentValue = this[iRow][tableColumnIndexes[iColumn]];
if (isDistinct && (null != currentValue))
stb.Append(currentValue.ToString());
rowNew[newtableColumnIndexes[iColumn]] = currentValue;
}
// do the DISTINCT checks before inserting row
if (isDistinct)
{
string strRowKey = stb.ToString();
if (!ht.ContainsKey(strRowKey))
{
ht.Add(strRowKey, null);
tableNew.Rows.Add(rowNew);
}
}
else
{
tableNew.Rows.Add(rowNew);
}
}
// return the new table
return tableNew;
}
}
The reason for this section to have become so bloated lies in the treatment of the DISTINCT rows. The easiest way to compare rows is to join the column values, calculate a hash and comparing the hash with the rows already inserted. To make this piece of work easier for me, I use StringBuilder and Hashtable. Apart from this, the code is almost identical to the simple ToTable and we are done. To finish off, I want to demonstrate how the class can be used in applications (also included in today's download):
SqlConnection conn = new SqlConnection("data source=(local)\\NetSDK;" +
"initial catalog=Northwind; integrated security=SSPI");
SqlDataAdapter da = new SqlDataAdapter("select * from Customers", conn);
DataSet ds = new DataSet("Demo");
da.Fill(ds, "Customers");
DataViewEx demoFilter = new DataViewEx(ds.Tables[0]);
demoFilter.RowFilter = "City='Buenos Aires'";
DataTable table = demoFilter.ToTable(true, new string[] {"ContactTitle", "CompanyName", "ContactName"});
// DataTable table = demoFilter.ToTable(true, new string[] {"ContactTitle"});
// DataTable table = demoFilter.ToTable();
dg.DataSource = table;
Only one class has changed and we obtain the ToTable overloads - other than that it still is the DataView we know. ConclusionWith a little programming effort, even the .NET framework classes can be augmented with additional functionality. Especially this conversion of a DataView into a DataTable is a frequently recurring problem which now hopefully has been solved once and for all. Downloading the CodeClick here, to start the download.
©2000-2004 AspHeute.com |