Understanding Stored Procedure Basics

What is a Stored Procedure?

A stored procedure is a pre-compiled code which is ready for execution and will directly execute the statments without parsing each time


Uses
-------
1. Our application becomes Faster.
2. Avoiding the Sql-Injuctions
3. Reusability
4. Providing Security


Syntax


create Procedure[procedure-name(parameters)
as
Begin
-----
----
----
End

Selecting


Create Procedure Emp_Selct(@eno int =null)
as
begin

if @eno is Null
select eno,ename,sal from emp;
else
select ename,sal from emp where eno=@eno

end


Inserting or Updating


Create Procedure Emp_InsertorUpdate(@eno int ,@ename nvarchar(50),@sal int)
as
begin
if Not Exists(select * from emp)
insert into emp values(@eno,@ename,@sal) 
else
update emp set ename=@ename,sal=@sal where eno=@eno
begin
end

Deleting

Create Procedure Emp_delete(@eno int =null)
as
begin
delete from emp where eno=@eno
end


How We use stored procedures in Ado.net


insert Button Click
-------------------------

sqlconnection con=new sqlconnection("");

sqlcommand cmd=new sqlcommand();
 
cmd.connection=con;
cmd.commandType=commandType.StoredProcedure;

//Initially Command type will be Text For executing SqlQuires.. Now we are executing stored procedures so it changed to StoredProcedure

cmd.commandText="Emp_InsertorUpdate";//Stored Procedure Name

SqldataAdapter ad=new SqldataAdapter(cmd);

cmd.parameter.addWithValue("@eno",TextBox1.Text);
cmd.parameter.addWithValue("@ename",TextBox2.Text);
cmd.parameter.addWithValue("@sal",TextBox3.Text);
cmd.ExecuteNonQuery();


Delete Button
=============

cmd.commandText="Emp_delete";//Stored Procedure Name

SqldataAdapter ad=new SqldataAdapter(cmd);

cmd.parameter.addWithValue("@eno",TextBox1.Text);]
cmd.ExecuteNonQuery();

Select a Record to pass a one value
---------------------------------------


   SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.commandText="Emp_select";//Stored Procedure Name
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@eno", TextBox1.Text);
        DataSet ds = new DataSet();
        SqlDataAdapter ad = new SqlDataAdapter(cmd);
        cmd.ExecuteNonQuery();
        ad.Fill(ds);
        TextBox2.Text = ds.Tables[0].Rows[0][1].ToString();
        TextBox3.Text = ds.Tables[0].Rows[0][2].ToString();








Ebook Download
View all
Learn
View all