Hello,

I have simple ASP.net web form. With a simple click on a button i need to call a the Multithread process in order to execute my querry in SQL Server DB. I know that my querry willl take up to 45 min to be executed. While the process is running i need to display some status information like: Starting time, Time elapsed, % of completed...

So after running the process i got the following error message: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

So i have 2 problems to fix:

Avoid getting timeout while processing/calling the stored procedure. I tried to add cmd.CommandTimeout = 1000 but it didn't work out!
Display correctly Time elapsed and % Completed while processing.
I used the code on this link to build my code: http://www.sitepoint.com/print/threading-asp-net/

Thank for your help


Paul
<FAKE SIGNATURE>


Here's my code in the main form:

Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.IO

Imports System.Threading

Partial Public Class Inventory
    Inherits System.Web.UI.Page

    Private v_thread As Thread

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

        If Not Page.IsPostBack Then

            Me.lblErrorMessage.Text = ""
            Me.lblErrorMessage.Visible = False
            Me.pnlErrorMessage.Visible = False

            Me.rdResults.Visible = False

        End If

        If Session("ProcessStart") = True Then
            Response.Redirect("archive_style_result.aspx")
        End If

        ' --------------------------------------------------------------------------------------------
        ' Add this new section in order to display the Status of the archiving while proceeding
        ' --------------------------------------------------------------------------------------------
        SyncLock Session.SyncRoot
            ' Initialize pour session variables to hold our results 
            Session("ProcessStart") = False
            Session("Complete") = False
            Session("ProcessStartAt") = ""
            Session("ProcessEndAt") = ""
            Session("TotalTime") = ""
            Session("Status") = ""
            Session("Phrase") = ""
        End SyncLock

    End Sub


    Protected Sub BtnRebuild_Click(ByVal sender As Object, ByVal e As EventArgs)
        If Page.IsPostBack Then

            lblErrorMessage.Text = ""
            lblErrorMessage.Visible = False
            pnlErrorMessage.Visible = False

            Try

                Dim v_thread As New System.Threading.Thread(AddressOf Me.RebuildInventory)
                'v_thread = New Thread(AddressOf Me.ArchiveStyle1)

                v_thread.IsBackground = True
                v_thread.Priority = ThreadPriority.Lowest
                v_thread.Start()

                'now redirect to the results page 
                Response.Redirect("archive_style_result.aspx")

                lblErrorMessage.Text = "Rebuild inventory is in process... This may take some times. <BR>Please verify later on your Global Inventory. "
                lblErrorMessage.Visible = True
                pnlErrorMessage.Visible = True

            Catch ex As Exception
                lblErrorMessage.Text = ex.Message.ToString
                lblErrorMessage.Visible = True
                pnlErrorMessage.Visible = True

            End Try

        End If

    End Sub

    Sub RebuildInventory()

        Try

            Dim v_string As String = ""
            Dim i As Integer = 0

            Dim v_start_time_ticks As Long = 0
            Dim v_string_start_time As String = ""

            Dim v_total_sleep_time As Double = 0.0
            Dim v_total_thread_life As Integer = 2

            Dim v_length As Integer = 100000

            ' log our start time, in ticks, and in Long Date format 
            v_start_time_ticks = DateTime.Now.Ticks
            v_string_start_time = DateTime.Now

            ' Get phrase 
            v_string = "Archiving style in process ..." 

            'convert users time from seconds to milliseconds 
            v_total_sleep_time = 1000.0
            v_total_sleep_time = v_total_sleep_time * CInt(v_total_thread_life)
            v_total_sleep_time = (v_total_sleep_time / v_length)


            ' ------------------------------------------------------------------------
            Dim v_connection As SqlConnection = Nothing
            Dim v_sql_connection_string As String = ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString
            v_connection = New SqlConnection(v_sql_connection_string)

            Try

                SyncLock Session.SyncRoot
                    Session("ProcessStart") = True
                    Session("ProcessStartAt") = v_string_start_time
                    Session("Phrase") = v_string
                End SyncLock

                ' Open Connection
                v_connection.Open()

                Dim cmd As SqlCommand

                'Insert/Update data in database 
                cmd = New SqlCommand("pr_update_warehouse_status_populate_all", v_connection)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandTimeout = 1000

                With cmd.Parameters.AddWithValue("@p_transaction_code", "-1")
                    .Size = 20
                    .SqlDbType = SqlDbType.NVarChar
                    .Direction = ParameterDirection.Input
                End With

                With cmd.Parameters.AddWithValue("@p_error_no", "")
                    .Size = 20
                    .SqlDbType = SqlDbType.NVarChar
                    .Direction = ParameterDirection.Output
                End With

                With cmd.Parameters.AddWithValue("@p_error_txt", "")
                    .Size = 1000
                    .SqlDbType = SqlDbType.NVarChar
                    .Direction = ParameterDirection.Output
                End With

                ' This method will put our thread to sleep for the specified number of milliseconds. 
                ' Without the sleep, this method would execute too fast to see the thread working. 
                Thread.Sleep(v_total_sleep_time)

                cmd.ExecuteNonQuery()

            Catch ex As Exception
                Throw ex

            Finally
                If Not v_connection Is Nothing Then
                    v_connection.Close()
                    v_connection = Nothing
                End If
            End Try


            'we use synclock to block any other thread from accessing 
            'session variables while we are changing their values. 
            SyncLock Session.SyncRoot
                Session("Status") = Format((i / (v_length - 1)) * 100, "#0.00") & "% complete."
                Session("TotalTime") = Format((DateTime.Now.Ticks - v_start_time_ticks) / 10000000, "#0.00") & " sec."
                Session("Phrase") = v_string
            End SyncLock

            ' Our method is complete, so set the Session variables accordingly 
            SyncLock Session.SyncRoot
                Session("ProcessStartAt") = v_string_start_time
                Session("ProcessEndAt") = DateTime.Now
                Session("Status") = "100% completed"
                Session("Complete") = True
                Session("ProcessStart") = False ' End of the process
                Session("Phrase") = "Archiving style is completed!"
            End SyncLock

            'cmd.ResetCommandTimeout()
        Catch ex As Exception
            Throw ex

        End Try


    End Sub

