How Do I change the where clause of the TableAdapter

[Rob Dob]

Hi,

in the old days, pre .Net 2.0 I could just modify the SQLCommand of the
SQlDataAdapter to reflect a different where clause and then issue a .Fill(),
and Whamo!!! everything worked.. How am I to accomplish the same task using
..NET 2.0 and the NEW "TableAdapter", I can't see myself creating 100's of
Queries using the wizard, is there anyway for me to just change the sql
Where clause at runtime? I have the TableAdapter bound to a DataGrid, and
am aware that there is a filter property, but I don't think setting this
filter property forces a reread from the database, and my my database
contains 100,000's of records.

any help would be very much appreciated..

thanks,

 

[Bart Mermuys]

hi,

Hi,

in the old days, pre .Net 2.0 I could just modify the SQLCommand of the
SQlDataAdapter to reflect a different where clause and then issue a
.Fill(), and Whamo!!! everything worked.. How am I to accomplish the same
task using .NET 2.0 and the NEW "TableAdapter", I can't see myself
creating 100's of Queries using the wizard, is there anyway for me to
just change the sql Where clause at runtime? I have the TableAdapter
bound to a DataGrid, and am aware that there is a filter property, but I
don't think setting this filter property forces a reread from the
database, and my my database contains 100,000's of records.

Since TableAdapters are partial classes you could extend them by adding
another partial class, eg:

SomeDataSet.cs
------------------

using System.Data.SqlClient;

namespace SomeWindowsApplication
{
namespace SomeDataSetTableAdapters
{
partial class SomeTableAdapter
{
public int FillBySelect(SomeDataSet.SomeDataTable Table, string
Select)
{
this.Adapter.SelectCommand = new SqlCommand(Select,
this.Connection);
if (this.ClearBeforeFill)
Table.Clear();

return this.Adapter.Fill(Table);
}
}
}
}

(You offcourse have to replace "Some" with the actual names you have and if
you prefer you can easily make it a "FillByWhere" instead.)

HTH,
Greetings

 

[Rob Dob]

Hi,

Thanks, that worked like a Champ!!!