CODEWITHSUNDEEP
c#sql-serverlinq-to-sqlstored-procedures
mortenstarck
mortenstarck

Reputation: 2803

INSERT using LIST into Stored Procedure

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

Upvotes: 2

Views: 4848

Answers (2)

Pranav Singh
Pranav Singh

Reputation: 20111

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

Upvotes: 4

Ehsan
Ehsan

Reputation: 32681

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

Upvotes: 2

Related Questions