JQuery Validation Engine Custom Control in Asp.net

Dynamic Where clause in Sql server

Using COALESCE

/* Create Stored Procedure 'sp_EmployeeSelect_Coalesce'. 
Example 2.1 - Using Coalesce */ 

Create Procedure sp_EmployeeSelect_Coalesce
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary Decimal(10,2)
        
AS
      Set NoCount ON
 
    Select * From tblEmployees 
    where EmployeeName = Coalesce(@EmployeeName, EmployeeName) AND
        Department = Coalesce(@Department, Department ) AND
        Designation = Coalesce(@Designation, Designation) AND
        JoiningDate >= Coalesce(@StartDate, JoiningDate) AND 
        JoiningDate <= Coalesce(@EndDate, JoiningDate) AND
        Salary >= Coalesce(@Salary, Salary)

    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO

In the above stored procedure, for each condition in the WHERE-clause, the COALESCE function picks the first non-null value and uses it for the comparison operation. If the input parameter value is NULL, then the coalesce function returns the actual value which equals itself. The causes the particular row to be returned for that operation.

Using ISNULL

ISNULL is a T-SQL System function used to handle NULL values, it takes two argument, the first one is to check for an expression and the second argument is for the replacement value if the check expression value is NULL. We can say that ISNULL is equivalent to COALESCE function with two arguments.
Basic Syntax : ISNULL()

ISNULL ( check_expression , replacement_value )

Note: replacement_value must have the same type as check_expresssion.
Let's take the above example and write the stored procedure that builds the WHERE-clause dynamically using the ISNULL function.

Using ISNULL

/* Create Stored Procedure 'sp_EmployeeSelect_ISNULL'. 
Example 2.2 - Using IsNull */ 

Create Procedure sp_EmployeeSelect_ISNULL
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary Decimal(10,2)
AS
      Set NoCount ON
  
    Select * From tblEmployees 
    where EmployeeName = IsNull(@EmployeeName, EmployeeName) AND
        Department = IsNull(@Department, Department ) AND
        Designation = IsNull(@Designation, Designation) AND
        JoiningDate >= IsNull(@StartDate, JoiningDate) AND 
        JoiningDate <= IsNull(@EndDate, JoiningDate) AND
        Salary >= IsNull(@Salary, Salary)

    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO 

You can see in example 2.2 - WHERE-clause is built dynamically using the ISNULL function. It evaluates the expression and checks whether the parameter value is NULL or not. When this check expression returns a Non-Null value, it uses the parameter value in the comparison operation. When the check expression returns null, it uses the current value which equals itself and that causes all the rows to be returned for that operation.

Using CASE

CASE function is equivalent to the COALESCE function in SQL Server. It evaluates a list of conditions and returns one result expression from multiple possible result expressions. There are two types of CASE functions:

• Simple CASE
• Searched CASE

Basic Syntax: Simple CASE

CASE input_expression
   WHEN (when_expression1 IS NOT NULL) THEN result_expression1
   WHEN (when_expression2 IS NOT NULL) THEN result_expression2
   ...
   WHEN (when_expressionN IS NOT NULL) THEN result_expressionN
   ELSE else_result_expression
END 

Basic Syntax: Searched CASE

CASE
   WHEN (boolean_expression1 IS NOT NULL) THEN result_expression1
   WHEN (boolean_expression2 IS NOT NULL) THEN result_expression2
   ...
   WHEN (boolean_expressionN IS NOT NULL) THEN result_expressionN
   ELSE else_result_expression
END

The simple CASE function compares the input_expression with the when_expression to get the desired result_expression. The searched CASE function evaluates a set of Boolean expression to get the desired result_expression. Let's take the same example and write the stored procedure that builds the WHERE-clause dynamically using the CASE function.

 

Using CASE

/* Create Stored Procedure 'sp_EmployeeSelect_Case'. 
Example 2.3 - Using Case-When */ 

Create Procedure sp_EmployeeSelect_Case
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary Decimal(10,2)
AS
      Set NoCount ON
  
    Select * From tblEmployees where EmployeeName = 
    Case When @EmployeeName Is Not Null Then @EmployeeName 
    Else EmployeeName End AND Department = 
    Case When @Department Is Not Null Then @Department 
    Else Department End AND Designation = 
    Case When @Designation Is Not Null Then @Designation 
    Else Designation End AND JoiningDate >= 
    Case When @StartDate Is Not Null Then @StartDate 
    Else JoiningDate End AND JoiningDate <= 
    Case When @EndDate Is Not Null Then @EndDate 
    Else JoiningDate End AND Salary >= 
    Case When @Salary Is Not Null Then @Salary 
    Else Salary End 

    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO

