I haven't used the FillByStoredProcudure, but I use a lot of sprocs, for a lot of things. I don't think that a .NET generic type (List) will mix well with SQL. I know it won't when using SqlCommand.Parameters. Here is what the code might look like.
SQL Sproc Example:
Create Proc testFill
@key int
AS
Select table1.*
From table1 Left Outer Join table2
Where id = @key
SF Fill procedure using the above sproc:
Public Sub FillByKey(ByVal key As Integer)
Using cmd As New SqlCommand()
cmd.CommandType = StoredProcedure
cmd.CommandText = "testFill"
cmd.Parameters.AddWithValue("@key", key).SqlDataType = SqlDataType.int
Me.FillDataTable(cmd)
End Using
End Sub
Now, I have a feeling that it is possible that a sproc might not work out as well as you'd like. The reason being that you want to pass a list in. I.e. there will be an unknown number of keys, and as far as I know, SQL server doesn't do that well (if it does, I'd really like to know about it!). You might be able to use a CLR sproc that could be used to parse a string of delimited keys in, but an easier way is this. You simply build the SQL statement with the appropriate number of parameters and then add those parameters, driven by the list. In order to handle the complex joins, you could either do them here or create a view and then query the view (probably the better way to go).
Here is an example. I just hacked this out, so I might have messed up on some of the syntax or made some other mistake. Hopefully you'll get the idea.
'''<Summary>
''' Fill the BO with the records that contain the indicated keys.
''' This is generic so one method can handle both integers and Guids.
'''</Summary>
Public Sub FillByKeyList(Of T)(keyList As List(Of T))
'-- Validate that T of supported type.
If T.GetType() IsNot GetType(Integer) AndAlso T.GetType() IsNot GetType(Guid) Then
Throw New ArgumentException("The list must be of either integers or GUIDS.")
End If
'-- Define a template that will be used to build the name of the parameter.
' {1} is replaced with a delimiter if needed and {0} with
' the index of the item in the list.
Dim paramNameTemplate As String = "{1}@id_{0}"
'-- Build basic SQL statement. We'll use a view to make life easier.
Dim sql As String
sql = "Select * From ComplexJoinView"
'-- Build an In clause with parameters names.
Dim keyParamList As String = String.Empty
For idx As Integer = 0 To keyList.Count - 1
Dim itemDelimiter As String = ","
If idx = 0 Then idx = String.Empty
keyParamList &= String.Format(paramNameTemplate, idx, itemDelimiter)
Next
'-- Add In clause to SQL
sql &= String.Format(" In({0})", keyParamList)
'-- Build SQL Command
Using cmd As New SqlCommand()
cmd.CommandText = sql
'-- Add the parameters for each key
For idx As Integer = 0 To keyList.Count - 1
'-- Determine what the SQL Data Type is Based on generic type.
Dim sqlType As SqlDataType
If T.GetType() Is GetType(Integer) Then
sqlType = SqlDataType.Integer
ElseIf T.GetType() Is GetType(Guid) Then
sqlType = SqlDataType.UniqueIdentifier
End If
'-- Build name of this parameter
Dim paramName As String = String.Format(paramNameTemplate, idx, String.Empty)
'-- Add the parameter
cmd.Parameters.AddWithValue(paramName, keyList(idx)).SqlDataType = sqlType
Next
'-- Fill BO
Me.FillDataTable(cmd)
End Using
End Sub
Hopefully that will get you going or spark a useful idea!