Not getting modified rows with sqlbulkcopy writetoserver method

Posted Date: 12 Jul 2012 Posted By:: suneel Member Level: Bronze Member Rank: 5210 Points: 5 Responses: 2

Hi all,

My scenario is to load excel file data into a sql database table. I am using bulkcopy to insert data. And when data in the excel file is modified I need to load the modified data only into the table.

A temp table is used to fetch fresh data from excel file, and comparing with destination table for any modifications, and should insert only the modified data. I am using C# script in my SSIS package.

Here is the method I am using:

SqlbulkCopy.WriteToServer(Temp, DataRowState.Modified);

But It is not inserting any fresh data into the destination table and destination table has only old data. Can I use a temp table here? Does this function works as per my requirement?

Responses

#680112 Author: Nathaniel Nellas Sumaya Member Level: Silver Member Rank: 1262 Date: 12/Jul/2012 Rating: Points: 4
Here's how I do it. protected void UploadToSQL(string connectionstring) { //declare variables //string name for sql grosscan table string sSQLTable = "sqltable"; //string name for excel file string sExcelFileName = "excel.xls"; //string name for worksheet string sWorkBook = "[worksheet$]"; //date of operation DateTime dtdate = DateTime.Now; //excel connectionString string sExcelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(sExcelFileName) + ";Extended Properties='Excel 8.0;HDR=YES'"; // sql connectionString //string sSqlConnectionString = WebConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString(); string sSqlConnectionString = WebConfigurationManager.ConnectionStrings[connectionstring].ToString(); //get the existing date of grosscan excel OleDbConnection oleDbCnn = new OleDbConnection(sExcelConnectionString); OleDbCommand oleDbCmd1 = new OleDbCommand(("SELECT * FROM " + sWorkBook), oleDbCnn); oleDbCnn.Open(); OleDbDataReader myDataReader = oleDbCmd1.ExecuteReader(); if (myDataReader.HasRows) { if (myDataReader.Read()) { dtdate = FillDataRecord(myDataReader); } myDataReader.Close(); } oleDbCnn.Close(); //Execute a query to erase any previous data from our destination table string sClearSQL = "DELETE FROM " + sSQLTable + " WHERE dtdate = '" + dtdate + "'"; SqlConnection sqlConn = new SqlConnection(sSqlConnectionString); SqlCommand sqlCmd = new SqlCommand(sClearSQL, sqlConn); //open the connection sqlConn.Open(); sqlCmd.ExecuteNonQuery(); //close the connection sqlConn.Close(); //Series of commands to bulk copy data from the excel file into our SQL table OleDbConnection oleDbConn = new OleDbConnection(sExcelConnectionString); OleDbCommand oleDbCmd = new OleDbCommand(("SELECT * FROM " + sWorkBook), oleDbConn); //open the oledb connection oleDbConn.Open(); OleDbDataReader dr = oleDbCmd.ExecuteReader(); SqlBulkCopy bulkCopy = new SqlBulkCopy(sSqlConnectionString); bulkCopy.DestinationTableName = sSQLTable; bulkCopy.WriteToServer(dr); oleDbConn.Close(); }

#680493 Author: suneel Member Level: Bronze Member Rank: 5210 Date: 16/Jul/2012 Rating: Points: 1
Hi Nathaniel Nellas Sumaya, Thank you for the reply. But Here I am looking to insert only the modified data when I am loading data from excel sheet to table for 2nd time. Looking for a way to insert only modified data to the table. Is it possible using Bulkcopy? Thanks in advance

Post Reply

This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post.

	Next : Bind the Gridview in VB.Net with SQL
	Previous : Converting file path to C# format
	Return to Discussion Forum
	Post New Message
	Category:

Related Messages

Follow us on Twitter: https://twitter.com/dotnetspider

Active Members
Today

Last 7 Days

baskar (298)
Asheej T K (173)
Ultimaterengan (167)

more...

Awards & Gifts

Email subscription