Any solution or workaround to serializing SQLParameter in WCF?

Question

I had originally defined in my MessageContract a collection of SQLParameters in order to create a simple stored procedure execution through a WCF service. Apparently, SQLParameter type is not serializable, so I need some advice on how to proceed here.

Is it possible to still somehow use SQLParameter as part of my WCF contract, or I have to do something else, like creating a custom class with the same properties as an SQLParameter, then create the SQLParameters elsewhere in my code?

UPDATE:
For further context as to why this situation comes about, originally the windows form client was connecting directly to the database to retrieve a DataSet for reporting purposes, using the usual ADO.NET objects. Now, the customer wants a common web service to handle all reports. This is the best I can think of to handle it without too much changes.

Answer 1

I was relatively underwhelmed with the accepted-response that:

You might find you want to refactor those further, to reduce the number or increase the level of abstraction. But if not, then you should then do the equivalent of extracting all of those methods into one or more interfaces. Those interfaces would become the ServiceContracts for your WCF service. Move the methods into the new services to implement these service contracts, and you're pretty much done.

Fundamentally, this is a correct response for simple predefined business logic; however, with different levels of abstraction, say a service that is required to run ad-hoc sql queries, one cannot simply provide this level of flexibility with predefined service calls.

For ad-hoc queries to work in a WCF service environment the parameters must be passed in order to safeguard the system and prevent various SQL-injection style attack vectors.

Point in case, I have built a service that as a business requirement needs to abstract the data layer away from the client and allow for third-parties to interface with a number of databases on disparate db systems.

For this system, I have taken Craig H's approach above and created a SerializableSqlParam class to pass as a list object to my service.

The benefit of my SerializableSqlParam class is as follows:

1. Direct serialization and typecasting of SqlParameter class.
2. Serialized objects are stored in UTF-16 string format to allow for SQL server to save the objects.
3. Proper use of AssemblyQualifiedName to allow for deserialization of objects not in the immediate assembly.
4. Complete marshalling of the SqlParameter class parameters.

The general usage is as follows:

SerializedSqlParam sp = new SerializedSqlParam(new SqlParameter("@id", 1));

//or through typecasting:

SqlParameter parameter = new SqlParameter("@id", 1);
SerializedSqlParam sp = (SerializedSqlParam) parameter;

To deserialize simply do the following:

SqlParameter parameter = sp.GetSqlParameter();

//or through typecasting

SqlParameter parameter = (SqlParameter) sp;

Here is my class. I'm sure that there are things that can be fixed / improved; however, this is simply to get the concept across. Hopefully, other readers will find this helpful!

SerializedSqlParam.cs

[DataContract]
public class SerializedSqlParam
{
    [Browsable(false)]
    [DataMember]
    public string CompareInfo { get; set; } 

    [RefreshProperties(RefreshProperties.All)]
    [DataMember]
    public string Direction { get; set; }

    [DataMember]
    public bool IsNullable { get; set; }

    [Browsable(false)]
    [DataMember]
    public int LocaleId { get; set; }

    [Browsable(false)]
    [EditorBrowsable(EditorBrowsableState.Advanced)]
    [DataMember]
    public int Offset { get; set; }

    [DataMember]
    public string ParameterName { get; set; }

    [DefaultValue(0)]
    [DataMember]
    public byte Precision { get; set; }

    [DefaultValue(0)]
    [DataMember]
    public byte Scale { get; set; }

    [DataMember]
    public int Size { get; set; }

    [DataMember]
    public string SourceColumn { get; set; }

    [DataMember]
    public bool SourceColumnNullMapping { get; set; }

    [DataMember]
    public string SourceVersion { get; set; }

    [DataMember]
    public string SqlDbType { get; set; }

    [DataMember]
    public string TypeName { get; set; }

    [DataMember]
    public string UdtTypeName { get; set; }

    [DataMember]
    public string Value { get; set; }

    [DataMember]
    public string ValueType { get; protected set; }

    [DataMember]
    public string XmlSchemaCollectionDatabase { get; set; }
    [DataMember]
    public string XmlSchemaCollectionName { get; set; }
    [DataMember]
    public string XmlSchemaCollectionOwningSchema { get; set; }

    public SerializedSqlParam(SqlParameter p)
    {
        this.CopyProperties(p);
        this.SerializeParameterValue(p);
    }

