Thursday 17 September 2015

Single type Stored Procedure


Single type Stored Procedure


What is Single type Stored Procedure? By Single type of Stored Procedure we can perform only single operation. If we want to perform Insert, Update, Delete and Search operations in SQL SERVER with ASP.NET then we have to create four Stored Procudures.

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 five Stored Procedures

  

--------1st Stored Procedure for Insert-------------
CREATE PROC USP_InsertStudent  
@Name varchar(100),@Rollno int, @Branch varchar(50),@Status varchar(50) output  
  
AS  
BEGIN  
 begin try  
  begin tran  
 Insert into tblStudent values(@Name,@Rollno,@Branch)  
 set @Status='Student record has been inserted!'  
 commit tran  
 end try  
   
 begin catch  
 set @Status='Student record has not been inserted!'  
 rollback  tran
 end catch  
  
END


    --------2st Stored Procedure for Update----------

CREATE PROC USP_UpdateStudent
@Name varchar(100),@Rollno int,@Branch varchar(50),@Status varchar(50) output     
    
AS    
BEGIN    
    
 begin try    
  begin tran    
  Update tblStudent set Name=@Name,Branch=@Branch where Rollno=@Rollno    
      
 set @Status='Student record has been updated!.'    
  commit tran    
 end try    
     
 begin catch    
  set @Status='Student record has not been updated!.'    
 rollback    
 end catch    
     
END


--------3rd Stored Procedure for Delete----------

CREATE PROC USP_DeleteStudent    
@Rollno int,@Status varchar(50) output     
AS    
BEGIN    
  begin try    
   begin tran    
   Delete from tblStudent where Rollno=@Rollno    
   set @Status= 'Student record has been deleted!.'    
   commit tran    
       
  end try    
      
  begin catch    
  set @Status= 'Student record has not been deleted!.'    
   rollback    
  end   catch    
         
END


--------4th Stored Procedure for Search all Records----------

CREATE PROC USP_GetStudent  
AS  
BEGIN  
  Select * from tblStudent  
END


--------5th Stored Procedure for Search single record by Roll number----------

CREATE PROC USP_GetStudentByRollno 
@Rollno int  
AS  
BEGIN  
  Select * from tblStudent where Rollno=@Rollno
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 namedStoredProcedure 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>Stored Procedure 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>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> <asp:Button ID="btnSave" runat="server" Text="Save" Font-Bold="true" Width="140px" Height="30px" OnClick="btnSave_Click" /> </td> <td> <asp:Button ID="btnUpdate" runat="server" Text="Update" Font-Bold="true" Width="150px" Height="30px" OnClick="btnUpdate_Click" /> </td> </tr>
        <tr> <td> <asp:Button ID="btnDelete" runat="server" Text="Delete" Font-Bold="true" Width="140px" Height="30px" OnClick="btnDelete_Click" /></td> <td> <asp:Button ID="btnSearch" runat="server" Text="Search" Font-Bold="true" Width="150px" Height="30px" OnClick="btnSearch_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
{
    //Connection String called from Web.config
    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindGridView();
        }
    }


    #region "Function"

    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 BindGridView()  //Bind Student record in GridView
    {
        using (SqlConnection con = new SqlConnection(CS))
        {
            SqlCommand cmd = new SqlCommand("USP_GetStudent",con);
            DataSet dataset = new DataSet();
            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();
            }          
        }
    }



    private string InsertRecord()   //Insert record in tblStudent
    {
        
        string Status = "";
        try
        {
           
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("USP_InsertStudent", 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.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();
                
            }
            return Status;
        }
        catch(Exception ex)
        {
            lblMessage.ForeColor = Color.Red;
            lblMessage.Text = "Insert operation failed try again....";
        }
        return Status;
    }


    private string UpdateRecord()   //Update record in tblStudent
    {

        string Status = "";
        try
        {

            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("USP_UpdateStudent", 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.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();

            }
            return Status;
        }
        catch (Exception ex)
        {
            lblMessage.ForeColor = Color.Red;
            lblMessage.Text = "Update operation failed try again....";
        }
        return Status;
    }


    private string DeleteRecord()    //Delete record from tblStudent
    {

        string Status = "";
        try
        {

            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("USP_DeleteStudent", con);
                cmd.CommandType = CommandType.StoredProcedure;           
                cmd.Parameters.AddWithValue("@Rollno", Convert.ToInt32(txtRollno.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();

            }
            return Status;
        }
        catch (Exception ex)
        {
            lblMessage.ForeColor = Color.Red;
            lblMessage.Text = "Delete operation failed try again....";
        }
        return Status;
    }


    private string SearchRecord()  //Search record in tblStudent by Roll numbere
    {

        string Status = "";
        try
        {

            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("USP_GetStudentByRollno", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Rollno", Convert.ToInt32(txtRollno.Text));
                DataSet dataset = new DataSet();
                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.";
                }        

            }
            return Status;
        }
        catch (Exception ex)
        {
            lblMessage.ForeColor = Color.Red;
            lblMessage.Text = "Delete operation failed try again....";
        }
        return Status;
    }


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



    #endregion

    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (validate() == false)
        {
            return;
        }
       lblMessage.ForeColor = Color.GreenYellow;
       lblMessage.Text= InsertRecord();
       BindGridView();
       Clear();
    }

    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        if (validate() == false)
        {
            return;
        }
       lblMessage.ForeColor = Color.GreenYellow;
       lblMessage.Text= UpdateRecord();
       BindGridView();
       Clear();
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        if (txtRollno.Text == "")
        {
            lblMessage.ForeColor = Color.Red;
            lblMessage.Text = "Roll number field should not be blank.";
            return;
        }
        else
        {
            if (validRollno(txtRollno.Text) == false)
            {
                lblMessage.ForeColor = Color.Red;
                lblMessage.Text = "Please enter only integer roll number.";
                return;
            }
        }
        lblMessage.ForeColor = Color.YellowGreen;
        lblMessage.Text= DeleteRecord();
        BindGridView();
        Clear();
    }

    protected void btnSearch_Click(object sender, EventArgs e)
    {

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

        SearchRecord();
        Clear();
    }
}


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



Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)