.Net Multiple update to SQL on single call means....Pass Datatable to Store Procedure...instead of Loop...

DataAccess.vb

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Diagnostics

Public Class DataAccess
  Implements IDisposable

  'Take ConnedctionString Using dbSetting.dll File
  'Create Instant of SQL & Use it as a Connection Object
  Public strConnection As String = System.Configuration.ConfigurationSettings.AppSettings("thriftsql10AIM")
  Private da As SqlDataAdapter
  Private command As SqlCommand
  Private sqlTrans As SqlTransaction
  Private objCn As SqlConnection

  Public Enum daCommandType

    SelectCommand = 1
    InsertCommand = 2
    UpdateCommand = 3
    DeleteCommand = 4

  End Enum

  Public ReadOnly Property ReturnConnection(Optional ByVal intConType As Integer = 0)

    Get
      Dim oCn As SqlConnection
      If intConType = 0 Then
        If oCn Is Nothing Then
          oCn = New SqlConnection(strConnection)
        End If
      Else
        If oCn Is Nothing Then
          oCn = New SqlConnection(strConnection)
        End If
      End If

      oCn.Open()

      Return oCn

    End Get

  End Property
  Public Function CloseConnection(Optional ByVal intConType As Integer = 0)
    Dim oCn As SqlConnection
    If intConType = 0 Then
      If oCn Is Nothing Then
        oCn = New SqlConnection(strConnection)
      End If
    Else
      If oCn Is Nothing Then
        oCn = New SqlConnection(strConnection)
      End If
    End If

    oCn.Open()

    Return oCn

  End Function

  'Used when connection is for ThriftWeb DB & for SqlTransaction --- Creates New Object
  Public Sub New(Optional ByVal intConType As Integer = 0)
    If intConType = 0 Then
      objCn = New SqlConnection(strConnection)
    Else
      objCn = New SqlConnection(strConnection)
    End If
    objCn.Open()
  End Sub

  'Used when no Parameters are passed while Executing Store Procedure
  Public Sub New(ByVal sprocName As String, ByVal commandType As CommandType, Optional ByVal intConType As Integer = 0)

    'Use Connection Object created with dbSetting.dll
    If intConType = 0 Then
      If objCn Is Nothing Then
        objCn = New SqlConnection(strConnection)
      End If
    Else
      If objCn Is Nothing Then
        objCn = New SqlConnection(strConnection)
      End If
    End If
    command = New SqlCommand(sprocName, objCn)
    command.CommandType = commandType
    'command.CommandTimeout = 0 'command.Connection.ConnectionTimeout

    command.Connection.Open()

  End Sub

  'Used when Parameters are passed while Executing Store Procedure
  Public Sub New(ByVal sprocName As String, ByVal commandType As CommandType, ByVal parameters As SqlParameter(), Optional ByVal intConType As Integer = 0)

    'Use Connection Object created with dbSetting.dll
    If intConType = 0 Then
      If objCn Is Nothing Then
        objCn = New SqlConnection(strConnection)
      End If
    Else
      If objCn Is Nothing Then
        objCn = New SqlConnection(strConnection)
      End If
    End If
    command = New SqlCommand(sprocName, objCn)
    command.CommandType = commandType

    Dim parameter As SqlParameter
    For Each parameter In parameters
      command.Parameters.Add(parameter)
    Next
    'command.CommandTimeout = 0 'command.Connection.ConnectionTimeout

    command.Connection.Open()
  End Sub

  'Used to begin Sql Transaction
  Public Function BeginTrans() As SqlTransaction
    Try
      sqlTrans = objCn.BeginTransaction
    Catch ex As Exception
      Throw ex
    End Try
    Return sqlTrans
  End Function

  'Used to Rooback Sql Transaction
  Public Sub RollBackTrans()
    sqlTrans.Rollback()
  End Sub

  'Used to Commit Sql Transaction
  Public Sub CommitTrans()
    sqlTrans.Commit()
  End Sub

  'Used to call SP for Sql Transaction when No Parameters are passed to SP
  Public Sub SetCommand(ByVal sprocName As String, ByVal commandType As CommandType, Optional ByRef sqlTrans As SqlTransaction = Nothing)
    'ASSIGN COMMAND PROPERTIES, ASSIGN TRANSACTION, ASSIGN STORED PROCEDURE NAME
    command = New SqlCommand(sprocName, objCn)
    command.CommandType = commandType
    If Not IsNothing(sqlTrans) Then
      command.Transaction = sqlTrans
    End If
  End Sub

  'Used to call SP for Sql Transaction when Parameters are passed to SP
  Public Sub SetCommand(ByVal sprocName As String, ByVal commandType As CommandType, ByRef Parameters As SqlParameter(), Optional ByRef sqlTrans As SqlTransaction = Nothing)
    'SET COMMAND PROPERTIES, ASSIGN PARAMETERS, ASSIGN TRANSACTION, ASSIGN STORED PROCEDURE NAME
    command = New SqlCommand(sprocName, objCn)
    command.CommandType = commandType
    Dim parameter As SqlParameter
    For Each parameter In Parameters
      command.Parameters.Add(parameter)
    Next
    If Not IsNothing(sqlTrans) Then
      command.Transaction = sqlTrans
    End If
  End Sub

  Public Function runNonQuery_Int(ByRef cn As SqlConnection) As Int32
    If IsDBNull(command) Then
      Throw New ObjectDisposedException("Object is disposed")
    End If
    command.Connection = cn
    command.CommandTimeout = 300
    Return command.ExecuteNonQuery()

  End Function

  Public Function runNonQuery_Int() As Int32
    If IsDBNull(command) Then
      Throw New ObjectDisposedException("Object is disposed")
    End If
    Try
      Return command.ExecuteNonQuery()
    Catch ex As Exception
      Throw ex
    End Try
  End Function

  Public Function runScalar_Obj() As Object
    If IsDBNull(command) Then
      Throw New ObjectDisposedException("Object is disposed")
    End If
    Return (command.ExecuteScalar())
  End Function

  Public Function runReader_Rd() As SqlDataReader
    Dim rdr As SqlDataReader
    If IsDBNull(command) Then
      Throw New ObjectDisposedException("Object is disposed")
    End If
    rdr = command.ExecuteReader()
    Return rdr
  End Function

  Public Function runAdapter_Dt(ByRef cn As SqlConnection) As DataTable
    If IsDBNull(command) Then
      Throw New ObjectDisposedException("Object is disposed")
    End If

    command.Connection = cn

    Dim dataAdapter As SqlDataAdapter
    Dim dataTable As DataTable
    dataAdapter = New SqlDataAdapter
    dataTable = New DataTable
    dataAdapter.SelectCommand = command
    command.CommandTimeout = 300
    dataAdapter.Fill(dataTable)
    dataAdapter.Dispose()

    Return dataTable

  End Function

  Public Function runAdapter_Dt() As DataTable
    If IsDBNull(command) Then
      Throw New ObjectDisposedException("Object is disposed")
    End If

    Dim dataAdapter As SqlDataAdapter
    Dim dataTable As DataTable
    Try


      dataAdapter = New SqlDataAdapter
      dataTable = New DataTable
      command.CommandTimeout = 300
      dataAdapter.SelectCommand = command

      dataAdapter.Fill(dataTable)
      dataAdapter.Dispose()
    Catch ex As Exception
      Throw ex
    End Try
    Return dataTable
  End Function

  Public Function runAdapter_Ds() As DataSet
    If IsDBNull(command) Then
      Throw New ObjectDisposedException("Object is disposed")
    End If
    Dim dataAdapter As SqlDataAdapter
    Dim dataSet As DataSet
    dataAdapter = New SqlDataAdapter
    dataSet = New DataSet
    dataAdapter.SelectCommand = command
    command.CommandTimeout = 300
    dataAdapter.Fill(dataSet)
    dataAdapter.Dispose()
    Return dataSet
  End Function

  Public Function runAdapater_Update(ByVal objDt As DataTable, Optional ByVal type As daCommandType = daCommandType.InsertCommand) As Int32

    If IsDBNull(command) Then
      Throw New ObjectDisposedException("Object is disposed")
    End If

    Dim da As SqlDataAdapter
    da = New SqlDataAdapter

    Select Case type

      Case daCommandType.InsertCommand
        da.InsertCommand = command
      Case daCommandType.UpdateCommand
        da.UpdateCommand = command
      Case daCommandType.DeleteCommand
        da.DeleteCommand = command
      Case daCommandType.SelectCommand
        da.SelectCommand = command
      Case Else
        da.InsertCommand = command
    End Select

    Return da.Update(objDt)

  End Function

  Public Sub Dispose() Implements System.IDisposable.Dispose
    If Not IsDBNull(command) Then
      Dim tmpsqlcon As SqlConnection
      tmpsqlcon = command.Connection
      Debug.Assert(Not IsDBNull(tmpsqlcon))

      command.Dispose()
      command = Nothing

      tmpsqlcon.Dispose()
      tmpsqlcon = Nothing

      objCn.Close()
      objCn = Nothing

    End If

  End Sub

