Understanding Variable Scope in Anonymous Methods / Lambda Statements

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.

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'
)