Add Auto Number (serial number) For an Existing Datatable
Posted: August 19, 2011 Filed under: ADO.NET Leave a commentIt will be a lot easier to do it either from front end if you use datacontrol or from back end in T-SQL query with a Row_number function.
If both options are not your choice, you can work with your datatable object directly through datatable.Load method.
Here is the code snippet to add autoincremental number column to an existing datatable:
private static DataTable AddAutoIncrementColumn() { DataColumn myDataColumn = new DataColumn(); myDataColumn.AllowDBNull = false; myDataColumn.AutoIncrement = true; myDataColumn.AutoIncrementSeed = 1; myDataColumn.AutoIncrementStep = 1; myDataColumn.ColumnName = "autoID"; myDataColumn.DataType = System.Type.GetType("System.Int32"); myDataColumn.Unique = true; //Create a new datatable DataTable mydt = new DataTable(); //Add this AutoIncrement Column to a new datatable mydt.Columns.Add(myDataColumn); return mydt; } protected void Page_Load(object sender, EventArgs e) { DataTable myDataTable = new DataTable(); myDataTable = AddAutoIncrementColumn(); //Read your existing datatable into a datatableReader DataTableReader reader = new DataTableReader(GetTable()); myDataTable.Load(reader); GridView1.DataSource = myDataTable; GridView1.DataBind(); } http://msdn.microsoft.com/en-us/library/7x8ccbsb.aspx Another way is to add a column and loop through the datatable to add the counter: (From this thread: http://forums.asp.net/t/1712346.aspx/1?add+serial+number+in+existing+datatable )
dt.Columns.Add(“S.No”, typeof(int));
for (int count = 0; count < dt.Rows.Count; count++)
{
dt.Rows[count][“S.No”] = count+1;
}