C#: how to cast a database field of guid type holding a null value to a local variable?

Question

I have on one side an Access database, where Tbl_Application.id_connexion is a field with a Guid type (called 'replication ID' in ms Access terminology).

I am collecting some data from this Tbl_Application table through a DataRow[] array, dr_Tbl_Application. The following code reads the first DataRow:

private Guid? mid_connexion = null;
mid_connexion = (Guid)dr_Tbl_Application[0]["id_connexion"]

Everything is ok as long as Tbl_Application.id_connexion holds a value. In case this field does not hold a value, I will get the following error:

InvalidCastException was unhandled

And these are some things I can see in the immediate window:

? dr_Programme[0]["id_Connexion"]
{}

? dr_Programme[0]["id_Connexion"].GetType()
{Name = "DBNull" FullName = "System.DBNull"}

? dr_Programme[0]["id_Connexion"] is System.DBNull
true

So, to avoid my exception, I guess I'd better test before transfering a unique identifier value from a field in a database to a local variable. This said, I am still bothered by my finding, and I'd like to dig deeper into this issue.

My questions are the following:

Is there a way to write some basic code to assign a value from a database Guid value to a local Guid object without having to test on System.DBNull?
Why should the same instruction, applied on the same object, return different types, depending if the the original fields holds or not a value?

Edit on question 2:

? dr_Programme[0]["id_Connexion"].GetType()

returns System.Guid type when the corresponding field is populated in the original table, while

? dr_Programme[0]["id_Connexion"].GetType()

returns System.DBNull type when the field is null (or not populated) in the original table ...

have you tried `mid_connexion = (Guid?)dr_Tbl_Application[0]["id_connexion"]`note the ? to mark it as a nullable Guid like you did with your mid_connexion. Cos the `(Guid)dr_Tbl_Application[0]["id_connexion"]` will attempt to cast null to a type of GUID which is not a nullable type. — Syneryx, Dec 28 '12 at 12:51
@Syneryx That doesn't work. System.DBNull can't be cast to Nullable. — Hamlet Hakobyan, Dec 28 '12 at 12:55
@HamletHakobyan my bad. but it seems Morten mertner has an answer. — Syneryx, Dec 28 '12 at 12:57
This has already been discussed in a [similar question here](http://stackoverflow.com/questions/870697/unable-to-cast-object-of-type-system-dbnull-to-type-system-string) — Steve, Dec 28 '12 at 12:58

score 4 · Accepted Answer · edited May 23 '17 at 12:15

DBNull implements the Null Object Design Pattern to address the difference between DB returning a NULL vs. not returning anything. Unfortunately, the lack of its intuitiveness keeps tripping up programmers for many years.

The best you can do is wrapping it in a generic method, like this:

public static T? GetNullable<T>(object obj) where T : struct
{
    if (obj == DBNull.Value) return null;
    return (T?)obj;
}

Now you can call this method like this:

mid_connexion = GetNullable<Guid>(dr_Tbl_Application[0]["id_connexion"]);

Morten Mertner · Answer 2 · 2012-12-28T13:00:46.763

I'm afraid that you will have to keep checking for DBNull, unless you design your DB schema to not allow them to occur. Also note that DBNull is different from C# null.

You can probably make the code slightly less verbose by using the Convert class in C#. In general it will perform default conversions rather than throw an exception (e.g. when it encounters a null value; note that it does not to my knowledge know how to deal with DBNull values, so you'll need to deal with those manually).

My recommendation would be to create a set of extension methods to do the necessary work:

public static class AccessExtensions
{
    public static Guid GetGuidOrEmpty( this IDbReader reader, string columnName )
    {
        // all the code to check for DBNull and conversions goes here
        // ...

        return hasValue ? value : Guid.Empty;
    }
}

One last word of caution is that Access can be funny about GUIDs, such as requiring strings for insert/update but returning GUID types on select. This may have improved since I tried it in 2003 or thereabouts.

Guid management between Access tables and Access vba and\or other vb code has always been a real PITA ... — Philippe Grondier, Dec 28 '12 at 15:29

score 1 · Answer 3 · answered Dec 28 '12 at 13:12

1

Use this:

public static class SomeClass
    {
        public static Guid? With(this Guid? o, object x)
        {
            if (x is System.DBNull) return null;
            return o = (Guid)x;
        }
    }

answered Dec 28 '12 at 13:12

Evgeny Bychkov

322
1
2
11

C#: how to cast a database field of guid type holding a null value to a local variable?

3 Answers3