Using SQLite in a C# Application

ALL ABOUT COMPUTER SCIENCE AND TECHNOLOGY

BUILD THE MASS IN A FEW MONTHS THAT OTHERS BUILD IN YEARS!

Publish perfectly-optimized content in 1-click




Using SQLite in a C# Application




SQLite is a small, fast, and embeddable open source file system-based SQL database. It doesn’t have a separate server component like traditional databases. Rather, it reads and writes data directly in disk files. A SQLite database is integrated with the application that accesses the database. The SQLite database file format is cross-platform and can be copied between 32-bit and 64-bit file systems. Due to the serverless architecture, developers don’t need to install SQLite before using it. All SQLite transactions are fully ACID compliant; that means all queries and changes are Atomic, Consistent, Isolated, and Durable. The SQLite source code is public and is free for use for any purpose, commercial or private.


Getting Started with SQLite from a .NET Project


Let’s create a project in Visual Studio to demonstrate SQLite’s capabilities. Open Visual Studio, select new project, and, in Visual C#, select “Console Application” and provide the name as SQLiteDemo. Click OK.
To connect SQLite with C#, we need drivers.
Install all required SQLite resources from the NuGet package

To install the driver, right-click the solution and go to “Manage NuGet Packages.” In the search bar, type “SQLite” and install the package that appears















Next, copy and paste the following code in your Program.cs file. This code will create a SQLite connection, add tables, Insert rows, and, finally, read data from the tables and display in console. Make sure to copy the code exactly as written, otherwise it may not work.

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace SQLiteDemo
{
   class Program
   {


      static void Main(string[] args)
      {
         SQLiteConnection sqlite_conn;
         sqlite_conn = CreateConnection();
         CreateTable(sqlite_conn);
         InsertData(sqlite_conn);
         ReadData(sqlite_conn);
      }


      static SQLiteConnection CreateConnection()
      {


         SQLiteConnection sqlite_conn;
         // Create a new database connection:
         sqlite_conn = new SQLiteConnection("Data Source=
            database.db;Version=3;New=True;Compress=True;");
         // Open the connection:
         try
         {
            sqlite_conn.Open();
         }
         catch (Exception ex)
         {


         }
         return sqlite_conn;
      }


      static void CreateTable(SQLiteConnection conn)
      {


         SQLiteCommand sqlite_cmd;
         string strSqlCreateMyTable = "CREATE TABLE MyTable
            (Column_one VARCHAR(30), Column_two INT)";
         sqlite_cmd = conn.CreateCommand();
         sqlite_cmd.CommandText = strSqlCreateMyTable;
         sqlite_cmd.ExecuteNonQuery();


      }


      static void InsertData(SQLiteConnection conn)
      {
         SQLiteCommand sqlite_cmd;
         sqlite_cmd = conn.CreateCommand();
         sqlite_cmd.CommandText = "INSERT INTO MyTable
            (Column_one, Column_two) VALUES ('Some Text 1 ', 1);";
         sqlite_cmd.ExecuteNonQuery();
         sqlite_cmd.CommandText = "INSERT INTO MyTable
            (Column_one, Column_two) VALUES ('Some Text 2 ', 2);";
         sqlite_cmd.ExecuteNonQuery();
         sqlite_cmd.CommandText = "INSERT INTO MyTable
            (Column_one, Column_two) VALUES ('Some Text 2 ', 3);";
         sqlite_cmd.ExecuteNonQuery();


      }


      static void ReadData(SQLiteConnection conn)
      {
         SQLiteDataReader sqlite_datareader;
         SQLiteCommand sqlite_cmd;
         sqlite_cmd = conn.CreateCommand();
         sqlite_cmd.CommandText = "SELECT * FROM MyTable";


         sqlite_datareader = sqlite_cmd.ExecuteReader();
         while (sqlite_datareader.Read())
         {
            string myreader = sqlite_datareader.GetString(0);
            Console.WriteLine(myreader);
         }
         conn.Close();
      }
   }
}

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
</br>
namespace SQLiteDemo
{
   class Program
   {
</br>
      static void Main(string[] args)
      {
         SQLiteConnection sqlite_conn;
         sqlite_conn = CreateConnection();
         CreateTable(sqlite_conn);
         InsertData(sqlite_conn);
         ReadData(sqlite_conn);
      }
</br>
      static SQLiteConnection CreateConnection()
      {
</br>
         SQLiteConnection sqlite_conn;
         // Create a new database connection:
         sqlite_conn = new SQLiteConnection("Data Source=
            database.db;Version=3;New=True;Compress=True;");
         // Open the connection:
         try
         {
            sqlite_conn.Open();
         }
         catch (Exception ex)
         {
</br>
         }
         return sqlite_conn;
      }
</br>
      static void CreateTable(SQLiteConnection conn)
      {
</br>
         SQLiteCommand sqlite_cmd;
         string strSqlCreateMyTable = "CREATE TABLE MyTable
            (Column_one VARCHAR(30), Column_two INT)";
         sqlite_cmd = conn.CreateCommand();
         sqlite_cmd.CommandText = strSqlCreateMyTable;
         sqlite_cmd.ExecuteNonQuery();
</br>
      }
</br>
      static void InsertData(SQLiteConnection conn)
      {
         SQLiteCommand sqlite_cmd;
         sqlite_cmd = conn.CreateCommand();
         sqlite_cmd.CommandText = "INSERT INTO MyTable
            (Column_one, Column_two) VALUES ('Some Text 1 ', 1);";
         sqlite_cmd.ExecuteNonQuery();
         sqlite_cmd.CommandText = "INSERT INTO MyTable
            (Column_one, Column_two) VALUES ('Some Text 2 ', 2);";
         sqlite_cmd.ExecuteNonQuery();
         sqlite_cmd.CommandText = "INSERT INTO MyTable
            (Column_one, Column_two) VALUES ('Some Text 2 ', 3);";
         sqlite_cmd.ExecuteNonQuery();
</br>
      }
</br>
      static void ReadData(SQLiteConnection conn)
      {
         SQLiteDataReader sqlite_datareader;
         SQLiteCommand sqlite_cmd;
         sqlite_cmd = conn.CreateCommand();
         sqlite_cmd.CommandText = "SELECT * FROM MyTable";
</br>
         sqlite_datareader = sqlite_cmd.ExecuteReader();
         while (sqlite_datareader.Read())
         {
            string myreader = sqlite_datareader.GetString(0);
            Console.WriteLine(myreader);
         }
         conn.Close();
      }
   }
}