使用 ADO 执行 Updategram (SQLXML 4.0)
该 Microsoft Visual Basic 应用程序使用 ADO 建立与 Microsoft SQL Server 实例的连接,并执行 updategram。updategram 更新特定雇员的姓氏。此示例使用 AdventureWorks2008R2 示例数据库。
在此示例应用程序中:
conn 对象 (ADODB.Connection) 建立与特定服务器计算机上正在运行的 SQL Server 实例的连接。
在已建立的连接上执行 cmd 对象 (ADODB.Command)。
将命令方言设置为 DBGUID_MSSQLXML。
将 updategram 复制到命令流 (strmIn)。
将该命令的输出流设置为 StrmOut 对象 (ADODB.Stream) 以便接收任何返回的数据。
最后,执行该命令 (updategram)。
以下是示例代码:
Private Sub Form_Load()
Dim cmd As New ADODB.Command
Dim conn As New ADODB.Connection
Dim strmIn As New ADODB.Stream
Dim strmOut As New ADODB.Stream
Dim SQLxml As String
' Open a connection to the instance of SQL Server.
conn.Provider = "SQLOLEDB"
conn.Open "server=(local); database=AdventureWorks2008R2; Integrated Security=SSPI; "
conn.Properties("SQLXML Version") = "SQLXML.4.0"
Set cmd.ActiveConnection = conn
' Build the command string in the form of an XML template.
SQLxml = "<ROOT xmlns:updg='urn:schemas-microsoft-com:xml-updategram' >"
SQLxml = SQLxml & " <updg:sync updg:nullvalue='IsNULL'>"
SQLxml = SQLxml & " <updg:before>"
SQLxml = SQLxml & " <Person.Person BusinessEntityID='64' Title='IsNULL'/>"
SQLxml = SQLxml & " </updg:before>"
SQLxml = SQLxml & " <updg:after>"
SQLxml = SQLxml & " <Person.Person BusinessEntityID='64' Title='Mr.'/>"
SQLxml = SQLxml & " </updg:after>"
SQLxml = SQLxml & " </updg:sync>"
SQLxml = SQLxml & "</ROOT>"
' Set the command dialect to DBGUID_MSSQLXML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
' Open the command stream and write our template to it.
strmIn.Open
strmIn.WriteText SQLxml
strmIn.Position = 0
Set cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open
strmOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = strmOut
cmd.Properties("Output Encoding").Value = "UTF-8"
cmd.Execute , , adExecuteStream
strmOut.Position = 0
Debug.Print strmOut.ReadText
strmOut.Close
strmIn.Close
End Sub
注意 |
---|
如果从 ADO 使用 SQLXML 执行指定 XSD 架构的 updategram,必须在连接对象上将“SQLXML Version”属性设置为“SQLXML.4.0”,如下面的示例代码行所示: conn.Properties("SQLXML Version") = "SQLXML.4.0" |
为 Updategram 指定映射架构
本示例中演示如何在 updategram 中指定和使用映射架构。
将以下 XSD 架构 (EmpSchema.xml) 保存到磁盘,并确保将代码中指定的路径更新为磁盘上映射架构的位置。代码假定该架构保存在 C: 驱动器的 Schemas 文件夹中。
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Contact" sql:relation="Person.Person" >
<xsd:complexType>
<xsd:attribute name="CID"
sql:field="BusinessEntityID"
type="xsd:string" />
<xsd:attribute name="MName"
sql:field="MiddleName"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
可以指定 XSD 和 XDR 架构,以下是等效的 XDR 架构:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="Contact" sql:relation="Person.Person" >
<AttributeType name="CID" />
<AttributeType name="MName" />
<attribute type="CID" sql:field="BusinessEntityID" />
<attribute type="MName" sql:field="MiddleName" />
</ElementType>
</Schema>
以下是执行具有关联映射架构的 updategram 的 Visual Basic 代码。该 updategram 更新 Person.Person 表中第 1 个联系人的中间名。
Private Sub Form_Load()
Dim cmd As New ADODB.Command
Dim conn As New ADODB.Connection
Dim strmIn As New ADODB.Stream
Dim strmOut As New ADODB.Stream
' Open a connection to the SQL Server.
conn.Provider = "SQLOLEDB"
conn.Open "server=(local); database=AdventureWorks2008R2; Integrated Security='SSPI' ;"
conn.Properties("SQLXML Version") = "SQLXML.4.0"
Set cmd.ActiveConnection = conn
' Open the command stream and write the template to it.
strmIn.Open
strmIn.WriteText "<ROOT xmlns:updg='urn:schemas-microsoft-com:xml-updategram' >"
strmIn.WriteText " <updg:sync mapping-schema='C:\Schemas\EmpSchema.xml' >"
strmIn.WriteText " <updg:before>"
strmIn.WriteText " <Contact CID='1' />"
strmIn.WriteText " </updg:before>"
strmIn.WriteText " <updg:after>"
strmIn.WriteText " <Contact MName='M.'/>"
strmIn.WriteText " </updg:after>"
strmIn.WriteText " </updg:sync>"
strmIn.WriteText "</ROOT>"
' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
strmIn.Position = 0
Set cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open
strmOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute , , adExecuteStream
strmOut.Position = 0
Debug.Print strmOut.ReadText
strmOut.Close
strmIn.Close
conn.Close
End Sub
传递参数
在以前提供的 Visual Basic 应用程序中,不能传递参数。在此应用程序中,BusinessEntityID 和 MiddleName 值作为参数化输入传递到 updategram。
Private Sub Form_Load()
Dim cmd As New ADODB.Command
Dim conn As New ADODB.Connection
Dim strmIn As New ADODB.Stream
Dim strmOut As New ADODB.Stream
Dim InputBusinessEntityID As String
Dim InputMiddleName As String
InputBusinessEntityID = "1"
InputMiddleName = "Q."
' Open a connection to the instance of SQL Server.
conn.Provider = "SQLOLEDB"
conn.Open "server=(local); database=AdventureWorks2008R2; Integrated Security=SSPI; "
conn.Properties("SQLXML Version") = "SQLXML.4.0"
Set cmd.ActiveConnection = conn
' Build the command string in the form of an XML template.
SQLxml = "<ROOT xmlns:updg='urn:schemas-microsoft-com:xml-updategram' >"
SQLxml = SQLxml & "<updg:header>"
SQLxml = SQLxml & "<updg:param name='BusinessEntityID'/>"
SQLxml = SQLxml & "<updg:param name='MiddleName' />"
SQLxml = SQLxml & "</updg:header>"
SQLxml = SQLxml & "<updg:sync >"
SQLxml = SQLxml & " <updg:before>"
SQLxml = SQLxml & " <Person.Person BusinessEntityID='$BusinessEntityID' />"
SQLxml = SQLxml & "</updg:before>"
SQLxml = SQLxml & "<updg:after>"
SQLxml = SQLxml & "<Person.Person MiddleName='$MiddleName' />"
SQLxml = SQLxml & "</updg:after>"
SQLxml = SQLxml & "</updg:sync>"
SQLxml = SQLxml & "</ROOT>"
' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
' Open the command stream and write the template to it.
strmIn.Open
strmIn.WriteText SQLxml
strmIn.Position = 0
Set cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open
strmOut.LineSeparator = adCRLF
cmd.NamedParameters = True
cmd.Parameters.Append cmd.CreateParameter("@BusinessEntityID", adBSTR, adParamInput, 1, InputBusinessEntityID)
cmd.Parameters.Append cmd.CreateParameter("@MiddleName", adBSTR, adParamInput, 7, InputMiddleName)
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute , , adExecuteStream
strmOut.Position = 0
Debug.Print strmOut.ReadText
End Sub