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();