ADO.NET 2.0 DataSet as a Self-Contained

by Peter A. Bromberg, Ph.D.

"Always forgive your enemies; nothing annoys them so much." -- Oscar Wilde I've done some fun work with Compressed DataSets using BinaryFormatter and a DataSetSurrogate class to "flatten out" the ADO.NET DataSet so it can be efficiently serialized in Binary format, as well as some experiments with Angelo Scotto's CompactFormatter, which is a bit faster and offers more compact results than BinaryFormatter. This, combined with ICSharpCode SharpZipLib compression, can make for a very small remotable byte stream that can be sent over the wire via Remoting, sockets, or a WebService. One organization that has it in a production application claims up to 92% bandwidth savings.

In the 2.0 Framework, we have a couple of noteworthy new items that make this reliance on outside libraries and Serialization "helper" classes unnecessary:

1) The ADO.NET 2.0 DataSet now supports Binary Serialization through its RemotingFormat Property, which can now be set to SerializationFormat.Binary, and

2) The .NET Framework 2.0 now supports Deflate Compression "natively" through the System.IO.Compression.DeflateStream class.

With this in mind, I set out to design a little experiment. The basic concept is relatively simple: Let's try to make a DataSet into a self-describing, compressible, remoteable object that can be passed over the wire as a byte array.

Since .NET 1.0, the DataSet has sported a public PropertyCollection called ExtendedProperties which consists of a PropertyCollection that would look like this in C#:

public PropertyCollection get_ExtendedProperties()
{
   if (this.extendedProperties == null)
     {
       this.extendedProperties = new PropertyCollection();
     }
   return this.extendedProperties;
}

This has an indexer which will accept either the key name or an integer index. What this means, in a nutshell, is that we can store anything we want in a DataSet - connection strings, SQL Statements, you name it. Better yet, we can store a custom class that's designed to hold a variety of settings, and that's what I've chosen to do here. So first, let's take a look at the first iteration of my MetaData class:

using System;

using System.Collections.Generic;

using System.Text;

namespace DataSetCompressor

{

    [Serializable]

    public class MetaData

    {

        private DataSetCompressor.Action _action;

        public DataSetCompressor.Action Action

        {

            get { return _action; }

            set { _action = value; }

        }

        private string _insertSpName;

        public string InsertSpName

        {

            get { return _insertSpName; }

            set { _insertSpName = value; }

        }

        private string _updateSpName;

        public string UpdateSpName

        {

            get { return _updateSpName; }

            set { _updateSpName = value; }

        }

        private string _deleteSpName;

        public string DeleteSpName

        {

            get { return _deleteSpName; }

            set { _deleteSpName = value; }

        }

        private string _selectSpName;

        public string SelectSpName

        {

            get { return _selectSpName; }

            set { _selectSpName = value; }

        }

        private string _connectionString;

        public string ConnectionString

        {

            get { return _connectionString; }

            set { _connectionString = value; }

        }

        private System.Data.CommandType _commandType;

        public System.Data.CommandType CommandType

        {

            get { return _commandType; }

            set { _commandType = value; }

        }

        private string _commandText;

        public string CommandText

        {

            get { return _commandText; }

            set { _commandText = value; }

        }

        private object[] _parameters;

        public object[] Parameters

        {

            get { return _parameters; }

            set { _parameters = value; }

        }

        private string _tableName;

        public string TableName

        {

            get { return _tableName; }

            set { _tableName = value; }

        }

        private bool _success;

        public bool Success

        {

            get { return _success; }

            set { _success = value; }

        }

        private Exception _exceptionObject;

        public Exception ExceptionObject

        {

            get { return _exceptionObject; }

            set { _exceptionObject = value; }

        }

    }

}

You can see above that I've provided a number of properties that allow this MetaData class, which is marked Serializable, to hold sufficient information to support a number of different operations. There is also an Action enum which includes the following that describe to the remote endpoint "what action" to perform:

public enum Action
{
AdapterUpdate, //0
RunSproc, //1
RunTextCommand, //2
ExecuteScalar, //3
SprocWithReturnValue //4
}

By adding a utility CODEC (Compressor/Decompressor) class, I have all the ingredients I need to create a 100% self-contained, remoteable DataSet that can be passed over the wire in either direction. Now let's take a look at the CODEC class:

namespace DataSetCompressor

{

    using System.Data;

    using System.Diagnostics;

    using System.IO;

    using System.Runtime.Serialization.Formatters.Binary;

    public class DataSetUtil

    {

        public static byte[] PrepareDataSet(DataSet ds, MetaData md)

        {

            byte[] b = null;

            ds.ExtendedProperties.Add("metaData", md);

            ds.RemotingFormat = System.Data.SerializationFormat.Binary;

            b = CompressDataSet(ds);

            return b;

        }

        public static byte[] CompressDataSet(DataSet ds)

        {

            ds.RemotingFormat = SerializationFormat.Binary;

            BinaryFormatter bf = new BinaryFormatter();

            MemoryStream ms = new MemoryStream();

            bf.Serialize(ms, ds);

            byte[] inbyt = ms.ToArray();

            MemoryStream objStream = new MemoryStream();

            System.IO.Compression.DeflateStream objZS = new System.IO.Compression.DeflateStream(objStream, System.IO.Compression.CompressionMode.Compress);

            objZS.Write(inbyt, 0, inbyt.Length);

            objZS.Flush();

            objZS.Close();

            byte[] b = objStream.ToArray();

            objZS.Dispose();

            return b;

        }

