By chaw12 - 2/18/2013
Hi Members,
Currently, i am uploading a file from excel going to Sql Server with the help of VB.net as my front end. My approach is by making use of a stored procedure.
So, from excel file i make use of a Datatable, where I loop in the excel file and stored every rows and columns to Datatable then once all the rows and columns are collected inside the Datatable, I will now pass the Datatable going to stored procedure which resides on the server.
my problem is, it is taking a lot of minutes if i am going to upload an excel file containing 10,000 rows. can someone give me an insight on how could i make it a lot faster. Below would be my sample code.
Dim OutletUploadRec As New Tbl_OutletUpload
Private sub Form1
Dim myTmpDT as new datatable
For i as integer=4 to excel.usedrange.rows.count
With myTmpDT
.Rows.Add( _
Trim(xlWS.Range("A" & iDx).Value), _
Replace(xlWS.Range("B" & iDx).Value, "", "0"), _
Trim(xlWS.Range("C" & iDx).Value), _
Trim(xlWS.Range("D" & iDx).Text), _
Trim(xlWS.Range("E" & iDx).Text)
End with
next
OutletUploadRec.AppendFiledataServerSide(myTmpDT)
end sub
Public Class Tbl_OutletUpload
Function AppendFiledataServerSide(ByRef DT As DataTable) As DataTable
If myConn.State = ConnectionState.Closed Then myConn.Open()
Dim myCommand As New SqlCommand("[dbo].[AppendTblFileDatas]", myConn)
With myCommand
.CommandType = CommandType.StoredProcedure
.Parameters.AddWithValue("@TblFileData", DT)
.ExecuteNonQuery()
End With
myCommand.Dispose()
myConn.Close()
Return DT
End Function
end Class
i assigned OutletUploadRec to inherit the functions i created inside the Class Tbl_OutletUpload. AppendFileDataServerSide
|
|