Sunday, April 6, 2025

Optimizing Expression Tree Variable Extraction in .NET

In one of my recent experiments, I explored the performance of extracting captured variable values from LINQ expression trees. This is a common need when you're working with LINQ providers or building your own ORM.

For example, consider the following expression:

int minAge = 30;
Expression<Func<User, bool>> expr = x => x.Age > minAge;
  

While it's easy to cache the translated SQL to avoid repeated query generation, we still need to extract the current value of minAge every time the expression is executed. This extraction requires walking through the expression tree to locate and resolve the captured variables.

I compared two approaches for extracting these variable values:

  • Visitor-Based Traversal: Traditional approach using ExpressionVisitor to walk the tree and manually check for relevant node types.
  • Cached Traversal Path: My approach, where the tree is analyzed once to build a path map to variable nodes, and that path is used for fast extraction.

The project is available as a study on GitHub:

https://github.com/sallushan/linq-variable-finder

Here's the benchmark comparison I got using BenchmarkDotNet:

| Method                    | Mean     | StdDev   |
|---------------------------|---------:|---------:|
| VisitorBasedExtraction    | 703.2 ns | 15.81 ns |
| CachedTraversalExtraction | 374.1 ns |  5.60 ns |
  

As seen above, the cached traversal approach provides a significant performance gain and keeps your tree navigation logic clean and separated.

This is not a reusable library (yet), but rather a focused study that I plan to integrate into my ORM project in the future.

Hope it helps someone looking into similar expression tree scenarios.

Monday, March 17, 2025

Atis ORM is now public

Atis ORM is a lightweight LINQ Expression to SqlExpression (AST) conversion system. I've been working on this ORM from past several years and an older version of this ORM is currently being used in a complex ERP system.

It's still under development but I have decided to make it public https://github.com/atis-orm/atis-orm.

Wednesday, May 17, 2023

Crystal Reports font problem in Exported PDF

So, we had this issue, that one of our Web App running on Server was generating a PDF file using Crystal Reports Export API. The Crystal Reports file had barcode fonts to generate barcodes. But the exported PDF was showing the normal Arial font instead of barcodes.

First I investigated that PDF file itself, whether barcode fonts are being embedded in the PDF file or not. So, I realized that instead of barcode fonts, it has Arial font only. So Crystal Reports export system was not embedding the Barcode Fonts during PDF file creation process.

Though we had already installed the fonts on the server, but the font was installed for the user which was logged in at the time of installation. So, I right clicked on the font file and clicked on Install for all Users. This resolved the issue and barcode fonts started to appear in PDF.

Note that this problem can occur for other reasons as well, for example, the fonts are secured and they are not allowed to be embedded. But in my case installing the fonts for all users resolved the problem.

Sunday, April 16, 2023

How to prevent authentication cookie to be renewed on background (polling) AJAX call in ASP.net core

I know that we should be using Web Sockets / SignalR for server side push notifications instead of polling. But I was looking for a quick solution to a simple problem where there is an AJAX call being made after every 5 seconds to server.

This web application is using cookie based authentication with SlidingExpiration. So, whenever any AJAX call is being made to the server, the cookie is renewed. Which is causing the user to be logged in for an indefinite time.

I wanted to have one specific API call to be marked in a way so that it should not renew the cookie. In this way, non-active user will be logged out automatically, even with continuous AJAX calls being made in the background.

We can use OnCheckSlidingExpiration event in cookie settings where we can check if our specific action is being called then we can simply set ShouldRenew to false.

    cookieOptions.Events.OnCheckSlidingExpiration = context =>
    {
        var controllerName = context.Request.RouteValues["controller"] as string;
        var actionName = context.Request.RouteValues["action"] as string;
        if (controllerName == "MyController" && actionName == "MyAction")
            context.ShouldRenew = false;
        return Task.CompletedTask;
    };

I didn't like this solution but unfortunately, I couldn't find any ActionFilterAttribute or any other way so that I can set some type of flag in the pipeline from action level, which can tell cookie event to not renew the cookie. It seems like the cookie events are the first thing in the pipeline that's executed, and action filters are executed later.

Thursday, January 26, 2023

Cache or Not to Cache LambdaExpression.Compile

As per below statement from Microsoft, don't need to create a cache mechanism to avoid LambdaExpression.Compile() calls.

I will caution you against trying to create any more sophisticated caching mechanisms to increase performance by avoiding unnecessary compile calls. Comparing two arbitrary expression trees to determine if they represent the same algorithm will also be time consuming to execute. You'll likely find that the compute time you save avoiding any extra calls to LambdaExpression.Compile() will be more than consumed by the time executing code that determines of two different expression trees result in the same executable code.

