VB.NET - Dataset/Dataview navigation with add/delete/modify and data on form

Situation:
Having on a tabpage: a textbox, a datetimepicker and a datagridview and 3 records in the dataset: ID=1,2,3 LastName='X','Y','Z' and StartDate='2010/1/1','2010/2/2','2010/3/3' order by StartDate desc.
The data of the most recent one (ID=3) is shown in the tbx/dtp to be modified and the other records in the datagridview. This is achieved by having 2 dataviews on the dataset where the first has a rowfilter making that only one record is withheld (.RowFilter="ID=3") and bound to the tbx and dtp, and the other view has a rowfilter making that all except the first one are shown in the grid (.RowFilter="ID<>3").
And now:
- Modifying the first dataview trough the tbx and/or dtp and saving the dataset works.
- Deleting the one record in the first dataview, getting the ID of the first record in the second dataview and adapting the rowfilters to shift the wole up works (thisID=dataview2.item(0)("ID") dataview1..RowFilter="ID=" & thisID and dataview2.RowFilter="ID<>" & thisID), except that I can't get the binding with the tbx and dtp to work.
Further deleting of the records in dataview1 works, but referring to dataview2 gets fuzzy because the deleted record is still at position '0': so how to skip the deleted records
Didn't even try to add a record to dataview1 and sending the current ID of dataview1 to dataview2 not even having an ID for the new record as it is only generated by the database after insertion (Identify).
I don't want to save intermediate results, as the user must be able to trow the whole corrected scheme away.

By the way: what is the difference betwwen .Delete() (only flags the RowState) and RemoveAt(T) (Actually removes it from the dataset) in relation to dataset.UpDate() when I finally push everything to the database?

I will try to answer a few of your questions:

1) By the way: what is the difference betwwen .Delete() (only flags the RowState) and RemoveAt(T) (Actually removes it from the dataset) in relation to dataset.UpDate() when I finally push everything to the database?

The Dataadapter's Update method examines the rowstae of each row and executes the appropriate DataAdapter command.
Row State     Command
Unchanged      skips
Delete         DeleteCommand
Add           InsertCommand
Modified        UpdateCommand

The .Delete method changes the row state to Deleted.  The RemoveAt method removes the row from the Dataset's datatable.  If the row is removed, the dataadapter's Update method never sees the row and it can not be deleted from the database.

2) When adding rows to a datatable with an identity column, I use negative integers for the ID column.  When the row is added to the database, the ID column will be assigned a value.

Dim newrowcnt as Integer = -1
...
nr("ID") = newrowcnt 
newrowcnt -= -1

or you can set the id columns properties to automatically increment the id column when adding a new row:
Assumes ID column is column 0

dt.Columns(0).AutoIncrement = True

dt.Columns(0).AutoIncrementSeed = -1

dt.Columns(0).AutoIncrementStep = -1

3) You can check the RowState to bypass processing of a deleted row:

If row.RowState <> DataRowState.Deleted Then
    ...
End If

Thanks Tom, I begin to see the light....

 

Just trying to cope with this:

In initial situation, no records, hence: DataSet.Tables(0).Rows.Add() and I initialise keyfields.