    public static explicit operator SerializedSqlParam(SqlParameter p)
    {
        return new SerializedSqlParam(p);
    }

    public static explicit operator SqlParameter(SerializedSqlParam p)
    {
        return p.GetSqlParameter(p);
    }

    public SqlParameter GetSqlParameter()
    {
        return this.GetSqlParameter(this);
    }

    public SqlParameter GetSqlParameter(SerializedSqlParam serialized)
    {
        SqlParameter p = new SqlParameter();

        p.ParameterName = serialized.ParameterName;
        p.Precision = serialized.Precision;
        p.Scale = serialized.Scale;
        p.Size = serialized.Size;
        p.IsNullable = serialized.IsNullable;
        p.LocaleId = serialized.LocaleId;
        p.Offset = serialized.Offset;
        p.SourceColumn = serialized.SourceColumn;
        p.SourceColumnNullMapping = serialized.SourceColumnNullMapping;

        p.XmlSchemaCollectionDatabase = serialized.XmlSchemaCollectionDatabase;
        p.XmlSchemaCollectionName = serialized.XmlSchemaCollectionName;
        p.XmlSchemaCollectionOwningSchema = serialized.XmlSchemaCollectionOwningSchema;

        p.TypeName = serialized.TypeName;
        p.UdtTypeName = serialized.UdtTypeName;

        p.Direction = (ParameterDirection)Enum.Parse(typeof(ParameterDirection), serialized.Direction);
        p.CompareInfo = (SqlCompareOptions)Enum.Parse(typeof(SqlCompareOptions), serialized.CompareInfo);
        p.SourceVersion = (DataRowVersion)Enum.Parse(typeof(DataRowVersion), serialized.SourceVersion);

        p.Value = this.DeserializeObject(serialized.Value, Type.GetType(serialized.ValueType));

        return p;
    }

    private void SerializeParameterValue(SqlParameter p)
    {
        if (p.Value.GetType().IsSerializable)
        {
            this.ValueType = this.GetTypeAssemblyQualifiedName(p.Value);
            this.Value = this.SerializeObject(p.Value);
        }
        else
        {
            throw new SerializationException("Cannot serialize the parameter value object. Recast that object into a primitive or class that can be serialized.");
        }
    }

    private void CopyProperties(SqlParameter p)
    {
        this.ParameterName = p.ParameterName;
        this.Precision = p.Precision;
        this.Scale = p.Scale;
        this.Size = p.Size;
        this.IsNullable = p.IsNullable;
        this.LocaleId = p.LocaleId;
        this.Offset = p.Offset;
        this.SourceColumn = p.SourceColumn;
        this.SourceColumnNullMapping = p.SourceColumnNullMapping;

        this.XmlSchemaCollectionDatabase = p.XmlSchemaCollectionDatabase;
        this.XmlSchemaCollectionName = p.XmlSchemaCollectionName;
        this.XmlSchemaCollectionOwningSchema = p.XmlSchemaCollectionOwningSchema;

        this.TypeName = p.TypeName;
        this.UdtTypeName = p.UdtTypeName;

        this.Direction = p.Direction.ToString();
        this.CompareInfo = p.CompareInfo.ToString();
        this.SourceVersion = p.SourceVersion.ToString();

        try
        {
            this.SqlDbType = p.SqlDbType.ToString();
        }
        catch
        {
            this.SqlDbType = null;
        }
    }

    private string SerializeObject(object value)
    {
        if (value == null) return null;

        XmlSerializer serializer = new XmlSerializer(value.GetType());
        XmlWriterSettings settings = new XmlWriterSettings();

        settings.Encoding = new UnicodeEncoding(false, false);
        settings.Indent = false;
        settings.OmitXmlDeclaration = false;

        using (StringWriter textWriter = new StringWriter())
        {
            using (XmlWriter xmlWriter = XmlWriter.Create(textWriter, settings))
            {
                serializer.Serialize(xmlWriter, value);
            }
            return textWriter.ToString();
        }
    }

    private object DeserializeObject(string xml, Type type)
    {
        if (string.IsNullOrEmpty(xml)) return null;

        XmlSerializer serializer = new XmlSerializer(type);

        XmlReaderSettings settings = new XmlReaderSettings();
        using (StringReader textReader = new StringReader(xml))
        {
            using (XmlReader xmlReader = XmlReader.Create(textReader, settings))
            {
                return Convert.ChangeType(serializer.Deserialize(xmlReader), type);
            }
        }
    }

