How can I force my Dataset using application to reconnect and prevent timeout error?

Question

Brief question

What command can I use to make my DataSet refresh it's connection to the SQL Server before I go on to work with the DataSet object? I'm working with C# in .Net 2.0

---

Much longer version of the same question with specifics

I have a database application that is often left running for several hours between manually instigated operations. When returning to the application after a long period (> 1 hour) and clicking a button it will throw a System.Data.SqlClient.SqlException with the error message

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

I figure I have a few options here:

• increase the timeout
• force the application to close after it has performed it's operation ensuring that the app is always started afresh when a user wants it.
• test the health of the connection before performing each operation.

My preference would be the latter but I don't know where a connection can be invoked or tested. I'm using Visual Studio to drag-drop build my DataSet, creating an instance. Filling tables and passing the dataset to another form. It is when loading this other form that the exception is thrown, but only if the application is left idle for hours.

I have not managed to replicate this in debug mode as yet, but I think the exception is likely being thrown by the _dsDistrib.tblReport.DefaultView.Sortline of the child forms constructor below.

So how can I make sure this DataSet has an active connection, or force a new connection, before I try to use the DataSet?

Relevant code of the main form

    public frmMain(string[] args)
    {
        InitializeComponent();
        _dsDistrib = new dsDistrib();
        taDistrib = new ReportShunt.dsDistribTableAdapters.tblDistribTableAdapter();
        taDistrib.Fill(_dsDistrib.tblDistrib);
    } 

    private void btnManage_Click(object sender, EventArgs e)
    {
        new frmManageDespatch(_dsDistrib,taDistrib).ShowDialog();
    }

Constructor of the child form

    public frmManageDespatch(dsDistrib ds, dsDistribTableAdapters.tblDistribTableAdapter taDis)
    {
        InitializeComponent();

        _dsDistrib = ds;
        taDistrib = taDis;

        _dsDistrib.tblReport.DefaultView.Sort = "tblReportId";

        dtUsers = (dsDistrib.tblUserDataTable)_dsDistrib.tblUser.Copy();

        cboReport.DataSource = _dsDistrib.tblReport;
        cboReport.DisplayMember = _dsDistrib.tblReport.ReportNameColumn.ColumnName;
        cboReport.ValueMember = _dsDistrib.tblReport.tblReportIdColumn.ColumnName;

        lbxUserSelection.DataSource = dtUsers;
        lbxUserSelection.DisplayMember = dtUsers.OutputFolderColumn.ColumnName;
        lbxUserSelection.ValueMember = dtUsers.tblUserIdColumn.ColumnName;

    }

Answer 1

EDIT: from your comment, I'm now guessing taDistrib is a SqlDataAdapter that is populated from a library called ReportShunt. The library is responsible for opening the connection.

Assuming that the table adapter has a select command, you could close the connection like. The table adapter would normally open it itself, before it talks to the database:

private void btnManage_Click(object sender, EventArgs e)
{
    taDistrib.SelectCommand.Connection.Close();
    new frmManageDespatch(_dsDistrib,taDistrib).ShowDialog();
}

You can also grab an entirely new connection:

private void btnManage_Click(object sender, EventArgs e)
{
    var localDsDistrib = new dsDistrib();
    var localTaDistrib = new ReportShunt.dsDistribTableAdapters.
         tblDistribTableAdapter();
    taDistrib.Fill(dsDistrib.tblDistrib);
    new frmManageDespatch(localDsDistrib,localTaDistrib).ShowDialog();
}

Better yet, only keep the connection open when you're actually doing something with it. Connections get cached and reopening them has no overhead at all. (We measured it with millions of request, and couldn't find a noticaeble difference.)