Arsalan Tamiz

Understanding Variable Scope in Anonymous Methods / Lambda Statements

2011-12-17T17:39:00.000+05:00

There are 2 goals of this article. 1) to understand the variable scope in anonymous methods and 2) to look at a real world scenario where we have used the anonymous methods effectively.

Probably there will be several articles about this topic out there. Like:

To understand this article you should have:

Strong Knowledge of: Object Oriented Programming
Little bit know how of: Anonymous Methods / Lambda Statements

Description:

Note that in this article I've used term anonymous method which means both normal anonymous methods or lambda statements.

While working with JavaScript we use anonymous methods as delegates to function most of the time, for example:

document.getElementById('button1').onclick = function() {
    alert('button 1 clicked');
}

The thing which confused me a lot is the variable scoping. So let's try to learn this thing intuitively.

Let's start with a code for example:

public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void InitializeButton(string sValue)
        {
            string sToAdd = "****";
            this.button1.Click += (sender, e) =>
            {
                MessageBox.Show(sToAdd + sValue + sToAdd);
            };
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.InitializeButton("Testing method");
        }
    }

I used to think that this anonymous method gets created somewhere in memory without being a part of any class. But then how this anonymous method is accessing the variables that are out of its scope like sValue and sToAdd in above example? This was the question which bothered me.

Later I tried to figure out if I don't have the anonymous method then how can I achieve this functionality i.e. how can we access the variables that are out of scope. Following solution came into my mind.

public partial class Form1 : Form
    {
        class tempclass
        {
            public string sValue;
            public string sToAdd;

            public void tempmethod1(object sender, EventArgs e)
            {
                MessageBox.Show(sToAdd + sValue + sToAdd);
            }
        }

        public Form1()
        {
            InitializeComponent();
        }

        private void InitializeButton(string sValue)
        {
            string sToAdd = "****";
            tempclass t = new tempclass();
            t.sValue = sValue;
            t.sToAdd = sToAdd;
            this.button1.Click += t.tempmethod1;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.InitializeButton("Testing method");
        }
    }

What I have done is just encapsulated the anonymous method and its variables in a temporary class. And guess what, compiler actually does the same thing when we use anonymous method and local variables.

Another workaround could be to move the method's local variables to class level for example:

    public partial class Form1 : Form
    {        
        public Form1()
        {
            InitializeComponent();
        }

        private string sToAdd;
        private string sValue;

        private void InitializeButton(string sValue)
        {
            this.sToAdd = "****";
            this.sValue = sValue;
            this.button1.Click += this.button1click;
        }

        private void button1click(object sender, EventArgs e)
        {
            MessageBox.Show(this.sToAdd + this.sValue + this.sToAdd);
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.InitializeButton("Testing method");
        }
    }

Above example does the same thing, but we are actually extracting the inner variables of method to outer class which ultimately will make our class a mess and managing such type of classes is horribly hard.

As you can see the anonymous method makes our code simplified and easy to manage. We can easily encapsulate our method's local variables and we don't need to define them on class level.

A Real World Scenario:

The given example is NOT a real world scenario and does NOT actually show us the benefit of using anonymous method. So now I'll share a real world scenario which I face.

I was actually creating a TCP Communication Layer, which later can be used in network projects like if you are creating a chat server, multiplayer game, etc.

In short if you are creating a network application, you typically have a server which listens for incoming connections and also you have one or more clients which connect to server. When a connection is established between server and client, client then sends requests to server and server responds back for that particular request. Normally this communication between clients and server is carried out via events, which means whenever client/server receives data, an event is triggered that Data has been received.

    public class Server
    {
        private void PacketReceived(PacketClass pkt)
        {
            switch (pkt.Type)
            {
                case "Login":
                    if (isValidLogin(pkt))
                        SendPacket(new LoginSuccessPacket(pkt.PacketID));
                    else
                        SendPacket(new LoginFailedPacket(pkt.PacketID));
                    break;
            }
        }
        private void SendPacket(PacketClass pkt)
        {
            // code to send packet
        }
    }

    public class Client
    {
        // this login method just sends login packet to server and does NOT
        // return any login success or failure message because we can only
        // determine the success or failure once we received the response
        // packet back from server
        public void Login(string ID, string pwd)
        {
            SendPacket(new LoginPacket(ID, pwd));
        }

        private void PacketReceived(PacketClass pkt)
        {
            switch (pkt.Type)
            {
                case "LoginSuccessPaket":
                    // successfully logged in
                    break;
                case "LoginFailedPacket":
                    // failed
                    break;
            }
        }

        private void SendPacket(PacketClass pkt)
        {
            pkt.PacketID = GetNewPacketID();
            // code to send packet
        }
    }

Suppose we have above 2 classes. In both classes PacketReceived() is executed whenever some data is received and similarly SendPacket() method is used to send packet to other end.

Now we have used Client Class in our Client application (a Windows Forms Application) and have created a Login Form which uses that class to send Login Request to server.

Whenever we hit "Login" button Login() method gets called and login packet is sent to server. At this point the main UI thread sends bytes and immediately returns back. Now UI thread is free and you can again hit the Login button or can even close this window. We have to write some logic here to disable the Login button and to prevent the Form to be closed.

What I want is that my UI thread should freeze on the point where I have called the Login() method of Client class. It should either through the exception or should send me the success code. In simple words I want the thread to be blocked until

I don't receive a response from server either success or failure
Or Timeout occurs in case server didn't respond in time

This is because I don't want my login process to be spread across events. I just want my Login() method to be simple and concrete and it should return either the error or success.

Modified version of Client Class

