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