Imports System.Data.SqlServerCe
Imports System.Data
Imports CamadaDTO
Imports System.Collections.Generic
Public Class EnsaioAmostraDAL
    Public Function DeletaEnsaioAmostraPorAmostra(ByVal numeroEnsaio As Integer, ByVal numeroAmostra As Integer) As Boolean
        Dim strSql As String
        Dim objDB As AcessoDados
        strSql = "Delete from ensaio_amostra where numeroEnsaioAmostra =" & numeroAmostra & "numeroEnsaio =" & numeroEnsaio
        objDB = New AcessoDados
        objDB.ExecuteQuery(strSql)
        Return True
    End Function

    Public Function DeletaEnsaioAmostraPorEnsaio(ByVal numeroEnsaio As Integer, ByVal numeroAmostra As Integer) As Boolean
        Dim strSql As String
        Dim objDB As AcessoDados
        strSql = "Delete from ensaio_amostra where numeroEnsaio =" & numeroEnsaio
        objDB = New AcessoDados
        objDB.ExecuteQuery(strSql)
        Return True
    End Function

    Public Function GetEnsaioAmostras() As List(Of EnsaioAmostra)
        Dim objdb As New AcessoDados
        Dim strSql As String = ""
        strSql = "Select * from ensaio_amostra"
        Dim dataReader As SQLCEdataReader = objdb.ExecuteAndGetReader(strSql)
        Dim lista As New List(Of EnsaioAmostra)
        While dataReader.Read
            Dim ensAmotra As EnsaioAmostra = New EnsaioAmostra
            ensAmotra.numeroEnsaio = IIf(IsDBNull(dataReader("numeroEnsaio")), 0, dataReader("numeroEnsaio"))
            ensAmotra.numeroEnsaioAmostra = IIf(IsDBNull(dataReader("numeroEnsaioAmostra")), 0, dataReader("numeroEnsaioAmostra"))
            ensAmotra.Codigo = IIf(IsDBNull(dataReader("codigo")), "", dataReader("codigo"))
            ensAmotra.responsavelColeta = IIf(IsDBNull(dataReader("responsavelColeta")), "", dataReader("responsavelColeta"))
            ensAmotra.identificacaoAmostra = IIf(IsDBNull(dataReader("identificacaoAmostra")), "", dataReader("identificacaoAmostra"))

            ensAmotra.dataColeta = IIf(IsDBNull(dataReader("dataColeta")), Date.Now, dataReader("dataColeta"))
            ensAmotra.dataEnsaio = IIf(IsDBNull(dataReader("dataEnsaio")), Date.Now, dataReader("dataEnsaio"))
            ensAmotra.dataValidade = IIf(IsDBNull(dataReader("dataValidade")), Date.Now, dataReader("dataValidade"))
            ensAmotra.dataLote = IIf(IsDBNull(dataReader("dataLote")), Date.Now, dataReader("dataLote"))
            ensAmotra.dataPreparoZinco = IIf(IsDBNull(dataReader("dataPreparaZinco")), Date.Now, dataReader("dataPreparaZinco"))
            ensAmotra.dataPreparoFenol = IIf(IsDBNull(dataReader("dataPreparaFenol")), Date.Now, dataReader("dataPreparaFenol"))
            ensAmotra.dataPreparoCromo = IIf(IsDBNull(dataReader("dataPreparaCromo")), Date.Now, dataReader("dataPreparaCromo"))

            ensAmotra.armazenamento = IIf(IsDBNull(dataReader("armazenamento")), "", dataReader("armazenamento"))
            ensAmotra.caracteristicas = IIf(IsDBNull(dataReader("caracteristicas")), "", dataReader("caracteristicas"))
            ensAmotra.valorPH = IIf(IsDBNull(dataReader("valorPH")), "", dataReader("valorPH"))
            ensAmotra.valorSalinidade = IIf(IsDBNull(dataReader("valorSalinidade")), "", dataReader("valorSalinidade"))
            ensAmotra.ajustePH = IIf(IsDBNull(dataReader("ajustePH")), "", dataReader("ajustePH"))
            ensAmotra.ajusteSalinidade = IIf(IsDBNull(dataReader("ajusteSalinidade")), "", dataReader("ajusteSalinidade"))
            ensAmotra.observacoesPreTratamento = IIf(IsDBNull(dataReader("observacoesPreTratamento")), "", dataReader("observacoesPreTratamento"))

            ensAmotra.bacteriaNome = IIf(IsDBNull(dataReader("bacteriaNome")), "", dataReader("bacteriaNome"))
            ensAmotra.bacteriaFonte = IIf(IsDBNull(dataReader("bacteriaFonte")), "", dataReader("bacteriaFonte"))
            ensAmotra.bacteriaNumeroLote = IIf(IsDBNull(dataReader("bacteriaNumeroLote")), "", dataReader("bacteriaNumeroLote"))
            ensAmotra.bacteriaCondicoesArmazenamento = IIf(IsDBNull(dataReader("bacteriaCondicoesArmazenamento")), "", dataReader("bacteriaCondicoesArmazenamento"))

            ensAmotra.solucaoZinco = IIf(IsDBNull(dataReader("solucaoZinco")), "", dataReader("solucaoZinco"))
            ensAmotra.concentracaoZinco = IIf(IsDBNull(dataReader("concentracaoZinco")), "", dataReader("concentracaoZinco"))

            ensAmotra.solucaoFenol = IIf(IsDBNull(dataReader("solucaoFenol")), "", dataReader("solucaoFenol"))
            ensAmotra.concentracaoFenol = IIf(IsDBNull(dataReader("concentracaoFenol")), "", dataReader("concentracaoFenol"))

            ensAmotra.solucaoCromo = IIf(IsDBNull(dataReader("solucaoCromo")), "", dataReader("solucaoCromo"))
            ensAmotra.concentracaoCromo = IIf(IsDBNull(dataReader("concentracaoCromo")), "", dataReader("concentracaoCromo"))

            ensAmotra.serieDiluicoes = IIf(IsDBNull(dataReader("serieDiluicoes")), 0, dataReader("serieDiluicoes"))
            ensAmotra.preDiluicao = IIf(IsDBNull(dataReader("preDiluicao")), "", dataReader("preDiluicao"))
            ensAmotra.numeroDiluicoesBloco = IIf(IsDBNull(dataReader("numeroDiluicoesBloco")), 0, dataReader("numeroDiluicoesBloco"))
            ensAmotra.concentracao = IIf(IsDBNull(dataReader("concentracao")), 0, dataReader("concentracao"))
            ensAmotra.numeroBloco = IIf(IsDBNull(dataReader("numeroBloco")), 0, dataReader("numeroBloco"))
            ensAmotra.unidade = IIf(IsDBNull(dataReader("unidade")), "", dataReader("unidade"))
            ensAmotra.observacoes = IIf(IsDBNull(dataReader("observacoes")), "", dataReader("observacoes"))


            ensAmotra.livreB1 = IIf(IsDBNull(dataReader("livreB1")), 0, dataReader("livreB1"))
            ensAmotra.livreC1 = IIf(IsDBNull(dataReader("livreC1")), 0, dataReader("livreC1"))
            ensAmotra.livreD1 = IIf(IsDBNull(dataReader("livreD1")), 0, dataReader("livreD1"))
            ensAmotra.livreA4 = IIf(IsDBNull(dataReader("livreA4")), 0, dataReader("livreA4"))
            ensAmotra.livreB4 = IIf(IsDBNull(dataReader("livreB4")), 0, dataReader("livreB4"))
            ensAmotra.livreC4 = IIf(IsDBNull(dataReader("livreC4")), 0, dataReader("livreC4"))
            ensAmotra.livreD4 = IIf(IsDBNull(dataReader("livreD4")), 0, dataReader("livreD4"))
            ensAmotra.livre2B1 = IIf(IsDBNull(dataReader("livre2B1")), 0, dataReader("livre2B1"))
            ensAmotra.livre2C1 = IIf(IsDBNull(dataReader("livre2C1")), 0, dataReader("livre2C1"))
            ensAmotra.livre2D1 = IIf(IsDBNull(dataReader("livre2D1")), 0, dataReader("livre2D1"))
            ensAmotra.livre2A4 = IIf(IsDBNull(dataReader("livre2A4")), 0, dataReader("livre2A4"))
            ensAmotra.livre2B4 = IIf(IsDBNull(dataReader("livre2B4")), 0, dataReader("livre2B4"))
            ensAmotra.livre2C4 = IIf(IsDBNull(dataReader("livre2C4")), 0, dataReader("livre2C4"))
            ensAmotra.livre2D4 = IIf(IsDBNull(dataReader("livre2D4")), 0, dataReader("livre2D4"))
            lista.Add(ensAmotra)
        End While
        dataReader.Close()
        Return lista
    End Function

    Public Function GetEnsaioAmostraPorId(ByVal numeroEnsaio As Integer, ByVal numeroAmostra As Integer) As EnsaioAmostra
        Dim objdb As New AcessoDados
        Dim strSql As String = ""
        strSql = "Select * from ensaio_amostra where numeroEnsaio = '" & numeroEnsaio & "' and numeroEnsaioAmostra = '" & numeroAmostra & "'"
        Dim dataReader As DataRow = objdb.ExecuteAndGetRow(strSql)
        Dim lista As New List(Of EnsaioAmostra)
        Dim ensAmotra As EnsaioAmostra = Nothing
        ensAmotra = New EnsaioAmostra
        ensAmotra.numeroEnsaio = IIf(IsDBNull(dataReader("numeroEnsaio")), 0, dataReader("numeroEnsaio"))
        ensAmotra.numeroEnsaioAmostra = IIf(IsDBNull(dataReader("numeroEnsaioAmostra")), 0, dataReader("numeroEnsaioAmostra"))
        ensAmotra.Codigo = IIf(IsDBNull(dataReader("codigo")), "", dataReader("codigo"))
        ensAmotra.responsavelColeta = IIf(IsDBNull(dataReader("responsavelColeta")), "", dataReader("responsavelColeta"))
        ensAmotra.identificacaoAmostra = IIf(IsDBNull(dataReader("identificacaoAmostra")), "", dataReader("identificacaoAmostra"))
        ensAmotra.localizacaoAmostra = IIf(IsDBNull(dataReader("localizacaoAmostras")), "", dataReader("localizacaoAmostras"))

        ensAmotra.dataColeta = IIf(IsDBNull(dataReader("dataColeta")), Date.Now, dataReader("dataColeta"))
        ensAmotra.dataEnsaio = IIf(IsDBNull(dataReader("dataEnsaio")), Date.Now, dataReader("dataEnsaio"))
        ensAmotra.dataValidade = IIf(IsDBNull(dataReader("dataValidade")), Date.Now, dataReader("dataValidade"))
        ensAmotra.dataLote = IIf(IsDBNull(dataReader("dataLote")), Date.Now, dataReader("dataLote"))
        ensAmotra.dataPreparoZinco = IIf(IsDBNull(dataReader("dataPreparaZinco")), Date.Now, dataReader("dataPreparaZinco"))
        ensAmotra.dataPreparoFenol = IIf(IsDBNull(dataReader("dataPreparaFenol")), Date.Now, dataReader("dataPreparaFenol"))
        ensAmotra.dataPreparoCromo = IIf(IsDBNull(dataReader("dataPreparaCromo")), Date.Now, dataReader("dataPreparaCromo"))

        ensAmotra.armazenamento = IIf(IsDBNull(dataReader("armazenamento")), "", dataReader("armazenamento"))
        ensAmotra.caracteristicas = IIf(IsDBNull(dataReader("caracteristicas")), "", dataReader("caracteristicas"))
        ensAmotra.valorPH = IIf(IsDBNull(dataReader("valorPH")), "", dataReader("valorPH"))
        ensAmotra.valorSalinidade = IIf(IsDBNull(dataReader("valorSalinidade")), "", dataReader("valorSalinidade"))
        ensAmotra.ajustePH = IIf(IsDBNull(dataReader("ajustePH")), "", dataReader("ajustePH"))
        ensAmotra.ajusteSalinidade = IIf(IsDBNull(dataReader("ajusteSalinidade")), "", dataReader("ajusteSalinidade"))
        ensAmotra.observacoesPreTratamento = IIf(IsDBNull(dataReader("observacoesPreTratamento")), "", dataReader("observacoesPreTratamento"))

        ensAmotra.bacteriaNome = IIf(IsDBNull(dataReader("bacteriaNome")), "", dataReader("bacteriaNome"))
        ensAmotra.bacteriaFonte = IIf(IsDBNull(dataReader("bacteriaFonte")), "", dataReader("bacteriaFonte"))
        ensAmotra.bacteriaNumeroLote = IIf(IsDBNull(dataReader("bacteriaNumeroLote")), "", dataReader("bacteriaNumeroLote"))
        ensAmotra.bacteriaCondicoesArmazenamento = IIf(IsDBNull(dataReader("bacteriaCondicoesArmazenamento")), "", dataReader("bacteriaCondicoesArmazenamento"))

        ensAmotra.solucaoZinco = IIf(IsDBNull(dataReader("solucaoZinco")), "", dataReader("solucaoZinco"))
        ensAmotra.concentracaoZinco = IIf(IsDBNull(dataReader("concentracaoZinco")), "", dataReader("concentracaoZinco"))

        ensAmotra.solucaoFenol = IIf(IsDBNull(dataReader("solucaoFenol")), "", dataReader("solucaoFenol"))
        ensAmotra.concentracaoFenol = IIf(IsDBNull(dataReader("concentracaoFenol")), "", dataReader("concentracaoFenol"))

        ensAmotra.solucaoCromo = IIf(IsDBNull(dataReader("solucaoCromo")), "", dataReader("solucaoCromo"))
        ensAmotra.concentracaoCromo = IIf(IsDBNull(dataReader("concentracaoCromo")), "", dataReader("concentracaoCromo"))

        ensAmotra.serieDiluicoes = IIf(IsDBNull(dataReader("serieDiluicoes")), "", dataReader("serieDiluicoes"))
        ensAmotra.preDiluicao = IIf(IsDBNull(dataReader("preDiluicao")), "", dataReader("preDiluicao"))
        ensAmotra.numeroDiluicoesBloco = IIf(IsDBNull(dataReader("numeroDiluicoesBloco")), 0, dataReader("numeroDiluicoesBloco"))
        ensAmotra.concentracao = IIf(IsDBNull(dataReader("concentracao")), "", dataReader("concentracao"))
        ensAmotra.numeroBloco = IIf(IsDBNull(dataReader("numeroBloco")), 0, dataReader("numeroBloco"))
        ensAmotra.unidade = IIf(IsDBNull(dataReader("unidade")), "", dataReader("unidade"))
        ensAmotra.observacoes = IIf(IsDBNull(dataReader("observacoes")), "", dataReader("observacoes"))


        ensAmotra.livreB1 = IIf(IsDBNull(dataReader("livreB1")), 0, dataReader("livreB1"))
        ensAmotra.livreC1 = IIf(IsDBNull(dataReader("livreC1")), 0, dataReader("livreC1"))
        ensAmotra.livreD1 = IIf(IsDBNull(dataReader("livreD1")), 0, dataReader("livreD1"))
        ensAmotra.livreA4 = IIf(IsDBNull(dataReader("livreA4")), 0, dataReader("livreA4"))
        ensAmotra.livreB4 = IIf(IsDBNull(dataReader("livreB4")), 0, dataReader("livreB4"))
        ensAmotra.livreC4 = IIf(IsDBNull(dataReader("livreC4")), 0, dataReader("livreC4"))
        ensAmotra.livreD4 = IIf(IsDBNull(dataReader("livreD4")), 0, dataReader("livreD4"))
        ensAmotra.livre2B1 = IIf(IsDBNull(dataReader("livre2B1")), 0, dataReader("livre2B1"))
        ensAmotra.livre2C1 = IIf(IsDBNull(dataReader("livre2C1")), 0, dataReader("livre2C1"))
        ensAmotra.livre2D1 = IIf(IsDBNull(dataReader("livre2D1")), 0, dataReader("livre2D1"))
        ensAmotra.livre2A4 = IIf(IsDBNull(dataReader("livre2A4")), 0, dataReader("livre2A4"))
        ensAmotra.livre2B4 = IIf(IsDBNull(dataReader("livre2B4")), 0, dataReader("livre2B4"))
        ensAmotra.livre2C4 = IIf(IsDBNull(dataReader("livre2C4")), 0, dataReader("livre2C4"))
        ensAmotra.livre2D4 = IIf(IsDBNull(dataReader("livre2D4")), 0, dataReader("livre2D4"))
        Return ensAmotra
    End Function


    Public Function GetEnsaioAmostraPorEnsaioAmostra(ByVal numeroEnsaio As Integer, ByVal numeroAmostra As Integer) As List(Of EnsaioAmostra)
        Dim objdb As New AcessoDados
        Dim strSql As String = ""
        strSql = "Select * from ensaio_amostra where numeroEnsaio = '" & numeroEnsaio & "' and numeroEnsaioAmostra = '" & numeroAmostra & "'"
        Dim dataReader As SQLCEDataReader = objdb.ExecuteAndGetReader(strSql)
        Dim lista As New List(Of EnsaioAmostra)
        Dim ensAmotra As EnsaioAmostra = Nothing
        While dataReader.Read
            ensAmotra = New EnsaioAmostra
            ensAmotra.numeroEnsaio = IIf(IsDBNull(dataReader("numeroEnsaio")), 0, dataReader("numeroEnsaio"))
            ensAmotra.numeroEnsaioAmostra = IIf(IsDBNull(dataReader("numeroEnsaioAmostra")), 0, dataReader("numeroEnsaioAmostra"))
            ensAmotra.Codigo = IIf(IsDBNull(dataReader("codigo")), "", dataReader("codigo"))
            ensAmotra.responsavelColeta = IIf(IsDBNull(dataReader("responsavelColeta")), "", dataReader("responsavelColeta"))
            ensAmotra.identificacaoAmostra = IIf(IsDBNull(dataReader("identificacaoAmostra")), "", dataReader("identificacaoAmostra"))
            ensAmotra.localizacaoAmostra = IIf(IsDBNull(dataReader("localizacaoAmostras")), "", dataReader("localizacaoAmostras"))

            ensAmotra.dataColeta = IIf(IsDBNull(dataReader("dataColeta")), Date.Now, dataReader("dataColeta"))
            ensAmotra.dataEnsaio = IIf(IsDBNull(dataReader("dataEnsaio")), Date.Now, dataReader("dataEnsaio"))
            ensAmotra.dataValidade = IIf(IsDBNull(dataReader("dataValidade")), Date.Now, dataReader("dataValidade"))
            ensAmotra.dataLote = IIf(IsDBNull(dataReader("dataLote")), Date.Now, dataReader("dataLote"))
            ensAmotra.dataPreparoZinco = IIf(IsDBNull(dataReader("dataPreparaZinco")), Date.Now, dataReader("dataPreparaZinco"))
            ensAmotra.dataPreparoFenol = IIf(IsDBNull(dataReader("dataPreparaFenol")), Date.Now, dataReader("dataPreparaFenol"))
            ensAmotra.dataPreparoCromo = IIf(IsDBNull(dataReader("dataPreparaCromo")), Date.Now, dataReader("dataPreparaCromo"))

            ensAmotra.armazenamento = IIf(IsDBNull(dataReader("armazenamento")), "", dataReader("armazenamento"))
            ensAmotra.caracteristicas = IIf(IsDBNull(dataReader("caracteristicas")), "", dataReader("caracteristicas"))
            ensAmotra.valorPH = IIf(IsDBNull(dataReader("valorPH")), "", dataReader("valorPH"))
            ensAmotra.valorSalinidade = IIf(IsDBNull(dataReader("valorSalinidade")), "", dataReader("valorSalinidade"))
            ensAmotra.ajustePH = IIf(IsDBNull(dataReader("ajustePH")), "", dataReader("ajustePH"))
            ensAmotra.ajusteSalinidade = IIf(IsDBNull(dataReader("ajusteSalinidade")), "", dataReader("ajusteSalinidade"))
            ensAmotra.observacoesPreTratamento = IIf(IsDBNull(dataReader("observacoesPreTratamento")), "", dataReader("observacoesPreTratamento"))

            ensAmotra.bacteriaNome = IIf(IsDBNull(dataReader("bacteriaNome")), "", dataReader("bacteriaNome"))
            ensAmotra.bacteriaFonte = IIf(IsDBNull(dataReader("bacteriaFonte")), "", dataReader("bacteriaFonte"))
            ensAmotra.bacteriaNumeroLote = IIf(IsDBNull(dataReader("bacteriaNumeroLote")), "", dataReader("bacteriaNumeroLote"))
            ensAmotra.bacteriaCondicoesArmazenamento = IIf(IsDBNull(dataReader("bacteriaCondicoesArmazenamento")), "", dataReader("bacteriaCondicoesArmazenamento"))

            ensAmotra.solucaoZinco = IIf(IsDBNull(dataReader("solucaoZinco")), "", dataReader("solucaoZinco"))
            ensAmotra.concentracaoZinco = IIf(IsDBNull(dataReader("concentracaoZinco")), "", dataReader("concentracaoZinco"))

            ensAmotra.solucaoFenol = IIf(IsDBNull(dataReader("solucaoFenol")), "", dataReader("solucaoFenol"))
            ensAmotra.concentracaoFenol = IIf(IsDBNull(dataReader("concentracaoFenol")), "", dataReader("concentracaoFenol"))

            ensAmotra.solucaoCromo = IIf(IsDBNull(dataReader("solucaoCromo")), "", dataReader("solucaoCromo"))
            ensAmotra.concentracaoCromo = IIf(IsDBNull(dataReader("concentracaoCromo")), "", dataReader("concentracaoCromo"))

            ensAmotra.serieDiluicoes = IIf(IsDBNull(dataReader("serieDiluicoes")), "", dataReader("serieDiluicoes"))
            ensAmotra.preDiluicao = IIf(IsDBNull(dataReader("preDiluicao")), "", dataReader("preDiluicao"))
            ensAmotra.numeroDiluicoesBloco = IIf(IsDBNull(dataReader("numeroDiluicoesBloco")), 0, dataReader("numeroDiluicoesBloco"))
            ensAmotra.concentracao = IIf(IsDBNull(dataReader("concentracao")), "", dataReader("concentracao"))
            ensAmotra.numeroBloco = IIf(IsDBNull(dataReader("numeroBloco")), 0, dataReader("numeroBloco"))
            ensAmotra.unidade = IIf(IsDBNull(dataReader("unidade")), "", dataReader("unidade"))
            ensAmotra.observacoes = IIf(IsDBNull(dataReader("observacoes")), "", dataReader("observacoes"))


            ensAmotra.livreB1 = IIf(IsDBNull(dataReader("livreB1")), 0, dataReader("livreB1"))
            ensAmotra.livreC1 = IIf(IsDBNull(dataReader("livreC1")), 0, dataReader("livreC1"))
            ensAmotra.livreD1 = IIf(IsDBNull(dataReader("livreD1")), 0, dataReader("livreD1"))
            ensAmotra.livreA4 = IIf(IsDBNull(dataReader("livreA4")), 0, dataReader("livreA4"))
            ensAmotra.livreB4 = IIf(IsDBNull(dataReader("livreB4")), 0, dataReader("livreB4"))
            ensAmotra.livreC4 = IIf(IsDBNull(dataReader("livreC4")), 0, dataReader("livreC4"))
            ensAmotra.livreD4 = IIf(IsDBNull(dataReader("livreD4")), 0, dataReader("livreD4"))
            ensAmotra.livre2B1 = IIf(IsDBNull(dataReader("livre2B1")), 0, dataReader("livre2B1"))
            ensAmotra.livre2C1 = IIf(IsDBNull(dataReader("livre2C1")), 0, dataReader("livre2C1"))
            ensAmotra.livre2D1 = IIf(IsDBNull(dataReader("livre2D1")), 0, dataReader("livre2D1"))
            ensAmotra.livre2A4 = IIf(IsDBNull(dataReader("livre2A4")), 0, dataReader("livre2A4"))
            ensAmotra.livre2B4 = IIf(IsDBNull(dataReader("livre2B4")), 0, dataReader("livre2B4"))
            ensAmotra.livre2C4 = IIf(IsDBNull(dataReader("livre2C4")), 0, dataReader("livre2C4"))
            ensAmotra.livre2D4 = IIf(IsDBNull(dataReader("livre2D4")), 0, dataReader("livre2D4"))
            lista.Add(ensAmotra)
        End While
        dataReader.Close()
        Return lista
    End Function

    Public Function GetEnsaioAmostrasPorEnsaio(ByVal numeroEnsaio As Integer) As List(Of EnsaioAmostra)
        Dim objdb As New AcessoDados
        Dim strSql As String = ""
        strSql = "Select * from ensaio_amostra where numeroEnsaio= " & numeroEnsaio
        Dim dataReader As SQLCEDataReader = objdb.ExecuteAndGetReader(strSql)
        Dim lista As New List(Of EnsaioAmostra)
        Dim ensAmotra As EnsaioAmostra = Nothing
        While dataReader.Read
            ensAmotra = New EnsaioAmostra
            ensAmotra.numeroEnsaio = IIf(IsDBNull(dataReader("numeroEnsaio")), 0, dataReader("numeroEnsaio"))
            ensAmotra.numeroEnsaioAmostra = IIf(IsDBNull(dataReader("numeroEnsaioAmostra")), 0, dataReader("numeroEnsaioAmostra"))
            ensAmotra.Codigo = IIf(IsDBNull(dataReader("codigo")), "", dataReader("codigo"))
            ensAmotra.responsavelColeta = IIf(IsDBNull(dataReader("responsavelColeta")), "", dataReader("responsavelColeta"))
            ensAmotra.identificacaoAmostra = IIf(IsDBNull(dataReader("identificacaoAmostra")), "", dataReader("identificacaoAmostra"))

            ensAmotra.dataColeta = IIf(IsDBNull(dataReader("dataColeta")), Date.Now, dataReader("dataColeta"))
            ensAmotra.dataEnsaio = IIf(IsDBNull(dataReader("dataEnsaio")), Date.Now, dataReader("dataEnsaio"))
            ensAmotra.dataValidade = IIf(IsDBNull(dataReader("dataValidade")), Date.Now, dataReader("dataValidade"))
            ensAmotra.dataLote = IIf(IsDBNull(dataReader("dataLote")), Date.Now, dataReader("dataLote"))
            ensAmotra.dataPreparoZinco = IIf(IsDBNull(dataReader("dataPreparaZinco")), Date.Now, dataReader("dataPreparaZinco"))
            ensAmotra.dataPreparoFenol = IIf(IsDBNull(dataReader("dataPreparaFenol")), Date.Now, dataReader("dataPreparaFenol"))
            ensAmotra.dataPreparoCromo = IIf(IsDBNull(dataReader("dataPreparaCromo")), Date.Now, dataReader("dataPreparaCromo"))

            ensAmotra.armazenamento = IIf(IsDBNull(dataReader("armazenamento")), "", dataReader("armazenamento"))
            ensAmotra.caracteristicas = IIf(IsDBNull(dataReader("caracteristicas")), "", dataReader("caracteristicas"))
            ensAmotra.valorPH = IIf(IsDBNull(dataReader("valorPH")), "", dataReader("valorPH"))
            ensAmotra.valorSalinidade = IIf(IsDBNull(dataReader("valorSalinidade")), "", dataReader("valorSalinidade"))
            ensAmotra.ajustePH = IIf(IsDBNull(dataReader("ajustePH")), "", dataReader("ajustePH"))
            ensAmotra.ajusteSalinidade = IIf(IsDBNull(dataReader("ajusteSalinidade")), "", dataReader("ajusteSalinidade"))
            ensAmotra.observacoesPreTratamento = IIf(IsDBNull(dataReader("observacoesPreTratamento")), "", dataReader("observacoesPreTratamento"))

            ensAmotra.bacteriaNome = IIf(IsDBNull(dataReader("bacteriaNome")), "", dataReader("bacteriaNome"))
            ensAmotra.bacteriaFonte = IIf(IsDBNull(dataReader("bacteriaFonte")), "", dataReader("bacteriaFonte"))
            ensAmotra.bacteriaNumeroLote = IIf(IsDBNull(dataReader("bacteriaNumeroLote")), "", dataReader("bacteriaNumeroLote"))
            ensAmotra.bacteriaCondicoesArmazenamento = IIf(IsDBNull(dataReader("bacteriaCondicoesArmazenamento")), "", dataReader("bacteriaCondicoesArmazenamento"))

            ensAmotra.solucaoZinco = IIf(IsDBNull(dataReader("solucaoZinco")), "", dataReader("solucaoZinco"))
            ensAmotra.concentracaoZinco = IIf(IsDBNull(dataReader("concentracaoZinco")), "", dataReader("concentracaoZinco"))

            ensAmotra.solucaoFenol = IIf(IsDBNull(dataReader("solucaoFenol")), "", dataReader("solucaoFenol"))
            ensAmotra.concentracaoFenol = IIf(IsDBNull(dataReader("concentracaoFenol")), "", dataReader("concentracaoFenol"))

            ensAmotra.solucaoCromo = IIf(IsDBNull(dataReader("solucaoCromo")), "", dataReader("solucaoCromo"))
            ensAmotra.concentracaoCromo = IIf(IsDBNull(dataReader("concentracaoCromo")), "", dataReader("concentracaoCromo"))

            ensAmotra.serieDiluicoes = IIf(IsDBNull(dataReader("serieDiluicoes")), 0, dataReader("serieDiluicoes"))
            ensAmotra.preDiluicao = IIf(IsDBNull(dataReader("preDiluicao")), "", dataReader("preDiluicao"))
            ensAmotra.numeroDiluicoesBloco = IIf(IsDBNull(dataReader("numeroDiluicoesBloco")), 0, dataReader("numeroDiluicoesBloco"))
            ensAmotra.concentracao = IIf(IsDBNull(dataReader("concentracao")), 0, dataReader("concentracao"))
            ensAmotra.numeroBloco = IIf(IsDBNull(dataReader("numeroBloco")), 0, dataReader("numeroBloco"))
            ensAmotra.unidade = IIf(IsDBNull(dataReader("unidade")), "", dataReader("unidade"))
            ensAmotra.observacoes = IIf(IsDBNull(dataReader("observacoes")), "", dataReader("observacoes"))


            ensAmotra.livreB1 = IIf(IsDBNull(dataReader("livreB1")), 0, dataReader("livreB1"))
            ensAmotra.livreC1 = IIf(IsDBNull(dataReader("livreC1")), 0, dataReader("livreC1"))
            ensAmotra.livreD1 = IIf(IsDBNull(dataReader("livreD1")), 0, dataReader("livreD1"))
            ensAmotra.livreA4 = IIf(IsDBNull(dataReader("livreA4")), 0, dataReader("livreA4"))
            ensAmotra.livreB4 = IIf(IsDBNull(dataReader("livreB4")), 0, dataReader("livreB4"))
            ensAmotra.livreC4 = IIf(IsDBNull(dataReader("livreC4")), 0, dataReader("livreC4"))
            ensAmotra.livreD4 = IIf(IsDBNull(dataReader("livreD4")), 0, dataReader("livreD4"))
            ensAmotra.livre2B1 = IIf(IsDBNull(dataReader("livre2B1")), 0, dataReader("livre2B1"))
            ensAmotra.livre2C1 = IIf(IsDBNull(dataReader("livre2C1")), 0, dataReader("livre2C1"))
            ensAmotra.livre2D1 = IIf(IsDBNull(dataReader("livre2D1")), 0, dataReader("livre2D1"))
            ensAmotra.livre2A4 = IIf(IsDBNull(dataReader("livre2A4")), 0, dataReader("livre2A4"))
            ensAmotra.livre2B4 = IIf(IsDBNull(dataReader("livre2B4")), 0, dataReader("livre2B4"))
            ensAmotra.livre2C4 = IIf(IsDBNull(dataReader("livre2C4")), 0, dataReader("livre2C4"))
            ensAmotra.livre2D4 = IIf(IsDBNull(dataReader("livre2D4")), 0, dataReader("livre2D4"))
            lista.Add(ensAmotra)
        End While
        dataReader.Close()
        Return lista

    End Function

    Public Function SalvaEnsaioAmostra(ByVal _EnsaioAmostra As EnsaioAmostra) As Boolean
        Dim strsql As String = ""
        Dim objDB As New AcessoDados
        If _EnsaioAmostra.Acao = FlagAcao.Insert Then
            strsql = "Insert into ensaio_amostra (numeroEnsaio, numeroEnsaioAmostra, codigo) " & "values( '" & _EnsaioAmostra.numeroEnsaio & " ', " & _EnsaioAmostra.numeroEnsaioAmostra & "," & _EnsaioAmostra.Codigo & ")"
        ElseIf _EnsaioAmostra.Acao = FlagAcao.Update Then
            strsql = "Update ensaio_amostra set" & "codigo='" & _EnsaioAmostra.Codigo & "'," & " identificacao=" & _EnsaioAmostra.identificacaoAmostra & "' " & " where numeroEnsaio=" & _EnsaioAmostra.numeroEnsaio & " and numeroEnsaioAmostra=" & _EnsaioAmostra.numeroEnsaioAmostra
        End If
        objDB.ExecuteQuery(strsql)
        Return True
    End Function

    Public Function SalvaEnsaioAmostraNova(ByVal numeroEnsaio As Integer, ByVal numeroAmostra As Integer) As Boolean
        Dim strsql As String = ""
        Dim existe As Boolean = True
        Dim objDB As New AcessoDados
        strsql = " Select * from ensaio_amostra where numeroEnsaio= '" & numeroEnsaio & "' and numeroEnsaioAmostra = '" & numeroAmostra & "'"
        existe = objDB.VerificaExiste(strsql)
        If existe = False Then
            strsql = "Insert into ensaio_amostra (numeroEnsaio, numeroEnsaioAmostra) " & "values( '" & numeroEnsaio & " ', " & numeroAmostra & ")"
        End If
        objDB.ExecuteQuery(strsql)
        Return True
    End Function

    Public Function SalvaAtributoEnsaioAmostra(ByVal numeroEnsaio As Integer, ByVal numeroAmostra As Integer, ByVal campo As String, ByVal valor As String) As Boolean
        Dim strsql As String = ""
        Dim objDB As New AcessoDados
        strsql = "Update ensaio_amostra set " & campo & " ='" & valor & "'  where numeroEnsaio= '" & numeroEnsaio & "' and numeroEnsaioAmostra= '" & numeroAmostra & "'"
        objDB.ExecuteQuery(strsql)
        Return True
    End Function

    Public Function SalvarFormBPL(ByVal numeroEnsaio As Integer, ByVal numeroAmostra As Integer, ByVal IdentificaAmostra As String, ByVal DataColeta As Date, ByVal DataEnsaio As Date, ByVal RespColeta As String, ByVal Armazenamento As String, ByVal Caracteristicas As String, ByVal valorPh As String, ByVal AjustePh As String, ByVal ValorSalinidade As String, ByVal AjusteSalinidade As String, ByVal ObsevacaoPre As String, ByVal BacteriaNome As String, ByVal BacteriaLote As String, ByVal DataLote As Date, ByVal DataValidade As Date, ByVal CondArma As String, ByVal SolucaoZinco As String, ByVal ConcZinco As String, ByVal dataPreparoZinco As Date, ByVal SoluFenol As String, ByVal ConcFenol As String, ByVal dataPreparoFenol As Date, ByVal SoluCromo As String, ByVal ConceCromo As String, ByVal DataPreparoCromo As Date)
        Dim DataColeta1 As String = DataColeta.Year.ToString() & "/" & DataColeta.Month.ToString() & "/" & DataColeta.Day.ToString()
        Dim DataEnsaio1 As String = DataColeta.Year.ToString() & "/" & DataEnsaio.Month.ToString() & "/" & DataEnsaio.Day.ToString()
        Dim DataLote1 As String = DataLote.Year.ToString() & "/" & DataLote.Month.ToString() & "/" & DataLote.Day.ToString()
        Dim DataValidade1 As String = DataValidade.Year.ToString() & "/" & DataValidade.Month.ToString() & "/" & DataValidade.Day.ToString()
        Dim dataPreparoZinco1 As String = dataPreparoZinco.Year.ToString() & "/" & dataPreparoZinco.Month.ToString() & "/" & dataPreparoZinco.Day.ToString()
        Dim dataPreparoFenol1 As String = dataPreparoFenol.Year.ToString() & "/" & dataPreparoFenol.Month.ToString() & "/" & dataPreparoFenol.Day.ToString()
        Dim DataPreparoCromo1 As String = DataPreparoCromo.Year.ToString() & "/" & DataPreparoCromo.Month.ToString() & "/" & DataPreparoCromo.Day.ToString()
        Dim strsql As String = ""
        Dim objDB As New AcessoDados
        strsql = "Update ensaio_amostra set identificacaoAmostra ='" & IdentificaAmostra & "', dataColeta ='" & DataColeta1 & "', dataEnsaio ='" & DataEnsaio1 & "' , responsavelColeta ='" & RespColeta & "', armazenamento ='" & Armazenamento & "', caracteristicas ='" & Caracteristicas & "' ,valorPh ='" & valorPh & "', ajustePh ='" & AjustePh & "', valorSalinidade ='" & ValorSalinidade & "', ajusteSalinidade ='" & AjusteSalinidade & "', obsevacaoPre ='" & ObsevacaoPre & "', bacteriaNome ='" & BacteriaNome & "',  bacteriaNumeroLote ='" & BacteriaLote & "', dataLote ='" & DataLote1 & "', dataValidade ='" & DataValidade1 & "', bacteriaCondicoesArmazenamento ='" & CondArma & "', solucaoZinco ='" & SolucaoZinco & "' , concentracaoZinco ='" & ConcZinco & "', dataPreparaZinco ='" & dataPreparoZinco1 & "' , solucaoFenol ='" & SoluFenol & "' , concentracaoFenol ='" & ConcFenol & "' , dataPreparaFenol ='" & dataPreparoFenol1 & "' , solucaoCromo ='" & SoluCromo & "' , concentracaoCromo ='" & ConceCromo & "' , dataPreparaCromo ='" & DataPreparoCromo1 & "' where numeroEnsaio= '" & numeroEnsaio & "' and numeroEnsaioAmostra= '" & numeroAmostra & "'"
        objDB.ExecuteQuery(strsql)
        Return True

    End Function


    Public Function SalvarFormAmostras(ByVal numeroEnsaio As Integer, ByVal numeroAmostra As Integer, ByVal identificacaoAmostra As String, ByVal codigoAmostra As String, ByVal serieDiluicoes As String, ByVal preDiluicaos As String, ByVal numeroDoBloco As String, ByVal numeroDiluicaoBloco As String, ByVal concentracao As String, ByVal unidade As String, ByVal observacao As String, ByVal livre1 As String, ByVal livre2 As String, ByVal livre3 As String, ByVal livre4 As String, ByVal livre5 As String, ByVal livre6 As String, ByVal livre7 As String, ByVal livre8 As String, ByVal livre9 As String, ByVal livre10 As String, ByVal livre11 As String, ByVal livre12 As String, ByVal livre13 As String, ByVal livre14 As String)

        If preDiluicaos = "Livre" Then preDiluicaos = "1"
        Dim strsql As String = ""
        Dim objDB As New AcessoDados
        strsql = "Update ensaio_amostra set identificacaoAmostra ='" & identificacaoAmostra & "', codigo ='" & codigoAmostra & "', serieDiluicoes ='" & serieDiluicoes & "' , preDiluicao ='" & preDiluicaos & "', numeroBloco ='" & numeroDoBloco & "', numeroDiluicoesBloco ='" & numeroDiluicaoBloco & "' ,concentracao ='" & concentracao & "', unidade ='" & unidade & "', observacoes ='" & observacao & "', livreB1 ='" & livre1 & "', livreC1 ='" & livre2 & "', livreD1 ='" & livre3 & "', livreA4 ='" & livre4 & "', livreB4 ='" & livre5 & "', livreC4 ='" & livre6 & "', livreD4 ='" & livre7 & "', livre2B1 ='" & livre8 & "', livre2C1 ='" & livre9 & "' , livre2D1 ='" & livre10 & "', livre2A4 ='" & livre11 & "' , livre2B4 ='" & livre12 & "' , livre2C4 ='" & livre13 & "' , livre2D4='" & livre14 & "' where numeroEnsaio= '" & numeroEnsaio & "' and numeroEnsaioAmostra= '" & numeroAmostra & "'"
        objDB.ExecuteQuery(strsql)
        Return True

    End Function
End Class
