Link to home
Start Free TrialLog in
Avatar of Seven price
Seven priceFlag for United States of America

asked on

import datatable to sql

I have this datatable display in a gridview. I want to import it into a sql database after.
 protected void btnDetails_Click(object sender, EventArgs e)
        {

            string path = Server.MapPath("~/UploadFiles/" + fupExcel.FileName);
            if (!File.Exists(path.ToString().ToUpper()))
            {
                fupExcel.SaveAs(path);
            }

             OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
              path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
            conn.Open();
           
            OleDbCommand cmd = new OleDbCommand(); ;
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            DataSet ds = new DataSet();

            // selecting distict list of EmpNo 
                       cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [1108$]";
            //[Sheet1$] is Excel sheet name in your file
            oleda = new OleDbDataAdapter(cmd);
            oleda.Fill(ds, "dsSlno");
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                
                grvData.DataSource = ds.Tables[0].DefaultView;
                grvData.DataBind();
                          
            }
            else
            {
                grvData.DataSource = null;
                grvData.DataBind();
            }

        }
    }

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ess Kay
Ess Kay
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Seven price

ASKER

 conn.Open();

                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
                {
                    // Specify the destination table name.
                    bulkCopy.DestinationTableName = "dbo.Person";
                    // bulkCopy.ColumnMappings.Add("PORKY", "PORKY");
                    foreach (DataSet dc in ds.Tables)
                    {
                        // Because the number of the test Excel columns is not
                        // equal to the number of table columns, we need to map
                        // columns
                      bulkCopy.ColumnMappings.Add(dc.Tables[0], dc.Tables[0]);
                    }

                    // Write from the source to the destination. 
                    bulkCopy.WriteToServer(ds.Tables[0]);
                }
            }

Open in new window



ok I am using a dataset but having trouble in this area to import the data.

 foreach (DataSet dc in ds.Tables)
                    {
                        // Because the number of the test Excel columns is not
                        // equal to the number of table columns, we need to map
                        // columns
                      bulkCopy.ColumnMappings.Add(dc.Tables[0], dc.Tables[0]);
                    }
tks