Introduction
This article touches the idea of adding one or more columns to a DataGrid
by manipulating the DataSet
which is linked to the DataGrid
. The objective is to add new columns to the DataGrid
. These columns are not present in the DataSet
which is populated with rows returned from a database query.
Basic DataGrid Code
I created a new project with a DataGrid
control, loaded it with data from an Access MDB file using the classes OleDbConnection
, OleDbCommand
, OleDbDataAdapter
, and DataSet
:
using System.Data.OleDb;
private void btnLoadFields_Click(object sender, System.EventArgs e)
{
string accessDB_filename = AppDomain.CurrentDomain.BaseDirectory +
"tweakDataGrid_testDB.mdb";
string sConnstring = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + accessDB_filename;
conn1= new OleDbConnection(sConnstring);
conn1.Open();
ds_data = new DataSet("test");
OleDbCommand cmd1 = new OleDbCommand(
"select * from telephone_orders", conn1);
OleDbDataAdapter dbAdapter1 = new OleDbDataAdapter(cmd1);
dbAdapter1.Fill(ds_data);
dataGrid1.DataSource = ds_data;
}
The Tweak
There are many methods to add a new column in the DataGrid
. I took the approach of modifying the underlying XML schema of a DataSet
and applying this DataSet
to the grid and then just refreshing the grid to see the changes. I wrote a small method to isolate the part where DataGrid
is linked to a DataSet
and refreshed... so that this could be called from other methods easily if ds_data
is loaded with data from any method:
private void loadData()
{
dataGrid1.DataSource = ds_data;
dataGrid1.Refresh();
}
For understanding the tweak, we need to study the structure of an XMLSchema
and what the schema means so that we can debug if there are any errors.
A Typical XMLSchema
The XMLSchema
of my DataSet
looks like the one shown below. The DataSet
class has two methods to retrieve the XMLSchema
of the data and the data itself in XML format. The methods are getXML()
and getXMLSchema()
:
ds_data.getXMLSchema()
="1.0"="utf-16"
<xs:schema id="test" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="test" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="name" type="xs:string" minOccurs="0" />
<xs:element name="phone" type="xs:string" minOccurs="0" />
<xs:element name="phoneOrderNo"
type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
What is Contained in an XMLSchema?
XMLSchema
is nothing but the DataTypeDefn
of an XML. While DTD is a specification by a different consortium, XMLSchema
is a specification of Microsoft. As far as I know, only Microsoft tools support XMLSchema
. All open source and others support DTD, which is older and has been supported and used even before XMLSchema
s came into the picture. An XMLSchema
defines a set of rules and is used for validating or parsing the XML created for tools that support a particular schema.
XMLSchema
is a definition language for defining things like:
- The data types that can be contained in an XML, like our schema above has a structure that our XML should contain: This is the defined structure if you can see the above schema's
<xs:element>
tags:
<test>
<Table>
//We derive that its a string from type="xs:string"
<name>some string</name>
<phone>some string</phone>
<phoneOrderNo>some string</phoneOrderNo>
</Table>
<Table>
<name>some string</name>
<phone>some string</phone>
<phoneOrderNo>some string</phoneOrderNo>
</Table>
</test>
- The number of times a particular element in the XML should occur.
In our schema, all 'minOccurs
' are 0
. It means that an XML without <name>
, <phone>
, <phoneOrderNo>
tags is still valid.
Why Rules for XML?
XML in general can be anything but the applications that we develop expect XML inputs with certain elements, certain data types and sometimes certain minimum number of elements.
So we write an XMLSchema
with these rules and validate our input XML against the schema to determine whether or not the XML is usable.
Tweaking the XMLSchema
There can be two approaches to tweaking the schema:
- The schema can be worked with using XML DOM or other parsers since it's a valid XML
string
. - Microsoft also provides a class called
XMLSchema
under the System.XML.Schema
namespace.
I prefer the first method since I cannot see much possibility in modifying an existing schema with the XMLSchema
class. But for generating an XMLSchema
from scratch, this class could be useful.
I wrote the following method to add a boolean datatype
or text datatype
into an existing XMLSchema
of a DataSet
:
using System.Xml;
using System.Xml.Schema;
using System.IO;
Steps
- Load the existing schema into the
xmldom
document. - Select the node (the 'sequence' node) where we have to add a child to create the new data types.
- Create a new element with attributes for the new data types.
- Insert the new element into the schema document.
- Create a new
DataSet
with the new schema, and old data... and load this to the DataGrid
.
private void Add_toSchema(string newType, string nameOfCol)
{
XmlDocument oXMLschema;
XmlElement oE;
XmlAttribute oAtt1, oAtt2, oAtt3;
XmlNode oN, oN1;
oXMLschema = new XmlDocument();
oXMLschema.LoadXml(ds_data.GetXmlSchema());
Parsing the schema document to do a selectSingleNode
for the 'sequence' element is tricky since all the elements involve a namespace prefix namely "xs
". So, we create an XmlNamespaceManager
object and link it to the 'NameTable
' of our schema document. Then, we use the AddNamespace
method to add the namespace which we have to include while parsing:
XmlNamespaceManager nmsMgr =
new XmlNamespaceManager(oXMLschema.NameTable);
nmsMgr.AddNamespace("xs",
"http://www.w3.org/2001/XMLSchema");
string xpath_toEle_inDSschema = "/xs:schema[@id='test']/"+
"xs:element[@name='test']/"+
"xs:complexType/xs:choice/xs:element[@name='Table']/"+
"xs:complexType/xs:sequence";
oN = oXMLschema.SelectSingleNode(
xpath_toEle_inDSschema,nmsMgr);
if (oN!=null)
{
oN1 = oN.FirstChild;
if (newType=="BOOLEAN")
{
oAtt1 = oXMLschema.CreateAttribute("name");
oAtt1.Value=nameOfCol;
oAtt2 = oXMLschema.CreateAttribute("type");
oAtt2.Value="xs:boolean";
oAtt3 = oXMLschema.CreateAttribute("minOccurs");
oAtt3.Value="0";
oE = oXMLschema.CreateElement("xs","element",
"http://www.w3.org/2001/XMLSchema");
oE.Attributes.Append(oAtt1);
oE.Attributes.Append(oAtt2);
oE.Attributes.Append(oAtt3);
oN.InsertBefore(oE,oN1);
}
else if (newType=="TEXT")
{
oAtt1 = oXMLschema.CreateAttribute("name");
oAtt1.Value=nameOfCol;
oAtt2 = oXMLschema.CreateAttribute("type");
oAtt2.Value="xs:string";
oAtt3 = oXMLschema.CreateAttribute("minOccurs");
oAtt3.Value="0";
oE = oXMLschema.CreateElement("xs","element",
"http://www.w3.org/2001/XMLSchema");
oE.Attributes.Append(oAtt1);
oE.Attributes.Append(oAtt2);
oE.Attributes.Append(oAtt3);
oN.InsertBefore(oE,oN1);
}
ds_data.Clear();
ds_data = new DataSet("test");
StringReader sr = new StringReader(oXMLschema.OuterXml);
ds_data.ReadXmlSchema(sr);
}
}
Done! By calling the above method with the parameters shown below, we achieve the necessary tweaking to insert a new column in the DataGrid
.
Add_toSchema("BOOLEAN", "callSuccess");
Possibilities This Tweak Opens
- Forget the tweak. The simplest of all is that we could generate the
XMLSchema
of an XML by using the DataSet
class alone. - By tweaking the
XMLSchema
of a DataSet
linked to a DataGrid
, we can modify the way the UI interacts with the user. For example, we could add a boolean column to select records to approve if it's an article approval engine, or delete selected rows if it's a comment approval engine... so much in content management.
I started doing this for a telemarketing application where a list of phone numbers is loaded from the database and called. If the call succeeds, the user manually checks the boolean column, and types his/her remarks about the call into the String
column. Also, the below feature 3 would help my users save their work and generate reports.
- We can save the XML data and the schema as a log file, which can be later loaded into a
DataGrid
for viewing.
Related Links
License
This article has no explicit license attached to it, but may contain usage terms in the article text or the download files themselves. If in doubt, please contact the author via the discussion board below.
A list of licenses authors might use can be found here.