DataGridView control change Selection on Mouse Up

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,

1. MouseDown
2. SelectionChanged
3. 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

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

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

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

Back to Top

-- =============================================
-- 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

Back to Top

-- =============================================
-- 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

Back to Top

-- =============================================
-- 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

Back to Top

-- =============================================
-- 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

Back to Top

-- =============================================
-- 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

Back to Top

-- =============================================
-- 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

Back to Top

-- =============================================
-- 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

Back to Top

Notification Bar Control For Windows Forms (Win Forms)

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

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

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