DataTableTutorial

Creating a DataTable

DataTable dTable = new DataTable("Dynamically_Generated");

Creating Columns in the DataTable

// 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

// specify it as auto increment field

auto.AutoIncrement = true;

auto.AutoIncrementSeed = 1;

auto.ReadOnly = true;


Specifying Primary Key column in the DataTable

// create primary key on this field

DataColumn[] pK = new DataColumn[1];

pK[0] = auto;

dTable.PrimaryKey = pK;

Populating data into DataTable

DataRow row = null;

for (int i = 0; i < 5; 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

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


dTable.Rows[2]["AutoID"] = 20;

dTable.Rows[2]["Name"] = "Modified";

dTable.Rows[2]["Address"] = "Modified Address";

dTable.AcceptChanges();


Deleting Row

dTable.Rows[1].Delete();

dTable.AcceptChanges();

Filtering data from DataTable

DataRow[] rows = dTable.Select(" AutoID > 5");

DataRow[] rows1 = dTable.Select(" AutoID > 5", "AuotID ASC");

Adding filtered data to another datatable

foreach (DataRow thisRow in rows)

{

    // add values into the datatable

    dTable1.Rows.Add(thisRow.ItemArray);

}

maximum value of a particular column

DataRow[] rows22 = dTable.Select("AutoID = max(AutoID)");

string str = "MaxAutoID: " + rows22[0]["AutoID"].ToString();

Compute method of the DataTable

object objSum = dTable.Compute("sum(AutoID)", "AutoID > 7");

string sum = "Sum: " + objSum.ToString();

// To get sum of AutoID for all rows of the DataTable

object objSum = dTable.Compute("sum(AutoID)", "");

Sorting data of DataTable

// Sorting DataTable

DataView dataView = new DataView(dTable);

dataView.Sort = " AutoID DESC, Name DESC";

foreach (DataRowView view in dataView)

{

    Response.Write(view["Address"].ToString());

}

Writing and Reading XmlSchema of the DataTable

// 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

// 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"));

How to remove duplicate rows from a datatable
DataColumn[] keyColumns = new DataColumn[] { dtStore.Columns["DocUniqueID"], dtStore.Columns["DocType"] };
//remove the duplicates
RemoveDuplicates(dtStore, keyColumns);

private static void RemoveDuplicates(DataTable tbl,DataColumn[] keyColumns)
{
int rowNdx = 0;
while (rowNdx 0)
{
foreach (DataRow dup in dups)
{
tbl.Rows.Remove(dup);
}
}
else
{
rowNdx++;
}
}
}

private static DataRow[] FindDups(DataTable tbl,int sourceNdx,DataColumn[] keyColumns)
{
ArrayList retVal = new ArrayList();
DataRow sourceRow = tbl.Rows[sourceNdx];
for (int i = sourceNdx + 1; i < tbl.Rows.Count; i++)
{
DataRow targetRow = tbl.Rows[i];
if (IsDup(sourceRow, targetRow, keyColumns))
{
retVal.Add(targetRow);
}
}
return (DataRow[])retVal.ToArray(typeof(DataRow));
}

private static bool IsDup(DataRow sourceRow,DataRow targetRow,DataColumn[] keyColumns)
{
bool retVal = true;
foreach (DataColumn column in keyColumns)
{
retVal = retVal && sourceRow[column].Equals(targetRow[column]);
if (!retVal) break;
}
return retVal;
}