Biztalk SQL Adapter or a .NET SqlClient

Question

My application has BizTalk orchestration which needs to do basic insert into a table. Which is the better way of doing it

• using a SQL adapter that calls the stored procedure(with just one insert statement) on SQL server
• including a method call on orchestration where the method uses the SQLclient.SQLCommand.ExecuteNonQuery method with the SQLstored procedure on the GetCommand.

I want to know the best way of inserting the data into a database in the BizTalk orchestration.

Answer 1

You should always prefer using the SQL Adapter.

1. It will be tracked in the Group Hub
2. You'll get better diagnostics and tracking options around the port
3. Retry logic is built in and configurable

The biggest downside is performance - it will create another persistence point in your orchestration on the send shape, whereas an inline SQL call would avoid that.