CE's Blog

.NET RSACryptoServiceProvider PEM + DER Support

October 4th, 2012 Christian Etter 7 comments

In .NET, RSACryptoServiceProvider greatly simplifies common tasks associated with public/private keys, such as signing of data and verifying a signature.
Unfortunately, there is only a single format (proprietary XML) available for importing and exporting public/private key data.
The two widely spread formats for key exchange, PEM and DER are not supported, which limits the usability of the class when working with different kinds of public key APIs.

There are a few workarounds though:

Write your own .NET code to support the desired formats, which might not be that simple, as the following blog post suggests.
Use a third party component, such as the PEMReader class of the Bouncy Castle Library (Java/C# port).
Leverage Microsoft Crypto API (CAPI), which provides support for opening and converting a variety of different public/private key file formats. Here I would like to demonstrate how this can be achieved by means of simple extension methods to the RSACryptoServiceProvider class.

Here is a sample RSA 1024 bit private key in PEM format:

This is the corresponding public key in textual PEM representation:

We cannot load these keys into the RSACryptoServiceProvider directly. However the class supports an import method which is compatible with CryptoAPI: ImportCspBlob().
With CAPI in return, we can do all the heavy lifting including format conversion which the .NET Framework does not support.
For public keys, the conversion process for PEM (string) key data requires the following steps:

Converting a public key in PEM string format into DER representation.

CryptStringToBinaryA( sPEM, (UInt32)sPEM.Length, CRYPT_STRING_FLAGS.CRYPT_STRING_BASE64HEADER, IntPtr.Zero, ref dwBinarySize, out dwSkip, out dwFlags ) )

Retrieve the key through a CERT_PUBLIC_KEY_INFO struct.

CryptDecodeObjectEx( CRYPT_ENCODING_FLAGS.X509_ASN_ENCODING | CRYPT_ENCODING_FLAGS.PKCS_7_ASN_ENCODING, new IntPtr( (int)CRYPT_OUTPUT_TYPES.X509_PUBLIC_KEY_INFO ), DERData, (UInt32)DERData.Length, CRYPT_DECODE_FLAGS.CRYPT_DECODE_ALLOC_FLAG, IntPtr.Zero, ref pCertPublicKeyInfo, out dwCertPublicKeyInfoSize )

Convert the returned RSA key into a Diffie-Hellman Version 3 Public Key BLOBs or DSS Version 3 Public Key BLOBs struct:

CryptDecodeObjectEx( CRYPT_ENCODING_FLAGS.X509_ASN_ENCODING | CRYPT_ENCODING_FLAGS.PKCS_7_ASN_ENCODING, new IntPtr( (int)CRYPT_OUTPUT_TYPES.RSA_CSP_PUBLICKEYBLOB ), RSAData, (UInt32)RSAData.Length, CRYPT_DECODE_FLAGS.CRYPT_DECODE_ALLOC_FLAG, IntPtr.Zero, ref pCertPublicKeyBlob, out dwCertPublicKeyBlobSize )

Call RSACryptoServiceProvider.ImportCspBlob() for the resulting binary data.

For private keys, the following steps are required:

Converting a private key in PEM string format into DER representation.

CryptStringToBinaryA( sPEM, (UInt32)sPEM.Length, CRYPT_STRING_FLAGS.CRYPT_STRING_BASE64HEADER, IntPtr.Zero, ref dwBinarySize, out dwSkip, out dwFlags ) )

For private keys, retrieve a pointer to an RSA private key BLOB directly:

CryptDecodeObjectEx( CRYPT_ENCODING_FLAGS.X509_ASN_ENCODING | CRYPT_ENCODING_FLAGS.PKCS_7_ASN_ENCODING, new IntPtr( (int)CRYPT_OUTPUT_TYPES.PKCS_RSA_PRIVATE_KEY ), DERData, (UInt32)DERData.Length, CRYPT_DECODE_FLAGS.CRYPT_DECODE_ALLOC_FLAG, IntPtr.Zero, ref pRSAPrivateKeyBlob, out pRSAPrivateKeyBlobSize )

