Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Set autonumber for DataTable after it already has data

Tags:

c#

datatable

I have the following code to add an autonumber column to a DataTable:

public void AddAutoIncrementColumn(DataTable dt)
{
   DataColumn column = new DataColumn();
   column.DataType = System.Type.GetType("System.Int32");
   column.AutoIncrement = true;
   column.AutoIncrementSeed = 0;
   column.AutoIncrementStep = 1;
   dt.Columns.Add(column);
}

However, this value will be blank for all rows that are already in the table; it seems that the AutoIncrement is only triggered for new rows that are added after this column has been added. Is there a way to set autonumber values for the rows that already exist?

like image 974
sigil Avatar asked Dec 15 '22 11:12

sigil


2 Answers

I don't think that it's possible to trigger the AutoIncrement functionality when the rows are already in the table. But you could update the table manually easily:

public void AddAutoIncrementColumn(DataTable dt)
{
    DataColumn column = new DataColumn();
    column.DataType = System.Type.GetType("System.Int32");
    column.AutoIncrement = true;
    column.AutoIncrementSeed = 0;
    column.AutoIncrementStep = 1;
    dt.Columns.Add(column);
    int index = -1;
    foreach (DataRow row in dt.Rows)
    {
        row.SetField(column, ++index);
    }
}
like image 58
Tim Schmelter Avatar answered Dec 30 '22 11:12

Tim Schmelter


I made a change to @Programnik solution.

DataTable dt = LoadDataTable();
using (DbDataReader dr = dt.CreateDataReader())
{
    //Get Original Datatable structure
    dt = dt.Clone();

    // Add Auto Increment Column called ID
    dt.Columns.Add(new DataColumn("ID")
    {
        AutoIncrement = true,
        AllowDBNull = false,
        AutoIncrementSeed = 1,
        AutoIncrementStep = 1,
        DataType = typeof(System.Int32),
        Unique = true
    });

    // Change Auto Increment Column Ordinal Position to 0 (ie First Column)
    dt.Columns["TabID"].SetOrdinal(0);

    // Re-load original Data
    dt.Load(dr);
}
like image 37
Mark Kram Avatar answered Dec 30 '22 12:12

Mark Kram