launch sqlserver, MicroSoft visual studio, Remote login, iis from run command.

SQL Server 2005:

To launch SQL Server Management Studio 2005 from Run Command
Click Start  > Run  > sqlwb.exe


 ============================

SQL Server 2008/2012:
To launch SQL Server Management Studio 2008 or SQL Server Management Studio 2012 from Run Command
Click Start  > Run  > SSMS.exe





 ============================
 MicroSoft Visual Studio:

To launch MicroSoft Visual Studio from Run Command
Click Start  > Run  > devenv

 ============================
  Remote Login:
To launch Reomote Login from Run Command
click start > Run > mstsc

 ============================
 
  IIS Admin:
To launch IIS Admin from Run Command
click start > Run > inetmgr
 ============================  

URL Writting rule Example

              URL Writting rule Example:===

           example:---

         1.  redirect  store/plpcategory.aspx?cat=xyz   to    /xyz
          then
          2. rewrite /xyz to   store/plpcategory.aspx?cat=xyz
          


       1st step.

               <rule name="plpcategoryUserFriendlyURL1" stopProcessing="true">
                    <match url="^store/plpcategory\.aspx$" />
                    <conditions>
                        <add input="{REQUEST_METHOD}" pattern="^POST$" negate="true" />
                        <add input="{QUERY_STRING}" pattern="^cat=([^=&amp;]+)$" />
                    </conditions>
                    <action type="Redirect" url="{C:1}" appendQueryString="false" />
                </rule>



=================================================================

      2nd step.


                <rule name="itemsRewriteUserFriendlyURL1" stopProcessing="true">
                    <match url="^([^/]+)/?$" />
                    <conditions>
                        <add input="{REQUEST_FILENAME}" matchType="IsFile" negate="true" />
                        <add input="{REQUEST_FILENAME}" matchType="IsDirectory" negate="false" />
                        <add input="{REQUEST_URI}" pattern="^/(online|events|global|fabrics|fashion|community|embroidery|promotions|testimonials|store|shop|secure|account|pay|controls|help|pages|xml|accessories|dberror|images|include|instore|masters)" negate="true" />
                    </conditions>
                    <action type="Rewrite" url="store/plpcategory.aspx?cat={R:1}" />
                </rule>

Create connection file and fill datatable with data in asp.net

 Create connection file and fill data table with data:-----------

 file name : DataAccess.cs
 ======================


using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Web.Configuration;

namespace DataAccessNamespace
{
    /// <summary>
    /// Summary description for DataAccess
    /// </summary>

    public class DataAccess
    {
        //Variables & public properties ***********************************
        private string connectionString = "";
        private int recordCount = -1;

        /// <summary>
        /// Property: gets count of records retrieved or changed
        /// </summary>
        public int Count
        {
            get
            {
                return recordCount;
            }
        }

        //Class constructor is executed when object is initialized ***********
        /// <summary>
        /// Connection string name in web.config file is required to initialize DataAccess
        /// </summary>
        /// <param name="ConnectionName">Name of web.config connection string</param>
        public DataAccess()
        {
            //if (WebConfigurationManager.ConnectionStrings[ConnectionName] == null)
            //{
            //    throw new Exception("Cannot find connection string named '" +
            //       ConnectionName + "' in web.config");
            //}
            //Get connection string from web.config.
            //connectionString = WebConfigurationManager.ConnectionStrings[ConnectionName].ConnectionString;
            connectionString = @"Data Source=USF142\SQLEXPRESS;Initial Catalog=demo;Integrated Security=True;";
        }
        /// <summary>
        /// Executes SELECT statement and returns results in dataTable
        /// </summary>
        /// <param name="SQL">Select SQL statement</param>
        /// <returns></returns>
        public DataTable FillDataTable(string SQL)
        {
            SqlConnection _objConn = new SqlConnection(connectionString);
            SqlDataAdapter objAdapter = new SqlDataAdapter(SQL, _objConn);
            DataTable dt = new DataTable();
            try
            {
                objAdapter.Fill(dt);
            }
            catch (SqlException ex)
            {
                throw new Exception("Error in SQL:" + SQL, ex);
            }
            catch (Exception ex)
            {
                throw ex; //Bubbling exception up to parent class
            }
            finally
            {
                _objConn.Close();
            }

            recordCount = dt.Rows.Count;
            return dt;
        }

