StrataFrame Forum

Table Valued Parameters - Passing a table into a sproc in SQL 2008

http://forum.strataframe.net/Topic24706.aspx

By Charles R Hankey - 9/24/2009

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 ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- 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 READONLY

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT 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. w00t 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.

 

By Charles R Hankey - 9/24/2009

To clarify when i talked about creating an overload :

Now that i have the

get_Members_by_pklist(byval pklist As List(Of System.Guid))

working I will abstract this to my basebo as FillbyPKlist(byval pklist AS List(Of T), byval sproc as string)

so that I can pass in a list of whatever type is used for pks and call whatever sproc I have setup to use it.  The important thing is that there will only be one TVP definition for each datatype (and they have been added to my Model DB) and any sproc I create that is meant to use this collection to pull (or not pull) records will use the appropriate type for its @pklist

 
By Edhy Rijo - 9/24/2009

Hi Charles,



Thanks for the contribution. I will have to print it out to parse the whole concept and digest it BigGrin.



I never used store procedures in my VFP application and suddenly after I learned how to use them in SF I now have 20+ in my current project and loving them. Tongue
By Greg McGuffey - 9/25/2009

This is awesome Charles! Thanks!
By Charles R Hankey - 9/25/2009

I just took my first leap into Generic methods so don't laugh - I think this is generic. The goal is to have one sub in my basebo that can handle collections of pks whatever the data type. In my previous post in this topic i have a method that works specifically for guid keys. Here is that method refactored



Public Sub FillfromSproc_by_pklist(Of T) (byval pklist As List(Of T),Byval sproc As string)



' I need the name of the TVP defined in that database. That is a schema that needs to know the name of the

' the pkkey column - renamed from previous guidkey/intkey version

' still have 2 TVPs - could probably get around that by using a single TVP with multiple columns for different

' data types and only using the one appropriate, but for right now it is just as easy to do it this way



' build the name of the TVP being used to declare the parm @pklist in the sproc



Dim TType As String = gettype(T).Name

Dim typestr As String = "dbo.TVP" & TType & "keys"



' since this is the function that actually builds the datatable it needed to be

' refactored as well, getting its type for the pkkey column - renamed to be type agnostic

' from the type of the pklist that is being passed in - see below



Dim pktbl As DataTable = Utility.PKlisttoDatatable(pklist)

Dim cmd As SqlCommand = new SqlCommand(sproc)



cmd.CommandType = commandtype.StoredProcedure

Dim oparm As SqlParameter = cmd.Parameters.AddWithValue("@pklist",pktbl)

oparm.SqlDbType = sqldbtype.Structured



'use the TVP type name built above for the type represented by T



oparm.TypeName = typestr



Me.FillDataTable(cmd)



End Sub




Now the refactor of the function that converts a list(Of T) to a datatable with a single column of type T



Public Shared Function PKListtoDatatable(Of T)(byval pklist As List(Of T)) As DataTable



Dim pktbl As DataTable = New DataTable("pktbl")

Dim newrow As DataRow



Dim colname As DataColumn = New DataColumn("pkkey")



' this made me feel like "Hey baby, now you're cookin' with Generics !"



colname.DataType = gettype(T)



pktbl.Columns.Add(colname)



For Each pk As T In pklist

newrow = pktbl.NewRow()

newrow("pkkey")=pk

pktbl.Rows.Add(newrow)

Next

Return pktbl



End Function




This is now part of my BaseBO



The requirement for the sproc that receives it is that it declares a parm called @pklist of a previously created table type for the correct type of the pklist that will be passed in



Suggestion for further extension or refactor most welcome
By Charles R Hankey - 11/12/2010

Boy am I glad I wrote this up the first time around!  Now I actually really need it and frankly I'm having to read it over multiple times to remember what the heck I did Smile

In using sprocs for Xtrareports datasets, I'm finding there will be a lot of times when I need to first get the records for the main band, and then get child records for all the records in the first set,  In order to do this with 2 sprocs - one for parent and then one for children connected to the selected parent set - I need a way to tell the child sproc which record to pull based on matching fks with a list of parent pks. 

And this is the way to go, unless somebody else has a suggestion ...