However, still not sure if again and again compiling of an expression tree is memory friendly or not.

Source: https://learn.microsoft.com/en-us/dotnet/csharp/expression-trees-execution

A guy here is claiming that there seems to be no memory problem. I guess we'll see in production :P.

Monday, September 26, 2022

JavaScript Kendo Window as Dialog (async/await)

	
	// This function will add following methods in kendo Window
	//		openAsync()		opens the window asynchronously, true = OK, false = Cancel
	//		closeOk()		call this method on "OK" button click
	//		closeCancel()		call this method on "Cancel" button click
	//	IMPORTANT: kendoWindow paramter must be a kendow window
	//				e.g. addOpenAsyncInKendoWindow($("#popup").data("kendowWindow"));
    
	function addOpenAsyncInKendoWindow(kendoWindow) {
  		if (!kendoWindow.openAsync) {
			kendoWindow.bind("deactivate", () => {
      				if (kendoWindow.dispatchClosePopupCall)
        				kendoWindow.dispatchClosePopupCall(kendoWindow.asyncDialogResult);
			});
    			kendoWindow.getResponseAsync = async () => {
      				return (new Promise(resolve => kendowWindow.dispatchClosePopupCall = resolve)).then((data) => { kendowWindow.dispatchClosePopupCall = null; return data; });
			};  
    			kendoWindow.openAsync = async () => {
      				kendoWindow.center();
      				kendoWindow.open();
      				return await kendowWindow.getResponseAsync();
			};
    			kendoWindow.closeOk = () => {
      				kendoWindow.asyncDialogResult = true;
      				kendoWindow.close();
			};
    			kendoWindow.closeCancel = () => {
      				kendoWindow.asyncDialogResult = false;
      				kendoWindow.close();
			};
		}
	}
    
	// Example Usage
	$("#btnOpenPopup").on("click", async e => {
		e.preventDefault();
		const kp = $("#popup").data("kendoWindow");
		addOpenAsyncInKendowWindow(kp);
		const dialogResult = await kp.openAsync();
		if (dialogResult)
			alert("Ok was clicked");
		else
			alert("Cancel was clicked");
	});
    
	$("#btnOk").on("click", e => {
		e.preventDefault();
		const kp = $("#popup").data("kendoWindow");
		kp.closeOk();
	});
	$("#btnCancel").on("click", e => {
		e.preventDefault();
		const kp = $("#popup").data("kendoWindow");
		kp.closeCancel();
	});
JsFiddle Demo

Saturday, March 30, 2019

Generic Insert/Update/Delete Permission Checking Using Reflection

Summary:

A good number of developers spend their time in working with LOB (business) applications wether Desktop or Web. Most of the business applications revolve around CRUD operations on several entities and each CRUD method usually requires Permission Checking routine. In this post we shall see how we can utilize the Reflection to create a generic Permission Checking method in order to minimize the code writing.

Download: Visual Studio C# Project GenericPermission_Reflection.zip

Level: Expert

Knowledge Required:
  • Reflection
  • Application Architecture
  • Some ORM e.g. Entity Framework
Description

For this post, I have created a sample business application which just keeps track of customer sales orders. To make project simple, I haven’t implemented any Data Access code. Usually in business applications we have 3 layers, UI / Presentation Layer, Data Access Layer and Business Layer. Since this post only focuses on business layer functionality therefore, we’ll not have any UI or Data Access Layer implementation.

In this application I have created 4 entities

As you can notice that other than 4 entity classes, we have RecordState enum and EntityBase class. All the 4 POCO entity classes are inhertied from the EntityBase class.

As shown in above image, the EntityBase class has RecordState property which tells the current state of the object that we are about to manipulate. I am assuming that you are already familiar with different ORMs like Entity Framework, which uses similar approach to check what DB operation (insert/update or delete) needs to be performed on the data. A quick recap of this approach is that, each entity is loaded from database with RecordState = Unchanged, then it is passed to user interface, later user makes changes on the user interface which changes the RecordState = Updated, then this object with RecordState = Updated is sent back to the Business Layer and finally business layer performs DB Update using Data Access Layer. Similarly, RecordState property can be used to create a new record or to delete a record by setting appropriate value in RecordState property.

And we also have defined the RecordState enum

Now assume that we are about to create methods in our business layer to manipulate Item entity.

First method that we defined is Item_Get method. It’s pretty straight forward and just loads 1 record from database and return it to the caller which can be a presentation layer.

Next method we will define is Item_Save method.

