Select returns nothing at all

Asked

Viewed 931 times

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;
}

There is no mistake happening, however select is coming empty, and the database contains value.

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

c# sql .net sql-server wpf

  • In place of " _cmd.Executenonquery();" use this: "_dta = _cmd.Executereader();"

  • @Metalus, I did this and now you’re making a mistake on the line_dta.Fill(ds); saying that there is already a DataReader for this Command open.

  • I’ll give you some additional information that I think is even more important. It may even be easier for you to identify the error. You do not create a variable in the method. This seems very wrong to me. It seems that you are reusing instance variables. This looks really wrong. I don’t like the use of underscore in front of variable names. You must be doing this because the variables must be getting very confused. You should isolate things as much as possible. Worse, you’re leaking resources and memory everywhere. That’s all you shouldn’t be doing. And I don’t even know if I should DataTable

  • @mustache what you suggested to wear instead of DataTable?

  • For this case a DataReader. DataTabele is a tricky even powerful, but so bad that create the Entity Framework to replace it. Besides being more powerful is better done. But the DataReader is all you need in most cases. Exchanging one for the other without solving the other problems will not help much.

  • @bigown would have like to post an example of how I would do for after using the DataReader show the data in a DataGrid?

  • I don’t know much about WPF but I think this is it https://social.msdn.microsoft.com/Forums/en-US/64c9d100-08e4-4f0e-82d0-900571dcf54d/datareader-using-wpf?forum=wpf and http://bytes.com/topic-sharp/answers/273697-bind-datareader-griddatagrid

  • @bigown, Thanks, the first link solved...

Show 3 more comments

2 answers

1


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

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 at that link).

When you use the Fill you do not need to open and close the connection explicitly, as this is done automatically, but if you open the connection before calling the Fill, it will remain open after the execution of the same.

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

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 that 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..

0

try like 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;
}

Browser other questions tagged c# sql .net sql-server wpf

You are not signed in. Login or sign up in order to post.