Link to home
Create AccountLog in
Avatar of helena80
helena80

asked on

Import excel sheet on web client using sqlbulkcopy

I'm using sqlbulkcopy to get data from an excel sheet and save it to  a table en sqlserver2005. My code works fine when the excel file is in my aplication server, but it doesn't work when it is in the web client. I use an input type="file" to get the excel's file path from the client's machine but sqlbulcopy searchs the file by default in the aplication server.
Is there a way to get the file directly from the client machine? If it is not possible with sqlbulkcopy I would really apretiate other suggestions to do it.
 
This is the code I'm using:
 try
            {
               
                string path = fileExcel.Value;
               
                string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=" + '"' + "Excel 8.0;HDR=YES;" + '"';                                
                using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
                {
                    OleDbCommand command = new OleDbCommand("Select * FROM [Hoja1$]", connection);
                    connection.Open();
                using (DbDataReader dr = command.ExecuteReader())
                    {
                      string sqlConnectionString = "Data Source=ipserver; Initial Catalog=bd;user id=user; password=password";

                      using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                        {
                       bulkCopy.DestinationTableName = "NombreTabla";
                           
                            try
                            {
                                bulkCopy.WriteToServer(dr);
                   
                            }
                            catch (Exception ex)
                            {
                                lblError.Text = ex.ToString();    
                            }
                            finally
                            {
                                dr.Close();
                               
                            }                          
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                lblError.Text = ex.ToString();          
            }          
ASKER CERTIFIED SOLUTION
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of helena80
helena80

ASKER

It worked perfectly. Thanks a lot!
Great solution, it works all right now.