15: Modifying Databases Using SQLXML in Visual Studio .NET

Generating DataMatrix in Visual Studio .NET 15: Modifying Databases Using SQLXML

15: Modifying Databases Using SQLXML
Decode Data Matrix In .NET
Using Barcode Control SDK for .NET framework Control to generate, create, read, scan barcode image in VS .NET applications.
Making Data Matrix ECC200 In VS .NET
Using Barcode creation for VS .NET Control to generate, create DataMatrix image in .NET framework applications.
updg:at-identity="x" RequestedById = "1" TargetDate = "1/1/2004" DestinationLocationId = "10" RequestedById = "1" OrderTypeId = "3" OrderStatusId = "1" OrderDate = "11/3/2004" /> <OrderItem OrderID="x" InventoryId="90" EquipmentId="10" Quantity="1" Note="B123112" /> </updg:after> </updg:sync> </ROOT>
DataMatrix Reader In .NET Framework
Using Barcode recognizer for .NET Control to read, scan read, scan image in VS .NET applications.
Painting Barcode In Visual Studio .NET
Using Barcode creation for VS .NET Control to generate, create barcode image in VS .NET applications.
<_x005B_Order_x005D_
Recognize Bar Code In .NET Framework
Using Barcode scanner for VS .NET Control to read, scan read, scan image in VS .NET applications.
Painting ECC200 In Visual C#
Using Barcode creation for .NET framework Control to generate, create Data Matrix image in .NET applications.
If you need to return the identifier to the calling application to do some additional processing, you should use the updg:returnid attribute. If you add it to the updg:after element, the calling application will receive a response in the form of an XML document with the identity value:
Data Matrix ECC200 Drawer In .NET
Using Barcode printer for ASP.NET Control to generate, create ECC200 image in ASP.NET applications.
DataMatrix Generation In Visual Basic .NET
Using Barcode generator for Visual Studio .NET Control to generate, create Data Matrix ECC200 image in VS .NET applications.
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> <returnid> <x>11091</x> </returnid> </ROOT>
Generate UPC Code In Visual Studio .NET
Using Barcode creator for .NET framework Control to generate, create UPC-A Supplement 2 image in .NET framework applications.
Making Data Matrix 2d Barcode In VS .NET
Using Barcode drawer for .NET framework Control to generate, create ECC200 image in VS .NET applications.
It is possible to process more than one set of records in the preceding example, more than one order is processed. Each identity value would need to be named differently. The updg:returnid attribute needs to contain a list of all identity value names (separated by spaces) that will be returned to the caller:
Paint Code 128 Code Set A In .NET
Using Barcode creator for .NET Control to generate, create USS Code 128 image in .NET framework applications.
Painting International Standard Serial Number In .NET
Using Barcode maker for .NET Control to generate, create ISSN - 10 image in .NET framework applications.
<updg:after updg:returnid="x y" > <_x005B_Order_x005D_ updg:at-identity="x" ... <Order_x0020_Details OrderID="x" ... <_x005B_Order_x005D_ updg:at-identity="y" ... <Order_x0020_Details OrderID="y" ...
Paint Bar Code In None
Using Barcode maker for Font Control to generate, create barcode image in Font applications.
UPC-A Supplement 2 Recognizer In Java
Using Barcode decoder for Java Control to read, scan read, scan image in Java applications.
SQL Server 2000 Stored Procedure & XML Programming
Linear Drawer In Visual Basic .NET
Using Barcode generator for Visual Studio .NET Control to generate, create 1D Barcode image in .NET applications.
Bar Code Generation In .NET Framework
Using Barcode drawer for Reporting Service Control to generate, create barcode image in Reporting Service applications.
The result will again be an XML document:
Encode GS1 128 In None
Using Barcode generation for Word Control to generate, create UCC - 12 image in Office Word applications.
Making Code 128A In .NET Framework
Using Barcode maker for ASP.NET Control to generate, create Code 128 Code Set C image in ASP.NET applications.
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> <returnid> <x>11091</x> <y>11092</y> </returnid> </ROOT>
Drawing Code 39 In Objective-C
Using Barcode encoder for iPhone Control to generate, create Code 3/9 image in iPhone applications.
Bar Code Decoder In .NET
Using Barcode decoder for VS .NET Control to read, scan read, scan image in .NET framework applications.
Uniqueidentifiers
UpdateGrams can also process uniqueidentifier values. They first need to be initialized and named using the updg:guid attribute of the updg:after element. Then you can use this attribute to set one or more fields and even return it to the caller. In the following example, the record will be inserted and the GUID returned to the caller:
<SessionGuid updg:guid="x" >
The caller will receive the GUID inside an XML document:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> <returnid> <x>7111BD1A-7F0B-4CEE-B411-260DADFEFA2A</x> </returnid> </ROOT>
You will be able to save some time if you generate the GUID (in the middleware or client) before you send it to the database.
Special Characters
You have probably noticed in the preceding examples that I encoded some values using _xHex-value_. The Hex-value stands for a four-digit hexadecimal USC-2 code. For example, the characters [ and ] are encoded as _x005B_ and _x005D_. You must use such codes for characters that are legal in Transact-SQL, but illegal in UpdateGrams.
UpdateGrams Behind the Scene
You can run SQL Profiler to evaluate the way that UpdateGrams are converted to Transact-SQL code. Here is an example that I have captured:
15: Modifying Databases Using SQLXML
SET XACT_ABORT ON BEGIN TRAN DECLARE @eip INT, @r__ int, @e__ int SET @eip = 0 DECLARE @V1 nvarchar(40);INSERT [Order] (RequestedById, TargetDate, DestinationLocationId, OrderTypeId, OrderStatusId, OrderDate) VALUES (N'1', N'1/1/2004', N'10', N'3', N'1', N'11/3/2004'); SELECT @e__ = @@ERROR, @r__ = @@ROWCOUNT IF (@e__ != 0 OR @r__ != 1) SET @eip = 1 SELECT @V1 = SCOPE_IDENTITY( );INSERT OrderItem (OrderID, InventoryId, EquipmentId, Note) VALUES (@V1, N'90', N'4', N'B123112'); SELECT @e__ = @@ERROR, @r__ = @@ROWCOUNT IF (@e__ != 0 OR @r__ != 1) SET @eip = 1 select '<returnid>'+ '<x>' + cast(@V1 as varchar(4000))+ '</x>' + '</returnid>' IF (@eip != 0) ROLLBACK ELSE COMMIT SET XACT_ABORT OFF
Optimistic Locking with UpdateGrams
There are two basic ways to solve concurrency problems using optimistic locking with UpdateGrams: By comparing all fields By comparing only timestamp fields In the first scenario, the updg:before element should contain the original values of all fields. The updg:after element contains only the changed values. SQL Server performs the update only if the record contains all of the original values specified in the updg:before element:
<root xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> <updg:sync> <updg:before> <Contact ContactId="1013" FirstName="Tom" LastName="Jones" OrgUnitId="2" Phone = "(416) 123-1217"/> </updg:before> <updg:after> <Contact Phone = "(416) 123-1234"/> </updg:after> </updg:sync> </root>
SQL Server 2000 Stored Procedure & XML Programming
The second scenario is based on the use of the timestamp field in the updg: before element (along with the key value). Again, the updg:after element contains only the changed values. SQL Server performs the update only if the timestamp value matches the one found in the record:
<root xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> <updg:sync mapping-schema="UpdategramContact_TS.xsd" > <updg:before> <Contact ContactId = "1013" ts = "0x0000000000001907"/> </updg:before> <updg:after> <Contact OrgUnitId = "1"/> </updg:after> </updg:sync></root>
However, timestamp values require special care because they are essentially binary fields, and UpdateGram needs to treat this attribute value as a binary (not a string) value. You must assign the sql:timestamp data type to them using a mapping schema. The UpdateGram must reference the schema in the mappingschema attribute of the updg:synch element:
< xml version="1.0" encoding="UTF-8" standalone="no" > <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:dt="urn:schemas-microsoft-com:datatypes" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> <xsd:element name="Contact" sql:relation="Contact" type="Contact_type"/> <xsd:complexType name="Contact_type"> <xsd:attribute name="ContactId" type="xsd:int"/> <xsd:attribute name="FirstName" type="xsd:string"/> <xsd:attribute name="LastName" type="xsd:string"/> <xsd:attribute name="Phone" type="xsd:string"/> <xsd:attribute name="Fax" type="xsd:string"/> <xsd:attribute name="Email" type="xsd:string"/> <xsd:attribute name="OrgUnitId" type="xsd:short"/> <xsd:attribute name="UserName" type="xsd:string"/> <xsd:attribute name="ts" sql:datatype="timestamp" type="xsd:hexBinary"/> </xsd:complexType> </xsd:schema>
In cases where the timestamp in UpdateGram matches the timestamp in the record, SQLXML returns the usual confirmation:
Copyright © OnBarcode.com . All rights reserved.