Note that in order to understand this solution you should have a strong grip on Threading and Locking

    public class Client
    {
        private int GetNewPacketID()
        {
            // code to generate new unique Packet ID
        }

        // now login method is concrete and it will return either error or success
        public void Login(string ID, string pwd)
        {
            PacketClass pktReturned;
            // here thread will be blocked until the response from server is NOT received or timeout expired
            pktReturned = SendPacketGetResponse(new LoginPacket(ID, pwd), 10000); // 10000 milliseconds = 10 seconds
            // code to perform operation on pktReturned
        }

        // this is actually an event handler which is executed
        // if a packet is received from server
        private void PacketReceived(PacketClass pkt)
        {
            Action<PacketClass> callback = null;

            lock (this.CallBackMethods)
            {
                // trying to extract out the callback if is associated with this packet id
                if (this.CallBackMethods.TryGetValue(pkt.PacketID, out callback))
                    // if found then we will remove it from dictionary
                    this.CallBackMethods.Remove(pkt.PacketID);
            }

            // if this is the response of some packet
            if (callback != null)
            {
                // call the callback
                callback(pkt);
            }
            else
            {
                // handle packet normally
            }
        }

        // this dictionary will hold the callback methods associated
        // with packet id. this dictionary will only be used
        // if we want to call our custom delegate on arrival of certain
        // packet id
        private Dictionary<int, Action<PacketClass>> CallBackMethods = new Dictionary<int, Action<PacketClass>>();

        // sends packet to server and does NOT wait for server response
        // immediately returns to the caller
        private void SendPacket(PacketClass pkt, Action<PacketClass> callback)
        {
            // attach a new unique packet id with the given packet
            pkt.PacketID = GetNewPacketID();

            // if callback method is provided then it means we want
            // to execute our callback method whenever response of
            // the given packet is received
            if (callback != null)
            {
                // add in the dictionary
                lock (this.CallBackMethods)
                {
                    this.CallBackMethods.Add(pkt.PacketID, callback);
                }
            }
            
            // code to send packet
        }

        // this method will block the calling thread until
        // 1) response is received from server
        // 2) or timeout expired
        private PacketClass SendPacketGetResponse(PacketClass pktToSend, int iTimeOut)
        {
            PacketClass pktReturn = null;

            using (System.Threading.AutoResetEvent are = new System.Threading.AutoResetEvent(false))
            {
                // send packet to server
                this.SendPacket(pktToSend, (pktReceived) =>
                    // anonymous method:
                {
                    // pktReturn variable has been declared in the outer method i.e.
                    // SendPacketGetResponse
                    pktReturn = pktReceived;
                    are.Set();  // Release the sendpacketgetresponse() method thread
                });

                // here the calling thread will be blocked
                // and once the response packet is received in our PacketReceived() method
                // it will call our anonymous method (written above) and our anonymous
                // method will call are.Set() which will resume this thread
                if (!are.WaitOne(iTimeOut))
                {
                    // since timeout has been expired therefore
                    // we are now removing the callback method from our dictionary
                    // even if server sends a response we are NOT interested 
                    lock (this.CallBackMethods)
                    {
                        this.CallBackMethods.Remove(pkt.PacketID);
                    }
                    throw new Exception("Timeout Expired");
                }
            }
            return pktReturn;
        }
    }

The core of this topic is the SendPacketGetResponse() method in above example.

The main goal which I've achieved here is that I have performed asynchronous operation synchronously. Plus the the returning variable is declared within the SendPacketGetResponse() method while the actual packet is received in PacketReceived() method but using Anonymous Method we encapsulated our whole process in a single method.

DataGridView control change Selection on Mouse Up

2010-01-28T10:35:00.004+05:00

Level: Intermediate

Knowledge Required:

DataGridView Control
Windows Forms

Description:
Recently, one of our friend "Luc" asked me,

Is there an "easy" way to change the selection behavior in a datagridview so that a click on a selected row does not toggle the selection as long as the mouse button is not released, without resorting to a user-defined control with inheritence and method overrides ?

So I was about to paste the whole code in comments but that was looking a mess. So I decided to publish a new post, after a very long time :).

The solution "is" simple. We just need to look what is the event sequence. So whenever we press mouse button on a cell or row of DataGridView control, events trigger in this way,

MouseDown
SelectionChanged
MouseUp

When MouseDown event occurs Selection does NOT change. So in this event handler we can save the current row in some variable and then in SelectionChanged event handler we can select previously saved row, in this way user will experience that selection never changed.

And here is the code,

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.DataGridView1.Columns.Add("Column1", "Column1")
        Me.DataGridView1.Columns.Add("Column2", "Column2")
        Me.DataGridView1.Columns.Add("Column3", "Column3")

        Me.DataGridView1.Rows.Add("laksjdf", "alksjfsdf", "aksldjf")
        Me.DataGridView1.Rows.Add("laksjdf", "alksjfsdf", "aksldjf")
        Me.DataGridView1.Rows.Add("laksjdf", "alksjfsdf", "aksldjf")
        Me.DataGridView1.Rows.Add("laksjdf", "alksjfsdf", "aksldjf")
        Me.DataGridView1.Rows.Add("laksjdf", "alksjfsdf", "aksldjf")
        Me.DataGridView1.Rows.Add("laksjdf", "alksjfsdf", "aksldjf")
        Me.DataGridView1.Rows.Add("laksjdf", "alksjfsdf", "aksldjf")

        ' ***************************************
        ' IMPORTANT: MultiSelect property of DataGridView control should be False
        '            The result maybe same but if we set it to true then you 
        '            may experience the flickering
        ' ***************************************
        Me.DataGridView1.MultiSelect = False
    End Sub

    Private _MouseDown As Boolean
    Private _PreviousSelectedCell As DataGridViewCell
    Private _NewSelectedCell As DataGridViewCell

    Private Sub DataGridView1_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseDown
        ' if left button is clicked
        If e.Button = Windows.Forms.MouseButtons.Left Then
            Me._MouseDown = True    ' we will mark a flag that mouse is down
            ' and will note the row before the selection changed
            Me._PreviousSelectedCell = Me.DataGridView1.CurrentCell
        End If
    End Sub

    Private Sub DataGridView1_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.SelectionChanged
        If Me._MouseDown Then       ' if this event is triggerd after the mouse down
            ' we will first note the new row on which mouse was clicked
            Me._NewSelectedCell = Me.DataGridView1.CurrentCell
            ' then change the selection back to the one before mouse was down
            Me.DataGridView1.CurrentCell = Me._PreviousSelectedCell
        End If
    End Sub

    Private Sub DataGridView1_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseUp
        If Me._MouseDown Then       ' if this event is triggered after the mouse down
            ' we will set the selection to the new row which was clicked
            Me.DataGridView1.CurrentCell = Me._NewSelectedCell
            Me._MouseDown = False   ' reset the flag
        End If
    End Sub

End Class

Useful Links for SQL Server

2009-09-24T12:24:00.003+06:00

Stuck while manually inserting values in an Identity column? Here is what you need to do How to Insert Values into an Identity Column in SQL Server

Here is a great topic for beginners to learn about the Execution Plans in SQL Server Beginners topic for understanding Execution Plans in SQL Server

Is your SQL Server eating your CPU too much? Don't know which Query or Stored Procedure is behind this? A must have tool for SQL Server Making the Most Out of the SQL Server 2005 Performance Dashboard

Are you an advanced SQL Server 2005 Programmer? Do you know about CROSS APPLY? Using CROSS APPLY in SQL Server 2005

Importing IIS Web Log into SQL Server 2005

2009-05-07T16:15:00.003+06:00

Today I was trying to import the IIS log of my Web Server into SQL Server 2005 Database (for further analyses). So first I started googling about it and found this link,

How To Use SQL Server to Analyze Web Logs

Very useful article BUT this one is old maybe because the table have different schema. By the way I have IIS 6.0 on my web server. So I did some workarounds and successfully imported the Log. Now I am posting here in case someone might be trying to do the same thing.

