發行項
09/24/2010

使用 ADO 執行 Updategram (SQLXML 4.0)

這個 Microsoft Visual Basic 應用程式會使用 ADO 來建立 Microsoft SQL Server 執行個體的連接，然後執行 updategram。updategram 會更新特定員工的姓氏。這個範例會使用 AdventureWorks 範例資料庫。

在此範例應用程式中：

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=AdventureWorks; 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.Contact ContactID='64' Title='IsNULL'/>"
    SQLxml = SQLxml & "    </updg:before>"
    SQLxml = SQLxml & "    <updg:after>"
    SQLxml = SQLxml & "       <Person.Contact ContactID='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 結構描述的 updategrams，您必須在連接物件上，將 "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.Contact" >
   <xsd:complexType>
        <xsd:attribute name="CID"  
                       sql:field="ContactID" 
                       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.Contact" >
       <AttributeType name="CID" />
       <AttributeType name="MName" />

       <attribute type="CID" sql:field="ContactID" />
       <attribute type="MName" sql:field="MiddleName" />
     </ElementType>
   </Schema>

這是由 Visual Basic 程式碼執行擁有相關聯之對應結構描述的 updategram。updategram 會在 Person.Contact 資料表中更新連絡人 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=AdventureWorks; 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 應用程式中，系統不會傳遞參數。在此應用程式中，系統會將 ContactID 和 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 InputContactID As String
  Dim InputMiddleName As String

  InputContactID = "1"
  InputMiddleName = "Q."

  ' Open a connection to the instance of SQL Server.
  conn.Provider = "SQLOLEDB"
  conn.Open "server=(local); database=AdventureWorks; 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='ContactID'/>"
  SQLxml = SQLxml & "<updg:param name='MiddleName' />"
  SQLxml = SQLxml & "</updg:header>"
  SQLxml = SQLxml & "<updg:sync >"
  SQLxml = SQLxml & " <updg:before>"
  SQLxml = SQLxml & "   <Person.Contact ContactID='$ContactID' />"
  SQLxml = SQLxml & "</updg:before>"
  SQLxml = SQLxml & "<updg:after>"
  SQLxml = SQLxml & "<Person.Contact 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("@ContactID", adBSTR, adParamInput, 1, InputContactID)
  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

共用方式為

使用 ADO 執行 Updategram (SQLXML 4.0)

指定 Updategram 的對應結構描述

傳遞參數

其他資源