Returning Output Param from SQL Connection

Question

Is this possible or the right way to go about this (if you're not using a stored proc):

conn.CommandText = "set @TotalPts = select SUM (pts) from cars where completedDate is not null and customerID = @customerID";
                conn.AddParam("@customerID", customerID);
                conn.AddOutputParam("@TotalPts", SqlDbType.Int);

                return (int)conn.OutputParamValue;

I want to get back the sum so I can return it in my C# method. I'm not sure how to set the output param in the string.

Answer 1

Use ExecuteScalar along with "select SUM (pts) from cars where completedDate is not null and customerID = @customerID".

It will give u what's in the first row / column, which is what u want.

Answer 2

You can instead use ExecuteScalar method as following.

conn.CommandText = select SUM (pts) 
                    from cars 
                    where completedDate is not null 
                           and customerID = @customerID";

obj count=cmd.ExecuteScaler();

Convert that count to Integer using Convert.ToInt32 function.

Answer 3

"I'm not sure how to set the output param in the string."

select @TotalPts = SUM(pts) from ...