Reader is not reading DB values

Question

I have this issue that is eating my mind. The thing is that the _reader is not reading anything.

I have this code:

public DataEntities.Usuario GetUser(string field, string value)
    {
        using (SqlConnection connection = new SqlConnection(@"connectionstring"))
        {
            using (SqlCommand query = new SqlCommand())
            {
                DataEntities.Usuario _usuario = new DataEntities.Usuario();

                if (field == "id" || field == "username" || field == "emailaddress")
                {
                    query.Connection = connection;
                    query.CommandType = CommandType.StoredProcedure;
                    query.CommandText = "GetUser";
                    var pField = query.Parameters.AddWithValue("@Field", field);
                    pField.Direction = ParameterDirection.Input;
                    var pValue = query.Parameters.AddWithValue("@Value", value);
                    pValue.Direction = ParameterDirection.Input;

                    try
                    {
                        connection.Open();

                        SqlDataReader _reader = query.ExecuteReader();
                        >>> while(_reader.Read())
                        {
                            _usuario.EmailAddress = (string)_reader["EmailAddress"];
                            _usuario.Username =     (string)_reader["Username"];
                            _usuario.FirstName =    (_reader["FirstName"] != DBNull.Value) ? (string)_reader["FirstName"] : string.Empty;
                            _usuario.LastName =     (_reader["LastName"] != DBNull.Value) ? (string)_reader["LastName"] : string.Empty;
                            _usuario.MobileNumber = (_reader["MobileNumber"] != DBNull.Value) ? (string)_reader["MobileNumber"] : string.Empty;
                        }
                        _reader.Close();

                        return _usuario;
                    }
                    catch (SqlException ex)
                    {
                        Console.WriteLine("SQL Error: " + ex.Message);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
                return _usuario;
            }
        }
    }

And I have this Stored Procedure:

CREATE PROCEDURE [dbo].[GetUser]
@Field nvarchar(100),
@Value nvarchar(100)
AS
SELECT EmailAddress, 
Username, 
FirstName, 
LastName, 
MobileNumber,
Register_Date 
FROM Users 
WHERE @Field = @Value
RETURN 0

And this is not returning anything. This doesn't generate an exception but when it arrives to the while loop it jumps and end it returning the created object with everything null.

Any clues? I've tried to change the STORED PROCEDURE but without luck,

Your code is NOT going to do what you think it will. You can't dynamically look at a column like that. The reason you get nothing back is because the where predicate is looking at the values of those two parameters, not the actual column data. If you pass in the same value you will get all rows in the table. Otherwise you get nothing. What you should be doing is passing in three nullable parameters and extend your where predicates to handle any of the three. — Sean Lange
– Sean Lange, Commented Oct 14, 2015 at 19:16
First, I already used a function to list all the users. Second, "return 0" is created by default by Visual Studio when creating a Stored Procedure — user3018216
– user3018216, Commented Oct 14, 2015 at 19:21
I think you are missing the point. This code will NOT get a single user. It isn't how parameters work. And even if VS adds a return 0 by default you should remove it. By default all procedures return 0 when the execution is successful. — Sean Lange
– Sean Lange, Commented Oct 14, 2015 at 19:31

Dai · Accepted Answer · 2015-10-14 19:16:36Z

2

You cannot parameterise fields in T-SQL. Your query will actually execute as WHERE 'someStringValue' = 'anotherStringValue', which will never be true and so will never return any fields (unless you pipe in @field = 'foo' and @value = 'foo', of course - in which case every row will be returned).

If you want to make the field-name a variable you'll have to use Dynamic SQL.

answered Oct 14, 2015 at 19:16

Dai

157k31 gold badges314 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michael Y. · Accepted Answer · 2015-10-14 19:17:17Z

1

It's not reading anything because SQL isn't returning anything. @Field = @Value doesn't do what you think it does. If you pass 'a' and 'b' respectively, your query would return all rows where this condition is true (which is 0 rows).

@Field is just a variable, it's not a column on users as (I'm guessing) you think it may be.

answered Oct 14, 2015 at 19:17

Michael Y.

6617 silver badges12 bronze badges

Comments

Larry · Accepted Answer · 2015-10-14 19:47:51Z

There is a confusion between variable and fields name.

You cannot do it this way. You have to use Dynamic SQL.

Please be aware of potential SQL injection attacks !

I suggest that you validate your input in the stored procedure at first.

CREATE PROCEDURE [dbo].[GetUser]
@Field nvarchar(100),
@Value nvarchar(100)
AS

-- Do check @Field to prevent SQL injection !
-- IF @Field <> 'Username' or @Field <> 'id' ...
    -- RAISEERROR ...

DECLARE @sql NVARCHAR(max)
SET @sql = 
    'SELECT EmailAddress, 
    Username, 
    FirstName, 
    LastName, 
    MobileNumber,
    Register_Date 
    FROM Users 
    WHERE [' + @Field + '] = ' + @Value

EXEC sp_executesql @sql

RETURN 0

EDIT: I just realized the dynamic SQL thing was already mentioned in an answer that has been accepted while I typed this. To avoid this answer to be redundant, here is a nice tip to handle DBNull.Value whithout repeating the _reader[""] thing.

_usuario.FirstName = (_reader["FirstName"] != DBNull.Value) ? (string)_reader["FirstName"] : string.Empty;

can be replaced with

_usuario.FirstName = _reader["FirstName"] as string ?? string.Empty;

If the field is a DBNull object, then casting it to a string will result in a null value. If it happens, the unary ?? operator transforms it to a string.Empty.

Collectives™ on Stack Overflow

Reader is not reading DB values

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related