Inserting into a SQL Server CE Database File and Returning the Inserted ID
Problem:
My request
INSERT INTO TableName(val1,val2)values(1,2);
SELECT @@IDENTITY;
When I run it in a launch request from the server explorer, I get the correct result.
But when I use ExecuteScalar
or ExecuteDataTable
, I get an error, ... query return null
public object ExecuteScalre(string Query, CommandType type)
{
OpenConnection();
cmd.CommandText = Query;
cmd.CommandType = type;
object obj = null;
try
{
obj = cmd.ExecuteScalar();
}
catch
{
}
finally
{
ReleaseResource();
}
return obj;
}
public DataTable ExecuteDataTable(string Query, CommandType type)
{
OpenConnection();
cmd.CommandText = Query;
cmd.CommandType = type;
DataTable dt = new DataTable();
dataAdaptor = new SqlCeDataAdapter(cmd);
try
{
dataAdaptor.Fill(dt);
}
catch
{
}
finally
{
ReleaseResource();
}
return dt;
}
Notes: this is a file .sdf
(SQL Server CE), NOT .mdf
, so we cannot use stored procedures
source to share
Sql Server Compact Edition does not support multiple operators in one query. This database is (usually) used in a single user scenario, so you can split your command and send two queries to the database, the first inserts a record, the second returns @@ IDENTITY.
cmd = new SqlCeCommand("INSERT INTO TableName(val1,val2)values(1,2)", cn);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT @@IDENTITY";
int result = Convert.ToInt32(cmd.ExecuteScalar());
source to share
This is due to the fact that you are sending two sql commands into one Command object. The operator INSERT
did not return anything, this is the correct behavior.
Use OUTPUT
-Clause from TSQL. This will give you values from inserted or deleted rows as a recordset. So you can use ExecuteScalar
to get this value.
Suppose you have a table with the following structure
CREATE TABLE [dbo].[Table_1]
([ID] [int] IDENTITY(1,1) NOT NULL,
[Value1] [int] NOT NULL,
[Value2] [int] NULL ) ON [PRIMARY]
Using the following SQL you will get the id of the row inserted as a result set
insert Table_1 OUTPUT Inserted.ID values (1,2)
source to share