Asp.net How to Create Store Procedure using Sql Server Database

Asp.net Store procedure how to use in web application and c#.net using Sql Server Database.

Introduction : This artical explain How to create Store procedure using Sql Server database in asp.net or C#.net With full source code here this code is for web application in 3-Tier Architecture.

Step 1: Create database with table name "stud" given below

CREATE TABLE [dbo].[stud] (

[Id] INT IDENTITY (1, 1) NOT NULL,

[name] NVARCHAR (50) NOT NULL,

[address] NVARCHAR (50) NOT NULL,

PRIMARY KEY CLUSTERED ([Id] ASC)

);

Step 2 : Now Add New Store Procesure and give name "sp_demo" Before the query you can declare the parameter of table collumn name and add extra parameter (here @query ) for if else condition. in one store procedure you can write more than one query between the Begin & End

CREATE PROCEDURE [dbo].[sp_demo]
@Id int,
@name nvarchar(50),
@address nvarchar(50),
@query nvarchar(50)
AS
    if(@query='insert')
    Begin
    insert into stud values(@name,@address);
    End
    else if(@query='delete')
    Begin
    delete from stud where (name)=(@name);
    End
    else if(@query='update')
    Begin
    update stud set address=@address where (name)=(@name);
    End
RETURN 0

Step 3: Create data Access Class And Give name DAL.CS

public class DAL
    {
        public int Id { get; set; }
        public string name { get; set; }
        public string address { get; set; }
    }

Step 4: Create Busseness Access Layer BAL.CS

public class BAL
{
SqlConnection con=new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename
=E:\Store_procedure\App_Data\mydb.mdf;Integrated Security=True");

public void Insert_Data(DAL Da)
    {
     con.Open();
     SqlCommand cmd = new SqlCommand();
     cmd = new SqlCommand("sp_demo '"+DBNull.Value+"','"+Da.name+"','"+Da.address+"','insert'",con);
     cmd.ExecuteNonQuery();
     con.Close();
     }

public void Delete_Data(DAL Da)
     {
      con.Open();
      SqlCommand cmd = new SqlCommand();
      cmd = new SqlCommand("sp_demo '"+DBNull.Value+"','"+Da.name+"',
                           '"+DBNull.Value+"','delete'",con);
      cmd.ExecuteNonQuery();
      con.Close();

}

   public void Update_Data(DAL Da)
     {
     con.Open();
     SqlCommand cmd = new SqlCommand();
     cmd = new SqlCommand("sp_demo '"+DBNull.Value+"','"+Da.name+"',
                           '"+Da.address+"','update'", con);
    cmd.ExecuteNonQuery();
    con.Close();
    }

Step 4 : Code in Code behind file

public partial class _default : System.Web.UI.Page
    {
        DAL da = new DAL();
        BAL ba = new BAL();

    protected void Page_Load(object sender, EventArgs e)
    {

        }

        protected void btn_submit_Click(object sender, EventArgs e)
        {
            da.name = txt_name.Text;
            da.address = txt_address.Text;
            ba.Insert_Data(da);
            Response.Write("data inserted....");
            Grid();
            txt_name.Text = "";
            txt_address.Text = "";
        }

        protected void btn_delete_Click(object sender, EventArgs e)
        {
            da.name = txt_name.Text;
            ba.Delete_Data(da);
            Grid();
            txt_name.Text = "";
        }

        protected void btn_update_Click(object sender, EventArgs e)
        {
            da.name = txt_name.Text;
            da.address = txt_address.Text;
            ba.Update_Data(da);
            Response.Write("Data Updated....");
            Grid();
            txt_name.Text = "";
            txt_address.Text = "";
        }
    }

Now Run Project And See O/P.

See Other Tutorial :

*  AngularJS CRUD Operation in ASP.Net MVC

*  AngularJS With ASP.NET MVC

*  Convert Rows to columns using 'Pivot' in SQL Server

*  Mvc Registration page With user exist using Ajax method

*  MVC 4 How to Perform Insert Update Delete Edit Select Operation

*  MVC4 Edit,update,Delete,cancel inside gridview using sql database

*  MVC 4 Gridview To Display Data Using SQL Server Database With Simple code

*  Login page in asp.net Mvc4 Web application

*  Mvc4 How to bind Dropdown List using Sql Database

* Gridview find control in asp.net

codelearne

Asp.net How to Create Store Procedure using Sql Server Database

Asp.net Store procedure how to use in web application and c#.net using Sql Server Database.