    private string GetTypeAssemblyQualifiedName(object obj)
    {
        return obj.GetType().AssemblyQualifiedName.ToString();
    }
}

Answer 2

First - if you want "database like" access over WCF, then ADO.NET Data Services is a far better option.

But no; you can't serialize an SqlParameter over WCF; you would need to encapsulate it in some other representation. Note that IMO it is quite dangerous to expose your database logic so close to the WCF boundary - I'd just have WCF methods that abstract this - i.e.

[OperationContract]
Customer[] FindCustomers(string id, string name, string location, ...);

Then you have a tightly controlled service interface.

Answer 3

It sounds like you're trying to take too much the easy way out. I would refactor those methods that were being used to do the direct database access, primarily by using an "Extract Method" refactoring. That would leave you with a (large) number of small methods, each accepting a set of parameters and returning a DataSet, and each with a specific purpose.

You might find you want to refactor those further, to reduce the number or increase the level of abstraction. But if not, then you should then do the equivalent of extracting all of those methods into one or more interfaces. Those interfaces would become the ServiceContracts for your WCF service. Move the methods into the new services to implement these service contracts, and you're pretty much done.

This works better with automated unit tests and good code coverage, of course. That will provide the level of confidence necessary to do something this radical.

Answer 4

I just created a simple serialization wrapper for the sqlparameter

#region

using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml.Serialization;

#endregion

[Serializable]
public class SQLParamSerializationWrapper
{
    #region Constants and Fields

    private SqlParameter param;

    #endregion

    #region Constructors and Destructors

    public SQLParamSerializationWrapper()
    {
        //paramless constructor for serialization
        this.param = new SqlParameter();
    }

    public SQLParamSerializationWrapper(SqlParameter param)
    {
        this.SQLParam = param;
    }

    #endregion

    #region Properties

    public DbType DbType
    {
        get
        {
            return this.SQLParam.DbType;
        }
        set
        {
            this.SQLParam.DbType = value;
        }
    }

    public ParameterDirection Direction
    {
        get
        {
            return this.SQLParam.Direction;
        }
        set
        {
            this.SQLParam.Direction = value;
        }
    }

    public string ParameterName
    {
        get
        {
            return this.SQLParam.ParameterName;
        }
        set
        {
            this.SQLParam.ParameterName = value;
        }
    }

    [XmlIgnore]
    public SqlParameter SQLParam
    {
        get
        {
            return this.param;
        }
        set
        {
            this.param = value;
        }
    }

    public int Size
    {
        get
        {
            return this.SQLParam.Size;
        }
        set
        {
            this.SQLParam.Size = value;
        }
    }

    public object Value
    {
        get
        {
            return this.SQLParam.Value;
        }
        set
        {
            this.SQLParam.Value = value;
        }
    }

    #endregion
}

You can then use it as follows

Serialize (im using a list of params) :-

List<SQLParamSerializationWrapper> procParams = new List<SQLParamSerializationWrapper>();
            SqlParameter startdate = new SqlParameter("dateStart", new DateTime(2011, 9, 5));
            SqlParameter enddate = new SqlParameter("dateEnd", new DateTime(2011, 9, 6));

            SQLParamSerializationWrapper startDateWrapper = new SQLParamSerializationWrapper(startdate);
            SQLParamSerializationWrapper endDateWrapper = new SQLParamSerializationWrapper(enddate);

            procParams.Add(startDateWrapper);
            procParams.Add(endDateWrapper);

            string paramsAsXML = "";

            using (var sw = new StringWriter())
            {
                using (var xw = XmlWriter.Create(sw))
                {
                    XmlSerializer xs = new XmlSerializer(procParams.GetType());
                    xs.Serialize(xw, procParams);
                }
                paramsAsXML = sw.ToString();
            }

Deserialize :-

var procParams = new List<SqlParameter>();

StringReader sr = new StringReader(parm.Value);
                        // Create an instance of the XmlSerializer specifying type.
                        XmlSerializer deserializer = new XmlSerializer(typeof(List<SQLParamSerializationWrapper>));

                        List<SQLParamSerializationWrapper> sqlParamWrapper = (List<SQLParamSerializationWrapper>)deserializer.Deserialize(sr);

                        foreach (var param in sqlParamWrapper)
                        {
                            procParams.Add(param.SQLParam);
                        }