CODEWITHSUNDEEP
c#sqliteuwp
Daniel
Daniel

Reputation: 242

Connection to database not open?

I'm currently working on a UWP app using the Microsoft.Data.Sqlite namespace.

I can write data to the database and everything but when I want to delete the database I get an error saying ExecuteNonQuery can only be called when the connection is open.

I'm really new to using SQLite and databases in general so there are bound to be some errors. I just can't seem to find anything related to this error so I have to ask here.

This is the code I use to initialize the database:

public static void InitializeDatabase()
{
    using (SqliteConnection db =
        new SqliteConnection($"Filename=DashboardDatabase.db"))
    {
        db.Open();

        const string tableCommand = "CREATE TABLE IF NOT EXISTS " +
                                    "VisibilityTable (" +
                                    "ParameterId INTEGER NOT NULL PRIMARY KEY, " +
                                    "UserVisibility INTEGER NOT NULL, " +
                                    "GeneralVisibility INTEGER NOT NULL, " +
                                    "Format TEXT NOT NULL)";

        SqliteCommand createTable = new SqliteCommand(tableCommand, db);

        try
        {
            createTable.ExecuteReader();
        }
        catch (SqliteException sqliteException)
        {
            Log.Logger.Error("An Error occurred while creating a table in the database:");
            Log.Logger.Error(sqliteException.Message);
        }

        db.Close();
    }
}

And this is the code that I use to drop the table and which creates the error es soon as it wants to run the ExecuteNonQuery method.

public static void DeleteTable()
{
    using (SqliteConnection db = new SqliteConnection("Filename=DashboardDatabase.db"))
    {
        db.Open();

        SqliteCommand deleteCommand = new SqliteCommand("DROP TABLE VisibilityTable");

        deleteCommand.ExecuteNonQuery();

        db.Close();
    }
}

I would really appreciate help in this situation.

EDIT:

As JayV pointed out in the comments I failed to add a part to my SqliteCommand...

So the solution was to change this line:

SqliteCommand deleteCommand = new SqliteCommand("DROP TABLE VisibilityTable");

to this:

SqliteCommand deleteCommand = new SqliteCommand("DROP TABLE VisibilityTable", db);

Upvotes: 0

Views: 407

Answers (1)

JayV
JayV

Reputation: 3271

When you create your deleteCommand object, you neglected to add the database connection to it.

Change the line:

SqliteCommand deleteCommand = new SqliteCommand("DROP TABLE VisibilityTable");

to:

SqliteCommand deleteCommand = new SqliteCommand("DROP TABLE VisibilityTable", db);

As an aside, you should consider wrapping the whole call in a using(..) block just like you did with the Connection.

Example:

using(SqliteCommand deleteCommand = new SqliteCommand("DROP TABLE VisibilityTable", db))
{
    deleteCommand.ExecuteNonQuery();
}

Upvotes: 1

Related Questions