CODEWITHSUNDEEP
c#sqlasp.netsql-server
user3018216
user3018216

Reputation:

Reader is not reading DB values

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,

Upvotes: 1

Views: 80

Answers (3)

Larry
Larry

Reputation: 18031

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.

Upvotes: 0

Michael Y.
Michael Y.

Reputation: 661

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.

Upvotes: 1

Dai
Dai

Reputation: 155055

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.

Upvotes: 2

Related Questions