Is it mandatory to open and close connection when entering data?

Question

In a *loop* que fazInsert` in an Excel spreadsheet it is mandatory to open and close the connection ?

I analyzed the following, depending on the amount of registration it may take up to 1:30 to do the insert.

Example: To Procedure returned 4443 lines and took right from 1h48 to do all the insert

I tried to open the connection before *loop8 and only close as soon as it ends, but this way corrupts the spreadsheet.

The application below executes a Procedure, stores in DataTable, does the insert and then download the spreadsheet

Follows code.

private void AtualizarPerformanceEntrega()
    {
        try
        {
            DataTable dt = ExportPerformanceEntrega();
            string dir = Session.SessionID;
            string sFileXLSX = Server.MapPath(dir) + @"\Performance_Entrega_base.xls";

        if (File.Exists(sFileXLSX))
        {
            string strConnXLSX = (@"Provider =Microsoft.ACE.OLEDB.12.0;Data Source= " + sFileXLSX + "; Extended Properties='Excel 12.0 Xml;HDR=YES;ReadOnly=False';");

                foreach (DataRow row in dt.Rows)
                {

                using (OleDbConnection connection = new OleDbConnection(strConnXLSX))
                {
                    string strSQL;
                    OleDbCommand cmd;

                    if ((string)row["StatusEntrega"] == "No Prazo" || (string)row["StatusEntrega"] == "Fora do Prazo" || (string)row["StatusEntrega"] == "Antes do Prazo")
                    {

                        if (dt.Rows.Count > 0)
                        {
                            strSQL = "INSERT INTO [Base Entregue$] " +
                           " (NFEmpresa,NFNumero,NFSerie,NFCliente,NFPedido,NFCidade,NFUF,Regiao,NFTransp,NomeTransp,DataEmbarque,DataPrevEntrega,DataEntrega,StatusEntrega,DiasAtraso,CodOcorrencia,DescrOcorrencia) " +
                           " VALUES (@NFEmpresa, @NFNumero, @NFSerie, @NFCliente, @NFPedido, @NFCidade, @NFUF, @Regiao, @NFTransp, @NomeTransp, @DataEmbarque, @DataPrevEntrega, " +
                           " @DataEntrega, @StatusEntrega, @DiasAtraso, @CodOcorrencia, @DescrOcorrencia)";
                            cmd = new OleDbCommand(strSQL, connection);
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFEmpresa"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFNumero"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFSerie"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFCliente"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFPedido"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFCidade"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFUF"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["Regiao"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFTransp"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NomeTransp"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DataEmbarque"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DataPrevEntrega"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DataEntrega"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["StatusEntrega"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DiasAtraso"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["CodOcorrencia"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DescrOcorrencia"]));
                            //Abrindo a conexão
                            connection.Open();
                            //Executando o INSERT
                            cmd.ExecuteNonQuery();
                            //Fechando a conexão
                            connection.Close();
                        }

                    }
                    if ((string)row["StatusEntrega"] == "Não Entregue")
                    {


                        if (dt.Rows.Count > 0)
                        {
                            strSQL = "INSERT INTO [Base Não Entregue$] " +
                          " (NFEmpresa,NFNumero,NFSerie,NFCliente,NFPedido,NFCidade,NFUF,Regiao,NFTransp,NomeTransp,DataEmbarque,DataPrevEntrega,DataEntrega,StatusEntrega,DiasAtraso,CodOcorrencia,DescrOcorrencia) " +
                          " VALUES (@NFEmpresa, @NFNumero, @NFSerie, @NFCliente, @NFPedido, @NFCidade, @NFUF, @Regiao, @NFTransp, @NomeTransp, @DataEmbarque, @DataPrevEntrega, " +
                          " @DataEntrega, @StatusEntrega, @DiasAtraso, @CodOcorrencia, @DescrOcorrencia)";
                            cmd = new OleDbCommand(strSQL, connection);
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFEmpresa"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFNumero"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFSerie"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFCliente"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFPedido"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFCidade"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFUF"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["Regiao"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFTransp"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NomeTransp"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DataEmbarque"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DataPrevEntrega"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DataEntrega"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["StatusEntrega"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DiasAtraso"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["CodOcorrencia"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DescrOcorrencia"]));
                            //Abrindo a conexão
                            connection.Open();
                            //Executando o INSERT
                            cmd.ExecuteNonQuery();
                            //Fechando a conexão
                            connection.Close();
                        }

                    }

                }

            }

        }
    }
    catch (Exception ex)
    {

        throw ex;
    }
}

Answer 1

It is not mandatory, but it is common to close so you no longer need to use it at that time. Corruption happens for other reasons, even because if it was open or could access again.

You don’t even need to close explicitly because you’re already doing it implicitly when you use using.

The OleDbCommand should be in a using also.

The File.Exists(sFileXLSX) cause a running condition. It may not have a direct relationship to the problem, but it may be interfering with it, or else it may still bring future problems.

I would take away the capture of this exception, is doing nothing. Actually it is yes, is bringing problem to debugging by modifying the stack trace.

Answer 2

You should not leave connections open.

You must:

1) Open connections as late as possible

2) Close connections as quickly as possible

The connection itself is returned to the connection pool. Connections are a limited and relatively expensive feature. Any new connection you establish that has exactly the same connection string will be able to reuse the pool connection.

We recommend that you always close the connection when you finish using it so that the connection is returned to the pool. Not closing a connection is something that in the long run can generate many problems, the shorter the time the connection is open, the better it will be for the health of your base date.

Source: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e7ccaab1-fa23-4652-b77d-5d7a8a4efb1e/what-if-i-leave-sql-connection-opened-?forum=sqldataaccess