插入 SQL Server CE 数据库文件并返回插入的 id [英] Inserting into SQL Server CE database file and return inserted id

问题描述

问题是:

我的查询

INSERT INTO TableName(val1,val2)values(1,2);
SELECT @@IDENTITY;

当我在服务器资源管理器的运行查询中运行它时，我得到了正确的结果.

When I run it in run query from server explorer I get the correct result.

但是当我使用 ExecuteScalar 或 ExecuteDataTable 时，我得到一个错误，...查询返回空

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

注意:它是一个.sdf文件(SQL Server CE)，NOT .mdf，所以我们不能使用存储过程

Notes: it's an .sdf file (SQL Server CE), NOT .mdf, so we can not use stored procedures

推荐答案

Sql Server Compact Edition 不支持在一个查询中使用多个语句.
该数据库(通常)在单个用户场景中工作，因此您可以拆分命令并向数据库发送两个查询，第一个插入记录，第二个返回 @@IDENTITY 值.

Sql Server Compact Edition doesn't support multiple statements in one query.
This database (usually) is employeed in a single user scenario, so you could split your command and send two queries to the database, the first inserts the record, the second one returns the @@IDENTITY value.

    cmd = new SqlCeCommand("INSERT INTO TableName(val1,val2)values(1,2)", cn);
    cmd.ExecuteNonQuery();
    cmd.CommandText = "SELECT @@IDENTITY";
    int result = Convert.ToInt32(cmd.ExecuteScalar());

这篇关于插入 SQL Server CE 数据库文件并返回插入的 id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！