Category

Imports System

Imports System.ComponentModel

Imports System.Collections

Imports System.Diagnostics

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

 

Public Class Database

    Implements IDisposable

 

#Region " Local Private Properties "

 

    ' connection to data source

    Private con As SqlConnection

    Private _connectionString As String = ""

 

#End Region

 

#Region "Class Constructors"

 

    ' <summary>

    ' Default constructor for a database object

    ' </summary>

    Public Sub New()

        MyBase.New()

    End Sub

 

#End Region

 

#Region "Public Properties"

 

    Public Property ConnectionString() As String

        Get

            Return Me._connectionString

        End Get

        Set(ByVal value As String)

            Me._connectionString = value

        End Set

    End Property

 

#End Region

 

#Region " Private Methods "

    ' <summary>

    ' Open the connection.

    ' </summary>

    Private Sub Open()

        ' open connection

        Try

            setConnectionString()

            If (con Is Nothing) Then

                Try

                    con = New SqlConnection(ConnectionString)

                    con.Open()

                Catch ex As Exception

                    con.Dispose()

                    con = Nothing

                    Throw New Exception(("Open: " + ex.Message))

                End Try

            ElseIf (con.State = System.Data.ConnectionState.Closed) Then

                con.ConnectionString = ConnectionString

                con.Open()

            End If

        Catch ex As Exception

            Throw New Exception(("Open: " + ex.Message))

        End Try

    End Sub

 

    Private Sub setConnectionString()

        Dim connectionStrings As ConnectionStringSettingsCollection = ConfigurationManager.ConnectionStrings

        If connectionStrings.Count <> 0 Then

            Dim connection As ConnectionStringSettings = connectionStrings.Item("Database.ConnectionString")

            ConnectionString = connection.ConnectionString

        End If

    End Sub

 

#End Region

 