Step #1: Create a table in Database

CREATE TABLE [dbo].[IISLog](
 [date] [datetime] NULL,
 [time] [datetime] NULL,
 [site-name] [varchar](255) NULL,
 [s-computername] [varchar](255) NULL,
 [s-ip] [varchar](50) NULL,
 [cs-uri-stem] [varchar](255) NULL,
 [cs-uri-query] [varchar](2048) NULL,
 [c-ip] [varchar](50) NULL,
 [cs(User-Agent)] [varchar](2048) NULL,
 [cs(Cookie)] [varchar](2048) NULL,
 [cs(Referer)] [varchar](2048) NULL,
 [sc-status] [int] NULL,
 [sc-bytes] [int] NULL,
 [cs-bytes] [int] NULL,
 [time-taken] [int] NULL
)

Step #2: Prepare the Log file (since it contains some description lines on top)

So this is a tricky step. As the Log file contains some description lines on top starting with "#" sign. Therefore SQL Server will NOT be able to import it. One more thing is that these log files can be large (or very large). The Log file I had was of size aprox. 216 MB. So obviously we cannot open it in NotePad etc.

The same article provides a small utility which removes the line, but I think there is bug in this utility, cause it is limiting file upto 43 MB. So I decided to write my own version.

PrepIISLogFileForImport.zip

As I started to create this application for IIS Log import but then it ends up with a generic utility. Which actually displays the text file content and have an option to skip number of lines from start. Therefore we can use it as,

C:\>PrepIISLogFileForImport C:\LogFile.Log skip=4 >newlogfile.txt

Step #3: Bulk import the Log file into SQL Server Table

Hence the final step is to import the log file in the same table. Which can be done by,

BULK INSERT [dbo].[IISLog] FROM 'C:\newlogfile.txt'
WITH (
    FIELDTERMINATOR = ' ',
    ROWTERMINATOR = '\n'
)

SQL Server Date Time Useful Functions

2009-03-09T16:24:00.009+05:00

Here are some SQL Server Date Time Useful functions.

DateCreate - Creates Date through Year, Month and Day
DateToStrShort - Accepts Date and returns as '1-Jan-2009'
DateGetDaysInMonth - Accepts Month and Year and returns number of days in month
DateGetTimeOnly - Accepts date and returns time as '10:33 PM'
DateGetDateOnly - Accepts Date and convert it into Date only i.e. removes the Time Part
DateGetMonthFirstDate - Gets the first date of Month
DateGetMonthLastDate - Gets the last date of Month
DateGetMonthName - Gets the Month Name

Review the functions below or download the SQL file.

-- =============================================
-- Author:        Arsalan Tamiz
-- Description:   This function accepts Year, Month and Day
--                and returns Date
-- =============================================
CREATE FUNCTION [DateCreate]
(
    @Year int,
    @Month int,
    @Day int
)
RETURNS datetime
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result datetime;

    -- Add the T-SQL statements to compute the return value here
    SET @Result = CAST    (
                            CAST(@Year AS varchar(10)) + '-' +
                            CAST(@Month AS varchar(2)) + '-' + 
                            CAST(@Day AS varchar(2))
                            AS datetime
                          );

    -- Return the result of the function
    RETURN @Result;
END

-- =============================================
-- Author:        Arsalan Tamiz
-- Description:   This function converts the given
--                date in to string. Useful function
--                for displaying date as '10-Jan-2009' for example
-- =============================================
CREATE FUNCTION [DateToStrShort] 
(
    -- Add the parameters for the function here
    @DateToConvert datetime
)
RETURNS varchar(50)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varchar(50)

    SET @Result = CAST(Day(@DateToConvert) AS varchar(50)) + '-' + 
                    Left(DateName(m, @DateToConvert), 3) + '-' + 
                    CAST(Year(@DateToConvert) AS varchar(50))

    -- Return the result of the function
    RETURN @Result;
END

-- =============================================
-- Author:        unknown (I copied it from internet)
-- Description:   Returns the Days in a Month
--                parameters = Year and Month
-- =============================================
CREATE FUNCTION [DateGetDaysInMonth] (@Year int, @Month int)
RETURNS INT
AS
BEGIN

    RETURN CASE WHEN @Month IN (1, 3, 5, 7, 8, 10, 12) THEN 31
                WHEN @Month IN (4, 6, 9, 11) THEN 30
                ELSE CASE WHEN (@Year % 4    = 0 AND
                                @Year % 100 != 0) OR
                               (@Year % 400  = 0)
                          THEN 29
                          ELSE 28
                     END
           END

END

-- =============================================
-- Author:  Arsalan Tamiz
-- Description: Gets the Time from Date
-- =============================================
CREATE FUNCTION [DateGetTimeOnly] 
(
 -- Add the parameters for the function here
 @DateToFormat datetime
)
RETURNS varchar(100)
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result varchar(100);
 
 DECLARE @H varchar(3);
 DECLARE @Hour int;
 DECLARE @M varchar(3); 

 SET @Hour = DatePart(hh, @DateToFormat);
 SET @M = DateName(mi, @DateToFormat);
 If @M = '0' SET @M = '00';

 -- Add the T-SQL statements to compute the return value here
 SET @H = CAST(CASE WHEN @Hour > 12 THEN @Hour - 12 ELSE CASE WHEN @Hour = 0 THEN 12 ELSE @Hour END END AS varchar);
 
 SET @Result = @H + ':' + @M + ' ' + CASE WHEN @Hour >= 12 THEN 'PM' ELSE 'AM' END;
 
 -- Return the result of the function
 RETURN @Result;
END

-- =============================================
-- Author:  Arsalan Tamiz
-- Description: Returns Date only from given Date
--    That is removes the Time Part
--    so that we can compare two dates
-- =============================================
CREATE FUNCTION [DateGetDateOnly] 
(
 -- Add the parameters for the function here
 @DateToConvert datetime
)
RETURNS datetime
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result datetime;

 -- Add the T-SQL statements to compute the return value here
 SET @Result = CAST(CONVERT(varchar(100), @DateToConvert, 112) AS datetime);

 -- Return the result of the function
 RETURN @Result;
END

-- =============================================
-- Author:  Arsalan Tamiz
-- Description: Gets the first date of month
-- =============================================
CREATE FUNCTION [DateGetMonthFirstDate] 
(
 -- Add the parameters for the function here
 @Month int
)
RETURNS datetime
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result datetime;
 DECLARE @Year int;

 SET @Year = Year(GetDate());

 SET @Result = CAST (
    CAST(@Year AS varchar(10)) + '-' + 
    CAST(@Month AS varchar(2)) + '-1' AS datetime
    );

 -- Return the result of the function
 RETURN @Result;
END

