Dapper can empty bool Conversion error and solution, dapperbool

Dapper can empty bool Conversion error and solution, dapperbool

Recently, entityframewok was used to generate a database and dapper was used to access the database, which resulted in an unexpected bug. The following is an example of a bug and a solution.

Because entityframework is used to generate a database, entityframewok defaults bool? Convert to tinyint (1 ),

When you use dapper to query data, an error is returned (some data is empty, some data is not empty, and the first data to be queried is empty. Otherwise, no error is reported ).

1. Define a class:

Public class BugNullable
{
Public int ID {get; set ;}
Public string Name {get; set ;}
Public bool? IsBug {get; set ;}

Public DateTime? UpdatedTime {get; set ;}
}

2. Generate database tables

Create table 'bugnullable '(
'Id' INT (11) not null AUTO_INCREMENT,
'Name' VARCHAR (50) not null default '0 ',
'Isbug 'TINYINT (1) null default null,
'Updatedtime' timestamp null default null,
Primary key ('id ')
)
COLLATE = 'utf8 _ general_ci'
ENGINE = InnoDB
AUTO_INCREMENT = 3
;
3. Fill in Data

Insert into 'bugnullable' ('id', 'name', 'isbug ', 'updatedtime') VALUES
(1, 'test', NULL, NULL ),
(2, 'test1', 1, '2017-01-14 10:05:15 ');

4. dapper. net test code

Class Program
{
Static void Main (string [] args)
{
/// <Summary>
/// Database connection string
/// </Summary>
Var connectionString = ConfigurationManager. etettings ["DefaultConnection"]. Trim ();
Using (var conn = new MySqlConnection (connectionString ))
{
// Var bugs = conn. Query <BugNullable> ("select * from BugNullable order by ID asc;"). ToList (); no bug
SqlMapper. GridReader gr = conn. QueryMultiple ("select * from BugNullable order by ID asc;"); // bug
Var bugs = gr. Read <BugNullable> (). ToList ();

Bugs. ForEach (h => {
Console. WriteLine ($ "ID: {h. ID}, Name: {h. Name}, IsBug: {h. IsBug}, UpdatedTime: {h. UpdatedTime }");
});
Conn. Close ();
}

Console. ReadLine ();
}
}

Public class BugNullable
{
Public int ID {get; set ;}
Public string Name {get; set ;}
Public bool? IsBug {get; set ;}

Public DateTime? UpdatedTime {get; set ;}
}

5. Results

6. Solution: Modify the database field size (expand tinyint (1) corresponding to bool, such as tinyint (2) or bit (1 )).