In this tutorial i will discuss how we can manage DataTable object on the fly using Asp.net C#. There were a lot of scnario where we need to dynamically create a DataTable, modify, sorting & paging. Asp.net gives us the bulit in System.Data namespace to programatically manage tabular data in runtime. Note that the DataTable can be bound to any data Web control just like a DataSet or DataReader as a datasource. When binding a DataTable to a data Web control, in actuality, the default DataTable is is used as a datasource to that control like GridView, Repeater, Dropdown List etc.
Focus Area:
1. Create a DataTable
2. Modify a DataTable
3. Delete From a DataTable
4. Filtering, Sorting & Copy one DataTable to Another
5. Use Aggregate Functions
6. Create Computed Columns
7. Create XML file from a DataTable
8. DataView Sorting, Filtering, Binding
9. Bind the DataTable with GridView & Dropdown List
Create a DataTable:
To create a DataTable in runtime first instantiate the DataTable object then add columns with data types & then assign values to create rows.
DataTable oTable = new DataTable("Article");
//Add DataTable column dynamically/run time/on the fly.
oTable.Columns.Add(new DataColumn("ID", typeof(System.Int64)));
oTable.Columns.Add(new DataColumn("Title", typeof(System.String)));
oTable.Columns.Add(new DataColumn("Published", typeof(System.DateTime)));
//Add DataTable rows dynamically/run time/on the fly.
oTable.Rows.Add(1001, "DataTable Engineering", DateTime.Now);
oTable.Rows.Add(1002, "Event Calendar", DateTime.Now);
oTable.Rows.Add(1003, "Master Detail Data", DateTime.Now);
// Bind GridView Data from dynamically created DataTable
GridView1.DataSource = oTable;
GridView1.DataBind();
Modify a DataTable:
You can modify any row's or column's data by specifying the row's index or column's index. Also you can access all values by iterative statement.
//By specifying column index
oTable.Rows[0][0] = 1000;
//By Specifying column name
oTable.Rows[1]["Title"] = "Event Calendar in Asp.net";
oTable.AcceptChanges();
//Iterate
foreach (DataRow oItem in oTable.Rows)
Response.Write(oItem[0].ToString() + " " + oItem[1].ToString() + " " + oItem[2].ToString()+"</br>");
//OR
for(int i=0;i<oTable.Rows.Count;i++)
Response.Write(oTable.Rows[i][0].ToString() + " " + oTable.Rows[i][1].ToString() + " " + oTable.Rows[i][2].ToString() + "</br>");
Delete From a DataTable:
You can runtime delete any row from the DataTable directly. One thing you have to keep in mind that you will get "Deleted row information cannot be accessed through the row" error if try to iterate through rows without use AcceptChanges() just after deletion.
oTable.Rows[1].Delete();
oTable.AcceptChanges();
GridView1.DataSource = oTable;
GridView1.DataBind();
Filtering, Sorting & Copy one DataTable to Another:
You can apply filter to a DataTable by Select() method. This method will take two argument. The first one is FilterExpression & 2nd one is Sort Expression. You knew the in Asp.net object will take same reference when you assign an object to another. Which causes same impact on both objects. We can remove the reference by using the Clone() method. Have a look:
DataRow[] oRows = oTable.Select("ID>500", "Title DESC");
// Create oTable DataTable structure to OTable2 object
DataTable oTable2 = oTable.Clone();
// Now assign the rows data
foreach (DataRow oItem in oRows)
oTable2.Rows.Add(oItem.ItemArray);
GridView1.DataSource = oTable2;
GridView1.DataBind();
For an Example on Clone() method
Click Here.
Use Aggregate Functions:In this example here i will show you how you can working with Aggregate Functions in a DataTable. To do that DataTable provide us a method named Compute() which will take two parameters "String Expression", "String Filter". Within "String Expression" we can apply the Aggregate function & in "String Filter" we can apply our required filterings. You will receive Evaluate Expression error "Cannot find column [xxxx]" if you provide wrong column name in Aggregate function.
DateTime LastPublished = Convert.ToDateTime(oTable.Compute("MAX(Published)", "Published > 'Jan 01, 2005'"));
Response.Write(LastPublished.ToString("MMM dd, yyyy"));
int RowCount = Convert.ToInt32(oTable.Compute("COUNT(Title)", "Published > 'Jan 01, 2005'"));
Response.Write(RowCount.ToString());
Create Computed Columns:
You can add runtime a summary or status column in a DataTable. Which will minimize your effort dramatically. Let i want to display a column in the GridView that does this article published in this year or not. For such type cases we can use Expression property which save iur valuable time.
oTable.Columns.Add("This Year?", typeof(System.Boolean));
oTable.Columns[3].Expression = "Published > 'Jan 01, 2010'";
GridView1.DataSource = oTable;
GridView1.DataBind();
Create XML file from a DataTable: Asp.net provide us a method named WriteXmlSchema to create runtime or on the fly XML file.
// Just one line code Convert DataTable to XML Schema
//Check the root folder for the XML file
oTable.WriteXmlSchema(Server.MapPath(".")+"/XMLFile.xml");
For Security issues
Click Here.
DataView Sorting, Filtering, Binding:DataView is the top of a DataTable. From the above post i discussed a lot of issues on DataTable. Now i will show you how we can easily create a DataView, apply filtering as well as sorting. I also show you a most common usage like "Convert DataTable to DataView" & "Convert DataView to DataTable".
// Convert DataTable to DataView
DataView dView = new DataView(oTable);
// Sorting DataView
dView.Sort = " ID DESC, Title ASC";
//Filtering DataView
dView.RowFilter = "Published > 'Jan 01, 2010'";
// Convert DataView to DataTable
DataTable oTable3 = dView.Table;
// Bind DataView as a DataSource
GridView1.DataSource = dView;
GridView1.DataBind();
Bind the DataTable with GridView & Dropdown List:I have already discussed how to bind a DataTable or DataView to a GridView control. For DropdownList
Click Here.
Subscribe
