Friday 13 July 2012

How to create and execute Stored Procedured in Sql Server



What   is   Stored Procedure? 


stored procedure is a subroutine available to applications that access a relational system. It actually stored in the database data dictionaryIt is  collection of  query (Compiled) which storing in Database………


  Advantage:

             1.   Better  performance

             2.   Less  traffic

             3.   Security

             4.   Integrity

             5.   Code Re usability


     Disadvantage:

          1. no of call is very more use ,the process is  slow or performance  less

     2.  Same Data base  working


********************************************************************
Open Sq l Server .................  and  select New Query.......................

creating database test 

create database test


after creating database ,create table  p_customer in database test


use  test

create table p_customer (id int primary key,fname nvarchar(50),lname nvarchar(50),dob date)


******************************************************************

1./* creating procedure for insertting data in p_customer table */


create proc Ins_Data @id int,@fname nvarchar(50),@lname nvarchar(50),@dob date

as

Insert into p_customer values(@id ,@fname,@lname,@dob )


/* execute procedure for  */


exec Ins_Data 2,'kush','Tiwari', '02/23/1987'


***********************************************************************

2. creating procedure for selecting data


create proc selectdata

 as

Select * from p_customer


/* execute procedure for selectdata */


exec selectdata


****************************************************************

3. creating procedure for delete  data


create proc Delelet_Data @id int

as

delete from p_customer where id=@id


 execute procedure for Delelet_Data 


exec Delelet_Data 4


************************************************************************

4. creating procedure for updating data*/


create proc Update_Data @id int,@fname nvarchar(50),@lname nvarchar(50),@dob date

as

update p_customer set fname=@fname,lname=@lname,dob=@dob where id=@id


/* execute procedure for Update_Data */


exec Update_Data 1,'Lav','Tiwari','08/11/1987'


**********************************************************************

5.creating procedure for selecting data by Id


 create proc selectbyId @id int

 as

 select fname,lname,dob from p_customer where id=@id


 execute procedure for selectbyId 


 exec selectbyId 1









How to use this procedure in Asp.Net…………with  help web.config



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>


After  than  ……………………………………………………….Default.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
// this is  using for  configration
using System.Configuration;

public partial class _Default : 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);
       // con = n.ew SqlConnection("Data Source=KUSH-PC;Initial Catalog=test;Integrated Security=True");
    }
    protected void BSave_Click(object sender, EventArgs e)
    {
        con.Open();
        cmd = new SqlCommand("Ins_Data", con);
        cmd.Parameters.AddWithValue("@id", Convert.ToInt32(txtid.Text));
        cmd.Parameters.AddWithValue("@fname",txtfname.Text);
        cmd.Parameters.AddWithValue("@lname",txtlname.Text);
        cmd.Parameters.AddWithValue("@dob", txtdob.Text);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ExecuteNonQuery();
        con.Close();
        Message.Text = "Add successfully Record";
       
 
    }
    protected void BUpdate_Click(object sender, EventArgs e)
    {
        con.Open();
        cmd = new SqlCommand("Update_Data", con);
        cmd.Parameters.AddWithValue("@id", Convert.ToInt32(txtid.Text));
        cmd.Parameters.AddWithValue("@fname", txtfname.Text);
        cmd.Parameters.AddWithValue("@lname", txtlname.Text);
        cmd.Parameters.AddWithValue("@dob", txtdob.Text);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ExecuteNonQuery();
        con.Close();
        Message.Text = "Update successfully Record";

    }
    protected void BSelect_Click(object sender, EventArgs e)
    {
        con.Open();
        cmd = new SqlCommand("selectdata", con);
        cmd.CommandType = CommandType.StoredProcedure;
        dr = cmd.ExecuteReader();
        GridView1.DataSource = dr;
        GridView1.DataBind();
        con.Close();
    }
    protected void BDelete_Click(object sender, EventArgs e)
    {
        con.Open();
        cmd = new SqlCommand("Delelet_Data", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@id", Convert.ToInt32(txtid.Text));
        cmd.ExecuteNonQuery();
        con.Close();
        Message.Text = "Delete successfully Record";

    }
    protected void BSelectById_Click(object sender, EventArgs e)
    {
        con.Open();
        cmd = new SqlCommand("selectbyId", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@id",Convert.ToInt32(txtid.Text));

        dr = cmd.ExecuteReader();
        if (dr.Read())
        {
            txtfname.Text = dr["fname"].ToString();
            txtlname.Text = dr["lname"].ToString();
            txtdob.Text = dr["dob"].ToString();
        }
       else
        {
            Message.Text = "Id is  not exist";
        }


    }
}





creating procedure for cheak no even or odd


create proc even_odd @no int

as

begin

declare @res int

set @res=@no%2

if @res=0

print'Number is even'

else

print'Number is odd'

end


executing procedure for even_odd 

exec even_odd 41

Result---


Number is odd












4 comments:

  1. great work sir..it is totally different ...and secured technique..thnxxx

    ReplyDelete
  2. Your effort is highly appreciable Sir.

    ReplyDelete
  3. Its really help..Sir...thanks for sharing.

    ReplyDelete
  4. easy to understand..very good..notes

    ReplyDelete