将标签插入sqlite DB的问题，C＃

i have 2 tables

我有2张桌子

        "CREATE TABLE if not exists tag_name( " +
        "tagid INTEGER PRIMARY KEY, " +
        "title TEXT UNIQUE);";

        "CREATE TABLE if not exists media_tags( " +
        "media_id INTEGER, " +
        "tagid INTEGER);";

Then i insert them with this code. The problem is, last_insert_rowid is not what i expect. I expect when INSERT is ignored it will return the rowid that cause the insert to fail. Example if i insert "tag1", "tag2", "tag3". then insert "tag2" i expect 2 to be returned instead of the last inserted row that was a success. How do i solve this?

然后我用这段代码插入它们。问题是,last_insert_rowid不是我所期望的。我希望当INSERT被忽略时,它将返回导致插入失败的rowid。例如,如果我插入“tag1”,“tag2”,“tag3”。然后插入“tag2”我希望返回2而不是最后一次成功插入的行。我该如何解决这个问题?

        void insertMediaTags(List<string> tags, long mediaId)
        {
            foreach(string tag in tags)
            {
                command.CommandText = 
                    "INSERT OR IGNORE INTO tag_name(title) VALUES(@title); " +
                    "SELECT last_insert_rowid() AS RecordID;";
                command.Parameters.Add("@title", DbType.String).Value = tag;
                long tagid = (long)command.ExecuteScalar();
                command.CommandText =
                    "INSERT INTO media_tags(media_id, tagid) " +
                    "VALUES(@media_id, @tagid);";
                command.Parameters.Add("@media_id", DbType.Int32).Value = mediaId;
                command.Parameters.Add("@tagid", DbType.Int64).Value = tagid;
                command.ExecuteNonQuery();
            }
        }

2 个解决方案

#1

This may not be the best solution but using a SELECT statement and checking if the object is null works.

这可能不是最佳解决方案,但使用SELECT语句并检查对象是否为空。

-edit- i am too lazy to change and test it but the code should be doing a insert ... where not exist (select) for currency safety reasons.

-edit-我懒得改变并测试它但是代码应该插入...因为货币安全原因不存在(选择)。

    void insertMediaTags(List<string> tags, long mediaId)
    {
        foreach(string tag in tags)
        {
            long tagId;
            command.CommandText = "SELECT tagid FROM tag_name WHERE title=@title;";
            command.Parameters.Add("@title", DbType.String).Value = tag;
            object o = command.ExecuteScalar();
            if (o == null)
            {
                command.CommandText =
                    "INSERT OR IGNORE INTO tag_name(title) VALUES(@title); " +
                    "SELECT last_insert_rowid() AS RecordID;";
                command.Parameters.Add("@title", DbType.String).Value = tag;
                tagId = (long)command.ExecuteScalar();
            }
            else
                tagId = (long)o;

            command.CommandText =
                "INSERT INTO media_tags(media_id, tagid) " +
                "VALUES(@media_id, @tagid);";
            command.Parameters.Add("@media_id", DbType.Int32).Value = mediaId;
            command.Parameters.Add("@tagid", DbType.Int64).Value = tagId;
            command.ExecuteNonQuery();
        }
    }

#2

If you use INSERT OR REPLACE instead of INSERT OR IGNORE, I believe that last_insert_rowid will be as you wish it to be.

如果您使用INSERT OR REPLACE而不是INSERT OR IGNORE,我相信last_insert_rowid将如您所愿。

#1