/***********************************Function to Save Item File into Sql Server 2000 using Bulk Copy Method***********************************/ public string SaveItem(DataTable dt) { string lblMessage = ""; MastersController objCont = new MastersController(); string sConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ToString(); int Status = 0; try { string strDelete = "Delete from ItemTemp"; objController.ExecuteQuery(strDelete); SqlConnection sqlconn = new SqlConnection(sConnectionString); sqlconn.Open(); SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlconn); bulkCopy.DestinationTableName = "ItemTemp"; bulkCopy.WriteToServer(dt); Status = objCont.SaveItemData(); if (Status == 1) { //Ask User if the duplicates are to be replaced //if Yes then delete Page.RegisterStartupScript("OnBlock", "<script language='javascript'>getStatus('item');</script>"); Session.Add("dtitem", dt); } else { string strItemException = "Declare @cnt int exec usp_AcrBulkDataInsert @cnt out"; objController.ExecuteQuery(strItemException); lblMessage = "File Item saved successfully"; } } catch(Exception ex) { string strMessage = ex.Message.ToString(); if (strMessage.Contains("Cannot insert duplicate key in object")) { lblMessage = "Cannot insert duplicate values"; } else { lblMessage = "Excel file is not in valid format"; } //"File Item could not be saved successfully"; } return lblMessage; }