Import From Excel To SQL Server - Can It Be More Efficient ?

Hello All,





Iam new to the world of ASP .Net. Right now iam building an application that will IMPORT about 5,000 records from an Excel spreadsheet to a table in MS SQL Server. Right now the code works correctly, but i feel it is not efficient and takes a little bit of more time in doing the import. Could you guys throw some light on how i can make the code run more faster ? Someone suggested me that i can use DataAdapter and update the table in the database thru an update method available with it. I dont know how to do it? Could anyone share with me a snippet of code that does this ?





Here is my code:








Private Sub ProcessRecords()


Dim ds2 As New DataSet


' readExcelSheet is a user-defined function that reads a spreadsheet and returns a DataSet object


ds2 = readExcelSheet("C:InetpubwwwrootProject1Book2.xls", "SELECT * FROM [Sheet1$]")


Dim myConnection As SqlConnection = Connection() ' user-defined function that returns a SQLConnection object


myConnection.Open()


Dim strSQL As String = "insert_member" ' stored procedure that inserts records


Dim myCommand As New SqlCommand(strSQL, myConnection)


myCommand.CommandType = CommandType.StoredProcedure


myCommand.Parameters.Add("@salutation", SqlDbType.NVarChar)


myCommand.Parameters.Add("@firstname", SqlDbType.NVarChar)


myCommand.Parameters.Add("@lastname", SqlDbType.NVarChar)


myCommand.Parameters.Add("@company", SqlDbType.NVarChar)





Dim i, j As Integer


Response.Write(Date.Now() & "<br>")


For i = 0 To ds2.Tables("Members").Rows.Count() - 1


myCommand.Parameters("@salutation").Value = ds2.Tables("Members").Rows(i).Item("sal")


myCommand.Parameters("@firstname").Value = ds2.Tables("Members").Rows(i).Item("firstname")


myCommand.Parameters("@lastname").Value = ds2.Tables("Members").Rows(i).Item("lastname")


myCommand.Parameters("@company").Value = ds2.Tables("Members").Rows(i).Item("company")


j = myCommand.ExecuteNonQuery()


If (j > 0) Then


Response.Write("Record Inserted - " & i + 1 & "<br>")


End If


Next


Response.Write(Date.Now() & "<br>")


myConnection.Close()


End Sub














Please reply soon.


Thank You.