Creating a SQLite Database and Table

Question

In C # application code, I would like to create and then interact with one or more SQLite databases.

How to initialize a new SQLite database file and open it for reading and writing?

How to create a database after creating a database after creating a database?

Answer 1

The following link will lead you to a great tutorial that helped me a lot!

Like SQLITE in C #

In this article, I used almost everything to create a SQLite database for my own C # application.

Do not forget to download SQLite.dll and add it as a link to your project. This can be done using NuGet and manually adding a dll.

Once you have added the link, access the DLL from your code using the following line at the top of your class:

using System.Data.SQLite;

You can find the DLL here:

SQLite DLL

You can find the NuGet path here:

Nuget

Next up is the creation 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 the create script in C #, I think that you might want to add rollback transactions, it is safer and this will prevent your database from crashing because the data will be fixed in the end as one big part as atomic operations for the database, not in small pieces, where it may fail on 5 out of 10 queries, for example.

Transaction usage example:

  using (TransactionScope tran = new TransactionScope()) { //Insert create script here. //Indicates that creating the SQLiteDatabase went succesfully, so the database can be committed. tran.Complete(); }