End Class


* This source code was highlighted with Source Code Highlighter.

Pass Datatable to Store procedure:


  Public Shared Function InsertAmazonSettlementData(ByVal objDt As DataTable) As Boolean

    Dim objDa As DataAccess = Nothing
    Dim param(17) As SqlParameter
    'Dim intRetValue As Integer
    'Dim blnResult As Boolean = True
    Dim blnResult As Boolean = False
    param(0) = New SqlParameter("@idMarketplace", SqlDbType.Int)
    param(0).SourceColumn = "idMarketplace"
    param(1) = New SqlParameter("@prevSettlementDate", SqlDbType.SmallDateTime)
    param(1).SourceColumn = "prevSettlementDate"
    param(2) = New SqlParameter("@settlementDate", SqlDbType.SmallDateTime)
    param(2).SourceColumn = "settlementDate"

    param(3) = New SqlParameter("@transactionsPeriod", SqlDbType.VarChar, 255)
    param(3).SourceColumn = "transactionsPeriod"
    param(4) = New SqlParameter("@Orders_ProductCharges", SqlDbType.Money)
    param(4).SourceColumn = "Orders_ProductCharges"
    param(5) = New SqlParameter("@Orders_PromoRebates", SqlDbType.Money)
    param(5).SourceColumn = "Orders_PromoRebates"
    param(6) = New SqlParameter("@Orders_AmazonFees", SqlDbType.Money)
    param(6).SourceColumn = "Orders_AmazonFees"
    param(7) = New SqlParameter("@Orders_OtherCharges", SqlDbType.Money)
    param(7).SourceColumn = "Orders_OtherCharges"
    param(8) = New SqlParameter("@Orders_TotalBalance", SqlDbType.Money)
    param(8).SourceColumn = "Orders_TotalBalance"
    param(9) = New SqlParameter("@Refunds_ProductCharges", SqlDbType.Money)
    param(9).SourceColumn = "Refunds_ProductCharges"
    param(10) = New SqlParameter("@Refunds_PromoRebates", SqlDbType.Money)
    param(10).SourceColumn = "Refunds_PromoRebates"
    param(11) = New SqlParameter("@Refunds_AmazonFees", SqlDbType.Money)
    param(11).SourceColumn = "Refunds_AmazonFees"
    param(12) = New SqlParameter("@Refunds_OtherCharges", SqlDbType.Money)
    param(12).SourceColumn = "Refunds_OtherCharges"
    param(13) = New SqlParameter("@Refunds_TotalBalance", SqlDbType.Money)
    param(13).SourceColumn = "Refunds_TotalBalance"
    param(14) = New SqlParameter("@Other_TransactionCharges", SqlDbType.Money)
    param(14).SourceColumn = "Other_TransactionCharges"

    param(15) = New SqlParameter("@closingBalance", SqlDbType.Money)
    param(15).SourceColumn = "closingBalance"
    param(16) = New SqlParameter("@strText", SqlDbType.VarChar, 6000)
    param(16).SourceColumn = "strText"
    param(17) = New SqlParameter("@status", SqlDbType.VarChar, 50)
    param(17).SourceColumn = "status"
    'param(5) = New SqlParameter("@intRetValue", SqlDbType.Int)
    'param(5).Direction = ParameterDirection.Output

    Try
      objDa = New DataAccess("Amazon_Settlement_Insert", CommandType.StoredProcedure, param)
      objDa.runAdapater_Update(objDt, DataAccess.daCommandType.InsertCommand)
      'intRetValue = param(5).Value
      blnResult = True
    Catch ex As Exception
      blnResult = False
      'intRetValue = -1
    Finally

      If Not IsNothing(objDa) Then
        objDa.Dispose()
      End If
      objDa = Nothing

      If Not IsNothing(objDt) Then
        objDt.Dispose()
        objDt = Nothing
      End If

    End Try
    'If intRetValue = -1 Then
    '  blnResult = True
    'ElseIf intRetValue = 1 Then
    '  blnResult = False
    'End If

    Return blnResult
  End Function

* This source code was highlighted with Source Code Highlighter.