SQLite Create Table Tutorial with Columns, Primary Key, Schema
SQLite Create Table Tutorial with Columns, Primary Key, Schema. One of the reasons SQLite is so beloved by the open source community is that it’s lightweight and more accessible than most database solutions. However, it does come with somewhat of a learning curve, especially if you’re unfamiliar with embedded databases.
First of all, it comes as a library that many programming languages, such as Android, are bound to. You may be familiar with SQL database management and integrated development environments like Microsoft’s SQL Management Studio. So, tools like these make it easier to manage your databases. But how do you actually create a database using SQL and then fill it with tables that have columns, a primary key, and schema outside the bindings of a programming language? This is what the following guide will explore.
Shall we continue with SQLite Create Table Tutorial with Columns, Primary Key, Schema.
How To Use SQLite With C#
There are a few ways to use SQLite and they greatly depend on what you want to use it for and with what programming language. This guide will focus on using SQLite with C# in Visual Studios.
Well, C# remains one of the most in-demand programming languages. Because it uses an object oriented paradigm. Many of its methodologies are transferable to other languages such as JavaScript, Java, and Python. Furthermore, you can download the latest Visual Studio Community edition for free. A multi programming language IDE. Nevertheless, to add and use SQLite to your Visual Studio C# project, do the following:
The first thing you must do is open the NuGet Package Manager to install the SQLite library and drivers. Select Tools > NuGet Package Manager > Manage NuGet Packages for Solution… from Visual Studios main menu.
- Select Browse near the top left hand corner of the NuGet Package Manager.
- Ensure the package source is set to All.
- Click on the search field and type data.sqlite into it. This will populate the search results panel with a list of SQLite related packages and libraries.
- Select the package labelled data.sqlite.
- Tick your project checkbox under the versions panel.
- Click on the Install. After that, the installation may ask you if you want to confirm the installation and accept the license. Click on the OK and Accept buttons (respectively).
- Close the NuGet Package Manager once the installation is complete.
Create a SQLite Table with Columns, Primary Key, Schema Using C#
*Note: The following example uses .net core 3.1. If you are using a later version, you will have to adapt the steps accordingly.
Creating a SQLite Database in C#
Now that you have imported the SQLite package for C#, you can create a database and populate it with a table. Do the following in the Program class for your project:
- Import the Data.SQLite namespace:
using System.Data.SQLite - Next, define a static method that will create a connection to the SQLite database. This method will return an object of the SQLiteConnection class:
static SQLiteConnection CreateConnection() - Define a new object of the SQLiteConnection class in your CreateConnection class. This will ultimately define your database. The CreateConnection constructor requires at least one argument. In his case, you’ll use the name of your database as the data source:
SQLiteConnection sqliteConn = new SQLiteConnection(“Data Source= mydatabase.db”); - Next, you must open a connection to the database. Since this can potentially throw an exception, you must place this code between a try-catch statement and handle the exception:
try
{
sqliteConn.Open();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
- Finally, return the SQLite Connection object from the method and add the necessary closing bracket:
return sqliteConn;
}
Creating a Table in SQLite with C#
- Define a new static method with void as its return type and an SQLiteConnection object as its only argument:
static void CreateTable(SQLiteConnection conn) - Use the SQLiteConnection object to create a new SQLiteCommand object:
SQLiteCommand sqliteCmd = conn.CreateCommand(); - You can use the above method to execute commands. In this case, you will execute one that creates a table. First, you must define the table creation command text:
CommandText = “CREATE TABLE IF NOT EXISTS myTable (Col1 INT Primary Key, Col2 VARCHAR(20))”;”
The above command creates a table called ‘myTable’. However, before it creates the said table, it checks if it exists. The table contains two columns. The first column accepts integer types and acts as a primary key. The second column accepts varying character data that has a limit of twenty characters (VARCHAR(20)). - Next, you will use the ExecuteNonQuery method to execute the creation command.
ExecuteNonQuery(); - Add a closing curly bracket to the method. Your method’s code should look identical to the following:
static void CreateTable(SQLiteConnection conn)
{
SQLiteCommand sqliteCmd = conn.CreateCommand();
sqliteCmd.CommandText = "CREATE TABLE IF NOT EXISTS myTable (Col1 INT Primary Key, Col2 VARCHAR(20))";
sqliteCmd.ExecuteNonQuery();
}
- Now both methods must be run from your main method:
static void Main(string[] args)
{
SQLiteConnection conn = CreateConnection();
CreateTable(conn);
}
Once created, you can find the database file in your bin folder i.e.
C:\Users\beart\source\repos\SQLiteDemo\bin\Debug\netcoreapp3.1
So now that you have created a database and a table with columns, a primary key, and a schema, you can get information about it. Besides, it is important to note that SQLite doesn’t use a traditional schema and schemata for its databases and tables. However, it does supply you with a few tools to retrieve and view information about the structure of your databases and tables.
Please follow this article blog further to read more about SQLite Create Table Tutorial with Columns, Primary Key, Schema.
View Your SQLite Table's Schema Information Using C#
- Create a new static method called GetTableInfo. It returns Void and accepts an SQLiteConnection object as its only parameter:
static void GetTableInfo(SQLiteConnection conn) - You will first be required to create a command that retrieves everything from your table:
SQLiteCommand sqlite_cmd = conn.CreateCommand();
CommandText = “SELECT * FROM MyTable”; - Next, you must fetch an SQLiteDataReader from the command:
SQLiteDataReader sqlite_datareader = sqlite_cmd.ExecuteReader(); - You can then use the SQLiteDataReader object to fetch a schema table. Working with SQLite Schema tables is a bit tricky. You have to use a DataTable to represent your schema table:
DataTable dataTable = sqlite_datareader.GetSchemaTable(); - Now, you must iterate through the table, row by row and column by column to fetch and view all the data from the schema table. First, you must define a for loop that uses the rows to loop through the schema table:
for (int i = 0; i < dataTable.Rows.Count; i++)
{ - Create an object to represent the current row in the for You will fetch the row using the schema table:
DataRow row = dataTable.Rows[i]; - Next, define a foreach that will loop through the schema (data) table’s columns:
foreach (DataColumn col in dataTable.Columns)
{ - You must print out each column and value of the schema table:
WriteLine(“{0}: {1}”, col.ColumnName, row[col.Ordinal]);
Prints the name of the column and the value of the row relative to the column - Close the foreach loop and a writeline method to help you separate all the information according to row:
Console.Writeline(“ ”); - Close the top level for loop, the SLiteConnection object, and the current method.
Completed C# Schema Code:
The final method should look like this:
static void GetTableInfo(SQLiteConnection conn)
{
SQLiteCommand sqlite_cmd = conn.CreateCommand();
sqlite_cmd.CommandText = "SELECT * FROM MyTable";
SQLiteDataReader sqlite_datareader = sqlite_cmd.ExecuteReader();
DataTable dataTable = sqlite_datareader.GetSchemaTable();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
DataRow row = dataTable.Rows[i];
foreach (DataColumn col in dataTable.Columns)
{
Console.WriteLine("{0}: {1}",
col.ColumnName, row[col.Ordinal]);
}
Console.WriteLine("");
}
conn.Close();
}
}
The final method should look like this:
Alternatively, you can use the Pragma table_info method to view your table’s structure (essentially the schema). Basically, it retrieves six columns including the column ID, column name, column type, if the column accepts null values, the column’s default value, and if the column is a primary key. If you elect to go with this approach, your method will look like this:
static void GetTableInfo(SQLiteConnection conn)
{
SQLiteCommand sqlite_cmd = conn.CreateCommand(); ;
sqlite_cmd.CommandText = "pragma table_info('myTable');";
SQLiteDataReader sqlite_datareader = sqlite_cmd.ExecuteReader();
while (sqlite_datareader.Read())
{
Console.Write("cid: " + sqlite_datareader.GetInt32(0) + " ");
Console.Write(" name: " + sqlite_datareader.GetString(1) + " ");
Console.Write(" type: " + sqlite_datareader.GetString(2) + " ");
Console.Write(" notnull: " + sqlite_datareader.GetInt32(3) + " ");
Console.Write(" dflt_value: " + sqlite_datareader.GetValue(4) + " ");
Console.Write(" Is A PK: " + sqlite_datareader.GetInt32(5) + "\n");
}
conn.Close();
}
The output for the above code should look like this:
The output is arguably more manageable.
SQLite Create Table Tutorial with Columns, Primary Key, Schema: Syntax Breakdown
Now that you’ve covered how to implement an embedded database in .Net (C#) using SQLite, let’s explore the operational syntax.
Certainly, you can use the following syntax to create a table with columns and a primary key:
CREATE TABLE dbname.table_name(
col1 datatype PRIMARY KEY(column(s)), //Creates a column and assigns it as a primary key
col2 datatype, //Creates a normal column with a data type
col3 datatype,
);
The above syntax example is designed to create three columns. Of course, you can create more.
In action, it would look like this:
CREATE TABLE my_database.my_table (
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
);
Then you can use the following code to retrieve the schema information of the table:
.schema tableName
eg. .schema myTable
Once again, using Pragma table_info is also a good option.
Let’s see these commands in action using DB browser for SQLite.
Using DB Browser for SQLite
You can either create a new database or open one that you’ve previously created:
As noted, when you create a new database using the DB Browser, it will ask you where you want to save it:
Once you give it a name, the user interface will launch the Table Definition dialog. Concurrently, it allows you to create tables for your SQLite database. You can use the fields and constraints tab to define the columns and/or rules for your table:
As you define the fields and constraints, you’ll notice that DB Explorer automatically generates the transact-SQL for you. You will also find this code under the schema column in DB Explorer’s main user interface screen.
If you want more control over your database creation, you can execute manual t-SQL code by clicking on the execute SQL tab. In this case, you can use the Pragma table_info function to retrieve a schema table of your table:
Alternatively, you can use the Command Line Shell For SQLite which is a far more challenging endeavor.
Thank you for reading SQLite Create Table Tutorial with Columns, Primary Key, Schema. We shall conclude.
SQLite Create Table Tutorial with Columns, Primary Key, Schema. Conclusion
The greatest advantage of SQLite is its flexibility. But often, this can be its weakness too. Equally, SQLite offers a plethora of ways you can use its operations. Picking the most optimal approach can become tedious and difficult over time. Nevertheless, knowledge is power. Once you discover most of the ways you can create databases and tables in SQLite, you can focus on discovering which approach suits your style and goals the best. Did you find this guide helpful? Please leave a comment down below if you’d like to make any corrections or would like a deeper explanation of one of the concepts.
Also Take a look at more SQLITE content here in our blog.
