Catch when db is unavailable

[MrGTI]

How do you handle a DataSet not being returned from the database if the server is off or unavailable?

For instance, you have some code that requests a DataSet from SS2K like so:

Dim dsUSA As DataSet = MakeConnection("sql statement", "jimmy")
Dim iRecCount As Integer = dsUSA.Tables(DS_ENTRY).Rows.Count
 
Public Function MakeConnection(ByVal sSQL As String, ByVal sTempDataSetName As String) As DataSet
        Dim DS As DataSet = New DataSet
        Try
            'Creates connection to database and gets a dataset of values
            Dim Conn As SqlConnection = New SqlConnection(SS2K)
            Dim Cmd As SqlDataAdapter = New SqlDataAdapter(sSQL, Conn)
            'Sends back a dataset object full of data from the SQL statement
            Cmd.Fill(DS, sTempDataSetName)
        Catch Exp As Exception
            '
 
        End Try
        Return DS
End Function

How do you guys catch a situation where the db is not available? Right now, this code will error out on the row count line saying "Object reference not set to an instance of an object".

[Mike Hildner]

What object doesn't have an instance? Maybe the DataSet itself or the table you're trying to get?

Edited something stupid out.

Edit again: I should really have another cocktail
If you're db code errors out, and you squelch the exception, you're just asking for trouble, and sure you'll have a dataset that will not have a table DS_ENTRY, so I guess the .Rows throws that exception.

If that's what's happening, at least don't squelch the exception. Lately I've been gravitating toward returning an object that contains my dataset, a boolean success/fail and a "friendly" message. Then I check if myObject.Success and go from there.

[MrGTI]

Yes, i did it just like that once before, but i thought maybe there was a smarter, built in way.

I centralize the connection code since it used in numerous locations. But since the DataSet is declared properly, i figured the function would return it properly, only with no rows, so the row count check would return 0.

Is there a way in the Expection to alter the DataSet so it doesn't throw that error?

[Mike Hildner]

My code is centralized also, and I really just want a DataSet back, but you never know if the method will succeed or not, that's why I went with an object that contains, among other things, a success flag.

DataSet doesn't contain any rows. It contains tables, and the tables have rows. I guess one could create a table in the exception handler, and add it to the DataSet. But then you'd still have to check that table, and see if it has any rows or not.

[MrGTI]

Actually, the next line of code does check if iRecCount > 0, so we're both thinking the same thing (i just didn't post that line of code).

Yeah, i know the DataSet contains tables, not rows. I guess the returned DataSet doesn't have a table, which is probably something i didn't consider since i was concentrating on the rows, not the table.

I think the best idea would be to create a fake table in the Exception, and add it to the DataSet.

[MrGTI]

After thinking about this a little more,........

Since it's a web app, and it's not as easy to debug a live website as it is to debug a desktop app, i probably should create a custom DataSet class like i did in the past (and like you do).

At least with a custom class i can send back if it was successful, and what the error message was (if a problem occurs).

[Mike Hildner]

That's exactly why I started doing it that way. Much more informative to be able to say something's wrong than just to have an empty table.

[MrGTI]

I'll keep the error message hidden, and only display it on the web if i need to debug it.

But the Boolean flag in the custom class will determine if the user gets a pretty message saying there was a problem.