Type as Stored Proc Prameter

[Marshal]

Telerik ORM is not able to generate domain method for following stored proc
Please suggest!

ALTER PROCEDURE [dbo].[SP_AddUpdateEmployee_Home]
@Employee dbo.[TYPE_EmployeeEntity] READONLY
AS
BEGIN

CREATE TYPE [dbo].[TYPE_EmployeeEntity] AS TABLE(
    [EmpId] [int] NOT NULL,
    [EmployeeSID] [varchar](50) NULL,
    [EmployeeStatus] [varchar](50) NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [PreferredName] [varchar](100) NULL,
    [JobGrade] [varchar](100) NULL,
    [Modified_Date] [datetime] NULL,
    [JobGradeID] [int] NULL,
    [Cost_Centre_ID] [int] NULL,
    [Country_ID] [int] NULL,
    [Country] [varchar](100) NULL,
    [Desk_Number_ID] [int] NULL,
    [DeskNo] [varchar](100) NULL,
    [Job_Title_ID] [int] NULL,
    [Job_Title] [varchar](255) NULL,
    [Team_ID] [int] NULL,
    [Team_Name] [varchar](255) NULL,
    [Length_of_Service] [varchar](50) NULL,
    [Active] [char](1) NULL,
    [Created_Date] [datetime] NULL,
    [Created_By_SID] [nvarchar](50) NULL,
    [Created_By_Name] [nvarchar](50) NULL,
    [Name_Preferred] [nvarchar](500) NULL,
    [Role_ID] [int] NULL,
)
GO

[Boyan]

Hello Marshal,

Currently User Defined Table Type are not supported. If you are interested in you could request this as  feature at our feedback portal. Voting for it will help us prioritize its eventual implementation.

Meanwhile, as an alternative approach you could consider mapping your table in a way that all CUD operation will be performed as stored procedures. If this is not applicable for you, it would be very helpful if you could give us more detailed description of you current implementation scenario, your needs, the TYPE_EmployeeEntity procedure code so we could provide you with suitable workaround.

Regards,
Boyan
Telerik

[Jürgen]

Bump

I found threads from back to 2010, where this problem was already posted

Until now I was satisfied with your ORM, but now I will switch back and stay at EntityFramework for the sake of completeness

Luckily I haven't switched all our projects - It's one of the highest voted feature request, hmm - Lesson learned

[Jürgen]

I have to regret, EF also doesn't help me out with this issue, so I ended up the traditional way

01.DataTable myTable = new DataTable();
02.myTable.Columns.Add("id");
03.myTable.Columns.Add("text");
04.
05.foreach (DepartmentLOV department in departments)
06.{
07.    myTable.Rows.Add(department.id, department.name);
08.}
09.
10.using (SqlConnection connection = new SqlConnection(
11.    System.Configuration.ConfigurationManager.ConnectionStrings["WenaCRMEntities"].ConnectionString))
12.{
13.    SqlCommand command = new SqlCommand("[dat].[proc_import_lov_department_from_ax]", connection)
14.    {
15.        CommandType = CommandType.StoredProcedure,
16.        CommandTimeout = 240
17.    };
18.    command.Parameters.AddWithValue("@data_table", myTable);
19.
20.    connection.Open();
21.    command.ExecuteNonQuery();
22.}

[Kristian Nikolov]

Hi Jürgen,

We are sorry that you have experienced difficulties with Telerik Data Access.

Indeed currently Telerik Data Access does not support Table Valued Parameters. We recognize this as a limitation of our product and therefore we have a feature request for such functionality in our Feedback Portal. 

Votes and feedback for the requests help us greatly when prioritizing their eventual implementation. We have already implemented some of them (Default Values in Fluent released just recently) and we are currently working on introducing Custom Attributes Support. However, there are additional factors beside votes that also need be considered when prioritizing and deciding on which feature is to be implemented. Therefore we cannot promise when a certain feature request will be implemented.

As you have discovered, you can call stored procedures with table valued parameters using ADO.NET. While the TVP support is absent from the product, you could define the ADO.NET logic that calls stored procedures with TVP`s in a separate class and expose it through the context class of your model. This way you will still have a single point of access to the database.

I hope this helps. Should you have additional questions or need help, do not hesitate to post in our forums again.

Regards,
Kristian Nikolov
Telerik