How to use multiple query in single Stored Procdure in Sql Server

creating database test 

create database test

after creating database ,create table  Student in database test

use  test

Create table Student (RollNo int primary key,Name nvarchar(50),MobileNo nvarchar(50),dob nvarchar(50))

creating single procedure allquery for multiple query (insert,delete,update,selete) for Student table

Create proc allquery @op nvarchar(50)='g',@RollNo int=0,@Name nvarchar(50)=null,@MobileNo nvarchar(50)=null,@dob nvarchar(50)=''

as

begin

if @op='insert'

begin

insert into Student values(@RollNo,@Name,@MobileNo,@dob)

end

if @op='update'

begin

update Student set Name=@Name,MobileNo=@MobileNo,dob=@dob where RollNo=@RollNo

end

if @op='delete'

begin

delete from Student where RollNo=@RollNo

end

if @op='select'

begin

select * from Student where RollNo=@RollNo

end

else

begin

select * from Student

end

end

1.  executing procedure for insertting data in Student table

exec allquery 'insert',3,'Udayan Sir','0750325345','02-06-1987'

2. executing  procedure for updating data in Student table

  exec allquery 'update',2,'Kush Tiwari','08826382483','11-AUG-1987'

3. executing  procedure for select data in Student table

    exec allquery 'select',1

4. executing  procedure for delete data in Student table  

   exec allquery 'delete',4

5. executing  default procedure for  Student table  

      exec allquery

How  to  use above  store procedure in Asp.net page:

Frist we  create connectionstring in <connectionStrings> with connection name="kush"

Write code in web.config…………………………………………………………………………………………

<configuration>

  <connectionStrings>

    <add name="kush" connectionString="Data Source=KUSH-PC;Initial Catalog=test;Integrated Security=True"providerName="System.Data.SqlClient"/>

  </connectionStrings>

  <system.web>

    <compilation debug="true" targetFramework="4.0"/>

  </system.web>

</configuration>

We  are  using  insert,delete,update,select  all  work  by dropdown list  whose  name  is DropDownList1 and  all coding on DropDownList1_SelectedIndexChanged

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

public partial class StoreProcwithMultipleQuery : System.Web.UI.Page

{

    SqlConnection con;

    SqlCommand cmd;

    SqlDataReader dr;

    protected void Page_Load(object sender, EventArgs e)

    {

        con = new SqlConnection(ConfigurationManager.ConnectionStrings["kush"].ConnectionString);

      

    }

 

    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)

    {

        if (DropDownList1.SelectedIndex == 0)

        {

            txtrollno.Text="";

            txtname.Text="";

            txtmobileno.Text = "";

            txtdob.Text = "";

        }

       

        if (DropDownList1.SelectedIndex == 1)

        {

            cmd = new SqlCommand("allquery", con);

            con.Open();

            cmd.Parameters.AddWithValue("@op","Insert");

            cmd.Parameters.AddWithValue("@RollNo", Convert.ToInt32(txtrollno.Text));

            cmd.Parameters.AddWithValue("@Name", txtname.Text);

            cmd.Parameters.AddWithValue("@MobileNo", txtmobileno.Text);

            cmd.Parameters.AddWithValue("@dob", txtdob.Text);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.ExecuteNonQuery();

            con.Close();

            GridView1.DataBind();

            Message.Text = "Add successfully Record";

        }

        if (DropDownList1.SelectedIndex == 2)

        {

            cmd = new SqlCommand("allquery", con);

            con.Open();

            cmd.Parameters.AddWithValue("@op", "Update");

            cmd.Parameters.AddWithValue("@RollNo", Convert.ToInt32(txtrollno.Text));

            cmd.Parameters.AddWithValue("@Name", txtname.Text);

            cmd.Parameters.AddWithValue("@MobileNo", txtmobileno.Text);

            cmd.Parameters.AddWithValue("@dob", txtdob.Text);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.ExecuteNonQuery();

            con.Close();

            Message.Text = "Update successfully Record";

        }

        if (DropDownList1.SelectedIndex == 3)

        {

            cmd = new SqlCommand("allquery", con);

            con.Open();

            cmd.Parameters.AddWithValue("@op", "Delete");

            cmd.Parameters.AddWithValue("@RollNo", Convert.ToInt32(txtrollno.Text));

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.ExecuteNonQuery();

            con.Close();

            Message.Text = "Delete  Record successfully";

        }

        if (DropDownList1.SelectedIndex == 4)

        {

            con.Open();

            cmd = new SqlCommand("allquery", con);

            cmd.Parameters.AddWithValue("@op", "Select");

            cmd.Parameters.AddWithValue("@RollNo", Convert.ToInt32(txtrollno.Text));

            cmd.CommandType = CommandType.StoredProcedure;

            dr = cmd.ExecuteReader();

            if (dr.Read())

            {

                txtname.Text = dr["Name"].ToString();

                txtmobileno.Text = dr["MobileNo"].ToString();

                txtdob.Text = dr["dob"].ToString();

    

       }

            con.Close();

        }

        if (DropDownList1.SelectedIndex == 5)

        {

            con.Open();

            cmd = new SqlCommand("allquery", con);

            cmd.Parameters.AddWithValue("@op", " ");

            cmd.CommandType = CommandType.StoredProcedure;

            dr = cmd.ExecuteReader();

            dr.Read();

            GridView1.DataSource = dr;

            GridView1.DataBind();

            con.Close();

        }

    }

}