NOCOUNT In SQL Server

This Statement is used to stop the message that shows the count of the number of rows affected by the SQL statement written in the store procedure or directly SQL statement.You Can view this message in the management studio in the message tab of the result panel.

When it is ON-    The number of affected rows will not be returned
When it is OFF-- The number of affected row will be returned

@@ROWCOUNT- is used to get a number of rows affected. either SET NOCOUNT ON or OFF  
@@ROWCOUNT-- Is alway updated with the number of rows affected


Example

Create Table for performing NOCOUNT Statement

create table tbl_NocuntDemo
  (
  number int
  )

When NOCOUNT is OFF

SET NOCOUNT OFF
insert into tbl_NocuntDemo values (1),(2),(3),(4)

Output

It return number of rows affected in table in message tab pane

When NOCUNT is ON

SET NOCOUNT ON
insert into tbl_NocuntDemo values (5),(6),(7),(8)

Output

it does not return row affected in a table but write "Command(s) completed successfully" in message tab pane

so Microsoft think about this error and add a @@ROWCOUNT statement
@@ROWCOUNT is returned number of row affected in table either NOCOUNT ON or OFF

Use @@ROWCOUNT when NOCOUNT is ON

SET NOCOUNT ON
insert into tbl_NocuntDemo values (9),(10),(11),(12)
select @@ROWCOUNT as NoOFRowAffected

Output 
It return @@ROWCOUNT as NoOFRowAffected number of row affected in message
NoOFRowAffected  = 4

Use @@ROWCOUNT when NOCOUNT is OFF

SET NOCOUNT OFF
insert into tbl_NocuntDemo values (13),(14),(15),(16)
select @@ROWCOUNT as NoOFRowAffected

Output 

It return @@ROWCOUNT as NoOFRowAffected number of row affected in message
NoOFRowAffected  = 4


select * from tbl_NocuntDemo

Trigger

Trigger Is One Kind Of StoreProcedure that automatically Execute when DML or DDL Statement Associated with the trigger is executed. DML Triggers are used to evaluate data after data manipulation using DML statements.   
*1 After Trigger (For Trigger)

This Trigger fire After SQL server completes the execution of the action successfully that fired it.

Example: - If you insert/Update/Delete record on table trigger associated that command and then change the respective table but when insert/Update/Delete fail After Trigger is not Fired

After Insert Trigger
After Update Trigger
After Delete Trigger

**2 Instead Of Trigger

This Trigger Fire Before SQL Server completes the execution of the action.Example: - If you insert/Update/Delete record on table trigger associated that command and then change the respective table but when insert/Update/Delete fail Instead Trigger is Fired.

Instead, Insert Trigger
Instead, Update Trigger
Instead, Delete Trigger

---First Create Table tblEmployee2
create table tblEmployee2
(
Employee_Id int,
Employee_Name nvarchar(50),
Employee_Salary decimal(10,2)
)

---Insert Record Into Table tblEmployee2
Insert into tblEmployee2 values (1,'Ben',2000)
Insert into tblEmployee2 values (2,'Clark',1200)
Insert into tblEmployee2 values (3,'John',3500)
Insert into tblEmployee2 values (4,'Sam',4000)
Insert into tblEmployee2 values (5,'Ponting',5000)

Now Create Table tblEmployee2_Audit for logging/Backup purpose of Table tblEmployee2 
------Create Table tblEmployee2_Audit

create table tblEmployee2_Audit
(
Employee_Id int,
Employee_Name nvarchar(50),
Employee_Salary decimal(10,2),
Audit_Action nvarchar(50),
Audit_Timestamp datetime
)

After Insert Trigger
---create trigger on tblEmployee2 for Insert statement

create trigger trgAfterInsert on tblEmployee2
for insert
as 
declare @Employee_Id int,
@Employee_Name nvarchar(50),
@Employee_Salary decimal(10,2),
@Audit_Action varchar(100);

select @Employee_Id= i.Employee_Id from inserted i;
select @Employee_Name= i.Employee_Name from inserted i;
select @Employee_Salary= i.Employee_Salary from inserted i;

set @Audit_Action = 'Inserted Record, After Trigger';

insert into tblEmployee2_Audit(Employee_Id,Employee_Name,Employee_Salary,Audit_Action,Audit_Timestamp)
values (@Employee_Id,@Employee_Name,@Employee_Salary,@Audit_Action,GETDATE());

Print 'After Trigger Is Fired ';


Now Try To Insert Data in tblEMployee2 Table

