Blog: Base Crystal Knowledge Report clinicaldatabase.info

Converting Base64 values to varbinary and vice versa is now easier using the XQuery functionality available from SQL Server 2005 onwards. The code samples below show how to perform the conversion:

-- Convert Base64 value in a variable to varbinary:

declare @str varchar(20);

set @str = '3qAAAA==';

select cast(N'' as xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(20)');

-- Convert binary value in a variable to Base64:

declare @bin varbinary(20);

set @bin = 0xDEA00000;

select cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'varchar(20)');

-- Convert varbinary value in a column to Base64:

select top (10) cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:column("qs.sql_handle")))', 'varchar(512)') as sql_handle_base64

into #t

from sys.dm_exec_query_stats as qs;

-- Convert Base64 value in a column to varbinary:

select cast(N'' as xml).value('xs:base64Binary(sql:column("t.sql_handle_base64"))', 'varbinary(20)') as sql_handle

from #t as t;

drop table #t;

 

For more details on XQuery see link below:

 

http://msdn.microsoft.com/en-us/library/ms189075(SQL.100).aspx

I would like to share my thoughts on Message Transmission Optimization Mechanism that comes with WSE 3.0.

WSE 3.0 provides the ability to send large amounts of binary data efficiently and securely by the W3C SOAP Message Transmission Optimization Mechanism (MTOM) specification. MTOM supports WS-Security, using raw bytes in soap message for attachments reduces the size of messages on the wire helping in low bandwidth scenarios and in integrates smoothly with existing Web services.

MTOM leverages XOP(XML binary Optimizition packaging),which is an alternate serialisation of XML that look like a MIME multipart/related package, with an XML document as the root part. The root part is similar to the XML serialisation of the document, except that base64-encoded data is replaced by a reference to one of the MIME parts, which isn’t base64 encoded.

How MTOM works ?

The client application sends an soap request with attachments encoded in base64binary format.On the wire transmission,base64binary format gets converted into MIME data due to the bloating nature of base64binary format.This conversion is obtained by XOP serialisation.


Base64Binary encoded attachment - photo and sig element

Before Conversion

<soap:Envelope xmlns:soap='http://www.w3.org/2003/05/soap-envelope'
xmlns:xmlmime='http://www.w3.org/2004/11/xmlmime'>
<soap:Body>
<m:data xmlns:m='http://example.org/stuff'>
<m:photo
xmlmime:contentType='image/png'>xLjYNJeLjz9MNCjE+</m:photo>
<m:sig
xmlmime:contentType='application/pkcs7-signature'>DQpzdGFyNCjEx0YNCg</m:sig>
</m:data>
</soap:Body>
</soap:Envelope>


In the conversion process,Base64Binary data replaces element that references the original binary octects(raw data) of the document being transmitted.


After conversion

MIME-Version: 1.0
Content-Type: Multipart/Related;boundary=MIME_boundary;
type="application/xop+xml";
start="<mymessage.xml@example.org>";
startinfo="application/soap+xml; action=\"ProcessData\""
Content-Description: A SOAP message with my pic and sig in it

--MIME_boundary
Content-Type: application/xop+xml;
charset=UTF-8;
type="application/soap+xml; action=\"ProcessData\""
Content-Transfer-Encoding: 8bit
Content-ID: <mymessage.xml@example.org>

<soap:Envelope
xmlns:soap='http://www.w3.org/2003/05/soap-envelope'
xmlns:xmlmime='http://www.w3.org/2004/11/xmlmime'>
<soap:Body>
<m:data xmlns:m='http://example.org/stuff'>
<m:photo
xmlmime:contentType='image/png'><b><xop:Include
xmlns:xop='http://www.w3.org/2004/08/xop/include'
href='cid:http://example.org/me.png'/><b></m:photo>
<m:sig
xmlmime:contentType='application/pkcs7-signature'><b><xop:Include
xmlns:xop='http://www.w3.org/2004/08/xop/include'
href='cid:http://example.org/my.hsh'/></b></m:sig>
</m:data>
</soap:Body>
</soap:Envelope>

--MIME_boundary
Content-Type: image/png
Content-Transfer-Encoding: binary
Content-ID: <http://example.org/me.png>

// binary octets for png

--MIME_boundary
Content-Type: application/pkcs7-signature
Content-Transfer-Encoding: binary
Content-ID: <http://example.org/my.hsh>

// binary octets for signature

--MIME_boundary--

On the other side,MTOM enabled Web service converts MIME data back to base64binary format ( for its interoperability and composability) using XOP deserilization.

Similarly , data from Web services to client application happens in reverse process.

Advantages of MTOM

1. Efficient Transmission of large amount of data:

Base64Binary encoded data is larger than raw byte transmission using MIME. MTOM therefore reduces this drawback by converting Base64Binary encoding to raw bytes for transmission.

2. Excellent Composability :

In dime attachments are represented outside xml root in soap message.This needs additional effort to secure the attachments.Unlike Dime,MTOM has ability to
send attachments more securely by using Base64 encoded data and MIME-based attachments.

Base64Binary encoded data is represented within an element of a SOAP message. Security standards such as WS-Signatures and WS-Encryption can directly be
applied to the SOAP Message. Once such operations are performed, the Base64Binary data can be converted to raw bytes for efficient transmission. Securing
document transmission via SOAP, therefore, does not require additional standards for securing MIME-based attachments.

3. With MTOM, binary attachments can be logically signed as if they were part of the SOAP XML document.


Sample Code

The sample demonstrates the MTOM enabled web service that returns gif image attachment to the client application.It works on .Net framework 2.0 and WSE 3.0.

Prerequisite


Install WSE 3.0 where client and server resides.


Web Service Code


using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.IO;
using Microsoft.Web.Services3; // added

namespace MTOM
{
public class MTOMService : System.Web.Services.WebService
{

[WebMethod]
public byte[] GetGif()
{
byte[] result;
String ImagePath = AppDomain.CurrentDomain.BaseDirectory + @"\1.gif";
result = File.ReadAllBytes(ImagePath);
return result;
}
}
}

Update the web.config to enable MTOM by using WSE 3.0 configuration tool.
1. Open the web.config using configuration tool.
2. Use the general tab to check the 'Enable this project for WSE' and 'Enable WSE soap protocol factory'.
3. Use the messaging tab to select to select mode in 'always' or 'optional' mode.


Proxy Code


Wsdl http://localhost/MTOM/MTOMService.asmx /out:MTOMclient.cs
Run this command at the cmd prompt to generate proxy source code.

Update proxy class to inherit from Microsoft.Web.Services3.WebServicesClientProtocol in MTOMClient.cs

Add this proxy code to the client project or create a proxy dll using CSC exe


Client code

Update the App.config to enable MTOM by using WSE 3.0 configuration tool.
1. Open the App.config using configuration tool.
2. Use the messaging tab to switch on the client mode.


Add this code to button click of windows form application to display gif image in picturebox

MTOMService obj = new MTOMService();
byte[] result = obj.GetGif();
pictureBox1.Image = System.Drawing.Image.FromStream(new MemoryStream(result));