Type as Stored Proc Prameter

Thread is closed for posting
5 posts, 0 answers
  1. Marshal
    Marshal avatar
    3 posts
    Member since:
    Nov 2012

    Posted 23 Jan 2014 Link to this post

    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

    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,   
  2. Boyan
    Boyan avatar
    100 posts

    Posted 27 Jan 2014 Link to this post

    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.

    OpenAccess ORM Q3 2013 simplifies your model operations even further providing you with greater flexibility. Check out the list of new features shipped with our latest release!
  3. Jürgen
    Jürgen avatar
    7 posts
    Member since:
    May 2013

    Posted 25 Apr 2014 Link to this post


    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
  4. Jürgen
    Jürgen avatar
    7 posts
    Member since:
    May 2013

    Posted 25 Apr 2014 Link to this post

    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();
    05.foreach (DepartmentLOV department in departments)
    07.    myTable.Rows.Add(department.id, department.name);
    10.using (SqlConnection connection = new SqlConnection(
    11.    System.Configuration.ConfigurationManager.ConnectionStrings["WenaCRMEntities"].ConnectionString))
    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);
    20.    connection.Open();
    21.    command.ExecuteNonQuery();
  5. Kristian Nikolov
    Kristian Nikolov avatar
    206 posts

    Posted 29 Apr 2014 Link to this post

    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.

    Kristian Nikolov
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
Back to Top