locked
insert data in database using stored procedure RRS feed

  • Question

  • Hi,

    I need to insert data in many tables by calling a stored procedure in an orchestration.

    What is the best option to perform the task:

    1. use a wcf sql adapter

    2. sql adapter for biztalk

    3. perform the database tasks in a class library and call the functions of the class library in the expression shape in the orchestration

    Please help.

    Regards,

    Sharmishtha

     

    Friday, November 4, 2011 7:22 AM

Answers

  • Yes it is advisable to use the adapters. Prefer the WCF-SQL adapter as the old SQL adapter isn't updated anymore.
    Torben Christiansen http://www.snatchedmoments.com/
    • Marked as answer by Sharmishtha Friday, November 4, 2011 10:27 AM
    Friday, November 4, 2011 10:03 AM
  • Polling database is a diffenrent functionality. It is more like you have to receive some data from Database table/stored procedure at regular intervals, and WCF SQL Adapter's polling functionality (Select Operation) does that for you.

    WCF SQL Adapter also supports Insert, Update & Delete operations. And in your case since you need to insert records into multiple tables using Stored Procedure, you can refer to the links provided earlier to implement same.


    Don't forget to mark the post as answer or vote as helpful if it does, Regards - Sathish Krishnan
    • Marked as answer by Sharmishtha Friday, November 4, 2011 10:27 AM
    Friday, November 4, 2011 10:12 AM

All replies

  • You can use WCF SQL Adapter by Executing composite transactions to perform Insert to multiple tables. Refer following links for 2 different approches.

    http://geekswithblogs.net/StuartBrierley/archive/2011/10/19/biztalk-server-2010---using-the-wcf-sql-adapter-to-make.aspx

    http://learnbiztalk.com/index.php?option=com_content&view=article&id=13:new-soa-capabilities-in-biztalk-server-2009-wcf-sql-server-adapter&catid=4:wcf&Itemid=5

    HTH.


    Don't forget to mark the post as answer or vote as helpful if it does, Regards - Sathish Krishnan
    Friday, November 4, 2011 7:45 AM
  • Hi 

    Agree with Sathish that this is the way to go. I have done so my self and you have a lot of option by using composite transactions.


    Torben Christiansen http://www.snatchedmoments.com/
    Friday, November 4, 2011 8:29 AM
  • Thanks Sathish

    I have an xml file containing repeated records. I need to insert this data in the database.

    So, I need to loop through the records in my xml file and call the stored procedures.

    Is it not better to use a class library to do the task and then later call the function from the orchesatration expression shape.

     

    Regards,

    Sharmishtha

    Friday, November 4, 2011 8:48 AM
  • Incidently I happened to check through following link, which discusses about inserting a multiple occurance record into SQL tables using Stored Procedure. I hope it will suit your requirement.

    http://connectedthoughts.wordpress.com/2009/06/


    Don't forget to mark the post as answer or vote as helpful if it does, Regards - Sathish Krishnan
    Friday, November 4, 2011 8:56 AM
  • Hi

    You loose a lot of standard features that you get with BizTalk like mapping, configuration, retry, guaranteed delivery and more by using a class library, so you should look into composite operations as this is the correct architecture for your type of solution.


    Torben Christiansen http://www.snatchedmoments.com/
    Friday, November 4, 2011 9:05 AM
  • Thanks Torben and Sathish

    The output of 1 stored procedure is the input for the other stored procedure.

    an I implement this scenario using the WCF SQL Adapter by Executing composite transactions .

     

    Friday, November 4, 2011 9:49 AM
  • The output of 1 stored procedure is the input for the other stored procedure.

    Can you elaborate this requirement?
    Don't forget to mark the post as answer or vote as helpful if it does, Regards - Sathish Krishnan
    Friday, November 4, 2011 9:51 AM
  • Sathish

    If we use a SQL/ WCF SQL Adapter  , it polls the database at regular intervals

    In my case i just have to insert the data in the tables

    Is it advisable to use the adapters.

    Friday, November 4, 2011 10:02 AM
  • Yes it is advisable to use the adapters. Prefer the WCF-SQL adapter as the old SQL adapter isn't updated anymore.
    Torben Christiansen http://www.snatchedmoments.com/
    • Marked as answer by Sharmishtha Friday, November 4, 2011 10:27 AM
    Friday, November 4, 2011 10:03 AM
  • Ok , Thanks Torben
    Friday, November 4, 2011 10:05 AM
  • Sathish

    I have an xml file containing 1 customer information + (1-n) information about addresses of the customer + ..........  so on

    The insertcustomer SP returns a customerID.

    This customerID needs to be passed to the insertAddress SP.

    I need to loop the input xml file and for each customer each address execute the SPs.

    Can I do this using the WCF SQL Adapter by Executing composite transactions

    Friday, November 4, 2011 10:11 AM
  • Polling database is a diffenrent functionality. It is more like you have to receive some data from Database table/stored procedure at regular intervals, and WCF SQL Adapter's polling functionality (Select Operation) does that for you.

    WCF SQL Adapter also supports Insert, Update & Delete operations. And in your case since you need to insert records into multiple tables using Stored Procedure, you can refer to the links provided earlier to implement same.


    Don't forget to mark the post as answer or vote as helpful if it does, Regards - Sathish Krishnan
    • Marked as answer by Sharmishtha Friday, November 4, 2011 10:27 AM
    Friday, November 4, 2011 10:12 AM
  • Hi

    Is there any possiblity that you can control the input to the CustomerID, so it is Biztalk that creates the key? Could be a guid.

    If the customerID is an identity column is this the only cause of input data to the customer table, then you can use the property "AllowIdentityInsert" in the binding for the WCF-SQL send port.

    Otherwise is there a chance to add a extra field to both customer and address to a guid column. This would mean you would do this in your composite operation, where you assign the same guid to rows in both address and customer:

    - insert customer

    - insert address

    - Execute stored procedure to assign customer ID on address based on the same guid value both in customer and address records.


    Torben Christiansen http://www.snatchedmoments.com/
    Friday, November 4, 2011 10:27 AM
  • Sharmitha,

    Refer to the following blogs which addresses similar requirement of yours by executing stored procedure from WCF SQL Adapter.

    http://connectedcircuits.wordpress.com/2011/08/17/using-sql2008-data-table-type-biztalk-to-insert-parent-child-rows/

    http://blogs.msdn.com/b/biztalkcpr/archive/2009/10/05/inserting-parent-child-records-with-identity-column-using-wcf-sql-adapter-in-one-transaction.aspx

    HTH.


    Don't forget to mark the post as answer or vote as helpful if it does, Regards - Sathish Krishnan
    Friday, November 4, 2011 10:47 AM
  • Thanks Torben and Sathish

    Following your advice I should be able to solve the scenario.

    Friday, November 4, 2011 11:24 AM