Automatic conversion between database ints and booleans in code about dapper-dot-net HOT 8 CLOSED

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

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.

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

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).

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

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?

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

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.