Firebird C# standard Singleton

Question

I am using the following function to connect to the database, I am programming in C# in Visual Studio 2013.

namespace WindowsFormsApplication1
{
    static class Conexao
    {

        private static String strConn = Properties.Settings.Default.caminhoFbConnection;
        private static FbConnection conn = null;


        public static void Conection()
        {

        }

        public static FbConnection getConnection()
        {
            try
            {

                if (conn == null)
                {
                    conn = new FbConnection(strConn);
                    conn.Open();
                    return conn;
                }
                else
                {
                    if (conn.State == System.Data.ConnectionState.Open)
                    {
                        return conn;
                    }
                    else
                    {
                        conn.Open();
                        return conn;
                    }
                }
            }
            catch (Exception excep)
            {
                MessageBox.Show("Erro - " + excep.Message);
                return null;
            }
        }

        public static void closeConnection()
        {
            try
            {

                conn.Close();

            }
            catch (Exception excep)
            {
                MessageBox.Show(excep.Message);
            }
        }
    }
}

My idea of how to use this function would be, the connection only open, perform the necessary operations and then close the connection. But I’m having some problems.

When using this connection model the pool Firebird connection will only be with 1 active connection, or when I open the connection and close it the pool increases?

I created in Firebird an example table with auto increment of the primary key using Trigger, but it is increasing 2 by 2, when I open and close the connection, if I insert two items without closing the connection it increases 1 by 1. What can cause this?

Answer 1

@wmaicon951, I didn’t quite understand your question, but as for keeping several active connections with the database I think this will solve. Where I have the methods of abrirConexao, fecharConexao and executarComando, and whenever I need to call the method executarComando will be ensuring that the connection to the bank will always be closed after the completion of the command with the block:

 finally
  {
    fecharConexao(cn);
  } 

Follow the code I have:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.IO;
using FirebirdSql.Data.FirebirdClient;
using Demo_NFe.Code.BLL;

namespace MeuProjeto.Code.DAL
{
    class ConexaoBDDAL
    {
        public FbConnection abrirConexao()
        {
            #region Método responsável por abrir a conexão com Banco de Dados

            FbConnection conexao = null;

            UtilitariosBLL util = new UtilitariosBLL();

            try
            {
                string strConexao = String.Empty;
                string banco = "nome_banco"
                string servidor = "localhost";
                string usuario = "SYSDBA";
                string senha = "masterkey";

                strConexao = @"User=" + usuario + "; "
                + @"Password=" + senha + "; "
                + @"Database=" + banco + "; "
                + @"DataSource=" + servidor + "; "
                + "Dialect=3; "
                + "Charset=WIN1252; "
                + "Role=; "
                + "Connectionlifetime =15; "
                + "Pooling =true; "
                + "MinPoolSize =0; "
                + "MaxPoolSize =50; "
                + "PacketSize =8192; "
                + "ServerType =0";

                conexao = new FbConnection(strConexao);

                if (conexao.State == ConnectionState.Closed)
                    conexao.Open();
            }
            catch (Exception)
            {
                MessageBox.Show("Ocorreu um erro ao Estabelecer a Comunicação com o Banco de Dados, por favor verrifique se todas as configurações "
                               + "foram informadas corretamente!", "Atenção!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);             
            }

            return conexao;

            #endregion
        }


        //Método para Fechar a Conexão com o Banco de Dados
        public void fecharConexao(FbConnection cn)
        {
            if (cn.State == ConnectionState.Open)
                cn.Close();
        }

        //Método para Exerculta comandos SQL
        public void executarComando(string strQuery)
        {
            FbConnection cn = new FbConnection();

            try
            {
                cn = abrirConexao();
                FbCommand cmd = new FbCommand();
                cmd.CommandText = strQuery.ToString();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = cn;
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                MessageBox.Show("Ocorreu um erro ao Excultar o Comando SQL, Por Favor Certifique-se se o mesmo foi escrito corretamente!", "Atenção!", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                fecharConexao(cn);
            }
        }
    }
}