Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data.SqlServerCe
Imports System.Data


Public Class AcessoDados
    Dim conn As SqlCeConnection
    Dim cmd As SqlCeCommand
    Dim isTran As Boolean

    Private trans As SqlCeTransaction
    Dim connStr As String

    Public Shared Function GetConnectionString(ByVal strConnection As String) As String
        Dim retorno As New String("")
        Dim myConnection As SQLCEConnection = New SQLCEConnection()
        Dim myConnectionString As String

        myConnectionString = "Data Source=|DataDirectory|\ecosoft.sdf"
        myConnection.ConnectionString = myConnectionString
        myConnection.Open()

        If Not String.IsNullOrEmpty(strConnection) Then
            Try
                retorno = ConfigurationManager.ConnectionStrings(strConnection).ConnectionString
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        Else
            retorno = ConfigurationManager.ConnectionStrings("ConexaoSQLServer").ConnectionString
        End If
        Return retorno
    End Function

    Private Function Connect() As Boolean
        Dim connstr As String
        Dim bln As Boolean
        'Try
        If conn Is Nothing Then
            connstr = GetConnectionString("ConexaoSQLServer")
            If connstr <> String.Empty Then
                bln = True
                conn = New SQLCEConnection(connstr)
            Else
                bln = False
            End If
        End If
        If conn.State = ConnectionState.Closed Then
            Try
                conn.Open()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If
        Return bln
    End Function
    Public Sub BeginTransaction()
        If isTran Then Return
        If conn.State = ConnectionState.Closed Then
            conn.Open()
        End If
        trans = conn.BeginTransaction()
        isTran = True
    End Sub
    Public Sub CommitTransaction()
        If Not isTran Then Return
        trans.Commit()
        conn.Close()
        trans = Nothing
        isTran = False
    End Sub
    Public Sub RollBackTransaction()
        If Not isTran Then Return
        trans.Rollback()
        conn.Close()
        trans = Nothing
        isTran = False
    End Sub
    Public Sub CloseConn()
        If Not conn Is Nothing Then
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End If
    End Sub
    Public Function ExecuteQuery(ByVal strCmdTxt As String) As Boolean
        Dim intRows As Integer

        If conn.State = ConnectionState.Closed Then
            Connect()
        End If

        cmd = New SqlCeCommand
        cmd.Connection = conn
        cmd.CommandText = strCmdTxt
        cmd.CommandType = CommandType.Text

        If Not isTran Then
            intRows = cmd.ExecuteNonQuery()
            conn.Close()
        Else
            cmd.Transaction = trans
            intRows = cmd.ExecuteNonQuery()
        End If
        If intRows > 0 Then
            ExecuteQuery = True
        Else
            ExecuteQuery = False
        End If

    End Function

    Public Function ExecuteAndGetID(ByVal strCmdTxt As String, Optional ByVal blnNonID As Boolean = False) As String
        Dim intRows As Integer
        If conn.State = ConnectionState.Closed Then
            Connect()
        End If

        cmd = New SqlCeCommand
        cmd.Connection = conn
        cmd.CommandText = strCmdTxt
        cmd.CommandType = CommandType.Text

        If Not isTran Then
            intRows = cmd.ExecuteNonQuery()
        Else
            cmd.Transaction = trans
            intRows = cmd.ExecuteNonQuery()
        End If

        strCmdTxt = " SELECT @@IDENTITY AS TEMPNAME "
        cmd.CommandText = strCmdTxt
        cmd.CommandType = CommandType.Text

        If Not isTran Then
            ExecuteAndGetID = CStr(cmd.ExecuteScalar())
            conn.Close()
        Else
            cmd.Transaction = trans
            ExecuteAndGetID = CStr(cmd.ExecuteScalar())
        End If
    End Function

    Public Function ExecuteAndGetReader(ByVal strCmdTxt As String) As SqlCeDataReader
        If conn.State = ConnectionState.Closed Then
            Connect()
        End If

        cmd = New SQLCECommand
        cmd.Connection = conn
        cmd.CommandText = strCmdTxt
        cmd.CommandType = CommandType.Text

        If Not isTran Then
            ExecuteAndGetReader = cmd.ExecuteReader

        Else
            cmd.Transaction = trans
            ExecuteAndGetReader = cmd.ExecuteReader
        End If

    End Function

    Public Function ExecuteAndGetRow(ByVal strCmdTxt As String) As DataRow

        Dim dt As DataTable
        Dim da As SQLCEDataAdapter
        Dim row As DataRow

        If conn.State = ConnectionState.Closed Then
            Connect()
        End If
        cmd = New SQLCECommand
        cmd.Connection = conn
        cmd.CommandText = strCmdTxt
        cmd.CommandType = CommandType.Text
        dt = New DataTable
        If Not isTran Then

            da = New SQLCEDataAdapter(cmd)
        Else
            cmd.Transaction = trans
            da = New SQLCEDataAdapter(cmd)
        End If

        da.Fill(dt)
        da.Dispose()

        row = dt.Rows(0)
        ExecuteAndGetRow = row


    End Function

    Public Function ExecuteAndGetRowUsuario(ByVal strCmdTxt As String) As DataRow

        Dim dt As DataTable
        Dim da As SqlCeDataAdapter
        Dim row As DataRow

        If conn.State = ConnectionState.Closed Then
            Connect()
        End If
        cmd = New SqlCeCommand
        cmd.Connection = conn
        cmd.CommandText = strCmdTxt
        cmd.CommandType = CommandType.Text
        dt = New DataTable
        If Not isTran Then

            da = New SqlCeDataAdapter(cmd)
        Else
            cmd.Transaction = trans
            da = New SqlCeDataAdapter(cmd)
        End If

        da.Fill(dt)
        da.Dispose()
        If dt.Rows.Count > 0 Then
            row = dt.Rows(0)
            ExecuteAndGetRowUsuario = row
        Else
            ExecuteAndGetRowUsuario = row
        End If

    End Function

    Public Function VerificaExiste(ByVal strCmdTxt As String) As Boolean

        Dim dt As DataTable
        Dim da As SQLCEDataAdapter

        If conn.State = ConnectionState.Closed Then
            Connect()
        End If
        cmd = New SQLCECommand
        cmd.Connection = conn
        cmd.CommandText = strCmdTxt
        cmd.CommandType = CommandType.Text
        dt = New DataTable
        If Not isTran Then

            da = New SQLCEDataAdapter(cmd)
        Else
            cmd.Transaction = trans
            da = New SQLCEDataAdapter(cmd)
        End If

        da.Fill(dt)
        da.Dispose()

        If dt.Rows.Count > 0 Then
            Return True
        Else
            Return False
        End If

    End Function


    Public Function getDataset(ByVal strCmdTxt As String) As DataSet
        Dim da As SQLCEDataAdapter = New SQLCEDataAdapter
        Dim ds As DataSet = New DataSet

        If conn.State = ConnectionState.Closed Then
            Connect()
        End If

        cmd = New SQLCECommand
        cmd.Connection = conn
        cmd.CommandText = strCmdTxt
        cmd.CommandType = CommandType.Text
        If isTran Then
            cmd.Transaction = trans
        End If
        da.SelectCommand = cmd
        da.Fill(ds)
        If Not isTran Then
            conn.Close()
        End If
        Return ds

    End Function
    Public Sub New()
        If Not Connect() Then
            Exit Sub
        End If
    End Sub
End Class
