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