Imports System.Data.SqlServerCe
Imports CamadaDTO
Imports System.Collections.Generic
Public Class UsuarioDAL
    Public Function DeletaUsuario(ByVal NumeroUsuario As Integer) As Boolean
        Dim strSql As String
        Dim objdb As New AcessoDados
        Try
            strSql = "Delete from usuario where numeroUsuario = " & NumeroUsuario
            objdb.ExecuteQuery(strSql)
        Catch ex As Exception
            Throw New Exception
        End Try
        Return True
    End Function

    Public Function GetUsuarios() As List(Of Usuario)
        Dim strSql As String
        Dim objdb As New AcessoDados
        Dim lista As New List(Of Usuario)
        strSql = "Select * from ensaio"
        Dim dataReader As SqlCeDataReader = objdb.ExecuteAndGetReader(strSql)
        While dataReader.Read
            Dim ens As Usuario = New Usuario
            ens.numeroUsuario = IIf(IsDBNull(dataReader("numeroUsuario")), 0, dataReader("numeroUsuario"))
            ens.nome = IIf(IsDBNull(dataReader("nome")), "", dataReader("nome"))
            ens.codigo = IIf(IsDBNull(dataReader("codigo")), "", dataReader("codigo"))
            ens.funcao = IIf(IsDBNull(dataReader("funcao")), "", dataReader("funcao"))
            ens.senha = IIf(IsDBNull(dataReader("senha")), Date.Now, dataReader("senha"))
            ens.usuario = IIf(IsDBNull(dataReader("usuario")), 0, dataReader("usuario"))
            lista.Add(ens)
        End While
        dataReader.Close()
        Return lista
    End Function

    Public Function GetUsuarioPorNome(ByVal nome As String) As List(Of Usuario)
        Dim strSql As String
        Dim objdb As New AcessoDados
        Dim lista As New List(Of Usuario)

        strSql = "Select * from usuario where nome like '%" & nome & "%'"
        Dim dataReader As SqlCeDataReader = objdb.ExecuteAndGetReader(strSql)
        While dataReader.Read
            Dim ens As Usuario = New Usuario
            ens.numeroUsuario = IIf(IsDBNull(dataReader("numeroUsuario")), 0, dataReader("numeroUsuario"))
            ens.nome = IIf(IsDBNull(dataReader("nome")), "", dataReader("nome"))
            ens.codigo = IIf(IsDBNull(dataReader("codigo")), "", dataReader("codigo"))
            ens.funcao = IIf(IsDBNull(dataReader("funcao")), "", dataReader("funcao"))
            ens.senha = IIf(IsDBNull(dataReader("senha")), Date.Now, dataReader("senha"))
            ens.usuario = IIf(IsDBNull(dataReader("usuario")), 0, dataReader("usuario"))
            lista.Add(ens)
        End While

        dataReader.Close()
        Return lista
    End Function

    Public Function GetUsuarioPorNumero(ByVal numeroUsuario As Integer) As Usuario
        Dim strSql As String
        Dim objdb As New AcessoDados
        strSql = "Select * from usuario where numeroUsuario = '" & numeroUsuario & "'"
        Dim dataReader As DataRow = objdb.ExecuteAndGetRow(strSql)
        Dim ens As Usuario = New Usuario
        ens.numeroUsuario = IIf(IsDBNull(dataReader("numeroUsuario")), 0, dataReader("numeroUsuario"))
        ens.nome = IIf(IsDBNull(dataReader("nome")), "", dataReader("nome"))
        ens.codigo = IIf(IsDBNull(dataReader("codigo")), "", dataReader("codigo"))
        ens.funcao = IIf(IsDBNull(dataReader("funcao")), "", dataReader("funcao"))
        ens.senha = IIf(IsDBNull(dataReader("senha")), Date.Now, dataReader("senha"))
        ens.usuario = IIf(IsDBNull(dataReader("usuario")), 0, dataReader("usuario"))
        Return ens
    End Function

    Public Function GetUsuarioPorUsuario(ByVal usuario As String) As Usuario
        Dim strSql As String
        Dim objdb As New AcessoDados
        strSql = "Select * from usuario where usuario = '" & usuario & "'"
        Dim dataReader As DataRow = objdb.ExecuteAndGetRowUsuario(strSql)
        Dim ens As Usuario = New Usuario
        If dataReader Is Nothing Then
            Return ens
        Else
            ens.numeroUsuario = IIf(IsDBNull(dataReader("numeroUsuario")), 0, dataReader("numeroUsuario"))
            ens.nome = IIf(IsDBNull(dataReader("nome")), "", dataReader("nome"))
            ens.codigo = IIf(IsDBNull(dataReader("codigo")), "", dataReader("codigo"))
            ens.funcao = IIf(IsDBNull(dataReader("funcao")), "", dataReader("funcao"))
            ens.senha = IIf(IsDBNull(dataReader("senha")), Date.Now, dataReader("senha"))
            ens.usuario = IIf(IsDBNull(dataReader("usuario")), 0, dataReader("usuario"))
        End If

        Return ens
    End Function

    Public Function SalvarFormUsuario(ByVal _usuario As Usuario, ByVal numeroUsuario As Integer) As String
        Dim strSql As String
        Dim objdb As New AcessoDados

        If numeroUsuario.ToString = 0 Then
            strSql = "insert into usuario (nome, codigo, funcao, usuario, senha ) values( '" & _usuario.nome & "','" & _usuario.codigo & "','" & _usuario.funcao & "','" & _usuario.usuario & "','" & _usuario.senha & "')"

            numeroUsuario = objdb.ExecuteAndGetID(strSql)

            Return numeroUsuario

        Else
            strSql = " update usuario set" _
               & " nome = '" & _usuario.nome & "'" _
               & " , codigo = '" & _usuario.codigo & "'" _
               & " , funcao = '" & _usuario.funcao & "'" _
               & " , usuario = '" & _usuario.usuario & "'" _
               & " , senha = '" & _usuario.senha & "'" _
               & " where numeroUsuario= '" & numeroUsuario & "'"
            objdb.ExecuteQuery(strSql)
        End If

        Return numeroUsuario

    End Function

  

  
End Class
