Sqlite.net ...获取最后一个插入ID ... [英] Sqlite.net... get last insert id...

问题描述

我正准备在下周接受C#的求职面试...以为我会写一个小应用程序来管理我的Tablature/Lyrics集合...我认为应该很简单...我在从sqlite数据库中检索最后一个插入ID时遇到问题...插入工作正常，但是我似乎无法使后续查询正常工作...代码在下面...

I''m trying to bone up on C# for a job interview I have this coming week... thought I''d write a little application to manage my Tablature/Lyrics collection... should be pretty simple me thinks... I''m having problems retrieving the last insert id from an sqlite database... the insert IS working, but I cannot seem to get the follow up query to work... the code is below...

           String connString = "Data Source=" + Properties.Resources.dataSource;
                SQLiteConnection conn = new SQLiteConnection(connString);

                SQLiteCommand cmd = new SQLiteCommand(conn);
                
                cmd.CommandText = "insert into lyrics (song, lyrics) values (@title, @words);";
                cmd.Parameters.AddWithValue("@title", _title);
                cmd.Parameters.AddWithValue("@words", _words);
                
                SQLiteCommand cmd_id = new SQLiteCommand(conn);
                cmd_id.CommandText = "select last_insert_rowid() as id from lyrics";
                
                conn.Open();
                cmd.ExecuteNonQuery();

<big>                _id = (int) cmd_id.ExecuteScalar(); <--- the code gags on this telling me I am not getting a valid value!!! 
</big>                //cmd_id.
                conn.Close();
                conn.Dispose();
                return true;

任何建议将不胜感激...我已经遍及Google了(这是我所拥有的代码的来源)...

谢谢

Michael

Any suggestions would be appreciated... I''ve been all over Google with this (which is where I got the code that I have)...

Thanks

Michael

推荐答案

如何将ExecuteScalar()的返回值分配给Object类型的temp变量，然后在调试器下查看要返回的内容? br/>
换句话说:

How about assigning the return value from ExecuteScalar() to a temp variable of type Object, and then looking under a debugger to see what is being returned?

In other words:

object val = cmd_id.ExecuteScalar();
_id = (int)val; // Set a breakpoint here, and examine the contents of val

也许您正在返回DbNull值，表明您的查询未正确执行.也许last_insert_rowid()返回的值不是int类型.

Maybe you''re getting back a DbNull value, indicating that your query didn''t execute properly. Or perhaps the value that is being returned by last_insert_rowid() isn''t of type int.

好，谢谢您提出的对象"建议.实际上，我得到了反对，并提出了正确"的答案.如下更改了我的代码，事情似乎正在起作用...

Ok, thanks for that "object" suggestion. In fact I was getting and object back with the ''right'' answer. Changed my code as below and things seem to be working...

cmd_id.CommandText = "select last_insert_rowid() as id from lyrics";
conn.Open();
cmd.ExecuteNonQuery();
System.Object temp = cmd_id.ExecuteScalar();
_id = int.Parse (temp.ToString());

有没有一种更整洁"，更干净"的转换方法?

谢谢
Michael

Is there a ''neater'',''cleaner'' way to do that conversion?

Thanks
Michael

调试器向我展示了很长一段时间.嗯...猜想我需要重新访问基础类并将_id从int更改为long. Sqlite表的定义为"INTEGER" ...假设我需要查看Sqlite文档以了解其实际含义.

感谢您的输入！

迈克尔

The debugger shows me getting a long back. Hmmm... guess I need to revisit the underlying class and change the _id from an int to a long. The Sqlite table definition is ''INTEGER''... Suppose I need to look at the Sqlite documentation to see what that actually means.

Thanks for the input!

Michael

这篇关于Sqlite.net ...获取最后一个插入ID ...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！