Wednesday, 25 November 2015

Datatable

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