v
DataTable - Adding, Modifying, Deleting, Filtering, Sorting rows
DataTable is a central object in the ADO.NET
library. If you are working with ADO.NET - accessing data from database,
you cannot escape from DataTable.
Other objects that use DataTable are DataSet and
DataView. In this tutorials, I will explain how to work with DataTable.
I have tried to cover most of the frequently used
activity in the DataTable, I hope you will like it.
Ø
Creating a DataTable
To create a DataTable, you need to use System.Data namespace
// instantiate DataTable
DataTable dTable
= new DataTable("Dynamically_Generated");
// 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));
Ø 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.
See the code
below, here I am setting the first column "AutoID" as autoincrement
field.
Whenever a
new row will be added its value will automatically increase by 1 as I am
specified AutoIncrementSeed value as 1.
// 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;
Ø Using
DataTable.Select() method
DataRow[]
rows = dTable.Select("", "AutoID DESC");
Ø Using
DataView
// Sorting DataTable
DataView dataView
= new DataView(dTable);
dataView.Sort = "
AutoID DESC, Name DESC";
foreach (DataRowView view in dataView)
{
Response.Write(view["Address"].ToString());
}
Ø
Working with Aggregate functions
DataRow[]
rows22 = dTable.Select("AutoID = max(AutoID)");
string str = "MaxAutoID:
" +
rows22[0]["AutoID"].ToString();
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)", "");
Ø Filtering data
from DataTable
DataRow[]
rows = dTable.Select(" AutoID > 5");
DataRow[] rows1 = dTable.Select(" AutoID > 5", "AuotID ASC");
