Switch Case,switch Statement in C#.NET

switch case or Switch statement

Switch statement is used to move the program control to a specific "Switch Case" based upon the Switch expression.

It can be used as a alternative to IF-ELSE.

Below is the skeleton for the Switch case .

switch (expression)  

{  

   case value-1:  

      Statement  

   break;  

   case value-2:  

      Statement  

   break;  

   case value-3:  

         Statement  

   break;  

   default:  

      Statement  

   break;  

}  

In the above , Expression can be any non null value . based upon the value of "Expression" the statement will be executed.

Lets Understand with a flow chart .

Flow Chart for Switch Statement,switch case

In the Above chart Enter a value to the switch statement Say "Age". Then switch Case will compare the "Age" value to the Case statement value and Print the statement. 

Before Writing the program lets understand the algorithm.

Step 1. Start

Step 2. Read Age

Step 3. Case 1:

            print "Your age is 1"

Step 4. Case 2:

            print "Your age is 2"

Step 5. Case 3:

            print "Your age is 3"

Step 6. Default:

            print "You are not a child"

Step 7. End

Lets Convert above chart and Algorithm to a Program and check the output.

using System;

namespace Console-Application

{

    class Switch-Check

    {

        public static void Main()

        {

            int iAge = 0;

            Console.WriteLine("Enter a number....");

            iAge = Convert.ToInt16(Console.ReadLine());

            switch (iAge)

            {

                case 1:

                    Console.WriteLine("Your Age is " + iAge);

                    break;

                case 2:

                    Console.WriteLine("Your Age is " + iAge);

                    break;

                case 3:

                    Console.WriteLine("Your Age is " + iAge);

                    break;

                default:

                    Console.WriteLine("Your are not a child");

                    break;

            }

            Console.ReadKey();

        }

    }

}

Output

See above output after running the above program in the visual studio.

In first output our entered value matches the Case statement so it printed the statement.
In second output our entered value did not matches the Case statement so it printed the statement which is Default one.

Few Points Need to Know about switch statement

1.It checks only the match "Case" equal to switch statement value and show the output rather than check each "Switch Case" unlike if-else.

2.It uses Jump table which makes Switch statement faster over if-else.

3.When conditions becomes very long in a program then we should prefer Switch over if-else.

Thanks.............

Share:

Read More

Understand Conditional Statement in C#

Introduction

Conditional statement is a set of Rule performed if a certain Condition met.
We can achieve this using IF-ELSE Statement.

Skeleton of IF-ELSE Statement

if(<condition-1>)
{
      print message
}
else if(<condition-2>)
{
      print message
}
else
{
     print message

}

we can use only IF instead of IF-ELSE Statement as below.

if(<condition-1>)
{
      print message
}
if(<condition-2>)
{
      print message
}
if(<condition-3>)
{
     print message

}

flowchart for if-else statement

The above chart describe as below algorithm.

Step 1. Start

Step 2. Read two number a,b

Step 3. if(a>b) then 

            print " a is greater"

Step 4. else if(a=b) then 

             print "Both are same"

Step 5. else(a>b) then 

             print " b is greater"

Step 6. End

Lets Convert this algorithm to a C# program.

using System;

namespace ConsoleApp1

{

    class ifelsecheck

    {

        public void CheckIfElse()

        {

            int a, b = 0;

            Console.WriteLine("Enter two number..");

            a = Convert.ToInt16(Console.ReadLine());

            b = Convert.ToInt16(Console.ReadLine());

            if (a > b)

                Console.WriteLine(a + " is bigger");

            else if (a == b)

                Console.WriteLine(a + " and " + b + " are equal");

            else

                Console.WriteLine(b + " is bigger");

            Console.ReadKey();

        }

    }

}

Limitations

• IF-ELSE check each and every condition till the condition is satisfied.
• Performance will degrade if more condition is there in the statement.
• ELSE is optional  in the statement but IF is compulsory to use it.
• The difference between IF and IF-ELSE is as below.
• IF will check each and every condition 
• IF-ELSE will check till condition satisfied.

Thanks.......

t.

Share:

Read More

Introduction To C# with an Example

Introduction to c#

C#  is a high level language and its first version C# 1.0  released by Microsoft in 13th February 2002. Syntax is almost similar to C++.

what is c#?

• C# is an Object Oriented Programming Language Developed By Microsoft.
• C# is a high level Language as this does not depend upon the hardware directly.
• Users having good knowledge into C++ can use it as syntax is almost similar to C# .

Why C#?

Below are the reason why C# is more popular.