You can see in example 2.3 that WHERE-clause is built dynamically using the CASE function. It evaluates the expression and checks whether the parameter values are NULL or not. When this boolean expression returns true, it uses the parameter value in the comparison operation. When the boolean expression returns false, it uses the current value which equals itself and that causes all the rows to be returned for that operation.

 

 

Alternative

Here is an alternative suggested by a CodeProject member in the article discussion of my first article. This alternative uses neither COALESCE nor CASE function to build Dynamic WHERE-clause but a logic equivalent to it - worth using it.

 Alternate

/* Create Stored Procedure 'sp_EmployeeSelect_Alternate'. 
Example 2.4 - Alternate */ 

Create Procedure sp_EmployeeSelect_Alternate
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary Decimal(10,2)
AS 
    Set NoCount ON
  
    SELECT * FROM tblEmployees
    WHERE (@EmployeeName Is Null OR @EmployeeName = EmployeeName) AND
        (@Department Is Null OR @Department = Department) AND
        (@Designation Is Null OR @Designation = Designation) AND
        (@Salary Is Null OR @Salary = Salary) AND
        (@StartDate Is Null OR @EndDate Is Null OR 
        (@StartDate Is Not Null AND @EndDate Is Not Null AND 
        JoiningDate BETWEEN @StartDate AND @EndDate))

    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO 

Capcha In ASP.NET??

Capcha In ASP.NET??

What is capcha??

A CAPTCHA  is a type of challenge-response test used in computing as an attempt to ensure that the response is not generated by a computer. The process usually involves one computer (a server) asking a user to complete a simple test which the computer is able to generate and grade. Because other computers are supposedly unable to solve the CAPTCHA, any user entering a correct solution is presumed to be human.

How to implement in ASP.NET?

Step 1: In the page add the following:

<%@ Register Assembly="WebControlCaptcha" Namespace="WebControlCaptcha" TagPrefix="Capcha" %>

<div style="width: 230px;" class="greenContainer">
                            

<Capcha:CaptchaControl id="capcha" enableviewstate="false" enabletheming="false"
 captchamaxtimeout="300" runat="server" layoutstyle="Vertical" cssclass="fL" />
 

</div>

Step 2: Add the following css

.greenContainer
{border:1px solid #6a377d;background-color:#f7f2f9;overflow:auto;padding:10px;}

Step 3: In the Save button

The save operation should be done only within

if (Page.IsValid)

{}

 Step 4: Add the following in the web.config file.

<system.web>

<httpHandlers>

<add verb="GET" path="CaptchaImage.aspx" type="WebControlCaptcha.CaptchaImageHandler, WebControlCaptcha"/>

</httpHandlers>

</system.web>

Webcontrolcaptcha image not showing

With IIS7 integrated mode, make sure you add the handler in the right section

<configuration>
  <system.webServer>
    <handlers>
      <add name="CaptchaImage"
        verb="*"
        path="CaptchaImage.aspx"
        preCondition="integratedMode"
        type="WebControlCaptcha.CaptchaImageHandler, WebControlCaptcha"/>
    </handlers>
  </system.webServer>
</configuration>

A CAPTCHA Server Control for ASP.NET (WebControl Captcha))

Implementation

The first thing I had to deal with was the image generated by the CAPTCHA class. This was originally done with a dedicated .aspx form-- something that won't exist for a server control. How could I generate an image on the fly? After some research, I was introduced to the world of HttpModules and HttpHandlers. They are extremely powerful -- and a single HttpHandler solves this problem neatly.
All we need is a small Web.config modification in the <system.web> section:

<httpHandlers>
    <add verb="GET" path="CaptchaImage.aspx" 
       type="WebControlCaptcha.CaptchaImageHandler, WebControlCaptcha" />
</httpHandlers>

This handler defines a special page named CaptchaImage.aspx. Now, this "page" doesn't actually exist. When a request for CaptchaImage.aspx occurs, it will be intercepted and handled by a class that implements the IHttpHandler interface: CaptchaImageHandler. Here's the relevant code section:

Public Sub ProcessRequest(ByVal context As System.Web.HttpContext) _
       Implements System.Web.IHttpHandler.ProcessRequest
    Dim app As HttpApplication = context.ApplicationInstance

    '-- get the unique GUID of the captcha;
    '   this must be passed in via querystring
    Dim strGuid As String = Convert.ToString(app.Request.QueryString("guid"))

    Dim ci As CaptchaImage
    If strGuid = "" Then
        '-- mostly for display purposes when in design mode
        '-- builds a CAPTCHA image with all default settings 
        '-- (this won't reflect any design time changes)
        ci = New CaptchaImage
    Else
        '-- get the CAPTCHA from the ASP.NET cache by GUID
        ci = CType(app.Context.Cache(strGuid), CaptchaImage)
        app.Context.Cache.Remove(strGuid)
    End If

    '-- write the image to the HTTP output stream as an array of bytes
    ci.Image.Save(app.Context.Response.OutputStream, _
                              Drawing.Imaging.ImageFormat.Jpeg)

    '-- let the browser know we are sending an image,
    '-- and that things are 200 A-OK
    app.Response.ContentType = "image/jpeg"
    app.Response.StatusCode = 200
    app.Response.End()

