How to Call Stored Procedure in .Net Application?

In this article I'm trying to explain how to call stored procedures in an application, how to read parameters of the procedure, how to assign the values to parameters and using that and how to communicate in to DataBase.

Calling Stored Procedure in an Application:

In this artical i'm trying to explain how to call stored procedures in an application and how to read parameters of the procedure and how to assign the values to parameters and using that how to communicate in to DataBase.

What is Stored Procedure:

Whenever we want to interact with a DataBase from an application we use SQL statements. These SQL statements when used with in an application has a problem i.e when we run the application Statements will be sent to DB for execution where the statements to be parsed / Compile and then execute. The process of parsing takes place each time , because of which performence of apllication is decreases. To overcome the above drawback write SQL statements directly in DataBase itself, with in an object known as Stored Procedures .As a SP is pre-compiled block which is ready for execution will directly execute the statements without parsing each time..

Eg:
Application DataBase

SQL statements compile & execute everytime
Stored Procedures only execution

Creat Table


CREATE TABLE EMP
(
	EMPNO INT CONSTRAINT EMPNO_PK PRIMARY KEY IDENTITY (1,1),
	ENAME VARCHAR(100),
	JOB VARCHAR(100),
	MGR VARCHAR(100),
	HIREDATE DATETIME,
	SAL MONEY,
	COMM MONEY,
	PICTURE VARCHAR(250),
	DEPTNO INT
)

Prepare Stored Procedures :

for getting employee information purpose we creat below stored procedure instead of select query.


/*
	EXEC Get_Employees @DeptNo=1
*/
CREATE PROCEDURE Get_Employees
(
	@DeptNo INT
)
AS
BEGIN
	SELECT  *
	FROM	EMP
	WHERE	DEPTNO=@DeptNo
END

for inserting employee details purpose we just preapre the below sample stored procedure.


CREATE PROCEDURE Insert_Employee_Details
(
	@Ename VARCHAR(100)=NULL,
	@Job VARCHAR(100)=NULL,
	@mgr VARCHAR(100)=NULL,
	@Sal MONEY =NULL,
	@Comm MONEY = NULL,
	@Pic VARCHAR(250)=NULL,
	@Dept_No INT
)
AS
BEGIN
	IF @Dept_No IS NOT NULL
	BEGIN
		INSERT INTO EMP ( ENAME,JOB, MGR, HIREDATE, SAL, COMM,PICTURE, DEPTNO)
		VALUES ( @Ename, @Job, @mgr,GETDATE(), @Sal, @Comm, @Pic, @Dept_No)
		
	END

END

for updating employee details purpose we just create below sample procedure


CREATE PROCEDURE Update_Employee_Details
(
	@EMPNO INT,
	@Ename VARCHAR(100)=NULL,
	@Job VARCHAR(100)=NULL,
	@mgr VARCHAR(100)=NULL,
	@Sal MONEY =NULL,
	@Comm MONEY = NULL,
	@Pic VARCHAR(250)=NULL
	
)
AS
BEGIN
	UPDATE EMP SET ENAME=@Ename, JOB=@Job, MGR=@mgr, SAL=@Sal, COMM=@Comm,PICTURE=@Pic
	WHERE EMPNO=@EMPNO 
	
END

for Deleting purpose we just creat below sample store procedure and call this in our application



CREATE PROCEDURE Delete_Employee_Details
(
	@EmpNo INT
	
)
AS
BEGIN
	DELETE FROM EMP
	WHERE  EMPNO=@EmpNo
	

END

Now call these all procedures in our application using Command Class.

Source Code:



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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

< html xmlns="http://www.w3.org/1999/xhtml">
< head runat="server">
    < title> < /title>