insert into tblEMployee2(Employee_Id, Employee_Name, Employee_Salary)  values (6,'Pam',5200)


---------------**********----------------

---After Update Trigger------

--Create trigger on tblEMployee2 for update statement
Create trigger trgAfterUpdate on tblEMployee2
for update
as
declare @Employee_Id int,
@Employee_Name nvarchar(50),
@Employee_Salary decimal(10,2),
@Audit_Action varchar(100);

select @Employee_Id= i.Employee_Id from inserted i;
select @Employee_Name= i.Employee_Name from inserted i;
select @Employee_Salary= i.Employee_Salary from inserted i;
if update(Employee_Name)
         set @Audit_Action = 'Update Record---After update trigger';
else
        set @Audit_Action = 'Update Record --After update trigger';

Insert into tblEmployee2_Audit(Employee_Id,Employee_Name,Employee_Salary,Audit_Action,Audit_Timestamp)
values (@Employee_Id,@Employee_Name,@Employee_Salary,@Audit_Action,GETDATE());

Print 'After Update Trigger Fired';

Now try to update data in tblEMployee2 table
update tblEMployee2 set Employee_Name='Paul' where Employee_Id = 2

-------******************-------------------

select * from tblEmployee2
select * from tblEmployee2_Audit

Functions In SQL Server*

We cover user defined function in this session

User Defined Function
--------*1 Scalar-Valued Function
-----------**2 Inline Table Valued Function
-----------------***3 Multi-Line Valued Function


Function Calling Syntax
--------Select OwnerName.FunctionName()-----1st type
--------Select DatabaseName.OwnerName.FunctionName()-----2nd type

Here OwnerName is Database Owner


Create Table For Perform Function On it.
create table tblEmployee1
(
Id int,
Name nvarchar(50),
Salary int 
)

Insert Value in Table tblEmployee1

insert into tblEmployee1 values (1,'John',4000)
insert into tblEmployee1 values (2,'Sara',4500)
insert into tblEmployee1 values (3,'Clark',3000)
insert into tblEmployee1 values (4,'Hyden',5000)
insert into tblEmployee1 values (5,'Ponting',8000)


-----*1 Scalar-Valued Function------------
Scalar-Valued Function Return Single Column Value only or return Single Value

Creating Scalar function for GetFullname of employee
Create function FnScalar_GetEmployeeName
(
@firstName varchar(50)
)
returns varchar(50)
as
begin
return  (select @firstName)
end

Calling Scaler Function Function
select dbo.FnScalar_GetEmployeeName(Name) from tblEmployee1


---------*******************-------------

----**2 Inline Valued Function-------
User Define inline Valued Function Return Table Variable As a result of action

Create Inline valued Function
create function FnInline_GetEmployeeName()
RETURNS TABLE
AS
RETURN (SELECT * FROM tblEmployee1)

Calling Inline Function

SELECT * FROM FnInline_GetEmployeeName()

---------*******************-------------

---***3 MultiLine Valued Function------

Create MultiLine Valued Function

Create Function For Id, orginalName, And Salary of tblEmpoyee1
Create Function FnMultiline_GetEmoloyeeDetail()
returns @Employee Table
(
Id int,
Name varchar(50),
Salary int
)
as
Begin
    Insert into @Employee Select e.Id,e.Name,e.Salary from tblEmployee1 e;
-----Now Update Salary of Clark
Update @Employee set Salary = 5000 Where Id =1;
-----It Will Update @Employee Table Runtime only Orginal Database Table
return
End

Calling  MultiLine Valued Function

Select * from FnMultiline_GetEmoloyeeDetail()

---------*******************-------------

Different Between Store Procedure and Function
1 Function Used with Select and Where clause, but StoreProdcedure  Not
Like
        Select dbo.FnScalar_GetEmployeeName(Name) as Employee Name From tblEmployee1

2 Try-Catch blog Is not in function, but in Store Procedure have

3 Function only input Parameter but in StoreProcedure both input/output parameter

4 Function Can not call StoreProdcedure, but in StoreProdcedure we call Function

5 Function Must Return Value, but StoreProdcedure is optional

What is QueryString??

Query string is used to transfer small amount of data into one web form to another web form in URL.

Query string is the part of a uniform resource locator (URL) containing data that does not fit conveniently into a hierarchical path structure. The query string commonly includes fields added to a base URL by a Web browser or other client application, for example as part of an HTML form.

Example:-

To loop through all the n variable values in a Query String:

The following request is sent:

http://www.ProgrammerSolution.com/test/names.asp?n=Ram&n=Shyam

