Search the Community

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.countWith 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 withnext 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