c# SqlDataReader Database stored guid or null

Admin 1
Viewed 1428

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)
}
sqldatareader guid c#
2 Answers

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.

Joel_Coehoorn_30430

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.

user_11323340
Related