To use the connect class, you can call it using the following example:
masterPage.Connect.StartTransaction()
Dim parameters As New List(Of SqlParameter)
parameters.Add(New SqlParameter("@UserName", SqlDbType.VarChar, 75, ParameterDirection.Input, False, 0, 0, String.Empty, DataRowVersion.Current, "Test"))
parameters.Add(New SqlParameter("@Password", SqlDbType.VarChar, 25, ParameterDirection.Input, False, 0, 0, String.Empty, DataRowVersion.Current, 0))
Dim ds = masterPage.Connect.FillDataset("ValidateUser", parameters, True)
I have added transaction to the class where you can create a transaction and then call all your stored procedures and tell them if you want them to be part of this transaction or not.
masterPage.Connect.StartTransaction()
masterPage.Connect.EndTransaction()
masterPage.Connect.RollbackTransaction()
And the Connect class...
Imports System
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Namespace Connect
''' <summary>
''' <para>Connect class provides connection for various type of external data. Class is not inheritable.</para>
''' </summary>
Public Class ConnectMe
#Region "Enum"
Public Enum SQLCall
DataSet = 0
DataTable = 1
NonQuery = 2
Reader = 3
Scalar = 4
BulkCopy = 5
End Enum
#End Region
#Region "Constructor"
Public Sub New(ByVal _connString As String)
If Not String.IsNullOrEmpty(_connString) Then Connection = New SqlConnection(_connString)
End Sub
''' <summary>
''' Initializes a new instance of the Connect class.
''' </summary>
Private Sub New()
End Sub
#End Region
#Region "Transaction"
Private transaction As SqlTransaction = Nothing
Public Sub StartTransaction()
Try
If Not Connection Is Nothing AndAlso transaction Is Nothing Then
Connection.Open()
transaction = Connection.BeginTransaction()
End If
Catch ex As Exception
CleanupErrorConnections()
MsgBox(ex.ToString)
End Try
End Sub
Public Sub EndTransaction()
Try
If Not transaction Is Nothing Then transaction.Commit()
If Not transaction Is Nothing Then transaction.Dispose()
If Not Connection Is Nothing AndAlso (Connection.State = ConnectionState.Open) Then Connection.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Public Sub RollbackTransaction()
Try
If Not transaction Is Nothing Then transaction.Rollback()
If Not transaction Is Nothing Then transaction.Dispose()
If Not Connection Is Nothing AndAlso (Connection.State = ConnectionState.Open) Then Connection.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
#End Region
#Region "Cleanup"
Public Sub CleanupErrorConnections()
If Not transaction Is Nothing Then transaction.Rollback()
If Not transaction Is Nothing Then transaction.Dispose()
If Not Connection Is Nothing AndAlso (Connection.State = ConnectionState.Open) Then Connection.Close()
End Sub
Public Sub CleanupConnections()
If Not transaction Is Nothing Then transaction.Dispose()
If Not Connection Is Nothing AndAlso (Connection.State = ConnectionState.Open) Then Connection.Close()
End Sub
#End Region
#Region " Public Properties "
''' <summary>
''' Gets or sets the string used to open a SQL Server database.
''' </summary>
''' <returns>The connection string that includes the source database name, and other parameters needed to establish the initial connection.</returns>
Public Property Connection() As SqlConnection
Get
Return _connection
End Get
Set(ByVal value As SqlConnection)
_connection = value
End Set
End Property
Private _connection As SqlConnection = Nothing
#End Region
#Region " Private Methods "
Private Sub AssignParameters(ByRef cmd As SqlCommand, ByRef cmdParameters As List(Of SqlParameter))
If (cmdParameters Is Nothing) Then Exit Sub
For Each p As SqlParameter In cmdParameters
cmd.Parameters.Add(p)
Next
End Sub
#End Region
#Region "Dataset"
''' <summary>
''' Adds or refreshes rows in the System.Data.DataSet to match those in the data source using the System.Data.DataSet name, and creates a System.Data.DataTable named "Table."
''' DataGridView1.DataSource = sqldb.FillDataset("Select * From dbo.Users", CommandType.Text).Tables(0)
''' </summary>
''' <param name="cmd">The Transact-SQL statement or stored procedure to execute at the data source.</param>
''' <param name="cmdType">A value indicating how the System.Data.SqlClient.SqlCommand.CommandText property is to be interpreted.</param>
''' <param name="parameters">The parameters of the Transact-SQL statement or stored procedure.</param>
''' <returns>A System.Data.Dataset object.</returns>
Public Function FillDataSet(ByVal _spName As String, Optional ByVal _parameters As List(Of SqlParameter) = Nothing, Optional ByVal _useTransaction As Boolean = False) As DataSet
Dim sqlda As SqlDataAdapter = Nothing
Dim resultObj As DataSet = Nothing
Try
Using command As New SqlCommand(_spName, Connection)
resultObj = New DataSet
command.CommandType = CommandType.StoredProcedure
''Assign parameters to command
Me.AssignParameters(command, _parameters)
''Check if using transaction
If _useTransaction Then command.Transaction = transaction
sqlda = New SqlDataAdapter(command)
sqlda.Fill(resultObj)
End Using
Catch ex As SqlException
CleanupErrorConnections()
MsgBox(ex.ToString)
Catch ex As Exception
CleanupErrorConnections()
MsgBox(ex.ToString)
Finally
If Not _useTransaction Then CleanupConnections()
End Try
Return resultObj
End Function
#End Region
#Region "DataTable"
Public Function FillDataTable(ByVal _spName As String, Optional ByVal _parameters As List(Of SqlParameter) = Nothing, Optional ByVal _useTransaction As Boolean = False) As DataTable
Dim sqlda As SqlDataAdapter = Nothing
Dim resultObj As DataTable = Nothing
Try
Using command As New SqlCommand(_spName, Connection)
resultObj = New DataTable
command.CommandType = CommandType.StoredProcedure
''Assign parameters to command
Me.AssignParameters(command, _parameters)
''Check if using transaction
If _useTransaction Then command.Transaction = transaction
sqlda = New SqlDataAdapter(command)
sqlda.Fill(resultObj)
End Using
Catch ex As SqlException
CleanupErrorConnections()
MsgBox(ex.ToString)
Catch ex As Exception
CleanupErrorConnections()
MsgBox(ex.ToString)
Finally
If Not _useTransaction Then CleanupConnections()
End Try
Return resultObj
End Function
#End Region
#Region "NonQuery"
Public Function ExecuteNonQuery(ByVal _spName As String, Optional ByVal _parameters As List(Of SqlParameter) = Nothing, Optional ByVal _useTransaction As Boolean = False) As Integer
Dim resultObj As Integer = Nothing
Try
Using command As New SqlCommand(_spName, Connection)
command.CommandType = CommandType.StoredProcedure
''Assign parameters to command
Me.AssignParameters(command, _parameters)
''Check if using transaction
If _useTransaction Then command.Transaction = transaction
resultObj = command.ExecuteNonQuery()
End Using
Catch ex As SqlException
CleanupErrorConnections()
MsgBox(ex.ToString)
Catch ex As Exception
CleanupErrorConnections()
MsgBox(ex.ToString)
Finally
If Not _useTransaction Then CleanupConnections()
End Try
Return resultObj
End Function
#End Region
#Region "Reader"
Public Function ExecuteReader(ByVal _spName As String, Optional ByVal _parameters As List(Of SqlParameter) = Nothing, Optional ByVal _useTransaction As Boolean = False) As IDataReader
Dim resultObj As SqlDataReader = Nothing
Try
Using command As New SqlCommand(_spName, Connection)
command.CommandType = CommandType.StoredProcedure
''Assign parameters to command
Me.AssignParameters(command, _parameters)
''Check if using transaction
If _useTransaction Then command.Transaction = transaction
resultObj = command.ExecuteReader(CommandBehavior.CloseConnection)
End Using
Catch ex As SqlException
CleanupErrorConnections()
MsgBox(ex.ToString)
Catch ex As Exception
CleanupErrorConnections()
MsgBox(ex.ToString)
Finally
If Not _useTransaction Then CleanupConnections()
End Try
Return CType(resultObj, IDataReader)
End Function
#End Region
#Region "Scalar"
Public Function ExecuteScalar(ByVal _spName As String, Optional ByVal _parameters As List(Of SqlParameter) = Nothing, Optional ByVal _useTransaction As Boolean = False) As Object
Dim resultObj As Object = Nothing
Try
Using command As New SqlCommand(_spName, Connection)
command.CommandType = CommandType.StoredProcedure
''Assign parameters to command
Me.AssignParameters(command, _parameters)
''Check if using transaction
If _useTransaction Then command.Transaction = transaction
resultObj = command.ExecuteScalar()
End Using
Catch ex As SqlException
CleanupErrorConnections()
MsgBox(ex.ToString)
Catch ex As Exception
CleanupErrorConnections()
MsgBox(ex.ToString)
Finally
If Not _useTransaction Then CleanupConnections()
End Try
Return CType(resultObj, IDataReader)
End Function
#End Region
#Region "BulkCopy"
Public Function ExecuteBulkCOpy(ByVal _spName As String, ByVal _desTableName As String, ByVal _sourceTable As DataTable, Optional ByVal _useTransaction As Boolean = False) As Object
Dim resultObj As Object = Nothing
Try
If _useTransaction Then
Using bulkCopy As New SqlBulkCopy(Connection, SqlBulkCopyOptions.Default, transaction)
bulkCopy.DestinationTableName = _desTableName
For Each c As DataColumn In _sourceTable.Columns
Dim map As New SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName)
bulkCopy.ColumnMappings.Add(map)
Next
bulkCopy.WriteToServer(_sourceTable)
End Using
Else
Using bulkCopy As New SqlBulkCopy(Connection)
bulkCopy.DestinationTableName = _desTableName
For Each c As DataColumn In _sourceTable.Columns
Dim map As New SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName)
bulkCopy.ColumnMappings.Add(map)
Next
bulkCopy.WriteToServer(_sourceTable)
End Using
End If
Catch ex As SqlException
CleanupErrorConnections()
MsgBox(ex.ToString)
Catch ex As Exception
CleanupErrorConnections()
MsgBox(ex.ToString)
Finally
If Not _useTransaction Then CleanupConnections()
End Try
Return CType(resultObj, IDataReader)
End Function
#End Region
End Class
End Namespace
No comments:
Post a Comment