深入biztalk中sql adapter

Sql adapter通过SQLXML访问sql server，SQLXML XML for SQL Server，又名SQLXML，可帮助开发人员在扩展标记语言（XML）和相关数据之间架设起沟通的桥梁。您还可针对现有关系型数据生成XML视图，并将其作为XML文件加以应用。在biztalk 的sql adapter中使用到SQLXML的下面两个技术来实现对sql server的访问：

l 借助Transact-SQL查询关系型数据并返回XML格式结果集。

l 按照XML格式更新关系型数据（updategram）。

1.1. Select语句查询数据

在一般的查询语句后面加上for xml auto，就可以把关系数据库的行集转换成xml形式的数据返回。

1.2. 执行存储过程

调用存储过程是将存储过程名和相关的参数打包成一个xml形式的数据发送到sql server，sql server执行后，一般在存储过程后也是通过带用for xml auto的select语句返回xml的结果集。

存储过程可以带有输入参数，既可以完成有条件的查询功能，又可以完成数据更新的功能，所以所有的对sql server数据库的访问都可以通过执行存储过程来完成。

2. 使用语句更新数据updategram

看一下如何使用XML格式更新数据库，SQLXML中使用 Updategrams 通过 XML 视图进行更新，可以通过 XML 视图修改（插入、更新或删除）SQL Server 中的数据库，方法是针对数据库的 XML 视图使用 Updategram。

updategram 是一个 XML 文档，带有<sync>、<before> 和<after> 元素，这些元素构成了 updategram 的语法。每个<sync> 块包含一个或多个<before> 和<after> 块。<before> 标识记录实例的现有状态（也称为“前状态”）。<after> 标识要更改的数据的新状态。updategram 是删除、插入还是更新记录实例取决于<before> 和<after> 块的内容。

2.2. 插入操作

当记录实例出现在<after> 块而没有出现在相应的<before> 块中时，updategram 指示插入操作。在这种情况下，updategram 将<after> 块中的记录插入数据库。

2.3. 删除操作

当记录实例出现在<before> 块而没有相应的记录出现在<after> 块中时，updategram 指示删除操作。在这种情况下，updategram 从数据库中删除<before> 块中的记录。

如果在 updategram 中指定的元素或者匹配表中的多行或者不匹配表中的任何行，则 updategram 会返回错误，并取消整个<sync> 块。updategram 中的元素一次只能删除一条记录。

2.4. 更新操作

当更新现有的数据时，必须指定<before> 和<after> 块。updategram 使用<before> 块中指定的元素来标识数据库中的现有记录。<after> 块中相应的元素指示在执行更新操作之后记录的外观应该怎样。

<before>块中的元素必须只与数据库中的一个表行相匹配。如果该元素匹配多个表行或不匹配任何表行，则 updategram 都会返回错误，并且取消整个<sync> 块。

二. Sql adapter访问sql server的两种端口

在使用sql adapter向导时，需要选择是生成接收端口还是发送端口。这两种端口的有什么区别，各自是什么含义？

接收端口，是这个端口从sql server获取数据，然后处理为消息进入到biztalk。端口采用定时轮询的方式获取数据，数据的获取可以是sql语句定义的，也可以是存储过程定义，这些定义都是在sql adapter端口中设置。

发送端口，这里的发送端口实际上是要求/响应（solicit/response）双向发送端口，先发送要求部分的消息，然后接收响应部分的消息。发送端口分两种情况，一种是使用updategram形式的更新数据库的xml消息，更新数据库的数据发送到sql server对数据库进行更新（包括插入、删除、更新），sql server返回一个是否操作成功的标志；另一种是使用存储过程，发送端口把存储过程名和相关参数组成的xml消息发送到sql server，sql server执行相应的存储过程，然后把存储过程返回的行集以xml方式返回到biztalk。

下面把sql adapter可能出现的各种情况分别详细描述，以biztalk的sql adapter示例带的BTS2004_SQL_Adapter_Loans数据库为例，如果没有安装这个数据库， biztalk文档的Samples in the SDK > Adapter Samples - Usage > SQL Adapter Samples >SQL Adapter Loan Application有软和安装这个数据库的说明。

