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.
No comments:
Post a Comment