As you can see, we haven’t implemented the method much and only defined the basic structure of this method. The Item class that we are using in this method has already been inherited from EntityBase class so it will automatically have the RecordState property.

We are about to implement the permission checking in this method, but for this we have created a separate class for permissions.

It’s a good approach to keep the constants aside. Although, we could define these constants within our business layer, but it’s better to keep them like this in separate class. This will also help us in implementing the generic permission checking method.

To check whether the currently logged-in user has certain permission or not I’ve created a separate method in our business layer as well.

Again, here we are not implementing actual permission checking code because it’s beyond the scope of this article.

Now we are good to go to implement the permission checking in our Item_Save method

As you can see above, we are checking the current RecordState property of Item and then we are validating if the current user has respective permission.

Now this is for 1 entity only i.e. Item entity, imagine if you have 50 entities and you have to write 50 Save methods. It would be easier if there is some generic method which we could use to avoid writing the same repeated code again and again. This is what we are going to achieve using Reflection.

First, we’ll create an Attribute class to create a link between different permissions constants and respective entity.

Next, we’ll add this Attribute class with each permission

We are actually linking the permission with the entity and also, we are telling for which RecordState this permission should be used.

Finally, we can define our generic permission testing method

In above method we are using Reflection to extract all the permissions where our special attribute is set, then we are trying to extract the exact permission which is matching with the given entity’s Type and RecordState, once we found the correct permission constant we pass it in our HasPermission method, if it returns false (does not have permission). Then we through exception as per the RecordState.

After this, we can implement this generic save permission checking method in our Item_Save method

Thursday, January 29, 2015

Export System.Data.DataTable to PDF using AT.PDFReportCreator

I was engaged in a big web-application which has lots of inquiry pages. As we all know, each inquiry page has filtering options and a grid which displays the data. With each inquiry page we need to show export buttons, in PDF and Excel. Exporting data in excel was simple, we just need to create a sheet and put all the data in there. But export of PDF was difficult, client was asking to have a good formatted PDF file, with company logo, total number of pages, heading and with inquiry parameters.

Features:

  • Provide your own Logo and Watermark files, yes AT logo and watermark is NOT hard-coded you can change it :-)
  • Page X of Y in the footer
  • Date and Time of printing in the footer
  • Custome Report Heading
  • Provide Page Layout, for example custom page width, height, margins and orientation
  • Define which columns should be included in PDF from DataTable, in-case if you don't want all columns to be printed
  • Define widths for each column
  • Provide Parameters which will be displayed in the report header

iTextSharp is a very powerful library for creating PDF files in .net Applications. So I decided to create a generic API using iTextSharp library, which can be used to create PDF for every inquiry screen. We just need to pass the Data to the utility and it will create the PDF File. Usually I don't have time, so I did a quick work and created a DLL, which can be used with .net Applications, like ASP.net or Desktop. Also I tried my best to keep the usage as simple as possible.

Below is the Export PDF code written for a ASP.net Web Application using my wrapper library around iTextSharp.

protected void btnCreatePDF_Click(object sender, EventArgs e)
{
    try
    {
        using (System.Data.DataTable dt = this.GetData())
        {
            var reportParam = new AT.PDFReportCreator.PDFReportParam()
            {
                ReportData = dt
            };

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=Customers.pdf");
            HttpContext.Current.Response.Charset = "";
            HttpContext.Current.Response.ContentType = "application/pdf";

            AT.PDFReportCreator.PDFReportCreator.CreatePDF(HttpContext.Current.Response.OutputStream, reportParam);

            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.End();
        }
    }
    catch (Exception ex)
    {
        Trace.Write("Export PDF", ex.Message, ex);
    }
}
Due to time limit, I only came up with DataTable export, while we have other possibilities as well, such as,
  • Export ASP.net GridView Control to PDF
  • Export Entity Framework's List of Entity to PDF
  • Export Desktop Application's DataGridView Control to PDF
For above scenarios we can convert them into DataTable, for example iterate each GridView control row and add the content in DataTable and then pass that DataTable to Export utility.
Downloads:
PDFExportTest.zip Download this file, it contains a Web Application project which shows the export option. Also contains the AT.PDFReportCreator.dll and iTextSharp.dll files. If you want to quickly start testing and don't want to go in complex details, then just download this zip file, extract the DLLs and use in your project.
AT.PDFReportCreator.zip Source code of export API, which is actually a wrapper around iTextSharp

Sunday, July 6, 2014

ASP.net ReadOnly TextBox get value on PostBack

Level: Intermediate

Knowledge Required:
  • ASP.net
  • Custom Controls