< /head>
< body>
    < form id="form1" runat="server">
    < div align="center" style="color: #000066">
      < span class="style1"> < strong> Employee Details< /strong> < /span> < br />
        < br />
        < br />
        < br />
    
    < table>
        < tr>
            < td>
                Emplpoyee Name :
            < /td>
            < td>
                < asp:TextBox ID="txtname" runat="server"> < /asp:TextBox>
            < /td>
        < /tr>
        < tr>
            < td>
                Job :
            < /td>
            < td>
                < asp:TextBox ID="txtjob" runat="server"> < /asp:TextBox>
            < /td>
        < /tr>
        < tr>
            < td>
                Manager :
            < /td>
            < td>
                < asp:TextBox ID="txtmgr" runat="server"> < /asp:TextBox>
            < /td>
        < /tr>
        < tr>
            < td>
                Salary :
            < /td>
            < td>
                < asp:TextBox ID="txtsal" runat="server">
                    < /asp:TextBox>
            < /td>
        < /tr>
        < tr>
            < td>
                Commission :
            < /td>
            < td>
                < asp:TextBox ID="txtcomm" runat="server">< /asp:TextBox>
            < /td>
        < /tr>
        < tr>
            < td>
                Upload Image :
            < /td>
            < td>
                < asp:FileUpload ID="fileuploadimage" runat="server" />
            < /td>
        < /tr>
        < tr>
            < td>
            < /td>
            < td>
                < asp:Button ID="btnInsert" runat="server" OnClick="btnInsert_Click" 
                    Text="Insert" />
            < /td>
        < /tr>
    < /table>
    < table>
        < asp:GridView ID="GV" runat="server" AutoGenerateColumns="false" 
            HeaderStyle-BackColor="#7779AF" HeaderStyle-ForeColor="white" 
            OnRowCancelingEdit="GV_RowCancelingEdit" OnRowDeleting="GV_RowDeleting" 
            OnRowEditing="GV_RowEditing" OnRowUpdating="GV_RowUpdating">
            < AlternatingRowStyle BackColor="White" />
            < EditRowStyle BackColor="#7C6F57" />
            < FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            < HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            < PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
            < RowStyle BackColor="#E3EAEB" />
            < SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
            < SortedAscendingCellStyle BackColor="#F8FAFA" />
            < SortedAscendingHeaderStyle BackColor="#246B61" />
            < SortedDescendingCellStyle BackColor="#D4DFE1" />
            < SortedDescendingHeaderStyle BackColor="#15524A" />
            < Columns>
                < asp:BoundField DataField="empno" HeaderText="Employee Number" 
                    ReadOnly="true" />
                < asp:BoundField DataField="ename" HeaderText="Employee Name" />
                < asp:BoundField DataField="job" HeaderText="Job" />
                < asp:BoundField DataField="mgr" HeaderText="Manager" />
                < asp:BoundField DataField="hiredate" HeaderText="HireDate" ReadOnly="true" />
                < asp:BoundField DataField="sal" HeaderText="Salary" />
                < asp:BoundField DataField="comm" HeaderText="Commission" />
                < asp:TemplateField HeaderText="Picture">
                        < ItemTemplate>
                            < asp:Image ID="img" runat="server" Height="50px" ImageAlign="Top" 
                                ImageUrl='< %#DataBinder.Eval(Container.DataItem,"picture") %> ' Width="50px" />
                        < /ItemTemplate>
                        < EditItemTemplate>
                            < asp:FileUpload ID="fileuploadimage" runat="server" />
                        < /EditItemTemplate>
                < /asp:TemplateField>
                < asp:CommandField HeaderText="Edit" ShowEditButton="true" />
                < asp:CommandField HeaderText="Delete" ShowDeleteButton="true" />
            < /Columns>
        < /asp:GridView>
    < /table>
    < /div>
    < div>
    
    < /div>
    < /form >
< /body >
< /html >

How to Call Stored Procedures in our Application?

1)Create an object of class command by passing the SP name as an arguement to it.Because it is reponsible for calling the procedures.


Eg: Command cmd=new Command("SP_Demo",con);

2)Change the commandtype property of command as stored procedure because by default it is configured to call sql stmts.


Eg: cmd.CommandType=CommandType.StoredProcedure;

3)If the SP having any parameters call that parameters using parameters option..


Eg: cmd.Parameters.AddWithValue("@SP_Id",value);

4)If the SP contains insert,update,delete statements in it then call the ExecuteNonQuery method of command to execute.

If the SP contains a select statements in it and if we want to load the data into a DataReader call ExecuteReader method on command or if we want to load the data into a DataSet create an object of DataAdapter by Passing command object as a parameter to it and then call Fill method on DataAdapter.

Code Behind:


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;

public partial class CallSPinApp : System.Web.UI.Page
{