Call RSACryptoServiceProvider.ImportCspBlob() for the resulting binary data.

If you wrap the above logic into extension methods for RSACryptoServiceProvider, importing keys in PEM format and signing data can be done with a few lines of code:

// -----BEGIN RSA PRIVATE KEY-----...-----END RSA PRIVATE KEY-----
string sPrivateKeyPEM = File.ReadAllText( "PrivateKey.pem" );
 
using ( RSACryptoServiceProvider rsa = new RSACryptoServiceProvider() )
{
    rsa.PersistKeyInCsp = false;
    rsa.LoadPrivateKeyPEM( sPrivateKeyPEM );
    using ( SHA1CryptoServiceProvider sha1 = new SHA1CryptoServiceProvider() )
        signature = rsa.SignData( dataToSign, sha1 );
}

Verifying the same signature also becomes easy:

// -----BEGIN PUBLIC KEY-----...-----END PUBLIC KEY-----
string sPublicKeyPEM = File.ReadAllText( "PublicKey.pem" );
 
using ( RSACryptoServiceProvider rsa = new RSACryptoServiceProvider() )
{
    rsa.PersistKeyInCsp = false;
    rsa.LoadPublicKeyPEM( sPublicKeyPEM );
    using ( SHA1CryptoServiceProvider sha1 = new SHA1CryptoServiceProvider() )
        bVerifyResultOriginal = rsa.VerifyData( dataToSign, sha1, signature );
}

Here is a cut-and-paste solution based on an extension class to RSACryptoServiceProvider:

/*********************************************************************************
 * Copyright (c) 2013, Christian Etter info at christian-etter dot de
 * 
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 * 
 * 1. Redistributions of source code must retain the above copyright notice, this
 *    list of conditions and the following disclaimer. 
 * 2. Redistributions in binary form must reproduce the above copyright notice,
 *    this list of conditions and the following disclaimer in the documentation
 *    and/or other materials provided with the distribution. 
 * 
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
 * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR
 * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *********************************************************************************/
 
using System;
using System.ComponentModel;
using System.Runtime.InteropServices;
using System.Security.Cryptography;
 
/// <summary>Extension method for initializing a RSACryptoServiceProvider from PEM data string.</summary>
public static class RSACryptoServiceProviderExtension
{
    #region Methods
 
    /// <summary>Extension method which initializes an RSACryptoServiceProvider from a DER public key blob.</summary>
    public static void LoadPublicKeyDER( this RSACryptoServiceProvider provider, byte[] DERData )
    {
        byte[] RSAData = RSACryptoServiceProviderExtension.GetRSAFromDER( DERData );
        byte[] publicKeyBlob = RSACryptoServiceProviderExtension.GetPublicKeyBlobFromRSA( RSAData );
        provider.ImportCspBlob( publicKeyBlob );
    }
 
    /// <summary>Extension method which initializes an RSACryptoServiceProvider from a DER private key blob.</summary>
    public static void LoadPrivateKeyDER( this RSACryptoServiceProvider provider, byte[] DERData )
    {
        byte[] privateKeyBlob = RSACryptoServiceProviderExtension.GetPrivateKeyDER( DERData );
        provider.ImportCspBlob( privateKeyBlob );
    }
 
    /// <summary>Extension method which initializes an RSACryptoServiceProvider from a PEM public key string.</summary>
    public static void LoadPublicKeyPEM( this RSACryptoServiceProvider provider, string sPEM )
    {
        byte[] DERData = RSACryptoServiceProviderExtension.GetDERFromPEM( sPEM );
        RSACryptoServiceProviderExtension.LoadPublicKeyDER( provider, DERData );
    }
 
    /// <summary>Extension method which initializes an RSACryptoServiceProvider from a PEM private key string.</summary>
    public static void LoadPrivateKeyPEM( this RSACryptoServiceProvider provider, string sPEM )
    {
        byte[] DERData = RSACryptoServiceProviderExtension.GetDERFromPEM( sPEM );
        RSACryptoServiceProviderExtension.LoadPrivateKeyDER( provider, DERData );
    }
 
