DNB5brims DNB5brims - 1 year ago 50
SQL Question

How can I get the result of a stored procedure in C#?

Here is my code in C#:

float r_discountValue = 0;

SqlConnection con = Constant.GetConnection();

SqlCommand cmd = new SqlCommand("Coupon_GetDiscountFromValidCouponCode", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@PKCouponCode", SqlDbType.VarChar);
cmd.Parameters["@PKCouponCode"].Value = "DIS_77";


SqlDataReader reader = cmd.ExecuteReader();

r_discountValue = float.Parse(reader[0].ToString());

catch(Exception exception)
throw exception;

return r_discountValue;

The stored procedure:

ALTER PROCEDURE [dbo].[Coupon_GetDiscountFromValidCouponCode]
@PKCouponCode varchar(50)
FROM Coupon
WHERE CouponCode = @PKCouponCode AND Valid = 1

Here is how the DB looks like:

enter image description here

I encounter an error

Input string was not in a correct format

I don't know what's thing is going wrong, any ideas?

Answer Source

If you want the discount value, then you should return only the discount from the SP (since it is named GetDiscountfrom...)

SELECT CouponDiscount FROM Coupon WHERE CouponCode = @PKCouponCode AND Valid = 1

This will make it a one-column resultset, which matches the access reader[0] from C#.

The other option is of course to change the C# side to read the second item (index 1) or reference the column by name, e.g.

r_discountValue = float.Parse(reader[1].ToString());
r_discountValue = float.Parse(reader["CouponDiscount"].ToString());

You would have got Input string was not in a correct format. because it was reading "DIS_77" which float.parse cannot process.