Dailycode.info

Short solution for short problems

Passing SQL parameters through WCF

 Passing SQL parameters through WCF. (cannot be serialized because serialization of indexed properties is not supported.)

 

I was having a problem with this, because of serialization. I was using a Dictionary base object to transport the parameters and at the server side I connected these parameters to the SQL command.  So after visiting this site: http://social.msdn.microsoft.com/Forums/en-US/wcf/thread/467b92bf-42e4-490d-ae92-1b1bc74d0daa/ I came up with a good solution.

 

The class containing the parameters looks like this:

 

using System;

 

using System.Collections;

 

using System.Runtime.Serialization;

 

using System.Collections.Generic;

 

 

 

 

 

namespace GP_Global

 

{

 

    /// <summary>

 

    /// The Collection of columns and their values

 

    /// </summary>

 

    /// <remarks>A pair value that holds column names and their values</remarks>

 

    [DataContract]

 

    public class ColumnCollection

 

    {

 

 

 

        [DataMember]

 

        private Dictionary<string, object> _dictonary;

 

 

 

 

 

        //private _dictionary

 

        /// <summary>

 

        /// Default constructor of ColumnCollection

 

        /// </summary>

 

        public ColumnCollection()

 

        {

 

            this._dictonary = new Dictionary<string, object>();

 

        }

 

 

 

        /// <summary>

 

        /// Add key = column and value to the Array

 

        /// </summary>

 

        /// <param name="Key"></param>

 

        /// <param name="Value"></param>

 

        public void Add(string Key, object Value)

 

        {

 

            _dictonary.Add(Key, Value);

 

        }

 

 

 

 

 

        /// <summary>

 

        /// Remove Key from collection

 

        /// </summary>

 

        /// <param name="Key"></param>

 

        public void Remove(string Key)

 

        {

 

            _dictonary.Remove(Key);

 

        }

 

 

 

        /// <summary>

 

        /// property that returns the value

 

        /// </summary>

 

        public object this[string Key]

 

        {

 

            get { return _dictonary[Key]; }

 

            set { _dictonary[Key] = value; }

 

        }

 

 

 

        [DataMember]

 

        public Dictionary<string, object> ColumnsEnumerator

 

        {

 

            get

 

            {

 

                return new Dictionary<string, object>(this._dictonary);

 

            }

 

            set

 

            {

 

                //blank on purpose

 

            }

 

        }

 

    }

 

}

 

 

 

On the client side you can use the code something like this:

 

Dim sql As StringBuilder = New StringBuilder

 

        'sql.AppendFormat("Select * from DD_Objects where Object like '{0}' and Server like '{1}' and DB like '{2}'", selectedTable, GetSelectedServer, GetSelectedDatabase)

 

        sql.Append("Select * from DD_Objects where Object like @Object and Server like @Server and DB like @DB")

 

        Dim params As ColumnCollection = New ColumnCollection

 

        params.Add("Object", selectedTable)

 

        params.Add("Server", GetSelectedServer)

 

        params.Add("DB", GetSelectedDatabase)

 

        Try

 

            Return service.GetDataSetWithParams(sql.ToString, "Master", params)

 

            'Return service.GetDataSet(sql.ToString, "Master")

 

        Catch servEx As System.ServiceModel.FaultException

 

            GlobalExceptionHandling.ShowException(servEx)

 

            GenericSingleton(Of ServiceFactory(Of GC_WCF_Library.IGlobalDataAccessService)).GetInstance().ReOpenService()

 

        End Try

 

 

 

On the server side I just add the parameters to the SQL command:

 

 

If Not Params Is Nothing Then

 

            AddParams(cmd, Params)

 

End If

 

           

 

Private Shared Sub AddParams(ByVal cmd As SqlCommand, ByVal Params As ColumnCollection)

 

        Dim Param As SqlParameter

 

        For Each de As System.Collections.Generic.KeyValuePair(Of String, Object) In Params.ColumnsEnumerator

 

            If de.Key.ToString() = "" Then

 

                Throw New ArgumentNullException("Key in parameter collection cannot be empty")

 

            ElseIf de.Value = Nothing Then

 

                Throw New ArgumentNullException("Value in parameter collection cannot be null")

 

            ElseIf de.Value.ToString() = "" Then

 

                Param = New SqlParameter(de.Key.ToString, DBNull.Value)

 

            ElseIf de.Value.ToString() = DateTime.MinValue.ToString() Then

 

                Param = New SqlParameter(de.Key.ToString, DBNull.Value)

 

            Else

 

                Param = New SqlParameter(de.Key.ToString, de.Value)

 

            End If

 

            cmd.Parameters.Add(Param)

 

        Next 

 

    End Sub

 

 Since I'm using the Channel Factory, I do not have to put all members as datamember and still I can use them on client side. So the trick is not to set the indexed property as datamamber!