Saturday, March 31, 2012

Thread Synchronization Context


Below example demonstrates, updating UI the status of background running long process.
This can also be implemented using delegates by invoking the actual method which deals with background job and updating the status in the UI which is running in a different thread.
Since the UI is running in a different thread than in which the actual background job is working, while updating it is required to perform invoking through delegate like “MethodInvoker”.
Beside this, below example also updates the status in UI, but by using the Synchronization context of UI Thread.
DB.cs
The actual background job
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Threading;
namespace InsertBulkRecords
{
    public class InsertStatus
    {
        public int CurrentRecord;
        public int MaxRecords;
        public bool Completed = false;
        public bool Cancelled = false;
        public string Error;
    }
    public class DB
    {
        SynchronizationContext objUIContext = null;
        SendOrPostCallback objUIUpdate = null;
        CancellationToken objTkn;
        Task objTask = null;

        //Insert function can also be defined as below
        //public void Insert(CancellationToken tkn, SynchronizationContext argUIContext, SendOrPostCallback argUIUpdate)

        public void Insert(CancellationToken tkn, SynchronizationContext argUIContext, Action<Object> argUIUpdate)
        {
            objTkn = tkn;
            objUIUpdate = new SendOrPostCallback(argUIUpdate);
            //Incase if the last argument type is "SendOrPostCallback" then above line should be commented
            //and below line should be uncommented
            //objUIUpdate=argUIUpdate

            objUIContext = argUIContext;
            //registering to invoke a function, when the cancellation token is cancelled
            tkn.Register(delegate
            {
                objUIContext.Send(objUIUpdate, new InsertStatus()
                {
                    Cancelled = true,
                    CurrentRecord = 0,
                    MaxRecords = 0
                });
            });

            //starting the task invoking the "InsertBackend" function
            objTask = Task.Factory.StartNew(() => { InsertBackend(); }, tkn);

            //making below task to continue when there is any
            //fault during the execution of the task
            objTask.ContinueWith(new Action<Task>((t) =>
            {
                objUIContext.Post(objUIUpdate, new InsertStatus()
                {
                    CurrentRecord = 0,
                    MaxRecords = 0,
                    Error = t.Exception.GetBaseException().Message
                });
            }), TaskContinuationOptions.OnlyOnFaulted);

            //making below task to continue when the task is
            //completed
            objTask.ContinueWith((t) =>
            {
                if (objTkn.IsCancellationRequested)
                    return;
                objUIContext.Post(objUIUpdate, new InsertStatus()
                {
                    Completed = true
                });
            }, TaskContinuationOptions.OnlyOnRanToCompletion);
        }

        //The function which inserts records (taking time).
        private void InsertBackend()
        {
            int maxRecords = 60;
            int recordIndex = 0;
            int recWaitTime = 100;
            while (recordIndex < maxRecords)
            {
                Thread.Sleep(recWaitTime);
                recordIndex++;
                //checking if the token cancellation is requested
                if (objTkn.IsCancellationRequested)
                    return;
                objUIContext.Send(objUIUpdate, new InsertStatus() { CurrentRecord = recordIndex, MaxRecords = maxRecords });
                //throwing exception, for specific record. The UI should be updated saying as failed
                //this helps to check if the "fault" continution task is getting invoked
                if (recordIndex == 1000)
                    throw new Exception("error while inserting 1000 th record");
            }
        }
    }
}

UI (Form.cs)
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Threading.Tasks;
using System.Threading;
using InsertBulkRecords;
namespace InsertBulkRecords_UI
{
    public partial class frmInsertRecords : Form
    {
        public frmInsertRecords()
        {
            InitializeComponent();
        }

        CancellationTokenSource cancellationSrc = new CancellationTokenSource();

