Getting a connection to database lost error

every once and a while my application throws a connection to database lost error.

The database class I got from a tutorial site and is below, it works great except for the above error sometimes, im guessing its timing out, like if the person using it goes for a smoke break and comes back and tries to continue where they left off.

And of coarse being the normal end-user they close then error message THEN come get me to tell me they got an error.

But until the error comes up again i thought i would ask what part of this code could be changed to prevent that error

this is a firebird db server and a c# application

    using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using FirebirdSql.Data.FirebirdClient;

namespace _0912111
{
    class DatabaseConnection
    {
        private FbConnection conn;
        private FbCommand sqlCommand;
        private FbDataAdapter DB;
        private DataSet DS = new DataSet();

        public DatabaseConnection()
        {
            conn = new FbConnection("User=myuser;" + "Password=mypw;" + "Database=dbpath;" + "DataSource=serverip;" + "Port=dbport;" + "Dialect=3;" + "Charset=UTF8;");
        }
        public void showDbError(string theError)
        {
            MessageBox.Show("Could not connect to database\n\nError Details:\n" + theError);
        }
        public FbConnection Openconn()
        {
            if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
            {
                try
                {
                    conn.Open();
                }
                catch (Exception e)
                {
                    showDbError(e.Message.ToString());
开发者_如何学运维                }
            }
            return conn;
        }
        public FbConnection Closeconn()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
            return conn;
        }
        public void nonQuery(string txtQuery)
        {
            FbCommand cmd = new FbCommand(txtQuery);
            try
            {
                cmd.Connection = Openconn();
                cmd.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
                showDbError(Ex.Message.ToString());
                throw Ex;
            }
            finally
            {
                cmd = null;
            }
        }
        public FbDataReader returnDataReader(string txtQuery)
        {
            FbCommand cmd = new FbCommand();
            try
            {
                cmd.Connection = Openconn();
                cmd.CommandText = txtQuery;
                FbDataReader rd;
                rd = cmd.ExecuteReader();
                return rd;
            }
            catch (Exception Ex)
            {
                showDbError(Ex.Message.ToString());
                throw Ex;
            }
            finally
            {
                cmd = null;
            }
        }
    }
}

I would think that the code in her that says

if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken) {

would prevent it??

---

Look, I suggest you to use using and rewrite your methods in a cleaner way, no reason to have another method to open and another to close connection, I'm not doing it anymore since longer than 5 years :D

also, no reason to do a ToString() on Ex.Message and also, notice, in C# you should throw exceptions with only throw not throw exc.

one of your methods would become this for example:

public void nonQuery(string txtQuery)
{
    using(var conn = new FbConnection(GetMyConnectionString(...parameters...)))
    {
        using(var cmd = new FbCommand(txtQuery))
        {
            try
            {           
                cmd.Connection = conn;
                conn.Open();

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                showDbError(ex.Message);
                throw;
            }
        }
    }
}

---

I'm 100% with Davide Piras on this. (upvoted him)

Delete the "Openconn" and "Closeconn" methods from your DatabaseConnection class. Then change your queries to have using statements for the connection open and command execution.

The database drivers already know how to perform connection pooling. Maintaining an open connection in code is not just a waste of time, but a potential cause of issues like the one you are experiencing. Other issues it can cause are leaked memory and the ability to open further connections with the database server.

So, rewrite your code to use best practices for database access and the problem will go away.

---

I'll leave this example that speaks of the connection and some examples that I hope will help.

http://code.msdn.microsoft.com/Esempio-applicazione-dati-494c129a

Regards.