Monday, 9 March 2009

How to serialize ADODB.Recordset in order to use it with WCF

It is easy to find that, unfortunately, ADODB.Recordset cannot be posted from client to server using WCF. The reason is simple: it is not serializable. The following code allows you to overcome this problem.

1. Without having to serialize using xml, so sufficiently increase the size of data flow in WCF.

2. Without having to convert it into Datatable - you will have quite a perfomnce problem converting it back from a DataTable to ADODB.Recordset in case on the client you (somekind old code) like to consume still ADODB.Recordset

This sample is written using VB.Net and implementing in any other language should be straight forward

'Imaging that rs is a ADODB.Recordset already existing in your code

Dim ret() As Byte
Dim aStr As Object = CreateObject("ADODB.Stream")

rs.Save(aStr, 0) ' 0 is ADODB.PersistFormatEnum.adPersistADTG)
Return aStr.Read(aStr.Size)

Dim binFormat = New System.Runtime.Serialization.Formatters.Binary.BinaryFormatter()

Dim oStream As New System.IO.MemoryStream()
binFormat.Serialize(oStream, rs)
oStream.Position = 0

ret = oStream.ToArray() ' now ret is the byte array and you can post it via WCF

On the client side the following code as an example can be used. Imagine that QueryBypassAsByte is the function that returns the earlier formed byte array over the WCF call

Dim res() As Byte = proxy.QueryBypassAsByte()
Dim oStr As New ADODB.Stream()

'' read back into the stream...
Dim resRecordset As New ADODB.Recordset()
oStr.Open(System.Reflection.Missing.Value, ADODB.ConnectModeEnum.adModeUnknown, ADODB.StreamOpenOptionsEnum.adOpenStreamUnspecified, "", "")
oStr.Type = ADODB.StreamTypeEnum.adTypeBinary
oStr.Position = 0

resRecordset.Open(oStr, System.Reflection.Missing.Value, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, -1)

Now the resRecordset object contains ADODB.Recordset and you can either consume it or pass it further into any old code.