DataSets, Xml Schemas and Excel

by the3factory 3/19/2008 6:56:00 AM

Introduction

This article explores the power of the DataSet in a way that you might not be familiar with. Two important (and very helpful) aspects of the DataSet are uncovered here:

A DataSet can be used to write to Excel and read from Excel using an OleDbConnection very easily.
A DataSet, in conjunction with an agnostic Xml Schema (not a DataSet Xml Schema) can be used to shape xml data

Background

I remember the DAO (Data Access Objects) days and the ADO Recordset days. I also remember being skeptical when Microsoft introduced yet another library to manipulate data. That was when .NET initially came out. My skepticism waned away very quickly when I started to delve under the covers of the DataSet.

Going back a few years ago, I had designed/developed an Enterprise Management Reporting system for a global company that required MSMQ driven server-side automation of Office. That was an unadvisable task, according to Microsoft - but it was achieved nonetheless (with macros and the like).

Then, I discovered the beauty of writing to and reading from Excel using an OleDbConnection. This meant I could limit the time that I was running macros to just the manipulation of data (I didn't need Excel to write to Excel anymore). That greatly improved the efficiency and reduced the risk of problems because the Excel instance was used for a much shorter time.

I also remember a time when I had to use an XmlWriter or the FOR XML clause in SQL Server to build XML Data. I also remember using the Relations collection of ADO.NET to build formatted XML. But those were my younger days, I rejoiced when I found that I could read XML Schemas into a DataSet and out came formatted XML!

Now you might say that .NET already provides you the ability with Strongly-Typed DataSets. But the problem is that you don't necessarily want .NET DataSet Xml Schemas. I have been on a few projects where the tools used to develop Xml Schema were not VS2005. Admit it, most times you need to use agnostic Xml Schemas. It is, after all, best practice to do so - isolate the presentation of the data from the retrieval of data.

By relaxing the constraints we can use the Xml Schema to build the data, then use the Xml Schema to validate the data. Kill two birds with one stone - so to speak. Enough of the sales pitch - let's take a look at what I am talking about.

Using the code

There are two main modules in this project.

Name	Description
`DataSetMethods`	This class has two methods: `gatherMetaData` `setupDataSet` `gatherMetaDat` is used to gather Table/Column information and Relations within the DataSet `setupDataSet` takes an agnostic Xml Schema and makes it DataSet-ready.
`ExcelFunctions`	This class contains two overloads to `ReadFromExcel` to a DataSet and two overloads to `WriteToExcel` from a DataSet.

DataSetMethods code:

Collapse

        /// 
/// This method gathers Table\Column information from a DataSet.
/// It also gathers Relation information from a DataSet.
/// 
public static DataSet gatherMetaData(DataSet ds)
{
XmlDocument xd = new XmlDocument();
xd.Load(@"Metadata Xsd\MetaData.xsd");
DataSet metaData = setupDataSet(xd, new DataSet());
for (int i = 0; i < ds.Tables.Count; i++)
{
for (int j = 0; j < ds.Tables[i].Columns.Count; j++)
{
DataRow dr = metaData.Tables["Table"].NewRow();
dr["Name"] = ds.Tables[i].TableName;
dr["Column"] = ds.Tables[i].Columns[j].ColumnName;
metaData.Tables["Table"].Rows.Add(dr);
}
}
for (int i = 0; i < ds.Relations.Count; i++)
{
for (int j = 0; j < ds.Relations[i].ParentColumns.Length; j++)
{
for (int k = 0; k < ds.Relations[i].ChildColumns.Length; k++)
{
DataRow dr = metaData.Tables["Relations"].NewRow();
dr["ParentTable"] = ds.Relations[i].ParentTable;
dr["ParentColumn"] = ds.Relations[i].ParentColumns[j].ColumnName;
dr["ChildTable"] = ds.Relations[i].ChildTable;
dr["ChildColumn"] = ds.Relations[i].ChildColumns[j].ColumnName;
metaData.Tables["Relations"].Rows.Add(dr);
}
}
}
return metaData;
}
/// 
/// Set up a dataset with an Xml Schema that is DataSet friendly
/// 
public static DataSet setupDataSet(XmlDocument xmlSchema, DataSet ds)
{
XmlAttribute xa = xmlSchema.CreateAttribute("xmlns:msdata", "http://www.w3.org/2000/xmlns/");
xa.Value = "urn:schemas-microsoft-com:xml-msdata";
XmlNode schemaNode = xmlSchema.SelectSingleNode("descendant::*[local-name() = 'schema']");
schemaNode.Attributes.Append(xa);
xa = xmlSchema.CreateAttribute("msdata:IsDataSet", "urn:schemas-microsoft-com:xml-msdata");
schemaNode.SelectSingleNode("*[local-name() = 'element']").Attributes.Append(xa);
StringReader sr = new StringReader(xmlSchema.OuterXml);
XmlReader xr = XmlReader.Create(sr);
ds.ReadXmlSchema(xr);
ds.EnforceConstraints = false;
return ds;
}

ExcelFunctions code:

Collapse

        #region Constants
/// 
/// string to use for setting up connection string to Excel
/// 
private const string _excelConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";User ID=" +
"Admin;Password=;Extended Properties=\"Excel 8.0;HDR=YES\";";
/// 
/// select statement to read from Excel
/// 
private const string _excelSelect = "select * from [{0}$]";
/// 
/// tablename column for DataRow
/// 
private const string _tableName = "TABLE_NAME";
/// 
/// CREATE TABLE Template
/// 
private const string _tableCreate = "CREATE TABLE [{0}] (";
/// 
/// COLUMN Template for CREATE TABLE
/// 
private const string _tableColumn = "[{0}] {1}{2}";
#endregion
#region Private Methods
/// 
/// Very simple function to specify Excel DataType mapping.
/// 
private static string getColumnType(DataColumn dc)
{
string columnType = "TEXT";
switch (dc.DataType.ToString())
{
case "System.Int64" :
case "System.Double":
case "System.Int32" :
columnType = "NUMERIC";
break;
default:
columnType = "TEXT";
break;
}
return columnType;
}
#endregion
#region Public Methods
#region WriteToExcel(DataSet ds)
/// 
/// Write data from a dataset to a new filename.
/// 
public static void WriteToExcel(DataSet ds)
{
WriteToExcel(ds,ds.DataSetName + ".xls",false);
}
#endregion
#region WriteToExcel(DataSet ds, String fileName, bool append)
/// 
/// Write data from a dataset to a filename.  
/// This method can either create a new file or append to
/// an existing Excel file. If append is specified and file does
/// not exist, the file will be created.
/// 
public static void WriteToExcel(DataSet ds, String fileName, bool append)
{
string excelConnectionString = string.Format(_excelConnectionString, fileName);
OleDbConnection excelFile = null;
OleDbCommand excelCmd = null;
OleDbDataAdapter excelDataAdapter = null;
OleDbCommandBuilder excelCommandBuilder = null;
StringBuilder sb = null;
try
{
if (File.Exists(fileName) && !append) File.Delete(fileName);
excelFile = new OleDbConnection(excelConnectionString);
excelFile.Open();
// write each DataTable to Excel Spreadsheet
foreach (DataTable dt in ds.Tables)
{
// file does not exist or we don't want to append
if (!File.Exists(fileName) || !append)
{
// build the CREATE TABLE statement
sb = new StringBuilder();
sb.AppendFormat(_tableCreate, dt.TableName);
foreach (DataColumn dc in ds.Tables[dt.TableName].Columns)
{
sb.AppendFormat(_tableColumn, dc.ColumnName,
getColumnType(dc)
, (dc.Ordinal == dt.Columns.Count - 1 ?
")" : ","));
}
excelCmd = new OleDbCommand(sb.ToString(), excelFile);
excelCmd.ExecuteNonQuery();
}
// use the command builder to generate insert command for DataSet Update to work
excelDataAdapter = new OleDbDataAdapter(string.Format(_excelSelect,dt.TableName), excelFile);
excelCommandBuilder = new OleDbCommandBuilder(excelDataAdapter);
excelCommandBuilder.QuotePrefix = "[";
excelCommandBuilder.QuoteSuffix = "]";
excelDataAdapter.InsertCommand = excelCommandBuilder.GetInsertCommand();
excelDataAdapter.Update(ds, dt.TableName);
}
}
finally
{
sb = null;
excelDataAdapter = null;
excelCommandBuilder = null;
excelCmd = null;
excelFile.Close();
excelFile.Dispose();
excelFile = null;
}
}
#endregion
#region ReadFromExcel(string fileName)
/// 
/// Read from an Excel file into a new DataSet
/// 
public static DataSet ReadFromExcel(string fileName)
{
return ReadFromExcel(fileName, new DataSet());
}
#endregion
#region ReadFromExcel(string fileName, DataSet ds)
/// 
/// Read from an Excel file into an existing DataSet
/// 
public static DataSet ReadFromExcel(string fileName, DataSet ds)
{
string excelConnectionString = string.Format(_excelConnectionString, fileName);
OleDbConnection excelFile;
DataTable schemaTable;
OleDbDataAdapter excelDataAdapter;
try
{
excelFile = new OleDbConnection(excelConnectionString);
excelFile.Open();
schemaTable = excelFile.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
// Read each DataTable (i.e. Excel Spreadsheet) into the DataSet
foreach (DataRow dr in schemaTable.Rows)
{
excelDataAdapter = new OleDbDataAdapter(dr[_tableName].ToString(), excelFile);
excelDataAdapter.SelectCommand.CommandType = CommandType.TableDirect;
excelDataAdapter.AcceptChangesDuringFill = false;
if (dr[_tableName].ToString().EndsWith("$"))
excelDataAdapter.Fill(ds, dr[_tableName].ToString().Replace("$", string.Empty));
}
excelFile.Close();
}
finally
{
excelDataAdapter = null;
schemaTable = null;
excelFile = null;
}
return ds;
}
#endregion
#endregion