Selectively filling a dataset

Question

I need to change what rows are grabbed from the database based upon a few flags. How can I do this? My current code is like this:

this.tblTenantTableAdapter.Fill(this.rentalEaseDataSet.tblTenant);

Say if I wanted only rows that the id was greater than 50, how would I do that?

Edit:

The code to access the database was autogenerated by the original programmer a long time ago though VisualStudio. I don't know exactly how to get a connection from the autogenerated code. If I could do that I know how to use the SqlDataAdapter

Answer 1

Why wouldn't you use a WHERE clause in your SQL query?

Also, I would hope you don't use your ID field for anything like this. If you just want the first 50 selections, you may want to use a TOP 50 clause or something similar.

For some info on how to do this with your TableAdapter: http://www.shiningstar.net/ASPNet_Articles/DataSet/DataSetProject7.aspx

Answer 2

I would imagine that the simplest way is to change whatever SQL query is running behind the scenes using a WHERE clause.

Answer 3

On the server side you can use plaid old SQL:

SELECT * FROM TBLTENANT WHERE id > 50

On the client side:

rentalEaseDataSet.tblTenant.DefaultView.RowFilter = "id > 50";

Answer 4

Your TableAdapter needs to have the CommandText specified to a SQL query with a WHERE clause.

    private void InitCommandCollection() {
        this._commandCollection = new global::System.Data.SqlClient.SqlCommand[1];
        this._commandCollection[0] = new global::System.Data.SqlClient.SqlCommand();
        this._commandCollection[0].Connection = this.Connection;
        this._commandCollection[0].CommandText = @"SELECT * FROM MyTable WHERE ID >50;";
        this._commandCollection[0].CommandType = global::System.Data.CommandType.Text;
    }

NOTE: The above query is just an example, not meant as a solution query. The format of the above code is used to edit the generated Table Adapter designer class's InitCommandCollection() method so you can specify your own SQL. It is possible your class already has some SQL in here, in which case you could just alter it.