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
