CODEWITHSUNDEEP
c#sql
Amicable
Amicable

Reputation: 3101

SELECT query on Nvarchar column returning Int

I'm trying to return a string value from my database but instead the query is returning "0" although the SELECT query is targeting a nvarchar column. The query is valid and runs correctly, returning "KYO" when run using SQL-SMS.

This is the method, which works as expected in the other place I use it, that I use for returning data:

    public static object GetData(string sql, SqlParameter[] parameters)
    {
        try
        {
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (DbCommand command = factory.CreateCommand())
                {
                    command.Connection  = connection;
                    command.CommandType = CommandType.Text;
                    command.CommandText = sql;

                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            if (parameter != null)
                                command.Parameters.Add(parameter);
                        }
                    }

                    object result = null;
                    SqlParameter returnValue = new SqlParameter("ReturnValue", result);
                    returnValue.Direction    = ParameterDirection.ReturnValue;
                    command.Parameters.Add(returnValue);

                    connection.Open();
                    command.ExecuteScalar();
                    result = command.Parameters["ReturnValue"].Value;
                    return result;
                }
            }
        }
        catch (Exception)
        {
            throw;
        }
    }
}

This is the method which is throwing a cast exception as it's returning an int instead of a string:

    private static String GetManufacturerCode(Int32 manufacturerID)
    {
        try
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ManufacturerCode FROM Manufacturers WHERE ManufacturerID = @ID");

            SqlParameter id = new SqlParameter("@ID", manufacturerID);

            return(String)DB.GetData(sql.ToString(), new[] { id });
        }
        catch (Exception)
        {

            throw;
        }
    }

I also set returnValue.DbType = DbType.String; as a test and this still returned an integer.

An example of where I use the GetData(...) method successfully is:

    public static Int32 GetMonitoredCount()
    {
        try
        {
            String GetMonitoredCount = "SELECT COUNT(*) FROM Devices WHERE Monitored = 1 ";

            return (Int32)DB.GetData(GetMonitoredCount, null);
        }
        catch (Exception)
        {
            throw;
        }
    }

I considered it might be returning a boolean bit but as my query executes correctly I'd have assumed it would return 1 not 0.

Why is an integer being returned? How can I return a string using my pattern?

Upvotes: 1

Views: 824

Answers (1)

Yuriy Galanter
Yuriy Galanter

Reputation: 39807

ReturnValue always returns int - this is by design.

Instead of this entire block

object result = null;
SqlParameter returnValue = new SqlParameter("ReturnValue", result);
returnValue.Direction    = ParameterDirection.ReturnValue;
command.Parameters.Add(returnValue);

connection.Open();
command.ExecuteScalar();
result = command.Parameters["ReturnValue"].Value;

Try

connection.Open();
object result = command.ExecuteScalar();

This will return you real result of your SQL statement

Method ExecuteScalar itself is capable of returning value - it return first column of the first row of the resultset and is ideal when your query returns a single value.

Upvotes: 4

Related Questions