    /// <summary>Returns a public key blob from an RSA public key.</summary>
    internal static byte[] GetPublicKeyBlobFromRSA( byte[] RSAData )
    {
        byte[] data = null;
        UInt32 dwCertPublicKeyBlobSize = 0;
        if ( RSACryptoServiceProviderExtension.CryptDecodeObject( CRYPT_ENCODING_FLAGS.X509_ASN_ENCODING | CRYPT_ENCODING_FLAGS.PKCS_7_ASN_ENCODING,
            new IntPtr( (int)CRYPT_OUTPUT_TYPES.RSA_CSP_PUBLICKEYBLOB ), RSAData, (UInt32)RSAData.Length, CRYPT_DECODE_FLAGS.NONE,
            data, ref dwCertPublicKeyBlobSize ) )
        {
            data = new byte[ dwCertPublicKeyBlobSize ];
            if ( !RSACryptoServiceProviderExtension.CryptDecodeObject( CRYPT_ENCODING_FLAGS.X509_ASN_ENCODING | CRYPT_ENCODING_FLAGS.PKCS_7_ASN_ENCODING,
                new IntPtr( (int)CRYPT_OUTPUT_TYPES.RSA_CSP_PUBLICKEYBLOB ), RSAData, (UInt32)RSAData.Length, CRYPT_DECODE_FLAGS.NONE,
                data, ref dwCertPublicKeyBlobSize ) )
                throw new Win32Exception( Marshal.GetLastWin32Error() );
        }
        else
            throw new Win32Exception( Marshal.GetLastWin32Error() );
        return data;
    }
 
    /// <summary>Converts DER binary format to a CAPI CRYPT_PRIVATE_KEY_INFO structure.</summary>
    internal static byte[] GetPrivateKeyDER( byte[] DERData )
    {
        byte[] data = null;
        UInt32 dwRSAPrivateKeyBlobSize = 0;
        IntPtr pRSAPrivateKeyBlob = IntPtr.Zero;
        if ( RSACryptoServiceProviderExtension.CryptDecodeObject( CRYPT_ENCODING_FLAGS.X509_ASN_ENCODING | CRYPT_ENCODING_FLAGS.PKCS_7_ASN_ENCODING, new IntPtr( (int)CRYPT_OUTPUT_TYPES.PKCS_RSA_PRIVATE_KEY ),
            DERData, (UInt32)DERData.Length, CRYPT_DECODE_FLAGS.NONE, data, ref dwRSAPrivateKeyBlobSize ) )
        {
            data = new byte[ dwRSAPrivateKeyBlobSize ];
            if ( !RSACryptoServiceProviderExtension.CryptDecodeObject( CRYPT_ENCODING_FLAGS.X509_ASN_ENCODING | CRYPT_ENCODING_FLAGS.PKCS_7_ASN_ENCODING, new IntPtr( (int)CRYPT_OUTPUT_TYPES.PKCS_RSA_PRIVATE_KEY ),
                DERData, (UInt32)DERData.Length, CRYPT_DECODE_FLAGS.NONE, data, ref dwRSAPrivateKeyBlobSize ) )
                throw new Win32Exception( Marshal.GetLastWin32Error() );
        }
        else
            throw new Win32Exception( Marshal.GetLastWin32Error() );
        return data;
    }
 
