SQL Server 2008 - User defined Table Data Type

[devoas]

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

[Thomas]

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

[Craig Dean]

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.

[Jan Blessenohl]

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

[Craig Dean]

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.

[Jan Blessenohl]

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

Best wishes,
Jan Blessenohl
the Telerik team

[Craig Dean]

BUMP

We're revisiting Telerik ORM for a new enterprise size project, has this issue been addressed?

[Craig Dean]

BUMP

We're revisiting Telerik ORM for a new enterprise size project, has this issue been addressed?

[Craig Dean]

Sorry, kept getting a server error on post.

[Jan Blessenohl]

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

[Ian]

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?

[PetarP]

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

[Ian]

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 :)

[PetarP]

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

[Gregory]

Is this implemented yet?

[PetarP]

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

[Ian]

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!

[Craig Dean]

FYI This was the thread that caused us to walk away...

[Gregory]

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();
        }
    }
}

[Terry]

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?

[Gregory]

well i selected Suggest new but it hasn't made it on the PITS page yet.

[Ivailo]

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

[Fawad]

Implemented yet? :/

[Gregory]

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 :-)

[Fawad]

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.

[Richard]

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.

[Ivailo]

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

[Fawad]

"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.

[Ivailo]

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