从BizTalk执行SQL存储过程 [英] Execute SQL stored procedure from BizTalk

问题描述

我在BizTalk编排中要完成三个任务

I have three tasks to do in my BizTalk orchestration

1. 使用动态参数执行存储过程
2. 插入数据库
3. 在数据库中进行更新

示例数据

存储过程[databasename].[storedprocedurename] 'param1Value', 'param2Value'

表[databasename].[tablename] (id integer,desc nvarchar(50))

更新

措辞:任务是

1. 如何使用传入XML消息中的参数从BizTalk业务流程中调用存储过程

1. How to call stored procedure from BizTalk orchestration with parameters from incoming XML message

如何从业务流程插入数据库表(并返回操作结果)

How to do insert into DB table from orchestration (and get back result of operation)

如何使用传入的xml中的动态"where"值条件进行更新

How to do update with dynamic "where' value conditions from incoming xml

推荐答案

此处有完整的文档:使用BizTalk Server在SQL Server中执行存储过程

1. 创建您的存储过程(例如usp_Test)以进行插入/更新.包括将在您的WHERE子句中使用的参数.
2. 使用添加"->添加生成的项"->消费适配器服务"为存储过程生成架构和绑定文件.
3. 从您的消息创建到存储过程模式的映射
4. 在逻辑发送端口上更新操作以匹配SP名称(usp_Test)
5. 部署您的应用程序.
6. 将步骤2中的物理发送端口的绑定导入到您的应用程序
7. 将业务流程绑定到此发送端口(或可能基于BTS.Operation在发送端口上创建筛选器).
8. 使用已在导入的发送端口上设置的地图和过滤器(编排)来创建端口.

1. Create your stored procedure (e.g. usp_Test) to do your inserts/updates. Include parameter(s) that would be used in your WHERE clause(s).
2. Use the Add->Add Generated Items->Consume Adapter Service to generate a schema and bindings file for the stored procedure.
3. Create a map from your message to the stored procedure schema
4. Update your Operation on the logical send port to match the SP name (usp_Test)
5. Deploy your Application.
6. Import the bindings for the physical send port from step 2 to your application
7. Bind the orchestration to this send port (or create a filter on the send port, perhaps based on BTS.Operation).
8. Create ports with the map set on the send port you've imported and a filter se the Orchestration).

奖金:

1. 使用CompositeOperations调用多个过程/tableops

1. Use table types to pass multiple inserts in a single call
2. Use CompositeOperations to call multiple procedures/tableops

注意事项:

• SQL适配器不喜欢空节点(假设您正在使用SQL Server).确保应该以NULL形式进入的节点设置为xsi:nil=true(使用Nil仿函数)，或者目的地中不存在该节点(使用Value Mapping仿函数或方法)来删除管道或助手类中的空节点).
• 其他适配器(例如Oracle或Db2)将面临其自身的特殊挑战. IMO，就可用功能和文档而言，sqlBinding是最好的选择.如果您知道其他平台，则应该能够找出问题所在.
• 除非打算将XML存储在SQL Server中，否则请避免发送XML参数.换句话说，当您有BizTalk为您服务时，请不要使SQL Server切碎XML-BizTalk几乎可以肯定会做得更好(在性能和开发方面).
• 如果要从SQL Server返回数据，请考虑使用强类型(如果过程结果集是作为普通的SELECT或通过OUTPUT参数返回的)或XML轮询(如果过程使用以下方法返回结果集) FOR XML).除非您不希望处理该过程的返回数据，否则请避免使用普通的过程调用.

CAVEATS:

• The SQL Adapter doesn't like empty nodes (assuming you're using SQL Server). Make sure a node that is supposed to go in as a NULL is either set to xsi:nil=true (use the Nil functoid), or that it's not present in the destination (use a Value Mapping functoid, or a method to remove empty nodes in a pipeline or helper class).
• Other adapters (Oracle or Db2 for example) will have their own particular challenges. IMO, the sqlBinding is the nicest to work with in terms of features available and documentation. If you know your other platform, you should be able to figure out the issues.
• Avoid sending XML parameters unless you intend to store the XML in SQL Server. In other words, don't make SQL Server shred XML when you have BizTalk to do that for you - BizTalk will almost certainly do a better job (performance and development wise).
• If you're returning data from SQL Server, consider using Strongly Typed (if your procedure result set is returned as a plain old SELECT or via OUTPUT parameters) or XML Polling (if your procedure returns a resultset using FOR XML). Avoid vanilla procedure calls unless you don't expect to deal with return data from the procedure.

这篇关于从BizTalk执行SQL存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！