How do we use stored procedure in ADO.NET? Dot Net Interview Questions

punjabtechnicaluniversity.blogspot.in

ADO.NET provides the SqlCommand object which provides the functionality of executing stored procedures.

Note :- Sample code is provided in folder “WindowsSqlClientCommand”.There are two stored procedures created in same database “Employees” which was created for the previous question.

CREATE PROCEDURE SelectByEmployee @FirstName nvarchar(200) AS Select FirstName from Employees where FirstName like @FirstName + '%'

CREATE PROCEDURE SelectEmployee AS Select FirstName from Employees
If txtEmployeeName.Text.Length = 0 Then
objCommand = New SqlCommand(“SelectEmployee”)
Else
objCommand = New SqlCommand(“SelectByEmployee”)
objCommand.Parameters.Add(“@FirstName”,
Data.SqlDbType.NVarChar, 200)
objCommand.Parameters.Item(“@FirstName”).Value =
txtEmployeeName.Text.Trim()
End If

In the above sample not lot has been changed only that the SQL is moved to the stored procedures.There are two stored procedures one is “SelectEmployee” which selects all the employees and the other is “SelectByEmployee” which returns employee name starting with a specific character.As you can see to provide parameters to the stored procedures we are using the parameter object of the command object.