1. 接收端口

请记住，接收端口只是个单向的端口，它根据预设的sql语句或者存储过程到sql server中获取数据（其中没有可变的参数），所以如果需要根据其他消息或它的某个值来决定查询哪些数据的情况不适合使用单向的接收端口，请用发送端口。

1.1. Select语句

1.1.1. 典型场景

需要每隔60秒从Loans表中获取Status字段为“new”的所有记录。

1.1.2. 运行sql adapter向导

在解决方案浏览器中，新建一个空biztalk项目，点击biztalk项目右键，选择“add—Add Generated Items”，弹出下面窗口

如图，选择Add Adapter Metadata，之后就会开始运行sql adapter向导。

在以注册的适配器中选择SQL（如果没有安装其他的有向导的适配器，这里就只有SQL适配器可选），其他的保持不变（除非biztalk的管理数据库是远程的），下一步

1.1.3. 设置数据库连接

这里的数据库连接的作用只是用做向导访问数据库时使用，跟实际运行时的数据库连接无关，运行时的连接在端口中另行设置。在此，点击“Set…”按钮，设置数据库连接，指向本机的c数据库。

下一步。

1.1.4. 设置接收数据消息的架构

这里首选选择端口的类型，选择“接收端口”类型。

设置目标名称空间，就是返回的数据消息的xml的名称空间，自行设定，这里设为“http://SqlTest”。

设置返回数据xml的根节点名称，自行设置，这里设置为“Result”。

一般的，使用sql adapter访问sql server数据库，返回的数据都是通过在sql语句中加上for xml auto来返回xml，for xml auto返回的xml的结构是这样的：

…….

元素名称为表名，表的每个字段就是这个元素的一个属性。

但是这样的结构不符合xml文档的一般规则，所以需要给这个结构加上跟节点和名称空间，形成如下的结构：

<Result xmlns=” http”//SqlTest”

…….

</Result>

1.1.5. 选择语句类型

可以选择select语句或者存储过程，这里选择select语句（insert，delete,update等更新数据库内容的语句是发送端口用到的语句），存储过程的情况是下面章节的内容。下一步。

1.1.6. 输入select语句

根据需求，这里输入合适的sql语句。

这里的sql语句有两个用处，一个是向导会使用这个语句访问sql server，然后生成返回数据的schema，第二个用处，这个sql语句会被保存到向导生成的schema中的注释中，以后在设置sql物理端口时，可以根据这个schema提取这条sql语句。

1.1.7. 向导完成

向导完成后生成一个schema架构和一个orchestration。

生成的架构是这个样子的：

这个架构就是sql接收端口接收到的消息架构。

生成的orchestration中包含了对应的sql的端口（orchestration中的逻辑端口）类型和对应的多部分消息类型。

其实向导生成的orchestration完全可以不用，生成的端口类型可以自己建，多部分消息类型完全可以不用，直接用相应的schema消息类型。而且，每使用一次向导就会生成一个新的orchestration，很是麻烦，所以，可以把向导生成的orchestration删除，保留schema即可。

1.1.8. 设计流程

建一个空orchestration，放置上如下图的形状。

新建一个类型为http://SqlTest#Result的消息叫做ResultMsg，端口1为单向接收端口，用来从sqlserver获取数据，端口2为单向发送端口，将获取的数据保存到一个文件夹中。接收和发送形状的消息都为ResultMsg。

编译部署这个项目。

然后在biztalk管理器中配置这个应用程序，相应的有两个端口需要配置，port2为file类型的发送端口，配置指向一个保存结果的文件夹

1.1.9. 测试

这里主要是要看一下怎么配置port1，port1应该配置为sql类型的接收端口，新建一个接收端口，之后新建接收位置，如下图：

类型选“SQL”，之后点击“configure…”按钮，开始配置sql adapter详细参数。

Receive Configuration这部分设置sql接收端口轮询的时间间隔，默认是30秒。

Connection String：设置数据库连接字符串

Document Root Element Name：设置给返回的xml数据增加的根元素的名称。这里设的值需要跟在向导中设置的Document Root Element Name严格一致。

