cmd.AddTableParam - lobodava/artisan-orm GitHub Wiki

cmd.AddTableParam is SqlCommand extension method which:

  • takes IEnumarable<T> collection as parameter,
  • creates ADO.NET DataTable,
  • populates the DataTable with data from the collection,
  • creates SqlParemeter with SqlDbType.Structured type,
  • add this parameter to the calling command.

To ensure that a stored procedure is able to get the data from table valued parameter, your database MUST have a corresponding user-defined table type created in advance.

Moreover, the DataTable which is added as SqlDbType.Structured type parameter MUST have the same name as the user-defined table type in the database.

Even more! The quantity, names and types of columns in the DataTable MUST correspond to that in the user-defined table type.

So to add table parameter it is required to provide that information.

Using mappers

One way is to use Mappers and their CreateDataTable and CreateDataRow methods.

For example, if a Mapper with CreateDataTable and CreateDataRow methods exist for a class User, then cmd.AddTableParam for the IEnumerable<User> is:

public void SaveUsers(IList<User> users)
{
    return ExecuteCommand(cmd =>
    {
        cmd.UseProcedure("dbo.SaveUsers");

        cmd.AddTableParam("@Users", users);
    }
}

Using auto-mapping

Another way is to use auto-mapping which:

  • uses the reflection and gets information about column types and values from object PropertyInfo,
  • builds the expression trees,
  • compiles them to CreateDataTable and CreateDataRow Func delegates,
  • caches the Func delegates to dictionary with a composite string key,
  • the composite string key consists of three parts: full type name, table name and concatenated column names.

Thus every next call of auto-mapping function uses cached Func delegates and the performance is almost the same as the use of the handwritten mappers.

Although the auto-mapping does the magic, it is still required to provide name for DataTable and a list of columns to create. And the names of columns MUST correspond to the existing object properties.

Here is an overload for cmd.AddTableParam method which uses auto-mapping:

public static void AddTableParam<T>(
    this SqlCommand cmd  ,   // calling command
    string parameterName ,   // stored procedure parameter name with leading at sign - @
    IEnumerable<T> list  ,   // list of T objects
    string tableName     ,   // name of DataTable and corresponding user-defined table type    
    string columnNames   )   // comma separated list with column and corresponding properties names

Use example:

public void SaveUsers(IList<User> users)
{
    return ExecuteCommand(cmd =>
    {
        cmd.UseProcedure("dbo.SaveUsers");

        cmd.AddTableParam("@Users", users, "UserTableType", "Id, Login, Name, Email");
    }
}

If to omit the list of column names, then the columns will be created based on the found public, non static, simple properties, which has get method.

See also:

⚠️ **GitHub.com Fallback** ⚠️