Firebird C# singleton pattern

Question:

I'm using the following function to connect to the database, I'm 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 will only open, perform the necessary operations and then close the connection. However I have some problems.

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

I created in Firebird an example table with auto-increment of the primary key using trigger , but it's incrementing from 2 to 2, when I open and close the connection, if I insert two items without closing the connection it increments from 1 to 1. What can this be causing this?

Answer:

@wmaicon951, I don't quite understand your question, but regarding keeping several active connections to the database I think this will solve it. 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 database will always be closed after the completion of the command with the block:

 finally
  {
    fecharConexao(cn);
  } 

Here's 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);
            }
        }
    }
}
Scroll to Top