About StringTable
StringTable is a useful data-structure for creating and processing tabular data, similar to System.Data.DataTable class. It is a simple and handy class for many purposes like creating and processing tabular data, reading database (like SQL, Oracle, etc.) tables from System.Data.IDataReader or System.Data.Common.DbDataReader, rendering data-grids in ASP.NET and Silverlight pages, creating comma-separated-values (CSV) files, serializing and deserializing tabular data in JSON, etc.
StringRow
First we create a simple dictionary that represents a single record in the table. Each entry in this dictionary will be a field value of the corresponding record. The Key property represents the column-name and the Value property represents the value of the cell (field of the corresponding record in the table).
The class is created as follows:
public class StringRow : Dictionary<string, string>
{
}
StringTable
The StringTable class is created with the following declaration. The Key property represents the zero-based index of the record and the Value property indicates the record values. Each entry (record) in the table is a pair of integer and a StringRow.
public class StringTable : Dictionary<int, StringRow>
{
#region Properties
public List<string> ColumnNames { get; set; }
#endregion
}
The following image shows the methods and members in the StringTable class.
Creating StringTable and processing tabular data
The StringTable class provides two constructors, a default constructor and a constructor that initializes the column names. For adding columns and rows, it provides two methods AddColumn() and AddRow(), respectively. The columns can be added using constructor or AddColumn() method.
All columns must be added before adding any row (record). Similarly, each row must contain values for all columns. The following example creates a StringTable with four columns, Name, Age, Gender and Date-of-Birth.
//creating table
StringTable personTable = new StringTable(new string[] { "Name",
"Age",
"Gender" });
personTable.AddColumn("DoB"); //add another column
//adding rows
personTable.AddRow(new string[] { "person1",
32.ToString(),
"M",
new DateTime(1978, 12, 27).ToString() });
personTable.AddRow(new string[] { "person2",
28.ToString(),
"F",
new DateTime(1981, 5, 15).ToString() });
personTable.AddRow(new string[] { "person3",
22.ToString(),
"M",
new DateTime(1987, 7, 25).ToString() });
personTable.AddRow(new string[] { "person4",
27.ToString(),
"M",
new DateTime(1982, 12, 12).ToString() });
//get record-count
Console.WriteLine("There are " + personTable.Count + " persons in this table");
//changing a column value
personTable[2]["Age"] = 29.ToString(); //change age of 3rd record
//changing entire-row values
personTable.ResetRowValues(2, new string[] { "person3_new",
27.ToString(),
"M",
new DateTime(1982, 12, 12).ToString() });
Please note, all values must be specified in their string representation, and the values has to be converted back to original data-type for any manipulation.
Manipulating Values
The values stored in a StringTable can be accessed directly by specifying row-index and column-name as in the following examples.
//get and use column values
string person2Name = personTable[1]["Name"];
int person3Age = Int32.Parse(personTable[2]["Age"]);
DateTime person1DateOfBirth = DateTime.Parse(personTable[0]["DoB"]);
A set of records can be processed using typical for/while looping statements or using LINQ queries. For example, the following LINQ statement finds average age of all persons. The examples for looping statements are available in the below sections.
//find average age using LINQ
double averageAge = (from rowIndex in personTable.Keys
select Int32.Parse(personTable[rowIndex]["Age"])).Average();
Reading Database Records as a StringTable
The following helper method converts an IDataReader into a StringTable. Since IDataReader is the base interface, this helper method will be useful for fetching data from any database (like SQL Server, Oracle, MySQL, etc).
public static StringTable FromIDataReader(IDataReader reader)
{
StringTable table = new StringTable();
//set column-names
for (int columnIndex = 0; columnIndex < reader.FieldCount; columnIndex++)
{
table.AddColumn(reader.GetName(columnIndex));
}
//set records
while (reader.Read())
{
List<string> recordValues = new List<string>();
for (int columnIndex = 0; columnIndex < reader.FieldCount; columnIndex++)
{
recordValues.Add(reader.GetValue(columnIndex).ToString());
}
table.AddRow(recordValues);
}
return (table);
}
Creating CSV File
The records of a StringTable can be saved in a comma-separated-values (CSV) file using the following method SaveAsCsv(). This method uses TextWriter class for writing lines of data. The first line of the file contains column-names and the other lines contains records (one record per line).
public void SaveAsCsv(string path)
{
TextWriter writer = new StreamWriter(path);
//write header (column names)
writer.WriteLine(string.Join(CsvColumnSeparator, ColumnNames.ToArray()));
//write all rows
foreach (int rowNumber in this.Keys)
{
string[] recordValues = (from value in this[rowNumber].Values
select "\"" + value + "\"").ToArray();
writer.WriteLine(string.Join(CsvColumnSeparator, recordValues));
}
writer.Close();
}
The following image shows a CSV file created by this method. This image shows the CSV file opened in Microsoft Office Excel.
Rendering StringTable in ASP.NET / Silverlight DataGrid
The data in a StringTable can be rendered in a ASP.NET DataGrid or as a HTML table. The following method creates a HtmlTable object from a StringTable and returns the HTML markup of the table. This method creates a HtmlTable and creates its equivalent HTML markup by rendering using StringBuilder and HtmlTextWriter classes.
public string RenderAsHtmlTable()
{
HtmlTable htmlTable = new HtmlTable();
htmlTable.Border = 1;
//create header-row
HtmlTableRow headerRow = new HtmlTableRow();
foreach (string columnName in this.ColumnNames)
{
HtmlTableCell headerCell = new HtmlTableCell();
headerCell.InnerText = columnName;
headerRow.Cells.Add(headerCell);
}
htmlTable.Rows.Add(headerRow);
//create data-rows
foreach (int recordIndex in this.Keys)
{
HtmlTableRow dataRow = new HtmlTableRow();
foreach (string columnName in this.ColumnNames)
{
HtmlTableCell dataCell = new HtmlTableCell();
dataCell.InnerText = this[recordIndex][columnName];
dataRow.Cells.Add(dataCell);
}
htmlTable.Rows.Add(dataRow);
}
//render html-table and get html-markup
StringBuilder buffer = new StringBuilder();
HtmlTextWriter writer = new HtmlTextWriter(new StringWriter(buffer));
htmlTable.RenderControl(writer);
return (buffer.ToString());
}
The following image shows the HTML markup generated by this method.
The following image shows the above HTML rendered in Mozilla Firefox browser.
For rendering Silverlight DataGrid control, please refer this article. It explains how to create a new Silverlight DataGrid control with dynamic set of columns and rows, using a StringTable.
Converting StringTable to JSON
JSON is the most widely used lightweight data storage and interchange format. Converting StringTable to JSON will be very useful in storing and retrieving a tabular data in a single field of a database table. For example, consider the case to store the four co-ordinates of a 3D Plane in a single field without creating an additional table in the database. Here you can use StringTable for co-ordinates and convert it to JSON for storing & retrieving in a database table. The following sample class Plane3D illustrates this concept. The Points property is a StringTable with three columns namely x, y and z. Each row in this table represents a co-ordinate of the plane.
public class Plane3D
{
public string Name { get; set; }
public StringTable Points { get; set; }
#region Constructors
public Plane3D()
{
Name = string.Empty;
Points = new StringTable(new string[] { "x", "y", "z" });
}
#endregion
#region Methods
public void SetPoints(int x1, int y1, int z1,
int x2, int y2, int z2,
int x3, int y3, int z3,
int x4, int y4, int z4)
{
this.Points.Clear();
this.Points.AddRow(new string[] { x1.ToString(), y1.ToString(), z1.ToString() });
this.Points.AddRow(new string[] { x2.ToString(), y2.ToString(), z2.ToString() });
this.Points.AddRow(new string[] { x3.ToString(), y3.ToString(), z3.ToString() });
this.Points.AddRow(new string[] { x4.ToString(), y4.ToString(), z4.ToString() });
}
public string GetPointsAsJson()
{
return (JsonUtility.SerializeObject(this.Points));
}
#endregion
}
The GetPointsAsJson() method uses JsonUtility class for converting the points to JSON string. The following code shows an example for Plane3D class usage.
Plane3D plane3d = new Plane3D();
plane3d.Name = "plane1";
plane3d.SetPoints(0, 0, 0, 2, 0, 0, 2, 3, 0, 0, 3, 0);
string json = plane3d.GetPointsAsJson();
The JSON string of the Points property will look like:
[{"Key":0,"Value":[{"Key":"x","Value":"0"},
{"Key":"y","Value":"0"},
{"Key":"z","Value":"0"}]},
{"Key":1,"Value":[{"Key":"x","Value":"2"},
{"Key":"y","Value":"0"},
{"Key":"z","Value":"0"}]},
{"Key":2,"Value":[{"Key":"x","Value":"2"},
{"Key":"y","Value":"3"},
{"Key":"z","Value":"0"}]},
{"Key":3,"Value":[{"Key":"x","Value":"0"},
{"Key":"y","Value":"3"},
{"Key":"z","Value":"0"}]}]
However in a real-time scenario, a separate class (say Point3D) will be created and a List<Point3D> will be serialized to JSON string. The above example is to illustrate the usage of StringTable class (or System.Collections.Generic.Dictionary class) for easy storage and retrieval of complex data structures in a single field of a database table.