How to get scalar value from stored procedure using Nettiers
I have a really simple stored procedure that looks like this:
CREATE PROCEDURE _Visitor_GetVisitorIDByVisitorGUID
(
@VisitorGUID AS UNIQUEIDENTIFIER
)
AS
DECLARE @VisitorID AS bigint
SELECT @VisitorID = VisitorID FROM dbo.Visitor WHERE VisitorGUID = @VisitorGUID
--Here what I've tried
RETURN @VisitorID 'Returns an IDataReader
SELECT @VisitorID 'Returns an IDataReader
--I've also set it up with a single output
--parameter, but that means I need to pass
--the long in by ref and that hideous to me
I am trying to get nettiers to generate a method with this signature:
public long VisitorService.GetVisitorIDByVisitorGUID(GUID visitorGUID);
Basically I want Nettiers to call ExecuteScalar instead of ExecuteReader. What am I doing wrong?
+2
a source to share
2 answers
Why not use custom data access functions to call the stored proc with ExecuteScalar? ( http://nettiers.com/DataLayer.ashx#Read_Methods:_4 )
var vistorId = (long)DataRepository.Provider.ExecuteScalar(CommandType.StoredProcedure, "_Visitor_GetVisitorIDByVisitorGUID");
The body of the stored procedure should look like this:
Select VisitorID
FROM dbo.Visitor
WHERE VisitorGUID = @VisitorGUID
Return
or
Declare @VisitorId bigint
Set @VisitorId = (
Select VisitorId
From dbo.Visitor
Where VisitorGuid = @VisitorGUID
)
Select @VisitorId
Return
+2
a source to share
For those who want to do this programmatically, the above example will not work as it is not possible to pass the required parameter value using any of these methods.
var vistorId = (long)DataRepository.Provider.ExecuteScalar(CommandType.StoredProcedure, "_Visitor_GetVisitorIDByVisitorGUID");
The only way I've found to do this is to pass the DbCommand as a single argument to the ExecuteScalar method:
int myReturnID = 0;
using (SqlCommand cmd = new SqlCommand()) {
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_MyStoredProcedure";
cmd.Parameters.Add(new SqlParameter("MyParameter", myParameter));
myReturnID = (Int32)DataRepository.Provider.ExecuteScalar(cmd);
}
0
a source to share