Catch when db is unavailable

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:

VB Code:

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".

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.

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?

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.

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.

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).

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.

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. :)