• Easy to Start- As C# syntax is similar to C,C++ and Java , Developer can easily migrate to this language and learn with no time.

• Widely used in Desktop and Web Application-As C# is more popular because of easy syntax and reach library, this is used widely now a days

• Larger Community-C# has larger community and hence more IDE to develop program.

• Game Development- C# is more popular in Game development because of reach libraries and easy to develop.

c# compare version numbers                                                           

Version number CLR version Release date
1.0 1.0 2002-02-13
1.1 1.1 2003-04-24
2.0 2.0 2005-11-07
3.0 2.0 2006-11-06
3.5 2.0 2007-11-19
4.0 4 2010-04-12
4.5 4 2012-08-15
4.5.1 4 2013-10-17
4.5.2 4 2014-05-05
4.6 4 2015-07-20
4.6.1 4 2015-11-17
4.6.2 4 2016-08-02
4.7 4 2017-04-05
4.7.1 4 2017-10-17


Lets Understand with a program.

Open/Lunch Visual Studio-> Go to File->New->Project

New Project Pop up will Appear.

Under Templates Select Visual C# and select the template 

called Console App as below.

visual studio application templates

Enter Project Name and Solution name of your Choice.

Set the location for your project and Click OK as below.

project and solution visual studio

A new project will be created based upon the Project name and the solution name.

Lets Write a simple program under above created Project.

using System;

namespace ConsoleApp1
{
    class SampleProgram
    {
        //Read Technotech
        //Print Welcome to Technotech
        static void Main(string[] args)
        {
            String string1 = string.Empty;
            Console.WriteLine("Enter a String....");
            string1 = Console.ReadLine();
            Console.WriteLine("Welcome to " + string1);
            Console.ReadKey();
        }
    }
}

Press F5 or Ctrl + F5 to run the application.

console output c#

In the above Image I enter "Technotech"
We got the output "Welcome to Technotech"


Thanks..........

Share:

Read More

difference between where and having clause in sql server

Introduction

Both Where and and having clause used to filter the record but for different purpose.

Lets Get Started

Where clause used to filter the data by row where as Having clause  is used to filter based upon the aggregate data.

Lets Understand practically.

I created a table as TEST-INSERT and inserted some value to it as below.

CREATE TABLE [TEST-INSERT]

( [RNO] [int] NULL, [ID] [int] NULL, [NAME] [varchar](20) NULL, [Category] [varchar](50) NULL) 

INSERT INTO TEST-INSERT VALUES(1,1,'Mark','Teacher')

INSERT INTO TEST-INSERT VALUES(2,2,'Ben','Teacher')

INSERT INTO TEST-INSERT VALUES(3,3,'Paul','Student')

INSERT INTO TEST-INSERT VALUES(4,4,'Struat','Student')

INSERT INTO TEST-INSERT VALUES(5,5,'RK','Cleark')

Table will display like below.

RNO         ID          NAME                 Category

----------- ----------- -------------------- --------------

1           1           Mark                 Teacher

2           2           Ben                  Teacher

3           3           Paul                 Student

4           4           Struat               Student

5           5           RK                   Cleark

Difference-1

If we want to select some specific row then we can use where clause as shown below.

SELECT * FROM TEST-INSERT WHERE ID=2 

If we want to count based up on the category then we can use Having clause.
See below query.

SELECT Category,COUNT(1) "Teacher Count" FROM TEST-INSERT GROUP BY Category HAVING Category='Teacher'

Difference-2

We can use where clause in Update query where as we can not use Having clause in Update query.

UPDATE TEST-INSERT SET Category='Student' WHERE ID=5

Difference-3

We we are using Having clause then Group by must be there in the query but in case of where no need any clause .

Difference-4

If we want to search any record from the table then we should go for Where clause.

If we are checking the duplicate record in a table or checking the count of a record based upon category we should go for Having clause.

Interview questions and Answers

1. Can we use Where and Having clause in a same table?

Yes,We can . See the below query.

SELECT Category FROM TEST-INSERT WHERE Category in('Student','Teacher') GROUP BY Category HAVING Category='TEACHER'

2. How to check the duplicate record from a table?

SELECT Category,COUNT(1) "Teacher Count" FROM TEST-INSERT GROUP BY Category HAVING COUNT(Category)>1

The above query will count the duplicate record whose count is more than 1.

2. Can we write Having Clause without Group By?

No,Not Possible.

Thanks.....................

Share:

Read More

Difference between Union and Union All

Introduction

Both Union and Union All are Used to Join Rows of two or more tables.

But the Union Or Union All  need the datatype same .

Lets Understand with the below example.

