SqlBulkCopy , ASP.NET web forms, Stuck without error

Question

Hello,

I have three excel files, uploading data from asp.net webforms and inserting into database.

I am reading data from excel files and then from datatable inserting to database using SqlBulkCopy, sometime it works perfectly and then next time it stuck, then I need to restart IIS and then it works again, creating too much problem, not sure what to do .

Please suggest.

Answer 1

Hi,

It is .xlsx on local environment everything is working fine, on production first time all three files uploaded successfully and then it is creating issue.

here is my code , this is for 1 file, same I have 3 files with different columns, and rows more than 1000.

 Protected Sub UploadButton_Click(sender As Object, e As EventArgs) Handles UploadButton.Click


            Dim dateAndTime As Date
            dateAndTime = Now
            '
            Dim dt As DataTable
            Dim rowcount As Integer = 0
            Dim fname As String
            lblmessage6.Text = ""


            If (uploadagenda.HasFile) Then

              

                lblmessage6.Text = ""
                Dim strdt As String = Convert.ToString(Date.Now.ToString("ddMMMyyyy_HHmmss"))
                fname = strdt + "_" + uploadagenda.FileName
                Dim userformid As String = Date.Now.ToString("ddMMyymm")
                userformid = Convert.ToInt32(userformid)
                lblUploadFileName.Text = fname
                lblUserFormId.Text = userformid
                Dim path As String = String.Concat((Server.MapPath("~/Forms/SalesTax/" + (lblUploadFileName.Text))))
                lblFilePath.Text = path
                Try
                    uploadagenda.PostedFile.SaveAs(path)
                    OleDbcon = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;")
                    OleDbcon.Open()
                    cmd = New OleDbCommand("SELECT * FROM [Sheet1$]", OleDbcon)
                    objAdapter1 = New OleDbDataAdapter(cmd)

                    dt = New DataTable

                    objAdapter1.Fill(dt)
                    dt.Columns.Add("BatchID", GetType(String))
                    totalrecords = dt.Rows.Count

                   

                    If lblcurrectBatchIDtxt.Text = "" Then

                        objsales.SalesTaxDashboard_SAVE_BatchDetails(Session("ThisEmployee"))

                        Dim ds As DataTable
                        ds = objsales.GET_BatchDetails()
                        lblcurrectBatchIDtxt.Text = ds.Rows(0).Item("BatchID").ToString
                        hiddBatchID6.Value = lblcurrectBatchIDtxt.Text

                        Bindbatch()

                    End If
                    For Each row As DataRow In dt.Rows
                        row("BatchID") = hiddBatchID6.Value
                    Next

                    If totalrecords > 0 Then

                        Dim SQLconn As New SqlConnection()
                        Dim ConnString As String = "Data Source=XXXXXX;Initial Catalog=BBBB; User Id=xxxx; Password=xxxx; max pool size=7500;"

                        SQLconn.ConnectionString = ConnString



                        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
                            bulkCopy.DestinationTableName = "TBL_SALES_TAX_SPR_FBR"

                            Try
                                bulkCopy.ColumnMappings.Add("BatchID", "BatchID")
                                bulkCopy.ColumnMappings.Add("Sr_No", "Sr_No")
                                bulkCopy.ColumnMappings.Add("BU_Control", "BU_Control")
                                bulkCopy.ColumnMappings.Add("Description", "Description")
                                bulkCopy.ColumnMappings.Add("Qty", "Qty")
                                bulkCopy.ColumnMappings.Add("Supplier_Name", "Supplier_Name")
                                bulkCopy.ColumnMappings.Add("Supplier_Address", "Supplier_Address")
                                bulkCopy.ColumnMappings.Add("City", "City")
                                bulkCopy.ColumnMappings.Add("Province", "Province")
                                bulkCopy.ColumnMappings.Add("NationalTaxNo", "NationalTaxNo")
                                bulkCopy.ColumnMappings.Add("Supplier_Reg_No", "Supplier_Reg_No")
                                bulkCopy.ColumnMappings.Add("Sales_Tax_Inv_No", "Sales_Tax_Inv_No")
                                bulkCopy.ColumnMappings.Add("S_T_Inv_Date2", "S_T_Inv_Date2")
                                bulkCopy.ColumnMappings.Add("S_T_Inv_Date", "S_T_Inv_Date")
                                bulkCopy.ColumnMappings.Add("Rate_of_Tax", "Rate_of_Tax")
                                bulkCopy.ColumnMappings.Add("Excl_Value", "Excl_Value")
                                bulkCopy.ColumnMappings.Add("Sales_Tax", "Sales_Tax")
                                bulkCopy.ColumnMappings.Add("WHST", "WHST")
                                bulkCopy.ColumnMappings.Add("Input type", "Input_type")
                                bulkCopy.ColumnMappings.Add("Purchase type", "Purchase_type")
                                bulkCopy.ColumnMappings.Add("CC", "CC")
                                bulkCopy.ColumnMappings.Add("BU", "BU")
                                bulkCopy.ColumnMappings.Add("Process_date", "Process_date")
                                bulkCopy.ColumnMappings.Add("Covering_sheet", "Covering_sheet")
                                bulkCopy.ColumnMappings.Add("Excl_Value_Un_Reg", "Excl_Value_Un_Reg")
                                bulkCopy.ColumnMappings.Add("Bill_Amount_Un_Reg", "Bill_Amount_Un_Reg")
                                bulkCopy.ColumnMappings.Add("Excl_Value_Time_bared", "Excl_Value_Time_bared")
                                bulkCopy.ColumnMappings.Add("Sales_Tax_Time_bared", "Sales_Tax_Time_bared")
                                bulkCopy.ColumnMappings.Add("Ref_No", "Ref_No")

                                bulkCopy.BulkCopyTimeout = 0
                                bulkCopy.BatchSize = 100
                                SQLconn.Open()

                                bulkCopy.WriteToServer(dt)

                                '  objsales.UPDATE_SalesTaxDashboard()
                                lblmessage6.Text = CStr(totalrecords) + " Rows affected, Data has been uploaded."
                                lblmessage6.ForeColor = Color.Green


                            Catch ex As Exception
                                MsgBox(ex.ToString)
                                lblmessage6.Text = ex.Message + "Error when inserting record."
                                lblmessage6.ForeColor = Color.Red
                            Finally
                                '  bulkCopy.Close()
                                OleDbcon.Close()
                                SQLconn.Close()
                            End Try


                        End Using

                     
                    Else
                        lblmessage6.ForeColor = Color.Red
                        lblmessage6.Text = "no data in excel file"
                        lblmessage6.ForeColor = Color.Red
                    End If
                Catch ex As Exception
                    lblmessage6.Text = ex.Message.ToString()
                    lblmessage6.ForeColor = Color.Red
                End Try
            Else
                lblmessage6.ForeColor = Color.Red
                lblmessage6.Text = "Please select the File"
            End If
            'hiddBatchID.Value = BatchID.ToString()
            Bindbatch()
        End Sub

