Edit the values returned from an SQL Query

November 11, 2021

For a project I am working on I have this OwnershipRole record:

public record OwnershipRole
{

    public OwnershipRole()
    {

    }

    public OwnershipRole(Guid id, string title, bool isWithdrawn)
    {
        Id = id;
        Title = title;
        IsWithdrawn = isWithdrawn;
    }

    public Guid Id { get; set; }
    public string Title { get; set; }
    public bool IsWithdrawn { get; set; }
}
}

For getting these details I am using an sql query like so:

    public List<OwnershipRole> GetOwnershipRoles()
    {

        var sql = @"
SELECT ItemID, Title, Status
FROM ItemDetail";


        var data = GetTable(sql);

        return data.Select().Select(dr => new OwnershipRole
        {
            Id = dr.Field<Guid>(0),
            Title = dr.Field<string>(1),
            IsWithdrawn = !dr.Field<bool>(2)
        }).ToList();
    }

    private DataTable GetTable(string sql)
    {
        var rv = new DataTable();
        using var cnn = new SqlConnection(_connectionString);
        using var cmd = new SqlCommand(sql, cnn);
        cnn.Open();

        var da = new SqlDataAdapter(cmd);
        da.Fill(rv);
    
        cnn.Close();
        return rv;
    }

We are grabbing the results and putting them into a data table. The issue I am having however is that the values from the column Status are actually strings. So I am wondering is it possible have a condition of something like:

if (Status == "Withdrawn") {
Status == false

}

I know that won’t work but is there a way I can manipulate the values that I get from the Status column in order to fit in with the bool parameter in the OwnershipRole class?

>Solution :

Sure, either:

        var sql = @"
SELECT ItemID, Title, CASE WHEN Status='Withdrawn' then 1 else 0 end IsWithdrawn
FROM ItemDetail";

    return data.Select().Select(dr => new OwnershipRole
    {
        Id = dr.Field<Guid>(0),
        Title = dr.Field<string>(1),
        IsWithdrawn = dr.Field<string>(2)=="Withdrawn"?true:false
    }).ToList();