CODEWITHSUNDEEP
c#sqlite
SQLTrooper
SQLTrooper

Reputation: 359

How do I check whether a database exists?

I'm in C# using SQLite as an embedded database. I have got my app to create a new database on start-up, but how do I get it to check if the database exists? If it does exist how do I get it to use it, and if not how to create a new one?

What I have so far:

private void MainWindow_Loaded(object sender, EventArgs e)
{
    SQLiteConnection sqlite_conn;
    SQLiteCommand sqlite_cmd;
    bool newdb = false;
    if (newdb == true)
    {
        sqlite_conn = new SQLiteConnection("DataSource=database.db;Version=3;");
        sqlite_conn.Open();
        MessageBox.Show("31");
    }
    else
    {
        sqlite_conn = new SQLiteConnection("Data Source=database.db;Version=3;New=True;Compress=True;");
        sqlite_conn.Open();
        sqlite_cmd = sqlite_conn.CreateCommand();
        sqlite_cmd.CommandText = "CREATE TABLE Client (id integer primary key, Title  varchar(100),Name  varchar(100),Surname  varchar(100),Dateofbirth DateTime , Propertyname varchar(100),Moveindate DateTime,Relationship varchar(100),Spouse  varchar(100),Gender  varchar(100), spTitle  varchar(100),SpouseName  varchar(100),SpouseSurname  varchar(100),spDateofbirth DateTime ,spRelationship varchar(100),spSpouse  varchar(100),spGender  varchar(100));";
        sqlite_cmd.ExecuteNonQuery();
    }
    sqlite_conn.Close();
    MessageBox.Show("dasdas");
}

Upvotes: 23

Views: 38455

Answers (4)

TylerD87
TylerD87

Reputation: 1618

How about:

if(File.Exists("database.db"))

Upvotes: 33

Kabilan Smart
Kabilan Smart

Reputation: 186

public async Task<bool> IsDbExists(string fileName)
    {
        try
        {
            var item = await ApplicationData.Current.LocalFolder.GetFileAsync(fileName);
            var db = new SQLiteConnection(DbHelper.DBPATH);
            var tb1 = db.GetTableInfo("Domain");
            var tb2 = db.GetTableInfo("Account");
            var tb3 = db.GetTableInfo("Product");
            var tb4 = db.GetTableInfo("Review");
            if (item == null || tb1.Count == 0 || tb2.Count == 0 || tb3.Count == 0 || tb4.Count == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        catch
        {
            return false;
        }
    }

Upvotes: 6

raiserle
raiserle

Reputation: 696

You can use this to extract the file name from the correct connection string data source= ....

SQLiteConnection sqlite_conn;
SQLiteCommand sqlite_cmd;

//I have removed the New=True parameter
string connectionString = "Data Source=database.db;Version=3;Compress=True;";
Regex regex = new Regex("data source=([^;]*)",
    RegexOptions.Multiline | RegexOptions.IgnoreCase);
var test = regex.Match(connectionString);
if (test.Groups.Count == 1)
{
    //Your ConnectionString is written as DataSource and not Data Source ;)
    throw new Exception("Wrong ConnectionString");
}

string databaseFile = test.Groups[1].Value;

if (!File.Exists(databaseFile))
{
    //here you can create your new tables
    sqlite_conn = new SQLiteConnection(connectionString);
    sqlite_conn.Open();
    sqlite_cmd = sqlite_conn.CreateCommand();    
    sqlite_cmd.CommandText = "CREATE TABLE Client (id integer primary key, Title  varchar(100),Name  varchar(100),Surname  varchar(100),Dateofbirth DateTime , Propertyname varchar(100),Moveindate DateTime,Relationship varchar(100),Spouse  varchar(100),Gender  varchar(100), spTitle  varchar(100),SpouseName  varchar(100),SpouseSurname  varchar(100),spDateofbirth DateTime ,spRelationship varchar(100),spSpouse  varchar(100),spGender  varchar(100));";
    sqlite_cmd.ExecuteNonQuery();
}
else
{
    sqlite_conn = new SQLiteConnection(connectionString);
    sqlite_conn.Open();
}

You can also get the ConnectionString from the SQLiteConnection You can change the code to follwoing

string connectionString = "Data Source=database.db;Version=3;Compress=True;";
sqlite_conn = new SQLiteConnection(connectionString);
Regex regex = new Regex("data source=([^;]*)",
    RegexOptions.Multiline | RegexOptions.IgnoreCase);
var test = regex.Match(sqlite_conn.ConnectionString);

Upvotes: 0

user8757645
user8757645

Reputation: 1

    privat string _dataSource = @"H:\Ik.db";
    private SQLiteConnection _connection;
    private SQLiteCommand _command;

    private void connectToSQLite()
    {
        using (SQLiteConnection _connection = new SQLiteConnection())
        {
            if (File.Exists(@"H:\Ik.db"))
            {
                _connection.ConnectionString = $"Data Source={_dataSource};Version=3";
                _connection.Open();
                using (SQLiteCommand _command = new SQLiteCommand())
                {
                    _command.Connection = _connection;
                    _command.CommandText = "CREATE TABLE IF NOT EXISTS Kunden ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Lastname VARCHAR(100) NOT NULL, " +
                     "name VARCHAR(100) NOT NULL, Code VARCHAR(100) NOT NULL, City VARCHAR(100) NOT NULL);";
                    try
                    {
                        _command.ExecuteNonQuery();
                        MessageBox.Show($"You Connected to local Data Base under {_dataSource} Sucssefuly");
                    }
                    catch (Exception)
                    {

                        throw;
                    }
                }
            }
            else
            {
                SQLiteConnection.CreateFile(@"H:\Ik.db");

            }
        }
    }

Upvotes: 0

Related Questions