Answer 2

Hi @XuDong Peng-MSFT

 bulkCopy.ColumnMappings.Add("FED_Charged", "FED_Charged")
                                bulkCopy.ColumnMappings.Add("Type_Desc", "Type_Desc")
                                bulkCopy.ColumnMappings.Add("Return_Status_Desc", "Return_Status_Desc")
                                bulkCopy.ColumnMappings.Add("Fixed_RetailValue", "Fixed_RetailValue")

                                bulkCopy.BulkCopyTimeout = 0
                                bulkCopy.BatchSize = 100
                                SQLconn.Open()
                                bulkCopy.WriteToServer(dt)

                                ' objsales.UPDATE_SalesTaxDashboard()
                                lblmessage6.Text = CStr(totalrecords) + " Rows affected, Data has been uploaded."
                                lblmessage6.ForeColor = Color.Green


                            Catch ex As Exception
                                MsgBox(ex.ToString)
                                lblmessage6.Text = ex.Message + "Error when inserting record."
                                lblmessage6.ForeColor = Color.Red
                            Finally
                                '   bulkCopy.Close()
                                OleDbcon.Close()
                                SQLconn.Close()
                            End Try

Answer 3

One thing I noticed , that after restarting IIS it is working fine, and then again suck, again restart IIS and start working.

Answer 4

I think you first need to determine where the operation gets stuck. Augment your code with writing to a log file, so that you can see how far it comes. There are a couple of candidates. I do have a feeling what is the issue, but I don't want to jump the gun. I think it is better get more information first.