How do we use stored procedure in ADO.NET and how do we provide parameters to the stored procedures?

 Posted by ArticlesMaint on 9/17/2009 | Category: ADO.NET Interview questions | Views: 10275

Answer: -
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 + '%'
Select FirstName from Employees

If txtEmployeeName.Text.Length = 0 Then
objCommand = New SqlCommand(“SelectEmployee”)
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 much has been changed only that the SQL is moved to the stored procedures. There are two stored procedures one is “Select Employee” 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. In such question interviewer expects two simple answers one is that we use command object to execute stored procedures and the parameter object to provide parameter to the stored procedure. Above sample is provided only for getting the actual feel of it. Be short be nice and get a job.

Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response