If you’ve used the SQL Adapter in CTP3 of the BizTalk Adapter Pack V2, you’ll notice that there are two nodes in the Metadata Hierarchy for Stored Procedures – one is named “Procedures”, and the other is named “Strongly-Typed Procedures”. What’s the difference, and what are the scenarios which they are meant to solve? What are their limitations? I’ll try to explain all this, in this post.
1. The first method of executing Stored Procedures which we added in the adapter, manifested as the operations under the “Procedures” node in the Metadata hierarchy. The operations here have the action “Procedure/<database_schema_name>/<procedure_name>”. For these operations, the adapter reads the System Tables in SQL Server, finds out the parameters to the procedure, and exposes them as IN or INOUT parameters in the WSDL. However, we yet need a way to return the result sets which the Stored Procedure can return at execution time. The adapter exposes these result sets as a return parameter, of type System.Data.DataSet. Being an array, multiple result sets can be returned.
At runtime, the adapter executes the Stored Procedure using the ADO.NET function SqlCommand::ExecuteReader(). Each result set returned by executing the Stored Procedure is serialized into its own DataSet; hence all of them together appear as DataSet. The response XML message contains both, the schema for each result set / DataSet, as well as the actual data in that result set / DataSet. When used in a .NET application, the schema + data together are used when deserializing the SOAP message into a DataSet object.
Advantages: Works for all stored procedures.
Limitations: The result sets are loosely typed (being a DataSet), and in BizTalk, are not helpful if you want to use the Mapper.
Workarounds: What you could do is, execute the procedure once, dump the XML message to a file location, and open it in notepad. Select the <schema> node within the result set section in the return parameter, copy-paste it into a new file, and save it with the .xsd extension. You now have a schema which you can deploy in your BizTalk orchestration/project. Also, use the Message Template feature in the WCF-Custom/WCF-SQL port configuration, using a XPath query to only select out the data (at runtime) for that particular result set (matching the XSD which you deployed); ignoring the other result sets (if any) and out parameters. You now have a XML blob being submitted to BizTalk, which conforms to the XSD which you deployed.
2. The next approach we took, was to try and expose the result sets as “strongly typed”, instead of the loosely typed DataSet above. These operations manifest as the operations under the “Strongly-Typed Procedures” node in the metadata hierarchy. The action is of the form “TypedProcedure/<database_schema_name>/<procedure_name>”. For these operations, the adapter reads the System Tables in SQL Server, finds out the parameters to the procedure, and exposes them as IN or INOUT parameters in the WSDL. In order to expose the returned result sets in a “strongly typed” fashion, the adapter needs to know what the metadata for the returned result sets will look like. For this purpose, at design time, the adapter executes the Stored Procedure using the ADO.NET function SqlCommand::ExecuteReader(CommandBehavior::SchemaOnly). This translates to the SET FMTONLY ON option being used. However, in order to execute the Stored Procedure, the adapter needs to pass in values to the parameters. For this purpose, the adapter uses DBNull.Value for each parameter. Upon execution, the adapter gets back multiple (empty) result sets, and from these, the adapter can obtain the metadata for the result sets which can potentially be returned at runtime.
Note – I use the word potentially. This is because, at runtime (FYI – the adapter uses the ADO.NET function SqlCommand::ExecuteReader() at runtime), a Stored Procedure can return different result sets based on the input values. For example, if an input parameter has the value 1, the Procedure could return a result set by performing the operation “SELECT * FROM TABLE1”. If the input parameter has the value 2, it might actually execute “SELECT * FROM TABLE2”. At runtime, only one of the two result sets will be returned, depending on whether the input parameter is 1 or 2. However, at design time, when the SET FMTONLY ON statement is used, both the (empty) result sets are returned. The adapter exposes them both as complex out parameters (and names them in the metadata as TypedProcedureResultSet1, TypedProcedureResultSet2, etc). At runtime, one will be null, while the other will have the appropriate data filled in.
Note – The current design of the WCF LOB Adapter SDK (on which the SQL Adapter is based) is that metadata is also required at runtime. Hence, at runtime too, the adapter executes the Stored Procedure using SET FMTONLY ON (just once) to obtain the metadata. Then, for every message being passed to the adapter, the adapter executes the Stored Procedure, and based on the result sets returned, tries to determine whether it should be serialized as TypedProcedureResultSet1, or TypedProcedureResultSet2, etc (for example) (based on the metadata it obtained earlier by using SET FMTONLY ON).
Advantages: Strong typing of the result sets.
3. In CTP4 (releasing end of October 2008), we’ve added one more mechanism to execute Stored Procedures. This was mainly done for backward compatibility.
The earlier SQL adapter required result sets returned from Stored Procedures to use the FOR XML syntax. This was because the old adapter used SQLXML; using FOR XML would instruct the SQL Server to return the result set as a single XML value, and the SQLXML code on the client would then parse the XML returned.
In methods 1 and 2 above, you’ve seen that the new adapter used the ADO.NET SqlCommand::ExecuteReader() function. If this function was used to execute a Stored Procedure which returned a result set using FOR XML, all the adapter would see is a single XML value (exposed as a string) – i.e., the adapter thinks that the returned result set only has one column. Even worse, if the XML value was large, it would get split into multiple rows. This is of course extremely cumbersome to work with.
For this purpose, a third mechanism was added, what I call “XML Procedures”. The action for such operations is “XmlProcedure/<database_schema_name>/<procedure_name>”.
There is no design time experience for such procedures. Generating metadata involves the following:
At runtime, you need to do this:
When the adapter sees the XmlProcedure action, it uses the ADO.NET SqlCommand::ExecuteXmlReader() function.
Advantages: You can continue using FOR XML Stored Procedures side-by-side with the old SQL Adapter. Can work with all stored procedures which return XML.
Limitations: There is no way to generate the metadata using the adapter, since the WCF LOB Adapter SDK does not have a way to accept input parameter values during design time. The adapter cannot use NULL for the parameter values (unlike how it did in 2 above), since obtaining metadata involves actual execution of the Stored Procedure, and therefore all the Stored Procedure logic comes into play, which might throw an error if an unexpected value is seen for an input parameter.
Workarounds: Use one of the other Stored Procedure execution methods.