Bulk Copy Operations in ADO.NET 2.0

Sign in to leave a comment.

• M

  Maruthakumar19y agoPosted Oct 9, 2007, 1:37 AM

  i use this same piece of code., to transfer data from excel to sqlserver. My excel sheets come with multiple worksheets., I supply to the reader names of the sheets dynamically. what happens is the first worksheet gets its data into the sqlserver. but fails for the second and others. in whatever combination i try, only one worksheet get in.. if i comment writetoserver, i am able to see all worksheet names.. what to do.. ?? kindly help its very urgent
• S

  Sunil19y agoeditedPosted Jun 7, 2007, 5:00 AMEdited Jun 7, 2007, 5:04 AM

  I have used the following code: // Connection String to Excel Workbook string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\details.xls;Extended Properties=""Excel 8.0;HDR=YES;"""; // Create Connection to Excel Workbook using (OleDbConnection connection = new OleDbConnection(excelConnectionString)) { OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection); connection.Open(); // Create DbDataReader to Data Worksheet using (DbDataReader dr = command.ExecuteReader()) { // SQL Server Connection String string sqlConnectionString = "Data Source=ss1;Initial Catalog=sunil;Integrated Security=True"; // Bulk Copy to SQL Server using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString)) { bulkCopy.DestinationTableName = "Employee1"; bulkCopy.WriteToServer(dr); } } } but this doesnt give the desired output. Can u provide me other code for importing data field wise?
• S

  Sunil19y agoeditedPosted Jun 7, 2007, 5:00 AMEdited Jun 7, 2007, 5:05 AM

  I have used the following code: // Connection String to Excel Workbook string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\details.xls;Extended Properties=""Excel 8.0;HDR=YES;"""; // Create Connection to Excel Workbook using (OleDbConnection connection = new OleDbConnection(excelConnectionString)) { OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection); connection.Open(); // Create DbDataReader to Data Worksheet using (DbDataReader dr = command.ExecuteReader()) { // SQL Server Connection String string sqlConnectionString = "Data Source=ss1;Initial Catalog=sunil;Integrated Security=True"; // Bulk Copy to SQL Server using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString)) { bulkCopy.DestinationTableName = "Employee1"; bulkCopy.WriteToServer(dr); } } } but this doesnt give the desired output. Can u provide me other code for importing data field wise?