Using Equals(...) filters operator with Stored Procedures
At the Filters tab AdHoc allows to select predefined value as filter value, instead of typing it directly. In the case of table used as data source, it works very simply - you just select field name, AdHoc fetches all values for that field from the data source in the DB, groups them and pulls to the dropdown control.
If Stored Procedure is used as data source, this becomes a bit more complex, because of few important things:
-
Stored Procedure usually have some input parameters, and output fields. Obviously, you can get predefined values only for output fields, because, for example, if input parameter is of int type, it has 2147483648 possible values, and there is no sense to put them into dropdown as predefined values.
-
List of predefined values for field F can be created only for the given set of values, i.e. for the given set of records, each of them has definite value in the field F. And result records set of Stored Procedure usually depends on input values set. This means, that list of predefined values can be built only when all input parameters have defined values.
In other words, you can get list of existing values only for output fields (which don't have "(Param)" in the end of name), and only when values for all input parameters are defined.
This will be demonstrated now using Stored Procedure SalesByCategory in the Northwind database. To see this Stored Procedure in the data sources list, you need following line of code in your Global.asax:
AdHocSettings.VisibleDataSources = new string[] {"SalesByCategory"};
Also, you need following line of code there to allow usage of Equals(...) operators for Stored Procedures:
AdHocSettings.AllowEqualsSelectForStoredProcedures = true;
After this, you will be able to select SalesByCategory as data source, and select Equals(...) operators for its' fields. At the figure 1 you can see list of fields for this Stored Procedure.
Figure 1. List of fields for
SalesByCategory.
CategoryName and OrdYear here are input parameters, and ProductName and TotalPurchase are output fields. Following mentioned above, we can get list of predefined values using Equals(...) operator for ProductName and TotalPurchase, after values of CategoryName and OrdYear will be defined. Defining of input parameters values should be done in the same way, as filter values for usual table fields, but using Equals operator only, since they will be passed to the Stored Procedure instead of using in query. At the figure 2 you can see them with some defined values.
Figure 2.
Input parameters values defined.
After this, you can get list of predefined values for any output fields, using Equals(...) operators. At the figure 3 you can see Equals(Multiple) and Equals(Select) selected for two remaining fields.
Figure 3. Predefined values lists.
Note that if you change values of input parameters, predefined values will also change, because they depend on input data (Fig. 4):
Figure 4. Predefined values for another
input parameters values.
Using ProcessEqualsSelectList()
From the example above you can see that it is required to specify all stored procedure parameters.
But if you don't want to specify parameters every time you can predefine them by overriding ProcessEqualsSelectList() method in AdHocConfig.
The example below will give the same results as if you have specified stored procedure parameters in Filters tab.
public override string[] ProcessEqualsSelectList(Izenda.AdHoc.Database.Column column)
{
if (column.Name == "ProductName")
{
string sql = @"EXEC SalesByCategory 'Beverages', '1998'";
DataSet res = Izenda.AdHoc.AdHocContext.Driver.GetDataSet(Izenda.AdHoc.AdHocContext.Driver.CreateCommand(sql));
List results = new List();
foreach (DataRow row in res.Tables[0].Rows)
results.Add(row.ItemArray[0].ToString());
return results.ToArray();
}
return base.ProcessEqualsSelectList(column);
}
Now you can just select "ProductName" and apply "Equals(Select)" filter to it:
Figure 5. Override ProcessEqualsSelectList() results.
See also Part 1.