        private void btnInsert_Click(object sender, EventArgs e)
        {
            try
            {
                InsertBulkRecords.DB objDB = new DB();
                cancellationSrc = new CancellationTokenSource();
                //below code to call the insert function and send the "SendOrPostCallback" delegating the "UpdateUI" function
                //"Insert" function should also be expecting the "SendOrPostCallback" type argument for the last
                //argument
                //objDB.Insert(cancellationSrc.Token, SynchronizationContext.Current, new SendOrPostCallback(UpdateUI));

                //calling the insert function sending the function using lamda expression
                //objDB.Insert(cancellationSrc.Token, SynchronizationContext.Current, (o) => {
                //    //write function to type cast "o" and perform UI Update
                //});

                //calling the insert function sending the function by lamda expression  delegting(Action) function
                //since "Action<Object>" delegate is same as "SendOrPostCallback" type
                //"Insert" function should also be expecting the "Action<Object>" type argument for the last
                //argument
                //objDB.Insert(cancellationSrc.Token, SynchronizationContext.Current, new Action<Object>((o) =>
                //{
                //write function to type cast "o" and perform UI Update
                //}));

                //calling the insert function sending the function by delegating
                objDB.Insert(cancellationSrc.Token, SynchronizationContext.Current, delegate(object o)
                {
                    {
                        try
                        {
                            InsertStatus argInsertStatus = o as InsertStatus;
                            if (argInsertStatus.Error != null && argInsertStatus.Error.ToString().Trim().Length > 0)
                            {
                                lblStatus.Text = "Error : " + argInsertStatus.Error;
                                prgInsertStatus.Value = 0;
                            }
                            else if (argInsertStatus.Completed)
                            {
                                lblStatus.Text = "Completed";
                                prgInsertStatus.Value = 0;
                            }
                            else if (argInsertStatus.Cancelled)
                            {
                                lblStatus.Text = "Cancelled";
                                prgInsertStatus.Value = 0;
                            }
                            else
                            {
                                prgInsertStatus.Value = argInsertStatus.CurrentRecord;
                                prgInsertStatus.Maximum = argInsertStatus.MaxRecords;
                                lblStatus.Text = "Inserting... " + argInsertStatus.CurrentRecord + "/" + argInsertStatus.MaxRecords;
                            }
                        }
                        catch (Exception exp)
                        {
                            MessageBox.Show("Error : " + exp.Message);
                        }
                    }
                });
            }
            catch (Exception exp)
            {
                MessageBox.Show("Error : " + exp.Message);
            }
        }

        public void UpdateUI(Object objInsertStatus)
        {
            try
            {
                InsertStatus argInsertStatus = objInsertStatus as InsertStatus;
                if (argInsertStatus.Error != null && argInsertStatus.Error.ToString().Trim().Length > 0)
                {
                    lblStatus.Text = "Error : " + argInsertStatus.Error;
                    prgInsertStatus.Value = 0;
                }
                else if (argInsertStatus.Completed)
                {
                    lblStatus.Text = "Completed";
                    prgInsertStatus.Value = 0;
                }
                else if (argInsertStatus.Cancelled)
                {
                    lblStatus.Text = "Cancelled";
                    prgInsertStatus.Value = 0;
                }
                else
                {
                    prgInsertStatus.Value = argInsertStatus.CurrentRecord;
                    prgInsertStatus.Maximum = argInsertStatus.MaxRecords;
                    lblStatus.Text = "Inserting... " + argInsertStatus.CurrentRecord + "/" + argInsertStatus.MaxRecords;
                }
            }
            catch (Exception exp)
            {
                MessageBox.Show("Error : " + exp.Message);
            }
        }
        private void btnCancel_Click(object sender, EventArgs e)
        {
            try
            {
                //cancelling token source
                if (!cancellationSrc.IsCancellationRequested)
                    cancellationSrc.Cancel();
            }
            catch (Exception exp)
            {
                MessageBox.Show("Error : " + exp.Message);
            }
        }
    }
}


Wednesday, March 28, 2012

Pivot and UnPivot - SQL


