Select does not return anything

Question

Select does not return anything

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

I have the following method:

public DataTable Select(bool all = true, string campos = null)
{
    if (all && campos == null)
        _sql.Append("SELECT * FROM ");
    else
        _sql.Append("SELECT " + campos + " FROM ");
    _sql.Append(_tabela);

    _cmd = new SqlCommand(_sql.ToString(), _conexao);
    _conexao.Open();
    _cmd.ExecuteNonQuery();

    _dta = new SqlDataAdapter();
    DataSet ds = new DataSet();
    _dta.SelectCommand = _cmd;
    _dta.Fill(ds);

    _dt = ds.Tables[_tabela];

    return _dt;
}

No error is occurring, but select is coming empty, and the database contains value.

Is there another way to make a select and return the result in DataTable ?

sql sql-server c# .net wpf

asked by anonymous 02.01.2015 / 14:03

2 answers

0

try this:

public DataTable ObterDataTable(string conexao, string sql, string nomeTabela)
{
    DataTable dt = new DataTable(nomeTabela);
    using (SqlCommand cmd = new SqlCommand(sql, conexao))
    {                
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            da.Fill(dt);
        }
    }
    return dt;
}

02.01.2015 / 14:54

Java 8 - Collect elements from a list VideoView overlaying another videoView on TabActivity Android

score 1 · Accepted Answer

If you want to use DataSet , you can do it like this:

public DataTable Select(bool all = true, string campos = null)
{
    if (all && campos == null)
        _sql.Append("SELECT * FROM ");
    else
        _sql.Append("SELECT " + campos + " FROM ");
    _sql.Append(_tabela);

    _cmd = new SqlCommand(_sql.ToString(), _conexao);
    _dta = new SqlDataAdapter();
    _dta.SelectCommand = _cmd;
    _dta.TableMappings.Add("Table", _tabela); \ mapeia a tabela
    DataSet ds = new DataSet();
    _dta.Fill(ds);

    _dt = ds.Tables[_tabela];
    return _dt;
}

The main problem was in mapping the table, something you were not doing (I found this information in this link ).

When you use Fill , you do not have to open and close the connection explicitly, because this is done automatically, but if you open the connection before calling Fill , it will remain open after the connection is executed .

You can do the same thing by directly using DataTable , like this:

public DataTable Select(bool all = true, string campos = null)
{
    if (all && campos == null)
        _sql.Append("SELECT * FROM ");
    else
        _sql.Append("SELECT " + campos + " FROM ");
    _sql.Append(_tabela);

    _cmd = new SqlCommand(_sql.ToString(), _conexao);
    _dta = new SqlDataAdapter();
    _dta.SelectCommand = _cmd;
    _dt = new DataTable(_tabela); // se ele não foi criado anteriormente
    _dta.Fill(_dt);

    return _dt;
}

The ExecuteNonQuery you used should be used when you want to execute some SQL statement that does not return records from your database, for example a UPDATE , DELETE , CREATE TABLE , etc.