checking for null value using dapper.net

c# dapper sql-server

Question

I am using dapper to read values into an object I created where I us a basic stored procedure.

IF EXISTS(SELECT LOWER(UT.UserID) FROM UserTable UT WHERE UT.UserID = @UserId)
BEGIN
    SELECT 
    UT.UserID,
    UT.Name, 
    UT.Surname, 
    UT.TrackingString
    FROM UserTable UT
    WHERE UT.UserID = @UserId
END

and this is the code in my view model.

 IDbConnection connection;
        using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Liberty"].ToString()))
        {
            var result = connection.QueryMultiple("GetUserData", new { UserId = userId.ToLower() }, commandType: CommandType.StoredProcedure);
            user = result.Read<UserData>().First();

        }

How can I check in the result if columns were selected.. As it is at the moment I get an InvalidOperation Exception if there is no user data(if the user does not exist).

Accepted Answer

Use the correct Linq method. You're using one that treats no results as an invalid result, whereas your query may return no values if no such user exists.

Switch to a method that doesn't throw on no elements in the return enumerable, then check for null and handle it gracefully.

    // snip
    user = result.Read<UserData>().FirstOrDefault();
}
if(user == null)
{
    // no such user exists, go do something about it

Popular Answer

Why are you using QueryMultiple? Use Query<> instead. Then just do result.Count() before result.first(). Or do FirstOrDefault()

Might I also suggest that you create a class to receive the data?

public class User
{
    public int UserID {get; set;}
....
}

Then use connection.Query("....", new...)

Much nicer to the eyes.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why