More about Stored Procedures using ASP.NET 2.0 with the Microsoft Data Access Application Block - Retrieving a complete row from a stored procedure using ASP.NET 2.0 and the Data Access Application Block
(Page 3 of 4 )
In previous sections, we worked with only single values. Now, in this section, we shall look into retrieving a complete row returned by a stored procedure.
Before going to the code to execute a stored procedure, we need to create a simple stored procedure (with a single parameter) for this demonstration. The following is the code for the stored procedure I created:
CREATE PROCEDURE dbo.p_GetEmployee
(
@empno int
)
AS
select * from emp where empno = @empno
RETURN
You can observe that the above stored procedure simply returns a complete row from the “emp” table based on the “empno” sent as parameter.
The following is the complete code needed to execute the above stored procedure using ASP.NET 2.0 through the Data Access Application Block
ImportsSystem.Data
ImportsMicrosoft.Practices.EnterpriseLibrary.Data.Sql
ImportsMicrosoft.Practices.EnterpriseLibrary.Data
PartialClass Sample3
Inherits System.Web.UI.Page
Protected Sub btnConnect_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnConnect.Click
Try
Dim dr As DataRow = SPExecute("p_GetEmployee", "1001")
Me.lblMsg.Text = dr("ename") & ", " & dr("sal")
Catch ex As Exception
Me.lblMsg.Text = ex.Message
End Try
End Sub
Private Function SPExecute(ByVal Name As String, ByVal empno As String) As DataRow
Try
Dim db As SqlDatabase = DirectCast(DatabaseFactory.CreateDatabase("AdventureWorks"), SqlDatabase)
Dim ds As DataSet = db.ExecuteDataSet(Name, New Object() {empno})
Return ds.Tables(0).Rows(0)
Catch ex As Exception
Throw New Exception("Unable to execute:" & ex.Message)
End Try
End Function
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
EndClass
Even though the above code works pretty well, not everyone likes the above methodology, because it works with dataset. The other way to make this work is by using OUTPUT parameters. The next section concentrates on this.
Next: Retrieving an OUTPUT parameter value from a stored procedure using ASP.NET 2.0 and the Data Access Application Block >>
More ASP.NET Articles
More By Jagadish Chaterjee