In ASP.net we have a problem, that if we set a TextBox control ReadOnly, then the value changed through JavaScript cannot be received back to Server on PostBack.

If we search on Google, we will find that a good solutions is to add readonly attribute on the Page_Load. For example,

protected void Page_Load(object sender, EventArgs e)
{
     TextBox1.Attributes.Add("readonly", "readonly");
}

So putting this code for every control and on every page is obviously a bit annoying. So a better way is to create your own TextBox control which implements this behaviour. But since we already have a good TextBox control available so we can just extend it

    public class ExtendedTextBox : System.Web.UI.WebControls.TextBox
    {
        public override bool ReadOnly
        {
            get
            {
                // We will always return ReadOnly false because internally control
                // is checking this property and it will start the same behaviour
                // if we return true, i.e. value will NOT be received on PostBack 
                return false;
            }
            set
            {
                // Here I have implemented a logic, if we you mark the control as
                // readonly then we will render it as background-color = light gray
                if (value)
                {
                    this.Attributes.Add("readonly", "readonly");
                    this.BackColor = System.Drawing.Color.WhiteSmoke;
                }
                else
                {
                    this.Attributes.Remove("readonly");
                    this.BackColor = System.Drawing.Color.White;
                }
            }
        }
    }

The point is, actually we do NOT set the ReadOnly property of control to true, just add the readonly attribute, so that ASP.net control will consider itself as normally rendered control, and will return the value correctly.

After this we can easily use this control on any web page, but before this we have to add the control reference in our web.config file as,

  <system.web>
    <pages>
      <controls>
        <add assembly="YourWebApplicationAssemblyName" namespace="NameSpaceWhereYouHaveThisExtendedClass" tagPrefix="asp"/>
      </controls>
    </pages>
  </system.web>

Monday, February 10, 2014

Changing SQL Server New Stored Procedure Template

You can change the default SQL Server's Stored Procedure Template by editing the following files. This will change the template which appears when you right click on Store Procedure folder (in object explorer) and select "New Stored Procedure..." Command

SQL Server 2008 R2
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\Stored Procedure\Create Stored Procedure (New Menu).sql

SQL Server 2012
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql\Stored Procedure\Create Stored Procedure (New Menu).sql

Note that these files may only be editable by Administrators only, so you may need to open your editor in Administrator mode.

Monday, July 16, 2012

LightSwitch: How to get Total Number of Records in all pages in a Multi-Page DataGrid

Level: Intermediate

Knowledge Required:
  • LightSwitch
  • Search Screen
  • DataGrid
  • Creating a new property and placing it on Screen

This post explains how to get the total number of records in all the pages of Multi-Page DataGrid.

Monday, May 14, 2012

T-SQL Search String From Right / Reverse Search

DECLARE @SomeText varchar(255);
DECLARE @TextToSearch varchar(10);

SET @SomeText = 'There are 3 spaces';
SET @TextToSearch = ' ';

Print CharIndex(@TextToSearch, @SomeText);
-- Following line will search @TextToSearch in @SomeText from right
Print
        CASE WHEN CharIndex(@TextToSearch, @SomeText) > 0 THEN
            (Len(@SomeText + '-') - 1) - 
                (CharIndex(Reverse(@TextToSearch), Reverse(@SomeText)) + (Len(@TextToSearch + '-')-1) - 1) + 1
        ELSE
            0
        END

-- Output:
-- -------------------------------------
-- 6
-- 12
-- -------------------------------------
-- Tips: 
--
-- Reverse() function reverses the string
--
-- Len(@SomeText + '-') - 1, returns the actual string length even if
-- @SomeText have space at the end. Note that Len() function ignores
-- the spaces at the end of string that is why we have placed a '-'
-- at the end of string then subtracted 1 from length so that Len()
-- function should return the correct length

Sunday, April 22, 2012

Loading and Disposing Crystal Reports on ASP.net Page

Recently one of my friend mentioned strange Crystal Reports behavior on ASP.net page. The scenario was simple,

  1. ASP.net web page
  2. A Button
  3. A CrystalReportViewer Control
  4. A simple Crystal Report

What he doing was, on clicking of button he was loading the report in CrsytalReportViewer control. The report was being displayed correctly, but when he was trying to Zoom In, Zoom Out or Exporting the report he was getting error “No valid report source is available”.

Saturday, December 17, 2011

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.

Thursday, January 28, 2010

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

Thursday, September 24, 2009

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

Thursday, May 7, 2009

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

Monday, March 9, 2009

SQL Server Date Time Useful Functions

Here are some SQL Server Date Time Useful functions. 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

Saturday, February 14, 2009

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