Dynamically creating and utilizing DataSet, DataTable, DataColumn, DataRow, DataRelation and DataView Objects

Introduction

In this article I will aim to introduce and deliver the following concepts:

1- Dynamically creating DataSet
2- Dynamically creating DataTable, DataColumn and DataRow Objects
3- Dynamically creating DataRelation among DataTable Objects within DataSet.
4- Dynamically creating DataView, Binding and Writing DataSet Contents

I am going to follow a scenerio like lets say we have dynamically built two DataTables in memory and we want to have a third DataTable that is a composite of first two DataTables based on DataRelation.

1- Dynamically creating DataSet

Creating a DataSet is as simple as that, simply instantiate an object of DataSet class:

        DataSet ds = new DataSet("MyDataSet");

A dataset is made up of atleast one DataTable. That datatable will have datacolumns and datarows. If we have two DataTables then we probably need to have Foreign Key Constraint or Data Relation constraint that we will be discussing shortly.

2- Dynamically creating DataTable, DataColumn and DataRow Objects

Lets create our first DataTable.

        DataTable dt1 = new DataTable("DataTable_PlotStatus");

Lets create PrimaryKey Column.

        DataColumn dt1PrimaryKeyColumn = dt1.Columns.Add("PlotID", Type.GetType("System.Int32"));
        dt1.PrimaryKey = new DataColumn[] { dt1PrimaryKeyColumn };
      dt1.Columns.Add("PlotStatus", Type.GetType("System.String"));

There are two major ways to add a Row into our DataTable. First is to create a Row and and populate it with data like this:

        DataRow dt1DataRow = dt1.NewRow();
        dt1DataRow[0] = 1;
        dt1DataRow[1] = "Active";

This is how we will then add the row to DataRow Collection of DataTable.

        dt1.Rows.Add(dt1DataRow);

Second way is to actually use Add method of DataRow Collection.

    dt1.Rows.Add(new Object[] { 2, "In Active" });
        dt1.Rows.Add(new Object[] { 3, "Active" });
        dt1.Rows.Add(new Object[] { 4, "In Active" });

Now lets add the DataTable to DataSet that we have created.

        ds.Tables.Add(dt1);

Since I am going to introduce a DataRelation Object, so I am going to create a second DataTable and populate it with some data.

        DataTable dt2 = new DataTable("DataTable_PlotDescription");

        DataColumn dt2PrimaryKeyColumn = dt2.Columns.Add("DetailsID", Type.GetType("System.Int32"));
        dt2.Columns.Add("PlotPrice", Type.GetType("System.Decimal"));
        dt2.Columns.Add("PlotLocation", Type.GetType("System.String"));
        dt2.Columns.Add("PlotSize", Type.GetType("System.String"));

        dt2.PrimaryKey = new DataColumn[] { dt2PrimaryKeyColumn };

        DataRow dt2DataRow = dt2.NewRow();
        dt2DataRow[0] = 1;
        dt2DataRow[1] = 20000;
        dt2DataRow[2] = "1 Shrewsbury Road";
        dt2DataRow[3] = "500 Sq. Yards";
        dt2.Rows.Add(dt2DataRow);

        dt2.Rows.Add(new Object[] { 2, 10000, "1 Shrewsbury Road", "500 Sq. Yards" });
        dt2.Rows.Add(new Object[] { 3, 30000, "2 Shrewsbury Road", "550 Sq. Yards" });
        dt2.Rows.Add(new Object[] { 4, 50000, "3 Shrewsbury Road", "30 Sq. Yards" });

        ds.Tables.Add(dt2);

3- Dynamically creating DataRelation among DataTable Objects within DataSet.

To Create a DataRelation between DataTables is very simple. One thing which is very important is to remember that each row in the child table must have a corresponding row in parent table.

            ds.Relations.Add("PlotsRelation",
            ds.Tables["DataTable_PlotStatus"].Columns["PlotID"],
            ds.Tables["DataTable_PlotDescription"].Columns["DetailsID"]);

Now lets dynamically build our third DataTable that will actually combine first two DataTables that we have created (i.e. DataTable_PlotStatus and DataTable_PlotDescription). This DataTable will be based on our DataRelation object that we have created.

        DataTable dt3 = new DataTable("DataTable_FinalPlotView");
        
        dt3.Columns.Add("PlotID", Type.GetType("System.Int32"));
        dt3.Columns.Add("PlotStatus", Type.GetType("System.String"));
        dt3.Columns.Add("DetailsID", Type.GetType("System.Int32"));
        dt3.Columns.Add("PlotPrice", Type.GetType("System.Decimal"));
        dt3.Columns.Add("PlotLocation", Type.GetType("System.String"));
        dt3.Columns.Add("PlotSize", Type.GetType("System.String"));


        foreach (DataRow parentRow in ds.Tables["DataTable_PlotStatus"].Rows)
        {
            DataRow dt3DataRow = dt3.NewRow();
            dt3DataRow[0] = parentRow["PlotID"];
            dt3DataRow[1] = parentRow["PlotStatus"];

            foreach (DataRow childRow in parentRow.GetChildRows("PlotsRelation"))
            {
                dt3DataRow[2] = childRow["DetailsID"];
                dt3DataRow[3] = childRow["PlotPrice"];
                dt3DataRow[4] = childRow["PlotLocation"];
                dt3DataRow[5] = childRow["PlotSize"];
            
            }
            dt3.Rows.Add(dt3DataRow);
        
        }

        ds.Tables.Add(dt3);

4- Dynamically creating DataView, Binding and Writing DataSet Contents

Now we can output our results in variety of ways, lets say we want to filter our DataSet using DataView and bind it to GridView.

        DataView dv = new DataView();
        dv.Table = ds.Tables["DataTable_FinalPlotView"];
        dv.Sort = "PlotID desc";
        dv.RowFilter = "PlotStatus = 'In Active'";
        dv.RowStateFilter= DataViewRowState.CurrentRows;
        GridView1.DataSource = dv;

Or simply we can bind DataSet to GridView like this:

        GridView1.DataSource = ds.Tables["DataTable_PlotDescription"];

One of the ways that I really like is to write the dataset content into an xml file for thorough investigation purposes. So lets say I want to write my dataset content in XML format to a file - DataSetOutput.xml in App_Code folder, then we can achieve this by following:

        ds.WriteXml(Server.MapPath("App_Code//DataSetOutput.xml"), XmlWriteMode.IgnoreSchema);

I hope you have enjoyed reading the article, in the meantime if you have any questions, do let me know.

You must Sign In to use this message board.

FAQ  Noise Tolerance Very HighHighMediumLowVery Low  Search Messages    Layout Expand All MessagesThread ViewNo Javascript (slow)No Javascript Preview    Per page 102550

Msgs 1 to 1 of 1 (Total in Forum: 1) (Refresh) FirstPrevNext

Good One ss_coders 8:46 12 May '08   quick and good code !! Sign In·View Thread·PermaLink 2.00/5 (1 vote)

Last Visit: Wednesday, December 3, 2008 8:42 PM     Last Update:Wednesday, December 3, 2008 8:42 PM