Returning a string from a stored procedure C#

Question

I have written a stored procedure:

USE [database]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [database table]

    @StoreNum INT,
    @CustomerID INT,
    @r VARCHAR(100) OUTPUT

AS
BEGIN

    Select @r = Notes FROM [Database table]
    WHERE CustomerID = @CustomerID 
    AND StoreNumber = CAST(@StoreNum AS NVARCHAR(10))

END

When I run this stored procedure in SQL server management studio, @r returns the string I am expecting.

However with the following C# code below I get the error: "Cannot convert from int to string".

            SqlParameter param4 = new SqlParameter("@StoreNum", storeaudit.StoreNumber);
            SqlParameter param5 = new SqlParameter("@CustomerID", "9");
            SqlParameter param7 = new SqlParameter("@r", "");

            param5.SqlDbType = System.Data.SqlDbType.Int;
            param7.Direction = System.Data.ParameterDirection.Output;

            var NoteMan = db.Database.ExecuteSqlCommand("sp_GetNotesData @StoreNum, @CustomerID, @r", param4, param5, param7);

            String managers = param7.Value.ToString();

            System.Console.WriteLine(param7.Value);

Any help would be greatly appreciated, thanks

UPDATE - Added the output and sqltype to the parameter and now the string managers returns "".

Dupe? http://stackoverflow.com/questions/290652/get-output-parameter-value-in-ado-net — Andy Wiesendanger, Jan 29 '16 at 14:26
when you use output parameter of string types, it is necessary to set the `size` and direction. You did set direction but not size. if you don't set size, you may get only single character out of string - I see update - you get nothing. Go ahead and set the size — T.S., Jan 29 '16 at 17:55

score 0 · Answer 1 · answered Jan 29 '16 at 14:30

you must change 2 things:

param5 value must be int

Set direction to output for param7

SqlParameter param4 = new SqlParameter("@StoreNum", storeaudit.StoreNumber);
SqlParameter param5 = new SqlParameter("@CustomerID", 9);
SqlParameter param7 = new SqlParameter("@r", "");
param7.Direction = System.Data.ParameterDirection.Output;

score 0 · Answer 2 · answered Jan 29 '16 at 14:31

0

you shouldn't get the return value of the stored procedure; you should take the result set.

Try this:

string str = db.Database.SqlQuery<string>("sp_GetNotesData @StoreNum, @CustomerID, @r", param4, param5, param7).First();

answered Jan 29 '16 at 14:31

stefano m

4,094
5
28
27

Added your code and debugged, the console returned:The data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types. – Cooper1810 Jan 29 '16 at 14:43
It's because you can't store the complex object in a string. http://stackoverflow.com/questions/15932239/error-in-data-reader-in-entity-framework – mariocatch Jan 29 '16 at 14:50
I have actually been able to pass the string through but the program then fails, saying cannot convert from string to int – Cooper1810 Jan 29 '16 at 14:55

Returning a string from a stored procedure C#

2 Answers2