Retrieving Scalar or Calculated Values from Stored Procedures with C#

I am trying to build an Sql page hit provider. I am having trouble getting a count back from the database. If I use ExecuteScalar it doesn't see any value in the returned R1C1. If I use ExecuteNonQuery with a @.ReturnValue, the return value parameter value is always zero. Ideally I would like to use a dynamic stored proceudre if there are any suggestions for using them with C#. My table has rvPathName, userName and a date. I have the AddWebPageHit method working so I know data connection and sql support code in provider is working. I think the problem is either in how I am writing the stored procedures or how I am trying to retrieve the data in C#. Any help with this will be greatly appreciated.

We're not going to be able to help without seeing the code, both the C# code and the stored procedure code.|||

Here you go. I worked on this for more than 8 hours so know that you help is greatly appreciated!!!

CREATE PROCEDURE dbo.WebPageHits_CountByWebPageVPathName @.webPageVPathName nvarchar(256)

AS

DECLARE @.count int

SELECT @.count = COUNT(WebPageHitId)

FROM dbo.WebPageHits

WHERE WebPageVPathName = @.webPageVPathName

RETURN (@.count)

GO

publicoverrideint GetWebPageHitCount(string webPageVPathName)

{

SecUtility.CheckParameter(ref webPageVPathName,true,false,true, 256,"webPageVPathName");

SqlConnectionHolder connectionHolder =null;

SqlConnection connection =null;

int webPageHitCount = 0;

try

{

try

{

connectionHolder =SqlConnectionHelper.GetConnection(_sqlConnectionString,true);

connection = connectionHolder.Connection;

CheckSchemaVersion(connectionHolder.Connection);

SqlCommand cmd =newSqlCommand("dbo.WebPageHits_CountByWebPageVPathName", connection);

cmd.CommandType =CommandType.StoredProcedure;

cmd.CommandTimeout = CommandTimeout;

SqlParameter p =newSqlParameter("@.ReturnValue",SqlDbType.Int);

p.Direction =ParameterDirection.ReturnValue;

cmd.Parameters.Add(p);

cmd.Parameters.Add(CreateInputParam("@.webPageVPathName",SqlDbType.VarChar, webPageVPathName));

cmd.ExecuteNonQuery();

webPageHitCount = GetReturnValue(cmd);

}

finally

{

if (connectionHolder !=null)

{

connectionHolder.Close();

connectionHolder =null;

}

}

}

catch

{

throw;

}

return webPageHitCount;

}

|||

It's a little tough, as you are using custom methods such as GetReturnValue and CreateInputParam.

My gut instinct is that you are having a problem because youare both using the wrong data type for the @.webPageVPathName as well asnot specifying the length. The lack of length specification is mostlikely causing the trouble; if you don't specify it then a length of 1is used. Use the nvarchar datatype and a length of 256 in your C#code, and I think you will have better luck.

I will tell you that it's a better practice to use Output parameters to return values from a stored procedure rather than a ReturnValue. Return values are typically used to communicate success or failure; using them in the manner you are attempting just because the value you want to communicate back to the calling code happens to be an integer can be seen as "cheating". Use an Output parameter instead.

|||

You are right about my cheating. Sometimes you have to hear things from somebody else to actually realize it even though it's right in front of your face.

I got this to work by following the aspnet profile provider code. The provider uses this stored procedure.

aspnet_Profile_GetCountOf...

If just does SELECT COUNT(*) FROM ... with no RETURN statement. Then the C# uses ExecuteScalar then dim o as object = cmd then if o <> null return cmd. My function returns an int so somehow the value gets from the cmd object to the function's return value. I don't know but it's easier.

|||

mtsonic:

If just does SELECT COUNT(*) FROM ... with no RETURN statement. Then the C# uses ExecuteScalar then dim o as object = cmd then if o <> null return cmd. My function returns an int so somehow the value gets from the cmd object to the function's return value. I don't know but it's easier.

ExecuteScalar is an OK way to accomplish what you need. Glad you got it working :-)

sql