C#:如何获取使用Informix插入的最后一行的ID号 [英] C#: How do I get the ID number of the last row inserted using Informix

问题描述

我正在使用Informix.NET驱动程序(C#)将一行插入数据库表中.效果很好，除了我不知道如何获取最后插入的行的ID:

I am using the Informix.NET driver (C#) to insert a row into a database table. This works fine, except I have no idea how to get the ID of the last inserted row:

插入命令:

    //Sample Query: "INSERT INTO names (id, name) values (0, 'John Smith');"
    public static void Insert(String query)
    {
        try
        {
            using (IFX::IfxConnection connection = ConnectionManager.GetConnection())
            {
                connection.Open();

                using (IFX::IfxCommand command = new IFX::IfxCommand(query, connection))
                    command.ExecuteNonQuery();

                if (connection != null) connection.Close();
            }
        }
        catch (IFX::IfxException)
        {
            throw;
        }
        catch (Exception)
        {
            throw;
        }
    }

如果我更改查询，使其包括:从systables的WHERE tabid = 1中选择SELECT DBINFO('sqlca.sqlerrd1')；"我遇到错误(此行使用unix dbaccess可以工作).我遇到错误.

If I change the query so that it includes: "SELECT DBINFO ('sqlca.sqlerrd1') FROM systables WHERE tabid = 1;" I get errors (this line works using unix dbaccess) I get errors.

我还尝试更改某些内容，例如使用IFX::IfxReader reader = command.ExecuteReader()尝试获取结果并读取这些结果，但是出现错误. (错误[HY000] [Informix .NET提供程序] [Informix]在多查询准备中不能使用select或任何数据库语句.").我也尝试过在command.Prepare();之前进行此操作，但这无济于事.我真的不确定如何通过C#Informix.NET客户端SDK进行插入并获取ID.

I've also tried to change things such as using IFX::IfxReader reader = command.ExecuteReader() to try to get the results and read those results, however I get errors. ("ERROR [HY000] [Informix .NET provider][Informix]Cannot use a select or any of the database statements in a multi-query prepare."). I've also tried to precede this with command.Prepare();, but that does nothing. I'm really not sure how to do an insert and get the ID through C# Informix.NET Client SDK.

=========
哦，我知道我可以分别运行这两个语句，它们会起作用，但我担心在第一个和获取ID号之间会执行另一个插入操作，这会导致错误.

=========
Oh, and I know I can run the two statements separately, which will work, except I'm worried that another insert will be executed in between the first and getting the ID number, which would result in errors.

推荐答案

我以前从未使用过此框架，但是我确实在另一个与之合作的框架中使用过此框架，因此最终要做的是完全单独查询以获取新元素的ID.

Granted I've never used this framework before, but I did needed like this in a different framework I worked with so what I ended up doing was doing a completely separate query to get the new element's ID.

我的代码在PHP中，但在伪代码中，它看起来像:

My code was in PHP but in pseudocode it looked like:

Insert(String query)
{
   Execute the Sql Query: query
   result = Execute the Sql Query: "SELECT last_insert_id"
   return the first element of result
}

我没有注意到您的编辑，对我来说这不是问题，因为它是PHP，所以它是单线程的，因此不可能在两次查询之间执行另一条语句.但是，如果在插入中添加锁，则可以强制两个查询都作为事务发生.同样，使用RegEx一点，您可能可以构造一个使用Insert语句中的信息的Select语句.

I didn't notice your edit, and that wasn't an issue for me because it was PHP so it was single-threaded and so it was impossible for another statement to execute in the time between the two queries. However, if you add a lock to inserts you can force that both queries happen as a transaction. Also with a bit of RegEx you could probably construct a Select statement that uses the information in the Insert statement.

这篇关于C#:如何获取使用Informix插入的最后一行的ID号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！