Best way to Insert multiple records into a table?

[Ian Macey]

I am wanting to Insert multiple records into a table, and although I have some working code I feel that there must be a better way. The purpose of the code is to do a directory listing of a sub-directory and for each file listed insert a record into a temporary table.

Can anyone help?

My code is as follows....

SQL Stored Procedure
dbo.sp_bmwrInsSpool @username VarChar(8), @spoolName VarChar(12), @spoolDate VarChar(6) AS

DECLARE @uid Int

SET rowcount 1
SELECT @uid=userid.uid
FROM userid
WHERE username = @username
SET rowcount 0

INSERT INTO bmwrTmp (uid, spoolName, spoolDate)
VALUES (@uid, @spoolName, @spoolDate)

RETURN

ASP.NET Code (VB.NET)
Sub GetSpoolFileListing
Dim FilSysObj = Server.CreateObject("Scripting.FileSystemObject")
Dim Folder = FilSysObj.GetFolder("\\huksrv03\bmliv$\bmpc\DATA\BRANCH00\Spool")
Dim FolderContents = Folder.Files
Dim File

For Each File in FolderContents
InsertSpoolFile(File.Name)
Next
End Sub

Sub InsertSpoolFile(sFileName As String)
Dim SqlConn As New SqlConnection(ConfigurationSettings.AppSettings("hibdb"))
Dim CmdTxt As String = "sp_bmwrInsSpool"
Dim SqlCmd As New SqlCommand(CmdTxt, SqlConn)
Dim rowsAffected As Integer = 0
sqlCmd.CommandType = CommandType.StoredProcedure

SqlCmd.Parameters.Add(New SQLParameter("@username", Context.User.Identity.Name))
SqlCmd.Parameters.Add(New SQLParameter("@spoolName", sFileName))
SqlCmd.Parameters.Add(New SQLParameter("@spoolDate", ""))

SqlConn.Open
Try
rowsaffected=SqlCmd.ExecuteNonQuery
Catch
' lblStatus.Text = "Insert not successful ! Possible dupilcate reference."
Finally
SqlConn.Close
End Try
End Sub

 

[Guest]

It looks good to me. There are ways to insert multiple rows with one stored
procedure call but they are hacks and kludges.