    /// <summary>Converts DER binary format to a CAPI CERT_PUBLIC_KEY_INFO structure containing an RSA key.</summary>
    internal static byte[] GetRSAFromDER( byte[] DERData )
    {
        byte[] data = null;
        byte[] publicKey = null;
        CERT_PUBLIC_KEY_INFO info;
        UInt32 dwCertPublicKeyInfoSize = 0;
        IntPtr pCertPublicKeyInfo = IntPtr.Zero;
        if ( RSACryptoServiceProviderExtension.CryptDecodeObject( CRYPT_ENCODING_FLAGS.X509_ASN_ENCODING | CRYPT_ENCODING_FLAGS.PKCS_7_ASN_ENCODING, new IntPtr( (int)CRYPT_OUTPUT_TYPES.X509_PUBLIC_KEY_INFO ),
            DERData, (UInt32)DERData.Length, CRYPT_DECODE_FLAGS.NONE, data, ref dwCertPublicKeyInfoSize ) )
        {
            data = new byte[ dwCertPublicKeyInfoSize ];
            if ( RSACryptoServiceProviderExtension.CryptDecodeObject( CRYPT_ENCODING_FLAGS.X509_ASN_ENCODING | CRYPT_ENCODING_FLAGS.PKCS_7_ASN_ENCODING, new IntPtr( (int)CRYPT_OUTPUT_TYPES.X509_PUBLIC_KEY_INFO ),
                DERData, (UInt32)DERData.Length, CRYPT_DECODE_FLAGS.NONE, data, ref dwCertPublicKeyInfoSize ) )
            {
                GCHandle handle = GCHandle.Alloc( data, GCHandleType.Pinned );
                try
                {
                    info = (CERT_PUBLIC_KEY_INFO)Marshal.PtrToStructure( handle.AddrOfPinnedObject(), typeof( CERT_PUBLIC_KEY_INFO ) );
                    publicKey = new byte[ info.PublicKey.cbData ];
                    Marshal.Copy( info.PublicKey.pbData, publicKey, 0, publicKey.Length );
                }
                finally
                {
                    handle.Free();
                }
            }
            else
                throw new Win32Exception( Marshal.GetLastWin32Error() );
        }
        else
            throw new Win32Exception( Marshal.GetLastWin32Error() );
        return publicKey;
    }
 
    /// <summary>Extracts the binary data from a PEM file.</summary>
    internal static byte[] GetDERFromPEM( string sPEM )
    {
        UInt32 dwSkip, dwFlags;
        UInt32 dwBinarySize = 0;
 
        if ( !RSACryptoServiceProviderExtension.CryptStringToBinary( sPEM, (UInt32)sPEM.Length, CRYPT_STRING_FLAGS.CRYPT_STRING_BASE64HEADER, null, ref dwBinarySize, out dwSkip, out dwFlags ) )
            throw new Win32Exception( Marshal.GetLastWin32Error() );
 
        byte[] decodedData = new byte[ dwBinarySize ];
        if ( !RSACryptoServiceProviderExtension.CryptStringToBinary( sPEM, (UInt32)sPEM.Length, CRYPT_STRING_FLAGS.CRYPT_STRING_BASE64HEADER, decodedData, ref dwBinarySize, out dwSkip, out dwFlags ) )
            throw new Win32Exception( Marshal.GetLastWin32Error() );
        return decodedData;
    }
 
    #endregion Methods
 
    #region P/Invoke Constants
 
    /// <summary>Enumeration derived from Crypto API.</summary>
    internal enum CRYPT_ACQUIRE_CONTEXT_FLAGS : uint
    {
        CRYPT_NEWKEYSET = 0x8,
        CRYPT_DELETEKEYSET = 0x10,
        CRYPT_MACHINE_KEYSET = 0x20,
        CRYPT_SILENT = 0x40,
        CRYPT_DEFAULT_CONTAINER_OPTIONAL = 0x80,
        CRYPT_VERIFYCONTEXT = 0xF0000000
    }
 
    /// <summary>Enumeration derived from Crypto API.</summary>
    internal enum CRYPT_PROVIDER_TYPE : uint
    {
        PROV_RSA_FULL = 1
    }
 
    /// <summary>Enumeration derived from Crypto API.</summary>
    internal enum CRYPT_DECODE_FLAGS : uint
    {
        NONE = 0,
        CRYPT_DECODE_ALLOC_FLAG = 0x8000
    }
 
