Asp.net: Uploading excel file using blkCopy and adding user defined column

Question

At the moment my program is uploading an excel file through the browser to the server. That excel file is then being read on the server and its value are being placed in a database. The problem I am looking to solve is I want to add one more column, but I want it to be user defined from the browser and I dont see any way of doing it because I'm using bulkCopy.

What I want to do as allow the user to add text in a textbox such as MAR12 or APR12 and then upload the excel file and that to be added in every column that the excel file fills. Can anyone help me out with a solution. Thanks

string tableRows= "SELECT [id],[DateEntered],[User_ID] FROM [EPG$]";          

string EPGTableName = "tableName";               table in the database

This is where the excel file gets copied to the database:

        using (OleDbConnection connection = new OleDbConnection(connStr))
        {
            OleDbCommand command = new OleDbCommand(tableRows, connection);

            connection.Open();
            // Create DbDataReader to Data Worksheet
            using (DbDataReader dr = command.ExecuteReader())
            {
                // SQL Server Connection String
                string sqlConnectionString = "SERVER=<server>;UID=schafc;Trusted_Connection=Yes;DATABASE=<database>;";

                // Bulk Copy to SQL Server
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                {
                    bulkCopy.DestinationTableName = tableName;
                    bulkCopy.WriteToServer(dr);
                }
            }
        }

score 1 · Answer 1 · answered May 16 '12 at 10:21

I am not sure how you would do this with a DataReader, but it can be done using a DataAdapter and DataTable. Filling the table, adding a column (with a default value) then passing the table to the bulk copy rather than a datareader.

using (OleDbDataAdapter adapter = new OleDbDataAdapter(tableRows, connStr))
{
    DataTable table = new DataTable();
    adapter.Fill(table);
    table.Columns.Add(new DataColumn("@ColumnName", typeof(string), string.Format("'{0}'", textboxvalue)));
    // SQL Server Connection String
    string sqlConnectionString = "SERVER=<server>;UID=schafc;Trusted_Connection=Yes;DATABASE=<database>;";

    // Bulk Copy to SQL Server
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
    {
        bulkCopy.DestinationTableName = tableName;
        bulkCopy.WriteToServer(table);
    }
}

Asp.net: Uploading excel file using blkCopy and adding user defined column

1 Answers1