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

StrataFrame Forum

Back

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

Author	Message
Charles R Hankey	Charles R Hankey posted 14 Years Ago Post Details Share Post
Group: Forum Members Posts: 524, Visits: 30K	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 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 ... Tags parameters stored procedure XtraReports
	Reply Like 2
Charles R Hankey	Charles R Hankey posted 15 Years Ago Post Details Share Post
Group: Forum Members Posts: 524, Visits: 30K	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
	Reply Like 2
Greg McGuffey	Greg McGuffey posted 15 Years Ago Post Details Share Post
Group: Forum Members Posts: 2K, Visits: 6.6K	This is awesome Charles! Thanks!
	Reply Like 2
Edhy Rijo	E Edhy Rijo posted 15 Years Ago Post Details Share Post
E Group: StrataFrame Users Posts: 2.4K, Visits: 23K	Hi Charles, Thanks for the contribution. I will have to print it out to parse the whole concept and digest it . 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. Edhy Rijo
	Reply Like 3
Charles R Hankey	Charles R Hankey posted 15 Years Ago Post Details Share Post
Group: Forum Members Posts: 524, Visits: 30K	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
	Reply Like 1
Charles R Hankey	Charles R Hankey posted 15 Years Ago Topic Details Share Topic
Group: Forum Members Posts: 524, Visits: 30K	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. 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.
	Reply Like 2