    /// <summary>Enumeration derived from Crypto API.</summary>
    internal enum CRYPT_ENCODING_FLAGS : uint
    {
        PKCS_7_ASN_ENCODING = 0x00010000,
        X509_ASN_ENCODING = 0x00000001,
    }
 
    /// <summary>Enumeration derived from Crypto API.</summary>
    internal enum CRYPT_OUTPUT_TYPES : int
    {
        X509_PUBLIC_KEY_INFO = 8,
        RSA_CSP_PUBLICKEYBLOB = 19,
        PKCS_RSA_PRIVATE_KEY = 43,
        PKCS_PRIVATE_KEY_INFO = 44
    }
 
    /// <summary>Enumeration derived from Crypto API.</summary>
    internal enum CRYPT_STRING_FLAGS : uint
    {
        CRYPT_STRING_BASE64HEADER = 0,
        CRYPT_STRING_BASE64 = 1,
        CRYPT_STRING_BINARY = 2,
        CRYPT_STRING_BASE64REQUESTHEADER = 3,
        CRYPT_STRING_HEX = 4,
        CRYPT_STRING_HEXASCII = 5,
        CRYPT_STRING_BASE64_ANY = 6,
        CRYPT_STRING_ANY = 7,
        CRYPT_STRING_HEX_ANY = 8,
        CRYPT_STRING_BASE64X509CRLHEADER = 9,
        CRYPT_STRING_HEXADDR = 10,
        CRYPT_STRING_HEXASCIIADDR = 11,
        CRYPT_STRING_HEXRAW = 12,
        CRYPT_STRING_NOCRLF = 0x40000000,
        CRYPT_STRING_NOCR = 0x80000000
    }
 
    #endregion P/Invoke Constants
 
    #region P/Invoke Structures
 
    /// <summary>Structure from Crypto API.</summary>
    [StructLayout( LayoutKind.Sequential )]
    internal struct CRYPT_OBJID_BLOB
    {
        internal UInt32 cbData;
        internal IntPtr pbData;
    }
 
    /// <summary>Structure from Crypto API.</summary>
    [StructLayout( LayoutKind.Sequential )]
    internal struct CRYPT_ALGORITHM_IDENTIFIER
    {
        internal IntPtr pszObjId;
        internal CRYPT_OBJID_BLOB Parameters;
    }
 
    /// <summary>Structure from Crypto API.</summary>
    [StructLayout( LayoutKind.Sequential )]
    struct CRYPT_BIT_BLOB
    {
        internal UInt32 cbData;
        internal IntPtr pbData;
        internal UInt32 cUnusedBits;
    }
 
    /// <summary>Structure from Crypto API.</summary>
    [StructLayout( LayoutKind.Sequential )]
    struct CERT_PUBLIC_KEY_INFO
    {
        internal CRYPT_ALGORITHM_IDENTIFIER Algorithm;
        internal CRYPT_BIT_BLOB PublicKey;
    }
 
    #endregion P/Invoke Structures
 
    #region P/Invoke Functions
 
    /// <summary>Function for Crypto API.</summary>
    [DllImport( "advapi32.dll", SetLastError = true )]
    [return: MarshalAs( UnmanagedType.Bool )]
    internal static extern bool CryptDestroyKey( IntPtr hKey );
 
    /// <summary>Function for Crypto API.</summary>
    [DllImport( "advapi32.dll", SetLastError = true )]
    [return: MarshalAs( UnmanagedType.Bool )]
    internal static extern bool CryptImportKey( IntPtr hProv, byte[] pbKeyData, UInt32 dwDataLen, IntPtr hPubKey, UInt32 dwFlags, ref IntPtr hKey );
 
    /// <summary>Function for Crypto API.</summary>
    [DllImport( "advapi32.dll", SetLastError = true )]
    [return: MarshalAs( UnmanagedType.Bool )]
    internal static extern bool CryptReleaseContext( IntPtr hProv, Int32 dwFlags );
 