Pivot helps to apply transpose on a table (making row values under specific column as column headers in the final output table and grouping the other column specific row values).
UnPivot helps to apply transpose such that the column names will be values under specific column for each row in the final output table after running the sql).
Looking into the behaviors of “Pivot” and “Un Pivot” as stated above, looks like
UnPivot(Pivot(Table))=Table?

But is not true really always, when a pivot is applied on a table, the query expects an aggregate function like (max, min, sum, etc…) on the column which has to be displayed in the final table under the specific column (which is row value before pivot). So when there is more than one record in the actual table which has same row value in the column which we are about to make as header in the pivot table will apply aggregate on the other column. This applied aggregate could be sum, cannot be brought back while applying unpivot.

Below example helps to understand better.

Pivot

I have created a new table “Marks” which holds the student name (sname), subject id (sid), marks (marks).
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Marks](
      [sid] [int] NOT NULL,
      [marks] [float] NOT NULL,
      [sname] [varchar](250) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

I have inserted few dummy records like


Sid   Marks SName
1     50    Name1
2     50    Name1
3     80    Name1
1     90    Name2
2     95    Name2
3     99    Name2

I am looking for an output where it should be like,
SName                 Maths   Social       English                TotalScore
Name1                 50          50              80                        180
Name2                 90          95              99                         284
Where Sid=1 (Maths), Sid=2(Social), Sid=3 (English).
So I can understand that the in each row in the actual table, I need to bring out the “Sid” values and
Attach as header in the output expected table also applying alias for each sid.
Now, we need to check the marks” and “SName”, in this case I can see “SName” is same for a set of records which will be grouped by in pivot. The other one is the marks”, so for a given Sid and Sname I can see there will be only on record and hence only one marks value can be fetched.
In this case it is not required to worry about to apply the aggregate function (min or max or sum), since there will be only one record.
In case for a given Sid and Sname if  there is more than one record and then it matters the right aggregate function to apply.

Below is my sql to get the expected table result.

select Sname, [1] as Maths,[2] as Social,[3] as English,
[1]+[2]+[3] as TotalScore
from
(select * from dbo.Marks) tab1
pivot(max(marks) for sid in ([1],[2],[3])) as pvt
From query, iterating  through each row in the main table, query considers only those records whose “Sid”is 1 or 2 or 3
After getting the final list of records, fetching the maximum of marks for each row (Sname and Sid).
The query groups the list of columns other than (Sid and Marks) which is SName.

UnPivot

Lets us to try to unpivot the same table which we pivoted above.
I am creating a temporary table inserting records from the above pivot query skipping the total score.

select Sname, [1] as Maths,[2] as Social,[3] as English
 into dbo.Marks_PVT
from
(select * from dbo.Marks) tab1
pivot(max(marks) for sid in ([1],[2],[3])) as pvt

The above creates a new table named as  Marks_PVT

SName                 Maths   Social       English               
Name1                 50          50              80                      
Name2                 90          95              99                      
                Now, while trying to unpivot obviously my thought will be generate a similar kind of original table which we used to pivot in above example.
Which is like
Subject           Marks    SName
Maths              50           Name1
Social              50           Name1
English             80           Name1
Maths              90           Name2
Social              95           Name2
English             99           Name2

Observing the expected output, I can understand from the Marks_PVT, ineed to make columns “Maths”, “ Social” and “English” as row values for each under some column called “Subject”.
Also moving the corresponding values for each column of “Maths”, “ Social” and “English” under a single column called ‘marks”.

To do this, we need to UnPivot saying as
“marks for Subject in
([Maths],[Social],[English]”
Below query reflects the desired output
select Subject,Marks,Sname from
(select * from dbo.Marks_PVT) tab1
unpivot(marks for Subject in
([Maths],[Social],[English])) tab2

Note : Here we can observe the behaviour is like
UnPivot(Pivot(Table))=Table
This fails when, during pivot for a given Sid and Sname if  there is more than one record and then it pivot helps to display by aggregating them as single row. In this case more than 1 row in the original table is pivoted as a single row and this can be brought back as multiple during unpivot, since aggregation has happened.