SQL Server 2008 - User defined Table Data Type

29 posts, 0 answers
  1. devoas
    devoas avatar
    69 posts
    Member since:
    Dec 2009

    Posted 26 Jun 2010 Link to this post

    Hi,
    In Sql Server 2008 now we can pass a table variable in a stored procedure as a parameter. We have implemented such stored procedure and trying to call the same from Open Access, but this is not working or recognizing. 

    Please confirm if this supported or not.

    Thanks,
    devoas

  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 29 Jun 2010 Link to this post

    Hello devoas,

    at the moment, this is not supported. We are going to improve our type handling with the next release, however, this feature will needs a bit more work than this timeframe allows us to do.

    Best wishes,
    Thomas
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  3. DevCraft banner
  4. Craig Dean
    Craig Dean avatar
    16 posts
    Member since:
    Jan 2010

    Posted 18 Jul 2010 Link to this post

    Is there a timeline/ETA for User defined Table Type support - it is by far the optimal way to pass large chunks of data from the CLR to a SQL DB, alternate approaches would have a severe performance impact on some of our core code.
  5. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 19 Jul 2010 Link to this post

    Hi Craig Dean,
    We are in the middle of the implementation. There is a good chance for you to get support for your types at least as beta in the next 2-3 weeks.

    For us it is interesting to see your UDT implementation and test it with our product. Is it possible to get such a type from you and a description how you are using it at the moment?

    Kind regards,
    Jan Blessenohl
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  6. Craig Dean
    Craig Dean avatar
    16 posts
    Member since:
    Jan 2010

    Posted 19 Jul 2010 Link to this post

    Luckily, this is an issue I had one of my guys blog about a couple of months ago, there's a good example in the blog if you're interested...
    http://www.webapplicationsuk.com/community/blog/entryid/98/passing-table-value-parameters-to-stored-procedures-from-c.aspx

    We've done some performance testing and the approach described is nothing short of blistering for updating collections of objects, previously such work was done using XML or even CSV, or worse, multiple DB calls or dynamic SQL.  Using the approach described and user defined table types is very quick and strongly typed.
  7. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 20 Jul 2010 Link to this post

    Hello Craig Dean,
    Thanks, I will keep you updated.

    Best wishes,
    Jan Blessenohl
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  8. Craig Dean
    Craig Dean avatar
    16 posts
    Member since:
    Jan 2010

    Posted 01 Nov 2011 Link to this post

    BUMP

    We're revisiting Telerik ORM for a new enterprise size project, has this issue been addressed?
  9. Craig Dean
    Craig Dean avatar
    16 posts
    Member since:
    Jan 2010

    Posted 01 Nov 2011 Link to this post

    BUMP

    We're revisiting Telerik ORM for a new enterprise size project, has this issue been addressed?
  10. Craig Dean
    Craig Dean avatar
    16 posts
    Member since:
    Jan 2010

    Posted 01 Nov 2011 Link to this post

    Sorry, kept getting a server error on post.
  11. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 02 Nov 2011 Link to this post

    Hello Craig Dean,
    We have added a new API that mimics ADO. You can use that API to execute your SP and nevertheless let OpenAccess shape the result to persistent or non persistent types.

    Please have a look here and here.

    Greetings,
    Jan Blessenohl
    the Telerik team

    NEW and UPDATED OpenAccess ORM Resources. Check them out!

  12. Ian
    Ian avatar
    26 posts
    Member since:
    Jun 2012

    Posted 26 Nov 2011 Link to this post

    A nice feature suggestion for a future release would be to have new your stored procedure designer support table valued parameters :)

    Is it something that's already on the table?
  13. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 30 Nov 2011 Link to this post

    Hello Vernon,

     We have a number of ideas on further improving the stored procedure but unfortunately table valued parameters is not one of them yet. We will certainly consider adding such a feature but I am afraid that this will not make it prior to Q1 2012. How important is that for your project? If it is a must have for you we will certainly look into the possibility of increasing the priority of this feature.

    Regards,
    Petar
    the Telerik team

    Q3’11 of Telerik OpenAccess ORM is available for download. Register for the What's New in Data Tools webinar to see what's new and get a chance to WIN A FREE LICENSE!

  14. Ian
    Ian avatar
    26 posts
    Member since:
    Jun 2012

    Posted 02 Dec 2011 Link to this post

    Hi Peter,

    For us it's a "really nice to have" - we've just moved to .NET properly away from Delphi v7 and have survived without TVPs for a few years by using string concatenation, scratch tables, etc when calling our stored procs.
    Using an ORM we can avoid a lot of the reasons we were using fake TVPs in the first place, so I admit it's not critical for us but there are a few cases where it would be really nice (a bit of our logic in a few modules is, by necessity, in SQL Server).

    I appreciate that you have to prioritise things but Q2/Q3 of 2012 would be wonderful if possible :)
  15. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 02 Dec 2011 Link to this post

    Hi Vernon,

     We will certainly look into the possibility of adding this to our product as we definitely recognize this as a nice to have feature.
    Please find your Telerik points updated for this feature suggestion.

    Greetings,
    Petar
    the Telerik team

    Q3’11 of Telerik OpenAccess ORM is available for download. Register for the What's New in Data Tools webinar to see what's new and get a chance to WIN A FREE LICENSE!

  16. Gregory
    Gregory avatar
    9 posts
    Member since:
    May 2011

    Posted 08 Nov 2012 Link to this post

    Is this implemented yet?
  17. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 13 Nov 2012 Link to this post

    Hi Gregory,

     This is still in our to-do list but I am afraid that no significant effort has been put in that feature.
    How important is that for you? 
    I would like to point out that there is a pits issue opened concerning that matter and so far it only has one vote. If you are interested in that feature please add your vote there so that we can have a more clear view of how important that feature is.

    Kind regards,
    Petar
    the Telerik team
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
  18. Ian
    Ian avatar
    26 posts
    Member since:
    Jun 2012

    Posted 13 Nov 2012 Link to this post

    Hi Petar,

    Thanks for the PITS link.  However that's not quite the same thing.

    The PITS item is about trying to automatically retrieve resultset metadata from stored procedures where the procedure uses temporary tables internally.  This can cause trouble with SQL Server as it's not always able to determine the result set.

    What we're discussing here is passing a parameter to a stored procedure where the parameter, instead of being a simple scalar, is a table itself.

    I appreciate this could be a lot of work to implement, but you've been saying it's a great idea for a couple of years and now just link to a PITS item that's only a fortnight old - of course it's only got one vote  (two now - whilst not on this topic it's still a great PITS item).  What happened to the beta that was hinted at back in 2010 in this forum thread?

    The earlier links to using the ADO-style API are fairly helpful, but perhaps an example of constructing an OAParameter instance that was for a table-valued parameter would at least tie us over until you could have more native support in the product.

    Cheers!
  19. Craig Dean
    Craig Dean avatar
    16 posts
    Member since:
    Jan 2010

    Posted 13 Nov 2012 Link to this post

    FYI This was the thread that caused us to walk away...
  20. Gregory
    Gregory avatar
    9 posts
    Member since:
    May 2011

    Posted 13 Nov 2012 Link to this post

    I just submitted a PITs for adding TVP.  It baffles me why this wasn't added a long time ago, it's so much more powerful then having to send a comma separated string and parse the string in your procedure.

    And as far as importance, well.  I would really like to use the ORM for everything but now my model has a mix bag, since the OAParameter doesn't seem to have an equivalent of SqlDbType.Structured. I went and used the SqlConnection since all I'm doing is sending the table to be updated in the database, not expecting a return.  This would be a different story if I were expecting a return.

    Oh and the trick (for those of you using the ORM for your data models). You have to make sure that the defined type and the class's members are in the exact same order. So if you use a list of objects of the type of table you want to send back to your procedure and you use an extension method like this (http://www.extensionmethod.net/csharp/ienumerable-t/todatatable) to convert it to a datatable, it will create a datatable where the columns are in Alphabetical order DESC so your defined type in SQL server would need to be in that same order (I just scripted the table, took the column definitions and put it into Excel and sorted the columns there and finished creating the type.)  You'll also notice that I give the .TableName as the same name as the type.  I don't know if that is necessary, but I thought I had read that somewhere as well.

    Update 03/19/2013 - The extension method to create a datatable only works until the OpenAccessOrm stops putting the class fields in alphabetical order.  I ended up having to ditch the generic method and create each type in a datatable and assigning the values to the appropriate columns.
    So basically you end up with a method like this and the below must mactch the same column order that your user defined table type is in (I hacked out a bunch of columns):

    public static DataTable ToTableType(this IList<ReportingNavigatorDeal> data)
    {
        var table = new DataTable();
        table.Columns.Add("ScenarioID", typeof(long));
        table.Columns.Add("NAV_IB_FEES", typeof(decimal));
        table.Columns.Add("UpfrontFee", typeof(decimal));
     
     
        foreach (var item in data)
        {
            var dataRow = table.NewRow();
            dataRow["ScenarioID"] = item.ScenarioID;
            dataRow["NAV_IB_FEES"] = item.NAV_IB_FEES ?? 0;
            dataRow["UpfrontFee"] = item.CIBRUpfrontFee ?? 0;
        }
        return table;
    }

     

    Only reason I put the above up there is because it was a gotcha.

    public void SaveNonCreditRevenue(DataTable noncreditrevenue)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[DynamicConfigurationManager.AppSettings["DatabaseToUse"]].ConnectionString))
        {
            using (SqlCommand mysqlcommand = new SqlCommand("SaveNonCreditProducts", connection))
            {
                mysqlcommand.CommandType = CommandType.StoredProcedure;
                noncreditrevenue.TableName = "typ_NonCreditRevenue";
                mysqlcommand.Parameters.Add(new SqlParameter
                {
                    ParameterName = "@NonCreditRevenue",
                    Value = noncreditrevenue,
                    SqlDbType = SqlDbType.Structured
     
                });
     
                connection.Open();
                mysqlcommand.ExecuteNonQuery();
                connection.Close();
                connection.Dispose();
            }
        }
    }
  21. Terry
    Terry avatar
    8 posts
    Member since:
    Oct 2012

    Posted 15 Nov 2012 Link to this post

    Please can you tell me where  to find the PITs for adding TVP as this is exactly what I am looking for and I would like to vote for it?
  22. Gregory
    Gregory avatar
    9 posts
    Member since:
    May 2011

    Posted 15 Nov 2012 Link to this post

    well i selected Suggest new but it hasn't made it on the PITS page yet.
  23. Ivailo
    Admin
    Ivailo avatar
    318 posts

    Posted 16 Nov 2012 Link to this post

    Hi Gregory,

    Thank you for the feedback. Here is the link to the PITS item:

    http://www.telerik.com/support/pits.aspx#/public/openaccess/13483

    The reason why it was delayed is because the ideas are a subject of reviewing before they become active in the system, as per the current process. 

    Let us know if you have any other requests for extending OpenAccess ORM and making it more suitable for your needs.

    Kind regards,
    Ivailo
    the Telerik team
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
  24. Fawad
    Fawad avatar
    38 posts
    Member since:
    May 2009

    Posted 19 Mar 2013 Link to this post

    Implemented yet? :/
  25. Gregory
    Gregory avatar
    9 posts
    Member since:
    May 2011

    Posted 19 Mar 2013 Link to this post

    I know you are probably looking for a telerik response, however I'm guessing with 11 votes on the PITS, I don't see it happening yet. There are quiet a few other items with more votes :-(.  So we need to get people to upvote this feature request so Telerik can get it on their radar to schedule it :-)
  26. Fawad
    Fawad avatar
    38 posts
    Member since:
    May 2009

    Posted 19 Mar 2013 Link to this post

    Meh, nevermind, I've taken help from here and it worked great.

    Mind you, Translate sometimes plays funny things with TelerikORM generated classes, so I had to replace it with below code after the executereader commad, to get MM_Access (ORM generated classs) because Translate was giving me first row as null for reasons known only to universe, lol, here is the code:

    SqlDataReader reader = cmd.ExecuteReader();
     
    List<MM_Access> lst1 = new List<MM_Access>();
     
    while (reader.Read())
    {
        MM_Access access = new MM_Access();
        access.AccessID = (Guid)reader["AccessID"];
        access.AccessRole = (byte)reader["AccessRole"];
        access.AccessStatus = (byte)reader["AccessStatus"];
        access.ClientID = (Guid)reader["ClientID"];
        access.Email = (string)reader["Email"];                       
        access.Firstnames = (string)reader["Firstnames"];
        access.FullName = (string)reader["Firstnames"] + " " + (string)reader["Surname"];
        access.InitialPassword = (string)reader["InitialPassword"];
        access.InsertionDate = (DateTime)reader["InsertionDate"];
        access.Notes = (string)reader["Notes"];
        access.StaffIdentification = (string)reader["StaffIdentification"];
        access.Surname = (string)reader["Surname"];
        access.Username = (string)reader["Username"];
        access.UserPassword =(string)reader["UserPassword"];
        lst1.Add(access);
    }
     
    return lst1;


    Best of luck in your endeavour. :)

    Regards.
  27. Richard
    Richard avatar
    7 posts
    Member since:
    Nov 2012

    Posted 22 Mar 2013 Link to this post

    Craig, I agree - same here. Not having TVP's are a no go for me. TVP's are so powerful, it's not something that should need to be, "voted" for. It should be there by now.
  28. Ivailo
    Admin
    Ivailo avatar
    318 posts

    Posted 25 Mar 2013 Link to this post

    Hi,

    We understand that there are database features that might seem less important in some scenarios, while for others they might be critical. This is the reason why we are maintaining systems such as PITS - there is no better answer to the question whether some feature is useful then the amount of votes it gets from the developers using OpenAccess ORM. 

    To give you an example, the support for default value constraints in the database is something we didn't originally consider really important, since instead of using those constraints you can just initialize a property in the class constructor. However, many people preferred to use constraints, and based on the votes we have implemented this feature in 2012. My expectation is that since Table Valued Parameters is something of importance for you and other developers as well, we will probably be looking into it this year, but only in the context of other features in the same list.

    In any case we are grateful for your feedback, we will consider it and we hope that you will share here any additional ideas you have to make OpenAccess ORM suit your needs better.

     

    All the best,
    Ivailo
    the Telerik team
    Free Webinar: OpenAccess Integration in Sitefinity. SIGN UP NOW.
  29. Fawad
    Fawad avatar
    38 posts
    Member since:
    May 2009

    Posted 25 Mar 2013 Link to this post

    "This is the reason why we are maintaining systems such as PITS - there is no better answer to the question whether some feature is useful then the amount of votes it gets from the developers using OpenAccess ORM. "

    Partially true. If we're talking about small companies/projects etc. But this can be flipped.

    Suppose, if a developer team from a big company, say Mercedes or Boeing etc, wants to implement some big software and its developers drop ORM just because of TVP not present and word gets spread, its not good marketing, is it? They may or may not contact Telerik directly. But that's another discussion.

    Anyway, I'm very glad its in this year's roadmap, I can't wait since I really like ORM for many of its features. So I'm looking forward to it. Best of luck. :)

    Regards.
  30. Ivailo
    Admin
    Ivailo avatar
    318 posts

    Posted 26 Mar 2013 Link to this post

    Hello Fawad,

    Indeed, you are correct and we do not rely explicitly on votes for our roadmap definition - however, we do use it for determining priority between features that are relatively similar as per the value they can deliver to clients.

    Regarding the particular feature - I recommend you to follow its progress on PITS, we will most certainly change the status there if we begin implementing it, so that you can be informed for the progress as soon as possible.


    Kind regards,
    Ivailo
    the Telerik team
    Free Webinar: OpenAccess Integration in Sitefinity. SIGN UP NOW.
Back to Top
DevCraft banner