        /// <summary>
        /// Executes "non-query" SQL statements (insert, update, delete)
        /// </summary>
        /// <param name="SQL">insert, update or delete</param>
        /// <returns>Number of records affected</returns>
        public int ExecuteNonQuery(string SQL)
        {
            SqlConnection _objConn = new SqlConnection(connectionString);
            try
            {
                _objConn.Open();
                SqlCommand objCmd = new SqlCommand(SQL, _objConn);
                recordCount = objCmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw new Exception("Error in SQL:" + SQL, ex);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message); //Rethrowing exception up to parent class
            }
            finally { _objConn.Close(); }

            return recordCount;
        }

        public int ExecuteScalar(String SQL)
        {
            SqlConnection _objConn = new SqlConnection(connectionString);
            int intID;
            try
            {
                _objConn.Open();
                SqlCommand objCmd = new SqlCommand(SQL, _objConn);
                intID = Convert.ToInt32(objCmd.ExecuteScalar());
            }
            catch (SqlException ex)
            {
                throw new Exception("Error in SQL:" + SQL, ex);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message); //Rethrowing exception up to parent class
            }
            finally { _objConn.Close(); }
            return intID;
        }


    }
}

==============================
create Default2.aspx+cs file

aspx.cs file


using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using DataAccessNamespace;
public partial class demo_Default2 : System.Web.UI.Page
{
   
    protected void Page_Load(object sender, EventArgs e)
    { 

     
        DataAccess myDA = new DataAccess();

        //Populate dataTable and bind to GridView Control
        string strSQL = "Select * from employee";

        GridView1.DataSource = myDA.FillDataTable(strSQL);
        GridView1.DataBind();
    }
}

.aspx file:-----------

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="demo_Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" >
        </asp:GridView>
     
    </div>
    </form>
</body>
</html>

C# Delegates

 A Simple Example of Delegates

using System;
public class Test
 {
  public delegate int CalculationHandler(int x, int y);
  static void Main(string[]  args)
   {
     Math math = new Math();
    //create a new instance of the delegate class

     CalculationHandler sumHandler = new CalculationHandler(math.Sum);
    //invoke the delegate
      int result = sumHandler(8,9);
     Console.WriteLine("Result is: " + result);
     }
   }

 public class Math
  {
    public int Sum(int x, int y)
     {
       return x + y;
     }
  }

Authentication through web.config in ASP.net 3.5

 add this on web.config file.

<authentication mode="Forms">

        <forms loginUrl="default.aspx">

          <credentials passwordFormat="Clear">

            <user name="a" password="b" />

            <user name="c" password="d" />

          </credentials>

        </forms>

      </authentication>

=======================

 <h3>Login</h3>

  <asp:Label id="Msg" ForeColor="maroon" runat="server" /><br />

  Username: <asp:Textbox id="UsernameTextbox" runat="server" /><br />
  Password: <asp:Textbox id="PasswordTextbox" runat="server" TextMode="Password" /><br />

  <asp:Button id="LoginButton" Text="Login" OnClick="Login_OnClick" runat="server" />

========================

public void Login_OnClick(object sender, EventArgs args)
    {
        if (FormsAuthentication.Authenticate(UsernameTextbox.Text, PasswordTextbox.Text))
            Msg.Text = "done";
        else
            Msg.Text = "Login failed. Please check your user name and password and try again.";
    }

use of output parameter in sql sever

--use of output parameter in sql sever

create procedure contEmp
@count varchar(50) output
as
select @count=count(employee_id) from employee
-----------------------------
 declare @count varchar(50)
 exec contEmp @count output
 select @count
-------------------------------------------

SQL Practise

SQL Practise content Start.:---------

--creation of a table start

CREATE TABLE [dbo].[department](

[department_id] [int] NOT NULL,

[name] [nvarchar](50) NOT NULL,

[location_id] [int] NOT NULL

) ON [PRIMARY]

=====================

CREATE TABLE [dbo].[job](

[job_id] [int] NOT NULL,

[Function] [varchar](50) NOT NULL

) ON [PRIMARY]

