SqlBulkCopy - Add rows along with additional values to database

Question

SqlBulkCopy - Add rows along with additional values to database

ado.net asp.net c# sqlbulkcopy sql-server

Question

I'm experimenting with a code snippet that uses SqlBulkCopy to add Excel data to a SQL database. The piece of code is shown below.

OleDbConnection connection=null;
        string FilePath="";
         try
            {
                if (FileUpload1.HasFile)
                {
                    FileUpload1.SaveAs(Server.MapPath("~/UploadedFolder/"+FileUpload1.FileName));
                    FilePath = Server.MapPath("~/UploadedFolder/"+FileUpload1.FileName);
         
                }

                string path = FilePath;
                // Connection String to Excel Workbook
                string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
                connection = new OleDbConnection();
                connection.ConnectionString = excelConnectionString;
                OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                connection.Open();
                // Create DbDataReader to Data Worksheet
                DbDataReader dr = command.ExecuteReader();

                // SQL Server Connection String
                string sqlConnectionString = @"Data Source=sample;Initial Catalog=ExcelImport;User ID=sample;Password=sample";

                // Bulk Copy to SQL Server 
                SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);
                bulkInsert.DestinationTableName = "Customer_Table";
                bulkInsert.WriteToServer(dr);
           
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
            finally
            {
                connection.Close();
                Array.ForEach(Directory.GetFiles(Server.MapPath("~/UploadedFolder/")), File.Delete);
            }

This updates a table in a SQL Server database using data from an Excel file. However, I need to include my own information, such as an automatically produced student id, in addition to the ones from the excel file.

My concern is how to add additional data (such studentid, batchcode, etc.) in addition to those that are read from Excel. And each row of the Excel data will also have these numbers.

Example:-

The columns in Excel are as follows:

CustomerID,City,Country,PostalCode

As of right now, I must add values to SQL Server by including some new fields.

StudentID,CustomerID,BatchCode,City,Country,Email,PostalCode

How do I do it?

kindly assist

user2363957

Related Questions

View more on Stack Overflow

JesÃºs LÃ³pez · Accepted Answer · 11/1/2015 9:24:32 AM

The following are some options:

the data from Excel into a data table,
The data table should now include the additional columns.
new values for the columns
the data table into SQL Server using sqlbulkcopy.

Possibly like this:

DbDataReader dr = command.ExecuteReader();

DataTable table = new DataTable("Customers");
table.Load(dr);
table.Columns.Add("StudentId", typeof(int));
table.Columns.Add("BatchCode", typeof(string));
table.Columns.Add("Email", typeof(string));

foreach (DataRow row in table.Rows)
{
    row["StudentId"] = GetStudentId(row);
    row["BatchCode"] = GetBatchCode(row);
    row["Email"] = GetEmail(row);
}


// SQL Server Connection String
string sqlConnectionString = @"Data Source=sample;Initial Catalog=ExcelImport;User ID=sample;Password=sample";

// Bulk Copy to SQL Server 
SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);
bulkInsert.DestinationTableName = "Customer_Table";
bulkInsert.WriteToServer(table);

SqlBulkCopy - Add rows along with additional values to database

Question

Fastest Entity Framework Extensions

Accepted Answer

Related Questions

Fastest Entity Framework Extensions

Performance

Expression Evaluator