Public Shared Function SaveToDB(ByVal strconnection As String, ByVal dt() As DataTable) As SaveInfo
Dim sinfo As New SaveInfo
Dim sql As String
Dim column() As String
Dim insert As String
Dim values As String
Dim i As Integer = 0
Dim x As Integer = 0
Dim cmd As New SqlCommand
Dim ctr As Integer = 0
Dim trans As SqlTransaction = Nothing
Using conn As New SqlConnection(strconnection)
conn.Open()
For aa As Integer = 0 To dt.GetLength(0) - 1
Try
ReDim column(dt(aa).Columns.Count)
'ReDim insert(dt.rows.count)
x = 0
For Each col As DataColumn In dt(aa).Columns
column(x) = col.ColumnName
x += 1
Next
insert = String.Format("INSERT INTO {0}(", dt(aa).TableName)
values = "VALUES("
For i = 0 To column.GetUpperBound(0) - 1
If i = column.GetUpperBound(0) - 1 Then
insert &= column(i) & ") "
values &= String.Format("@{0})", column(i))
Else
insert &= column(i) & ", "
values &= String.Format("@{0}, ", column(i))
End If
Next
sql = String.Format("{0}{1}", insert, values)
'conn.Open()
trans = conn.BeginTransaction
cmd.Transaction = trans
cmd.CommandText = sql
cmd.Connection = conn
For Each r As DataRow In dt(aa).Rows
cmd.Parameters.Clear()
'cmd.Parameters.Add(
For Each c As DataColumn In dt(aa).Columns
cmd.Parameters.AddWithValue("@" & c.ColumnName, r.Item(c.ColumnName))
Next
ctr += cmd.ExecuteNonQuery()
Next
Catch pgex As SqlException
If pgex.ErrorCode = 23505 Then
sinfo.ErrFound = True
sinfo.Message = String.Format("Error in saving: it may create duplicate value in table {0}. " & _
"Please contact Database Administrator.", dt(aa).TableName.ToString)
trans.Rollback()
Return sinfo
Else
sinfo.ErrFound = True
sinfo.Message = pgex.Message.ToString
trans.Rollback()
Return sinfo
End If
Catch ex As Exception
sinfo.ErrFound = True
sinfo.Message = ex.Message.ToString
trans.Rollback()
Return sinfo
End Try
Next
If ctr <> 0 Then
trans.Commit()
Else
trans.Rollback()
End If
End Using
Return sinfo
End Function
--error = sql connection does not support parallel transactions
Dim sinfo As New SaveInfo
Dim sql As String
Dim column() As String
Dim insert As String
Dim values As String
Dim i As Integer = 0
Dim x As Integer = 0
Dim cmd As New SqlCommand
Dim ctr As Integer = 0
Dim trans As SqlTransaction = Nothing
Using conn As New SqlConnection(strconnection)
conn.Open()
For aa As Integer = 0 To dt.GetLength(0) - 1
Try
ReDim column(dt(aa).Columns.Count)
'ReDim insert(dt.rows.count)
x = 0
For Each col As DataColumn In dt(aa).Columns
column(x) = col.ColumnName
x += 1
Next
insert = String.Format("INSERT INTO {0}(", dt(aa).TableName)
values = "VALUES("
For i = 0 To column.GetUpperBound(0) - 1
If i = column.GetUpperBound(0) - 1 Then
insert &= column(i) & ") "
values &= String.Format("@{0})", column(i))
Else
insert &= column(i) & ", "
values &= String.Format("@{0}, ", column(i))
End If
Next
sql = String.Format("{0}{1}", insert, values)
'conn.Open()
trans = conn.BeginTransaction
cmd.Transaction = trans
cmd.CommandText = sql
cmd.Connection = conn
For Each r As DataRow In dt(aa).Rows
cmd.Parameters.Clear()
'cmd.Parameters.Add(
For Each c As DataColumn In dt(aa).Columns
cmd.Parameters.AddWithValue("@" & c.ColumnName, r.Item(c.ColumnName))
Next
ctr += cmd.ExecuteNonQuery()
Next
Catch pgex As SqlException
If pgex.ErrorCode = 23505 Then
sinfo.ErrFound = True
sinfo.Message = String.Format("Error in saving: it may create duplicate value in table {0}. " & _
"Please contact Database Administrator.", dt(aa).TableName.ToString)
trans.Rollback()
Return sinfo
Else
sinfo.ErrFound = True
sinfo.Message = pgex.Message.ToString
trans.Rollback()
Return sinfo
End If
Catch ex As Exception
sinfo.ErrFound = True
sinfo.Message = ex.Message.ToString
trans.Rollback()
Return sinfo
End Try
Next
If ctr <> 0 Then
trans.Commit()
Else
trans.Rollback()
End If
End Using
Return sinfo
End Function
--error = sql connection does not support parallel transactions
Last edited: