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