C# – LINQ to SQL with stored procedures and user defined table type parameter

clinq-to-sqlstored-procedures

I am using LINQ to SQL with stored procedures in SQL Server 2008. Everything work well except one problem. L2S cannot generate the method for the stored procedure with user defined table type as parameter.
Method signature in dbml design panel use object for parameter type instead of table type and when I tried to compile I got error:

Error: DBML1005: Mapping between DbType 'Structured' and
Type 'System.Object' in Parameter 'ParaName' of Function 'dbo.StoredProcName'
is not supported.

Is there a way to work around this of this problem? I don't want to go back to traditional ADO.NET data access.

Best Answer

I don't think there's anything available right now - table-valued parameters were introduced in SQL Server 2008 - after LINQ and LINQ2SQL were out already.

I haven't found any mention in any of the blog post about support for table-valued parameters in LINQ2SQL in .NET 4.0 so far.

UPDATE:
Here's a blog post by Damien Guard on the fixes and improvement for Linq-to-SQL in the .NET 4.0 time frame.

UPDATE 2:
Unfortunately, according to Damien, this feature isn't supported in .NET 3.5, and will not be supported in .NET 4.0 either :-(

Can't be done in 3.5 or 4.0 right now
:( [)amien

Related Solutions

C# – How to pass User Defined Table Type as Stored Procedured parameter in C#

You need to see this example on CodeProject.

SqlParameter param = cmd.Parameters.AddWithValue("@FileDetails", dt);

where dt is a DataTable, and the @fileDetails parameter is a table type in SQL:

create type FileDetailsType as table
(
    FileName        varchar(50),
    CreatedDate        varchar(50),
    Size       decimal(18,0)
)

Edit: This MSDN Developer's Guide article also would help.

Visual-studio – LINQ and Stored procedures DBML updating problem

There is no way DBML file gets updated as and when you change the SP.

I have a suggestion for you to get rid of it. This is what we followed in our projects.

Whenever you have update in your SP whether it is input or output parameters. Edit the dbml file as xml file (you can use open as xml in VS only) and save the dbml file. That will automatically updates the changes in designer.cs file as well. In this way you dont have to delete the SP from dbml and re-add it to get the updates. Though this is manual process but it really helps you a lot.

This way you can even modify the entity names (to comply with your entity naming conventions) for the output you are getting from SP as those default names are same as the SP name.

Best Answer

Related Solutions

C# – How to pass User Defined Table Type as Stored Procedured parameter in C#

Visual-studio – LINQ and Stored procedures DBML updating problem

Related Topic