Click here to Skip to main content
Rate this: bad
Please Sign up or sign in to vote.
See more: ASP.NET C#4.0
Hi everyone i want to know if it is possible to add a identity column to an in memory datatable to make editing of the in memroy data easier I import an spreadsheet and i want to add a identity column to the data which increments with 1 to the data so there will be an id number next to each row here is my code it works hundred percent to import i just dont know how to add an id column

void ExportToGrid(String path)
            OleDbConnection MyConnection = null;
            DataSet DtSet = null;
            OleDbDataAdapter MyCommand = null;
            String NewString = filename.Remove(filename.Length - 2, 2);
            string last = NewString[NewString.Length - 1].ToString();
            switch (last)
                case "x":
                    //Connection for MS Excel 2003 .xls format
                    MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties='Excel 8.0;HDR=No;'");
                case "l":
                    //Connection for .xslx 2007 format
                    MyConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + path + "';Extended Properties='Excel 12.0;HDR=No;'");  
            //Select your Excel file
            MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
            //Worksheet sheet = new Worksheet("Sheet1");
            //int LastRow = sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            DtSet = new System.Data.DataSet();
            //Bind all excel data in to data set
            MyCommand.Fill(DtSet, "[Sheet1$]");
            System.Data.DataTable dt = DtSet.Tables[0];
            ViewState["SelectedRecords"] = dt;
            //Check datatable have records
            if (dt.Rows.Count > 0)
                GridView2.DataSource = dt;
            //Delete temporary Excel file from the Server path
            if (System.IO.File.Exists(path))
            string expression1 = "F4 = ''";
            String valuesarr = String.Empty;
            for (int i = 0; i < dt.Rows.Count - 1; i++)
                List<object> lst = dt.Rows[i].ItemArray.ToList();
                foreach (Object s in lst)
                    valuesarr += s.ToString();                   
                if (String.IsNullOrEmpty(valuesarr))
            DataRow[] foundRows = dt.Select(expression1);
            int CellCount = foundRows.Length;
            string expression2 = "F2 IS NULL";
            DataRow[] foundRows2 = dt.Select(expression2);
            int nameCount = foundRows2.Length;
            string expression3 = "F3 IS NULL";
            DataRow[] foundRows3 = dt.Select(expression3);
            int surnameCount = foundRows3.Length;
            tableImport.Visible = true;
Posted 20-Dec-12 22:47pm
Edited 20-Dec-12 22:52pm

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Hi Divan,

you can create Identity column in Existing Datatable in following Steps :

Step 1) Create an Identity Column

DataColumn IdentityCol = new DataColumn("ID");
IdentityCol.AutoIncrement = true;
IdentityCol.AutoIncrementSeed = 1;
IdentityCol.AutoIncrementStep = 1;

Step 2) Add Identity Column to Exisiting Datatable
//Assuming dt as Datatable 

Step 3) Update Exisitng DataColumn Value

for(i=1; i<=dt.Rows.Count; i++)
dt.Rows[i-1]["ID"] = i;

Now Identity Column will automatically Incremented, whenever new row gets added. 
Yogendra Dubey
mrDivan at 21-Dec-12 4:53am
thank you so much it worked
kanyogendra at 21-Dec-12 8:12am
Please rate my Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sascha Lefévre 365
1 Sergey Alexandrovich Kryukov 325
2 Maciej Los 240
3 Abhinav S 184
4 Peter Leow 120
0 Sergey Alexandrovich Kryukov 6,773
1 OriginalGriff 6,311
2 Maciej Los 2,692
3 Peter Leow 2,654
4 Abhinav S 2,562

Advertise | Privacy | Mobile
Web02 | 2.8.150414.1 | Last Updated 21 Dec 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100