-- =============================================
-- Author:  Arsalan Tamiz
-- Description: Returns last date of month
-- ***********************************************
-- IMPORTANT: This function depends on [dbo].[DateGetDaysInMonth]() function
--    which can be found above
-- ***********************************************
-- =============================================
CREATE FUNCTION [DateGetMonthLastDate] 
(
 -- Add the parameters for the function here
 @Month int
)
RETURNS datetime
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result datetime;
 DECLARE @Year int;
 
 SET @Year = Year(GetDate());
 
 SET @Result = CAST (
    CAST(@Year AS varchar(10)) + '-' + 
    CAST(@Month AS varchar(2)) + '-' +
    CAST([dbo].[DateGetDaysInMonth](@Year, @Month) AS varchar(2)) +
    AS datetime
    );

 -- Return the result of the function
 RETURN @Result;
END

-- =============================================
-- Author:  Arsalan Tamiz
-- Description: This function Retuns the name of month
-- =============================================
CREATE FUNCTION [DateGetMonthName] 
(
 -- Add the parameters for the function here
 @Month int
)
RETURNS varchar(100)
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result varchar(100)
 DECLARE @d datetime;

 SET @d = CAST('2009-' + CAST(@Month AS varchar(2)) + '-1' AS datetime);

 -- Add the T-SQL statements to compute the return value here
 SET @Result = DateName(mm, @d);

 -- Return the result of the function
 RETURN @Result;
END

Notification Bar Control For Windows Forms (Win Forms)

2009-02-14T11:01:00.004+05:00

Here is simple Notification Bar control (like we see in Internet Explorer). Gives application a good look. Note that I haven't done much thing, you can customize is further if you like. It supports blinking too.

Usage:
ShowNotification() method to display the Notification.
BlinkTimes property to set the Number of Times to blink.

Download Source Code:
Notificationbar.zip

DataGridView Custom Cell ToolTip

2009-01-26T12:44:00.003+05:00

Level: Intermediate

Knowledge Required:

DataGridView Control
Windows Forms

Description:
We have a built-in ToolTip for each Cell in DataGridView control. Which is displayed when the text is long and cannot be displayed completely in Cell. But my requirement was to create a custom ToolTip (as displayed in above image).

There were 2 main requirements,

1) ToolTip should be displayed permanently that is, it should NOT be disappeared automatically after sometime.
2) I want to have a fancy look of my ToolTip (again see the image).

For this purpose we can also use the .net's built-in ToolTip control. Which can have a little fancy look by setting some of its properties like IsBubble, ToolTipIcon and ToolTipTitle. Also the ToolTip control can be more customized by using OwnerDraw mode. But still I would like to use my own ToolTip window. Because to me, this is more easier to do.

Simply I implemented it by creating a new window for ToolTip and I show this window when mouse enters in a cell. But to make it more user friendly, I have used a Timer control. So that whenever mouse enters in a Cell I start timer and in Timer's Tick Event Handler, I display the ToolTip window.

Few things to be considered here,

1) DataGridView's ShowCellToolTips property should be set to False
2) Decide where to display the ToolTip window

So in my case I decided to display the ToolTip window just on the Cell. OK this is NOT just straight. We first need to get the Cell's actual Coordinate in DataGridView by executing DataGridView's GetCellDisplayRectangle() function. Then we need to convert these coordinates into Screen's Coordinates by calling the DataGridView's PointToScreen() function.

Please note that, to display our own custom ToolTip window on our given position we need to set its property StartPosition = Manual, which I have discussed in my earlier post Setting Window / Form Position Programmatically

Another thing I have added in the ToolTip window is the Close Button. Which is actually a NON Focusable button as I have discussed my previous post NOT Focusable / NOT Selectable Button

Download Source:
DataGridViewCustomCellToolTip.zip

NOT Focusable / NOT Selectable Button

2009-01-23T15:10:00.006+05:00

Level: Intermediate

Knowledge Required:
User Controls

Description:
Few days back I was creating a user control like ComboBox. In ComboBox we have a Drop Down button at the right side, which cannot have focus. This button can only be clicked by mouse to open the Drop Down portion (Alt+Down is the shortcut).

Similarly my control also have a button on right. I decided to make that button just like the Drop Down button of ComboBox. For this purpose I added a plain Class in my project and then put the following Code,

Public Class NotSelectableButton
    Inherits Button

    Public Sub New()
        MyBase.New()
        ' following line will make this button Not Focusable
        SetStyle(ControlStyles.Selectable, False)
    End Sub
End Class

FTP File Upload Error

2009-01-17T12:28:00.005+05:00

It seems that our Network Administrator has changed some settings because my previous file uploading code is NOT working. And giving the following exception:

The server returned an address in response to the PASV command that is different than the address to which the FTP connection was made.

I was using the VB.net My namespace,

My.Computer.Network.UploadFile("C:\SomeFile.txt", "ftp://ftpsite/somefile.txt", "userid", "pwd")

So I Googled about that error and found some dicussion on MSDN forums.

Then I modified and created my own upload function as,

Private Sub UploadFile(ByVal sDestination As String, ByVal sSource As String, ByVal sUserID As String, ByVal sPassword As String)
    Dim i As System.Net.FtpWebRequest
    Dim us As System.IO.Stream
    Dim filebytes As Byte()

    ' create the FTP Web Request
    i = System.Net.WebRequest.Create(sDestination)
    ' set credentials
    i.Credentials = New System.Net.NetworkCredential(sUserID, sPassword)
    ' method = Upload File
    i.Method = System.Net.WebRequestMethods.Ftp.UploadFile
    i.UsePassive = False
    i.Proxy = Nothing
    ' get the Request Stream
    us = i.GetRequestStream()
    ' get the file bytes from source
    filebytes = My.Computer.FileSystem.ReadAllBytes(sSource)
    ' write the bytes in stream
    us.Write(filebytes, 0, filebytes.Length)
    ' close the stream
    us.Close()
End Sub

Warning! Above function will read all the bytes (in one go) from file. Which is NOT a good practice for larger files.

Another way of accessing a file on Web Server (Javascript/ASP.net)

2009-01-02T11:14:00.006+05:00

Few days back while working on back-end of a Web Portal. I was trying to create an Anchor Tag (<a>) in such a way that a log should also be created whenever that anchor tag is clicked by user.

So there are couple of techniques.

Approach #1:
On Click: Post back to server create the log and redirect to the URL.

Approach #2:
Use AJAX

So I used the 2nd approach. For this purpose I first created a Javascript function as,

First Try:

