ASP.NET - how to update sql record using excel

Asked By ravindra patil on 06-Sep-13 09:20 AM

i insert records from excel to sql using SqlBulkCopy. Now i want to update that records by uploading another excel. How can i do this ????

Reena Jain replied to ravindra patil on 10-Sep-13 02:35 AM

hi,

here is the code for you

string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("ElsImport.xls") + ";" + "Extended Properties=Excel 8.0;";using (OleDbConnection connection = new OleDbConnection(xConnStr))

{

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 =DataAccess_Perf.GetConnectionString() ;

// Bulk Copy to SQL Server

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))

{

bulkCopy.DestinationTableName = "user.ExcelTest";

bulkCopy.WriteToServer(dr);

}

Ref: - https://www.nullskull.com/q/10253316/want-to-transfer-excel-data-to-sql-server-2005-using-c-aspnet.aspx
https://www.nullskull.com/q/10297371/how-to-transfer-excel-data-to-a-table-in-sql-server-using-dts.aspx

hope this will help you

ravindra patil replied to Reena Jain on 10-Sep-13 04:10 AM

Thanx for reply, but this is code to insert data.I want to update the data using excel.
I solve this by taking data in DataTable.

ex.
DataTable dt = objdb.GetExcelData(xcel);
        if (dt.Rows.Count > 0)
        {
          for (int i = 0; i < dt.Rows.Count; i++)
          {
            Dictionary<string, string> param = new Dictionary<string, string>();
            param.Add("@prdt_code", dt.Rows[i]["prdt_code"].ToString());
            param.Add("@post_date", dt.Rows[i]["post_date"].ToString());
            param.Add("@pur_price", dt.Rows[i]["pur_price"].ToString());
            param.Add("@units", dt.Rows[i]["units"].ToString());
            sh.executeScalar("SP_upNAV", param);
          }
        }

I use store procedure to update data. Here it check each row and update data.