Doubt C# ADO.NET class - Accessing data from a Datatable

Question

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

I am developing a CRUD vinyl record system (a particular hahaha addiction), using ADO.NET classes, similar to the article below. However I am not able to advance in my project rs, I am trying to perform a search through the name of the album and or the name of the artist/ band, through a form, but I am not succeeding in my research.

Follow part of my project:

1) Query Class album, where returns a Datatable.

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, probably here is my problem, where I am not able to perform the search, by not getting any data in Datatable, the data comes from a return Datatable of the above class, where I pass by parameter the name of the artist and or the album, below is an example of how I am trying to carry out the search, through a foreach and accessing its lines, just how I am trying to carry out 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) Album Class

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 research, online courses, articles and questions published here on the site. Thanks for all your help. Thank you =)

***Article using for the creation of the Dataaccess class ADO.NET: https://www.devmedia.com.br/desenvolvendo-uma-classe-ado-net-para-acesso-a-dados-sql-server/32336

Answer 1

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

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

Answer 2

From what I see the "WHERE" condition doesn’t seem right.

It should be "WHERE ... AND (... OR ...)"

 WHERE 
      A.FLAG = 'A' AND 
      (UPPER(A.ALBUM) LIKE '%@PESQUISA_ALBUM%' OR                                           
       UPPER(AR.ARTISTA) LIKE'%@PESQUISA_ARTISTA%')";