function createlog(param1, param2) {
 var xmlHttp;
 try
  {  // Firefox, Opera 8.0+, Safari
  xmlHttp=new XMLHttpRequest();  
 }
 catch (e)
 {  // Internet Explorer
  try
  {
   xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");    
  }
  catch (e)
      {
   try
   {
    xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
   }
       catch (e)
   {
    alert("Your browser does not support AJAX!");
    return false;
   }
  }
 }
 try
 {
  var u;
  u = "createlog.aspx?param1=" + param1 + "&param2=" + param2;
  xmlHttp.open("POST", u, false);
  xmlHttp.send(null);
  return true;
 }
 catch (e)
 {
  return false;
 }
}

So this is an example function. Which will create a log by executing a "createlog.aspx" file on server which accepts 2 parameters in QueryString. The above function is simply using the XMLHttpRequest() object for AJAX call.

Then we can use this function in an anchor tag as,

<a href="http://www.domain.com" onclick="return createlog(1, 2);">www.domain.com</a>

When user will click on the link the createlog() function will be executed first. Which then will create the log on server and then redirect to the link.

Second Try:
Secondly I looked in the Google's Result Page. Because they are doing this also. Whenever we click on a result, Google redirects to that URL directly (without posting back) but also keeps the track of clicking. I looked into their Javascript and found a very interesting thing. And hence modified my Javascript function to this,

function createlog(param1, param2) {
 var u = "createlog.aspx?param1=" + param1 + "&param2=" + param2;
 (new Image).src = u;
 return true;
}

Because my requirement is to just execute the "createlog.aspx" file on the server. I do NOT need the returned value. That is why XMLHttpRequest() is NOT critically required in this scenario. On the other hand (new Image).src approach is lot simpler.

What happens is that the browser creates an Image object and go to the "source" (in this case my createlog.aspx) which actually do NOT return any data (i.e. image bytes). But by going to that URL it actually is executed on server. And the Image object is then discarded at the end of function (I guess).

One more thing I found was Google is using "onmousedown" event instead of "onclick". I don't know exactly the benefits but I have experience that "onclick" event is NOT fired (on some browsers) when we right click and open the link in new window or tab. "onmousedown" event is triggered each time we click on link even if we right click on it. So it seems that this is NOT a good approach to use "onmousedown". But I have checked that whenever we right/left click on the link for the first time, the file "createlog.aspx" is executed on server. But it does NOT execute, if we click again. I think this is due to cache maybe.

Image to Byte Array and Byte Array to Image

2008-12-23T12:14:00.004+05:00

Level: Intermediate

Knowledge Required:
Image Class

Description:
Here are simple functions that convert an Image into Byte Array and Byte Array into Image. An image is required to be converted into bytes array when we save it in Database, hence on reading the bytes from database, it needs to be converted back into image.

Private Function BytesToImage(ByVal ImageBytes() As Byte) As Image
    Dim imgNew As Image
    Dim memImage As New System.IO.MemoryStream(ImageBytes)
    imgNew = Image.FromStream(memImage)
    Return imgNew
End Function

Private Function ImageToBytes(ByVal Image As Image) As Byte()
    Dim memImage As New System.IO.MemoryStream
    Dim bytImage() As Byte

    Image.Save(memImage, Image.RawFormat)
    bytImage = memImage.GetBuffer()

    Return bytImage
End Function

Java Script Function to Show Flash Banner

2008-12-18T22:44:00.003+05:00

Usually I develop Desktop Application. But sometimes I also get involve in Web Development. So we were developing a Website in which we were trying to create a Flash Banner rotator. To accomplish this there are several techniques, but I created the following Java Script function for our Web Developer.

function showflashbanner(htmlobjectid, flashfilename, width, height) {
    var script;
    script = "<object classid='clsid:D27CDB6E-AE6D-11cf-96B8-444553540000' " +
             "codebase='http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,28,0' " +
             "width='" + width + "' height='" + height + "' \>" +
             "<param name='movie' " +
             "value='" + flashfilename + "' /\>" +
             "<param name='quality' value='high' /\>" + 
             "<embed src='" + flashfilename + "' " + 
             "quality='high' " +
             "pluginspage='http://www.adobe.com/shockwave/download/download.cgi?P1_Prod_Version=ShockwaveFlash' " +
             "type='application/x-shockwave-flash'" +
             "width='" + width + "'" +
             "height='" + height + "'\>'" +
             "</embed\>" +
             "</object\>";
    document.getElementById(htmlobjectid).innerHTML = script;
}

Example:

<body onload="showflashbanner('bannercontainer', 'http://www.mywebsite.com/banners/banner1.swf', 720, 100)">
<div id="bannercontainer">Loading...</div>
</body>

As you can see the above example we have used "div" as a Banner container. Then we are passing this div's id in our function so the Flash File will be loaded in it.

The Web developer then used this function by creating an Array of Flash Files. He also created another function in which he used setTimeout function. So the function calls itself after some time with new Flash File.

Setting Window / Form Position Programmatically

2008-11-18T14:21:00.003+05:00

Level: Beginner

Knowledge Required:
Windows Forms

Description:
While working with Windows Forms we sometimes require to set the Window/Form Position manually before we show it. Consider the following code:

Dim frmNew as Form2
frmNew = New Form2()
frmNew.Location = New Point(10, 10)
frmNew.Show()

If we execute the above code, it is NOT sure that window will be displayed at 10, 10. Because we haven't set the StartPosition property. By default this property is equal to WindowsDefaultLocation that is why whenever we display the Form, Windows Operating system choose where to put it. And if we change Location after the frmNew.Show(), then our location will set.

To set the Location programmatically we should first set the StartPosition property as,

Dim frmNew as Form2
frmNew = New Form2()
frmNew.StartPosition = FormStartPosition.Manual
frmNew.Location = New Point(10, 10)
frmNew.Show()

HTML Anchor Tag ( Link) with NO Redirection

2008-10-16T11:52:00.002+06:00

Although this Blog is purely for Visual Basic Windows Forms Application Development. But I think we can discuss other stuff too.

So meanwhile working with HTML, I was trying to create an Anchor with a href defined but the link should NOT work. Click the following link,

Google

As you can see the above link should redirect this page to Google, but it is NOT redirecting it. OK now try to open the link in a new Tab (if you are using a borwser which has tabbed browsing support, i.e. Ctrl+Click). This time it should work. Here is the HTML,

<a href="http://www.google.com" onclick="return false;">Google</a>

As you can see in above "onclick" event we have executed a little javascript which returns false. This is the statement which is suppressing the default behaviour of anchor. Some sites handle it like this,

Google

<a href="javascript:void(0);" onclick="alert('You Clicked the Link');">Google</a>

Binding DataGridView ComboBox Column

2008-09-14T13:27:00.006+06:00

Level: Beginner

Knowledge Required:

Data Binding
DataGridView

Description:
We use DataGridView control lots of times while developing a Data Driven Application. We usually bind the DataGridView control with some BindingSource. Also we can bind the DataGridView's ComboBox Column to some other BindingSource. That is DataGridView has different DataSource and it's ComboBox column has a different. Consider the following scenario,

