I have a class named ImageData who contains a list of Tags I get the database locked error only if an image has more than 1 tag and I can't find out why 0 tag and 1 tag is always fine, with 1 image or a 100. As soon as 1 image has 2 tags, I get the error

I make sure of disposing of everything with the using statement

here is the method

public static int addImages(List<ImageData> images)
    {
        int rows = 0;
        using (SQLiteConnection con = new SQLiteConnection(ConnectionString()))
        {
            con.Open();
            foreach (ImageData img in images)
            {
                using (SQLiteCommand cmd = new SQLiteCommand($"INSERT INTO images(Hash, Extension, Name) VALUES(@IHash,@IExtension, @IName)", con))
                {
                    cmd.Parameters.AddWithValue("@IHash", img.Hash);
                    cmd.Parameters.AddWithValue("@IExtension", img.Extension);
                    cmd.Parameters.AddWithValue("@IName", img.Name);
                    rows  = cmd.ExecuteNonQuery();
                }
                foreach (Tag tag in img.Tags)
                {
                    using (SQLiteCommand cmd = new SQLiteCommand($"INSERT INTO ImagesTags(ImageHash, TagName) VALUES(@IHash,@IName)", con))
                    {
                        cmd.Parameters.AddWithValue("@IHash", img.Hash);
                        cmd.Parameters.AddWithValue("@IName", tag.Name);
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            con.Close();
        }
        
        return rows;
    }

here are my tables Creation

string[] createTable =
        {
            "CREATE TABLE images(Hash TEXT PRIMARY KEY, Extension TEXT, Name TEXT)",
            "CREATE TABLE tags(NAME TEXT PRIMARY KEY NOT NULL,DESCRIPTION TEXT,COLLECTIONNAME TEXT)",
            "CREATE TABLE ImagesTags(ImageHash TEXT,TagName TEXT,Primary KEY (ImageHash, TagName),FOREIGN KEY (ImageHash) REFERENCES images(Hash),FOREIGN KEY (TagName) REFERENCES tags(Name))"

        };

There are multiple cases where I insert data in a foreach loop and this is the only place where I get this error.

CodePudding user response：

After googling quite a lot I learned about cleaner ways of working with sqlite.

The probleme was fixed by using using statement for every connections, commands, transactions and readers.

something somewhere must have been incorrectly disposed

here is the same function as before but with better coding I also added transactions to make only 1 call to the database and allow a rollback if something went wrong

public static int addImages(List<ImageData> images)
    {
        int rows = 0;
        using (var con = new SQLiteConnection(ConnectionString()))
        {
            con.Open();
            
            using (var tra = con.BeginTransaction())
            {
                try
                {
                    foreach (ImageData img in images)
                    {
                        SQLiteParameter p1 = new SQLiteParameter("@IHash", System.Data.DbType.String);
                        SQLiteParameter p2 = new SQLiteParameter("@IExtension", System.Data.DbType.String);
                        SQLiteParameter p3 = new SQLiteParameter("@IName", System.Data.DbType.String);
                        using (SQLiteCommand cmd = new SQLiteCommand($"INSERT INTO images(Hash, Extension, Name) VALUES(@IHash,@IExtension, @IName)", tra.Connection))
                        {

                            cmd.Parameters.Add(p1);
                            cmd.Parameters.Add(p2);
                            cmd.Parameters.Add(p3);

                            p1.Value = img.Hash;
                            p2.Value = img.Extension;
                            p3.Value = img.Name;
                            cmd.ExecuteNonQuery();
                        }

                        foreach (Tag tag in img.Tags)
                        {
                            using (SQLiteCommand cmd = new SQLiteCommand($"INSERT INTO ImagesTags(ImageHash, TagName) VALUES(@IHash,@IName)", tra.Connection))
                            {
                                cmd.Parameters.AddWithValue("@IHash", img.Hash);
                                cmd.Parameters.AddWithValue("@IName", tag.Name);
                                cmd.ExecuteNonQuery();
                            }
                        }

                    }
                    tra.Commit();
                }
                catch(Exception ex) 
                {
                    tra.Rollback();
                    throw;
                }
            }

        }
        return rows;
    }