#Region " Public Methods "

 

    ' <summary>

    ' Close the connection.

    ' </summary>

    Public Sub Close()

        If (Not (con) Is Nothing) Then

            con.Close()

        End If

    End Sub

 

    ' <summary>

    ' Make input param.

    ' </summary>

    ' <param name="ParamName">Name of param.</param>

    ' <param name="DbType">Param type.</param>

    ' <param name="Size">Param size.</param>

    ' <param name="Value">Param value.</param>

    ' <returns>New parameter.</returns>

    Public Function MakeInParam(ByVal ParamName As String, ByVal DbType As SqlDbType, ByVal Size As Integer, ByVal Value As Object) As SqlParameter

        If (Value Is Nothing) Then

            Return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, System.DBNull.Value)

        Else

            Return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value)

        End If

    End Function

 

    ' <summary>

    ' Make input param.

    ' </summary>

    ' <param name="ParamName">Name of param.</param>

    ' <param name="DbType">Param type.</param>

    ' <param name="Size">Param size.</param>

    ' <returns>New parameter.</returns>

    Public Function MakeOutParam(ByVal ParamName As String, ByVal DbType As SqlDbType, ByVal Size As Integer) As SqlParameter

        Return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, Nothing)

    End Function

 

    ' <summary>

    ' Make stored procedure param.

    ' </summary>

    ' <param name="ParamName">Name of param.</param>

    ' <param name="DbType">Param type.</param>

    ' <param name="Size">Param size.</param>

    ' <param name="Direction">Parm direction.</param>

    ' <param name="Value">Param value.</param>

    ' <returns>New parameter.</returns>

    Public Function MakeParam(ByVal ParamName As String, ByVal DbType As SqlDbType, ByVal Size As Int32, ByVal Direction As ParameterDirection, ByVal Value As Object) As SqlParameter

        Dim param As SqlParameter

        If (Size > 0) Then

            param = New SqlParameter(ParamName, DbType, Size)

        Else

            param = New SqlParameter(ParamName, DbType)

        End If

        param.Direction = Direction

        If Not ((Direction = ParameterDirection.Output) _

                    AndAlso (Value Is Nothing)) Then

            param.Value = Value

        End If

        Return param

    End Function

 

    ' <summary>

    ' Run stored procedure.

    ' </summary>

    ' <param name="procName">Name of stored procedure.</param>

    ' <returns>Stored procedure return value.</returns>

    Public Overloads Function RunProc(ByVal procName As String) As Integer

        Dim cmd As SqlCommand = CreateCommand(procName, Nothing)

        cmd.ExecuteNonQuery()

        Me.Close()

        Return CType(cmd.Parameters("ReturnValue").Value, Integer)

    End Function

 

    ' <summary>

    ' Run stored procedure.

    ' </summary>

    ' <param name="procName">Name of stored procedure.</param>

    ' <param name="prams">Stored procedure params.</param>

    ' <returns>Stored procedure return value.</returns>

    Public Overloads Function RunProc(ByVal procName As String, ByVal prams() As SqlParameter) As Integer

        Dim cmd As SqlCommand = CreateCommand(procName, prams)

        cmd.ExecuteNonQuery()

        Me.Close()

        Return CType(cmd.Parameters("ReturnValue").Value, Integer)

    End Function

 

    ' <summary>

    ' Run stored procedure.

    ' </summary>

    ' <param name="procName">Name of stored procedure.</param>

    ' <param name="dataReader">Return result of procedure.</param>

    Public Overloads Sub RunProc(ByVal procName As String, ByRef dataReader As SqlDataReader)

        Dim cmd As SqlCommand = CreateCommand(procName, Nothing)

        dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

    End Sub

 

    ' <summary>

    ' Run stored procedure.

    ' </summary>

    ' <param name="procName">Name of stored procedure.</param>

    ' <param name="prams">Stored procedure params.</param>

    ' <param name="dataReader">Return result of procedure.</param>

    Public Overloads Sub RunProc(ByVal procName As String, ByVal prams() As SqlParameter, ByRef dataReader As SqlDataReader)

        Dim cmd As SqlCommand = CreateCommand(procName, prams)

        dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

    End Sub

 

    ' <summary>

    ' Run stored procedure.

    ' </summary>

    ' <param name="procName">Name of stored procedure.</param>

    ' <param name="prams">Stored procedure params.</param>

    ' <param name="dataSet">Return result of procedure as a DataSet.</param>

    Public Overloads Sub RunProc(ByVal procName As String, ByVal prams() As SqlParameter, ByRef dataSet As DataSet)

        Dim cmd As SqlCommand = CreateCommand(procName, prams)

        Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(cmd)

        Dim ds As DataSet = New DataSet

        dataAdapter.Fill(ds)

        dataSet = ds

    End Sub

 

    ' <summary>

    ' Run stored procedure.

    ' </summary>

    ' <param name="procName">Name of stored procedure.</param>

    ' <param name="prams">Stored procedure params.</param>

    ' <param name="dataReader">Return result of procedure as a DataSet.</param>

    ' <param name="srcTable">Name of DataTable inside the DataSet to be returned.</param>

    Public Overloads Sub RunProc(ByVal procName As String, ByVal prams() As SqlParameter, ByRef dataSet As DataSet, ByVal srcTable As String)

        Dim cmd As SqlCommand = CreateCommand(procName, prams)

        Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(cmd)

        Dim ds As DataSet = New DataSet

        dataAdapter.Fill(ds, srcTable)

        dataSet = ds

    End Sub

 

    ' <summary>

    ' Create command object used to call stored procedure.

    ' </summary>

    ' <param name="procName">Name of stored procedure.</param>

    ' <param name="prams">Params to stored procedure.</param>

    ' <returns>Command object.</returns>

    Private Function CreateCommand(ByVal procName As String, ByVal prams() As SqlParameter) As SqlCommand

        ' make sure connection is open

        Open()

        'command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );

        Dim cmd As SqlCommand = New SqlCommand(procName, con)

        cmd.CommandType = CommandType.StoredProcedure

        ' add proc parameters

        If (Not (prams) Is Nothing) Then

            For Each parameter As SqlParameter In prams

                cmd.Parameters.Add(parameter)

            Next

        End If

        ' return param

        cmd.Parameters.Add(New SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, 0, 0, String.Empty, DataRowVersion.Default, Nothing))

        Return cmd

    End Function

#End Region

 

#Region " IDisposable Support "

 

    ' IDisposable

    Protected Overridable Sub Dispose(ByVal disposing As Boolean)

    End Sub

 

    ' This code added by Visual Basic to correctly implement the disposable pattern.

    Public Sub Dispose() Implements IDisposable.Dispose

        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.

        Dispose(True)

        GC.SuppressFinalize(Me)

    End Sub

#End Region

 

End Class