Creating a DataTable
To create a DataTable, you need to use System.Data namespace, generally when you create a new class or page, it is included by default by the Visual Studio. Lets write following code to create a DataTable object. Here, I have pased a string as the DataTable name while creating DataTable object.
// instantiate DataTable
DataTable dTable = new DataTable("Dynamically_Generated");
Creating Columns in the DataTable
To create column in the DataTable, you need to use DataColumn object. Instantiate the DataColumn object and pass column name and its data type as parameter. Then call add method of DataTable column and pass the DataColumn object as parameter.
// create columns for the DataTable
DataColumn auto = new DataColumn("AutoID", typeof(System.Int32));
dTable.Columns.Add(auto);
// create another column
DataColumn name = new DataColumn("Name", typeof(string));
dTable.Columns.Add(name);
// create one more column
DataColumn address = new DataColumn("Address", typeof(string));
dTable.Columns.Add(address);
Specifying AutoIncrement column in the DataTable
To specify a column as AutoIncrement (naturally it should be an integer type of field only), you need to set some properties of the column like AutoIncrement, AutoIncrementSeed.
// specify it as auto increment field
auto.AutoIncrement = true;
auto.AutoIncrementSeed = 1;
auto.ReadOnly = true;
If you want a particular column to be a unique column ie. you don't want duplicate records into that column, then set its Unique property to true like below.
auto.Unique = true;
Specifying Primary Key column in the DataTable
To set the primary key column in the DataTable, you need to create arrays of column and store column you want as primary key for the DataTable and set its PrimaryKey property to the column arrays. See the code below.
// create primary key on this field
DataColumn[] pK = new DataColumn[1];
pK[0] = auto;
dTable.PrimaryKey = pK;
Populating data into DataTable
There are two ways to populate DataTable.
Using DataRow object
// populate the DataTable using DataRow object
DataRow row = null;
for (int i = 0; i <>
{
row = dTable.NewRow();
row["AutoID"] = i + 1;
row["Name"] = i + " - Ram";
row["Address"] = "Ram Nagar, India - " + i;
dTable.Rows.Add(row);
}
Asiging the value of column using Arrays
// manually adding rows using array of values
dTable.Rows.Add(6, "Manual Data - 1", "Manual Address - 1, USA");
dTable.Rows.Add(7, "Manual Data - 2", "Manual Address - 2, USA");
Modifying data into DataTable
Modifying Row Data
// modify certain values into the DataTable
dTable.Rows[2]["AutoID"] = 20;
dTable.Rows[2]["Name"] = "Modified";
dTable.Rows[2]["Address"] = "Modified Address";
dTable.AcceptChanges();
Deleting Row
// Delete row
dTable.Rows[1].Delete();
dTable.AcceptChanges();
Filtering data from DataTable
To filter records from the DataTable, use Select method and pass necessary filter expression. In below code, the 1st line will simply filter all rows whose AutoID value is greater than 5. The 2nd line of the code filters the DataTable whose AutoID value is greater than 5 after sorting it.
DataRow[] rows = dTable.Select(" AutoID > 5");
DataRow[] rows1 = dTable.Select(" AutoID > 5", "AuotID ASC");
Note that Select method of the DataTable returns the array of rows that matche the filter expression. If you want to loop through all the filtered rows, you can use foreach loop as shown below. In this code, I am adding all the filtered rows into another DataTable.
foreach (DataRow thisRow in rows)
{
// add values into the datatable
dTable1.Rows.Add(thisRow.ItemArray);
}
Sorting data of DataTable
Using DataView
See the code below.
// Sorting DataTable
DataView dataView = new DataView(dTable);
dataView.Sort = " AutoID DESC, Name DESC";
foreach (DataRowView view in dataView)
{
Response.Write(view["Address"].ToString());
}
Using DataTable.Select() method
Yes, you can sort all the rows using Select method too provided you have not specified any filter expression. If you will specify the filter expression, ofcourse your rows will be sorted but filter will also be applied. A small drawback of this way of sorting is that it will return array of DataRows as descibed earlier so if you are planning to bind it to the Data controls like GridView or DataList you will have for form a DataTable by looping through because directly binding arrays of rows to the Data controls will not give desired results.
DataRow[] rows = dTable.Select("", "AutoID DESC");
Writing and Reading XmlSchema of the DataTable
If you need XmlSchema of the DataTabe, you can use WriteXmlSchema to write and ReadXmlSchema to read it. There are several overloads methods of both methods and you can pass filename, stream, TextReader, XmlReader etc. as the parameter. In this code, the schema will be written to the .xml file and will be read from there.
// creating schema definition of the DataTable
dTable.WriteXmlSchema(Server.MapPath("~/DataTableSchema.xml"));
// Reading XmlSchema from the xml file we just created
DataTable dTableXmlSchema = new DataTable();
dTableXmlSchema.ReadXmlSchema(Server.MapPath("~/DataTableSchema.xml"));
Reading/Writing from/to Xml
If you have a scenario, where you need to write the data of the DataTable into xml format, you can use WriteXml method of the DataTable. Note that WriteXml method will not work if you will not specify the name of the DataTable object while creating it. Look at the first code block above.
// Note: In order to write the DataTable into XML,
// you must define the name of the DataTable while creating it
// Also if you are planning to read back this XML into DataTable, you should define the XmlWriteMode.WriteSchema too
// Otherwise ReadXml method will not understand simple xml file
dTable.WriteXml(Server.MapPath("~/DataTable.xml"), XmlWriteMode.WriteSchema);
// Loading Data from XML into DataTable
DataTable dTableXml = new DataTable();
dTableXml.ReadXml(Server.MapPath("~/DataTable.xml"));
If you are planning to read the xml you have just created into the DataTable sometime later then you need to specify XmlWriteMode.WriteSchema too as the 2nd parameter while calling WriteXml method of the DataTable otherwise normally WriteXml method doesn't write schema of the DataTable. In the abscence of the schema, you will get error (DataTable does not support schema inference from Xml) while calling ReadXml method of the DataTable.
Source:http://www.dotnetfunda.com/articles/article131.aspx
