C#: DataTable getting only one row of the search result

Question

I'm having a sudden and strange problem with DataTable. I'm using C# with MySQL database to develop a system, and I'm trying to export custom reports. The problem is that, somehow, my DataTable is getting only one result (I've tested my query on MySQL and should be something like 30 results on the xls file and the DataTable). Strangely, these functions are used in other parts of the system to export other kinds of reports, and work perfectly. This is the select function that I'm using:

public DataTable selectBD(String tabela, String colunas) {
    var query = "SELECT " + colunas + " FROM " + tabela;
    var dt = new DataTable();

    Console.WriteLine("\n\n" + query + "\n\n");

    try
    {
        using (var command = new MySqlCommand(query, bdConn)) {
            MySqlDataReader reader = command.ExecuteReader();
            dt.Load(reader);
            reader.Close();
        }
    }
    catch (MySqlException) {
        return null;
    }
    bdConn.Close();

    return dt;
}

And this is my query:

SELECT 
    cpf_cnpj, nomeCliente, agenciaContrato, contaContrato, 
    regionalContrato, carteiraContrato, contratoContrato, 
    gcpjContrato, avalistaContrato, enderecoContrato, 
    telefoneContrato, dataChegadaContrato, dataFatoGerContrato,
    dataPrimeiraParcelaContrato, dataEmissaoContrato, valorPlanilhaDebitoContrato 
FROM 
    precadastro 
    INNER JOIN 
    contrato 
        ON precadastro.cpf_cnpj = contrato.FK_cpf_cnpj 
    LEFT JOIN faseprocessual 
        ON contrato.idContrato = faseprocessual.FK_idContrato

And that is the result of the query on SQLyog

I've tested and the DataTable returned by the function only receive the one row, and it's not the first row of the MySQL results. Someone had this kind of problem before?

this: "Persist Security Info = False;Server = localhost;DataBase = absadvocacia;Uid = root;Pwd = '''';" But besides that place, the connection with the database works perfectly. Login, insert, update, delete... Actually, I use even the same functions in other place (to export user monitoring report), and it works. — Patrick Pierre, Mar 29 '17 at 01:31

score 3 · Accepted Answer · edited May 23 '17 at 12:02

DataTable load expects primary key from your data (supplied by DataReader) and tries to guess it from passed rows. Since there's no such key, Load method guesses it's the first column (cpf_cnpj). But, values in that column aren't unique so the each row gets overwritten by next one, and the result is just one row in your DataTable. It's the issue that persist for years, and I'm not sure there's one solution to rule them all. :)

You can try:

change query so that some unique values get into first column (unfortunately, I can't see something unique in your screenshot) or concatenate two or more values to get unique value.
Prepare DataTable by yourself by creating columns (this mirroring structure of resultset) and then iterate through DataReader to copy data.
add some autoincrement value in your query (or make temporary table with auto_increment column then fill that table)

Last suggestion could be something like this (I haven't worked much with mySql, so this is some suggestion i have googled :)):

SELECT 
    @i:=@i+1 AS id,
    cpf_cnpj, nomeCliente, agenciaContrato, contaContrato, 
    regionalContrato, carteiraContrato, contratoContrato, 
    gcpjContrato, avalistaContrato, enderecoContrato, 
    telefoneContrato, dataChegadaContrato, dataFatoGerContrato,
    dataPrimeiraParcelaContrato, dataEmissaoContrato, valorPlanilhaDebitoContrato 
FROM 
    precadastro 
    INNER JOIN 
    contrato 
        ON precadastro.cpf_cnpj = contrato.FK_cpf_cnpj 
    LEFT JOIN faseprocessual 
        ON contrato.idContrato = faseprocessual.FK_idContrato
    CROSS JOIN (SELECT @i:= 0) AS i

here's answer on SO which uses auto number in query.

Thank you for the answer, it helped me a lot. I've tried to add "@i:=@i+1 AS id" and "(SELECT @i:= 0) AS i" and it worked on MySQL console and SQLyog, but not on my C# code and Visual Studio query editor. I've search a little more and find out that problably it's because it realy can't be done - it seems that it is blocked to prevent SQL injection. But so I've looked again on my Data Base and I had the primary key of my table "contrato" (wich in this case will always be unique), so I take it and ignore it when generating the .xls file and worked just fine. Thanks a lot man. — Patrick Pierre, Mar 30 '17 at 15:07

C#: DataTable getting only one row of the search result

1 Answers1