Click here to Skip to main content
15,890,506 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear All,

I want to show % of processing while execute store procedure on sql server 2005:

'''App.config file
XML
<appSettings>
    <add key="CON" value="Data Source=SERVER;Initial Catalog=Reporting;Persist Security Info=True;User ID=user; password =123"/>
    <add key="AsynCON" value="Data Source=SERVER\MBWIN;Initial Catalog=Reporting;Persist Security Info=True;Asynchronous Processing=true;User ID=user; password =123"/>
  </appSettings>


''--------------------------------------

VB
Private Sub CmdTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdTest.Click
        Dim myWatch = New Stopwatch() '' use stopWath class to keep track of performent.
        myWatch.Start()
        timeCount = 0
        Dim strAsyn = ConfigurationManager.AppSettings("AsynCON")
        SQLAsynConnect = New SqlConnection(strAsyn)

        dtData = New DataTable
        'dataAdabter = New SqlDataAdapter
        command = New SqlCommand
        command.CommandType = CommandType.StoredProcedure
        command.Connection = SQLAsynConnect
        command.CommandText = "Ysp_LoanProvisionWeekly"
        command.CommandTimeout = 3000
        command.Parameters.Add("@BRANCHCODE", SqlDbType.NVarChar).Value = "KCM"
        SQLAsynConnect.Open()

        Dim myResult As IAsyncResult = command.BeginExecuteReader()
        While Not myResult.IsCompleted
            timeCount = timeCount + Double.Parse(Convert.ToDecimal(myWatch.Elapsed.TotalSeconds.ToString()))
            pgrPer3.Value = timeCount '' progress bar pgrPer3
            If timeCount = 100 Or timeCount > 100 Then
                timeCount = 0
            End If
        End While
        myReader = command.EndExecuteReader(myResult)
        myWatch.Stop()
        dtData = New DataTable()
        dtData.Load(myReader)
        pgrPer3.Value = 100
        dgData.DataSource = dtData
    End Sub  

''--------------------------------

The problem is that, the progress bar not show the right values of percentage during
execute store procedure.
Please help me..
thank.
Posted
Updated 27-Nov-11 23:21pm
v2

Try this:
VB
pgrPer3.Value = timeCount ' progress bar pgrPer3
Application.DoEvents()
 
Share this answer
 
v2
Comments
LTMKH 29-Nov-11 4:04am    
Thank for your answer, But my it really take a lot of time to execute or read data from my database. so it still not show the right percentage of execute time on my progress bar.
Try this one :
VB
ProgressBar1.Value = CInt((timeCount / 3000) * 100)

And try to ignore this section from your code if error not come :
VB
If timeCount = 100 Or timeCount > 100 Then
        timeCount = 0
End If
 
Share this answer
 
It depends how you've implemented your stored procedure. If it's one big SQL Statement, it's going to run in a single batch so you've got no way to raise any events from the server.

However, if your procedure is broken down into numerous SQL statements you could use the InfoMessage approach to raise events throughout your stored procedure execution

http://geekswithblogs.net/mrnat/archive/2004/09/20/11431.aspx[^]

This way, your application is notified as different parts of the procedure are executed & you can update your progress bar accordingly.
 
Share this answer
 
Comments
LTMKH 29-Nov-11 4:08am    
Dear Dylan Morley,
It's only one statement. but it take 17mn or some statement take 30mn.

Regard.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900