This project has moved. For the latest updates, please go here.

Using Parameters - Add ability to use TVP with CommandType.Text

Aug 25, 2016 at 5:17 PM
It would be nice if the DbParallel.DataAccess partial class was updated with the ability to use TVP parameters with command text (as opposed to just stored procedures). It looks easy enough to do and would really help us out (we create a lot of dynamic sql and don't want to have to create a stored procedure for each).

If anyone has a workaround for this, I'd love to hear it.
Coordinator
Aug 26, 2016 at 4:51 PM
Edited Aug 26, 2016 at 5:18 PM
Not sure which method were you trying to use with TVP parameters.

Actually, TVP parameter is already supported by all methods prefixed with "Execute".
The DefaultCommandType of ExecuteReader(...), ExecuteMultiReader(...) and ExecuteNonQuery(...) methods are just default to StoredProcedure, but not limited to StoredProcedure.

There are typically two kinds of practices:
  1. If most of your cases will use StoredProcedure, leave the DbAccess.DefaultCommandType as it is;
    Then you can use shorter overloads (fewer parameters required) of Execute... methods for most cases,
    and use longer overloads (more parameters required) of Execute... mothods (with commandType: CommandType.Text) for some particular cases.
  2. If most of your cases will use SQL Text command (dynamic sql), please set the DbAccess.DefaultCommandType = CommandType.Text;
    Then you can use shorter overloads (fewer parameters required) of Execute... methods for most cases,
    and use longer overloads (more parameters required) of Execute... mothods (with commandType: CommandType.StoredProcedure) for some particular cases.
See also https://databooster.codeplex.com/#api-dbaccess-static-global.

To add/set a TVP value for a stored procedure or a SQL Text command, you can either:
  • parameters.AddTableValue("inTvp", tvpValue); // Mainly for stored procedure
    or
  • parameters.AddTableValue("inTvp", "YourDatabaseSideTableValueTypeName").SetValue(tvpValue);
    // Mainly for sql text command
The tvpValue can be a DataTable, a DbDataReader or any IEnumerable<object> (the object can be anonymous type instances or named type instances). The AddTableValue or SetValue method internally takes care the data transformation as needed (based on what kind of value you pass in) for you.

For example,
DbAccess.DefaultCommandType = CommandType.Text;
....
dbAccess.ExecuteReader("Your dynamic sql text", parameters =>
    {
        parameters.Add("inDate", date);              // Simple Parameter
        parameters.AddTableValue("inTvp", "YourDatabaseSideTableValueTypeName")
                  .SetValue(tvpValue);               // Table-Valued Parameter
    },
    ....
);
....
See also https://databooster.codeplex.com/#table-valued-parameters


Could you try again, and let me know if you have new progress?

If it still doesn't work, would you mind posting your example code about the relevant parts? That would help me further understand your actual situation about the issue.