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? There are no triggers in the database. Currently, the Customers table is the only object i have added to the database.
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]