Recordset vs Dataset

R

Roey

Well-known member
Joined
Oct 10, 2002
Messages
238
Location
Canada
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 cant seem to figure it out.

Heres 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
 
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.
 
DATAADAPTER

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

Code: 
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
 
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
 
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 Ive still not explained this clearly.....
 
Back
Top