Typed dataset - how to I alter SelectCommand

[DG]

Hi,

Can anyone advise how I alter the SelectCommand in an auto-generated typed
dataset programmatically? The select command would be part of a search
condition built by user inputs in the GUI.

Thanks,

DG

 

[Cor Ligthert [MVP]]

DG,

You can at any moment before the fill change the Command (which is the
SelectCommand) in a DataAdapter

Cor

 

[DG]

Cor - here's the method I tried but it isn't working - the query should
crash this code (because it doesn't exist) but the code runs fine. Can you
advise where I alter the command before filling the dataset?

Thanks,

DG
using System;

using System.Data;

using System.Data.SqlClient;



namespace NorthwindDataAccess.CustomersDataSetTableAdapters

{

public partial class CustomersTableAdapter

{

public void UpdateSelectCommand()

{

SqlConnection conn = new
SqlConnection(NorthwindDataAccess.Properties.Settings.Default.NorthwindConnectionString);




SqlCommand cmd = new SqlCommand("customer_qry", conn);

cmd.CommandType = CommandType.StoredProcedure;



this.Adapter.SelectCommand = cmd;

}

}

}

And try to call this code from Form1 as follows:-

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using NorthwindDataAccess.CustomersDataSetTableAdapters;

using NorthwindDataAccess;



namespace NorthwindGUI

{

public partial class Form1 : Form

{

CustomersTableAdapter m_Adapter = new CustomersTableAdapter();

CustomersDataSet m_Customer = new CustomersDataSet();

public Form1()

{

InitializeComponent();

}



private void OnFormLoad(object sender, EventArgs e)

{

m_Adapter.UpdateSelectCommand();

// This shouldn't work as there is no 'customer_qry'
stored proc in the database

// - but it does...

m_Adapter.Fill(m_Customer.Customers);

...etc...

}

The 'UpdateSelectCommand' doesn't have any effect on the adapters select
command.

 

[Cor Ligthert [MVP]]

DG,

This looks crazy are you sure that the method is executed, at least there
should be an error in my opinion.

Cor

 

[DG]

This is the problem I'm having - it isn't altering the query so the whole
thing executes fine. I'm trying to alter the select (insert/delete/update)
commands within the table adapter generated along with the typed dataset but
I don't know how to 'break in' to the code.

I'm sure I must be missing something blindingly obvious but there's no
middle ground in the books I've read - either you use ADO directly to create
untyped data tables/sets (I'm comfortable with this) or you use the wizards
to created typed datasets/adapters. Nowhere seems to explain the process to
alter the table adapters using partial classes.

Much appreciated,

DG

 

[Cor Ligthert [MVP]]

DG,

As you dl I miss this documentation.

However if you go to your Solution Explorer (not with ASPNET 2.0
applications) and you click on the icon in top of that show all files, than
you will can see exactly the decriptions of the classes that are generated.

Be aware that the strongly typed dataset class from 2.0 does look in nothing
as the one from 1.x (the 2.0 ASPNET one is again completeley different).

I hope this helps something,

Cor

 

[DG]

Thanks for this Cor, I've found what I've been looking for (Brian - thanks
again if you're reading this).

I've created some commands in other functions but the following code shows
what I've been looking for:

public void AlterTableAdapterCommands()
{
// The commands built using the dataset/table adapter wizard
refer to the underlying
// table. Alter these commands to work with the stored
procedures in the database

// Initialize the adapter - you need to do this before assigning
commands
this.InitAdapter();

// Alter the select command
this.CommandCollection[0] = GetSelectCommand();
// Alter the insert, delete and update commands
this._adapter.DeleteCommand = GetDeleteCommand();
this._adapter.InsertCommand = GetInsertCommand();
this._adapter.UpdateCommand = GetUpdateCommand();
}

If anyone has refinements to this functionality I'd be interested to hear
about it.

Thanks,

DG