Doubt C # ADO.NET Class - Accessing Data from a DataTable

1

Hello, good evening, I'm new here and a beginner in C # programming.

I'm developing a CRUD system for vinyl records (a particular hahaha) using ADO.NET classes, similar to the article below. However, I'm not getting any progress in my rs project, I'm trying to search through the album name and / or artist / band name through a form, but I'm not able to succeed in my search.

Here's part of my project:

1) QueryAlbum class, where a DataTable returns.

class ConsultaAlbum
{
    DataAccess dataAccess = new DataAccess();

    public DataTable ConsultarAlbum(string pesquisaAlbum, string pesquisaArtista)
    {
        // Limpando os parãmetros
        dataAccess.LimparParametros();
        string SQL = @"SELECT 
                                       A.ID AS ID, 
                                       AR.ARTISTA AS ARTISTA, 
                                       GR.GRAVADORA AS GRAVADORA, 
                                       A.ALBUM AS ALBUM, 
                                       A.ANO AS ANO, 
                                       A.GENERO AS GENERO, 
                                       A.PRENSAGEM AS PRENSAGEM, 
                                       A.CORVINIL AS COR, 
                                       A.FORMATO AS FORMATO, 
                                       A.ROTACAO ROTACAO, 
                                       A.IMAGEM AS IMAGEM, 
                                       A.FLAG AS FLAG 
                       FROM 
                                       ALBUM AS A 
                                       INNER JOIN ARTISTA AS AR ON (A.ID_ARTISTA = AR.ID) 
                                       INNER JOIN GRAVADORA AS GR ON (A.ID_GRAVADORA = GR.ID)
                       WHERE 
                                       FLAG = 'A' AND 
                                       UPPER(ALBUM) LIKE '%@PESQUISA_ALBUM%' OR                                           
                                       UPPER(ARTISTA) LIKE'%@PESQUISA_ARTISTA%'";
        // Adicionando novos parâmetros
        dataAccess.AdicionarParametro("@PESQUISA_ALBUM", SqlDbType.VarChar, pesquisaAlbum);
        dataAccess.AdicionarParametro("@PESQUISA_ARTISTA", SqlDbType.VarChar, pesquisaArtista);
        // Retorna um DataTable com os dados da consulta
        return dataAccess.ExecutaConsulta(SQL);
    }

}
}

2) Search button, here is probably my problem, where I am not able to perform the search, for not getting any data in the DataTable, the data comes from a DataTable return of the above class, where I step by parameter the name of the artist and or the album, below is an example of how I'm trying to perform the search, through a foreach and accessing its lines, just how I'm trying to perform the search.

public partial class Consultar : UserControl
{
    DataAccess dataAccess = new DataAccess();

    public Consultar()
    {
        InitializeComponent();
    }
   private void btnPesquisarArtistaAlbum_Click(object sender, EventArgs e)
   {
        ConsultaAlbum consulta = new ConsultaAlbum();
        Album album = new Album();

        string pesquisaAlbum = "";
        string pesquisaArtista = "";

        pesquisaAlbum = txtPesquisarAlbum.Text;
        pesquisaArtista = txtPesquisarArtista.Text;

        //Pega os valores dos campos e seta nas propriedades das classes Artista, Gravadora e Album 
        consulta.ConsultarAlbum(pesquisaAlbum, pesquisaArtista);

        album.Artista = txtArtista.Text;
        txtAlbum.Text = album.NomeAlbum;
        txtGenero.Text = album.Genero;
        txtPrensagem.Text = album.Prensagem;
        txtGravadora.Text = album.Gravadora;
        //Tratar exceção!!
        //album.Ano = Convert.ToInt32(txtAno.Text);
        txtCor.Text = album.Cor;
        //Tratar exceção!!
        //(rButtonLp.Checked) ? "LP" : "EP" = album.Formato;
        //(rButton33.Checked) ? "33" : "45" = album.Rotacao;
        //Falta validar imagem em branco antes de salvar !!
        pictureVinyl.ImageLocation = album.Imagem;

    }

 }
}

3) Class Album

class Album
{
[Required]
public int Id { get; set; }

[Required(ErrorMessage = "Nome do álbum obrigatório")]
[RegularExpression(@"^[0-9a-zA-Z''''''''''''''''-''''''''\\\\\\\\s]{1,100}$", ErrorMessage = "Nome inválido")]
public string NomeAlbum { get; set; }


[RegularExpression(@"^[0-9a-zA-Z''''''''''''''''-''''''''\\\\\\\\s]{1,100}$", ErrorMessage = "Gênero inválido")]
public string Genero { get; set; }

[RegularExpression(@"^[0-9a-zA-Z''''''''''''''''-''''''''\\\\\\\\s]{1,100}$", ErrorMessage = "Prensagem inválida")]
public string Prensagem { get; set; }

[Required(ErrorMessage = "Ano do álbum obrigatório")]
[RegularExpression(@"^[0-9'-'\s]{4,4}$", ErrorMessage = "Ano inválido")]
public int Ano { get; set; }

[RegularExpression(@"^[a-zA-Z''''''''''''''''-''''''''\\\\\\\\s]{1,100}$", ErrorMessage = "Cor inválida")]
public string Cor { get; set; }

[Required]
public string Formato { get; set; }

[Required]
public string Rotacao { get; set; }

public string Imagem { get; set; }

public string Flag { get; set; }

public string Artista { get; set; }


public string Gravadora { get; set; }

}

I am developing this project in a self-taught way, and learning a lot from what I search, online courses, articles and doubts published here on the site. Thanks in advance for your help. Thanks =)

ADO.NET: Using DataAccess Class to Create DataAccess Class link

    
asked by anonymous 19.07.2018 / 02:55

2 answers

0

From what I see the condition "WHERE" does not seem right.

Should be "WHERE ... AND (... OR ...)"

 WHERE 
      A.FLAG = 'A' AND 
      (UPPER(A.ALBUM) LIKE '%@PESQUISA_ALBUM%' OR                                           
       UPPER(AR.ARTISTA) LIKE'%@PESQUISA_ARTISTA%')";
    
19.07.2018 / 11:23
0

Probably the clauses are conflicting, use parameters to set precedence when there is AND and OR in the same clause, otherwise the FLAG filter will only work in conjunction with the ALBUM but not with the ARTIST filter. Another important point is to remember that in SQL the comparison of anything with NULL will return false. So a better writing for the WHERE clause would be:



WHERE FLAG = 'A' AND (UPPER(ISNULL(ALBUM,'')) LIKE '%@PESQUISA_ALBUM%' OR                                       
  UPPER(ISNULL(ARTISTA,'')) LIKE '%@PESQUISA_ARTISTA%'")


    
21.07.2018 / 10:56