Since I need to have a view to map my business objects to when pulling data from many tables, what (if any) is the advantage of populating via a SPROC versus using the view I already have defined for mapping?
I like (in theory) not having to keep the SPROC and views in sync. In admittedly limited testing performance seems equal.
Thanks in advance for your opinions, as always.
Method A (mapped to view, populate via SPROC, passing in parameters):
Public Sub FillBySprocGetRosterValues(ByVal p_rosterPK As Integer)
Dim cmd As New SqlCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "dbo.sdsi_GetRosterValues"
'-- Create the parms
cmd.Parameters.AddWithValue("@rosterPK", p_rosterPK).SqlDbType = SqlDbType.Int
'-- Return the results
Me.FillDataTable(cmd)
End Sub
Method 2 (Map to view, populate BO using the view):
Public Sub FillBySessionPK(ByVal p_sessionPK As Integer)
'-- Establish locals
Dim loCommand As New SqlCommand()
'-- Build the query
loCommand.CommandText = "SELECT * FROM vwAllAcademiesBySession WHERE sxa_session_fk = @param1"
'-- Add the parameter
loCommand.Parameters.Add("@param1", SqlDbType.Int)
loCommand.Parameters("@param1").Value = p_sessionPK
'-- Execute the command to fill the business object
Me.FillDataTable(loCommand)
End Sub
Keith Chisarik