Executing an Updategram by Using ADO (SQLXML 4.0)
This Microsoft Visual Basic application uses ADO to establish a connection to an instance of Microsoft SQL Server and execute an updategram. The updategram updates the last name of a specific employee. This example uses the AdventureWorks2008R2 sample database.
In this sample application:
The conn object (ADODB.Connection) establishes a connection to a running instance of SQL Server on a specific server computer.
The cmd object (ADODB.Command) executes on the established connection.
The command dialect is set to DBGUID_MSSQLXML.
The updategram is copied to the command stream (strmIn).
The command's output stream is set to the StrmOut object (ADODB.Stream) to receive any returned data.
Finally the command (updategram) is executed.
Here is the sample code:
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
Note
If you're using SQLXML from ADO to execute updategrams that specify an XSD schema, you must set the "SQLXML Version" property to "SQLXML.4.0" on the connection object, as shown in the following example line of code:
conn.Properties("SQLXML Version") = "SQLXML.4.0"
Specifying a Mapping Schema for the Updategram
This example illustrates how to specify and use a mapping schema in an updategram.
Save the following XSD schema (EmpSchema.xml) to your disk, and be sure to update the path that is specified in the code to the location of the mapping schema on your disk. The code assumes that the schema is saved on the C: drive in the Schemas folder.
<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>
Because both XSD and XDR schemas can be specified, this is the equivalent XDR schema:
<?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>
This is the Visual Basic code to execute an updategram that has an associated mapping schema. The updategram updates the middle name for contact 1 in the Person.Person table.
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
Passing Parameters
In the Visual Basic applications provided earlier, parameters are not passed. In this application, the BusinessEntityID and MiddleName values are passed as parameterized input to the 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