Document Target Namespace: 设置给返回的xml数据增加的命名空间。这里设的值需要跟在向导中设置的Document Target Namespace严格一致。

SQL Command：查询的具体语句，你可以点击后面的按钮在弹出的对话框中找到向导生成的架构，架构的注释中保存了使用向导是输入的sql语句，选择后会自动把原来的sql语句提取出来。当然，你也可以自己输入sql语句，但是sql语句返回的结果一定要跟向导生成的架构一致。

URI：指示sql adapter访问sql server的地址，形式为：SQL://sql server服务器名/数据库名，本例中就是：SQL://localhost/ Document Target Namespace。不过一个应用程序中sql类型的接收位置指向的URI必须是唯一的，如果有多个sql类型的接收位置指向同一个数据库，那么需要不同的URI，可以把URI设为：SQL://localhost/ Document Target Namespace/01，SQL://localhost/ Document Target Namespace/02等等，后面的01，02不影响访问效果。

设置好后，运行应用程序，过一会在输出文件夹中会不断的生成文件，内容如下：

<?xml version="1.0" encoding="utf-16" ?>

- <<Result xmlns="http://SqlTest">

< <loans LoanID="loan4" CustomerID="cust2" AgentID="blank" RequestAmt="100.45" Interest="0" Status="new" />

</Result>

看一下数据库，loans表中，status字段为new的记录就是上面这两条，测试正确。

1.2. 存储过程

1.2.1. 典型场景

需要每隔60秒从Loans表中获取Status字段为“new”的所有记录。场景跟上面使用select语句的一样，只是这回改为使用存储过程来实现。

先在BTS2004_SQL_Adapter_Loans数据库建一个存储过程，实现select语句的功能，存储过程如下：

ALTER PROCEDURE [dbo].[Get_loans]

SELECT LoanID, CustomerID, AgentID, RequestAmt, Interest, Status

FROM Loans

WHERE (Status = 'new')

RETURN

测试一下存储过程可以返回需要的结果，然后在where语句后面加上：for xml auto，xmldata。其中for xml auto 是为了能把结果行集转换成xml数据返回，xmldata是在返回的结果前面加上这个xml数据的架构，以便sql adapter根据这个返回的数据生成schema。

这里的实现过程绝大部分跟上一节select的过程相同，所以下面只把两个过程不同的部分做描述。

1.2.2. 选择语句类型

前面的过程都一致，一直到选择语句类型时，选择存储过程：

然后下一步，选择存储过程。

选择刚才新建的存储过程Get_loans，这个存储过程没有参数，如果有参数，也会在此列出来，你可以给参数设定值，注意这里设定的值只是向导用来访问数据库使用的。

然后点击“Generate”按钮，生成执行存储过程的语句，这里是：exec [Get_loans]。

向导完成，同样生成一个schema架构和一个orchestration。同样orchestration不用，删掉。将存储过程中的,xmldata删除。再看生成的schema架构，跟前面用select语句生成的是一样的。

1.2.3. 测试

跟上面select的例子一样，新建一个orchestration，还是照样设计一样的简单的流程，编译部署。

配置应用程序。port2为file类型的发送端口，配置指向一个保存结果的文件夹。看端口1的配置，除了SQL Command跟前面select时的不一样为：exec [Get_loans]，表示是执行一个存储过程以外，别的设置一样。当然，存储过程是可以有参数的，只要在后面加上参数的值。

设置完成后，运行应用程序，会得到跟前面select一样的结果。

2. 发送端口

上面讨论完了接收端口，从中可以发现，接收端口只能根据预设的select语句或者存储过程从数据库中获取数据，不能根据变化的参数获取不同的数据。还有，接收端口不能对数据库进行更新操作。

这两个功能可以由sql adapter的发送端口来实现，严格的说是要求/响应（solicit/response）双向发送端口实现。

根据变化的参数获取不同的数据只能有存储过程实现，更新数据有两个办法，使用updategram语句和存储过程都可以更新数据。

2.1. Updategram语句

2.1.1. 典型场景

对数据表进行增、删、改。

就是用sql adapter向导时，选择了发送端口后，在选择语句时，选择了updategram时的情况。

2.1.2. 设置架构信息

首先选择发送端口类型

