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

Ess Kay

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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]);
}

Seven price

ASKER

tks