We have following Typed DataSet,

OrderDataSet

First we will create a simple User Interface,

As you can see in the above figure, the DataGridView control is bind with OrderDetail Table. Note that the Product_ID column is displaying ID which is NOT a friendly approach. Instead Product Name should be displayed here. We can use the DataGridView's ComboBox Column here, in which we will populate all the products.

The main point to be focused here is that, we will be binding the DataGridView control with the same OrderDetail DataTable, in which there is no Product_Name column. But on front-end the Product_ID column will be set to ComboBox Column in which we will populate the Products' Name.

Since we need to populate the ComboBox with Products' Name. Therefore we will add another Typed DataSet and BindingSource to our Form,

We have bind our ProductBindingSource with ProductDataSet which contains Product DataTable. We will fill this table in Form_Load event Handler.

IMPORTANT: This table needs to be filled before we fill the OrderDataSet OR at-least before the OrderDetail DataGridView is displayed

Now we need to setup the Product_ID column. First set the ColumnType of Product_ID column to DataGridViewComboBoxColumn and then set the properties as,

DataPropertyName = Product_ID
DataSource = ProductBindingSource
DisplayMember = Product_Name
ValueMember = Product_ID

DataPropertyName: tells that Product_ID column of OrderDetail DataTable should be updated
DataSource: is the source from where the list of Products will be taken, in this case the ProductBindingSource which is actually bind with Product DataTable
DisplayMember: Column of Product DataTable which should be used to display in DataGridView control
ValueMember: Column of Product DataTable which should be used to set the Value in DataGridView which ultemately will send the value in OrderDetail DataTable

Download:
BindingDataGridViewComboBox.zip

How to handle Overlapping of Large Text Fields in Crystal Reports

2008-09-11T10:36:00.003+06:00

Level: Intermediate

Knowledge Required:
Crystal Reports

Description:
While working in Crystal Reports, we sometimes face a scenario when we need to put a Large Text Field (which can grow upto multiple lines). The issue comes if we try to put another Field after this Text Field. Example:

As you can see in above I have put to Formula Fields, both will contain Large Text which may expand. So here is the preview,

Therefore to solve this issue we can create multiple sub-sections in the same section. This can be achieved by Section Expert.

Now we will put the Fields in different sub-sections as,

And here is the preview again,

Note that in Crystal Reports Sections automatically expand, that is why when the first Text Field expands, the section also expands itself, therefore ultimately the next field renders properly.

Using DataGridView CheckBox Column as RadioButton (OptionButton)

2008-09-04T13:18:00.004+06:00

Level: Beginner

Knowledge Required:

DataGridView Control
Data Binding

Description:
In this post we shall see that how we can transform DataGridView control’s CheckBox column into Radio Button (option button).

I have done 2 main things,

Created a back-end logic when user clicks on CheckBox Column so only one CheckBox should be checked at a time
Change the look of CheckBox column so it looks a Radio Button Column

So to understand the first one, consider a DataGridView control as shown in the above figure. This DataGridView control is actually bind with a DataSet for example,

As you can see there are 2 columns. The IsSelected column is actually a Boolean column which is normally rendered as CheckBox in DataGridView control. What we will be doing is that we first set our DataGridView to Read Only i.e.,

AllowUserToAddRows = False
AllowUserToDeleteRows = False
ReadOnly = True

We are making our DataGridView control Read Only because it is easier to set the CheckBox checked or unchecked programmatically otherwise DataGridView control itself will be interfering and will create problems and complexities for us.

OK now whenever user clicks on CheckBox we will be performing our custom operation. To do this we will use the DataGridView’s CellContentClick event. Here is the code,

Private Sub ShutDownOptionsDataGridView_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles ShutDownOptionsDataGridView.CellContentClick
    If e.ColumnIndex = Me.columnIsSelected.Index Then
        Dim drv As DataRowView
        Dim rowShutDownOption As ShutdownOptionDataSet.ShutDownOptionsRow
        ' in this event handler we know that which DataGridView's row is clicked
        ' so we are going to extract out the actual DataTable's row which is
        ' bind with this DataGridView's Row
        drv = CType(Me.ShutDownOptionsDataGridView.Rows(e.RowIndex).DataBoundItem, DataRowView)
        ' get the DataTable's row
        rowShutDownOption = CType(drv.Row, ShutdownOptionDataSet.ShutDownOptionsRow)

        ' get the row which is currently selected
        Dim rowCurrentlySelected() As ShutdownOptionDataSet.ShutDownOptionsRow
        rowCurrentlySelected = Me.ShutdownOptionDataSet.ShutDownOptions.Select("IsSelected=True")
        ' if some row found then make it de-selected
        If rowCurrentlySelected.Length > 0 Then
            rowCurrentlySelected(0).IsSelected = False
        End If
            ' ok now select the row which is clicked
        rowShutDownOption.IsSelected = True
    End If
End Sub

What we do is first get the row in which IsSelected=True and we make that row IsSelected=False. Then we set the row which is clicked as IsSelected=True.

Next thing is to change the look of CheckBox to OptionButton / RadioButton. For this purpose we will be using DataGridView’s CellPainting event. Here is the code,

Private Sub ShutDownOptionsDataGridView_CellPainting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles ShutDownOptionsDataGridView.CellPainting
    If e.ColumnIndex = Me.columnIsSelected.Index AndAlso _
        e.RowIndex >= 0 Then
        e.PaintBackground(e.ClipBounds, True)

        Dim rectRadioButton As Rectangle

        rectRadioButton.Width = 14
        rectRadioButton.Height = 14
        rectRadioButton.X = e.CellBounds.X + (e.CellBounds.Width - rectRadioButton.Width) / 2
        rectRadioButton.Y = e.CellBounds.Y + (e.CellBounds.Height - rectRadioButton.Height) / 2

        If IsDBNull(e.Value) OrElse e.Value = False Then
            ControlPaint.DrawRadioButton(e.Graphics, rectRadioButton, ButtonState.Normal)
        Else
            ControlPaint.DrawRadioButton(e.Graphics, rectRadioButton, ButtonState.Checked)
        End If

        e.Paint(e.ClipBounds, DataGridViewPaintParts.Focus)

        e.Handled = True
    End If
End Sub

As you can see we have used the ControlPaint class to draw the RadioButton / OptionButton.

Download:
DGVCheckBoxAsRadioButton.zip

Asynchronous Data Loading using TableAdapter with Cancel Feature

2008-09-03T11:42:00.004+06:00

Level: Intermediate

Knowledge Required:

BackgroundWorker Control
DataGridView Control
Data Binding
TableAdapter

Description:
In my previous post

Implement SQLCommand Cancel with Threading

I have discussed how to implement the SQLCommand Cancel using Threading. In this post we shall see, how we can implement the same feature in a TableAdapter.