    /// <summary>Function for Crypto API.</summary>
    [DllImport( "advapi32.dll", CharSet = CharSet.Auto, SetLastError = true )]
    [return: MarshalAs( UnmanagedType.Bool )]
    internal static extern bool CryptAcquireContext( ref IntPtr hProv, string pszContainer, string pszProvider, CRYPT_PROVIDER_TYPE dwProvType, CRYPT_ACQUIRE_CONTEXT_FLAGS dwFlags );
 
    /// <summary>Function from Crypto API.</summary>
    [DllImport( "crypt32.dll", SetLastError = true, CharSet = CharSet.Auto )]
    [return: MarshalAs( UnmanagedType.Bool )]
    internal static extern bool CryptStringToBinary( string sPEM, UInt32 sPEMLength, CRYPT_STRING_FLAGS dwFlags, [Out] byte[] pbBinary, ref UInt32 pcbBinary, out UInt32 pdwSkip, out UInt32 pdwFlags );
 
    /// <summary>Function from Crypto API.</summary>
    [DllImport( "crypt32.dll", SetLastError = true )]
    [return: MarshalAs( UnmanagedType.Bool )]
    internal static extern bool CryptDecodeObjectEx( CRYPT_ENCODING_FLAGS dwCertEncodingType, IntPtr lpszStructType, byte[] pbEncoded, UInt32 cbEncoded, CRYPT_DECODE_FLAGS dwFlags, IntPtr pDecodePara, ref byte[] pvStructInfo, ref UInt32 pcbStructInfo );
 
    /// <summary>Function from Crypto API.</summary>
    [DllImport( "crypt32.dll", SetLastError = true )]
    [return: MarshalAs( UnmanagedType.Bool )]
    internal static extern bool CryptDecodeObject( CRYPT_ENCODING_FLAGS dwCertEncodingType, IntPtr lpszStructType, byte[] pbEncoded, UInt32 cbEncoded, CRYPT_DECODE_FLAGS flags, [In, Out] byte[] pvStructInfo, ref UInt32 cbStructInfo );
 
    #endregion P/Invoke Functions
}

I am attaching a Visual Studio 2012 solution containing a sample usage for PEM and DER encoded files. Click here to download.

Solution containing sample keys:
RSACryptoServiceProviderExtension - Solution
Test program output:

SQL Server – Issues With Linked Server and XML

May 3rd, 2012 Christian Etter No comments

Returning XML data over a Linked Server connection in SQL Server 2005/2008 is not supported in general. However there are several workarounds for this issue which I would like to discuss in this post.

Recently we have encountered a major issue within an application that was supposed to retrieve XML data through a linked server. When the application was set up to connect and retrieve XML data directly from the SQL Server (2008 R2), the result was as expected. However when we executed the same stored procedure over a linked server connection, we were only able to retrieve binary data from the same call instead of proper XML.

Nothing wrong with the linked server though, since the standard “SQL Server” provider was used between both instances of SQL Server 2008 R2.
To reproduce the issue, it is not even necessary to have a second SQL Server instance:

EXEC master.dbo.sp_addlinkedserver @server = N'LOCALHOST', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @server=N'LOCALHOST', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LOCALHOST', @optname=N'rpc out', @optvalue=N'true'

This is all it takes to set up a new linked server which points at the local SQL Server instance. The result of the following two queries is very diffferent:

EXEC           master.dbo.sp_executesql N'SELECT 255 AS A FOR XML RAW'
EXEC LOCALHOST.master.dbo.sp_executesql N'SELECT 255 AS A FOR XML RAW'

We would expect that both would return a single XML resultset in SQL Server Management Studio. Here is the result:

Result of the second query:

0x44014100440372006F0077000102020142FF00000043

Obviously the data returned from the second query is in binary format, always starting with 0×44. However it is not just an XML or NVARCHAR data type which was cast to XML, so any attempts to recover the string representation of the XML file by means of CAST or CONVERT will fail. It turns out that the linked server is not able to decode the SQL Server binary serialized XML format which is used for passing XML data over the TDS protocol to clients. Instead, this data is interpreted as VARBINARY and therefore fails to convert into text or xml on the client side.