As one part of the keyfields is missing (and it isn't autoincrement), just before dataset.update I get me a new value for that field and update the dataset:

 

With DataSet.Tables(0).Rows(0)  'containing only added row

  .Item("ID") = newID   'obtained from elsewhere

  .EndEdit()

End With

 

But then it fails the DataSet.Update as it executes an Update iso Insert.

I see that I can do a .SetAdded to change this state, but it seems not logical that I should do that, and after trying this anyway, it seems that it can only be done on unchanged records anyhow ...

 

 

 

 

 

 

 

 

 

 

 

 

 

I would think that row(0) is not the added row.  The dataset contains all rows retrieved from the database that have not been removed.  If a row is added, it will be the last row in the dataset.  The ID column's value can not be determined until the row is inserted into the database.  Any non-duplicate value will work is why I suggested using a negative integer for the new row ID.  If you are adding the row using the Binding Navigator, I would set the ID column's properties as mentioned in my previous post.

DataSet.Tables(0).Columns("ID").AutoIncrement = True

DataSet.Tables(0).Columns("ID").AutoIncrementSeed = -1

DataSet.Tables(0).Columns("ID").AutoIncrementStep = -1

When a row is added to the dataset's datatable, the ID column's value will be set automatically.  The Row State will be "Added" and when the DataAdapter's Update method examines the row, the InsertCommand will be executed.

Reason I had the Added->modified was because I did an AcceptChanges at the end of the form load.
Reason I did this is because I enable the btnSave if a dataset has changes and ask the user if he want's to discard if btnCancel is used. To make that a form only opened to create a new record, but nothing entered would not ask for 'do you want to ...', I did the Acceptchanges making the added record to become 'UnChanged' and when I modified something to 'Modified'. Now I don't do this anymore, but I will have to find a way to overcome this scenario.

Now I'm left with this: when I save the form, I want to make sure its all or nothing, meaning that several datasets should save or not at all. I do this by setting a transaction and passing it along to each of the calls to the savechanges, but the problem is that or a error that the object is nothing hence setting the transaction failes (as code is now), or (with blok 1 & 2 swapped) error that a transaction need te be set.
I found something here http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic58825.aspx
but that would imply that I make/start the transaction after having initialized the commandbuilder.
I have a public connection that is always open, and the transaction is started before to combine multiple Updates.
What now...?

Public Function SaveChanges(ByRef ds As DataSet, ByRef da As SqlDataAdapter, ByVal sTable As String, Optional ByVal selectCmd As String = "", Optional ByVal trans As SqlTransaction = Nothing) As Boolean

Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da), msg$ = ""

If Not selectCmd = "" Then

 da.SelectCommand = New SqlCommand(selectCmd, connection, trans) 'connection is a public variable

End If

' block 1

cb.GetInsertCommand(True)

cb.GetUpdateCommand(True)

cb.GetDeleteCommand(True)

' blok 2
If trans IsNot Nothing Then

 da.InsertCommand.Transaction = trans

 da.UpdateCommand.Transaction = trans

 da.DeleteCommand.Transaction = trans

End If

Try

da.Update(ds, sTable)

SaveChanges = True

'don't forget to commit transaction in calling procedure

Catch exSQL As SqlException

'- if the table does not have a pk

'- not-Null violations

'- foreign-key violations

Select Case exSQL.Number

Case Else

msg = exSQL.Number & ": " & exSQL.Errors(0).Message

End Select

MsgBox(msg, MsgBoxStyle.Exclamation, "SaveChanges failed")

'don't forget to rollback transaction in calling procedure

Catch ex As Exception

MsgBox(ex.Message, MsgBoxStyle.Exclamation, "SaveChanges failed")

'don't forget to rollback transaction in calling procedure

End Try

cb.Dispose()

End Function

When the CommandBuilder generates the InsertCommand, DeleteCommand, and Update Command it uses the Select command.  The SelectCommand must be set before executing the CommandBuilder. 

 

If Not selectCmd = "" Then

 da.SelectCommand = New SqlCommand(selectCmd, connection, trans) 'connection is a public variable

End If
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)

The Block 1 code is not needed

I would change

SaveChanges = True

to
Return True

and a Return False at the end of each Catch block.

And put the cb.Dispose in the Finally block

Finally
   cb.Dispose
End Try

the

If Not selectCmd = "" Then

 da.SelectCommand = New SqlCommand(selectCmd, CONSyndic, trans)

End If

 

is only for when I need to override the original select command in case of joins where finally a simple select is needed to get the update done
Point is that the original dataset is set on a form-level and when the dataset is loaded the original .Selectcommand is also set, but at that time no transaction was yet defined, making that at this point (when setting the InsertCommand.Transaction) I have an error
For completeness I changed this (for in case I use a selectcommand override), but doesn't seem to help in solving my problem

