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

Import CSV to SQL

Not: The ff. was coded using VS 2003:

Private Sub ImportCsv()
Dim con As SqlConnection
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 = ","
Dim rows As Array = Split(readContents, rowDelimiter)
Dim i As Integer
If IsValidCsv(readContents) = False Then
lblMessage.Text = "Invalid CSV file"
Else

For i = 0 To UBound(rows)
'Response.Write(rows(i).ToString.Trim & "
")
con = Connection
Dim cmd As SqlCommand = con.CreateCommand()
con.Open()
cmd.CommandText = "Insert Into NEO_ProductSerialNumbers (SerialNumber) Values (@SerialNumber)"
cmd.CommandType = CommandType.Text
Dim params As SqlParameter
params = New SqlParameter("@SerialNumber", SqlDbType.VarChar, 255)
params.Value = rows(i).ToString.Trim()
cmd.Parameters.Add(params)
cmd.ExecuteNonQuery
Next

If con.State = ConnectionState.Open Then
con.Close()
End If
End If
fileStream.Close()
End Sub

Function IsValidCsv(ByVal contents As String) As Boolean
'Any validation the user needs
If (contents.IndexOf(" ") > -1) Then
Return False
Exit Function
End If
If (contents.IndexOf("NULL") > -1) Then
Return False
Exit Function
End If
If (contents.IndexOf(",") = -1) Then
Return False
Exit Function
End If
If contents.StartsWith(",") Or contents.EndsWith(",") Then
Return False
Exit Function
End If
Return True
End Function
End Class