Interestingly, the issue does not exist if we are using a very old SQL Server provider which is using ODBC over OLEDB in 32 bit mode. It is really not recommended to use this provider for setting up a linked server in a 64-bit production environment, so I won’t go into details here on how to configure it.

If it is an option to change the code which is executed over a linked server connection, the easiest solution is to return XML data as NVARCHAR instead of in native XML format.

SELECT 255 AS A FOR XML RAW

So the above query can be rewritten to a more or less equivalent:

WITH CTE( X ) AS ( SELECT 255 AS A FOR XML RAW ) SELECT CONVERT( NVARCHAR(MAX), X ) AS X FROM CTE

Result:

Microsoft SQL Server product support has confirmed the above limitation of the linked server provider and suggested a reengineering of the system to get rid of linked server connections in general, which may not be an option for everyone running into this issue. It was also confirmed that it is not possible to convert the binary serialized XML back into textual representation by means of .NET functionality within the Microsoft.SqlServer namespace.

Not all is lost though, since there is a relatively old SQL Server XML API called SQLXML (based on COM) which is capable of converting binary serialized XML data back into its string representation. The API does not ship as part of the .NET framework. Although it also contains managed wrapper classes so it is not necessary to directly use COM interop for using the API in .NET. The latest version can be downloaded here:

Unfortunately, SQLXML does not provide a simple conversion function which allows us to pass in an array of binary serialized XML and retrieve an object oriented or textual representation of its content. The conversion logic can only be used when retrieving binary serialized XML data from the server. So the only way to convert the data is to make a round trip to a SQL Server instance in binary form and apply the conversion logic to the result.
Here is an example:

public string ConvertBinaryToXmlString( string sColumnName, byte[] data )
{
	if ( sColumnName != null )
		sColumnName = sColumnName.Replace( "]", "" ); 
	string sQuery = String.Format( "SELECT ? AS [{0}]", sColumnName );
	SqlXmlCommand cmd = new SqlXmlCommand( this.ConnectionString );
	cmd.CreateParameter().Value = data;
	cmd.CommandType = SqlXmlCommandType.Sql;
	cmd.CommandText = sQuery;
	using ( System.IO.Stream s = cmd.ExecuteStream() )
	using ( StreamReader r = new StreamReader( s ) )
		return r.ReadToEnd();
}

The above is not sufficient for converting XML data of arbitrary length into an XML string. As a matter of fact, it will only work with XML data when the binary representation is smaller than approximately 2 KiB.

In order to convert larger resultsets which contain XML data, we have to add more logic to the actual retrieval of the binary serialized XML.

Categories: C#, Software Development, SQL Tags: .NET, 0x44, C++, Linked Server, SQL Server, SQLXML, XML

Custom Paging Using Linq and ObjectDataSource: Caching Results

June 23rd, 2011 Christian Etter No comments

This is a follow-up post to my previous explanation of writing custom Linq extension methods.

The declaration of a custom paging ObjectDataSource looks like this:

<asp:ObjectDataSource ID="SomeDataSource" EnablePaging="true" runat="server" SelectCountMethod="GetCount" 
    SortParameterName="sSortColumn" SelectMethod="GetData" TypeName="SomeNamespace+SomeClass" 
    StartRowIndexParameterName="iSkip" MaximumRowsParameterName="iTake" >
    <SelectParameters>	
        <asp:Parameter Name="sSomeParameter" Type="String" />
     </SelectParameters>	
</asp:ObjectDataSource>

In this case we need to implement a class named SomeClass which has the following public methods: GetData() and GetCount(). We make use of the non-generic IQueryable extension methods from the previous post. Note that the call of the ToArrayExtension() may be necessary to stop the defered execution built into Linq. Otherwise we could run into an exception when the Linq context is disposed (our class implements IDisposable).

public class SomeClass : IDisposable
{
    protected SomeDataContext db = new SomeDataContext();
 
