Selecting one row when working with typed datasets.

Question

I have a typed dataset in my project. I would like to populate a datatable with only one row instead of all rows. The selected row must be based on the primary key column. I know I could modify the designer code to achive this functionality however if I change the code in the designer I risk that this code will be deleted when I update my datased via designer in the future.

So I wanted to alter the SelectCommand not in the designer but just before firing up MyTypedTableAdapter.Fill method. The strange thing is that the designer does not create a SelectCommand! It creates all other commands but not this one. If it would create SelectCommand I could alter it in this way:

this.operatorzyTableAdapter.Adapter.SelectCommand.CommandText += " WHERE MyColumn = 1";

It is far from perfection but atleast I would not have to modify the designer's work. unfortunately as I said earlier the SelectCommand is not created. Instead designer creates something like this:

[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
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 Ope_OpeID, Ope_Kod, Ope_Haslo, Ope_Imie, Ope_Nazwisko FROM dbo.Operatorzy";
    this._commandCollection[0].CommandType = global::System.Data.CommandType.Text;
}

It doesn't make sense in my opinion. Why to create UpdateCommand, InsertCommand and DeleteCommand but do not create SelectCommand? I could bear with this but this._commandCollection is private so I cannot acces it outside of the class code.

I don't know how to get into this collection without changing the designer's code. The idea which I have is to expose the collection via partial class definition. However I want to introduce many typed datasets and I really don't want to create partial class definition for each of them.

Please note that I am using .NET 3.5.

I've found this article about accessing private properties but it concerns .NET 4.0

Thanks for your time.

Answer 1

In the Dataset designer, first configure a Table/Adapter pair with the basic (all rows) query. Make sure it works and updates/deletes etc.

Then right-click, "Add Query" and change the SQL text to include a "WHERE MyId = @Id". Make sure you keep the same columns. Select Next and call the generated method "FillById()".

Ad of course, use FillById(id) instead of Fill() at the right places.