==========================

CREATE TABLE [dbo].[location](

[location_id] [int] NOT NULL,

[regional_group] [nvarchar](50) NULL

) ON [PRIMARY]

============================

CREATE TABLE [dbo].[Employee](

[employee_id] [int] NOT NULL,

[last_name] [nvarchar](50) NULL,

[first_name] [nvarchar](50) NOT NULL,

[middle_name] [nvarchar](50) NULL,

[job_id] [int] NOT NULL,

[manager_id] [int] NOT NULL,

[hiredate] [datetime] not null,

[salary] [int] NOT NULL,

[comm] [int] NULL,

[department_id] [int] NOT NULL

) ON [PRIMARY]

--creation of a table end

--===============================

--insertion in tables

--===============================

insert into location

select 122,'new york'

union

select 123,'dallas'

union

select 124,'chicago'

union

select 167,'boston'

================================

insert into department

select 10,'accounting',122

union

select 20,'research',124

union

select 30,'sales',123

union

select 40,'operations',167

====================

insert into job

select 667,'clerk'

union

select 668,'staff'

union

select 669,'analyst'

union

select 670,'salesperson'

union

select 671,'manager'

union

select 672,'president'

==============================

insert into employee

select 7369,'Smith','John','Q','667','7902','17-DEC-84','800',null,'20'

union

select 7499,'Allen','Kevin','J','670','7698','20-FEB-85','1600','300','30'

union

select 7505,'Doyle','Jean','K','671','7839','04-APR-85','2850',null,'30'

union

select 7506,'Dennis','Lynn','S','671','7839','15-MAY-85','2750',null,'30'

union

select 7507,'Baker','Leslie','D','671','7839','10-JUN-85','2200',null,'40'

union

select 7521,'Wark','Cynthia','D','670','7698','22-FEB-85','1250','500','30'

--insertion in tables end

NOW Practise Session start 

--list all the location detail

select * from location

--list all the dempartment detail

select * from department

--list all the job detail

select * from job

--list all the employee detail

select * from employee

--list out the firstname ,lastname salary commission of employees

select first_name, last_name,salary,comm from employee

select employee_id as [ID of the employee],last_name as [name of the employee],department_id as [department Id]

from employee

--list out the employee anual salary of all employee

select last_name ,(12*salary) as [anuual salary] from employee

--where conditions

--list the detail about smith

select * from employee where last_name='smith'

--list the department 20 detail

select * from employee where department_id=20

--list out the detail of the employee whose salary between 800 and 2500

select * from employee where salary between 800 and 2500

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

--list out the detail of the employee whose department_id is 10 and 20

select * from employee where department_id=10 or department_id=20

select * from employee where department_id in(10,20)

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

--list out the detail of the employee who not working is 10,30

select * from employee where department_id not in(10,30) 

--list out the employee detail whose name start form s

select * from employee where last_name like 's%'

--list out the empoloyee whose last_name start with s and end with h

select * from employee where last_name like 's%h'

----------

--list out the employee whose name is of five char and start with s

select * from employee where Len(last_name)=5 and last_name like 's%'

select * from employee where last_name like 's____'

-----------

--list out the employee  whose department is 30 and salary is grater then 1700

select * from employee where department_id=30 and salary>1700

--whose not geting commission

select * from employee where comm is NULL or comm=0

--ascending order based on employee id

select employee_id,last_name from employee order by employee_id asc

--descending order based on salary

select employee_id,last_name from employee order by salary desc

--base on name and salary

select * from employee order by last_name asc, salary desc

--based on lastname asc department desc

select * from employee order by last_name asc , department_id desc

--group by and having clause

--how many employee working in diffrent department wise

select count(employee_id) as [number of employee],department_id from employee group by department_id

--list out department wise min,max,avg salary

select count(*), max(salary) as [max sal],avg(salary) as [avg salary], min(salary) as [min sal], department_id

from employee group by department_id 

--list out num of employee joind in every month

select count(*) as [num of employee] ,datename(month,hiredate) as [month] from employee group by datename(month,hiredate) order by [month]

select count(*) as [num of employee] ,datename(month,hiredate) as [month] from employee group by datename(month,hiredate),datepart(month,hiredate) order by datepart(month,hiredate)