    SqlConnection con = new SqlConnection("DataBase=ENGSOFT;User id=sa;Password=P@ssword9");
    SqlDataAdapter da;
    DataSet ds;
    DataTable dt;
    SqlCommand cmd;
    int DeptNo=1;
    static int num = 0;


    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bind_GV();
        }
    }
    protected void btnInsert_Click(object sender, EventArgs e)
    {
        if (fileuploadimage.HasFile)
        {
            string str = Server.MapPath(@"Image/");
            fileuploadimage.SaveAs(str + fileuploadimage.FileName);
            string img = fileuploadimage.FileName;
            try
            {
                con.Open();

                cmd = new SqlCommand("Insert_Employee_Details", con);
                cmd.CommandType = CommandType.StoredProcedure;


                cmd.Parameters.AddWithValue("@Ename", txtname.Text);
                cmd.Parameters.AddWithValue("@Job", txtjob.Text);
                cmd.Parameters.AddWithValue("@mgr", txtmgr.Text);
                cmd.Parameters.AddWithValue("@Sal", txtsal.Text);
                cmd.Parameters.AddWithValue("@Comm", txtcomm.Text);
                cmd.Parameters.AddWithValue("@Pic", img);
                cmd.Parameters.AddWithValue("@Dept_No", DeptNo);

                int n = cmd.ExecuteNonQuery();
                
                if (n == 1)
                {
                    Response.Write("Record inserted sucessfully");
                    txtname.Text = "";
                    txtjob.Text = "";
                    txtmgr.Text = "";
                    txtsal.Text = "";
                    txtcomm.Text = "";
                }
                num = 0;
                Bind_GV();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
    }
    



        protected void Bind_GV()
    {        
        cmd = new SqlCommand("Get_Employees", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@DeptNo", DeptNo);
        dt = new DataTable();

        try
        {
            con.Open();
            da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                GV.DataSource = dt;
                GV.DataBind();
            }
            else
            {
                DataRow dr = dt.NewRow();

                dt.Rows.Add(dr);
                dt.AcceptChanges();
                GV.DataSource = dt;
                GV.DataBind();
                GV.Rows[0].Visible = false;
            }
            if (num == 0)
            {
                int n = dt.Rows.Count;
                for (int i = 0; i < n; i++)
                {
                    string file = dt.Rows[i][7].ToString();
                    string path = "Image/" + file;
                    Image img = (Image)GV.Rows[i].FindControl("img");
                    img.ImageUrl = path;
                }
                num = 1;
            }
        }
        catch (Exception ex)
        {

        }
        finally
        {
            con.Close();
            con.Dispose();
        }
    }



        protected void GV_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GV.EditIndex = e.NewEditIndex;
        Bind_GV();
    }
    protected void GV_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GV.EditIndex = -1;
        num = 0;
        Bind_GV();
    }
    protected void GV_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        int EmpNo = Convert.ToInt32(GV.Rows[e.RowIndex].Cells[0].Text);
        FileUpload fu = (FileUpload)GV.Rows[e.RowIndex].FindControl("fileuploadimage");
        TextBox EName = (TextBox)GV.Rows[e.RowIndex].Cells[1].Controls[0];
        TextBox Job = (TextBox)GV.Rows[e.RowIndex].Cells[2].Controls[0];
        TextBox Mgr = (TextBox)GV.Rows[e.RowIndex].Cells[3].Controls[0];
        TextBox sal = (TextBox)GV.Rows[e.RowIndex].Cells[5].Controls[0];
        TextBox comm = (TextBox)GV.Rows[e.RowIndex].Cells[6].Controls[0];
        string imgPath = fu.FileName;
        string img = "Image/" + fu.FileName;
        if (fu.FileName == "")
        {
            img = imgPath;
        }
        else
        {
            fu.SaveAs(Server.MapPath("Image/" + fu.FileName));
        }
        try
        {
            con.Open();
            cmd = new SqlCommand("Update_Employee_Details", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@EMPNO", EmpNo);
            cmd.Parameters.AddWithValue("@Ename", EName.Text);
            cmd.Parameters.AddWithValue("@Job", Job.Text);
            cmd.Parameters.AddWithValue("@mgr", Mgr.Text);
            cmd.Parameters.AddWithValue("@Sal", sal.Text);
            cmd.Parameters.AddWithValue("@Comm", comm.Text);
            cmd.Parameters.AddWithValue("@Pic", imgPath);
            cmd.ExecuteNonQuery();

            GV.EditIndex = -1;
            num = 0;
            Bind_GV();
        }
        catch (Exception ex)
        {
        }
        finally
        {
            con.Close();
            con.Dispose();
        }
    }



        protected void GV_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        try
        {
            con.Open();
            int EmpNo = Convert.ToInt32(GV.Rows[e.RowIndex].Cells[0].Text);

            cmd = new SqlCommand("Delete_Employee_Details", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@EmpNo", EmpNo);
            cmd.ExecuteNonQuery();
            num = 0;  
            Bind_GV();
        }
        catch (Exception ex)
        {
        }
        finally
        {
            con.Close();
            con.Dispose();
        }
    }

}

How to Call Stored Procedure in .Net Application?

What is Stored Procedure:

How to Call Stored Procedures in our Application?

Comments

About dotnetspider.com

Quick Links

General

Services