Monday, December 12, 2011

How to call a Stored Procedure in asp.net

Title: How to use stored procedure in asp.net using c#.net

Description:
In this article i will shown how to call stored procedure in asp.net application.If u want to call a procedure it may have 2 types of parameters
                                       1.input
                                       2.Output
Input parameters are used for taking a value into procedure for Execution.Output parameters are used for sending a value out of the procedure after execution.If a procedure has parameters to it for each of every parameter of this procedure a matching parameter has to be created by us under .net application>a parameter under the .net application has five different attributes to it.Those are
                                      1.Name
                                      2.Size
                                      3.SqlDb Type
                                      4.Value
                                      5.Direction
Where direction can be input or output>to create magic input parameter under the .net application you need to specify the attributes name and value while creation..To create output parameter under .net application you need to specify the attributes Name,sqlDbtype,Size,Direction.After execution of the procedure it will return to the value
Example:
If you want to call a procedure that returns a value from our .net application the method that has to be applied on the command is Execution non query.
//under page load
SqlConnection cn=new SqlConnection("Userid=sa;Password=123;Database=employee");
SqlCommnad cmd=New SqlCoammand();
cmd.commandtype=Comandtype.storedprocedure;
//Under button click event
try
{ 
cmd.parameter.clear();
cmd.parameter.AddwithVlaue(@empId",txtempid.Text);
cmd.parameter.Add(@empId",sqlDbtype.Varchar)direction=Parameterdirection.output;
cmd.parameter["empname"].Size=50;
cmd.parameter.Add(@add",sqlDbtype.Varchar)direction=Parameterdirection.output;
cn.Open();
cmd.ExcuteNonQuery();
Textbox2.Text=cmd.parameters["@empname"].value.Tostring();
Textbox3.Text=cmd.parameters["@empname"].value.Tostring();
}
catch(exception ex)
{
messageBox.show(ex.message);
}
finally{
cn.Close();
}
  
Post a Comment

Bel