C# - Updating an SQL Table from a file upload in ASP.Net

Question

My scenario is that I have an Excel Spreadsheet that I want to upload to an SQL Database and UPDATE the information based upon the primary key value (which is locked and hidden within the Excel Spreadsheet). I have the below code that can be used to insert new entries into the database, but im not sure how to adapt it to UPDATE:

string path = string.Concat((Server.MapPath("~/temp/" + FileUpload1.FileName)));
                FileUpload1.PostedFile.SaveAs(path);
            OleDbConnection OleDbcon =
                new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path +
                                    ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";");
            OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]",
                OleDbcon);
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);

            OleDbcon.Open();

            DbDataReader dr = cmd.ExecuteReader();
            string con_str =
                @"Data Source=****************mydatasource""""""""""";


            SqlBulkCopy bulkInsert = new SqlBulkCopy(con_str);
            bulkInsert.DestinationTableName = "TableName";
            bulkInsert.WriteToServer(dr);

            OleDbcon.Close();

Example Data:

ID  StartDate   EndDate OrderNumber
1   01/02/2015  NULL    100
2   02/02/2015  NULL    100
3   03/02/2015  NULL    101
4   04/02/2015  NULL    102
5   05/02/2015  NULL    103

When the data in the database is inserted, the EndDate is Null.
The End Date is added into the excel sheet, and then I want the C# ASP.Net Application to UPDATE this information and update the SQL Table, not insert it as a new row.

End Data

ID  StartDate   EndDate OrderNumber
1   01/02/2015  02/02/2015  100
2   02/02/2015  03/02/2015  100
3   03/02/2015  04/02/2015  101
4   04/02/2015  05/02/2015  102
5   05/02/2015  06/02/2015  103

thanks for any help you can give me

Rhys Jones · Accepted Answer · 2015-02-06 11:06:34Z

1

The code you already have provides an efficient way of loading a file into SQL Server but doesn't provide any flexibility. Your options are to re-write your C# and do the INSERT/UPDATE one row at a time, or to change your approach slightly and bulkinsert the new data into a working table then call a stored procedure to do the actual INSERT/UPDATE from the working table to the real table - the latter would be my recommendation.

answered Feb 6, 2015 at 11:06

Rhys Jones

5,5361 gold badge25 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Crezzer7 Over a year ago

cheers, such a simple solution that will take minutes to do, thanks

TonyS · Accepted Answer · 2015-02-06 11:04:08Z

1

I believe you will need to iterate over the data reader and test for the date value and when found issue a single update statement, e.g.:

 while (reader.Read())
            {
               //test if row exists
               //then Update
               //else Insert
            }

answered Feb 6, 2015 at 11:04

TonyS

5568 silver badges11 bronze badges

Collectives™ on Stack Overflow

C# - Updating an SQL Table from a file upload in ASP.Net

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related