--list out num of employee joined in each month of the year

select count(*) as [num of employee], (datename(month,hiredate)+'/'+datename(year,hiredate))as [month-year] from employee group by (datename(month,hiredate)+'/'+datename(year,hiredate)) 

select count(*) as [num of employee], (datename(month,hiredate)+'/'+datename(year,hiredate))as [month-year] from employee group by (datename(month,hiredate)+'/'+datename(year,hiredate)),datename(year,hiredate) order by datename(year,hiredate)  

select count(*) as [num of employee], datename(month,hiredate)+'/'+datename(year,hiredate)as [month-year] from employee group by datename(month,hiredate),datename(year,hiredate)

--list out department id having more then 3 employee

select count(*)as [num of employee],department_id from employee group by department_id having count(*)>3

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

--how many employee in february month

select count(*) as [num of employee],datename(month,hiredate) month_char, datepart(month,hiredate) month_num from employee group by datepart(month,hiredate),datename(month,hiredate) having datepart(month,hiredate)=2

select count(*) as [num of employee],datename(month,hiredate) month_char, datepart(month,hiredate) month_num from employee where datepart(month,hiredate)=2 group by datepart(month,hiredate),datename(month,hiredate) 

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

--join in feb and jun

select count(*) as [num of employee],datename(month,hiredate),datepart(month,hiredate) from employee group by datepart(month,hiredate),datename(month,hiredate) having datepart(month,hiredate) in(2,6)

--joined in 1985

select count(*)as [num of employee],datename(year,hiredate)as [year] from employee group by datename(year,hiredate) having datename(year,hiredate)=1985

--each month in 1985

select count(*) as [num of employee], datename(month,hiredate) as month_char, datename(year,hiredate) from employee group by datename(month,hiredate),datename(year,hiredate) having datename(year,hiredate)=1985

--feb 1985

select count(*)as [num of employee], datename(month,hiredate)as month_char,datepart(month,hiredate) as month_num, datename(year,hiredate) as [year] from employee group by datename(year,hiredate),datepart(month,hiredate),datename(month,hiredate) having datepart(month,hiredate)=2 and datename(year,hiredate)=1985

--grater then 2 employee joined in feb 1985

select count(*)as [num of employee],department_id from employee group by datename(year,hiredate),datepart(month,hiredate),datename(month,hiredate),department_id having datepart(month,hiredate)=2 and datename(year,hiredate)=1985 and count(*)>=2

----------

--=============

--subquery

--display the employee who get the max salary

select * from employee where salary=(select max(salary) from employee)

--working in sales department

select * from employee where department_id=(select department_id from department where name='sales')

--working in clark department

select * from employee where job_id=(select job_id from job where  [function]='clerk')

--working in dallas

select * from employee where  department_id=( select department_id from department where location_id=(select location_id from location where regional_group='dallas'))

----------

--num of employee working in sales

select count(*) from employee group by department_id having department_id=(select department_id from department where name='sales')

select count(*) from employee  where department_id=(select department_id from department where name='sales')group by department_id

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

------

--update employee salary working as clerk with 10%

select * from employee where job_id=(select job_id from job where [function]='clerk')

update employee set salary=(salary*10/100)+salary where  job_id=(select job_id from job where [function]='clerk')

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

--delete the employee working in opertations department

select * from employee where department_id=(select department_id from department where name='operations')

delete  from employee where department_id=(select department_id from department where name='operations')

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

--==============================

--display second highest salary

select top 1  * from employee where salary in(select top 2 salary from employee order by salary desc) order by salary asc

select  * from employee where salary=(select max(salary) from employee where salary <(select max(salary) from employee))

--display the nth salary

WITH Salaries AS

(

select  row_number() over (order by salary desc)as row,salary from employee 

)

SELECT

  Salary

FROM

  Salaries

