Well, everyone has worked with datatables sometime or the other. In this article, I just wanted to illustrate the various usage and implementation of datatables.
The DataTable object represents tabular data as rows, columns, and constraints.Use the DataTable object to hold data in memory while performing disconnected data operations.Datatable consists of DataColumn and DataRow.
The DataTable object must contain DataColumn objects before any data can be added to the DataTable object.
In this example, we have a datatable with three columns, Name, Adress and Date of Joining(DOJ) which is a datetime column.
dtResults = new DataTable();
dtResults.Columns.Add(“Name”, Type.GetType(“System.String”));
dtResults.Columns.Add(“Department”, Type.GetType(“System.String”));
dtResults.Columns.Add(“DOJ”, Type.GetType(“System.DateTime”));
Adding row to datatable:
dtResults.Rows.Add(“Olyvia”, “Developer”, DateTime.Now);
Adding a datatacolumn to dtresults and populating it with default values
dcRec = new DataColumn(“Age”, typeof(int));
dcRec.DefaultValue = 23;
dtResults.Columns.Add(dcRec);
datatable Select
The DataTable Select method accepts a filter and sort argument to return an arry of DataRow objects that conform to the criteria in a FilterExpression.
DataRow[] drResultSet= dtResults.Select(“Age ‘21′”);
To get the column name dynamically, see the example below:
DataRow[] drResultSet;
drResultSet = dtResults.Select(site.Fields["ID"].InternalName + “=’” + dtStore.Rows[i]["DocUniqueID"].ToString().Substring(0, dtStore.Rows[i]["DocUniqueID"].ToString().IndexOf(“.”)) + “‘ and ” + site.Fields["Title"].InternalName + “=’” + dtStore.Rows[i]["Title"] + “‘”);
How to remove duplicate rows from a datatable
If some rows of a some columns in the datatable are repititive and duplicate and you want to remove them, then see the example below:
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;
}
How to edit a row in datatable
strName = “Gitolekha”;
drResultSet[0]["Name"] = strName.ToString();
dtResults.AcceptChanges();
Datatables have lots of features and functionality, I have just covered some of them, more will be covered next time.
