User1607285675 posted
Hello everyone, this is my last beach .... So, I'm developing
a web application that plans to import from an excel file about 80k rows.
Before
pouring the contents on the database I do check operations row by row to see if the customer is not already present in the database through the tax code and a question number if positive I jump and so far no problem just a bit slow but it is normal
. The problem arises when I put this application on various production servers with iis 8 (win2012 server).
In
fact, when I upload the file, the file is uploaded correctly, but when it is actually imported, after about 40 seconds it gives me the message that the file has been imported but if I look in the sql table (version 16)
I see that it has only loaded 3368 customers compared to 17300 without giving me error and nothing else.
If I try to do the same thing on my machine launching the
application from Visual Studio 2015 and uploading the file is imported correctly with all 17300 customers.
I thought of something like a timeout or something else ... I tried everything but nothing also some parameters in web config but nothing to do.
Can you give me a hand?
Thank you in advance.
Giuseppe
Here my code:
****** SUB CHIAMANTE *****
Protected Sub btnInserisci_Click(sender As Object, e As EventArgs) Handles btnInserisci.Click
If FileUpload1.HasFile Then
FileUpload1.BackColor = Drawing.Color.White
UploadThisFile(FileUpload1)
Else
FileUpload1.BackColor = Drawing.Color.Red
lblError.Text = "Inserimento file obbligatorio"
End If
End Sub
Protected Sub UploadThisFile(ByVal upload As FileUpload)
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("nomeconnessione").ConnectionString)
Dim format As String = "dd/MM/yyyy"
Dim ident As String
Dim DataInserimento As Date = txtDataIns.Text
Dim connString As String = WebConfigurationManager.ConnectionStrings("nomeconnessione").ConnectionString
Dim sqlConn As New SqlConnection(connString)
Dim sqlCmd As New SqlCommand()
Dim sqlReader As SqlDataReader
Dim NomeFile As String
Dim chkNomeFile As String
If upload.HasFile And txtDataIns.Text <> "" Then
Dim filePath As String = String.Empty
Dim di As DirectoryInfo = New DirectoryInfo(Server.MapPath("~/Import/"))
Try
If Not di.Exists Then
di.Create()
End If
Catch ex As Exception
'Response.Write(ex.Message.ToString())
Finally
End Try
Try
chkNomeFile = upload.FileName
sqlConn.Open()
sqlCmd.Connection = sqlConn
sqlCmd.CommandText = "SELECT NomeFile,Percorso FROM UpFiles WHERE NomeFile = '" & chkNomeFile & "'"
sqlReader = sqlCmd.ExecuteReader()
sqlReader.Read()
NomeFile = sqlReader("NomeFile")
If NomeFile = chkNomeFile Then
sqlReader.Close()
sqlConn.Close()
Dim message As String = "File già caricato."
Dim sb As New System.Text.StringBuilder()
sb.Append("<script type = 'text/javascript'>")
sb.Append("window.onload=function(){")
sb.Append("alert('")
sb.Append(message)
sb.Append("')};")
sb.Append("</script>")
ClientScript.RegisterClientScriptBlock(Me.GetType(), "alert", sb.ToString())
Exit Sub
End If
Catch err As Exception
sqlReader.Close()
sqlConn.Close()
Try
filePath = Path.Combine(Server.MapPath("~/Import/"), upload.FileName)
filePath = filePath.Replace(" ", "")
upload.SaveAs(filePath)
' Legge file Excel
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim dt As DataTable
Dim dr As DataRow
Dim CognomeNome As String
Dim CodFisc As String
Dim DataNascita As Date
Dim CodiceSedeInps As String
Dim NomeSedeInps As String
Dim Residenza As String
Dim CodicePatronato As String
Dim UfficioPatronato As String
Dim Patronato As String
Dim Domanda1 As String
Dim sqlstring As String
MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & filePath & "';Extended Properties=""Excel 12.0;HDR=YES;""")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Foglio1$]", MyConnection)
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet, "[Foglio1$]")
dt = DtSet.Tables(0)
MyConnection.Close()
If dt.Rows.Count > 0 Then
Dim i As Integer
For i = 0 To dt.Rows.Count - 1
dr = dt.Rows(i)
CodFisc = dt.Rows(i).Item(6)
Domanda1 = dt.Rows(i).Item(11)
Using sqlCon As New SqlConnection(conn)
sqlstring = "SELECT CodiceFiscale,Domanda FROM Clienti WHERE CodiceFiscale = '" & CodFisc & "' AND Domanda = '" & Domanda1 & "'"
Using cmd As New SqlCommand()
cmd.CommandTimeout = 3600
cmd.CommandText = sqlstring
cmd.Connection = sqlCon
sqlCon.Open()
Dim da As New SqlDataAdapter(cmd)
Dim dt1 As New DataTable()
da.Fill(dt1)
If dt1.Rows.Count = 0 Then
CognomeNome = dt.Rows(i).Item(5)
CodFisc = dt.Rows(i).Item(6)
If dt.Rows(i).Item(7) IsNot DBNull.Value Then
DataNascita = dt.Rows(i).Item(7)
Else
DataNascita = "01/01/2000"
End If
If dt.Rows(i).Item(2) IsNot DBNull.Value Then
CodiceSedeInps = dt.Rows(i).Item(2)
Else
CodiceSedeInps = ""
End If
Domanda1 = dt.Rows(i).Item(11)
If dt.Rows(i).Item(3) IsNot DBNull.Value Then
NomeSedeInps = dt.Rows(i).Item(3)
Else
NomeSedeInps = ""
End If
If dt.Rows(i).Item(12) IsNot DBNull.Value Then
Residenza = dt.Rows(i).Item(12)
Else
Residenza = ""
End If
If dt.Rows(i).Item(13) IsNot DBNull.Value Then
CodicePatronato = dt.Rows(i).Item(13)
Else
CodicePatronato = ""
End If
If dt.Rows(i).Item(14) IsNot DBNull.Value Then
UfficioPatronato = dt.Rows(i).Item(14)
Else
UfficioPatronato = ""
End If
If dt.Rows(i).Item(15) IsNot DBNull.Value Then
Patronato = dt.Rows(i).Item(15)
Else
Patronato = ""
End If
' FUNCTION THAT INSERT THE CUSTOMERS IN DATABASE
InserisciCli(CognomeNome, CodFisc, DataNascita, CodiceSedeInps, NomeSedeInps, Residenza, CodicePatronato, UfficioPatronato, Patronato, Domanda1)
End If
sqlCon.Close()
End Using
End Using
Next
End If
Catch ex As Exception
Response.Write(ex.Message.ToString())
Return
End Try
' Inserisce i dati del file fisico nel database
Try
Dim strFile As String = filePath.Substring(filePath.LastIndexOf("\"))
Dim strFileName As String = strFile.Remove(0, 1)
Dim strFilePath As String = "~/Import/" & strFileName
Dim cmd As New SqlCommand("Insert into UpFiles (DataFile,Nomefile,Percorso,Ident) values (@Datafile,@Nomefile,@Percorso,@Ident)")
cmd.Parameters.AddWithValue("@DataFile", DataInserimento)
cmd.Parameters.AddWithValue("@NomeFile", strFileName)
cmd.Parameters.AddWithValue("@Percorso", strFilePath)
cmd.Parameters.AddWithValue("@Ident", chkNomeFile)
cmd.CommandTimeout = 3600
cmd.Connection = con
con.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
Finally
con.Close()
mostra()
End Try
Finally
sqlConn.Close()
'Response.Redirect("~/upload.aspx")
End Try
Else
lblError.Text = "Campo Obbligatorio"
End If
End Sub
****** ' FUNCTION THAT INSERT THE CUSTOMERS IN DATABASE
Public Sub InserisciCli(ByRef CognNom As String, ByRef CF As String, ByRef Nascita As Date, ByRef CodSedeInps As String, ByRef SedeInps As String, ByRef Resid As String, ByRef CodPatr As String, ByRef UffPatr As String, ByRef Patr As String, ByRef Domand
As String)
Dim isupdated As Boolean
Using sqlCon As New SqlConnection(conn)
Using cmd As New SqlCommand()
cmd.CommandText = "Insert into Clienti ([CognomeNome],[CodiceFiscale],[DataNascita],[CodiceSedeInps],[NomeSedeInps],[ComResidenza],[CodicePatronato],[UfficioPatronato],[Patronato],[Domanda]) VALUES (@CognomeNome,@CodFisc,@Nascita,@CodSedeInps,@SedeInps,@Resid,@CodPatr,@UffPatr,@Patr,@Domanda)"
' Dim sqlstring1 As String = "Insert into Clienti ([Matricola],[Sede],[RagioneSociale],[Indirizzo],[Cap],[Prov],[Comune],[Settore],[CSC],[CodFiscale],[PIva],Assegnatario,Assegnata) VALUES (@Matricola,@Sede,@RagSoc,@Indirizzo,@Cap,@Prov,@Comune,@Settore,@CSC,@CodFisc,@Piva,@Assegnatario,@Assegnata)"
' Sql1.InsertCommandType = SqlDataSourceCommandType.Text
'Sql1.InsertCommand = sqlstring1
cmd.Parameters.AddWithValue("CognomeNome", CognNom)
cmd.Parameters.AddWithValue("CodFisc", CF)
cmd.Parameters.AddWithValue("Nascita", Nascita)
cmd.Parameters.AddWithValue("CodSedeInps", CodSedeInps)
cmd.Parameters.AddWithValue("SedeInps", SedeInps)
cmd.Parameters.AddWithValue("Resid", Resid)
cmd.Parameters.AddWithValue("CodPatr", CodPatr)
cmd.Parameters.AddWithValue("UffPatr", UffPatr)
cmd.Parameters.AddWithValue("Patr", Patr)
cmd.Parameters.AddWithValue("Domanda", Domand)
cmd.CommandTimeout = 3600
cmd.Connection = sqlCon
sqlCon.Open()
isupdated = cmd.ExecuteNonQuery() > 0
sqlCon.Close()
' Sql1.Insert()
'Sql1.InsertParameters.Clear()
End Using
End Using
End Sub
Strange behaviour importing a large data excel to sql server row by row
