santosh-asp tutorial

Multiple type Stored Procedures

What is Multiple type Stored Procedures? By Multiple type Stored Procedure we can perform multiple operations. If we want to perform Insert, Update, Delete and Search operations in SQL SERVER with ASP.NET then we have to create only single Stored Procudure.

Now, we are using SQL SERVER, so Open SQL SERVER and writer the following code:-

  

CREATE DATABASE dbSantoshTest

Use dbSantoshTest

CREATE TABLE tblStudent
(
Name varchar(100), Rollno int primary key,Branch varchar(50)
)

Now, we have to create only one Stored Procedure

  

CREATE PROC USP_MultipleStoredProc
@Name varchar(100), @Rollno int, @Branch varchar(50),@Operation varchar(30),@Status varchar(50) output

AS
BEGIN

  if(@Operation='Insert')
  begin 
  Insert into tblStudent values(@Name,@Rollno,@Branch)
  set @Status='Record has beeen inserted.'
  end

 if(@Operation='Update')
  begin 
   Update tblStudent set Name=@Name,Branch=@Branch where Rollno=@Rollno
  set @Status='Record has beeen updated.'
  end
  
  
  if(@Operation='Delete')

  
  begin 
   Delete from tblStudent where Rollno=@Rollno
  set @Status='Record has beeen deleted.'
  end
  
  
  if(@Operation='Search')
  begin 
   Select * from tblStudent where Rollno=@Rollno
  
  end
  
  if(@Operation='SearchAll')
  begin 
   Select * from tblStudent
 
  end

END

Step 1:- Now, Open Visual Studio and Go--->File--->New--->Select--->Web Site---->a Dailog Box--->Open---->In this Select ASP.NET Empty Web Site and give named StoredProcedure and click Ok button as shown in below:-

Step 2:- Now, Go to Solution Explorer and Ringht click on StoredProcedure project name--->Select--->Add--->Add New Item--->Dailog box open in which select Webform and give it named as Default.aspx as shown in below:-

Step 3:- Now, write the following code in Default.aspx page:-

  

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
     <style>

        #div {
            background-color:#0094ff;
            font-family:Verdana;
            font-size:16px;
            color:white;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <center> 
    <div id="div">
        <h1> Multiple Stored Procedures Test By santosh-asp.com</h1>
    <table style="height: 294px; width: 324px; background-color: #9966FF;">
        <tr> <td colspan="2"> <asp:Label ID="lblMessage" runat="server" Font-Bold="true"></asp:Label></td></tr>
          <tr> <td>Operation </td> <td> <asp:DropDownList ID="ddlOperation" runat="server" Width="150px" Font-Bold="true">
            <asp:ListItem Value="0">Select Operation</asp:ListItem>
            <asp:ListItem Value="1">Insert</asp:ListItem>
            <asp:ListItem Value="2">Update</asp:ListItem>
            <asp:ListItem Value="3">Delete</asp:ListItem>
            <asp:ListItem Value="4">Search</asp:ListItem>
            <asp:ListItem Value="5">SearchAll</asp:ListItem>
            </asp:DropDownList> </td> </tr>
        <tr> <td>Name </td> <td> <asp:TextBox ID="txtName" runat="server"></asp:TextBox> </td> </tr>
        <tr> <td>Roll Number </td> <td> <asp:TextBox ID="txtRollno" runat="server"></asp:TextBox> </td> </tr>
        <tr> <td>Branch </td> <td> <asp:DropDownList ID="ddlBranch" runat="server" Width="150px" Font-Bold="true">
            <asp:ListItem Value="0">----Select Branch----</asp:ListItem>
            <asp:ListItem Value="1">IT</asp:ListItem>
            <asp:ListItem Value="2">ECE</asp:ListItem>
            <asp:ListItem Value="3">ME</asp:ListItem>
            <asp:ListItem Value="4">MCA</asp:ListItem>
            <asp:ListItem Value="5">CSE</asp:ListItem>
            </asp:DropDownList> </td> </tr>
        <tr> <td colspan="2"> <asp:Button ID="btnOperation" runat="server" Text="Run Operation " Font-Bold="true" Width="300px" Height="30px" OnClick="btnOperation_Click" /> </td> </tr>
        

    </table>

        <asp:GridView ID="gvDetail" runat="server" BackColor="White" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" CellPadding="4">
            <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
            <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
            <RowStyle BackColor="White" ForeColor="#330099" />
            <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
            <SortedAscendingCellStyle BackColor="#FEFCEB" />
            <SortedAscendingHeaderStyle BackColor="#AF0101" />
            <SortedDescendingCellStyle BackColor="#F6F0C0" />
            <SortedDescendingHeaderStyle BackColor="#7E0000" />
        </asp:GridView>
    </div>

            </center>
    </form>
