CODEWITHSUNDEEP
c#ms-accessguid
Philippe Grondier
Philippe Grondier

Reputation: 11138

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

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:

  1. 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?
  2. 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 ...

Upvotes: 4

Views: 3663

Answers (3)

Evgeny Bychkov
Evgeny Bychkov

Reputation: 332

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;
        }
    }

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726489

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"]);

Upvotes: 4

Morten Mertner
Morten Mertner

Reputation: 9474

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.

Upvotes: 1

Related Questions