Repodb: Feature: Support the Output Parameters

[mikependon]

Describe the enhancement

Currently, RepoDB is not supporting the output parameters and/or there is no other way to extract the values.

Let us say, you had created the Stored Procedure below.

CREATE PROCEDURE sp_Multiply
(
    @Input1 INT,
    @Input2 INT,
    @Output INT OUT
)
AS
BEGIN
    SET @Output = (@Input * @Input2);
END

There is no way for us to get the value of the @Output parameter, unless we do it via native ADO.Net.

The current alternative is to return the output value as SCALAR and use the ExecuteScalar method instead. But that requires a small modification on the SP.

var output = connection.ExecuteScalar<decimal>("EXEC sp_Multiply;", new { Input1 = 100, Input2 = 200 });

Proposal

The QueryField object must support the ParamDirection enumeration so the Output value can be utilized. The value of the output parameters must be set back to the Parameter.Value of this object.

var outputQueryField = new QueryField("Output", ParamDirection.Output);
var params = new QueryField[]
{
    new QueryField("Input1", 100),
    new QueryField("Input2", 200),
    outputQueryField
};
var output = connection.ExecuteScalar<decimal>("EXEC sp_Multiply;", params);
/* Get the value from the `outputQueryField.Parameter.Value` */

Or, an object OutputQueryField must be introduced that wraps the proper value of the ParameterDirection of the QueryField object.

var outputQueryField = new OutputQueryField("Output");
var params = new QueryField[]
{
    new QueryField("Input1", 100),
    new QueryField("Input2", 200),
    outputQueryField
};
var output = connection.ExecuteScalar<decimal>("EXEC sp_Multiply;", params);
/* Get the value from the `outputQueryField.Parameter.Value` */

Additional Information

This is up and open for discussion with the .NET community.

[cajuncoding]

Personally I think that OutputQueryField class is more explicit and feels less confusing between stored procedures and normal queries where there is no parameter direction and may be confusing.

Or perhaps something different for stored proc invocations like DirectionalQueryField or ProcedureQueryField... as a non breaking enhancement, as this could inherit from the base QueryField.

[mikependon]

@cajuncoding , thanks for the reply.

Though there are pros to this, but you are correct that the OutputQueryField is too explicit on its purpose and could limit the further usability, specifically, we are not introducing an opposing object like InputQueryField.

The proposal DirectionalQueryField is good both to its name and its purpose. The only deviation to this is, it would somehow bring the property ParamDirection into this object or even completely eliminate it, which in my case, limiting the underlying base class QueryField capability. I somehow like to put such property there so the developers can polymorph the derived classes (always) without affecting the state of the object and can still explicitly set the ParanDirection property based on their preference. But again, there are pros and cons to this, like easy AOT compilation vs separation of concerns.

What do you think in your case as a user your preference?

[onuromer]

ParamDirection in QueryField would be better and -more important- simple. I think creating a new object just with one additional Enum (ParamDirection) would be an overkill. As a user I would prefer keeping it simple and inline with the standard functionality (SqlParameter) as much as possible.

Cheers!

[onuromer]

Just a temporary workaround:

Since RepoDB supports sending queries manually, we can overload SQL Query and use standard _ExecuteQuery_ or _ExecuteScalar_ to retrieve OUTPUT parameters from an execution for simple use-cases.

var sql = "DECLARE @OutParam BIGINT; EXEC MYPROCEDURE @Input1, @OutParam OUTPUT; SELECT @OutParam;"
var inputParams = new { Input1= "ABCDE" };
var result = Connection.ExecuteQuery(sql, inputParams);

//use result :)

[mikependon]

It is a lame that library is not supporting this, but TBH, you approach @onuromer is a very good hack, in which something I would recommend to the others up until this story feature is implemented. Thanks for this 👍🏼 🚀

[cajuncoding]

I'll just add a couple more thoughts...

I don't really agree that the idea that adding Parameter Direction to the existing QueryField class is more simple. It increases the scope and responsibility of that class whose primary use case (for the majority of users) will be as a _where condition field_ (not a parameter). This may be in part due to the re-use of this QueryField class for Stored Proc execution also, but going forward adding direction to the field may actually make this class more complex & less intuitive due to larger scope of responsibility.

Honestly, just adding the additional field to the existing class feels a lazy in my mind and doesn't clearly separate the actual function and responsibility between Stored Proc parameters vs SQL Where clause filtering....

