In% insert% in an Excel spreadsheet is it necessary to open and close the connection?
I have analyzed the following, depending on how much logging can take up to 1h30 to make *loop* que faz .
Example : The procedure returned 4443 lines and took a certain time of 1h48 to make all
insert
I have tried to open the connection before * loop8 and only close as soon as it finishes, but in this way corrupts the worksheet.
The application below executes a procedure , stores it in insert , does DataTable and then downloads the worksheet
Follow the 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;
}
}