How can I load a .Net DataTAble schema from a UDT Table declared in our DB?

Question

I've searched every way I can come up with, but can't find an technique for initializing a DataTable to match a UDT Table declared in our DB. I could manually go through and add columns, but I don't want to duplicate the structure in both places. For a normal table, one option would be to simply issue a "select * where ..." that returns no results. But can something like this be done for a UDT Table?

And here is the background problem.

This DB has a sproc that accepts a Table Valued Parameter that is an instance of the indicated UDT Table declared in the same DB. Most of the UD fields are nullable, and the logic to load the TVP is quite involved. What I hoped to do is initialize the DT, then insert rows as needed and set required column/field values as I go until I'm ready to toss the result to SS for final processing.

I can certainly add the dozen or more fields in code, but the details are still in flux (and may continue to be so for some time), which is one reason I don't really want to have to load all the columns in code.

So, is there a reasonable solution, or am I barking up the wrong tree? I've already spent more time looking for the solution I expected to exist than it would have taken to write the column loading code 100 times over, but now I just want to know if it's possible.

this may help you with some ideas http://www.codeproject.com/Tips/93248/SQL-Server-User-Defined-Table-Types-and-Table — MethodMan, Apr 08 '15 at 19:56
Thanks, but that is adding columns manually, then showing an option for passing TVP to sproc. All I'm looking for is how to get an empty DataTable with schema initialized from UDT table in our DB without having to manually add the columns. — BadDog, Apr 08 '15 at 20:02
it seems that you still should be able to select from the UDT if it's a table for example..but that would also depend what the type of your UDT is too.. — MethodMan, Apr 08 '15 at 20:04

score 0 · Accepted Answer · answered Apr 08 '15 at 20:37

Ok, I was discussing with a friend who is MUCH more SQL savvy than I am (doesn't take much), and he suggested the following SQL query:

"DECLARE @TVP as MyUDTTable; SELECT * FROM @TVP"

This appears to give me exactly what I want, so I'm updating here should some other poor sap want something similar in the future. Perhaps others may offer different or better answers.

score 0 · Answer 2 · answered Apr 08 '15 at 20:50

Here is an example of how I did this. This style of input/output is something me and a co-worker put together to allow quick and effective use of entity framework on his side and keeps my options open to use all sql toys. If that is the same use as you have you might also like the OUTPUT use I did here. It spits the newly created ids right back at whatever method calls the proc allowing the program to go right on to the next activity withouth pestering my database for the numbers.

My Udt

CREATE TYPE [dbo].[udtOrderLineBatch] AS TABLE
(
[BrandId] [bigint] NULL,
[ProductClassId] [bigint] NULL,
[ProductStatus] [bigint] NULL,
[Quantity] [bigint] NULL
)

and the procedure that takes is as an input

create procedure [ops].[uspBackOrderlineMultipleCreate]
    @parmBackOrderId int
   ,@UserGuid uniqueidentifier
   null
   ,@parmOrderLineBatch as udtOrderLineBatch readonly
as
    begin
            insert  ops.OrderLine
                    (
                     BrandId
                    ,ProductClassId
                    ,ProductStatusId
                    ,BackOrderId
                    ,OrderId
                    ,DeliveryId
                    ,CreatedDate
                    ,CreatedBy)
            output  cast(inserted.OrderLineId as bigint) OrderLineId
                    select  line.BrandId
                           ,line.ProductClassId
                           ,line.ProductStatus
                           ,@parmBackOrderId
                           ,null
                           ,null
                           ,getdate()
                           ,@UserGuid
                    from    @parmOrderLineBatch line
                            join NumberSequence seq on line.Quantity >= seq.Number
    end

Thanks, interesting idea, I'll pass it along to our DB owner. But it doesn't provide the schema to initialize a data table from a UDT Table, which is what I was looking for. — BadDog, Apr 10 '15 at 06:03
I'll see if i can get my hands on the C# code that uses this and add it to my post. — Tristan, Apr 10 '15 at 10:28

How can I load a .Net DataTAble schema from a UDT Table declared in our DB?

2 Answers2