Create SQLite Database and Table

Create SQLite Database and table

The next link will bring you to a great tutorial, that helped me a lot!

How to SQLITE in C#

I nearly used everything in that article to create the SQLite database for my own C# Application.

Don't forget to download the SQLite.dll, and add it as a reference to your project.
This can be done using NuGet and by adding the dll manually.

After you added the reference, refer to the dll from your code using the following line on top of your class:

using System.Data.SQLite;

You can find the dll's here:

SQLite DLL's

You can find the NuGet way here:

NuGet

Up next is the create script.
Creating a database file:

SQLiteConnection.CreateFile("MyDatabase.sqlite");

SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
m_dbConnection.Open();

string sql = "create table highscores (name varchar(20), score int)";

SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();

sql = "insert into highscores (name, score) values ('Me', 9001)";

command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();

m_dbConnection.Close();

After you created a create script in C#, I think you might want to add rollback transactions, it is safer and it will keep your database from failing, because the data will be committed at the end in one big piece as an atomic operation to the database and not in little pieces, where it could fail at 5th of 10 queries for example.

Example on how to use transactions:

 using (TransactionScope tran = new TransactionScope())
 {
     //Insert create script here.

     //Indicates that creating the SQLiteDatabase went succesfully, so the database can be committed.
     tran.Complete();
 }

Creating tables in sqlite database on android

First of all I would recommend using android.util.Log for logging exceptions in Android.

Second - I suspect you have tables with wrong names created. Your error says query can't find "log", but I see you make some concatenation in "CREATE" statement. That may be the reason.

You can check what is actually created for you. By viewing the sqlite base created.

You can try:

1. adb shell
2. cd /data/data/<your.package.name>/databases
3. sqlite3 <yourdbname>
4. .tables

Create sqlite database once on startup

If this is your actual code then there is no way to overwrite the tables because you are using IF NOT EXISTS in the CREATE TABLE statements.

A problematic part in your code is this query:

SELECT name 
FROM sqlite_master 
WHERE type='table' AND name='user' AND name='data'

Obviously you are trying to find out if the 2 tables already exist by executing the above query, but your logic is wrong.

This query never returns a row, because there is no way that the column name would be equal to 'user' AND 'data' in the same row.

You could get results if you used the operator OR instead of AND:

SELECT name 
FROM sqlite_master 
WHERE type='table' AND (name='user' OR name='data')

but, this would return 0 or 1 or 2 rows depending on the existence of either of the tables.

In any case, remove this query because it is not needed at all.

All you need is the 2 CREATE TABLE IF NOT EXISTS... statements and nothing more.

Also, check if by any chance, there is code that deletes the database, so when you restart the app, the database is recreated.

How to create database and add 2 table in SQLite

Use the SQLiteConnection's CreateFile() method.

SQLiteConnection.CreateFile("c:\\mydatabasefile.db3")

More info on the System.Data.SQLite forums

You can then send ad-hoc CREATE TABLE statements to the engine:

dim myTableCreate as String = 
                    "CREATE TABLE MyTable(CustomerID INTEGER PRIMARY KEY ASC, 
                     FirstName VARCHAR(25));"

cmd.CommandText = myTableCreate
cmd.ExecuteNonQuery()

More on SQLite CREATE TABLE.

Programmatically create sqlite db if it doesn't exist?

To execute any kind of data definition command on the database you need an open connection to pass the command. In your code you create the connection AFTER the execution of the query.

Of course, after that creation, you need to open the connection

if (!System.IO.File.Exists(@"C:\Users\abc\Desktop\1\synccc.sqlite"))
{
    Console.WriteLine("Just entered to create Sync DB");
    SQLiteConnection.CreateFile(@"C:\Users\abc\Desktop\1\synccc.sqlite");
    
    using(var sqlite2 = new SQLiteConnection(@"Data Source=C:\Users\abc\Desktop\1\synccc.sqlite"))
    {
        sqlite2.Open();
        string sql = "create table highscores (name varchar(20), score int)";
        SQLiteCommand command = new SQLiteCommand(sql, sqlite2);
        command.ExecuteNonQuery();
    }
}

However, if you use the version 3 of the provider, you don't have to check for the existence of the file. Just opening the connection will create the file if it doesn't exists.

---

Related Topics