C #: how to create a database field of type guid with zero value for a local variable?

Question

On the one hand, I have an Access database, where Tbl_Application.id_connexion is a field of type Guid (called the "replication identifier" in ms Access terminology).

I am collecting some data from this Tbl_Application table through the 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 fine as long as Tbl_Application.id_connexion contains the value. If this field does not contain a value, I will get the following error:

 InvalidCastException was unhandled 

And these are some of the things that I see in the next 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 think it's best to check before passing the unique identifier value from a field in the database to a local variable. That said, my discovery is still bothering me, and I would like to delve deeper into this problem.

My questions are as follows:

• Is there a way to write some basic code to assign a value from a database. Guid value for local Guid object without checking on System.DBNull ?
• Why does the same instruction applied to the same object return different types, depending on whether the source fields are or not?

Change to question 2:

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

returns the type System.Guid when the corresponding field is populated in the source table, and

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

returns the type System.DBNull when the field is null (or empty) in the source table ...

Answer 1

DBNull implements a null object design pattern to indicate the difference between a DB returning NULL vs. returns nothing . Unfortunately, the lack of his intuition is constantly holding back programmers.

The best you can do is wrap it in a generic method, for example:

 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 as follows:

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

Answer 2

I am afraid that you will have to keep checking DBNull if you do not develop a database schema to prevent them from appearing. Also note that DBNull is different from C # null.

You can probably make the code a little less verbose using the Convert class in C #. In general, it will perform default transformations instead of throwing an exception (for example, when it encounters a null value, note that I don’t know how to deal with DBNull values, so you have to deal with them 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 final word of caution is that Access can be ridiculous with respect to GUIDs, for example, requiring rows to insert / update, but returning GUID types when selected. It may have improved since I tried it in 2003 or so.

Answer 3

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