and names.asp contains the following script:

<%
for i=1 to Request.QueryString("n").Count
  Response.Write(Request.QueryString("n")(i) & "<br>")
next
%> 

The file names.asp would display the following:

Ram  

Shyam

Step By Step Calculator Creating using C# code

using System;
using System.Windows.Forms;

namespace calculator
{
    public partial class Calculator : Form
    {
        public Calculator ()
        {
            InitializeComponent();
        }
        char operation;
        double a;

////////Button ForNumber0 to Number9 on Number Button
        private void buttonForNumber0_Click(object sender, EventArgs e)
        {

            button6.Text = "0";
            if (Display.Text == "0")
            {
                Display.Clear();
                Display.Text = Display.Text + button6.Text;

            }
            else
            {
                Display.Text = Display.Text + button6.Text;
            }
        }

        private void buttonForNumber1_Click(object sender, EventArgs e)
        {
            button4.Text = "1";
            if (Display.Text == "0")
            {
                Display.Clear();
                Display.Text = Display.Text + button4.Text;

            }
            else
            {
                Display.Text = Display.Text + button4.Text;
            }
        }

        private void buttonForNumber2_Click(object sender, EventArgs e)
        {
            button7.Text = "2";
            if (Display.Text == "0")
            {
                Display.Clear();
                Display.Text = Display.Text + button7.Text;

            }
            else
            {
                Display.Text = Display.Text + button7.Text;
            }

        }

        private void buttonForNumber3_Click(object sender, EventArgs e)
        {
            button12.Text = "3";
            if (Display.Text == "0")
            {
                Display.Clear();
                Display.Text = Display.Text + button12.Text;

            }
            else
            {
                Display.Text = Display.Text + button12.Text;
            }

        }

        private void buttonForNumber4_Click(object sender, EventArgs e)
        {
            button3.Text = "4";
            if (Display.Text == "0")
            {
                Display.Clear();
                Display.Text = Display.Text + button3.Text;

            }
            else
            {
                Display.Text = Display.Text + button3.Text;
            }

        }

        private void buttonForNumber5_Click(object sender, EventArgs e)
        {

            button8.Text = "5";
            if (Display.Text == "0")
            {
                Display.Clear();
                Display.Text = Display.Text + button8.Text;

            }
            else
            {
                Display.Text = Display.Text + button8.Text;
            }
        }

        private void buttonForNumber6_Click(object sender, EventArgs e)
        {
            button13.Text = "6";
            if (Display.Text == "0")
            {
                Display.Clear();
                Display.Text = Display.Text + button13.Text;

            }
            else
            {
                Display.Text = Display.Text + button13.Text;
            }

        }

        private void buttonForNumber7_Click(object sender, EventArgs e)
        {
            button2.Text = "7";
            if (Display.Text == "0")
            {
                Display.Clear();
                Display.Text = Display.Text + button2.Text;

            }
            else
            {
                Display.Text = Display.Text + button2.Text;
            }

        }

        private void buttonForNumber8_Click(object sender, EventArgs e)
        {
            button9.Text = "8";
            if (Display.Text == "0")
            {
                Display.Clear();
                Display.Text = Display.Text + button9.Text;

            }
            else
            {
                Display.Text = Display.Text + button9.Text;
            }

        }