        public static DataSet DecompressDataSet(byte[] bytDs)

        {

            Debug.Write(bytDs.Length.ToString());

            DataSet outDs = new DataSet();

            MemoryStream inMs = new MemoryStream(bytDs);

            inMs.Seek(0, 0);

            using (DeflateStream zipStream = new DeflateStream(inMs, CompressionMode.Decompress, true))

            {

                byte[] outByt = ReadFullStream(zipStream);

                zipStream.Flush();

                zipStream.Close();

                MemoryStream outMs = new MemoryStream(outByt);

                outMs.Seek(0, 0);

                outDs.RemotingFormat = SerializationFormat.Binary;

                BinaryFormatter bf = new BinaryFormatter();

                outDs = (DataSet) bf.Deserialize(outMs, null);

            }

            return outDs;

        }

        public static byte[] ReadFullStream(Stream stream)

        {

            byte[] buffer = new byte[4096];

            using (MemoryStream ms = new MemoryStream())

            {

                while (true)

                {

                    int read = stream.Read(buffer, 0, buffer.Length);

                    if (read <= 0)

                        return ms.ToArray();

                    ms.Write(buffer, 0, read);

                }

            }

        }

    }

}

I hope the above is sufficiently self-explanatory. With these pieces completed, I'll put together a WebService that will have a ProcessDataSet method that accepts the byte array, decompresses, then deserializes back to a DataSet. This can then cast out the MetaData class instance stored in the ExtendedProperties collection, read what it is supposed to do, get the connection string, sprocs, parameters, and whatever else it needs, and perform the action. If the action returns a DataSet, that's fine because we are going to send one back in all cases anyway; it holds the MetaData that tells the caller what happened. After we set the MetaData, we binary serialize and recompress it back to a compact byte array and send it back to the caller.

The WebService ProcessDataSet method:

    [WebMethod]

    public byte[] ProcessDataSet(byte[] compressedDataSet)

    {

        byte[] bytReturn = null;

        DataSet ds = DataSetCompressor.DataSetUtil.DecompressDataSet(compressedDataSet);

        MetaData md = (MetaData) ds.ExtendedProperties["metaData"];

        string cnString = md.ConnectionString;

        CommandType cmdType = md.CommandType;

        string commandText = md.CommandText;

        object[] parms = md.Parameters;

        DataSet dsRet = new DataSet();

        MetaData mdRet = new MetaData();

        int action = (int) md.Action;

        switch (action)

        {

            case 0: //AdapterUpdate       

                // do DataAdapter Update here from Dataset.

                bool retval = false;

                try

                {

                    retval = DaHandler.SubmitChanges(ref ds, cnString, md.SelectSpName, md.UpdateSpName, md.InsertSpName, md.DeleteSpName);

                    mdRet.Success = retval;

                    dsRet.ExtendedProperties.Add("metaData", mdRet);

                }

                catch (Exception ex)

                {

                    mdRet.ExceptionObject = ex;

                    mdRet.Success = false;

                    dsRet.ExtendedProperties.Add("metaData", mdRet);

                }

                break;

            case 1: //RunSproc     

                try

                {

                    dsRet = SqlHelper.ExecuteDataset(cnString, commandText, parms);

                    mdRet.TableName = md.TableName;

                    mdRet.Success = true;

                    dsRet.ExtendedProperties.Add("metaData", mdRet);

                }

                catch (Exception ex)

                {

                    mdRet.ExceptionObject = ex;

                    mdRet.Success = false;

                    dsRet.ExtendedProperties.Add("metaData", mdRet);

                }

                break;

            case 2: // TextCOmmand

                try

                {

                    dsRet = SqlHelper.ExecuteDataset(cnString, CommandType.Text, commandText);

                    mdRet.TableName = md.TableName;

                    mdRet.Success = true;

                    dsRet.ExtendedProperties.Add("metaData", mdRet);

                }

                catch (Exception ex)

                {

                    mdRet.ExceptionObject = ex;

                    mdRet.Success = false;

                    dsRet.ExtendedProperties.Add("metaData", mdRet);

                }

                break;

        } // end switch

        bytReturn = DataSetCompressor.DataSetUtil.CompressDataSet(dsRet);

        return bytReturn;

    }

You may notice that I use a "DAHandler" class to perform DataAdapter updates. This is a utility class that I wrote some time ago that makes DataSet or DataTable updates very easy.

Finally, the solution has a Windows Forms "TestApp" project that uses the DataSetCompressor library to prepare and send requests. It has two buttons, one to get Customers data from Northwind and display it in a DataGridView, and the other to send in updates on anything you have changed in the DataGridView. This also displays the size of the compressed byte array that goes out and comes back in either case. Hint: it's pretty small!

I don't have any particular need for this right now, but I suspect that soon I will, so I wanted to lay the groundwork and "share the code". The 4 sprocs you need to put into Northwind are included in the "SQL" Subfolder.

NOTE: Be sure that your connection string is correct; it may not be the same as mine, and that you have permissions on your sprocs and so on. I always get questions on this very basic database stuff, so that's why I am reminding!

Download the Visual Studio.NET 2005 solution that accompanies this article

Peter Bromberg is a C# MVP, MCP, and .NET consultant who has worked in the banking and financial industry for 20 years. He has architected and developed web - based corporate distributed application solutions since 1995, and focuses exclusively on the .NET Platform. Pete's samples at GotDotNet.com have been downloaded over 41,000 times. You can read Peter's UnBlog Here.  --> Social Link this page<--NOTE: Post QUESTIONS on FORUMS!