Recordset vs Dataset

[Roey]

I have an n-tiered app developed using VB6 that I am trying to convert to .Net. I am passing data between the tiers using datasets, and can populate screens on the user interface without any problems.

 

The question I have is how do I update the database using a dataset, have read that I need to use a the dataadapter.update method but can't seem to figure it out.

 

Here's an example of my code:

 

VB6

----

 

Public Function Add_Item(rsNewItemDetails As ADODB.Recordset) As Boolean

 

On Error GoTo BadInsert

 

cnMRP.Open

cnMRP.CursorLocation = adUseServer

 

strSQL = "SELECT * FROM tblItemMaster WHERE 1=2"

rsItemDetails.LockType = adLockOptimistic

rsItemDetails.CursorType = adOpenKeyset

rsItemDetails.Open strSQL, cnMRP, , , adCmdText

 

With rsItemDetails

.AddNew

!ItemNo = rsNewItemDetails!ItemNo

!ItemDescShort = rsNewItemDetails!ItemDescShort

!ItemDescLong = rsNewItemDetails!ItemDescLong

!CatID = rsNewItemDetails!CatID

!UMID = rsNewItemDetails!UMID

!SafetyStock = rsNewItemDetails!SafetyStock

!MinOrderQty = rsNewItemDetails!MinOrderQty

!MaxOrderQty = rsNewItemDetails!MaxOrderQty

!VendorID = rsNewItemDetails!VendorID

!VendorItemNumber = rsNewItemDetails!VendorItemNumber

!LeadTime = rsNewItemDetails!LeadTime

!CostPurchase = rsNewItemDetails!CostPurchase

!ClassID = rsNewItemDetails!ClassID

!CostStandard = rsNewItemDetails!CostStandard

!CostEstimated = rsNewItemDetails!CostEstimated

!CostAverage = rsNewItemDetails!CostAverage

!CostLast = rsNewItemDetails!CostLast

!InventoryValue = rsNewItemDetails!InventoryValue

!EngLevel = rsNewItemDetails!EngLevel

!DrawingNumber = rsNewItemDetails!DrawingNumber

!DrawingLocation = rsNewItemDetails!DrawingLocation

!TypeID = rsNewItemDetails!TypeID

.Update

End With

 

Add_Item = True

 

cnMRP.Close

 

Exit Function

 

BadInsert:

 

Select Case Err.Number

Case -2147217887

Err.Raise Number:=vbObjectError + 512 + 1, _

Description:="Duplicate Values Exist for this Item"

Err.Clear

Case Else

Err.Raise Err.Number & Err.Description

Err.Clear

End Select

 

Add_Item = False

 

cnMRP.Close

 

End Function

 

 

.Net

-----

 

Public Function Add_Item(ByVal ItemDetails As DataSet) As Boolean

 

Dim mySelectCommand As OleDbCommand = New OleDbCommand(strSQL, oConnection)

Dim myOleDbDataAdapter As OleDbDataAdapter = New OleDbDataAdapter(mySelectCommand)

 

myOleDbDataAdapter.Update(dsReturn)

 

Add_Item = True

 

End Function

[Madz]

in .NET when ever your try to update you must have an Update command assosicated with a data adapter and then you can call update method.

[sizer]

DATAADAPTER

 

Before you call Adapter.Update you have to declare OleDBcommands:

 

Dim adp As New OleDb.OleDbDataAdapter()
Dim myConnection As New OleDb.OleDbConnection("Your connection string")

''Depends what you  want to do
Dim myDelCommand As New OleDb.OleDbCommand("delete query")
Dim myInsCommand As New OleDb.OleDbCommand("insert query")
Dim myUpdateCommand As New OleDb.OleDbCommand("update query")
Dim mySelectCommand As New OleDb.OleDbCommand("select query")

Dim adp As New OleDb.OleDbDataAdapter()

adp.SelectCommand = mySelectCommand


adp.SelectCommand.Connection = myConnection

adp.InsertCommand = myInsCommand
adp.InsertCommand.Connection = myConnection

adp.UpdateCommand = myUpdateCommand
adp.UpdateCommand.Connection = myConnection

adp.DeleteCommand = myDElCommand
adp.DeleteCommand.Connection = myConnection

''if you modify your data thru  your datagrid or in DataTable before update type

''me.BindingContext(yourDatatable).EndCurrentEdit()
''Why?
''Because rowstate is unchanged util your move to another row or call ''me.BindingContext(yourDatatable).EndCurrentEdit()

''if error occurs CATCH
try ''DEBUG
adp.Update(your datatable) 
catch errobj as Exception
MessageBox.Show(errobj.Message)
End Try

[sizer]

CommandBuilder

 

You could use CommandBuilder because it automatically generate the DeleteCommand, InsertCommand, and UpdateCommand of the DataAdapter.

As a minimum requirement, you must set the SelectCommand property in order for automatic command generation to work.

Also CommandBuilder have some retrictions and disadvatages like:

 

 

i) If you have many records CommandBuilder is very slow

 

ii) Automatic command generation logic fails if column names or table names contain any special characters, such as spaces, periods, quotation marks, or other nonalphanumeric characters, even if delimited by brackets. Fully qualified table names in the form of schema.owner.table are supported.

 

iii) The automatic command generation logic generates INSERT, UPDATE, or DELETE statements for standalone tables without taking into account any relationships to other tables at the data source.

 

 

iv) your datatable must have PRIMARY KEY

[Roey]

Thanks for your help, but not too sure I explained my problem correctly. The issue that I am having is that after the dataset is passed between tiers it is destroyed, and therefore when I try to use the Adpater for updating the database there is no original DataSet, so I need some other method.

 

I am currently looking at adding rows to the dataset and then passing that back down through the tiers, but need to know how to update the database.

 

 

Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click

 

Dim NewRow As DataRow = dsProductClass.Tables(0).NewRow()

NewRow.Item("ClassCode") = cboClassCode.Text

NewRow.Item("Description") = txtDescription.Text

dsProductClass.Tables(0).Rows.Add(NewRow)

End Sub

 

 

Sorry if I've still not explained this clearly.....