Solved

How to wrap important database update in .net

Posted on 2012-03-14
12
186 Views
Last Modified: 2012-04-02
Hi all,

I have a .net (vb) program (windows service) that gets to a stage where it needs to update a sql server by calling a stored procedure.  This works fine but occasionally the sql server could be unavailable for a few seconds due to connectivity issues.  This update is absolutely critical so I want to wrap it in some code to retry in case of failure to communicate with the sql server.  Is there any best practice or examples for adding this resilient retry functiionality?

Thanks in advance
0
Comment
Question by:cp30
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 19

Expert Comment

by:Bardobrave
ID: 37721336
Assign a boolean return value to the method that calls the stored procedure. If the execution works return true, otherwise return false.

On your main code, when calling the method do:

storedProcExecuted = false;
while(not storedProcExecuted)
    storedProcExecuted = callYourStoredProcMethod(yourParams);

This way your method will retry until it executes and returns true.
It's pretty rough but functional and easy.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37721812
Try catch to catch the unable to connect or timeout.

But when building a loop for retrial, don't make it running forever.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37722000
Within your method that executes the stored procedure, you can execute a simple Select Count to test the connection and keep trying (for a configurable number of times) to connect if you get error. Something like

Private Function ...
For i as integer = 0 to 10
  try
    'code to test connection
    connected=true
    exit for
  catch
  end try
  if connected then
     'code to execute stored procedure
  end if
End Function
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:cp30
ID: 37722197
Hi,

I'm not keen on something that runs indefinitely Bardobrave's suggestion, while the update is critical and I want to retry, I'd rather it fails after x number of retries and logs an exception rather than lock up the whole application because it's got into an infinite loop.

Problem with CodeCruiser's suggestion is that I don't think I can assume that just because I tested the connection a few lines back, that it will still be available.

Thinking that a limited loop with Try/Catch seems best suggestion so far...something like this....

retryExecuteSP:

 Try
            iRetry += 1

            Dim cmd As New SqlCommand("spLB_updateThreadInfo", conn)

            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("threadIinfo", threadInfo)

            conn.Open()
            cmd.ExecuteNonQuery()

            Return True

        Catch ex As Exception

            If iRetry < 6 Then
                Threading.Thread.Sleep(1000)
                GoTo retryExecuteSP
            End If

            Return False
        Finally
            conn.Close()
        End Try

    End Function

Open in new window


Any comments on that? So it should try 5 times and then return false if still no joy
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37722315
Yeah that would do the job as well.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37722494
You already implemented the comment I wanted to make: a waiting period between retrial.

Some comments on exception
- you only need to retry when there is something like 'not connected ..', 'time out' ...
- you don't close connection .... but you reopen it in second attempt , even for timeout it wasn't necessary
- here you have overview, but don't you want to rollback everything if your last statement errored
- what if you get an error 'unable to allocate space', or 'FK violation' -> you retry and after 6 times you return false .... and how do you know what error?
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 37722782
Threading.Thread.Sleep(1000) will block (won't it?) Why not start a timer and get it to fire off the retry?
0
 

Author Comment

by:cp30
ID: 37722790
Hi jogos,

- you only need to retry when there is something like 'not connected ..', 'time out' ...
yes, I will add in more specific checking when I make live

you don't close connection .... but you reopen it in second attempt , even for timeout it wasn't necessary
But I thought this would be closed by the finally block which is always executed

here you have overview, but don't you want to rollback everything if your last statement errored
It's actually a simple update that the sp performs which should either work or not, and I can add some error trapping in the sp itself, it's mainly the potential connectivity issue that worries me.

what if you get an error 'unable to allocate space', or 'FK violation' -> you retry and after 6 times you return false .... and how do you know what error?
Yes, I will add some kind of logging to catch the full exception details in case of 6 times failing.

Cheers
0
 

Author Comment

by:cp30
ID: 37722798
Hi nepaluz,

Threading.Thread.Sleep(1000) will block (won't it?) Why not start a timer and get it to fire off the retry?

This code will be in a multi-threaded windows service so I don't think it matters if I block the current thread as all other threads will carry on, (I think), and the current thread can't carry on until it knows if the sp has been executed as it will carry out more tasks once the sp is successfully called.

Hope that makes sense, and others can comment if thread.sleep is the correct call to make.

Cheers
0
 
LVL 17

Accepted Solution

by:
nepaluz earned 500 total points
ID: 37722830
The multi-thread environment was not apparent. In any case,this may work too
    Dim xTimer As New Timer With {.Interval = 10000}
    Function RetrySP() As Boolean
        AddHandler xTimer.Tick,
            Sub(sender As Object, e As EventArgs)
                xTimer.Stop()
                RetrySP()
            End Sub
        Try
            iRetry += 1

            Dim cmd As New SqlClient.SqlCommand("spLB_updateThreadInfo", conn)

            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("threadIinfo", threadInfo)

            conn.Open()
            cmd.ExecuteNonQuery()

            Return True

        Catch ex As Exception

            If iRetry < 6 Then
                xTimer.Start()
            End If

            Return False
        Finally
            conn.Close()
        End Try

    End Function

Open in new window

0
 

Author Comment

by:cp30
ID: 37722856
Thanks for that.  What is the benefit of using the timer over the sleep?

Cheers
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 37722889
Hmmm ........!! Get back to you on that.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question