WHERE

   row = 6

  

   

   ----

   --list out the employee who earn more then every employee employee in 30

   select * from employee where salary>(select max(salary) from employee where department_id=30)

   select * from employee where salary> all(select salary from employee where department_id=30)

   ------

    --list out the employee who earn more then lowest every employee employee in 30

   select * from employee where salary>(select min(salary) from employee where department_id=30)

   select * from employee where salary> any(select salary from employee)

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

   -- select last_name from department,employee e where department.department_id=e.department_id

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

    --find out whose department has not employee

    select last_name,department_id from employee e where not exists(select department_id from department d where d.department_id=e.department_id )--???

    select name  from department d where  not exists(select d.name from employee e where d.department_id=e.department_id)--???

   

   --co related sub query\

   --find out the employee who earn grater then the avr salary of there department

   select * from employee e where  salary>(select avg(salary)from employee where department_id=e.department_id)

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

   

   --joins 

   --list out employee with there department name

   select e.employee_id,last_name, e.department_id, d.name from employee e, department d where e.department_id=d.department_id

   select e.employee_id,e.last_name,e.department_id,d.name from employee e inner join department d on e.department_id=d.department_id

   

   select * from employee e inner join department d on e.department_id=d.department_id

   

   --list out employee with designation

   select employee_id,last_name,job.job_id,[function]  from employee inner join job on employee.job_id=job.job_id

   

   

   --===============

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

   --display employee with there department and regional group

   select employee_id,last_name,name,regional_group from employee e

   inner join department d on e.department_id=d.department_id

   inner join location l on d.location_id=l.location_id

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

   

   select e.employee_id,e.last_name,e.name,l.regional_group from 

   (select e.employee_id,last_name,d.name,e.department_id,d.location_id from employee e inner join department d on e.department_id=d.department_id) e

   inner join location l on e.location_id=l.location_id

   select employee_id,last_name,name,regional_group from employee e,department d, location l where

   e.department_id=d.department_id and l.location_id=d.location_id

--============

--how many employee who working in diffrent department and display departmentname

select count(*)[num of emploee],employee.department_id,name from employee

inner join department on employee.department_id=department.department_id

group by employee.department_id,department.name

-----

select count(*)[num of employee],name from employee,department 

where employee.department_id=department.department_id

group by name

--how many employee working in sales department

select count(*)[num of employee],name from employee inner join department

 on employee.department_id=department.department_id

 where name='sales' group by name

--which department having more then 3 employee and display the department name

select  name,count(*) from employee e inner join department d 

on e.department_id=d.department_id

group by name

having count(*)>3 order by name

select * from location

select * from department

how many jobs in the orgnigation with designations

select [function],count(*) [num of employee] from employee e,job j 

where e.job_id=j.job_id

group by j.[function]

--how many employee working in chicago

select count(*),regional_group from employee e

inner join department d on e.department_id=d.department_id

inner join location l on l.location_id=d.location_id

where l.regional_group='chicago'

group by  l.regional_group

--non equi join

?

--self join

select * from employee

select * from employee

--display the employee detail with manager name

select e.employee_id,e.last_name,e.manager_id,m.last_name  from employee e,employee m

where e.manager_id=m.employee_id

select e.employee_id,e.last_name,e.manager_id ,f.last_name from employee e inner join

employee f on  e.manager_id =f.employee_id

--display employee salary who earn more salary then his mangager salary

select e.employee_id,e.last_name,e.manager_id ,m.last_name from employee e inner join

employee m on  e.manager_id =m.employee_id

where e.salary>m.salary

--show the number of employee working under every manager

select count (e.employee_id),m.last_name as [manager name] from employee e

inner join employee m

on e.manager_id=m.employee_id

group by m.last_name

--outer join

select * from employee

insert into employee values(

7371,'singh','anish','kumar',667,7370,'2/2/1990',15000,null,50

)

select last_name,d.department_id,d.name from employee e

right outer join department d

on e.department_id=d.department_id

select last_name,d.department_id,d.name from employee e

left outer join department d

on e.department_id=d.department_id

select last_name,d.department_id,d.name from employee e

right outer join department d

on e.department_id=d.department_id 

where d.name in('sales','operation')

select * from job

select distinct j.[function],d.department_id from employee e

inner join department d

on e.department_id=d.department_id

inner join job j

on j.job_id=e.job_id

select  j.[function],d.department_id from employee e

inner join department d

on e.department_id=d.department_id

inner join job j

on j.job_id=e.job_id

--=========================

NOW Practise Session End

SQL Practise content end.:---------