    public IEnumerable GetData( int iSkip, int iTake, string sSortColumn, string sSomeParameter )
    {
        return this.GetDataInternal( sSomeParameter ).OrderBySQLSyntax( sSortColumn ).SkipExtension( iSkip ).TakeExtension( iTake ).ToArrayExtension();
    }
 
    public int GetCount( string sSomeParameter )
    {
        return this.GetDataInternal( sSomeParameter ).CountExtension();
    }
 
    protected IQueryable GetDataInternal( string sSomeParameter )
    {
        return this.db.tblSomeTable.Where( x => x.Value3 = sSomeParameter ).Select( x => new { ID = x.ID, V1 = x.Value1, V2 = x.Value2 } );
    }
 
    public void Dispose()
    {
        this.db.Dispose();
    }
}

The above gets more useful with increased complexity of the returned data.

There is an alternative approach, which will be based on the fact that GridView/ObjectDataSource will usually call the data retrieval method first, and then call the count method. Therefore we could actually cache the value for the count method when the select method is called. This may also have a positive impact on performance. I am not sure though, if this order of calling can be relied on. Caching cannot be done in an instance variable, since ObjectDataSource apparently creates a new instance upon every call. The place where it makes most sense is withing the HttpContext.Current.Items location inside web applications. In case you are using several ObjectDataSources on one page you have to make sure the parameter name for the cached value is unique, so there are no collisions.

Making use of caching, our query can be further simplified as:

public class SomeClass
{
    public IEnumerable GetData( int iSkip, int iTake, string sSortColumn, string sSomeParameter )
    {
        using ( SomeDataContext db = new SomeDataContext() )
        {
            var v = db.tblSomeTable.Where( x => x.Value3 = sSomeParameter ).Select( x => new { ID = x.ID, V1 = x.Value1, V2 = x.Value2 } );
            HttpContext.Current.Items[ "SomeClass.GetData" ] = v.Count();
            return v.OrderBySQLSyntax( sSortColumn ).Skip( iSkip ).Take( iTake ).ToArray();
        }
    }
 
    public int GetCount( string sSomeParameter )
    {
        object o = HttpContext.Current.Items[ "SomeClass.GetData" ];
        if ( o is int )
            return (int)o;
        throw new Exception( "Caching does not work, since GetCount() was called before GetData()!" );
    }
}

The order in which a GridView calls both methods may depend on the pagers used. Therefore a safe and well performing approach could be to combine the first and the second example.

public class SomeClass : IDisposable
{
    protected SomeDataContext db = new SomeDataContext();
 
    public IEnumerable GetData( int iSkip, int iTake, string sSortColumn, string sSomeParameter )
    {
        var v = this.GetDataInternal( sSomeParameter );
        HttpContext.Current.Items[ "SomeClass.GetData" ] = v.CountExtension();
        return v.OrderBySQLSyntax( sSortColumn ).SkipExtension( iSkip ).TakeExtension( iTake ).ToArrayExtension();
    }
 
    public int GetCount( string sSomeParameter )
    {
        object o = HttpContext.Current.Items[ "SomeClass.GetData" ];
        if ( o is int )
            return (int)o;
        return this.GetDataInternal( sSomeParameter ).CountExtension();
    }
 
    protected IQueryable GetDataInternal( string sSomeParameter )
    {
        return this.db.tblSomeTable.Where( x => x.Value3 = sSomeParameter ).Select( x => new { ID = x.ID, V1 = x.Value1, V2 = x.Value2 } );
    }
 
    public void Dispose()
    {
        this.db.Dispose();
    }
}

Categories: C#, Software Development Tags: .NET, ASP.NET, C++, Caching, GridView, Linq, ObjectDataSource

Older Entries

Archive

.NET RSACryptoServiceProvider PEM + DER Support

SQL Server – Issues With Linked Server and XML

Custom Paging Using Linq and ObjectDataSource: Caching Results

Categories

Categories

Blogroll

Archives

Meta