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
– Ehsan, Commented Jul 5, 2013 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
– mortenstarck, Commented Jul 5, 2013 at 11:07

Pranav Singh · Accepted Answer · 2013-07-05 11:21:17Z

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 5, 2013 at 11:21

Pranav Singh

20.3k32 gold badges83 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mortenstarck Over a year ago

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"

Pranav Singh Over a year ago

Yes, Linq-to-SQL doesnt support User-Defined Table Types currently. Use can use simple SP call from your code .

Ehsan · Accepted Answer · 2013-07-05 11:13:42Z

2

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;
    }

answered Jul 5, 2013 at 11:13

Ehsan

32.8k6 gold badges61 silver badges66 bronze badges

3 Comments

Thorsten Dittmar Over a year ago

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.

Ehsan Over a year ago

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. :)

Thorsten Dittmar Over a year ago

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.

Collectives™ on Stack Overflow

INSERT using LIST into Stored Procedure

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related