If variable is Empty or NULL in WHERE clause in SQL Server

If variable is Empty or NULL in WHERE clause in SQL Server

If @SearchType is Null or Empty  how to return all values from the database.

Sample Query:
SELECT
        P.[ProductId],
        P.[ProductName],
        P.[ProductPrice],
        P.[Type]
    FROM [Product] P
    -- if @Searchtype is not null then use the where clause

    WHERE p.[Type] = @SearchType

Just use

If @searchType is null means 'return the whole table' then use

WHERE p.[Type] = @SearchType OR @SearchType is NULL
If @searchType is an empty string means 'return the whole table' then use

WHERE p.[Type] = @SearchType OR @SearchType = ''
If @searchType is null or an empty string means 'return the whole table' then use

WHERE p.[Type] = @SearchType OR Coalesce(@SearchType,'') = ''

If the @SearchType is Null or Empty, it returns all the value from the database.

If the @SearchType contains any value, it returns only the condition satisfied data.

Difference between CTE and Temp Table and Table Variable  in SQL Server

1. Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.

It is divided into two Local temp tables and Global Temp Table, 

Local Temp table  are only available to the SQL Server session or connection (means single user) that created the tables. 

Global temp tables are available to all SQL Server sessions or connections (means all the user). 

These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed.

cases where you need transaction rollback support.

2. CTE - Common table Expression is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE.

3. Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped automatically once it comes out of batch. This is also created in the Tempdb database but not the memory.You cannot create a non-clustered index on a table variable, unless the index is a side effect of a PRIMARY KEY or UNIQUE constraint on the table.
If the resultset is small, the table variable is always the optimum choice.

Remove duplicates in SQL Server when tables have no primary key

Remove duplicates in SQL Server when tables have no primary key

I have table called CricketerDetails, and the table has no primary or unique key.
Below i have mentioned how to remove duplicate items with query.

1. Procedure to remove the Duplicate rows with query is

Query

WITH tempTable as
(
SELECT ROW_NUMBER() Over(PARTITION BY FirstName,LastName,HighestScore ORDER BY FirstName) As RowNumber,* FROM CricketerDetails
)
DELETE FROM tempTable where RowNumber >1

SELECT * FROM CricketerDetails order by FirstName asc

VB.Net vs C# Syntax

VB.Net vs C# Syntax

Click here to view

Access Modifiers in c#

Access Modifiers in c#

public

The type or member can be accessed by any other code in the same assembly or another assembly that references it.

private

The type or member can be accessed only by code in the same class or struct.

protected

The type or member can be accessed only by code in the same class or struct, or in a class that is derived from that class.

internal

The type or member can be accessed by any code in the same assembly, but not from another assembly.

protected internal

The type or member can be accessed by any code in the assembly in which it is declared, or from within a derived class in another assembly. Access from another assembly must take place within a class declaration that derives from the class in which the protected internal element is declared, and it must take place through an instance of the derived class type.

Extension Methods in C#

Extension Methods in C#

Extension methods enable you to "add" methods to existing types without creating a new derived type, recompiling, or otherwise modifying the original type. Extension methods are a special kind of static method, but they are called as if they were instance methods on the extended type.

Remove $ from the string using Extension Methods in C#

using System;
using System.Windows.Forms;

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

        private void Form1_Load(object sender, EventArgs e)
        {
            ExtensionMethodExample();
        }


        private void ExtensionMethodExample()
        {
            string dollarval = "2000$";

            dollarval = dollarval.RemoveDollarSign();
        }
    }


    public static class RemoveDollar
    {
        public static String RemoveDollarSign(this string dollarval)
        {
            string val = dollarval.Replace("$", "");
            return val;
        }

    }

}

HTTP Modules vs Global.asax Files

HTTP Modules vs Global.asax Files
You can implement much of the functionality of a Http modules in the application's Global.asax file, which enables you to respond to application events. However, modules have an advantage over the Global.asax file because they are encapsulated and can be created one time and used in many different applications. By adding them to the global assembly cache and registering them in the Machine.config file, you can reuse them across applications.

The advantage of using the Global.asax file is that you can handle other registered events such as Session_Start and Session_End. In addition, the Global.asax file enables you to instantiate global objects that are available throughout the application.

You should use a module whenever you must create code that depends on application events, and when the following conditions are true:

• You want to re-use the module in other applications.
• You want to avoid putting complex code in the Global.asax file.
• The module applies to all requests in the pipeline (IIS 7.0 Integrated mode only).