Menu
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.
581
Create a user-defined table type that corresponds to the table that you want to populate. Pass the user-defined table to the stored procedure as a parameter. Inside the stored procedure, select the data from the passed parameter and insert it into the table that you want to populate.
In LINQ to SQL, we can use stored procedures with or without parameters to get the required data from database tables. Before we start using LINQ to SQL with a stored procedure, we need to create a database with required tables and map those tables to LINQ to SQL file (. dbml).
Table-Valued Parameters aka TVPs are commonly used to pass a table as a parameter into stored procedures or functions. They are helpful in a way, we can use a table as an input to these routines and we can get rid of dealing more complex steps to achieve this process.
SQL Server supports various data types for storing different kinds of data. These data types store characters, numeric, decimal, string, binary, CLR and Spatial data types. Once you connect to a database in SSMS, you can view these data types by navigating to Programmability-> Types->System Data Types.
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
188
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
