Firebird C # singleton pattern

3

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 only open, perform the necessary operations and then close the connection. But I have some problems.

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

I created in Firebird a sample table with auto increment of the primary key using trigger , but it is incrementing by 2 when I open and close the connection if I insert two items without closing the connection it increments by 1. What can cause this?

    
asked by anonymous 03.11.2015 / 15:23

1 answer

2

@ wmaicon951, I did not quite understand your question, but as for keeping multiple active connections to the database I believe this will solve. Where I have the methods of abrirConexao , fecharConexao and executarComando , and whenever I need to call the executarComando method I will be ensuring that the connection to the database will always be closed after the command is executed with the block: / p>

 finally
  {
    fecharConexao(cn);
  } 

Here is 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);
            }
        }
    }
}
    
11.11.2015 / 12:58