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

Comments On: More about Stored Procedures using ASP.NET 2.0 with the Microsoft Data Access Application Block

· Hello guys!I expanded the previous article on working with stored procedures...    · This helped me a whole bunch! Thank you!   >>> Post your comment now!