Connection Must Be Opened

[wrwonrath]

Hi.

Using the code below a receive the message: "Connection Must Be Opened". But the connection is opened.

This happens randomically and with different databases, and sometimes everything works fine. Does anyone knows how can I solve this problem ?

Code: Select all

            StringBuilder stringTabela = null;
            double registros = 0;

            try
            {
                List lstConexao = GetConexoes(c.Id);

                foreach (ConexaoDTO conexao in lstConexao)
                {

                    stringTabela = new StringBuilder(1024);

                    for (int i = 0; i < estruturaTabela.FieldCount; i++)
                    {
                        stringTabela.Append(" \"" + estruturaTabela.GetName(i) + "\" " + estruturaTabela.GetDataTypeName(i));
                        if (i != (estruturaTabela.FieldCount - 1))
                            stringTabela.AppendLine(",");
                    }

                    string consultaSQL = c.Consulta.Sql.Replace("'", "''").Replace("\", "\");

                    foreach (ConsultaParamDTO parametro in parametros)
                    {
                        switch (parametro.TipoDado.ToString().ToLower())
                        {
                            case "boolean":
                                consultaSQL = consultaSQL.Replace(parametro.Campo, parametro.ValorCampo.ToString());
                                break;
                            case "datetime":
                                consultaSQL = consultaSQL.Replace(parametro.Campo, string.Format("''{0:yyyy/MM/dd}''", parametro.ValorCampo));
                                break;
                            case "integer":
                                consultaSQL = consultaSQL.Replace(parametro.Campo, parametro.ValorCampo.ToString());
                                break;
                            case "decimal":
                                consultaSQL = consultaSQL.Replace(parametro.Campo, parametro.ValorCampo.ToString());
                                break;
                            default:
                                consultaSQL = consultaSQL.Replace(parametro.Campo, string.Format("''{0}''", parametro.ValorCampo));
                                break;
                        }
                    }

                    consultaSQL = string.Format(" INSERT INTO {0} SELECT '{1}' AS Empresa, * FROM" +
                        " dblink('hostaddr={2} port={3} dbname={4} user={5} password={6}','{7}') AS t({8});",
                        new string[] {
                            c.Nometabela,                            
                            conexao.Empresa,
                            conexao.Servidor, 
                            conexao.Porta.ToString(),
                            conexao.Banco, 
                            conexao.Usuario, 
                            Util.Global.Crypt("D",conexao.Senha,string.Empty), 
                            consultaSQL,
                            stringTabela.ToString()});

                    dbCommand = db.GetSqlStringCommand(consultaSQL);                    

                    registros += db.ExecuteNonQuery(dbCommand, transacao);

Thanks.

Wilton

Code: Select all

[Shalex]

DAAB itself should automatically open and close a connection when needed.
You can check our source code for the Enterprise Library support (\Program Files\Devart\dotConnect\PostgreSQL\Enterprise Library\src\CS) to make sure that we don't interfere in the process of managing (opening/closing) connection objects.

Try debugging the issue to find out why it is not opened in the particular cases. You can also override the ExecuteNonQuery() method to include your own implementation.