You will
be using SqlCommand object to call and execute stored procedures. If your
stored procedure requires any parameters to be passed, then that is done
using parameters object.
Assume that your database contains the following stored procedure:
Create Procedure
sProcStudent @RollNo nvarchar(50) As
Select RollNo from Students where RollNo like @RollNo + ‘%’
In this stored
procedure, you display set of students whose RollNo match the pattern
specified in the parameter @RollNo. For example, if you pass the parameter
as ‘XII1’ then all students having their roll no starting with
this pattern will be displayed. Now how do you call this stored procedure
and pass appropriate parameter using ADO.NET in your .NET application?
That is demonstrated using the following sample snippet of code:
sampleCommand
= New SqlCommand(“sProcStudent”)
sampleCommand.Parameters.Add(“@RollNo”, Data.SqlDbType.NVarChar,
50)
sampleCommand.Parameters.Item(“@RollNo”).Value = txtStudRollNo.Text.Trim()
In the above
code instead of specifying a query in the sampleCommand, you specify the
stored procedure name. You specify the parameter name and type using sampleCommand.Parameters.Add
method. You then pass data to the parameter using sampleCommand.Parameters.Item
method. The data is received from the User using txtStudRollNo textbox.
Hence the textbox’s text value is assigned to the parameter.
