c# SqlDataReader Database stored guid or null

Question

I have two records in the Database Record 1 and Record 2. The datareader will return guid value for record 1 and the code works fine. But on record 2 the datareader will return "". My question is How can I add String Empty without the exception, the two options both error. Immediate Win

?dataReader["Id"].GetType().FullName
"System.Guid"

Code

while (dataReader.Read())
{
d.id = new Guid(dataReader["Id"].ToString());
//Guid.TryParse(dataReader["Id"]?.ToString(), out d.Id)
}

Check to see if the object in the data reader is null before calling .ToString(): `if (dataReader["Id"] != DBNull.Value) { d.id = new Guid(dataReader["Id"].ToString()); }`. — haldo, Nov 14 '18 at 15:50

score 2 · Accepted Answer · answered Nov 14 '18 at 15:54

I see this:

?dataReader["Id"].GetType().FullName
"System.Guid"

and also this:

on record 2 the datareader will return ""

Those two things are incompatible. I suspect you really have a Guid column and record 2 is returning NULL, which in turn shows up as DBNull.Value in the reader, and calling .ToString() for the DBNull.Value result then produces the empty string you observe here.

If that's true, you can do this:

while (dataReader.Read())
{
    if (dataReader["Id"] != DBNull.Value)
    {
        d.id = (Guid)dataReader["Id"];
    }
    else
    {
        //get an empty string
    }
}

Now the problem is that else block. The question says this:

How can I add String Empty without the exception

The answer is: you can't. C# is a strongly-typed language, and we have already seen the d.id property is a System.Guid. You can't fit a string-shaped peg into a Guid-shaped hole. You have to leave this field empty, or define some default Guid value to mean the value is still empty, and then change code elsewhere to display an empty string rather than the Guid then the value matches that chosen default.

Cee McSharpface · Answer 2 · 2018-11-14T15:57:03.623

1

It is always a good idea to anticipate all possible cases:

Guid guid;

if(dataReader.HasRows)
{
    while(dataReader.Read()) 
    {
        if(dataReader["Id"].Equals(DBNull.Value)==false)
        {
            var sid = dataReader["Id"].ToString();
            if(sid.Length > 0 && Guid.TryParse(sid, out guid))
            {
                d.id = guid;
            }
        }
    }
}

If you have to do this frequently, write a helper library function for the task.

Depending on the underlying type of the database column, you can simplify. For the likely example of uniqueidentifier in MS SQL Server, it would reduce to:

if(dataReader["Id"].Equals(DBNull.Value) == false)
{
    d.id = Convert.ToGuid(dataReader["Id"]);
}

because in this case, there cannot be a type mismatch if there is a non-null value.

edited Nov 14 '18 at 15:57

answered Nov 14 '18 at 15:50

Cee McSharpface

8,493
3
36
77

Is there any advantage in using the Equals operator? Surely "dataReader["Id"] != DBNull.Value" is simpler and easier to understand? – Polyfun Nov 14 '18 at 15:58
honestly I don't know. probably. `dr["column"] is DBNull` [is also popular](https://stackoverflow.com/a/3393971/1132334). – Cee McSharpface Nov 14 '18 at 16:38

c# SqlDataReader Database stored guid or null

2 Answers2