DataConnect.vb

Imports System.Configuration.ConfigurationManager
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient


Public Class DataConnect
    Private Shared _DataConnect As DataConnect
    Private _ConnectString As String

    Public Shared ReadOnly Property GetInstance As DataConnect
        Get
            If _DataConnect Is Nothing Then
                _DataConnect = New DataConnect
                _DataConnect.IntialiseMe()
            End If
            Return _DataConnect
        End Get

    End Property

    Private Sub IntialiseMe()
        BuildConnectionString()
    End Sub

    Private Sub BuildConnectionString()
        _ConnectString = Convert.ToString(ConfigurationManager.ConnectionStrings("SurgereConnection"))
        Using cn = New SqlConnection(_ConnectString)
            cn.Open()
            cn.Close()
        End Using
    End Sub
    Public Function GetDt(ByVal SQL As String) As DataTable
        Using cn = New SqlConnection(_ConnectString)
            Dim da As SqlDataAdapter = New SqlDataAdapter(SQL, cn)
            Dim dt As DataTable = New DataTable
            da.Fill(dt)
            Return dt
        End Using
    End Function
    'Public Function GetDTLang(ByVal SQL As String, ByVal lang As String) As DataTable
    '    Dim lt As New LanguageTranslator
    '    Using cn = New SqlConnection(_ConnectString)
    '        Dim da As SqlDataAdapter = New SqlDataAdapter(SQL, cn)
    '        Dim dt As DataTable = New DataTable
    '        da.Fill(dt)
    '        Dim clmns As Integer = dt.Columns.Count
    '        Dim rows As Integer = dt.Rows.Count
    '        For Each dr As DataRow In dt.Rows
    '            For i As Integer = 0 To clmns - 1
    '                If dr(i).ToString() <> "" Or dr(i).ToString() <> System.DBNull.Value.ToString() Then
    '                    dr(i) = lt.Translate(dr(i).ToString(), lang)
    '                Else
    '                    dr(i) = DBNull.Value
    '                End If
    '            Next
    '        Next
    '        dt.AcceptChanges()
    '        Return dt
    '    End Using
    'End Function
    Public Function GetData(ByVal cm As SqlCommand) As DataTable
        Using cn = New SqlConnection(_ConnectString)
            cn.Open()
            cm.Connection = cn
            Dim da As SqlDataAdapter = New SqlDataAdapter(cm)
            Dim dt As DataTable = New DataTable
            da.Fill(dt)
            Return dt
        End Using
    End Function
    Public Function GetDt(ByVal cmm As SqlCommand) As DataTable
        Using cn = New SqlConnection(_ConnectString)
            cn.Open()
            cmm.Connection = cn
            Dim da As SqlDataAdapter = New SqlDataAdapter(cmm)
            Dim dt As DataTable = New DataTable
            da.Fill(dt)
            Return dt
        End Using
    End Function
    'Public Function GetDTLang(ByVal cmm As SqlCommand, ByVal lang As String) As DataTable
    '    Dim lt As New LanguageTranslator
    '    Using cn = New SqlConnection(_ConnectString)
    '        cn.Open()
    '        cmm.Connection = cn
    '        Dim da As SqlDataAdapter = New SqlDataAdapter(cmm)
    '        Dim dt As DataTable = New DataTable
    '        da.Fill(dt)
    '        Dim clmns As Integer = dt.Columns.Count
    '        Dim rows As Integer = dt.Rows.Count
    '        For Each dr As DataRow In dt.Rows
    '            For i As Integer = 0 To clmns - 1
    '                If dr(i).ToString() <> "" Or dr(i).ToString() <> System.DBNull.Value.ToString() Then
    '                    dr(i) = lt.Translate(dr(i).ToString(), lang)
    '                Else
    '                    dr(i) = DBNull.Value
    '                End If
    '            Next
    '        Next
    '        dt.AcceptChanges()
    '        Return dt
    '    End Using
    'End Function

    Public Function ExecuteScalar(ByVal cmm As SqlCommand) As Object
        Dim result As Object
        Using cn = New SqlConnection(_ConnectString)
            cn.Open()
            cmm.Connection = cn
            result = cmm.ExecuteScalar()
            cn.Close()
            Return result
        End Using
    End Function
    Public Function ExecuteNonQuery(ByVal cmm As SqlCommand) As Object

        Dim result As Object

        Using cn = New SqlConnection(_ConnectString)
            cn.Open()
            cmm.Connection = cn
            result = cmm.ExecuteNonQuery()
            cn.Close()
            Return result
        End Using
        Return -1
    End Function

 
End Class