Consuming SPROC output parameter in DAL??

[Acuffdl]

I want to consume an output parameter from a stored procedure. I am getting pretty comfortable using the DAL that there is very little code to work with to actually consume the output parameter.

I was hoping it would show up in the ObjectDataSource / Gridview control as an available value but no such luck. I notice when I edit my Business Object in design view, choose configure DataSource, click next twice to get to define parameters, I have a numberRows parameter (along with selectDate, selectCountr) that has a Parameter Source set to none. In the default Value aree (after clicking advanced properties) I selected down to Direction and changed the value to Output. But it does now seem to stick..

Here is the working stored procedure:

ALTER PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime

/* Input or Output Parameters */
/* Note that if you declare a parameter for OUTPUT, it can still be used to accept values. */
/* as is this procedure will very well expect a value for @numberRows */
@selectDate datetime
,@selectCountry int
,@numberRows int OUTPUT

AS

SELECT DISTINCT configname FROM ModelRequests JOIN
CC_host.dbo.usr_c As t2 ON
t2.user_id = ModelRequests.username JOIN
Countries ON
Countries.Country_Short = t2.country
WHERE RequestDateTime >= @selectDate and RequestDateTime < dateadd(dd,1, @selectDate)
AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @selectCountry
ORDER BY configname

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */
/* Return a scalar value of the number of rows using an output parameter. */
SET @numberRows = @@RowCount
-- RETURN ??

GO

How exactly will I consume this? In the code behind? or in the .xsd source code view? Or some other way.

Using DAL my code behind is 100% eliminated and not needed.

' The Below code is no longer needed now that we can create and use

' Data Access Layer (DAL) Objects to query the database

'----------------------------------------------------------------------------------------

'Protected Sub BtnGetModels_Click1(ByVal sender As Object, ByVal e As System.EventArgs)

'Dim dateEntered As String = TxtDate.Text

'Dim selectCountry As String = CountryList.SelectedValue

'Dim con As New SqlClient.SqlConnection

'con.ConnectionString = "Data Source=;Initial Catalog=New_Products;Persist Security Info=True;User ID=ad;Password=wd"

'Dim myCommand As New SqlClient.SqlCommand

'myCommand.CommandText = "ap_Select_ModelRequests_RequestDateTime"

'myCommand.CommandType = CommandType.StoredProcedure

'myCommand.Parameters.AddWithValue("@selectDate", dateEntered)

'myCommand.Parameters.AddWithValue("@selectCountry", CInt(selectCountry))

'myCommand.Connection = con

'con.Open()

'Dim reader As SqlDataReader = myCommand.ExecuteReader()

'Dim rowCount As Integer = reader.RecordsAffected

'numberParts.Text = rowCount.ToString

'con.Close()

'End Sub

Is what I need to do possible by using only a DAL?

Help appreciated

[mendhak]

What you need may be possible, but not if you're using "wizards" (which are anything but).

Handcoding would make things much simpler for you. To start with, you could get the number of rows affected by your query by simply using .ExecuteNonQuery() instead of @@ROWCOUNT.

Second, a better way of doing this instead is to have your command object execute the stored procedure and return a dataset. Then simply do a ds.Tables[0].Rows.Count, and voila, your rowcount.

Third, if you still want to use @@ROWCOUNT, you'd create a parameter and set its direction property. For example,

Dim myParam as New SqlParameter("@numberRows",SqlDbType.Int)
myParam.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(myParam)

You then simply access myParam.Value after executing the stored procedure.

Fourth, your statement: "Using DAL my code behind is 100% eliminated and not needed." is technically incorrect. The 'codebehind' that you didn't need is still a DAL.

[Acuffdl]

While I believe you are more experienced than I, I think I have found facts to support that if the stored procedure or sql query is a select statement you in fact cannot use the ExecuteNonQuery method mentioned because you will get the value -1. Is there more evidence that this is not the case?

"Fourth, your statement: "Using DAL my code behind is 100% eliminated and not needed." is technically incorrect. The 'codebehind' that you didn't need is still a DAL."

I think we are mincing words here.. as from a "green" layman's point of view here, I am referring to the traditional code-behind pages not being used when instead applying the new business objects (DAL) method. Perhaps it *is* still code behind, but in an .XSD file instead. Yes? Either way...

I am sure Microsoft has made a way for this new approach to consume a Stored Procedures output parameter.

Anyone know what it is?

[Acuffdl]

I have changed the Stored procedure to SELECT @numberRows = @@RowCount

ALTER PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime

/* Input or Output Parameters */
/* Note that if you declare a parameter for OUTPUT, it can still be used to accept values. */
/* as is this procedure will very well expect a value for @numberRows */
@selectDate datetime
,@selectCountry int
,@numberRows int OUTPUT

AS

SELECT DISTINCT configname FROM ModelRequests JOIN
CC_host.dbo.usr_c As t2 ON
t2.user_id = ModelRequests.username JOIN
Countries ON
Countries.Country_Short = t2.country
WHERE RequestDateTime >= @selectDate and RequestDateTime < dateadd(dd,1, @selectDate)
AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @selectCountry
ORDER BY configname

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */
/* Return a scalar value of the number of rows using an output parameter. */
SELECT @numberRows = @@RowCount

GO

[mendhak]

While I believe you are more experienced than I, I think I have found facts to support that if the stored procedure or sql query is a select statement you in fact cannot use the ExecuteNonQuery method mentioned because you will get the value -1. Is there more evidence that this is not the case?

Yes, that is right. I hadn't noticed your SELECT statement.

"Fourth, your statement: "Using DAL my code behind is 100% eliminated and not needed." is technically incorrect. The 'codebehind' that you didn't need is still a DAL."

I think we are mincing words here.. as from a "green" layman's point of view here, I am referring to the traditional code-behind pages not being used when instead applying the new business objects (DAL) method. Perhaps it *is* still code behind, but in an .XSD file instead. Yes? Either way...

Believe me, words and terminology make all the difference when it comes to talking about a scenario. Development is just as much about vocabulary and communication as it is about coding.

[mendhak]

Did you add the code I suggested?

[Acuffdl]

Well I can give it a try.

This would mean totally abandoning the business (DAL) object layer, which seems odd to me since I was gearing up to try and do things the *right* way.

But I will conceed this task needs to be hand coded.

[mendhak]

Yes... no wonder it's so easily customizable. Post back with your results.