As we know that TableAdapter is used as a middle level man standing between Physical Database and Typed DataSet. Which gets the data from Physical Database and loads into Typed DataSet. Sometimes this loading of data takes time (for example due to complex query). Therefore we can provide the user an ability to cancel the on going command by clicking on Cancel Button.

I have created a Test Database (TestDB) and have added a table (tbl_Student). Also I have added a Stored Procedure (GetStudent). Take a look at this Stored Procedure:

CREATE PROCEDURE [dbo].[GetStudent] 
AS
BEGIN
    SET NOCOUNT ON;

    -- here we are simulating a very long query
    -- which takes 3 seconds to complete
    WAITFOR DELAY '00:00:03';

    SELECT *
    FROM tbl_Student;
END

In this procedure I have added a WAITFOR DELAY SQL Statement. Which simulates a long query that takes time 3 seconds. We will call this procedure from our TableAdapter to fill the DataTable.

Now we will add the SQLCommand Cancel stuff in our TableAdapter. This can be achieved by creating a Partial Class of TableAdapter,

Namespace TestDataSetTableAdapters
    Partial Class StudentTableAdapter
        Public Sub CancelSelect()
            Me.Adapter.SelectCommand.Cancel()
        End Sub
    End Class
End Namespace

As you see I have given the name of method CancelSelect which actually cancels the SelectCommand of Adapter. Note that I haven’t implemented any checking here (for example is command executing or NOT). This is upto the user of TableAdapter class. He/She will be responsible to check before executing this method.

Here we have done with our TableAdapter. Now next thing is to implement the loading in the different Thread. For this purpose I have used the BackgroundWorker control which is quite simple. The overall idea is,

Create 2 buttons

LoadButton
CancelButton

When LoadButton is clicked

Execute the BackgroundWorker

In BackgroundWorker DoWork Event Handler

Note somewhere that Execution is started
Execute the Fill() Method of TableAdapter

When CancelButton is clicked

Check if execution is started then

Execute the CancelSelect() method of TableAdapter

Update Sep-4-2008:
Here is the code of Form1

Public Class Form1

    ' ErrorText will contain the error information if occurred
    ' while loading data in different thread
    Private _ErrorText As String
    ' this boolean variable will be true when command is executed
    Private _CommandExecuted As Boolean

    Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
        ' first we will disable the Load button so it should NOT be clicked again
        Me.btnLoad.Enabled = False
        ' then we will enable the Cancel button so the command can be canceled
        Me.btnCancel.Enabled = True
        ' set the status text
        Call Me.SetStatusText("Loading...")

        ' clear the Error Text
        Me._ErrorText = ""

        ' here we have first clear the currently loaded data
        ' from DataSet, because the Adapter will fill the DataSet
        ' in different thread, therefore it will create problems
        ' while displaying the same data (which is being modified
        ' in different thread) in DataGridView
        Me.TestDataSet.Clear()

        ' start the asynchronous task
        Me.BackgroundWorker1.RunWorkerAsync()
    End Sub

    Private Sub BackgroundWorker1_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Try
            ' first set the executed flag
            Me._CommandExecuted = True
            ' execute the command
            Me.StudentTableAdapter.Fill(Me.TestDataSet.Student)
        Catch ex As Exception
            _ErrorText = ex.Message
        End Try
    End Sub

    Private Sub BackgroundWorker1_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
        ' ok our data is loaded now
        ' so enable the Load button
        Me.btnLoad.Enabled = True
        ' disable the Cancel button
        Me.btnCancel.Enabled = False
        ' if NO error was occurred in thread
        If Me._ErrorText = "" Then
            Me.SetStatusText(Me.TestDataSet.Student.Count & " Records loaded")
        Else ' else (some error was occurred)
            Me.SetStatusText(Me._ErrorText)
        End If
        ' since data is changed in different thread
        ' therefore by reseting the Bindings the DataGridView
        ' will refresh itself and will display all the data
        Me.StudentBindingSource.ResetBindings(False)
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        ' if command is executed then
        If Me._CommandExecuted Then
            ' cancel it
            Me.StudentTableAdapter.CancelSelect()
        End If
    End Sub


    Private Sub SetStatusText(ByVal sText As String)
        Me.lblStatus.Text = sText
    End Sub
End Class

Download:
TableAdapterWithCancel.zip

Note: The zip file contains TestDBScript.sql T-SQL Script which will,
1) Create a database "TestDB"
2) Create a table "tbl_Student" in that database
3) Create a Stored Procedure "GetStudent"
4) Put some data in tbl_Student

Please execute this script before running the project.

Query Timeout in ADO.Net but NOT in SQL Server Management Studio

2008-08-12T13:02:00.000+06:00

I was facing this issue for a while that I have created a Stored Procedure in SQL Server 2005 Database that performs a search on a table (according to the given parameters). It was happening that when this procedure was executed from ADO.net (my front-end Visual Basic Application) Timeout exception was occurring. But at the same time when we execute this procedure from SQL Server Management Studio it executes immediately.

The Timeout in Visual Basic Application was NOT coming immediately it was coming after some time when users execute the procedure many times. Whenever time out occurred, I went in the SQL Server Management Studio and just did ALTER (without changing a single thing in procedure). And the problem resolved for some time NOT permanently.

So I put this question on MSDN Forums and a member told me to put the WITH RECOMPILE in the procedure. He told me that

"I think the problem is with the cache. When you ALTER the procedure from Management Studio it is recompiled and the problem is fixed. this is my guess."

It sure fixed my problem and the Timeout never came afterwords. Here is the link to that post.

http://forums.microsoft.com/MSDN/ShowPost.aspx...

Another look at Exception Handling

2008-07-30T12:11:00.006+06:00

Level: Beginner

Knowledge Required:

Exception Handling
ADO.net

Description:
As we have used the Exception Handling (Try...Catch) many times. But in this post we shall understand an important aspect. Consider the following code,

Private Sub InsertSomeData()
    Dim conn As SqlClient.SqlConnection = Nothing
    Dim cmd As SqlClient.SqlCommand
    Dim tran As SqlClient.SqlTransaction = Nothing
    Try
        ' initialize connection
        conn = New SqlClient.SqlConnection("Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True")
        ' open connection
        conn.Open()
        ' begin transaction
        tran = conn.BeginTransaction

        ' create a Command
        cmd = conn.CreateCommand()

        ' setup command to insert data in 1st table
        cmd.CommandText = "INSERT INTO MyTable1(Column1) VALUES('Data in MyTable1.Column1')"
        cmd.ExecuteNonQuery()

        ' setup command to insert data in 2nd table
        cmd.CommandText = "INSERT INTO MyTable2(Column1) VALUES('Data in MyTable2.Column1')"
        cmd.ExecuteNonQuery()

        ' ok now commit the changes
        tran.Commit()
    Catch ex As Exception
        ' an exception is occurred
        ' check if transaction has been initialized then
        ' rollback
        If tran IsNot Nothing Then
            tran.Rollback()
        End If
        ' again throw the exception
        ' this is because we want the caller to be notified
        Throw ex
    Finally
        ' finally block will be executed anyway
        ' whether the exception is occurred or NOT
        ' ================---------------------
        ' Note: we are again throwing the exception in Catch block 
        '       but still the following code will be executed
        If conn IsNot Nothing AndAlso _
           conn.State = ConnectionState.Open Then
            ' if connection was established then close it
            conn.Close()
        End If
        ' =====================-------------------------
    End Try
    ' this line however will NOT be executed if
    ' exception is occurred
    Debug.Print("Outside the Try..Catch block")
