local sqlite locked after executing SQL query [duplicate]

Question

i am trying to debug a very strange behavior of my application: the problem is that when the code reach the blob.UploadFileAsync i have an exception saying that the file in question is used by another process. if I try to run only that piece of code it upload the file normally. this is the calling method:

private async void CreateAccount_Click(object sender, RoutedEventArgs e)
{
    string from = "[email protected]";
    string email = txtEmail.Text;
    string password = PasswordGenerator.GenerateRandomPassword();
    try
    {
        if (ValidateEmail(email) == 1)
        {
            bool userExists = await databaseManager.CheckIfExistsAsync("users", "userName", email);
            if (userExists)
            {
                ((MapViewModel)DataContext).MyVariable = "user already exists";
                return;
            }
            else
            {
                string subject = "new account";
                string bodyemail = $"Dear,Your new password is: {password}";

                ((MapViewModel)DataContext).MyVariable = "please wait";
                await Task.Run(() => emailService.SendEmail(from, email, subject, bodyemail));

                object[] values = { email, password, "editor" };
                string result = await databaseManager.InsertDataAsync("users", values);

                    //upload the db on the cloud
                    try
                    {
                        string baseDirectory = AppDomain.CurrentDomain.BaseDirectory;
                        string relativePath = @"filesystem\database\MainDatabase.db";
                        string fullPath = System.IO.Path.Combine(baseDirectory, relativePath);

                        blobMan blob = new();
                        await blob.UploadFileAsync(fullPath, "database");
                        ((MapViewModel)DataContext).MyVariable = "user successfully added, check your email";
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }

                }
            }
            else
            {
                ((MapViewModel)DataContext).MyVariable = "you need to insert a valid Email";
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

if I run the await blob.UploadFileAsync(fullPath, "database"); it works just fine so I isolated the problem to the two methods that interact with the database. here`s the methods:

public async Task<bool> CheckIfExistsAsync(string tableName, string columnName, string, data)
 {
     try
     {
         string query = $"SELECT COUNT(*) FROM {tableName} WHERE {columnName} = @Data";

         using (SqliteConnection connection = new SqliteConnection(connectionString))
         {
             await connection.OpenAsync();

             using (SqliteCommand command = new SqliteCommand(query, connection))
             {
                 command.Parameters.AddWithValue("@Data", data);

                 long count = (long)await command.ExecuteScalarAsync();

                 await connection.CloseAsync();

                 return count > 0;
             }

         }
         
     }
     catch (Exception ex)
     {
         Debug.WriteLine($"Error checking if data exists in database: {ex.Message}");
         return false;
     }
 }

public async Task<string> InsertDataAsync(string tableName, params object[] values)
{
    try
    {
        string query = $"INSERT INTO {tableName} VALUES ({string.Join(", ", Enumerable.Range(0, values.Length).Select(i => $"@Value{i + 1}"))})";

        using (SqliteConnection connection = new SqliteConnection(connectionString))
        {
            await connection.OpenAsync();

            using (SqliteCommand command = new SqliteCommand(query, connection))
            {
                for (int i = 0; i < values.Length; i++)
                {
                    command.Parameters.AddWithValue($"@Value{i + 1}", values[i]);
                }

                await command.ExecuteNonQueryAsync();
            }
        }

        return "ok";
    }
    catch (Exception ex)
    {
        Debug.WriteLine($"Error inserting data into database: {ex.Message}");
        return "error";
    }
}

i made good use of the Using statement to release the connection to the DB. I also tried multiple times to call the method connection.closeAsync() but it didn't change anything. I can't figure out which part of the code is locking my database file making the upload impossible.

Answer 1

This is probably due to connection pooling (see https://docs.devart.com/dotconnect/sqlite/FAQ.html#q54).

Try to add Pooling=false to your ConnectionString.