As a user that only occasionally uses Stored Procedures, and rarely uses them to send data back outside of the result/return value, this could make my usage of QueryField less intuitive; why would I need to see the direction parameter in an overload or as an optional param, becuase it has zero use as a _where condition_. In addition it may also make the RepoDB code potentially more complex, as any and all uses of the QueryField class would need testing, and possibly updates, etc.

Whereby separating responsibility of advanced handling of parameters from the existing QueryField into a new field class actually simplifies the use of both. To summarize, just because it may be less code in the class itself, does not make it more simple (or less complex). To follow on this thought process, what happens when we find another feature for Stored Proc parameters that isn't fully supported? Do you then add that also in the same way, because you have now established that the pattern of expanding scope of QueryField is right.... further expanding the responsibility, when I'd bet that the vast majority of uses of the class are fore where condition filtering.

@mikependon per my original thoughts, yes I may not had been clear on that point, but I was definitely thinking that a new class with the additional support for parameter features (e.g. Direction) would inherit from the existing QueryField for consistency and as a non-breaking change. And then yes, polymorphically you'd have more features for the new class, but existing code could work as it is by casting back to QueryField. And after some additional thought, it seems apparent to me that there should be a ParameterField class which has these capabilities instead of only a "DirectionalQueryField". This would perhaps be more consistent with the convention in place for OrderField, QueryField, etc.

[onuromer]

I'll just add a couple more thoughts...

I don't really agree that the idea that adding Parameter Direction to the existing QueryField class is more simple. It increases the scope and responsibility of that class whose primary use case (for the majority of users) will be as a _where condition field_ (not a parameter). This may be in part due to the re-use of this QueryField class for Stored Proc execution also, but going forward adding direction to the field may actually make this class more complex & less intuitive due to larger scope of responsibility.

Honestly, just adding the additional field to the existing class feels a lazy in my mind and doesn't clearly separate the actual function and responsibility between Stored Proc parameters vs SQL Where clause filtering....

As a user that only occasionally uses Stored Procedures, and rarely uses them to send data back outside of the result/return value, this could make my usage of QueryField less intuitive; why would I need to see the direction parameter in an overload or as an optional param, becuase it has zero use as a _where condition_. In addition it may also make the RepoDB code potentially more complex, as any and all uses of the QueryField class would need testing, and possibly updates, etc.

Whereby separating responsibility of advanced handling of parameters from the existing QueryField into a new field class actually simplifies the use of both. To summarize, just because it may be less code in the class itself, does not make it more simple (or less complex). To follow on this thought process, what happens when we find another feature for Stored Proc parameters that isn't fully supported? Do you then add that also in the same way, because you have now established that the pattern of expanding scope of QueryField is right.... further expanding the responsibility, when I'd bet that the vast majority of uses of the class are fore where condition filtering.

@mikependon per my original thoughts, yes I may not had been clear on that point, but I was definitely thinking that a new class with the additional support for parameter features (e.g. Direction) would inherit from the existing QueryField for consistency and as a non-breaking change. And then yes, polymorphically you'd have more features for the new class, but existing code could work as it is by casting back to QueryField. And after some additional thought, it seems apparent to me that there should be a ParameterField class which has these capabilities instead of only a "DirectionalQueryField". This would perhaps be more consistent with the convention in place for OrderField, QueryField, etc.

@cajuncoding: Good points indeed. I've looked at this from your perspective and I would say that you are right. I also agree. Well explained.

[mikependon]

@cajuncoding - currently, RepoDB supports various objects for defining an expression (i.e.: QueryGroup, QueryField, Anonymous Types, Dictionary<string, object> and ExpandoObject). Though it would make this library more hybrid, but really I think, it is too much by now to add a new expression-object just for this purpose like ParameterField.

I agree with your explanation above, where @onuromer agreed as well.

Also, it is important to take note that the QueryField object is wrapping both the Field and Parameter object (as corresponds to the WHERE (Field = @Parameter) in SQL). An object of like DirectionalQueryField would be the best middle-ground for this purpose, in which I think, really separating the responsibility of the class, at the same time, making and avoiding the complexities of the usage/implementation in the underlying base class QueryField.

For me, this argument is enough to proceed with this, but of-course unless somebody objects. 😺

[cajuncoding]

An object of like DirectionalQueryField would be
the best middle-ground for this purpose, in which I think, really separating the responsibility of the class, at the same time, making and avoiding the complexities of the usage/implementation in the underlying base class QueryField.

@mikependon Yep makes sense 👍

[mikependon]

This is now available at v1.12.6. The documentation is not yet completed, we are yet to update it.