End Sub

As you can see this method will be inserting data in 2 tables. Our goal is,

Begin the Transaction
Try to Insert the Data in Tables
If no error occurs on Insertion then Commit the Transaction
If error occurred then
Rollback the Transaction
Again throw the Exception (so the caller is being notified)
End If
At the end (whether exception is occred or NOT) Close the connection

Note that if Exception occurs then in Catch block we are throwing the same exception again. At this point it seems that control will immediately exit from this method, but fortunately this does NOT happen in this way. Instead control first jumps to Finally block then after that it exits from method.

Enter Key to Set Focus in Next Control (for Data Entry Forms)

2008-07-30T10:58:00.005+06:00

Level: Beginner

Knowledge Required:
Windows Forms

Description:
In a Windows Application, we use Tab key to move Focus from current control to next control, but in a Data Entry applications, usually Enter Key is used for this purpose.

There are different techniques to accomplish this. I think the following is the simplest one.

First set the Form's KeyPreview property to True. Then put the following code in the KeyPress Event Handler:

Private Sub Form1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles Me.KeyPress
    If e.KeyChar = Chr(Keys.Return) Then
        Me.SelectNextControl(Me.ActiveControl, True, True, True, True)
    End If
End Sub

Implement SQLCommand Cancel with Threading

2008-07-29T13:30:00.010+06:00

Level: Advanced

Knowledge Required:

ADO.net
Threading

Description:
In a data driven application, sometimes we built queries which take time. In these type of scenarios usually developers put the query execution on different thread, so the User Interface keep responsive.

A more user friendly applications provide a flexibility to cancel the currently executing command. This can be achieved by using SQLCommand.Cancel() method.

So to implement it,

Create a Thread
Execute Query in that Thread
Meanwhile if user clicks the Cancel button we will call the SQLCommand.Cancel method

Cancel method actually tries to cancel the in-process query. When the attempt to cancel the query succeeds then an exception is occured at the same point where Command was executed i.e. SQLCommand.ExecuteReader (or other Execute method) was called. When the cancellation is failed then no exception occurs and command continues its execution. Therefore we also need to handle the Exception at the same point where we have executed the query. Here is an example:

Private Sub DoSomeWork()
    objCon = New SqlConnection("Data Source=.;Integrated Security=True")
    objCmd = New SqlCommand("WAITFOR DELAY '00:00:05';", objCon)

    objCon.Open()
    Try
        objCmd.ExecuteReader()
    Catch ex As SqlException
        Debug.Print(ex.Message)
    End Try
    objCon.Close()
End Sub

The above method just executes a WAITFOR DELAY query which simulates a very long query that takes 5 seconds to complete. Note that objCon and objCmd are Module Level variables. This method will be executed in a different thread using the following code,

Dim t As Threading.Thread
t = New Threading.Thread(AddressOf DoSomeWork)
t.Start()

While this query is being executed we will perform a query cancel on a button click event as,

Private Sub CancelButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
    If objCmd IsNot Nothing Then
        objCmd.Cancel()
    End If
End Sub

Draw Focus Rectangle using ControlPaint Class

2008-07-28T23:32:00.003+06:00

Level: Beginner

Knowledge Required:
GDI+

Description:
Sometimes while creating a User Control from scratch. It is required to display the Focus Rectangle (the dotted border). This can be easily achieved by using

System.Windows.Forms.ControlPaint

E.g.:

Private Sub Form1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Me.Click
    Dim g As Graphics
    g = Me.CreateGraphics
    ControlPaint.DrawFocusRectangle(g, New Rectangle(10, 10, 100, 100))
End Sub

ControlPaint class contains other useful routines for control drawing you can explore the list using intellisense.

Dynamically Loading Data in TreeView control ASP.net

2008-07-26T16:47:00.002+06:00

Level: Beginner

Knowledge Required:

ASP.net
Typed DataSet
TableAdapter
SQL Server 2005

Description:
In my post Dynamically Loading Data in TreeView Control, I have discussed how to load the data on runtime in TreeView control. This post is for Windows Application (Windows Forms) platform. Recently I was asked to have the same code for ASP.net Web Application. I think there are lots of articles available on Internet about this kind of stuff. But I am also submitting my own.

This is a very simple project demonstrates how to dynamically load data when TreeView's Node is expanded.

Download:
DynamicTreeview.zip

Note: The zip file contains DynamicTreeViewDatabaseSetup.sql T-SQL Script which will,
1) Create a database "MyDatabase"
2) Create a table "tbl_Category" in that database
3) Create a Stored Procedure "CategorySelect"
4) Put some data in tbl_Category

Please execute this script before running the project.

Function to Trim any Character/String from Left of varchar in SQL Server

2008-07-19T15:08:00.007+06:00

An Extended Left Trim (LTRIM) Function for SQL Server which removes given string/character from Left of another varchar (String)

Level: Intermediate

Knowledge Required:

T-SQL
SQL Server 2005
SQL Server User Define Functions

Description:
The SQL Server's built-in LTrim() function removes only the blanks (spaces) from Left of a varchar (string). Here is a User Defined function which can remove any character.

CREATE FUNCTION [dbo].[LTrimString]
(
 -- Add the parameters for the function here
 @StringToTrim varchar(max),
 @CharToTrim varchar(10)
)
RETURNS varchar(max)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varchar(max);
    DECLARE @i int;
    DECLARE @l int;
    DECLARE @sl int;

    SET @i = 1;
    SET @l = Len(@StringToTrim);
    SET @sl = Len(@CharToTrim);
    While @i < @l
    Begin  
            If SubString(@StringToTrim, @i, @sl) <> @CharToTrim
            Begin
                SET @Result = SubString(@StringToTrim, @i, @l - @i + 1);
                Break;
            End
        SET @i = @i + @sl;
    End

    SET @Result = IsNull(@Result, @StringToTrim);

    -- Return the result of the function
    RETURN @Result;
END

Usage:

Print dbo.LTrimString('00000005221', '0')

Output: