how to check if a table exists in C#

Question

First, let me tell you I checked a bunch of the "How to check if a table exists in ...". I nevertheless need some more information about the query

SELECT name FROM sqlite_master WHERE type='table' AND name='table_name';

I supposed I had to change the names "sqlite_master" and "table_name", and here is my code

// a static function of the public class "SqliteBase"
public static void CreerBase(string dataSource)
{
    SQLiteConnection connection = new SQLiteConnection();

    connection.ConnectionString = "Data Source=" + dataSource;
    connection.Open();
    SQLiteCommand command = new SQLiteCommand(connection);

    // Create table if it does not exist
    command.CommandText = "CREATE TABLE IF NOT EXISTS beispiel ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name VARCHAR(100) NOT NULL);";
    Console.WriteLine("La Table a bien été créée");
    command.ExecuteNonQuery();
    command.Dispose();
    connection.Close();
    connection.Dispose();
}

and the unit test function :

[TestMethod]  
public void LaCreationBaseMarche()
{
    string dataSource = "beispiel.db";
    SqliteBase.CreerBase(dataSource);
    SQLiteConnection connection = new SQLiteConnection();

    connection.ConnectionString = "Data Source=" + dataSource;
    connection.Open();
    SQLiteCommand command = new SQLiteCommand(connection);
    command.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'beispiel';";
    SQLiteDataReader reader = command.ExecuteReader();
    Assert.Equals("beispiel", reader[0].ToString());
    reader.Close();
    reader.Dispose();

    command.Dispose();

}

My problem is : the command.executeReader() of the test method returns me a "null" reader, and of course i got an error when I try to do reader[0].. Did I misused the query ?

EDIT : Ok, I though I had to use the name of the file ^^. Now I changed it, but it still doesn't work (same error). I also changed the name "exemple.db" in "beispiel.db". I updated my code :)

Thank you in advance for your answers :)

score 5 · Answer 1 · answered Jul 27 '17 at 08:05

5

No, you didn't have to change the sqlite_master. That's SQLite's metadata table which contains information about all objects known to SQLite.

So your query will become:

SELECT name FROM sqlite_master WHERE type='table' AND name='beispiel';

answered Jul 27 '17 at 08:05

CodeCaster

147,647
23
218
272

Ok, I though I had to use the name of the file ^^. Now I changed it, but it still doesn't work (same error). – Doe Jowns Jul 27 '17 at 08:09

score 4 · Answer 2 · answered Apr 18 '21 at 00:13

4

I did the following to check if a table already exists in the database

public static bool tableAlreadyExists(SqliteConnection openConnection, string tableName)
{
  var sql = 
  "SELECT name FROM sqlite_master WHERE type='table' AND name='"+tableName +"';"; 
  if(openConnection.State == System.Data.ConnectionState.Open)
  {   
        SqliteCommand command = new SqliteCommand(sql, openConnection);
        SqliteDataReader reader =command.ExecuteReader();
        if(reader.HasRows)
        {
            return true;
        }
        return false;
    }else{
        throw new System.ArgumentException("Data.ConnectionState must be open");
    }
}

answered Apr 18 '21 at 00:13

surfmuggle

5,527
7
48
77

This is quite neat useful for testing as well when you use an in-memory version of the Sqlite database – Trevor Apr 22 '22 at 22:17
Very useful method, I would sugest though, to write it as an extension method: `public static bool ExistsTable(this SQLiteConnection con, string tableName) { ... }` – Paul Efford May 10 '22 at 11:39
A very nice solution – j.hull Jan 06 '23 at 17:22

Romano Zumbé · Accepted Answer · 2017-07-27T09:16:18.753

2

As far as I see it, you don't read from the reader:

[TestMethod]
public void LaCreationBaseMarche()
{
    string dataSource = "exemple.db";
    SqliteBase.CreerBase(dataSource);
    SQLiteConnection connection = new SQLiteConnection();

    connection.ConnectionString = "Data Source=" + dataSource;
    connection.Open();
    SQLiteCommand command = new SQLiteCommand(connection);
    command.CommandText = "SELECT name FROM exemple WHERE type = 'table' AND name = 'beispiel';";
    SQLiteDataReader reader = command.ExecuteReader();

    while (reader.Read())
    {
        Assert.Equals("beispiel", reader[0].ToString());
    }

    reader.Close();
    reader.Dispose();
    command.Dispose();
}

EDIT

A possible problem is the dataSource. You have to make sure, that both methods access the same location.

edited Jul 27 '17 at 09:16

answered Jul 27 '17 at 08:10

Romano Zumbé

7,893
4
33
55

That's a point, but the main problem is that my reader actually hasn't rows ^^ ! And I don't understand why my previous query doesn't work x) – Doe Jowns Jul 27 '17 at 08:15
1

The method `CreerBase` works and the table is created? – Romano Zumbé Jul 27 '17 at 08:17
Ok, good question.. Actually, it was what i was trying to test.. How can you figure that out ? Where should it be if it exists ? (I'm working on Visual Studio 2015) – Doe Jowns Jul 27 '17 at 08:22
Simplest solution I can think of right now: change your querry to "SELECT name FROM exemple WHERE type = 'table'" and debug the loop. Take a look at all results – Romano Zumbé Jul 27 '17 at 08:24
Ok the reader's value is still "null". But then, what is wrong in my first code part ? – Doe Jowns Jul 27 '17 at 08:26
If you changed your querry as I suggested, I think there is a problem with your database connection. It should return you at least something – Romano Zumbé Jul 27 '17 at 08:32
What I meant was " SELECT name FROM sqlite_master WHERE type='table' " – Romano Zumbé Jul 27 '17 at 08:32
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/150270/discussion-between-doe-jowns-and-romano-zumbe). – Doe Jowns Jul 27 '17 at 08:34
The problem was indeed in the datasource name and not in the query ! Thanks for helping :) – Doe Jowns Jul 27 '17 at 09:33

score 0 · Answer 4 · answered Jul 27 '17 at 08:05

0

You have to query sqlite_master table:

SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'beispiel';

answered Jul 27 '17 at 08:05

vasek

2,759
1
26
30

how to check if a table exists in C#

4 Answers4