Fetching Data From Multiple Tables Through Dynamically Invoking Stored Procedure

{tocify} $title={Table of Contents}

Introduction



There is a comment/question posted on following post:Polling data from SQL using WCF-SQL Adapter in BizTalk 2010 - TypedPolling(From Multiple table)  where Vinoth posted the scenario:

"Please help me, I have to poll the data from 15 Tables, Table by Table, should not join in to single select statement. For example, if I have 15 Tables to poll, I have to poll Table1 and store as XML and i have to poll Table 2, then 3, 4, 5...
Multiple Table polling by looping, how to do it dynamically poll the data Table by Table...
Please provide me the solution? its very urgent... "


There are situations where we have to configure WCF port dynamically,

1. The information for the configuration is available at runtime through some content in message or through some other way.

2. The configuration information is already known but it can change or has to be changed depending on the business needs.

To demonstrate how to configure WCF Port dynamically and how  to Invoke stored procedures dynamically, have used following scenario:

Scenario:

 I have to poll the data from Tables, one by one and save it on file system. The challenge is the number of tables can increase or can be reduced .For this demonstration , I have used 3 tables (Logic remains same for 15 tables as well).

Ok, as per the requirement posted the configuration of the port which fetches data has to be done dynamically and in a loop, so nothing has to be static inside orchestration related to the storedprocedure thus used BRE as it provides a way to do changes without having to change anything in orchestration after initial CallRules configuration. 


I have made some assumptions:

1. There is chances of changes in number of tables to be fetched - thus have used BRE which has provision of changing number of table without touching the Orchestration.
2. The response received is not to be mapped, instead it is to be saved as it is.
3. Invoking stored procedure can be anywhere in the designed process, thus have used dummy Order to instantiate the orchestration (which has nothing to do with logic)

Building the solution:


Database


a. Creating three tables

creating tables

Figure1. Demo Database with three tables


Figure2. Order table design

creating tables design 
Figure3. Order1 table design

creating tables design 1
Figure4. Order2 table design


b.Creating three stored procedures with simple query


creating stored procedure 1
Figure5. uspGetOrder1 stored procedure
creating stored procedure 2
Figure6. uspGetOrder2 stored procedure
Figure7. uspGetOrder stored procedure
 tables and stored procedure 
Figure8. Three tables and respective stored procedures

BRE


a. Creating Policy GetConfigDetails

create bre policy

Figure9. GetConfigDetails Policy

b.Creating four rules under GetConfig

The first rule is used to set the number of tables to be fetched.

rule 1

Figure10. GetConfigDetails Policy - Rule1

The rest three rules are used to set the properties i.e. Action,Address,Bindingtype and TransportType which are required to configure the port dynamically.

rule 2

Figure11. GetConfigDetails Policy - Rule2

rule 3

Figure12. GetConfigDetails Policy - Rule3

rule 5

Figure13. GetConfigDetails Policy - Rule4

   

BizTalk Solution


For solution the name given is DynamicInvocationOfStoredProcedures which has schemas(initiating message, Configuration message , storedprocedure message), Orchestration, strong name key(used to sign the project) and binding files which are generated by wizard while creating schemas from stored procedures. Steps are as below.

biztalk solution
Figure14. Solution structure

a. Create schema for message to instantiate the orchestration

create schema

b. Create schema for message to hold configuration details - which is updated using BRE

All the elements are promoted as distinguished field so as to get/set values from those in orchestration
create config schema

c. Generate schemas from the stored procedures which are to be executed dynamically

We need to do this so that we can refer to the binding files generated to get configuration details.

1. Right click the project and  select Add-->Add Generated Items
2. Then select "Consume Adapter Service"
3. Now the Consume Adapter Service wizard wants us to provide it the required information, so firstly select "sqlbinding" against the label Select a binding.
4. Click the Configure button, on security tab select the Client credential type
5. On URI Properties tab:
      i.Type the name of the server where your database is located ( I have used "." as the database resides on same machine)
     ii. Provide the name of the database you want to use.
     iii. I have left InstanceName as blank because am using the default instance of SQL (If need to use some other instance then provide the name of that instance)

consume adapter service

