Why does PetaPoco return from a db.Insert a ID field of 7 as a decimal

Question

When doing a Insert the variable id is returned as an object. However in my database it is an int and in my POCO it is an int however when the method call to ExecuteScalar to return @@IDENTITY is called it returns the number 7 as an object but the debugger thinks its a decimal.

Therefore when I do int newID = (int)db.Insert(...) it throws a

InvalidCastException

Is this a framework bug or a PetaPoco bug?

Seems weird. What is database.LastSql set to just before the crash? — Typo Johnson, Jun 07 '11 at 22:02
Is your identity column a decimal? If so maybe PetaPoco needs to cast the ExecuteScalar result to (int) : http://stackoverflow.com/questions/2601620/why-does-select-scope-identity-return-a-decimal-instead-of-an-integer — Typo Johnson, Jun 07 '11 at 22:08

Schotime · Answer 1 · 2011-06-10T04:17:03.447

7

Also remember that if you have set your class up and you insert like so.

[TableName("Users")]
[PrimaryKey("Id")]
public class User {
    public int Id {get;set;}
    public string Name {get;set;}
}

var user = new User() { Name = "My Name" };
db.Insert(user);
Assert.True(user.Id != 0);

user.Id will now change from 0 to the newly created identity value

edited Jun 10 '11 at 04:17

answered Jun 10 '11 at 04:10

Schotime

15,707
10
46
75

score 6 · Accepted Answer · answered Jun 07 '11 at 22:17

It's going to depend on what RDBMS you are connecting to, which you didn't state.

Here's the relevant code from PetaPoco that retrieves the last id and returns it:

object id;
switch (_dbType)
{
    case DBType.SqlServerCE:
        DoPreExecute(cmd);
        cmd.ExecuteNonQuery();
        id = ExecuteScalar<object>("SELECT @@@IDENTITY AS NewID;");
        break;
    case DBType.SqlServer:
        cmd.CommandText += ";\nSELECT SCOPE_IDENTITY() AS NewID;";
        DoPreExecute(cmd);
        id = cmd.ExecuteScalar();
        break;
    case DBType.PostgreSQL:
        if (primaryKeyName != null)
        {
            cmd.CommandText += string.Format("returning {0} as NewID", EscapeSqlIdentifier(primaryKeyName));
            DoPreExecute(cmd);
            id = cmd.ExecuteScalar();
        }
        else
        {
            id = -1;
            DoPreExecute(cmd);
            cmd.ExecuteNonQuery();
        }
        break;
    case DBType.Oracle:
        if (primaryKeyName != null)
        {
            cmd.CommandText += string.Format(" returning {0} into :newid", EscapeSqlIdentifier(primaryKeyName));
            var param = cmd.CreateParameter();
            param.ParameterName = ":newid";
            param.Value = DBNull.Value;
            param.Direction = ParameterDirection.ReturnValue;
            param.DbType = DbType.Int64;
            cmd.Parameters.Add(param);
            DoPreExecute(cmd);
            cmd.ExecuteNonQuery();
            id = param.Value;
        }
        else
        {
            id = -1;
            DoPreExecute(cmd);
            cmd.ExecuteNonQuery();
        }
        break;
    default:
        cmd.CommandText += ";\nSELECT @@IDENTITY AS NewID;";
        DoPreExecute(cmd);
        id = cmd.ExecuteScalar();
        break;
}

This is correct. It does depend on the db. Even if you do a count(*) is different db's they all return different types, some ints, longs, doubles and decimals. I will have a look at what can be done to make it more uniform though. — Schotime, Jun 07 '11 at 22:57
Then Gareth's answer should work for you if you don't mind having a modified version of PetaPoco and all your PK fields are auto increment ids. — quentin-starin, Jun 08 '11 at 15:02

score 4 · Answer 3 · answered Sep 16 '11 at 20:17

Another two cents. As already answered PetaPoco returns a decimal for SQL Server Inserts. The question author is attemping to cast to an int which throws an InvalidCastException. However, a Type Conversion does work. So instead of this:

int newID = (int)db.Insert(...)

do this:

int newID = Convert.ToInt32(db.Insert(...))

score 1 · Answer 4 · answered Jun 08 '11 at 09:44

1

It's a guess but worth a go. In PetePoco.cs try changing :

id = ExecuteScalar<object>("SELECT @@@IDENTITY AS NewID;");

to

id = ExecuteScalar<int>("SELECT @@@IDENTITY AS NewID;");

answered Jun 08 '11 at 09:44

Typo Johnson

5,974
6
29
40

Why does PetaPoco return from a db.Insert a ID field of 7 as a decimal

4 Answers4