DataAdapter using stored procedures for Insert, Update and Delete

[RB]

Hi All,

I'm trying to use more data-adapters/data-sets in a project, because
they are so awesome! Anyhoo, I want to use them with Stored Procedures,
rather than raw SQL text.

The hard part of working with SPs is passing the new values to the
insert, update and delete stored procedures. I've figured out one way to
do it, but was wondering if anyone else had any better ideas, as my way,
although it works fine, means writing quite a lot of code...

My way is to declare my data adapater globally and WithEvents, then use
the RowUpdating event to set the SQLCommand's parameters (see code for
example)

My question then is : Is this a reasonable practice, or is there a much
better way I'm too dense to see?

Cheeers,

RB.

============= SAMPLE CODE ================

' NOTE : Sample code to illustrate a point.
' Probably won't compile or anything.
Imports System.Data
Imports System.Data.SqlClient

Public Class Employee
Protected WithEvents da As New SqlDataAdapter()

Public Function GetTenantAccount(ByVal clientId As Integer) As
DataTable
Dim dt As New DataTable()
Dim scSelect As New SqlCommand("sp_SelectEmployee", conn)
Dim scUpdate As New SqlCommand("sp_UpdateEmployee", conn)
Dim scInsert As New SqlCommand("sp_InsertEmployee", conn)
Dim scDelete As New SqlCommand("sp_DeleteEmployee", conn)

scSelect.CommandType = CommandType.StoredProcedure
scUpdate.CommandType = CommandType.StoredProcedure
scInsert.CommandType = CommandType.StoredProcedure
scDelete.CommandType = CommandType.StoredProcedure

da = New SqlDataAdapter(scSelect)
da.Fill(dt)
da.UpdateCommand = scUpdate
da.InsertCommand = scInsert
da.DeleteCommand = scDelete

Return dt
End Function

Private Sub da_RowUpdating(ByVal sender As Object, _
ByVal e As System.Data.SqlClient.SqlRowUpdatingEventArgs) _
Handles da.RowUpdating
Select Case e.Row.RowState
Case DataRowState.Deleted
Dim sc As SqlCommand = da.UpdateCommand
sc.Parameters("EmployeeId").Value = e.Row("EmployeeId")
Case DataRowState.Modified
Dim sc As SqlCommand = da.UpdateCommand
sc.Parameters("EmployeeId").Value = e.Row("EmployeeId")
sc.Parameters("FirstName").Value = e.Row("FirstName")
Case DataRowState.Added
Dim sc As SqlCommand = da.InsertCommand
sc.Parameters("EmployeeId").Value = e.Row("EmployeeId")
sc.Parameters("FirstName").Value = e.Row("FirstName")
End Select
End Sub
End Class

 

[Cor Ligthert[MVP]]

RB,

What is your reason that you are updating every row change. Be aware that in
a multiuser environment you never get the updates cq changes that are be
done withouth a real select.

Normal you would update a datatable just as everything is done. (Be aware
that if it is in a relation you have to do the parents or child first,
depending of the rowstates)

However probably you have a reason for this.

Cor