Table valued parameters

[bartvanderwal]

Seems like a great package! But is it also possible to use table-valued parameters? Because I desperately need that to call my existing stored procedure!

And if not, could this package be extended for it? :+1:

[abe545]

As a matter of fact, it can be used to pass them. You have multiple ways of doing so (depending on how you're using the library). For instance, if you want to use the fluent syntax, you can call it like this:

IEnumerable<InputRow> rows = ...;
StoredProcedure.Create("dbo", "usp_TakesLotsOfInput")
               .WithTableValuedParameter("parameterName", rows, "schemaOfTable", "typeOfTable")
               .Execute(dbConnection);

You can also use dynamic syntax (my personal favorite under most circumstances):

IEnumerable<InputRow> rows = ...;
dbConnection.Execute().dbo.usp_TakesLotsOfInput(parameterName: rows);

For that syntax to work, you do need to decorate InputRow with TableValuedParameterAttribute:

[TableValuedParameter(Schema = "schemaOfTable", TableName = "typeOfTable")]
public class InputRow 
{
}

[bartvanderwal]

Great! I'll check that out monday! And is it by any change also possible to get back dynamic results from the sproc call? E.g. in EF normally always results are nicely typee, but I have to build an application on a very dynamic backend where the columns returned can be different per call for the same sproc For instance depending on the entitlements of the calling user (this is one of the table-valued input parameters). I want/have to inspect the result(s) at runtime for the available columns...

[abe545]

As of right now, there is not a way to expect a variable number of columns. It will throw an exception if an expected column isn't returned from the sproc. Would an Optional attribute meet your needs? You could mark the properties on your model that shouldn't throw if they aren't returned. For example, if your model would look like this:

public class Person
{
    public int Id { get; set; }
    [Optional]
    public string FirstName { get; set; }
    [Optional]
    public string LastName { get; set; }
    [Optional]
    public int Age { get; set; }
}

If that wouldn't work for you, how would you expect a feature like this to work?

[bartvanderwal]

Thanks for the quick answer again! An Optional attribute is a great idea. That certainly might work for other users. For my use case it is however still too limited. My application needs to support multiple customers, who can have different configurations/setups. The columns returned by the sproc depends on this setup.

Generally having this kind of dynamic result is considered bad practice, as exemplified by the first comment on this Stack Overflow issue.

I get their point. But I think my use case is an example where the simple rule 'never do that' is not valid. My system DOES have a list (table) of all columns that can be returned by the sproc. But it currently contains 600+ entries (!). So creating one generic return type with all these 600+ properties with an optional attribute would technically be a solution, but not one I want to use. Then I would need to handle a very 'sparse' matrix... And the available columns are also continually changing at this moment.

I am still investigating this myself, so to answer your question how I would expect a feature like this too work I can answer best for now by providing a little more background info.

The sproc returns an id an external id and then one or more of the mentioned columns. Typically 15. As mentioned my current approach would be to dynamically look at the returned set. There I want to lookup the (localized) column header name to display, together with it's type, and then do some runtime conversion.

I just 'throw' the returned set to a grid in my application to display (e.g a grid control in a view in my MVC app). I am currently looking at an OLD SKOOL datatable. As my requirement does seem to fall a bit out of scope of the 'Entity Framework model'. Namely to create a static and typed mapping of database tables to C# objects/classes.

But stored procedure in general also already leave this model a little. So if you have an idea, I'm very interested. I cannot imagine I am the only one with this requirement.

[abe545]

Now that I understand your use case... this is not something I ever envisioned needing. Both EF and this library are really geared towards getting your data objects into strongly typed .NET classes. Since a dynamically typed object won't really gain you anything over a DataTable, perhaps that is a better way of approaching this problem?

Having said that, would an IEnumerable<dynamic> work for you needs? If I return an ExpandoObject, you can cast one to an IDictionary<string, object> to query the columns, if that is something you require. I could see you calling it either way:

IEnumerable<InputRow> rows = ...;
StoredProcedure.Create("dbo", "usp_TakesLotsOfInput")
               .WithTableValuedParameter("parameterName", rows, "schemaOfTable", "typeOfTable") 
               .WithResult<dynamic>()
               .Execute(dbConnection);

IEnumerable<InputRow> rows = ...;
IEnumerable<dynamic> results = dbConnection.Execute().dbo.usp_TakesLotsOfInput(parameterName: rows);

[bartvanderwal]

Hi @abe545, I agree, and I actually started looking at DataTable yesterday. It's just something I hadn't used for a long time, after getting my hands on Entity Framework a few years ago.

I had to look into your answer, as I actually had not encountered the dynamic keyword before, nor the ExpandoObject class but after Google led me to Dino Esposito's articles here and here, this indeed seems like a great match!

But for now I'm gonna try and go with DataTables and an old-skool SqlDataReader brr... . If I have the time I will try and rework it to your NuGet package and IEnumerable<dynamic> code- example, and see if I can decrease our technical debt a little :smile:.

[abe545]

Okay, good luck. I think I'm going to add both the Optional attribute and dynamic support to the backlog, as I can see them being generally useful.