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!

 


Override combobox item style. (Windows.Forms)

 

I was thinking of a way to show the status of items in a dropdown list in a nice way.

After some googling around I found a pretty nice solution. You can change the color of the text depending on certain values or even draw a rectangle or circle in front of the items. I choose the last solution. A green item would mean that it’s correctly in the database, blue means it’s in the database but with some modifications to be done. Red means it’s not yet in the database.

Here’s how it looks:

 

The implementation needs 2 things. First you have to set the DrawMode to ‘OwnerDrawVariable’.

Then you can override the DrawItem event. Here’s an example of the implementation I did. It is in VB.net this time since my current workplace has VB.Net written programs.

Private Sub ComboBox1_DrawItem(ByVal sender As Object, _

        ByVal e As System.Windows.Forms.DrawItemEventArgs) _

        Handles cmbTable.DrawItem

 

        Dim size As Single

        Dim myFont As System.Drawing.Font

        Dim family As FontFamily

 

        Dim rectColor As New System.Drawing.Color

        Dim tableName As String = dsTables.Tables(0).Rows(e.Index)("TABLE_NAME")

        If tableDiffThenInDBHashtable.Contains(tableName) Then

            size = 9

            rectColor = System.Drawing.Color.Blue

            family = Me.Font.FontFamily

        ElseIf tableInDBHashtable.Contains(tableName) Then

            size = 8

            rectColor = System.Drawing.Color.Green

            family = Me.Font.FontFamily

        Else

            size = 9

            rectColor = System.Drawing.Color.Red

            family = Me.Font.FontFamily

        End If

 

        ' Draw the background of the item.

        e.DrawBackground()

 

        ' Create a square filled with the color.

        Dim rectangle As Rectangle = New Rectangle(2, e.Bounds.Top + 2, _

            e.Bounds.Height, e.Bounds.Height - 4)

        e.Graphics.FillRectangle(New SolidBrush(rectColor), rectangle)

 

        ' Draw each string in the datasource, using a size, color, and font for each item.

        myFont = New Font(family, size, FontStyle.Regular)

        e.Graphics.DrawString(dsTables.Tables(0).Rows(e.Index)("TABLE_NAME"), myFont, System.Drawing.Brushes.Black, _

            New RectangleF(e.Bounds.X + rectangle.Width, e.Bounds.Y, _

            e.Bounds.Width, e.Bounds.Height))

 

        ' Draw the focus rectangle if the mouse hovers over an item.

        e.DrawFocusRectangle()

    End Sub

 

I have filled some hash tables to quickly check the value of the item and decide which color to appoint.

Now it looks like this:

 


Using a singleton class to cache the WCF services

I’ve written several articles on generic singleton and WCF. I think it’s very powerfull. After some implementations, the code gets better and better. Together with a collague I created a generic factory to instantiate services. Then I add them to a singleton class to cache them. This works great.

First time I got some trouble with it was because the server only allowed 20 instances of a service for a session. To avoid exceptions messing up the connections, I created a new channel for each call. But the channel is not closed in a good way because of the singleton class. Therefore I needed some good exception handling. For certain exceptions I reopen the service letting go of the previous connection and creating a new connection. Here’s the function that is in the generic service factory (Full code below)that does the trick:

public void HandleWCFException(Exception er)

{

if (er.GetType() == typeof(System.TimeoutException) || er.GetType() == typeof(System.ServiceModel.FaultException))

{

             ReOpenService();

}

       else

       {

             throw er;

}

}

 

public void ReOpenService()

{

if (_fact.State == CommunicationState.Opened)

       {

             try

              {

                    _fact.Close();

              }

              catch { }

            }

            _fact = new ChannelFactory<T>(_endPoint);

            _service = _fact.CreateChannel();

       }

}

So the implementation look s like this:

Try 

GenericSingleton(Of ServiceFactory(Of GC_WCF_Library.IGlobalDataAccessService)).GetInstance().Service.SaveDataSet(sql, ds, "OPS")

Catch servEx As System.Exception

If Not GlobalFunctions.HandleWCFErrors(servEx) Then

             Throw servEx

End If

End Try

 

There you can see the Service factory cached in the Generic Singleton class. We call a function called SaveDataSet on the service of type IGlobalDataAccessService. If an exception is caught, we check if it’s an WCF related exception. If so I reopen the service because it could be in the faulted state.

Also make sure you set the close timeout of your service to 2 or 3 seconds, because alse the closing of a service in faulted state could last a long time.

Here the complete code for the generic service factory:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.ServiceModel;

 

namespace GP_Global

{

    public class ServiceFactory<T> where T : class

    {

        private T _service;

        private ChannelFactory<T> _fact;

        private string _endPoint;

 

        public ChannelFactory<T> Factory

        {

            get

            {

                if (_fact.State != CommunicationState.Opened)

                {

                    _fact = new ChannelFactory<T>(_endPoint);

                }

                return _fact;

            }

            set { _fact = value; }

        }

 

        public T Service

        {

            get

            {

                if (_fact.State != CommunicationState.Opened)

                {

                    _fact = new ChannelFactory<T>(_endPoint);

                    //fact.Open();

                    _service = _fact.CreateChannel();

                }

                //_service = fact.CreateChannel();

                return _service;

               

            }

            set

            {

                _service = value;

            }

        }

 

        public void HandleWCFException(Exception er)

        {

            if (er.GetType() == typeof(System.TimeoutException) || er.GetType() == typeof(System.ServiceModel.FaultException))

            {

                ReOpenService();

            }

            else

            {

                throw er;

            }

        }

 

        public void ReOpenService()

        {

            if (_fact.State == CommunicationState.Opened)

            {

                try

                {

                    _fact.Close();

                }

                catch { }

            }

            _fact = new ChannelFactory<T>(_endPoint);

            _service = _fact.CreateChannel();

        }

 

        public void InitFactory(string endpoint)

        {

            if (_service == null)

            {

                _fact = new ChannelFactory<T>(endpoint);

                _endPoint = endpoint;

                _service = _fact.CreateChannel();

            }

        }

    }

}


Binding doesn't track (pick up) the change if you programatically edit a control.

I'm working on a windows forms application that uses binding to update the contents of the controls. Works like a charm.
When the form got a little more complicated and controls where addedd dynamically, it got a little tricky.
When I changed the value of the controls programatically, the change was not picked up by the bindings. I didn't had to look very long for a solution.
I just added a .Focus or a .Select and then the binding picks up the change.
By example:
 
txtVersion.Text = versionNum.ToString
txtVersion.Select()
or
txtVersion.Text = versionNum.ToString
txtVersion.Focus()

WCF: Conflicts between config file on server and client

A simple trick to fix this problem is to add the service via visual studio's service reference. Then Visual studio will create the configuration settings for you on the client based on the settings of the server and the local machine settings. Sometimes the conflict comes because a setting is not explicitely defined on the server and then it picks up the machine.config settings. So just add the service with visual studio and inspect the settings or just take them over.