master - detail architecture for dataTable.

[Mr. X.]

Hello.

I have two dataTables.
h - header.
l - lines.

just hypothetic situation :
The header represents an invoice,
and lines represent invoice lines.

I link the header to a datatable by BindingSource object, and also the
lines.

When choosing another row of the header, the lines are new one, that are
linked to new header_id (the key of header table).

I want to update only one time :
when I press OK button in the screen.
I can do changes to other header rows, and other lines (with not the same
header_id).
When I go to another line of the header (another header_id), there are new
lines, that are represented. I can change each line.

How can I do one update to the whole change I have made to tables ?
Can I lock only the records which I am intending to change ?

Thanks

 

[Cor Ligthert[MVP]]

Hello,

I'm not sure but I thought that Armin has given a reply some days ago to use
a dataset.
A dataset is nothing more than a very easily to serialize Class which has
two main members.

The DataTable collection and the Relation Collection.

To update it, you have however to create your own logic around the sequence
(you have to investigate the rowstate).
It means that you have to Create always parents first and delete always
childs first.

It is mostly a bunch of code. If you search on Internet for rowstate, you
find probably the samples which fits you the most

http://www.google.com/webhp?hl=en#h...taadapter+update&gs_rfai=&fp=ca6b5a4f84435186

Cor

 

[Armin Zingler]

Am 05.04.2010 19:25, schrieb Cor Ligthert[MVP]:

Hello,

I'm not sure but I thought that Armin has given a reply some days ago to use
a dataset.

I didn't answer Mr.X because, if I read 'BindingSource', I skip a message.

 

[Mr. X.]

Beyond "knowing" about the basic architecture,
I need to do some implementation in code.
(Sorry for this long post ... :| )

Now I got a problem for the following situation :
I have two datagrids on the same form.
first is header : dgHeader
detail is : dgDetails.

The header is the master, and dgDetails is the details.

I don't know if there is easy way doing this, and whether there is something
in VB.NET. for that case,
and I am not writing too much code for that ...

dgHeader first initialized its rows by, and I give some of my code :

Public Shared Sub initTable(ByRef dt As DataTable, ByRef conn As
MySqlConnection, ByVal whereClause As String)
Dim command As MySqlCommand
Dim adapter As MySqlDataAdapter
Dim sql As String

Try
dt.Rows.Clear()
sql = sqlSelect(dt, True)
If whereClause <> "" Then
sql = sqlSelect(dt, True) & " where " & whereClause
End If
command = New MySqlCommand(sql, conn)
adapter = New MySqlDataAdapter
adapter.SelectCommand = command
adapter.Fill(dt)
Catch
MsgBox(Err.Description) ' ...
End Try
End Sub

On first time in program, I initialize the table : init(HeaderTable,
mainConn, "")
HeaderTable is the header table.
DetailsTable is the details table.
mainConn is the connection.

....
Also when rowenter, and rowleave - I am trying to update the details table.

Private Sub dgHeader_RowEnter(ByVal sender As System.Object, ByVal e As
System.Windows.Forms.DataGridViewCellEventArgs) Handles dgHeader.RowEnter
Dim objRow As DataRow
Dim dgRow As DataGridViewRow
dgRow = dgHeader.Rows(e.RowIndex)
If dgRow.DataBoundItem Is Nothing Then Exit Sub
objRow = TryCast(dgRow.DataBoundItem, DataRowView).Row
If objRow.RowState <> DataRowState.Detached Then
dgDetails.DataBindings.Clear()
init(DetailsTable, mainConn, objRow("database_id"))
End If
End Sub

Private Sub dgHeader_RowLeave(ByVal sender As System.Object, ByVal e As
System.Windows.Forms.DataGridViewCellEventArgs) Handles dgHeader.RowLeave
Dim objRow As DataRow
Dim dgRow As DataGridViewRow
Dim dgRowUpd As DataGridViewRow
dgRow = dgHeader.Rows(e.RowIndex)
If dgDetails.CurrentRow Is Nothing Then Exit Sub

dgRowUpd = dgDetails.Rows(dgDetails.CurrentRow.Index)
If dgRow.DataBoundItem Is Nothing Then Exit Sub
objRow = TryCast(dgRowUpd.DataBoundItem, DataRowView).Row
If objRow.RowState <> DataRowState.Detached Then
objRow.AcceptChanges() ' **************************************
??? Is this the command I should use ?
End If
End Sub

on OK button (finally) I update the database (that's work fine).
....

Everything works fine, but for a situation that :
I am changing some row on the dgDetails.
On header I have two rows, and I am moving at the header from row 1 to row 2
Changes are not made, even I get into objRow.acceptChanges() (remark in
asterisks).

1. Is there any more simply code that solve the master/detail situation, as
I have described?
2. What may be the reason for that : changes are not made on acceptChanges.

Thanks

 

[Cor Ligthert[MVP]]

What are you expecting acceptchanges does?

I've the idea something completely different than is stated in the
documentation

 

[Mr. X.]

I know my code is not so good.
Many mistakes, etc ...
More then code, I just need the concept - I didn't find any around the
internet.
Isn't any easier way doing so ?

I need to walk thorough the code, and find out what's wrong.
First thing - I cannot figure out the previous row index when row is
changed.

AcceptRowChanges -
When there are more then one bindingSource, usually I can control the
changes of any bindingSource on one row (for that I use beginEdit, endEdit,
cancelEdit).
AcceptRowChanges - accept the whole changes of all bindingSource at the
specific row.

Thanks

 

[Mr. X.]

Also :
If I do applyChanges, and after that fill again the dataTable with data,
Should I truly write records to the database ?

Thanks

 

[Cor Ligthert[MVP]]

In my idea you use Acceptchanges, however let me make this messagethread not
to long.
The purpose of the Acceptchanges method is to remove all the previous values
and set the rowstate to unchanged.

An unchanged row will never be updated by the DataAdapters

Don't rely to much on what you think a method does, another one like this is
the dispose method.
From both methods, I also had the idea in the beginning, that I understood
what they would do because their name.

So I expected the dispose would release the object, what is of course
completely impossible to do from the object itself.

The problem is, Microsoft can never change an already given method name
because that gives breaking chances.

Cor

 

[Mr. X.]

I see there are not shortcuts.
I can do the whole process, by using my own collection object.
That would take much less time then looking for solution that Microsoft may
or may not provide,
but indeed I prefer Microsoft solution.
I have thought that somehow, Microsoft has solution for doing
master-details, and maybe I am wrong.

For AcceptChanges - I need after using that , method to renew the child's
grid (If the master record has been changed),
and if computer does not memorized the previous situation (whether the
children had been changed),
I have to update the record in DB (otherwise - where the are kept).

I need some examples.
I didn't find any around the internet (that use two grids ...)
I can start from a good sample

Thanks

 

[Cor Ligthert[MVP]]

This sample is a very easy one about two datagrids be aware that if you are
you are in the Net world to be precise about what grid you mean, there are
so endless amounts of grids, while a DataGridView from forms is a complete
different thing then a Grid from WPF

http://www.vb-tips.com/DataGridRelation.aspx

I've however nowhere an update sample, because that will probably be huge
because you have to write all SQL transact for every operation for the
Master and for the Child, in my idea are for this Stored Procedures better
because it is then easier to test.


Cor

 

[Mr. X.]

What you say, that bindsource doesn't make thing simplier,
and for my case, not to use the bindsource object ?

 

[Mr. X.]

Well ... Things are getting in progress.
Not quite simple as at first glance ...
Lot of code I made, and finally succeeded (except minor issues) doing code
with master-details.

Thanks