Tweaking a Windows DataGrid control

• Download source files - 15.5 Kb

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;  //importing this namespace for oleDB classes

private void btnLoadFields_Click(object sender, System.EventArgs e)
{
  //preparing the connection
  string accessDB_filename = AppDomain.CurrentDomain.BaseDirectory + 
                                           "tweakDataGrid_testDB.mdb";
  string sConnstring = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                   "Data Source=" + accessDB_filename;
    
  //connecting to the source
  conn1= new OleDbConnection(sConnstring);
  conn1.Open(); 
  
  //loading the dataset with records using a sql command.
  ds_data = new DataSet("test");
  OleDbCommand cmd1 = new OleDbCommand(
                     "select * from telephone_orders", conn1);
  OleDbDataAdapter dbAdapter1 = new OleDbDataAdapter(cmd1);
  dbAdapter1.Fill(ds_data); 
  //link the dataset as a datasource to the grid.
  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() // returns the below string

<?xml version="1.0" encoding="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 XMLSchemas came into 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:

1. The schema can be worked with using XML DOM or other parsers since it's a valid XML string.
2. 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:

//importing the below namespaces to work with xml 
using System.Xml;
//this is useful if you will use XMLSchema class
using System.Xml.Schema;  
//this will be useful to create a stringWriter to 
//load our finished shema into dataset.
using System.IO;

Steps

1. Load the existing schema into the xmldom document.
2. Select the node (the 'sequence' node) where we have to add a child to create the new data types.
3. Create a new element with attributes for the new data types.
4. Insert the new element into the schema document.
5. 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;
   
   //load dataset schema into xml
   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:

   //modify the xml schema.
   /////////
   //get node for inserting new schema types
   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";
        
   //use the namespacemanager object in parsing 
   //alongwith XPATH
   oN = oXMLschema.SelectSingleNode(
                  xpath_toEle_inDSschema,nmsMgr);
   
   if (oN!=null)
   {
    
        //Get the first child in the sequence.. 
        //so we can insert new datatype before it.
        oN1 = oN.FirstChild;
        
        if (newType=="BOOLEAN")
        {
             //1. insert boolean dataType
             oAtt1 = oXMLschema.CreateAttribute("name");
            
            
             //name of dataType passed to method as parameter.
             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")
        {
             //2. insert string dataType
             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);
        }
        
        //clear dataSet and load customized xml
        //////////////
        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

1. Forget the tweak. The simplest of all is that we could generate the XMLSchema of an XML by using the DataSet class alone.
2. 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.

3. 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

• Microsoft XMLSchema Class - MSDN documentation
• For exploring and understanding more of XMLSchema, I liked the following startup links:
  • Smarten Up Your DataSets with XML Schema
  • XSD.exe tool from Microsoft.. for generating xmlSchema for existing xml files

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