Click here to Skip to main content
12,452,000 members (25,792 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# 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 21:47pm
mrDivan1.2K
Updated 20-Dec-12 21: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 21-Dec-12 4:53am
   
thank you so much it worked
kanyogendra 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160826.1 | Last Updated 21 Dec 2012
Copyright © CodeProject, 1999-2016
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