End Sub

A new CAPTCHA image will be generated, and the image streamed directly to the browser from memory. Problem solved!
However, there's another problem. There has to be communication between the HttpHandler responsible for displaying the image, and the web page hosting the control -- otherwise, how would the calling control know what the randomly generated CAPTCHA text was? If you view source on the rendered control, you'll see that a GUID is passed in through the querystring:

<img src="CaptchaImage.aspx?guid=99fecb18-ba00-4b60-9783-37225179a704" 
     border='0'>

This GUID (globally unique identifier) is a key used to access a CAPTCHA object that was originally stored in the ASP.NET Cache by the control. Take a look at the CaptchaControl.GenerateNewCaptcha method:

Private Sub GenerateNewCaptcha()
    LocalGuid = Guid.NewGuid.ToString
    If Not IsDesignMode Then
        HttpContext.Current.Cache.Add(LocalGuid, _captcha, Nothing, _
            DateTime.Now.AddSeconds(HttpContext.Current.Session.Timeout), _
            TimeSpan.Zero, Caching.CacheItemPriority.NotRemovable, Nothing)
    End If
    Me.CaptchaText = _captcha.Text
    Me.GeneratedAt = Now
End Sub

It may seem a little strange, but it works great! The sequence of ASP.NET events is as follows:

1. Page is rendered.
2. Page calls CaptchaControl1.OnPreRender . This generates a new GUID and a new CAPTCHA object reflecting the control properties. The resulting CAPTCHA object is stored in the Cache by GUID.
3. Page calls CaptchaControl1.Render; the special <img> tag URL is written to the browser.
4. Browser attempts to retrieve the special <img> tag URL.
5. CaptchaImageHandler.ProcessRequest fires. It retrieves the GUID from the querystring, the CAPTCHA object from the Cache, and renders the CAPTCHA image. It then removes the Cache object.

Note that there is a little cleanup involved at the end. If, for some reason, the control renders but the image URL is never retrieved, there would be an orphan CAPTCHA object in the Cache. This can happen, but should be rare in practice-- and our Cache entry only has a 20 minute lifetime anyway.
One mistake I made early on was storing the actual CAPTCHA text in the ViewState. The ViewState is not encrypted and can be easily decoded! I've switched to ControlState for the GUID, which is essential for retrieving the shared Captcha control from the Cache -- but by itself, it is useless.

CaptchaControl Properties

The CaptchaControl is a good ASP.NET citizen, and properly implements all the default ASP.NET Server Control properties. It also has a few properties of its own:

Property	Default	Description
CacheStrategy	HttpRuntime	For security reasons, the CAPTCHA text is never sent to the client; it is only stored on the server. It can be stored in Session (web-farm friendly) or HttpRuntime (very fast, but local to one webserver).
CaptchaBackgroundNoise	Low	Amount of background noise to add to the CAPTCHA image. Ranges from None to Extreme.
CaptchaChars	A-Z, 1-9	A whitelist of characters to use when building CAPTCHA text. A character will be picked randomly from this string. By default, I omit some characters likely to be confused, such as O, 0, I, 1, 8, B, etcetera.
CaptchaFont	""	Font family to use for the CAPTCHA text. If not provided, a random installed font will be chosen for each character. A font whitelist is maintained internally so only known legible fonts will be used (e.g., not WingDings).
CaptchaFontWarping	Low	Level of warping used on each character of the CAPTCHA text. Ranges from None to Extreme.
CaptchaHeight	50	Default height of the CAPTCHA image, in pixels.
CaptchaLength	5	Number of characters used in the randomly generated CAPTCHA text.
CaptchaLineNoise	None	Amount of "scribble" line noise to add to the CAPTCHA image. Ranges from None to Extreme.
CaptchaMaxTimeout	90	Number of seconds that the CAPTCHA will remain valid and stored in the cache after it is generated.
CaptchaMinTimeout	3	Minimum number of seconds the user must wait before entering a CAPTCHA.
CaptchaWidth	180	Default width of the CAPTCHA image, in pixels.
UserValidated	False	After postback, returns True if the user entered text that matches the randomly generated CAPTCHA text. Note that the standard IValidation interface is implemented as well.
LayoutStyle	Horizontal	Determines if the text and input box are to the right, or below, the image. Allows greater layout flexibility.

Many of these properties have to do with the inherent tradeoff between human readability and machine readability. The harder a CAPTCHA is for OCR software to read, the harder it will be for us human beings, too! For illustration, compare these two CAPTCHA images: