Click here to Skip to main content
Rate this: bad
good
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;'");
                    break;
                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;'");  
                    break;
            }                             
            //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;
            MyConnection.Close();
            
            //Check datatable have records
            if (dt.Rows.Count > 0)
            {
                GridView2.DataSource = dt;
                GridView2.DataBind();
            }
            //Delete temporary Excel file from the Server path
            if (System.IO.File.Exists(path))
            {
                System.IO.File.Delete(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))
                    dt.Rows.RemoveAt(i);
            }
            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
v2

1 solution

Rate this: bad
good
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 
dt.Columns.Add(IdentityCol);

Step 3) Update Exisitng DataColumn Value

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

Now Identity Column will automatically Incremented, whenever new row gets added. 
 
Thanks! 
Yogendra Dubey
  Permalink  
v3
Comments
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