Core concept :
You can now pass full tables - in fact anything that implements iEnumerable - into a SQL Server 2008 SProc
THIS IS BIG !
Walkthrough :
First, you need to create a user-defined table type. This establishes the schema of the data you will pass in. The datatable you pass in to the parameter that is defined as being of this type must match this schema as well.
In your database, go to the node database/programmability/types/user-defined types and right click for CREATE script
USE [membership]
GO
/****** Object: UserDefinedTableType [dbo].[TVPGuidkeys] Script Date: 09/24/2009 20:10:11 ******/
CREATE TYPE [dbo].[TVPGuidkeys] AS TABLE(
[guidkey] [uniqueidentifier] NOT NULL,
PRIMARY KEY CLUSTERED
(
[guidkey] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
Here I am creating the schema for a one column table where guidkey will contain a UID key ( system.guid on the .net side) which will be pks in the Members table
Now a sproc that will get Members matching the pks that are passed in :
USE
[membership]GO
/****** Object: StoredProcedure [dbo].[get_Members_by_pklist] Script Date: 09/24/2009 20:23:39 ******/
SET
ANSI_NULLS ONGO
SET
QUOTED_IDENTIFIER ONGO
-- =============================================
-- Author: Charles Hankey
-- Create date: 09-24-09
-- Description: Pull records from Members based on passed in table of pks
-- =============================================
CREATE
PROCEDURE [dbo].[get_Members_by_pklist] -- Add the parameters for the stored procedure here@pklist dbo
.TVPGuidkeys READONLYAS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT Members.cPK_Members,Members
.cCompany,Members
.cFirst_Name,Members
.cLast_Name,Members
.cClassCode,mc.cClass
FROM dbo.Members
JOIN dbo.MembershipClasses mc
ON dbo.Members.cClassCode = mc.cClassCode
WHERE dbo.Members.cPK_Members IN
(SELECT guidkey FROM @pklist)END
GO
Notice : @pklist dbo.TVPGuidkeys READONLY
@pklist can be treated like a table - you could do a left join to it and achieve the same thing here as the sub-query. You could also use it to do an insert if you had structured the param to pass in a set of records !!
You cannot change the values of the @pklist
Now here is the .NET side in your SF app :
this is in the MembersBO. I will later create an overload that receives a pklist as List(Of System.integer) That will require another TVPIntKeys in the database if I want to create sprocs to receive integer PKs.
Public Sub get_Members_by_pklist(byval pklist As List(Of System.Guid))
Dim pktbl As DataTable = New DataTable("pktbl")
Dim newrow As DataRow
Dim colname As DataColumn = New DataColumn("guidkey")
colname.DataType = System.Type.GetType("System.Guid")
pktbl.Columns.Add(colname)
For Each pk As System.guid In pklist
newrow = pktbl.NewRow()
newrow("guidkey")=pk
pktbl.Rows.Add(newrow)
Next
Dim cmd As SqlCommand = new SqlCommand("dbo.get_members_by_pklist")
cmd.CommandType = commandtype.StoredProcedure
Dim oparm As SqlParameter = cmd.Parameters.AddWithValue("@pklist",pktbl)
oparm.SqlDbType = sqldbtype.Structured
oparm.TypeName = "dbo.TVPGuidkeys"
Me.FillDataTable(cmd)
End Sub
Under the category of weird things I didn't expect : the ADO stuff - colname.DataType = System.Type.GetType("System.Guid") appears to be case-sensitive. 
At least I was getting design-time errows when I used system.type.getype ...
Now I call the fill method when I come back from a browse dialog (the BD was driven by a SQL view with very denormalized data )
Private Sub bdMembers_BrowseDialogClosed(ByVal e As MicroFour.StrataFrame.UI.Windows.Forms.BrowseDialogClosedEventArgs) _
Handles bdMembers.BrowseDialogClosed
If Me.Members_lookupBO1.Count > 0 Then
Dim pks As New System.Collections.Generic.List(Of System.Guid)
'find all the pks of the records selected in the browsedialog
For Each bo As Members_lookupbo In Me.Members_lookupBO1.GetEnumerable()
pks.Add(bo.cPK_Members)
Next
'get all the records for the selected pks using a stored procedure
Me.MembersBO1.get_Members_by_pklist(pks)
'this is a listview on the maintenance form
Me.lvmembers.Requery()
End If
End Sub
There it is. FillbyPrimaryKey using a sproc. And the tools to use datatables in all sorts of ways in conjunction with sprocs.
See the thread on this topic in Business Objects for some links to Kevin Goff's Code Magazine articles.