Ado.Net Transaction

Here's a sample use:

Private Sub ImportCsv()
Try
Dim con As SqlConnection = Connection
'Dim intResult As Integer
'Dim ctr As Integer
Dim fileToRead As String = brw_file.PostedFile.FileName
Dim fileStream As StreamReader
fileStream = File.OpenText(fileToRead)
Dim readContents As String
readContents = fileStream.ReadToEnd().Trim
Dim rowDelimiter As String
rowDelimiter = Chr(13) & Chr(10) '","
Dim rows As Array = Split(readContents, rowDelimiter)
Dim i As Integer
If IsValidCsv(readContents) = False Then
lblMessage.Text = message
Else
Try
If con.State = ConnectionState.Closed Then
con.Open()
End If
tr = con.BeginTransaction
For i = 0 To UBound(rows)
SaveToDb(con, tr, rows(i).ToString.Trim())
Next
tr.Commit()
Catch ex As Exception
tr.Rollback()
lblMessage.Text = ex.Message
End Try
If con.State = ConnectionState.Open Then
con.Close()
End If
End If
fileStream.Close()
Catch ex As Exception
'DotNetNuke.Services.Exceptions.LogException(ex)
lblMessage.Text = ex.Message
End Try


Sub SaveToDb(ByVal con As SqlConnection, ByVal tr As SqlTransaction, ByVal serial As String)
Dim intResult As Integer
Dim ctr As Integer
Dim cmd As SqlCommand = tr.Connection.CreateCommand
cmd.Transaction = tr
cmd.CommandText = "Insert Into Product (SerialNumber) Values (@SerialNumber)"
cmd.CommandType = CommandType.Text
Dim params As SqlParameter
params = New SqlParameter("@SerialNumber", SqlDbType.VarChar, 255)
params.Value = serial
cmd.Parameters.Add(params)
intResult = cmd.ExecuteNonQuery

If intResult > 0 Then
lblMessage.Text = "Records have been added to the database."
End If

End Sub