目标命名空间跟前面设置是一样的 http://SqlTest

发送端口是双向的发送端口，所以需要生成两个架构（实际是在一个schema中，只是这个schema有两个根元素），一个是用于发出请求的，一个是接收返回数据的。这里分别设置RequestRoot和ResponseRoot。

下一步，选择语句类型，选择“updategram”，然后再下一步。

这里需要选择如何更新数据，是插入新的行、删除、还是更新，这里先采用update更新数据。选择Agents表，表示要更新这个表的内容，右边选择字段，表示要根据哪些字段进行更新，就是形成sql语句中where字句后面作为条件的字段。这里选择AgentID这字段作为更新的条件字段。

向导完成，同样是生成一个schema架构和一个orchestration，同样也把orchestration删掉不用，重新新建一个orchestration。

看一下生成的schema架构的结构：

before节点下面的内容表示更新之前的内容，就是where后的条件，after节点下的内容表示更新后的数据。生成的这个架构可以被翻译成下面的sql语句：

update Agents set FirstName = @FirstName,LastName = @ LastName,RegionID = @RegionID where AgentID = @AgentID

Insert 和 delete的情况也是采用这样的架构，只是当为插入的时候，生成的架构中before部分没有了，直接把after中的内容插入到表。当位删除的时候，架构中after部分没有了，直接把数据表中符合before中内容的数据表删除。

2.1.3. 设计流程

在新建的orchestration中建两个消息，一个请求消息RequestMsg，类型为向导生成的架构的请求部分SQL_Adapter_SP.UpdateAgentsService.RequestRoot，一个返回消息ResponseMsg，类型位向导生成的架构的响应部分 SQL_Adapter_SP.UpdateAgentsService.ResponseRoot。

在orchestration中放置如下图的形状：

Port1是单向接收端口，用来从指定文件夹读取更新数据xml文件，port2是单向发送端口，用来把更新数据的结果发送到指定的文件夹。Port3是双向发送端口，是用来向sql发送更新数据请求并接收更新是否成功结果。

编译部署项目。

2.1.4. 配置应用程序

在biztalk管理器中配置这个应用程序。

Port1和port2都是file型适配器，分别设置指向输入和输入的文件夹。

现在来配置port3对应的sql类型的发送端口。选择类型位SQL类型。然后点击旁边的“configure…”按钮，配置sql适配器的属性。

连接字符串指向要访问的数据库

Document Target Namespace：是返回数据的目标命名空间，必须跟向导中设置的Document Target Namespace严格一致。

Response Document Root Element：是返回的数据的根节点名称，，必须跟向导中设置的Response Document Root Element严格一致。

2.1.5. 测试

根据请求部分的架构，设计一个测试用的xml，如下：

<ns0:RequestRoot xmlns:ns0="http://SqlTest">

<ns0:sync>

<ns0:before>

<ns0:Agents AgentID="Agnt1" />

</ns0:before>

<ns0:after>

<ns0:Agents AgentID="Agnt1" FirstName="FirstName_1" LastName="LastName_2" RegionID="RegionID_3" />

</ns0:after>

</ns0:sync>

</ns0:RequestRoot>

表示要更新的是AgentID为"Agnt1"的这一条记录，要更新成的内容在after节点中。

把这个测试xml文件放到输入文件夹中，等一会，在输出文件夹中生成一个文件，内容如下：

<?xml version="1.0" encoding="utf-16"?>

<ns:ResponseRoot xmlns:ns="http://SqlTest">

<ns:Success />

</ns:ResponseRoot>

表示更新数据成功。

再看数据库中的Agents表的内容：

Agent1这条记录的内容已经被更新。

2.2. 存储过程

在发送端口中使用存储过程是功能最强，使用范围最广的一种用法，它既可以根据参数灵活的获取数据，也可以用来更新数据。

2.2.1. 典型场景

根据参数返回不同的结果集，还是以Agents表为例，以RegionID字段为参数，根据这个参数的值不同，返回不同的记录。

先建立一个存储过程，实现上述的功能

ALTER PROCEDURE dbo.GetAgentForRegionid

(

@regionid nvarchar(10)

)

select * from Agents where regionid = @regionid