Wednesday, June 21, 2006

Asynchronous Invocation Using BackgroundWorker / Threading / C#

Sample Image

Introduction

When you have a function that takes a long time and it freezes the screen, the user gets nervous. They simply don't like to lose the control. It would give a friendly impression when they can still click on the form while the application is busy with getting the information, for example. When you start implementing asynchronous methods, you will wonder which technique is best suitable for your application. Would you use a Timer class or a Thread class or a BackgroundWorker? This article uses a BackgroundWorker class to show how easy it is, and where you need to pay attention when using it.

Background

I have tried to compare different methods of implementing Asynchronous Invocation in my weblog. With Microsoft Visual Studio 2005 , I discovered this class which is new to me.

Using the code

I have put extra comments everywhere possible, to make the sample a snippet of code you can use to start your own implementation. The code implements a very simple action, which is finding files in a directory to simulate the time consuming action. This is the place where you need to change. For the rest, the skeleton should be kept the same.

The background worker is able to send an event to the called thread to change the screen appearance. That is very easy, and I thought it would be easy for everyone to discover. But what if that is not enough and you would like to send other messages back to the caller? So I used BeginInvoke to invoke a call on the caller thread.

I have two delegates used from the sub thread, to call the method implemented in the main thread.

private delegate  void PaintMessage(TreeNode root,                                     string childText); private PaintMessage myAddNode = null;

The OnPaontMessage implements the adding of a newly found file into an existing (given) root. This is called when the sub-thread finds a new item.

private  delegate void EnableCancel(bool enabled); private EnableCancel myEnableCancel = null;

The second delegate is to indicate that the thread has been started and that it could be stopped. It is implemented by the main thread to enable the Cancel button.

Starting the action will be done by calling the RunWorkerAsync method on the worker.

this.backgroundWorker1.RunWorkerAsync(treeView1.Nodes[path]);

When a sub-folder is found, I send a notification to the main thread to create a node for the sub-folder. Then, I use that node to create a file node in it. But the dilemma is that I can not proceed with filling the file, if the main thread didn't get the chance to create the sub folder. Thus, I need to wait and let the main application take the chance to create it. One simple way to do this is the following loop:

// Create a folder node before proceeding IAsyncResult ar = BeginInvoke(myAddNode, new object[]                   { root, di.Name }); // Update the screen  // Wait until the folder has been created before // proceeding with the content of the folde while (!ar.IsCompleted) {     Application.DoEvents();     ar.AsyncWaitHandle.WaitOne(-1, false); }

First, I invoke the delegate and I keep the reference to it. I use the reference to see if it has been completed. And in the loop, I let the application take the chance and do its events. Meanwhile, I tell my sub-thread to wait a bit.

Tuesday, June 6, 2006

Searching tables and columns

I am working with some big databases having similar tables for different group.
It seems to be poluted by different parties and have no administrator. Some developers
put their application initials as a prefix of the table names and as a result we
got several Contact tables and several Items each with diffeent layouts.

When I am dealing with these tables I need to find everything to do with lets say
Code. I need to find out all the tables having a field like Tax_Code or TaxCode
or ItemCode etc. It might be even good to know if there are tables called like Meta_Code
or Item_Code etc.

To find these two things I have written two stored procedures to use as follows
:

sp_FindColumns ‘Code’
And
sp_FindTables ‘Code’

When I was using these two I realised that also some stored procedures
are dealing with changing a field internally. So I had to also write something to
search the definitions of the stored rocedures to find al those mentioning the field
in the code and I came up with the following procedure:

sp_FindInProcedure ‘Tax_Code’

.And below are the defenitions for them.


--------------------------------------------------------------------------------

if exists (select * from sysobjects
where id = object_id(N‘[dbo].[sp_FindColumns]’) and OBJECTPROPERTY(id,
N‘IsProcedure’) = 1)
drop procedure [dbo].[sp_FindColumns]
GO

Create Procedure sp_FindColumns
@sToSearch varchar(255)
AS
BEGIN
/************************************************************************************* *
* sp_FindColumns
*
* This stored procedure will look into all table collumns
* search the string which is given as the only parameter.
*
************************************************************************************* * By Asghar Panahy
* 12-jun-2006
**************************************************************************************/


SELECT SO.name + ‘.’ + Sc.name
FROM SysColumns Sc
Inner Join SysObjects SO
on SO.ID = SC.ID
Where SO.xtype=‘U’
And Sc.Name like ‘%’ + @sToSearch + ’%’

END

--------------------------------------------------------------------------------

if exists (select * from sysobjects
where id = object_id(N‘[dbo].[sp_FindTables]’) and OBJECTPROPERTY(id,
N‘IsProcedure’) = 1)
drop procedure [dbo].[sp_FindTables]
GO

Create Procedure sp_FindTables
@sToSearch varchar(255)
AS
BEGIN
/************************************************************************************* *
* sp_FindTables
*
* This stored procedure will look into all table names
* search the string which is given as the only parameter.
*
************************************************************************************* * By Asghar Panahy
* 12-jun-2006
*************************************************************************************
*/
SELECT Distinct So.Name
FROM SysColumns Sc
Inner Join SysObjects SO on SO.ID = SC.ID
Where So.xtype= ’U’
And SO.Name like ‘%’ + @sToSearch + ‘%’

END

if exists(select * from sysobjects where id = object_id (N‘[dbo].[sp_FindInProcedure]’) and
OBJECTPROPERTY(id, N‘IsProcedure’) = 1) color="#0000ff”
drop procedure [dbo].[sp_FindInProcedure]
GO

Create Procedure sp_FindInProcedure
@sToSearch
varchar(255)
AS

BEGIN
/************************************************************************************* *
* sp_FindInProcedure
*
* This stored procedure will look into all stored procedures defenitions to
* search the string which is given as the only parameter.
*
************************************************************************************* * By Asghar Panahy
* 12-jun-2006
**************************************************************************************/


SELECT so.name, sc.text
FROM syscomments sc
JOIN sysobjects so ON sc.id = so.id
WHERE so.xtype = ‘P’
And sc.Text like ’%’ + @sToSearch + ‘%’
Order By so.Name Asc

END

Column Description in SQL Server

The biggest challenge for me to learn de the business is to figure out how it hasbeen translated into a database. The more information I get out of the databasethe more familiar I feel myself with the business.
For applications living or quite some times and evolved by several developers, thisbecomes difficault if you can’t find out what those columns meant to keep. Speciallywhen they are keeping codes and flags which has not direct meaning for the businessbut rather they are stored as a meta data.
One of the handy implementation for such an information is the database itself.Lots of database administrators (and also developers) choose to keep the descriptionof a column somewhere reserved by the database. SQL-Server is not so convinientin this matter. Or maybe the system is while the user interface is not so open forit.
There is a so called undocumented stored procedure in the SQL-Server called sp_columns_rowset which shows you al you need and even more about atable. I am not going to talk about my experience with this undocumented storedprocedure which you can search the net and find some examples about it. Just mentioningit that it givs you something it calls DESCRIPTION of the field.
The question I have is where is this DESCRIPTION maintained? or even better, Howcan I fill this attribute? What should I do to get the descriptions of the columnsupdated with meaningfull sentence?