I have created a project ExcelChart.aspx where in the data from an excel file Sample.xls has been copied to a dbo table - ExcelTable.dbo. The details of the table is been displayed on the webpage via a gridview control.
Also the same dbo table is been used for a graphical representation via Chart.
I have used the below code to copy the data from Excel file to dbo table and to display in gridview control:
String strConnection = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ExcelChart.mdf;Integrated Security=True;User Instance=True";
string path = FileUploadexcel.PostedFile.FileName;
string fileBasePath = Server.MapPath("~/Excel/");
string fileName = Path.GetFileName(this.FileUploadexcel.FileName);
string fullFilePath = fileBasePath + fileName;
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullFilePath + ";Extended Properties=Excel 12.0;Persist Security Info=False";
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
OleDbCommand cmd = new OleDbCommand("Select [Carats],[Value],[Month] from [Sheet1$]", excelConnection);
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.DestinationTableName = "ExcelTable";
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
GridViewexcel.DataSource = ds.Tables;
The above code works fine for the first time execution. However on page load or on refresh the data is again been copied from excel file to dbo table. But as the Month column in dbo table is set to primary key the line sqlBulk.WriteToServer(dReader); gives me error for copying duplicate values.
I have tried a million other codes to set the same but failed to do so.
Now i was thinking if it is possible to delete records from dbo table on page load and let the details from excel file be copied again in the dbo table.
Please help. If the above concept is possible i will be really glad to gain some help.
Thanks a lot.
What I have tried:
sqlBulk.WriteToServer(dReader); gives error of duplicate records cannot be copied in the dbo table.