End Class

Here's my code in the result form:


Partial Class _ArchiveStyleResult
    Inherits System.Web.UI.Page

    Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Try
            'Put user code to initialize the page here 
            'check the value of Session("Completed"), if it is True, stop writing 

            Me.lblErrorMessage.Text = ""
            Me.lblErrorMessage.Visible = False
            Me.pnlErrorMessage.Visible = False

            If Session("Complete") <> True Then

                ' Make sure session variables are enabled, if not warn user 
                If Session("Complete") <> False Then

                    ' Error with session variable, Session("Complete") is not True or False 
                    lblComplete.Text = "Error with Session('Complete')"

                Else

                    ' Set page to auto refresh page every 2 seconds, until thread is done 
                    Response.Write("<META HTTP-EQUIV=Refresh CONTENT='10; URL='>")

                    SyncLock Session.SyncRoot
                        lblProcessStartAt.Text = Session("ProcessStartAt")
                        lblProcessEndAt.Text = Session("ProcessEndAt")
                        lblStatus.Text = Session("Status")
                        lblTotalTime.Text = Session("TotalTime")
                        lblPhrase.Text = Session("Phrase")
                        lblComplete.Text = Session("Complete")
                    End SyncLock

                End If


            Else

                ' Thread is complete, stop writing refresh tag, and display results 
                SyncLock Session.SyncRoot
                    lblProcessStartAt.Text = Session("ProcessStartAt")
                    lblProcessEndAt.Text = Session("ProcessEndAt")
                    lblStatus.Text = Session("Status")
                    lblTotalTime.Text = Session("TotalTime")
                    lblPhrase.Text = Session("Phrase")
                    lblComplete.Text = Session("Complete")
                End SyncLock

            End If

        Catch ex As Exception
            lblErrorMessage.Text = ex.Message.ToString
            lblErrorMessage.Visible = True
            pnlErrorMessage.Visible = True

        End Try


    End Sub


End Class

You mentioned that the SQL query will take 45 minutes to execute. But you have mentioned that CommandTimeout = 1000. The CommandTimeout is the time in seconds to wait for the command to execute. The default is 30 seconds.

Hence you need to increase the timeout to more than (45*60 = )2700. Also increase the Session timeout to more than 45 minutes in web.config. The default is 20 minutes.

Also it is not recommended to run big process under IIS. Becuase if the process exceeds the memory allocated IIS for processing, the asp.net worker process will be broken and the session will restart.

Hi Ramesh,

Thank you for your solution. It was very helpful!

Now it still have one problem to fix/solve:

How to display correctly "Time elapsed" and "% Completed" while processing the same stored procedure ?
Thank you


Paul
<FAKE SIGNATURE>

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.