</body>
</html>

Step 4:- Now, create connection string in Web.config

  

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="DBCS" connectionString="Data Source=PS-PC\SANTOSH; Database=dbSantoshTest; Integrated Security=true;" providerName="System.Data.SqlClient"/>
    
  </connectionStrings>
  
  <system.web>
    <compilation debug="true" targetFramework="4.0"/>
  </system.web>
</configuration>

Step 5:- Now, Go to code behind page i.e. Default.aspx.cs page:- and write the following code:-

  

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Drawing;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


public partial class Default : System.Web.UI.Page
{
    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindGridView();
        }
    }

    #region "Function"

    private void BindGridView()  //Bind Student record in GridView
    {
        DataSet dataset=new DataSet();
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("USP_MultipleStoredProc", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Name", "");
                cmd.Parameters.AddWithValue("@Rollno", 1);
                cmd.Parameters.AddWithValue("@Branch", "");
                cmd.Parameters.AddWithValue("@Operation","SearchAll");
                cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.VarChar, -1));
                cmd.Parameters["@Status"].Direction = ParameterDirection.Output;
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dataset, "tblStudent");
                gvDetail.DataSource = dataset;
                gvDetail.DataBind();

            }       
    }



    private bool validate() //validate text fields
    {
        lblMessage.ForeColor = Color.Red;
        if (txtName.Text == "")
        {
            lblMessage.Text = "Name field should not be blank.";
            return false;
        }

        if (txtRollno.Text == "")
        {
            lblMessage.Text = "Roll number field should not be blank.";
            return false;
        }
        else
        {
            if (validRollno(txtRollno.Text) == false)
            {
                lblMessage.Text = "Please enter only integer roll number.";
                return false;
            }

        }

        if (ddlBranch.SelectedValue == "0")
        {
            lblMessage.Text = "Please select valid branch name.";
            return false;
        }
        return true;
    }


    private bool validRollno(string rollno)  //validate roll number
    {
        bool IsValid = false;
        string Pattern = "0123456789";
        char[] charCheck = (rollno.ToCharArray());
        string check;
        for (int i = 0; i < charCheck.Length; i++)
        {
            check = charCheck[i].ToString();
            if (Pattern.Contains(check))
            {
                IsValid = true;
            }
            else
            {
                return IsValid = false;
            }
        }
        return IsValid;
    }

    private void Clear()
    {
        txtName.Text = "";
        txtRollno.Text = "";
    }


    private string Operations()   //oprations  in tblStudent
    {
        DataSet dataset = new DataSet();

        string Status = "";
        try
        {
            if (ddlOperation.SelectedItem.Text == "Insert" || ddlOperation.SelectedItem.Text == "Update")
            {
                if (validate() == false)
                {
                    return "";
                }
                using (SqlConnection con = new SqlConnection(CS))
                {
                    SqlCommand cmd = new SqlCommand("USP_MultipleStoredProc", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Name", txtName.Text);
                    cmd.Parameters.AddWithValue("@Rollno", Convert.ToInt32(txtRollno.Text));
                    cmd.Parameters.AddWithValue("@Branch", ddlBranch.SelectedItem.Text);
                    cmd.Parameters.AddWithValue("@Operation", ddlOperation.SelectedItem.Text);
                    cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.VarChar, -1));
                    cmd.Parameters["@Status"].Direction = ParameterDirection.Output;
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    cmd.ExecuteNonQuery();
                    Status = cmd.Parameters["@Status"].Value.ToString();
                    BindGridView();
                }
                return Status;
            }


            if (ddlOperation.SelectedItem.Text == "Delete")
            {
                using (SqlConnection con = new SqlConnection(CS))
                {
                    SqlCommand cmd = new SqlCommand("USP_MultipleStoredProc", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Name","");
                    cmd.Parameters.AddWithValue("@Rollno", Convert.ToInt32(txtRollno.Text));
                    cmd.Parameters.AddWithValue("@Branch","");
                    cmd.Parameters.AddWithValue("@Operation", ddlOperation.SelectedItem.Text);
                    cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.VarChar, -1));
                    cmd.Parameters["@Status"].Direction = ParameterDirection.Output;
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    cmd.ExecuteNonQuery();
                    Status = cmd.Parameters["@Status"].Value.ToString();
                    BindGridView();
                }
                return Status;
            }


            if (ddlOperation.SelectedItem.Text == "Search")
            {
                using (SqlConnection con = new SqlConnection(CS))
                {
                    SqlCommand cmd = new SqlCommand("USP_MultipleStoredProc", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Name", "");
                    cmd.Parameters.AddWithValue("@Rollno", Convert.ToInt32(txtRollno.Text));
                    cmd.Parameters.AddWithValue("@Branch", "");
                    cmd.Parameters.AddWithValue("@Operation", ddlOperation.SelectedItem.Text);
                    cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.VarChar, -1));
                    cmd.Parameters["@Status"].Direction = ParameterDirection.Output;
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(dataset, "tblStudent");
                    int count = dataset.Tables["tblStudent"].Rows.Count;
                    if (count > 0)
                    {
                        gvDetail.DataSource = dataset;
                        gvDetail.DataBind();
                    }
                    else
                    {
                        dataset.Tables["tblStudent"].Rows.Add(dataset.Tables["tblStudent"].NewRow());
                        gvDetail.DataSource = dataset;
                        gvDetail.DataBind();
                        lblMessage.ForeColor = Color.Red;
                        lblMessage.Text = "Record is not found.";
                        
                    }        

                }           
            }

            if (ddlOperation.SelectedItem.Text == "SearchAll")
            {
                BindGridView();
            }
           
        }
        catch (Exception ex)
        {
            lblMessage.ForeColor = Color.Red;
            lblMessage.Text = "Insert operation failed try again....";
        }
        return Status;
    }


    #endregion

    protected void btnOperation_Click(object sender, EventArgs e)
    {
        if (ddlOperation.SelectedValue == "0")
        {
            lblMessage.ForeColor = Color.Red;
            lblMessage.Text = "Please select vlaid Opration.";
            return;
        }
        if (ddlOperation.SelectedItem.Text == "Insert" || ddlOperation.SelectedItem.Text == "Update")
        {
            if (validate() == false)
            {
                return;
            }
        }

        if (ddlOperation.SelectedItem.Text == "Delete")
        {
            if (validRollno(txtRollno.Text) == false)
            {
                return;
            }
        }
        if (ddlOperation.SelectedItem.Text == "Search")
        {
            if (validRollno(txtRollno.Text) == false)
            {
                lblMessage.ForeColor = Color.Red;
                lblMessage.Text = "Please enter only integer roll number.";
                return;
            }
        }
        
        lblMessage.ForeColor = Color.YellowGreen;
        lblMessage.Text = Operations();
        Clear();
    }
}

Step 6:- Now, Run the application and perform Insert, Update, Delete, and Search operations.