        private void buttonForNumber9_Click(object sender, EventArgs e)
        {
            button14.Text = "9";
            if (Display.Text == "0")
            {
                Display.Clear();
                Display.Text = Display.Text + button14.Text;

            }
            else
            {
                Display.Text = Display.Text + button14.Text;
            }

        }
///BackSpace Button Coding
        private void buttonForBackSpace_Click(object sender, EventArgs e)
        {
            if ((String.Compare(Display.Text, " ") < 0))
            {
                Display.Clear();
                Display.Text = "0";


            }
            else
            {
                Display.Text = Display.Text.Substring(0, Display.Text.Length - 1);
                if ((String.Compare(Display.Text, " ") < 0))
                {
                    Display.Text = "0";
                }


            }
            

        }
/////Button For Decimal Point
        private void buttonForDecimal_Click(object sender, EventArgs e)
        {
            button11.Text = ".";
            if (Display.Text == "0")
            {
                Display.Clear();
                Display.Text = Display.Text + button11.Text;

            }
            else
            {
                Display.Text = Display.Text + button11.Text;
            }
        }

/////Button For Double Zero 00 Point
        private void button5_Click(object sender, EventArgs e)
        {
            button5.Text = "00";
            if (Display.Text == "0")
            {
                Display.Clear();
                Display.Text = Display.Text + button5.Text;

            }
            else
            {
                Display.Text = Display.Text + button5.Text;
            }

////On Addition Button Coding
        private void buttonAddition_Click(object sender, EventArgs e)
        {
            a = Convert.ToDouble(Display.Text);
            operation = '+';
            Display.Text = "0";
        }
        
////On Subtraction Button Coding
        private void buttonSubtraction _Click(object sender, EventArgs e)
        {
            a = Convert.ToDouble(Display.Text);
            operation = '-';
            Display.Text = "0";

        }

////On Multiplication Button Coding
        private void buttonMultiplication _Click(object sender, EventArgs e)
        {
            a = Convert.ToDouble(Display.Text);
            operation = '*';
            Display.Text = "0";

        }

////On DivisonButton Coding
        private void buttonDivison_Click(object sender, EventArgs e)
        {
            a = Convert.ToDouble(Display.Text);
            operation = '/';
            Display.Text = "0";

        }
////On Remainder Button Coding
        private void buttonRemainder _Click(object sender, EventArgs e)
        {
            a = Convert.ToDouble(Display.Text);
            operation = '%';
            Display.Text = "0";

        }

////On Equal = button coding

        private void buttonEqual_Click(object sender, EventArgs e)
        {
            if (Display.Text == "0")
            {
                Display.Text = "0";
            }
            else
            {
                switch (operation)
                {

                    case ('+'):
                        double add = Convert.ToDouble(Display.Text);
                        double addition = Convert.ToDouble(a + add);
                        Display.Text = Convert.ToString(addition);

                        break;
                    case ('-'):
                        double sub = Convert.ToDouble(Display.Text);
                        double substraction = Convert.ToDouble(a - sub);
                        Display.Text = Convert.ToString(substraction);
                        break;

                    case ('*'):
                        double multi = Convert.ToDouble(Display.Text);
                        double multiplication = Convert.ToDouble(a * multi);
                        Display.Text = Convert.ToString(multiplication);

                        break;

                    case ('/'):
                        double div = Convert.ToDouble(Display.Text);
                        double division = Convert.ToDouble(a / div);
                        Display.Text = Convert.ToString(division);

                        break;

                    case ('%'):
                        double m = Convert.ToDouble(Display.Text);
                        double mod = Convert.ToDouble(a % m);
                        Display.Text = Convert.ToString(mod);

                        break;
                }
            }
            }
        }
    }
}

Step By Step Calculator Creating using C#

Step By Step Creating Calculator Using C# Visual Studio.?

Ans.:

First Design Your Calculator Like This.

SQL Select Statement.?

The SELECT Statement is used to fetch the data from Database.

Select statement is used to select data from database.
The Result is stored in a Result Table, called Result-Set.

SQL Select Statement
SELECT ColumnName1, ColoumnName1 From  TableName;
OR
Select  *  from TableName ;

Example:=-
                  Table Name -  Student
id               Name         Age         Gender
 1                Ram            15              Male
 2                Bob             18             Male
 3                julie            18            Female
4                  john           22              Male


Write Query:-  Select * from Student;
OutPut 
 id               Name         Age         Gender
 1                Ram            15              Male
 2                Bob             18             Male
 3                julie            18            Female
4                  john           22              Male

 Write Query:- Select id, Name, Age From Student Where id= 1;
Output
 id               Name         Age        
 1                Ram            15         

Comments in SQL.

Comments can be used to explain section of statements.

Single Line Comments

-----Select All data from database Table
Select * from  TableName.

Multi Line Comments

/* Select all column form the database table*/
Select * form Tablename

Note:- Here TableName is our TableName like Student,Employee

"SQL Tutorial"

What is SQL.?

SQL is a standard language for accessing databases.
Our SQL tutorial will teach you how to use SQL to access and manipulate data in: MySQL, SQL Server, Access, Oracle, Sybase, DB2, and other database systems.

What is SQL Server.?
SQL Server is a relational database management system (RDBMS) from Microsoft that's designed for the enterprise environment. SQL Server runs on T-SQL , a set of programming extensions from Sybase and Microsoft that add several features to standard SQL, including transaction control, exception and error handling, row processing, and declared variables.

Use of SQL.?

• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views

 Example:- Select * from TableName

Commands in SQL.?

• SELECT - extracts data from a database
• UPDATE - updates data in a database
• DELETE - deletes data from a database
• INSERT INTO - inserts new data into a database
• CREATE DATABASE - creates a new database
• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index