Difference-1

Union 

Union will combine table rows and will display the non repeated records, which are not duplicate.

See the below query and see the below output.

Query

select 'India'

union

select 'Pakistan'

union

select 'Australia'

union

select 'Pakistan'

union

select 'India'

Output

Australia

India

Pakistan

Union All

1.Union All will combine table rows and will display all records .

See the below query and see the below output.

Query

select 'India'

union all

select 'Pakistan'

union all

select 'Australia'

union all

select 'Pakistan'

union all

select 'India'

Output

India

Pakistan

Australia

Pakistan

India

You can see India and Pakistan repeated in Both the query but we get different result.

Difference-2

Union Use sort mechanism based upon its key while displaying the record where as Union All never use sort mechanism. It display the record as it is .

Lets Take an example .

Let create two table as below.

create table tblUnion1

(

id int,

Name varchar(20),

Country varchar(20)

)

create table tblUnion2

(

id int,

Name varchar(20),

Country varchar(20)

)

Lets Insert few records to both the tables as below.

insert into tblUnion1 values(1,'Name1','India')

insert into tblUnion1 values(2,'Name2','Pakistan')

insert into tblUnion1 values(3,'Name3','Australia')

insert into tblUnion2 values(1,'Name1','India')

insert into tblUnion2 values(2,'Name2','Pakistan')

insert into tblUnion2 values(3,'Name3','Australia')

insert into tblUnion2 values(5,'Name5','Australia')

insert into tblUnion2 values(4,'Name4','Srilanka')

Lets Execute the below query using UNION and UNION ALL and see what is going to happen.

select * from tblUnion1

union

select * from tblUnion2

Output for Union

1 Name1 India

2 Name2 Pakistan

3 Name3 Australia

4 Name4 Srilanka

5 Name5 Australia

select * from tblUnion1

union all

select * from tblUnion2

Output For Union All

1 Name1 India

2 Name2 Pakistan

3 Name3 Australia

1 Name1 India

2 Name2 Pakistan

3 Name3 Australia

5 Name5 Australia

4 Name4 Srilanka

See the above two output .

In Case of Union we got the data in a sorted order where in case of Union All we got the data as it is in the table.

Difference-3

Lets check the performance.

To check performance we can use Execution Plan for both Union and Union All.

To get execution plan along with the output you can goto Query->Include Actual Execute Plan

See the below Image.

Execution Plan Option

After Inclusion of execution Plan Lets execute the Below statements and see the execution Plan associated with it.

select * from tblUnion1

union

select * from tblUnion2

Execution Plan for Union

select * from tblUnion1

Union All

select * from tblUnion2

Execution Plan for Union All

See Both the Execution Plan. 

In 1st case(UNION) the execution plan taking 63% for sorting where as in 2nd case(UNION ALL) the execution do not have any sorting operation.

So this conclude that UNION ALL is faster than UNION.

Note

Lets note some point what we discussed above.

1. Union take Unique records where as Union All take all the records whether it is repeated or non repeated.
2. Union All is faster than Union as Union take extra time while sorting the record.
3. When we need unique records we use Union.
4. When we need duplicate records also then we will use Union All.
5. It is recommended to use Union All as much as possible compared to Union because of the better performance prospective.

For More Information Please refer below books....

Thanks........... 

Share:

Read More

Dynamic SQL Query in SQL Server and Why We Need This

Introduction

Before Dive into the Topic of Dynamic SQL query lets have a look into Static query and why we need dynamic SQL query even though we have Static query .
Lets Get Started....
See the below Image which describes the SQL Queries types based upon the execution.
Basically we will discuss more about Dynamic SQL rather than Static SQL.

SQL Queries

It can be divided into 2 types.

• Static SQL Query

• Dynamic SQL query

Static SQL Query

Static SQL query is known at compile time i.e. Compiler have the idea about the SQL query .
This is straight forward what we use our day to day life. See the below example.

SELECT * FROM TESTINSERT t WHERE t.NAME='Rahul'

We are not warping the SQL Query as a string.  

Dynamic SQL query

Dynamic SQL query is not known at compile time i.e. Compiler do not have any idea about the SQL query .
This is quite different than the normal SQL Statement as this can not be executed straight forward.
This dynamic SQL query wrap in a string format and get executed by using below commands.

• EXECUTE(<string value>)

• EXECUTE SP_EXECUTESQL(<string value>)

We will discuss the above two command this article.Lets discuss Dynamic SQL with an example.

DECLARE @Statement NVARCHAR(200)DECLARE @Name VARCHAR(10)

