Open and close connection every time looping

Question

Open and close connection every time looping

Navigation

#1 by (3 votes)

1

I have an application that executes a procedure , saves it to a DataTable and then makes a looping to add the data to an excel worksheet. My question is, if I open and close the connection every insert will increase the process execution time and / or can I open / close only once while doing the whole process?

Follow the code below:

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;
                    //OleDbDataReader dts;
                    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();
                        }
                    }
                    //Fechando qualquer conexão que tenha ficado aberta, evitando o load infinito na página
                    connection.Close();   
                }               
            }
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

c# asp.net

asked by anonymous 10.10.2017 / 15:03

1 answer

Error in passing value to View in codeigniter How to implement an Abstract Controller that executes actions implemented in concrete controllers, depending on the variables passed via params?

score 3 · Accepted Answer

Performance will be impacted negatively if you open and close connections for each item in the loop. So the shortest and fastest answer is that you should open the connection before foreach , and close it later. You already use good practices for creating connections with using , so the last missing tip is: if the data is dependent on each other, using transactions can be a good deal.

Now, how performance is impacted.

On the client

There is a processor time consuming to open and close connection, and you will pay this cost every time you do these things. But this cost is small.

There is a cost of allocating memory to keep an open connection. You already use good practices ( using ), so the platform and Garbage Collector already do the logical part for you, and the cost is relatively low. But in general it is best to keep the amount of objects allocated for this as small as possible.

On the server

This is where the bug catches. The problem is not the cost of maintaining an individual connection open, but rather the fact that database servers generally have to cater to many clients at once. Opening and closing connections at random may overload the server.

Most current banks use a mechanism to deal with this called " pooling of connections"; Pooling could be freely translated as caster. The server maintains a set of internal connection objects alive indefinitely and changes only specific properties to vary the served clients. Each client requesting a connection is served by one of these objects, the first being free. When all the objects in the carousel are occupied, the next clients will wait in line. By default, SQL Server 201 maintains a cascade of 100 connections, but this number can be configured. The caster can even be turned off if it is more convenient.

Why is this important? When a server connection is released, it takes a while to become available again on the caster. If your application is the only one that uses the bank and your loop has less than 100 items, or if they all take too long, you will not feel much of an impact.

But if:

multiple instances of your application access the database at the same time, or;
The insert operation is super fast and the loop has more than 100 items, or;
For whatever reason, the pooling of connections is down or down ...

So you can feel a difference in the performance of the application if you test both situations: open connection to each item or open connection once to the loop.