Automatic conversion between database ints and booleans in code

[GoogleCodeExporter]

This isn't a bug report, it's a feature request/wish..

A lot of databases have strange behaviors when it comes to storing, indexing 
and querying boolean fields.

Most databases combine multiple bit fields in storage to save storage space. 
The downside of this, is that this costs performance and some database engine 
refused to index bit fields.

One of the more popular workarounds is to store booleans fields as TinyInt. 
When false is equal to zero and true to everything else (> 0). 

And as diskspace is cheap and CPU resources are expensive I also often use this 
workaround as a optimalization technique. 

How to reproduce the situation.

[SQL]
CREATE TABLE Users (UserID int IDENTITY(1,1) not null, Email varchar(255) not 
null, Password Char(40) not null, Active TinyInt not null, )
GO
ALTER TABLE Users ADD CONSTRAINT PK_Users PRIMARY KEY CLUSTERED (UserID)
GO
ALTER TABLE Users ADD CONSTRAINT DF_Users_Active DEFAULT (0) FOR Active
GO

[C#]

public class User
{
  public User()
  {
     Email = Password = String.Empty;
  }
  public int UserID { get; set; }
  public string Email { get; set; }
  public string Password { get; set; }
  public bool Active { get; set;
}

SqlConnection conn = new SqlConnection("server=...;");
conn.Open();

//This query will throw an conversion exception (boolean != TinyInt)
var activeUsers = conn.ExecuteMapper<User>("SELECT UserID, Email, Password, 
Active FROM Users WHERE (Active=1)");

In the past I have written an DataRecordHelper class that accepts a data reader 
as input. My GetBoolean(int ordinal) method uses GetFieldType() to determine 
the underlying data type. If it's a bit field it uses GetBoolean, if it's a 
tinyint it uses GetByte() or it uses GetIntxx() when it's a SmallInt or a 
regular integer.  

Well this isn't the fastest way to convert data types, it is simple and easy to 
understand. For dapper.net it might be feasable to use an array of expression 
to specify a custom conversion. 

For now I'm using a workaround like:
(Query has changed to 'SELECT UserID, Email, Password, Active as _Active FROM 
Users ...')

public class User
{
  ...
  public bool Active
  {
    get { return _Active > 0; }
    set { return _Active = value ? 1 : 0; }
  }

  //Alias property for mapping tinyint <--> boolean
  public int _Active { get; set; }
}

Original issue reported on code.google.com by zypre...@gmail.com on 28 Jun 2011 at 7:51

[GoogleCodeExporter]

At the IL level there's no real difference between the two anyway; I'll try to 
make this happen.

Original comment by marc.gravell on 28 Jun 2011 at 9:07

[GoogleCodeExporter]

Another solution might a temporary override of the typemap.

Dictionary<Type, DbType> overrides = new Dictionary<Type, DbType>();
overrides[typeof(bool)] = DbType.Byte;
overrides[typeof(bool?)] = DbType.Byte;

conn.Execute(query, new { Active=1}, null, overrides);

A solution like this would imply that you need to make a copy of the static 
typemap and override some keys and to pass the customized map to all internal 
methods. 

On the other hand, consistency in a project is important, so one can argue that 
changes to the typemap are static. Queries can be cached and has the least 
amount of impact on performance.

public class SqlMapper
{
  public void OverrideType<T>(DbType databaseType) where T : struct
  {
     typeMap[typeof(T)] = databaseType;
  }
}

Use:
SqlMapper.OverrideType<bool>(DbType.Byte);
SqlMapper.OverrideType<bool?>(DbType.Byte);

When I think about it, I find the latter the most elegant and only a check for 
the underlying type is necessary.

Original comment by zypre...@gmail.com on 28 Jun 2011 at 1:51

[GoogleCodeExporter]

Hi,

I am encountering the same issue as bit is not encouraged for MySql (E.g 
http://www.xaprb.com/blog/2006/04/11/bit-values-in-mysql/)

I've attempted to make the following changes to be handled as a special case 
like the char & char?.

I would like some feedback on the code changes below and whether I'm doing this 
correctly?  I've practically zero knowledge on IL and would appreciate any 
comments.

Added before "if (memberType == typeof(char) || memberType == typeof(char?))" 
in "public static Func<IDataReader, T> GetClassDeserializer<T>("

                    if (memberType == typeof(bool))
                    {
                        il.EmitCall(OpCodes.Call, typeof(SqlMapper).GetMethod("ReadBool", BindingFlags.Static | BindingFlags.Public), null); // stack is now [target][target][typed-value]
                    }
                    else if (memberType == typeof(char) || memberType == typeof(char?))

Added after "if (typeof(T) == typeof(System.Data.Linq.Binary))" in "private 
static Func<IDataReader, T> GetStructDeserializer<T>(int index)"

           if (typeof(T) == typeof(bool))
            { // this *does* need special handling, though
                Func<IDataReader, T> meth = r => (T)Convert.ChangeType(SqlMapper.ReadBool(r.GetValue(index)), typeof(T));
                return meth;
            }

Added after "public static char? ReadNullableChar(object value)"

        [Browsable(false), EditorBrowsable(EditorBrowsableState.Never)]
        [Obsolete("This method is for internal usage only", false)]
        public static bool ReadBool(object value)
        {
            if (value.GetType() != typeof(bool))
            {
                return Convert.ToBoolean(value);
            }
            else
                return (bool)value;
        }

Original comment by charles...@gmail.com on 12 Sep 2011 at 2:18

[GoogleCodeExporter]

The project I'm currently working on uses Dapper with SQLite. In SQLite there's 
no concept of bit or bool, only numbers. So I'd like to see that happen as 
well... if the target type is a bool, set it with a comparison != 0.

Please note that moreover SQLite doesn't really have a number size, so any 
integer is seen by the .NET provider as long (Int64). So ideally the conversion 
would need to be able to accept any integer (byte, short, int, long).

A related but different issue is that an automatic conversion from long -> int 
(or any other int size, really) would be nice as well... Simply throw when out 
of range? Currently with Dapper I have to get all my SQLite numbers as long, 
even if I know they fall in the 1-5 range (byte).

Original comment by joel.du...@gmail.com on 5 Jul 2012 at 5:48

[GoogleCodeExporter]

This is fixed in 12.1.1 by the new core (IL-level) conversions

Original comment by marc.gravell on 19 Sep 2012 at 1:40

• Changed state: Fixed

[GoogleCodeExporter]

I've been plagued by this issue in SQLite for a multi DP provider app (SQL 
Server, SqlCe and SQLite) when refactoring from ado to Dapper using 
DbProviderFactory. For example, I get a cast error from Dapper if I do the 
following:

dbc.Query<int>("SELECT COUNT(Id) FROM SomeTable").Single();

From what I understand, COUNT in Sqlite always returns a long. 
System.Data.Sqlite returns a long and Dapper passes it back to my application 
as System.Data.Sqlite delivered it. Was the fix above meant to address this 
type of scenario?

I can get around the count example above easily enough by using a long, but 
this one is a bigger problem:

int newId = dbc.Query<int>("SELECT last_insert_rowid()").Single();

I get the same cast error as above. The app I am working with uses 32 bit 
integers for Id fields on models, so changing these to longs is not practical. 
Any hope of addressing this in Dapper? Or are we limited because of the way 
SQLite and System.Data.Sqlite behave?

Original comment by j...@cybertechnical.com on 14 Mar 2013 at 8:47

[GoogleCodeExporter]

What does last-insert-rowed return? In SQL server, identity values from 
@@identity / scope-identity are technically decimal, for example.

Original comment by marc.gravell on 14 Mar 2013 at 9:11

[GoogleCodeExporter]

From http://www.sqlite.org/lang_createtable.html:
"Every row of every SQLite table has a 64-bit signed integer key that uniquely 
identifies the row within its table. This integer is usually called the 
"rowid". The rowid value can be accessed using one of the special 
case-independent names "rowid", "oid", or "_rowid_" in place of a column 
name....."

So, calling last_insert_rowid() via System.Data.Sqlite sends back a long. 
Before moving to Dapper I was doing ado stuff like this:
ModelId = Convert.ToInt32(rdr["ID"]) or int newId = 
Convert.ToInt32(cmd.ExecuteScalar())

So the fact that a long was returned never became an issue.

One approach I tried using Dapper was:
int newId = Convert.ToInt32(dbc.Query("SELECT last_insert_rowid() AS 
NewId").Single().NewId);

That works. But the fact that I have to use something like the following to get 
a count on a table no matter how small seems wrong to me:
dbc.Query<int64>("SELECT COUNT(Id) FROM SomeTable").Single();

So, lots of conversions because of SQLite's behavior. I understand that I am 
basically asking for Dapper to do this conversion for me, but I'm hoping that 
Dapper can handle it more efficiently than the process above where I am 
creating an anonymous type and then converting.

Original comment by j...@cybertechnical.com on 14 Mar 2013 at 9:43