WF 4.5: Is it possible to add variables dynamically at runtime?

Question

We are writing a custom activity. In this activity it is possible to set a database connection string and a name for a stored procedure. At runtime the stored procedure is executing. Now we have some stored procedures which has input parameters.

Is it possible to generate variables dynamically in WF 4.5 for each input parameter in the stored procedure? Reading the parameters from the stored procedure is not the problem, but I dont have any idea how to generate the variables.

Example: The user enters a name for the stored procedure to be executed (2 input params @Variable1 and @Variable2). Now in the variables tab should be 2 variables: @Variable1 and @Variable2. If the user changes the name in the stored procedure then in the variables tab should be the new params (for example only @Variable2)...

We spent a lot of time on this issue. But the only thing we have learned is that the activity has to be a NativeActivity and the variables should be added in the CacheMetadata method. But if I add a variable with AddVariable() method nothing happens :(

Answer 1

If you are open to including third-party libraries, you could try using an ORM tool like Dapper to accomplish this. A Dapper query generally takes an anonymous type to supply its parameters. Typical code for creating a custom object from fields in a database would look something like this:

IDbConnection db = New IDbConnection(...);
int id = 527; // normally passed in - using a hard coded value would defeat the purpose...
string myQuery = "SELECT Engine, Transmission, Make, Model, BodyStyle FROM Table WHERE ID = @ID";
Car result = db.Query<Car>(myQuery, new { ID = id }).First();

So I believe that you could use reflection to pass in the type ("Car") using reflection and create an anonymous object or pass in an actual object with an "ID" property at runtime. It will automatically create a custom Car object with the resulting data, assuming that the Car object has properties of Engine, Transmission, Make, Model, BodyStyle, etc.

Note that if you don't supply the type you expect to get back, you get an ExpandoObject: Creating an anonymous type dynamically? - you may also be able to pass one of these in for your input parameters, which would mean that you could create it at runtime.

This guy came up with a generic method for Dapper that may help you: http://www.bradoncode.com/blog/2012/12/creating-data-repository-using-dapper.html

What he came up with is something like this:

IEnumerable<T> items = null;

// extract the dynamic sql query and parameters from predicate
QueryResult result = DynamicQuery.GetDynamicQuery(_tableName, predicate);

using (IDbConnection cn = Connection)
{
    cn.Open();
    items = cn.Query<T>(result.Sql, (object)result.Param);
}

return items;