Creating batched sql statements

Question

I have a small job that takes a text file of email/zip codes and inserts them into a sql server 2005 table. It reads each line of the source file, checks to make sure that it parses into a valid email address/zip code, creates a sql insert command, adds it to a string builder and eventually executes it. I want to do a single execute instead of individual sql calls as there will possibly be several thousand inserts.

This works, but I'd like to know if this is a bad approach. What's the best practice here?

Dim SqlString As String = "insert into [CollectedEmail] values('{0}','{1}');"

Do Until sourceFile.Peek = -1
        line = sourceFile.ReadLine
        If line.Length > 0 Then

            Dim emailAddress As String = Trim(line.Substring(0, EmailLength))
            Dim zipcode As String = Trim(line.Substring(EmailLength + 2, ZipCodeLength))

            If CVal.validEmail(emailAddress) AndAlso CVal.validZip(zipcode) Then
                SQL.AppendLine(String.Format(SqlString, emailAddress, zipcode))
            ElseIf CVal.validEmail(emailAddress) Then
               SQL.AppendLine(String.Format(SqlString, emailAddress, ""))
            Else
                badAddresses.WriteLine(emailAddress)
            End If
        End If

    Loop
 InsertToDatabase(SQL.ToString)

Thank you

Answer 1

For bulk inserts, check out .NET 2.0 onwards, SqlBulkCopy class (in the System.Data.SqlClient namespace)

Marc Gravell posted an example of its use here on SO: Copy from one database table to another C#

Updated in response to comment: you can read the cleansed data into a DataTable, and use that with SqlBulkCopy, similiar to this example.

Answer 2

If you have sql server 2008 then you can use Table-Valued Parameters, see here for some more info Table-Valued Parameters in SQL Server 2008 and sample code. Otherwise do bulk insert

Answer 3

You could also bundle your emails into an XML parameter and have some stored procedure that is able to process it.

CREATE PROCEDURE [dbo].[saveEmails]
(   
    @emails xml
)
AS
BEGIN
SET NOCOUNT ON;


insert into collectedemail
(address
,zipcode)
select
x.value('address[1]', 'nvarchar(255)')
,x.value('zipcode[1]', 'nvarchar(10)') 
from @emails.nodes('/items/item') emails(x)

END
GO

You can keep a counter that stores how many records you have queued up and then execute after a batch size you specify.