What is the best practice to insert multiple records to DB and get multiple records from DB in single instance using BizTalk?

Question

I want to develop a BizTalk orchestration. Which should insert multiple records into multiple DB tables and retrieve inserted records from multiple DB tables, in single instance of orchestration. For this requirement, I'm able to insert the data in one instance, but seeing difficulty to retrieve the inserted data for that instance, as all the records has unique values for each record. For my situation, I should use stored procedures, to apply some other business logic. So I have 2 different methods by using "Wcf_Custom Adapter composite feature" by calling stored procedures, as stated below.

-> Method1
I have to develop a Stored procedure, which takes LoadDate("2016-05-12 10:11:22.147") as parameter along with inserting values and it will take care of inserting the records for that instance, by keeping the given LoadDate. Then immediately it will call Get stored procedure, which takes the LoadDate("2016-05-12 10:11:22.147") as parameter, then it will retrieve the recently inserted records from DB based on LoadDate value.

I know, Retrieving the data based on a date value from sql server is a bad practice and it will give performance issues too.

-> Method2
I'll design the inserting tables, with bool data type column name "New" and value will be 0 or 1. I'll develop a Insert Stored procedure, which inserts the data by giving the "New" column value as "1". Then immediately it will call Get stored procedure, which will not take no parameters, then it will retrieve the recently inserted records which are having "New" column indicator "1" from DB tables. Once it retrieves the data, then it will update "New" column value to "0".

I prefer this method2. But, do we have better option?

Is there a reason for all this complication? You can use Table Value Parameters to pass everything to one SP and then pretty much do a direct insert to the tables. You can return Table Value type columns as well. Also, there is nothing wrong with retrieving data based on a date. — Johns-305, May 12 '16 at 19:17

score 0 · Answer 1 · answered May 12 '16 at 18:49

0

I think your stored procedure may look like this:

create procedure myProc
@a int, @b varchar(100)
as
insert myTable(a,b,c)
OUTPUT inserted.* --this line
select a,b,c
from somewhere
where a=@a and b=@b

answered May 12 '16 at 18:49

Alex Kudryashev

8,484
3
24
31

score 0 · Answer 2 · answered May 12 '16 at 23:29

As @johns-305 mentioned in his comment. You shall use table value param in your sp. and assembly all your data in orchestration then make a call to this sp.

A sample sp may like below:

CREATE TYPE [dbo].[SampleDataTable_Type] AS TABLE(
    [ID] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)

GO 

CREATE PROCEDURE [dbo].[sp_InsertSampleTableData]
(
    @LoadDate DATETIME,
    @data [SampleDataTable_Type] READONLY
)
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO your_table(id, name,)
    SELECT id, name FROM @data;

    --Do whatever you want

    SET NOCOUNT OFF
END

GO

What is the best practice to insert multiple records to DB and get multiple records from DB in single instance using BizTalk?

2 Answers2