SET @Statement ='SELECT * FROM TESTINSERT t WHERE t.NAME=''' + @Name''''EXECUTE(@Statement )

The above is the example of Dynamic SQL as it stored in a string variable called @Statement and in the next step it execute the string to get the output by using EXECUTE Command.

Lets Dig into the Topic and Understand the topic more deeper.

1. EXECUTE SP_EXECUTESQL,<SQLstring>, <datatype>,parametervalue
2. EXECUTE(<SQL String>)

Lets Understand the below example.

Procedure-1(Execute)

--EXECUTE [PROCDynamicQueryExecute]
ALTER PROCEDURE [dbo].[PROCDynamicQueryExecute]
  AS
  BEGIN
  DECLARE @ID INT
  SET @ID=1
  DECLARE @FullQuery NVARCHAR(500)
  SET @FullQuery = N'SELECT * FROM TESTINSERT WHERE id= ' + CAST(@ID AS                VARCHAR) EXECUTE(@FullQuery)
END

Procedure-2  (Execute sp_executesql)

--EXEC [PROCDynamicQuery_ExecuteSQL]

ALTER PROCEDURE [dbo].[PROCDynamicQuery_ExecuteSQL]

AS

BEGIN

DECLARE @ID INT

SET @ID=1

DECLARE @QryName NVARCHAR(100)

set @QryName=N'ID=@ID'

DECLARE @str Nvarchar(100)

SET @str = N'@ID INT'

DECLARE @FullQuery NVARCHAR(500)

SET @FullQuery = N'SELECT * FROM TESTINSERT WHERE id= @ID'

EXECUTE sp_Executesql @FullQuery,@str,@ID=1

END

See the two procedure(Procedure1 and Procedure2) Carefully and lets find the difference between these two.

Difference 1

In Procedure1 we can not pass the value to the Execute(not allow parameter) where as we can pass the value in Procedure2(parameterized). See the below command.

1. EXECUTE(@FullQuery)
2. EXECUTE sp_Executesql @FullQuery,@str,@ID=1

Difference 2

We  can apply Execute Sp_ExecuteSQL in place of Execute .

Example- EXECUTE sp_Executesql @FullQuery

Difference 3

Risk of SQL injection is more in case of Execute where as less in Execute SP_executesql.

Difference 4

Plan not cached in Execute where as Plan cached in Execute SP_executesql. at 1st execution.

Note

Both the dynamic query building technique helps to build the dynamic SQL. So that even if we do not have any idea about the value in compile time we can can execute the query using Dynamic SQL with the help of the 2 command above discussed. We normally use dynamic query when value is known at run time.

Thanks.....................................

Share:

Read More

Understand Table Variable and Temp Table In SQL Server

Introduction 

As we know both Temp table and Table Variable are used to store the data temporarily.

But both have their pros and cons.We should have good understanding when to use what to improve our coding standard.

Lets understand the pros and cons and why we need over one another.

Get Started

Table variable is nothing but declaring a table like a variable declared in SQL Server variable .

Table variable name is prefixed with @ symbol.

DECLARE @MyTableVar TABLE
(
id INT,
Name VARCHAR(20)
)

Temp Table is nothing but creating a table like Table created in SQL Server , but for the current session(page).

Table variable name is prefixed with # symbol.

CREATE TABLE #MyTableVar

(

id INT,

Name VARCHAR(20)

)

Now both will give the same output . Lets see in action .

Execute the below code.

DECLARE @MyTableVar TABLE

(

id INT IDENTITY(1,1),

Name VARCHAR(20)

)

INSERT INTO @MyTableVar VALUES('A')

SELECT * FROM @MyTableVar

CREATE TABLE #MyTableVar

(

id INT IDENTITY(1,1),

Name VARCHAR(20)

)

INSERT INTO #MyTableVar VALUES('A')

SELECT * FROM #MyTableVar

DROP TABLE #MyTableVar

Output

Both are behaving same . 

Now a question comes to our mind why we need two types of table when we can achieve everything in a table?

Answer is we can not achieve everything in one types of table. Both have their pros and cons.

Difference between Table Variable and Temp Table

Temp table stored in Tempdb database where as Table variable stored in memory, but under memory pressure Table variable pushed to tempdb database.

1. DDL command will not applicable for Table Variable while we can apply DDL command for Temp Table.
2. Table variable drop immediately after the execution where as Temp table drop after the end of the session or with the drop command.
3. Temporary variable can not involve in Transaction,Logging and Locking while Temp variable Involve on this.
4. We can not create Index separately for Table Variable where as we can create for Temp table.
5. Table variable can be passed as a parameter to function and stored procedure where as we can not pass the temp table.
6. Temp table allows schema modification where table variable not.
7. Temporary variable is visible with in the scope where as temp table visible till the session end.

Share:

Read More

Normalization In Sql Server

Introduction

Normalization is Nothing but efficiently organizing the data in SQL Server. 
Its very easy to understand when data in a table is simple and well-organised That is what Normalization do.

Structuring of data can be of 2 types.

• Normalization
• De-Normalization

See the below image (Fig-1).

Fig-1

Lets Started

In this article we will discuss "Normalization" and why this is important.

Normalization is nothing but simplifying the records , in other we can say we make a table as simple as possible by removing the odd one. So that its very easy to understand the purpose of the each table.

Here we will discuss most widely used Normal Forms(NF-1,NF-2,NF-3).

Lets take an example of the below table. The table consists of Employee data as well as their department details.

We can see the EName column contains the name of the employee which consists of more than one employee in a single record. e.g. - RAJU,ABHI. Which is not the correct way to represent because which may cause problem while complex calculation in future.
So each employee should have their individual data.

TABLE1

EName	Salary Monthly	Salary Yearly	DEPT NAME
RAJU,ABHI	12000	144000	HR
AKU,RAVI,RAKESH	10000	120000	FINANCE
RAJ	20000	240000	EDUCATION
SANU	11000	132000	IT
MUNU	9000	108000	ADMIN

  

FIRST NORMALIZATION FORM(NF-1)

As per the 1st Normal form a table can not hold multiple values . It should have atomic values.
See the below table(TABLE2) EName contains the atomic values(single values) unlike the TABLE1.

TABLE2

Eid	EName	Salary Monthly	Salary Yearly	DEPT NAME
1	RAJU	12000	144000	HR
2	AKU	10000	120000	FINANCE
3	RAJ	20000	240000	EDUCATION
4	SANU	11000	132000	IT
5	MUNU	9000	108000	ADMIN
6	ABHI	12000	144000	HR
7	RAVI	10000	120000	FINANCE
8	RAKESH	10000	120000	FINANCE

But we can see the above table contains Lots of redundant data . Lets normalize the table better with the help of 2ND NORMALIZATION FORM.

SECOND NORMALIZATION FORM(NF-2)

It says

• It should fulfill 1st Normalization form .
• Non-key columns should be fully and functionally dependent on the primary key of the table.

In TABLE2 see the Dept Name column is not directly dependent on the primary key. So we break the tables into two tables as below.

• Employee
• Department

Department

Dept id
1	HR
2	FINANCE
3	EDUCATION
4	IT
5	ADMIN

Employee

eid	EName	Salary Monthly	Salary Yearly	Dept id
1	RAJU	12000	144000	1
2	AKU	10000	120000	2
3	RAJ	20000	240000	3
4	SANU	11000	132000	4
5	MUNU	9000	108000	5
6	ABHI	12000	144000	1
7	RAVI	10000	120000	2
8	RAKESH	10000	120000	2

As Department is not directly dependent to the Employee table, we break the table table into two parts as above and associated the two table by using Dept id.
The above table Satisfy NF-2. 

Here we can see still some redundant data are available in Employee table. Lets Understand how we can get rid of it by using Third Normalization Form(NF-3) 


THIRD NORMALIZATION FORM(NF-3)

This says 

• It should follow NF-1 and NF-2.
• It should not have dependency between the non key columns.

As the Employee table above satisfy both NF-1 and NF-2 , but not NF-3 we need to check how can we satisfy NF-3 by modifying the table.

Lets take Salary Monthly and Salary Yearly. Both are non key columns and both are related. We can say both are directly proportional to each other.

Salary Monthly=Salary Yearly*12 

if we have one column we can calculate the other without any problem.

So its better to remove "Yearly Salary".

We can find a table as below.

Employee

eid	EName	Salary Monthly	Dept id
1	RAJU	12000	1
2	AKU	10000	2
3	RAJ	20000	3
4	SANU	11000	4
5	MUNU	9000	5
6	ABHI	12000	1
7	RAVI	10000	2
8	RAKESH	10000	2

Department

Dept id
1	HR
2	FINANCE
3	EDUCATION
4	IT
5	ADMIN

The Above both the table satisfy NF-3. These table will satisfy NF-1,NF-2,NF-3.

Points to remember while using Normalization

Performance will decrease when Joining takes place.

Need to create lots of key to achieve this .

Need to divide single table to multiple table. 

 

Thanks................

Share:

Read More