If selectCmd = "" Then

da.SelectCommand.Transaction = trans

Else

da.SelectCommand = New SqlCommand(selectCmd, connection, trans)

End If

 

But I still get the error (Object reference not set to an instance of an object.) when setting the transaction on the insert/modify/delete commands.
I suppose that setting transaction already at the initial dataset creation would maybe solve my problem, but I can't imagine this to be good practice, because I can take minutes before one saves the result...

For the SaveChanges=True -> Return True and the cb.dispose in the Finally-block:
I'm always in doubt wheter a return will exit the function without executing some statements at the end. I believe that the Finally-block is made therefore. Is a 'Return False' not the default?

It's a good practice to keep all database activity in separate classes/methods instead of having them scattered throughout the program.  Leaving the connection open is a poor practicce.  I would modify the code as follows:

Create a Structure to contain the data passed to your update routine:
.

Public Structure UpdateDS

Private mDS As DataSet

Private mSelCmd As String

Private mTable As String

Public Sub New(ByVal ds As DataSet, ByVal selcmd As String, ByVal TableName As String)

mDS = ds

mSelCmd = selcmd

mTable = TableName

End Sub

Public Property DS() As DataSet

Get

Return mDS

End Get

Set(ByVal value As DataSet)

mDS = value

End Set

End Property

Public Property SelectCommand() As String

Get

Return mSelCmd

End Get

Set(ByVal value As String)

mSelCmd = value

End Set

End Property

Public Property TableName() As String

Get

Return mTable

End Get

Set(ByVal value As String)

mTable = value

End Set

End Property

End Structure

Use a List(Of UpdateDS) to pass data to your update routine

Public Sub Save()

Dim mylist As New List(Of UpdateDS)

mylist.Add(New UpdateDS(ds, "Select ....", "Table1"))

mylist.Add(New UpdateDS(ds1, "Select ....", "Table2"))

mylist.Add(New UpdateDS(ds2, "Select ....", "Table3"))

SaveChanges(mylist, True)

End Sub

Update Routine:

Public Function SaveChanges(ByVal colDS As List(Of UpdateDS), ByVal trans As Boolean) As Boolean

Dim connection As New SqlConnection(strConnection)

Dim transaction As SqlTransaction

If trans Then

connection.Open()

transaction = connection.BeginTransaction

End If

Try

For Each itm As UpdateDS In colDS

Dim cmd As New SqlCommand(itm.SelectCommand, connection)

Dim da As New SqlDataAdapter(cmd)

Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)

If trans Then

da.InsertCommand.Transaction = transaction

da.DeleteCommand.Transaction = transaction

da.UpdateCommand.Transaction = transaction

End If

da.Update(itm.DS, itm.TableName)

Next

If trans Then

transaction.Commit()

connection.Close()

End If

Return True

Catch sqlex As SqlException

If trans Then

transaction.Rollback()

End If

MessageBox.Show(sqlex.Message)

Return False

Catch ex As Exception

If trans Then

transaction.Rollback()

End If

MessageBox.Show(ex.Message)

Return False

Finally

If connection.State = ConnectionState.Open Then

connection.Close()

End If

End Try

End Function

From MSDN:

A few comments about the above code:

1) The connection is explicitly opened only when using transactions.  The dataadpater's Update method will automatically open and close the connection if it's closed when executed.

2)  The "handshake" between your application and the database only occurs the first time the connection is used even though the code is creating a new connection object each time the SavesChanges routine is executed, as long as the connection string remains the same.  Research Connection Pooling.

3) Keeping database objects in separate methods makes it much easier to switch databases.  If SQLDataadpter objects, etc. are scattered through out the code, it's a time consuming task to switch to say an Oracle database.

4) any number of datasets can be passed to the SaveChanges routine, whether using transaction or not. 

