Exception thrown by field value for DataTable because of DBNull

1 problem Recurrence

(1) New project Dbnullexp, Project property is "Console Application";

(2) Create a new dataset under Project Schools(the suffix of the dataset file is . xsd);

(3) In the New data table Studentsunder the dataset, the table field is defined as follows:

Field name	Description
Id	dc. DataType = Type.GetType ("System.Int32");//Type dc. AutoIncrement = true;//Auto Increment dc. AutoIncrementSeed = 1;//starting at 1 dc. AutoIncrementStep = 1;//step is 1 dc. AllowDBNull = false;//Non-empty
Name	dc. DataType = Type.GetType ("System.String");//Type dc. AllowDBNull = false;//Non-empty
Age	dc. DataType = Type.GetType ("System.Int32");//Type dc. AllowDBNull = true;//can be empty
Email	dc. DataType = Type.GetType ("System.String");//Type dc. AllowDBNull = true;//can be empty

(4) Insert 5 sample data into the Students Data Sheet .

Schools.studentsdatatable SDT = new schools.studentsdatatable (); SDT. Rows.Add (new object[] {null, "Zhang San", "[Email protected]"}); SDT. Rows.Add (new object[] {null, "John Doe", and "[email protected]"}); SDT. Rows.Add (new object[] {null, "Harry", "[Email protected]"}), SDT. Rows.Add (new object[] {null, "Saturday", NULL, NULL}); SDT. Rows.Add (new object[] {null, "Wu seven", "[Email protected]"});

(5) Query data Sheet SDT, get the student information of age greater than 24 output to the interface.

var newstudentlist = SDT. Where (IT and it. Age > 25). ToList (); foreach (Var s in newstudentlist) {    Console.WriteLine (s.name);}

(6) Run the code and throw the exception as shown.

2 problem analysis and Solution

As you can see from the exception tip message, the problem is on the age field of the Students table , and the exception information indicates that the value of the Age field is DBNull, so what is DBNull, and what is the difference betweenit and the Null we often see ?

Nullrefers to an invalid object reference;DBNullis a class,DBNull.Valueis the only instance of it。 DBNullinstances ofDBNull.Value is the empty data in the database table. Netthe representation in the code. We know that when a nullable field in a database table is not assigned a value, the field in the database table is specified asNull, then thisNullvalue in. NetWhat does the code say, using theDBNull. Value. Conversely, in code, when the value of a field equalsDBNull. Value, it means that this field is stored in the databaseNull, which means that its value in the database is empty. So,DBNull.Valueobject points to a valid object, not like nulldoes not point to any valid object.

Take a look at the data from the students table in our example, as shown in.

As you can see, the value of the age field for a student named "Saturday" is empty, and there is a case where the exception information says that the age value is DBNull .

Why, then, does the age field have a value of DBNull that throws an exception? I just want to get a student's age value, no value returned to me a dbnull.value No, why would you throw an exception?

Continue digging deep into the source of the exception by looking at the InnerException information of the exception, knowing that the exception's internal exception is "invalid for the specified conversion", and locates the problem code generated by the exception.

[Global::system.diagnostics.debuggernonusercodeattribute ()] [Global::system.codedom.compiler.generatedcodeattribute ("System.Data.Design.TypedDataSetGenerator", "4.0.0.0")] public int Age {     get {         try {                 return (int) (This[this.tablestudents.agecolumn]));             }         catch (Global::system.invalidcastexception e) {             throw new global::system.data.strongtypingexception (table Students The value of "age" in column is DBNull. ", E);         }     }     set {         This[this.tablestudents.agecolumn] = value;     }}

The problem code is:

return ((int) (This[this.tablestudents.agecolumn]));

Looking at the value of This[this.tablestudents.agecolumn] in the problem code, DBNull.Value, and then implicitly converting the DBNull.Value to integer data produces an exception because the type conversion is not possible at all. This code is generated automatically when you create a new dataset and creates a table, so you need to be cautious about the changes. But I do not know why Microsoft is not here to add a special treatment, if the return value of DBNull.Value, directly return 0. Why not do so, do not go blind guess. Finding a solution matters.

3 Workaround

We use it in addition to "it." Age "to get the value of a field, you can also use" it["to get the value of the field in the table, and know that when the database field value is empty," it["" "" "the value obtained by" DBNull.Value "will not throw an exception, so we can use the" it[ "Age"] "way to get the value of the time and then compare with DBNull.Value, when not equal to the type conversion operation, so all OK." The modified code is shown below.

var newStudentList1 = SDT. Where (it = it["Age"]! = DBNull.Value && (int) it["age"] > 24). ToList ();

Execute the program again and get the results you want below.