Multi-threading with the help of Extension method

I love Extension methods!

For people who work with multi-threaded windows forms application, it's common to write code like this:

public void BindNewLoginList(object logins)
{
      if (dgvLogins.InvokeRequired)
      {
          dgvLogins.BeginInvoke((System.Threading.ThreadStart)delegate()
          {
             BindNewLoginList(logins);
          });
          return;
      }
      dgvLogins.DataSource = logins;
}

But with the new extension feature of .Net Framework, I can create a method like this:

public static void ThreadSafeInvoke(this System.Windows.Forms.Control control, System.Threading.ThreadStart method)
{
           if (control.InvokeRequired)
               control.BeginInvoke(method);
           else
               method.DynamicInvoke();
}

Now, I simply call this method from any windows control, and it will take care of checking of the caller and invoke the method accordingly. How nice and neat it is!

public void BindNewLoginList(object logins)
{
     dgvLogins.ThreadSafeInvoke(() => dgvLogins.DataSource = logins);
}

SQL: A recursive query for a self-reference table

I was working with a self-reference table and wanted to query the parent-child relationship.



Say, given an ExceptionRepositoryId, I want to display all its children. After a little while I came up with a recursive SQL:

DECLARE @parentId int
SET @parentId = 210;

WITH TEMP (ExceptionRepositoryId, ParentExceptionRepositoryId)
AS
(
 SELECT
  ExceptionRepositoryId, ParentExceptionRepositoryId
 FROM
  dbo.tbl_ExceptionRepository
 WHERE
  ExceptionRepositoryId = @parentId
 UNION ALL
 SELECT
  t1.ExceptionRepositoryId, t1.ParentExceptionRepositoryId
 FROM
  dbo.tbl_ExceptionRepository t1
  inner join TEMP t2 ON t1.ParentExceptionRepositoryId = t2.ExceptionRepositoryId
)
SELECT * FROM TEMP

And I am happy to get the results like the following:

Entity Framework: Error 2048 when using custom stored procedures

I was trying to map my custom stored procedures to Insert/Update/Delete functions on the Entity Framework model designer. After hitting the "Save" button, I got 2 error messages like the following:

Error 2048: The entity set '[set name]' includes function mappings for association set '[fk name]', but none exists in element 'DeleteFunction' for type '[entity type name]'. Association sets must be consistently mapped for all operations.

I was confused, why do I need to have association mapping in the delete function? After a bit of searching and digging, here are the findings.

First, let's have a look at the two Entity models in question.

I have a 1-to-many relationship from left to right between ExceptionSource and ExceptionRepository. Besides, there is a self reference 1-to-many relationship on ExceptionRepository.

To avoid the mapping error 2048 , in my ExceptionRepository delete stored procedure, I have to include the two FKs ExceptionSourceId and InnerExceptionRepositoryId. Even though I don't really need to use these two keys in the delete stored procedure.



To conclude, if you want to map custom stored procedures to Entity functions:
1) you must map all Insert/Update/Delete functions.
2) include the FKs in the delete function if any defined in the source table.

Switched back from my other blog...

Ported all the old work related posts from the other blog I have.

I think I'll stick to this one from now on...

Catch unhandled exceptions in Windows Forms applications

The company I am working for has developed a Windows Forms application Framework. New applications are developed as plug-ins to this Framework. Our team is looking at catching all unhandled exceptions globally and log all these exceptions in a central repository.

After spending a little time on MSDN, I found this very helpful piece of information.

To handle all UI thread exceptions, the .Net Framework provides Application.ThreadException.To handle all non-UI thread exceptions, AppDomain.CurrentDomain.UnhandledException.

One thing worth noting is that UnhandledException won't prevent the application from terminating. But before the application terminates, you are given a chance to log the error or display some custom message to the users. And that's what I am looking for.

Here is the sample code from the MSDN article.

// Starts the application. 
[SecurityPermission(SecurityAction.Demand, Flags = SecurityPermissionFlag.ControlAppDomain)] 
public static void Main(string[] args)
{
  // Add the event handler for handling UI thread exceptions to the event.   
  Application.ThreadException += 
       new ThreadExceptionEventHandler(ErrorHandlerForm.Form1_UIThreadException);   

  // Set the unhandled exception mode to force all Windows Forms errors to go through our handler.
  Application.SetUnhandledExceptionMode(UnhandledExceptionMode.CatchException);

  // Add the event handler for handling non-UI thread exceptions to the event.
   AppDomain.CurrentDomain.UnhandledException +=
       new  UnhandledExceptionEventHandler(CurrentDomain_UnhandledException);

  // Runs the application.   
  Application.Run(new ErrorHandlerForm());
}

SSIS: Retrieve data from Recordset Destination

Some times when developing a SSIS package, it is handy to import data into an in-memory Recordset instead of a file or a database table.

However SSIS doesn't provide out of box support for reading data from a Recordset. A little workaround is required to make this working.

First, define a user variable of type "object".
Then map the varaible with the Recordset Destination object.



The last step is to drag a Script task onto the Control flow. Put the following code in the script and you are blessed with a DataTable object.

Dim dt As New Data.DataTable
Dim adapter As New Data.OleDb.OleDbDataAdapter
If Dts.Variables.Contains("RGRecordset") Then
adapter.Fill(dt, Dts.Variables("RGRecordset").Value)
End If

SSIS: Import multiple Excel files into database with Foreach Loop Container

I wanted to import more than 60 Excel files into a database table the other day. Foreach Loop Container really helped to perform the task.

I started with creating an Excel Connection Manager. I hard coded the file path to make the BI designer happy. Then in the Properties window of the Excel Connection Manager, I set the “ExcelFilePath” and/or “Server” to the value of the pre-defined user variable. I probably only need to set “ExcelFilePath”, but anyway I just wanted to make these values consistent.



Then I dragged a Foreach Loop container onto the Control Flow. From the Properties window of a Foreach Loop Container:
1) Foreach File Enumerator.
2) Hard coded the Folder path to please the designer, and set the file mask and the return value.
3) Added an Expression so that the value of “Directory” is actually set by a user variable I defined earlier. One thing that bugs me a litter is – it is called “Folder” on the screen but ‘Directory” in the Expressions list.



4) In the Variable Mappings section, I set the return value to the user variable.



Now I could drag a Data Flow Task into the Foreach Loop Container, and load data from an Excel file to a database table. Of course the Excel Data Source uses the Excel Connection Manager I created in the first step.