dbcontext.Database.ExecuteSqlRaw can't return a string

Question

I'm trying to return a string from a SQL Server stored procedure in a Blazor app using Entity Framework.

This is follow on from another GitHub post

The in-ap code is

string outList = "";

var xparams = new SqlParameter[] {
    new SqlParameter("@Start", 10),
    new SqlParameter("@End", 20),
    new SqlParameter("@output", outList) { Direction = ParameterDirection.Output }
};

_context.Database.ExecuteSqlRaw("exec GetMe  @Start, @End, @output output", xparams);
var ReturnValue = ((SqlParameter)xparams[2]).Value;

The problem is that the return value is only the first character. The stored procedure parameters are:

@Start int,
@End int,
@output varchar(50) output

And the assignment is:

Select @output = 'This is some text';
Return

• If I use Set instead of Select, the result is the same
• If I use a scalar type like int, float, real, date, the return value is correct
• If I initialise the string to be non blank the result is still the same
• The stored procedure runs OK in SSMS

I guess it something to do with multi-dimension data.

Answer 1

You can solve problem by changing the output SqlParameter by adding the datatype such as SqlDbType.NVarChar or SqlDbType.VarChar.

This is the correct code

var xparams = new SqlParameter[] {
                  new SqlParameter("@Start", 10),
                  new SqlParameter("@End", 20),
                  new SqlParameter("@output", SqlDbType.NVarChar, 4000) { Direction = ParameterDirection.Output }
              };

SchoolContext _context = new SchoolContext();

_context.Database.ExecuteSqlRaw("exec GetMe  @Start, @End, @output output", xparams);
var ReturnValue = ((SqlParameter)xparams[2]).Value;

SqlParameter has 7 constructors, you use constructor #1, but you should instead use constructor #2 or #3:

1. SqlParameter(string parameterName, object value)
2. SqlParameter(string parameterName, SqlDbType dbType, int size)
3. SqlParameter(string parameterName, SqlDbType dbType)
4. SqlParameter()
5. SqlParameter(string parameterName, SqlDbType dbType, int size, ParameterDirection direction, bool isNullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
6. SqlParameter(string parameterName, SqlDbType dbType, int size, ParameterDirection direction, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, bool sourceColumnNullMapping, object value, string xmlSchemaCollectionDatabase, string xmlSchemaCollectionOwningSchema, string xmlSchemaCollectionName)
7. SqlParameter(string parameterName, SqlDbType dbType, int size, string sourceColumn)