INSERT using LIST into Stored Procedure

Question

I have this stored procedure:

CREATE PROCEDURE [RSLinxMonitoring].[InsertFeatures] 
   @Features nvarchar(50), 
   @TotalLicenses int, 
   @LicensesUsed int, 
   @ServerName nvarchar(50) 
AS
   SET NOCOUNT ON

   INSERT INTO [RSLinxMonitoring].[FeatureServer]
        ([Features]
           ,[TotalLicenses]
           ,[LicensesUsed]
        ,[Server])
   VALUES(@Features
          ,@TotalLicenses
          ,@LicensesUsed
          ,@ServerName)

It works as expected, but since I need to insert quit a bit from my C# Linq-to-SQL class, I would like to insert a list from my application instead, is this possible?

I have seen it been done then using SELECT statement, but not when using INSERT.
UPDATE: Since LINQ to SQL Doesn't support User-Defined Table Types i can't Tables. :(

Have you heard of Table Type parameters in stored procedures? If you don't want to use ORM consider passing table type parameters — Ehsan, Jul 05 '13 at 11:04
#Ehsan Ullah Looks interresting. But have do i iterate trough the list, and what type shall i send from the application? — mortenstarck, Jul 05 '13 at 11:07

score 4 · Accepted Answer · answered Jul 05 '13 at 11:21

4

If you are using SQL server 2008 & above, you can use below solution. Declare Table type like :

CREATE TYPE FeatureServerType AS TABLE 
(
   [Features] nvarchar(50)
   ,[TotalLicenses] int
   ,[LicensesUsed] int
   ,[Server] nvarchar(50) 
);

Use it like :

CREATE PROCEDURE [RSLinxMonitoring].[InsertFeatures] 
   @TabletypeFeatures FeatureServerType READONLY
AS
   SET NOCOUNT ON;

   INSERT INTO [RSLinxMonitoring].[FeatureServer]
        ([Features]
           ,[TotalLicenses]
           ,[LicensesUsed]
        ,[Server])
   SELECT * FROM @TabletypeFeatures

answered Jul 05 '13 at 11:21

Pranav Singh

17,079
30
77
104

I have tried you example. But Linq-to-SQL doesnt support User-Defined Table Types. it gives me this error "UdtTypeName property must be set for UDT parameters" – mortenstarck Jul 05 '13 at 12:08
Yes, Linq-to-SQL doesnt support User-Defined Table Types currently. Use can use simple SP call from your code . – Pranav Singh Jul 05 '13 at 12:15

score 2 · Answer 2 · answered Jul 05 '13 at 11:13

You should use Table type parameters.

create a class and Table type in sql server. Names and order should match. Now just convert your list to Table using the following code and pass it as a paremeter to the procedure.

stored procedure help can be seen here

http://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/

    public static DataTable ToDataTable<T>(this List<T> iList)
    {
        DataTable dataTable = new DataTable();
        PropertyDescriptorCollection propertyDescriptorCollection =
            TypeDescriptor.GetProperties(typeof(T));
        for (int i = 0; i < propertyDescriptorCollection.Count; i++)
        {
            PropertyDescriptor propertyDescriptor = propertyDescriptorCollection[i];
            Type type = propertyDescriptor.PropertyType;

            if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
                type = Nullable.GetUnderlyingType(type);


            dataTable.Columns.Add(propertyDescriptor.Name, type);
        }
        object[] values = new object[propertyDescriptorCollection.Count];
        foreach (T iListItem in iList)
        {
            for (int i = 0; i < values.Length; i++)
            {
                values[i] = propertyDescriptorCollection[i].GetValue(iListItem);
            }
            dataTable.Rows.Add(values);
        }
        return dataTable;
    }

Yes, this. Please note however, that table types can not be altered. You need to re-create them upon every change, which also means you need to recompile every stored procedure that uses the type. — Thorsten Dittmar, Jul 05 '13 at 11:25
recompile means that before recreation of your table type you will have to drop all the stored proecdures that are using this type and then altering the table type. After altering you will have to create sp's again. :) — Ehsan, Jul 05 '13 at 11:28
I tend to rename the old table type first (which works, as opposed to deleting it). Then I create the new type with the old name, then I edit the SP and press F5. After that I delete the table type I renamed in the first step. — Thorsten Dittmar, Jul 05 '13 at 11:35

INSERT using LIST into Stored Procedure

2 Answers2

Linked