How to use DataGridView with database view?

[Martin]

Hi, I can use DataGridView with a database table, that works pretty
well - when I programatically add a new row (using DataTable.AddRow()
and TableAdapter.Update()) to the datatable it shows in the
DataGridView, when I delete it (using DataRow.Delete() and
TableAdapter.Update()), then it automatically disappears from the
DataGridView.

Now I need to show a database view in the DataGridView. But the view
is read-only of course, I can only change the tables that the view is
using. So now how can the DataGridView find out that the view content
has changed and update its own content when I change one of the view's
tables?

I found only one solution - manually call TableAdapter.Fill on the
view, but that clears the view and loads it again from the database -
that means that the DataGridView loses track of position of the cursor
and position in the list... is there any other solution that would act
as acts a table in the DataGridView?

 

[Cor Ligthert[MVP]]

Martin,

Have you binded using the the binding source the defaultview (or a dataview)
to the DataGridView?

Cor

 

[Martin]

Hi, I created everything in Visual Studio 2005 using visual editors.
The DataSet was created in data seteditor and the Form with
DataGridView in windows forms editor.
The view in dataset is created using drag&drop from Server Explorer
where I opened my SQL server database->Views->V_Item (thats name of
the view). In the DataGridView Tasks pane I have chosen that view as a
data source. The visual editor automatically created a binding source,
table adapter and the data set members of the Form.

The V_Item view is showing data from Item table and some other tables.
So I added also another DataGridView that I've bound to Item table.

When I add a row to the Item table using:

EvidenceDataSet.ItemRow irow=evidenceDataSet.Item.NewItemRow();
//here I fill the new row data to the irow variable
evidenceDataSet.Item.AddItemRow(irow);
itemTableAdapter.Update(evidenceDataSet.Item);

the DataGridView that is bound to the Item table gets updated and it
shows the new row. The data shows in the database, so the V_Item view
is changed also, but the DataGridView that is bound to the V_Item does
not get updated.

 

[Cor Ligthert[MVP]]

Martin,

The DataGridView shows the data as it is in its datasource. Be aware that
updating the datasource does not automaticly update the datatables as you
have created more.

For that you have to clean them and to fill again.
(Or any other way with the same result)

Cor

 

[Martin]

Hi Cor,

refilling the data after each change would be very annoying for the
user. How are applications that are using more complex databases doing
this? I don't see any reloading of the whole contents of the
DataGridView in them. And I doubt that they are showing data from one
table only - that must be sql views or data from more tables...

Martin

 

[Cor Ligthert[MVP]]

Martin,

Can be I understand you wrong, however in my idea are you talking about 2
datagridview which have not exact the same datasource.

Cor

 

[Martin]

Hi Cor,

the second DataGridView was only as a check that the data was updated
and that the auto-update feature works in DataGridView at all. So
forget on the second DataGridView, the main idea is a DataGridView
that has a sql view as a datasource (or any other datasource that in
results acts like a sql view) that gets updated when some of the sql
view's source tables changes.

Martin

 

[Cor Ligthert[MVP]]

Martin,

That you use an SQL view is not important for this.

The SQL server gives only back a resultset, which is placed in a (Data)table
in memory.

What happens is that the DataTable can be binded using the DefaultView and
the BindingSource to the DataGridView.Datasource.

The updates are always
DataTable > DataRow > SqlRow without that the DataGridView is involved.

Cor

 

[Martin]

Hi Cor,

I agree, but when I bind a sql table (now a view) as I wrote before,
the DataGridView does reflect changes in the DataTable that I make
programmatically. It gets messages about deleted rows, modified rows
and new rows added to the database. But when I use a sql view as a
source, there is no connection between the table that is modified and
the resulting view (I mean on the client side), so the DataGridView
does not get any notification that it should update some part of its
content. My question is, if there is some technique how to get these
notifications to the DataGridView so it updates only that rows that
are new/updated/deleted without refreshing the whole content of the
DataGridView - it losts track of its position, selections, redraws
itself and with bigger data it takes a lot of time = it's very
annoying to the user. Lots of programs do this, so there must some
way. Honestly I think that every a bit more complex database has to
use sql views for presenting data to the user, only some simple
databases can present a direct content of a table to the user...

Martin

 

[Cor Ligthert[MVP]]

Martin,

..Net uses disconnected data. This means that you only can get the latest
data from the SQL server as you get it from this server. This is unlike by
instance the recordset, which is connected. This is now common since version
2002. It is in fact inmpossible to get forever the latest data, it would
only complicate the problems for the user much more.

Cor