In this tutorial I will show you step by step how to create a SQLite database and how to add a table in the database in C# Console Application.
Before open and download SQLite DLL in your solution for connectting to sqlite database in C#, And you will learn step by step how to do this.
Step 1: Open Visual Studio and create a C# Console Application. File / New / Project -> C# / C# Console Application.
Step 2: Right click on the “Soliton Name” in the Soliton Explorer. Soliton explorer is on the right handside on the Visual Studio. If you can’t see Soliton explorer , click on the View Menu and you will see Solution Explorer in the View Menu.
After right clicking the Solution Name, click Manage Nuget Package… as in the following
Step 3 : Find the SQLite into Nuget packages and click install button.
Step 4: Create Sample class as following images.
Step 4: If database does not exist, CreateDatabaseAndTable method will automatically create the database and tables. If database exists, there are no affect any tables and the database. (C# SQLite Create Table if not exists )
And don’t forget, creating SQLiteConnection, SQLCommand objects before the CreateDatabaseTable
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | SQLiteConnection con; SQLiteCommand cmd; SQLiteDataReader dr; public void CreateDatabaseAndTable() { if (!File.Exists("MyDatabase.sqlite")) { SQLiteConnection.CreateFile("MyDatabase.sqlite"); string sql = @"CREATE TABLE Student( ID INTEGER PRIMARY KEY AUTOINCREMENT , FirstName TEXT NOT NULL, LastName TEXT NOT NULL );"; con = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;"); con.Open(); cmd = new SQLiteCommand(sql, con); cmd.ExecuteNonQuery(); con.Close(); } else { con = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;"); } } |
Step 5: If you want to add some data and select them in the C# Console add this methods in the Sample class.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | public void AddData(string name,string lastname) { cmd = new SQLiteCommand(); con.Open(); cmd.Connection = con; cmd.CommandText = "insert into Student(FirstName,LastName) values ('" + name + "','" + lastname + "')"; cmd.ExecuteNonQuery(); con.Close(); } public void SelectData() { int counter = 0; cmd = new SQLiteCommand("Select *From Student", con); con.Open(); dr = cmd.ExecuteReader(); while (dr.Read()) { counter++; Console.WriteLine(dr[0] + " : " + dr[1]+" "+dr[2]); } con.Close(); } |
Step 6: From the end of the Sample class will be as follows
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | class Sample { SQLiteConnection con; SQLiteCommand cmd; SQLiteDataReader dr; public void CreateDatabaseAndTable() { if (!File.Exists("MyDatabase.sqlite")) { SQLiteConnection.CreateFile("MyDatabase.sqlite"); string sql = @"CREATE TABLE Student( ID INTEGER PRIMARY KEY AUTOINCREMENT , FirstName TEXT NOT NULL, LastName TEXT NOT NULL );"; con = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;"); con.Open(); cmd = new SQLiteCommand(sql, con); cmd.ExecuteNonQuery(); con.Close(); } else { con = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;"); } } public void AddData(string name,string lastname) { cmd = new SQLiteCommand(); con.Open(); cmd.Connection = con; cmd.CommandText = "insert into Student(FirstName,LastName) values ('" + name + "','" + lastname + "')"; cmd.ExecuteNonQuery(); con.Close(); } public void SelectData() { int counter = 0; cmd = new SQLiteCommand("Select *From Student", con); con.Open(); dr = cmd.ExecuteReader(); while (dr.Read()) { counter++; Console.WriteLine(dr[0] + " : " + dr[1]+" "+dr[2]); } con.Close(); } } |
Step 7: In main method you can add some data and query them
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | class Program { static void Main(string[] args) { Sample samp = new Sample(); samp.CreateDatabaseAndTable(); Console.Write("Add Name :"); string name = Console.ReadLine(); Console.Write("Add LastName :"); string lastname = Console.ReadLine(); samp.AddData(name,lastname); Console.WriteLine("*************************"); samp.SelectData(); Console.ReadLine(); } } |
Output:
I do not understand Step 4.
“Step 4: If database does not exist, CreateDatabaseAndTable method will automatically create the database and tables. If database exists, there are no affect any tables and the database. (C# SQLite Create Table if not exists )
And don’t forget, creating SQLiteConnection, SQLCommand objects before the CreateDatabaseTable”
Where do I add this?
Seems like a big jump form Step 3.
Step 6 will help you. In step 6, the Sample.cs file is fully completed.