How to bind DataGrid to SQL query ?

[Dmitry Sazonov]

I have 2 tables:
Customers (
Cust_id int,
Cust_name text,
Type_id int )
Customer_Types (
Type_id int,
Type_name text )

I have loaded them into DataSet. When I can bind DataGrid as:

DataView view = new DataView ( ds.Tables["Customers"] );
dataGrid1.DataSource = view;

But I need to bind query to DataGrid (or to ListView). SQL like that:
"SELECT Cust_id, Cust_name, Type_name FROM Customers
INNER JOIN Customer_Types ON Customer_Types.Type_id = Customers.Type_id"

I can not create DataView on SQL query. I can not bind Grid to anything else
than DataView? I can not bind ListView to anything.

Please, help. Sorry for dumb question.

 

[Ravikanth[MVP]]

Hi

Could you please breifly tell what is the error you are
getting.

Ravikanth

 

[Ravikanth[MVP]]

Hi

Could you please breifly tell what is the error you are
getting.

Ravikanth

 

[Kathleen Dollard]

Dmitry,

You are describing two problems... restricting columns and a join. In SQL
you can solve these together, in .NET you can't. One solution _IF you aren't
updating_ is to get an extra copy of your data from SQL Server - thus three
resultsets - Customers, Customer_Types, and joined table. You can actually
do this if you are updating, but all updates against a join will take
special code.

Restricting columns requires a GridTableStyles and GridColumnStyles. Bind
via the property pages and check out the generated code.

The join is more problematic. In this case, your join is for a lookup value.
I don't join for lookup values in Windows.Forms, except under duress (large
lookup lists). Instead I bind a combo box and let Windows.Forms do the work.
In the case of the @#%%^#^$^ datagrid, this requires non-trivial effort.
www.gotdotnet.com has examples of adding a cmbo box to a data grid. This is
the answer if your user should be able to change the customer type. You can
use similer techniques to capture the paint event of the cell and print out
your type_id, even if you don't want a combo.

Alternatively, you can create a new DataSet and copy the columns in the way
you want. This is slow, but will probably cause no discernable delay on
WIndows.Forms (I'd hesitate to use this on an app server). In this case you
are basically doing a manual join.

Or you could retrieve the data the way you want from your backend.

I don't know whether we should ever have a client side join or not. SQL
Server (and MSDE which is the same thing) offers a tremendous engine for
optimizing queries. I don'twant the overhead of such a sophisticated engine
client side. Nor am I excited about a non-performant solution that looks
just like a highly efficient back end tool. Stil, the current answers are a
pain.

 

[Dmitry Sazonov]

thank you very much. I will look at these examples and I hope it will help.
Anyway, I feel what Microsoft did a huge job with ADO.NET and Windows forms,
but they did nothing for this very important issue: JOINs on the client
side...

Dmitry,

You are describing two problems... restricting columns and a join. In SQL
you can solve these together, in .NET you can't. One solution _IF you aren't
updating_ is to get an extra copy of your data from SQL Server - thus three
resultsets - Customers, Customer_Types, and joined table. You can actually
do this if you are updating, but all updates against a join will take
special code.

Restricting columns requires a GridTableStyles and GridColumnStyles. Bind
via the property pages and check out the generated code.

The join is more problematic. In this case, your join is for a lookup value.
I don't join for lookup values in Windows.Forms, except under duress (large
lookup lists). Instead I bind a combo box and let Windows.Forms do the work.
In the case of the @#%%^#^$^ datagrid, this requires non-trivial effort.
www.gotdotnet.com has examples of adding a cmbo box to a data grid. This is
the answer if your user should be able to change the customer type. You can
use similer techniques to capture the paint event of the cell and print out
your type_id, even if you don't want a combo.

Alternatively, you can create a new DataSet and copy the columns in the way
you want. This is slow, but will probably cause no discernable delay on
WIndows.Forms (I'd hesitate to use this on an app server). In this case you
are basically doing a manual join.

Or you could retrieve the data the way you want from your backend.

I don't know whether we should ever have a client side join or not. SQL
Server (and MSDE which is the same thing) offers a tremendous engine for
optimizing queries. I don'twant the overhead of such a sophisticated engine
client side. Nor am I excited about a non-performant solution that looks
just like a highly efficient back end tool. Stil, the current answers are a
pain.

--
Kathleen (MVP-VB)

I have 2 tables:
Customers (
Cust_id int,
Cust_name text,
Type_id int )
Customer_Types (
Type_id int,
Type_name text )

I have loaded them into DataSet. When I can bind DataGrid as:

DataView view = new DataView ( ds.Tables["Customers"] );
dataGrid1.DataSource = view;

But I need to bind query to DataGrid (or to ListView). SQL like that:
"SELECT Cust_id, Cust_name, Type_name FROM Customers
INNER JOIN Customer_Types ON Customer_Types.Type_id = Customers.Type_id"

I can not create DataView on SQL query. I can not bind Grid to anything else
than DataView? I can not bind ListView to anything.

Please, help. Sorry for dumb question.