6. After done with above configuration,click on Connect then select Contract type as Client(Outbound operations)
7. Select the category as Procedures then select procedure based on which schema is to be generated, click ADD
8. Give Filename Prefix (it's optional - but it helps) and click ok
client outbound operations
Figure. Generating schema from uspGetOrder storedprocedure

9. Repeat above steps for other stored procedures


Create Orchestration



create orchestration
Figure.PollMultipleOneByOneDynamically Orchestration

The orchestration as shown above has following variables and types used in it


Ports and Port Type
1. prtRcvStart of type prttypeRcvStart - to receive intial message thus binded to Order schema as intial message is based on it

2. prtExecuteSP of type prttypeExecuteSP - it is dynamic port to send message to invoke storedprocedure and to receive response from storedprocedure,binded to System.XMLDocument.
Why System.XmlDocument? - As per the requirement multiple storedprocedures are to be invoked but those are not static and are to be set dynamically so this port can't be binded to single schema. And as System.XmlDocument is the base schema for all schema it can be used in the scenario where generic approach is required.

3.prtSndInvokeSPResp of type prttypeSndInvokeSPResp - to send the response received from stored procedure to a folder

orchestration variables and types

Figure.PollMultipleOneByOneDynamically Orchestration variables and types

Message and  Variables
1. msgstart - based on Order schema, used to instantiate the orchestration
2. msgConfig - based on Config schema, used to get config details from BRE
3. msgInvokeSP - based on System.XmlDocument, used to send message expected by StoredProcedure
4. msgInvokeSPResp - based on System.XmlDocument, used to get response from stored procedure
5. varLoop - Int variable to hold the value (number of times loop is to be run)
6. varAction - String variable to hold Action value set on msgConfig by GetConfigdetails policy
7. varAddress - String variable to hold Address value set on msgConfig by GetConfigdetails policy
8. varBinding - String variable to hold Binding value set on msgConfig by GetConfigdetails policy
9. varTransportType- String variable to hold TransportType value set on msgConfig by GetConfigdetails policy
7. xmlDoc - Variable of type System.XmlDocument

Steps in Orchestration

1. Receive start message
2. Construct the config message in message assignment shape, this message will be passed, here to note is that GetTableNumber element has values as Yes  
Get Table Number

 3. Configure  the CallRules shape, select the GetConfigdetails policy and message expected by it  - msgConfig

policy editor
4. Assign  the Loop variable to the value received in NumberOfTables field of msgConfig
loop variable
5. Set the Loop condition
set loop condition

6. Inside loop , call the policy
call Policy
7. Assign  the varAction,varAddress, varBinding,VarTransportType to the value received in msgConfig


Assign varAction



8. Construct the message to invoke the stored procedure and set it's WCF.Action property - this is where we are dynamically setting the action - which we get from Policy we execute in step 6
Set wcf action



9. Send the message to prtExecuteSP and collect the response
10. Send the collected response to the folder
11. So  now reset the loop variable by reducing it by one and also now next table is to be fetched so set TableNumber field of msgConfig respectively.



configure message assignment shape







Allright, so why CallRules is used twice in above Orchestration?

Considering that number of tables to be fetched can be changed, the first callrule is used to tackle this scenario. So when Orchestration passes config message to rule engine having GetTableNumber as Yes, then  the NumberOfTables is set  and returned. And in loop, Orchestration is interested in knowing which StoredProcedure is to be executed(the WCF.Action property) and here second callrule is used to get the Action against the TableNumber.

If you see at Rules above, it will be more clear :)

e. Build, Deploy and Configure


After deployment the dynamic port is automatically added to the Application in the Admin console, whose configuration we did in orchestration dynamically.

deploy application




So we just need to configure one Recieve Port, one static send port and bind it to Logical ports of Orchestration
Orchestration Bindings

Figure. After  Binding Orchestration

To test I dropped the instance of Order message (message which instantiates the orchestration) and three xml files were generated in the folder which is binded to the port which sends the response received from stored procedures.

output files


Note : System.XmlDocument is used as base for the message  to communicate with stored procedures so it's untyped . There are two limitation that the response received can't be used in any map and if there is subscriber in your group for untyped message then copy of it will be passed to it as well.




Download Sample:


If you have questions or suggestions, feel free to do in comments section below !!!


Do share if you find this helpful .......
 
                          Knowledge Sharing is Caring !!!!!!


5 Comments

If you have any suggestions or questions or want to share something then please drop a comment

  1. Hi Mahesh,

    Hope you are doing good,
    Actually am recoded and implemented as per your solution. Now am in the process of implementing Exceptions. I tried multiple scenarios.

    My first loop is looping for number of databases to poll the data from multiple Tables. Second loop is number of Tables loop. In 2nd loop there is Scope and catch block. For the first time, first database is not reachable (for example), Table loop is keep trying to reconnect multiple times even my retry count is 0 for Dynamic send port.

    What my expectation is, if any one of the Database is not reachable, i have continue poll from the other databases. my code is keep trying the not reachable database but its not moving to next database.

    Please help me to resolve this.

    Thanks & regards
    Vinoth

    ReplyDelete
  2. Hi Mahesh,

    Hope you are doing good,
    Actually am recoded and implemented as per your solution. Now am in the process of implementing Exceptions. I tried multiple scenarios.

    My first loop is looping for number of databases to poll the data from multiple Tables. Second loop is number of Tables loop. In 2nd loop there is Scope and catch block. For the first time, first database is not reachable (for example), Table loop is keep trying to reconnect multiple times even my retry count is 0 for Dynamic send port.

    What my expectation is, if any one of the Database is not reachable, i have continue poll from the other databases. my code is keep trying the not reachable database but its not moving to next database.

    Please help me to resolve this.

    Thanks & regards
    Vinoth

    ReplyDelete
  3. Hi Vinoth,

    I am good, thanks. Was away this weekend.

    Have you set Delivery Notification = “Transmitted” , when this property is set to Transmitted it means that your orchestration will receive an exception if the message cannot be send to the destination.Delivery Notification works only when the Retry Count set to 0. When a message cannot be delivered, a DeliveryNotificationException is raised and this exception needs to be handled by the Orchestration (you can just log the error ).
    When you handle exception, the flow will continue else orchestration will be suspended.

    ReplyDelete
  4. hi mahesh,
    please how to get all fields in table , when i am working it gets the first field only please tell me how to do.

    ReplyDelete
  5. Hi,

    Sorry but question is not clear, can you please describe what exactly you want to do?

    ReplyDelete
Previous Post Next Post