5) research N-Tier architecture, (User Interface, Business Layer, Data Tier)

The more I learn, the more I realize I know verry little...
My biggest handicap is that I started with VB1 (yes, ONE, MS-DOS and even Basica before that), so it is sometimes hard to lose (bad) habbits.
Not really realizing that the (almost) same code can be used in a 100% detached (web) client, I should have anticipated this way of doing things.
Reading MSDN:

---

The DataAdapter serves as a bridge between a http://msdn.microsoft.com/en-us/library/system.data.dataset.aspx and a data source for retrieving and saving data. The DataAdapter provides this bridge by mapping http://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.fill.aspx, which changes the data in the http://msdn.microsoft.com/en-us/library/system.data.dataset.aspx to match the data in the data source, and http://msdn.microsoft.com/en-us/library/system.data.idataadapter.update.aspx, which changes the data in the data source to match the data in the http://msdn.microsoft.com/en-us/library/system.data.dataset.aspx.
....and ...

When an instance of DataAdapter is created, the read/write properties are set to initial values. For a list of these values, see the DataAdapter constructor.

---

would make one think that the dataadapter should be kept for later usage (.Update)

With the current way of working, as the initial datadapter can be discarded after filling the dataset, but I have to keep the initial SelectCommand somewhere for when I call the SaveChanges with it. 

But, more disturbing is that your version of SaveChanges works fine it trans is False. When True and the 'If trans' part is executed, the commands are Nothing, resulting in an error. (I added the transaction also to the SelectCommand), so no progress. Sorry.

For Each itm As UpdateDS In colDS

  Dim cmd As New SqlCommand(itm.SelectCommand, connection, transaction)

  Dim da As New SqlDataAdapter(cmd)

  Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)

  
  If trans Then

     da.InsertCommand.Transaction = transaction

     da.DeleteCommand.Transaction = transaction

     da.UpdateCommand.Transaction = transaction

  End If

  da.Update(itm.DS, itm.TableName)

Next

To make life easyer if only one dataset is in play, I added this (kind of overload):

Public Function SaveChanges(ByRef ds As DataSet, ByVal table As String, ByVal selectCmd As String, Optional ByVal trans As Boolean = False) As Boolean

  Dim mylist As New List(Of UpdateDS)

  mylist.Add(New UpdateDS(ds, selectCmd, "table"))

  Return SaveChanges(mylist, trans)

End Function

　

Adding the Transaction to the SelectCommand is sufficient, you do not need these lines:

Indeed, It works like that.

Just to finalize the topic these questions:

1. why do you pass the list ByValue in both SaveChanges and RetrieveData? It would take a possible huge overhead in duplicating the list compared to ByRef, or are there other things in play?

 

2. Would I gain something (speed, resources, ...) in ie SaveChanges to reuse the existing open connection in case there is no transaction rather then creating a new one? I have a permanent connection (with Multiple ResultSets) already open.

 

3. In SaveChanges a new connection is created, but it is only opened when trans = True

I traced the connection.State in SaveChanges and it stays in state=Closed in case of trans = False, so it is never opened: how does that work?

And this puts an extra questionmark behind point 2

1) See this link and the links provided in the article for an explanation of ByVal and ByRef.  In short, for a reference type, a pointer is passed using ByRef and a copy of the pointer is passed using ByVal.  A copy is not made of the reference type.

http://stackoverflow.com/questions/408101/which-is-faster-byval-or-byref

2) Short answer, NO.  The first time a connection is opened, a connection pool is created.  Each subsequent use of the connection, a connection from the existing connection pool is used. 

http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

3) The dataadpter's Fill and Update methods will automatically open and close the connection, if the connection is closed when executed.  The only time you need to explicitly open the connection is when using a datareader, cmd's executenonquery method, or using transactions.

http://msdn.microsoft.com/en-us/library/ms971481

Thanks for the links/hints/help: I learned a lot.