I'm using Dapper to execute the following query against a SQL Server 2008 R2 Express instance from a ASP.NET MVC 3 (.NET 4.0) application.
INSERT INTO Customers (
Type, Name, Address, ContactName,
ContactNumber, ContactEmail, Supplier)
VALUES (
@Type, @Name, @Address, @ContactName,
@ContactNumber, @ContactEmail, @Supplier)
SELECT @@IDENTITY
The call to connection.Query<int>(sql, ...)
is throwing an Invalid Cast Exception. I've debugged it and it's at the point where Dapper calls GetValue
on the returned SqlDataReader
.
The return type of GetValue
is Object
, inspecting it in the debugger show's it's a boxed decimal.
If I change the select to SELECT CAST(@@IDENTITY as int)
, the return of GetValue is a boxed int and the exception isn't thrown.
The Id column is definitely of type int; Why would SELECT @@IDENTITY
return a decimal?
Some additional information:
- The database is brand new.
- The Customers table is the only object I've added to it. There are no other (user) tables, views, triggers or stored procedures in the database.
- There are 10 rows in the database, there Id's are 1,2,3,4,5,6,7,8,9,10 (i.e. the column isn't beyond the limits of an int).
My table definition is
CREATE TABLE [dbo].[Customers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Type] [int] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Address] [nvarchar](1000) NOT NULL,
[ContactName] [nvarchar](255) NOT NULL,
[ContactNumber] [nvarchar](50) NOT NULL,
[ContactEmail] [nvarchar](255) NOT NULL,
[Supplier] [nvarchar](255) NOT NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]