dcsimg

Using SQLite in a C# Application

WEBINAR:
On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


Introduction

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.

Why SQLite in C#?

Due to its lightweight structure, SQLite is heavily used in embedded software with devices such as TV, mobile phones, cameras, home electronic devices, and so forth. Most mobile and small device databases doesn't need a server component; SQLite is recommended for mobile-based applications. Reading and writing operations in SQLite database are extremely fast, almost 35% faster than any traditional databases. SQLite reduces application cost because content can be accessed and updated by using concise SQL queries instead of lengthy procedural queries. To connect SQLite, no additional database drivers, or ODBC configuration are required. Developers just have to download the library and add the data file in their application.

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, as pictured in Figure 1.

.NET Console application
Figure 1: .NET Console application

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. Refer to Figure 2.

Installing the SQLite NuGet Package
Figure 2: Installing the SQLite NuGet Package

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.

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 Createsql = "CREATE TABLE SampleTable
            (Col1 VARCHAR(20), Col2 INT)";
         string Createsql1 = "CREATE TABLE SampleTable1
            (Col1 VARCHAR(20), Col2 INT)";
         sqlite_cmd = conn.CreateCommand();
         sqlite_cmd.CommandText = Createsql;
         sqlite_cmd.ExecuteNonQuery();
         sqlite_cmd.CommandText = Createsql1;
         sqlite_cmd.ExecuteNonQuery();

      }

      static void InsertData(SQLiteConnection conn)
      {
         SQLiteCommand sqlite_cmd;
         sqlite_cmd = conn.CreateCommand();
         sqlite_cmd.CommandText = "INSERT INTO SampleTable
            (Col1, Col2) VALUES ('Test Text ', 1);";
         sqlite_cmd.ExecuteNonQuery();
         sqlite_cmd.CommandText = "INSERT INTO SampleTable
            (Col1, Col2) VALUES ('Test1 Text1 ', 2);";
         sqlite_cmd.ExecuteNonQuery();
         sqlite_cmd.CommandText = "INSERT INTO SampleTable
            (Col1, Col2) VALUES ('Test2 Text2 ', 3);";
         sqlite_cmd.ExecuteNonQuery();


         sqlite_cmd.CommandText = "INSERT INTO SampleTable1
            (Col1, Col2) VALUES ('Test3 Text3 ', 3);";
         sqlite_cmd.ExecuteNonQuery();

      }

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

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

To help you create a connection, I have provided a SQLite connection string. This string contains information about the database connection, such as the filename of the database, version, user id, and password, if required. After creating the connection object, I opened it by calling Open() and called the Close() method after displaying the records to close the database connection.

To add tables in the database, I have written two SQL create table statements. Also, I executed those create table statements by using an SQL command object. To insert data in these tables, I wrote SQL insert statements. Next, I created a SQL command to execute the inset queries.

To query the database for the inserted records, I wrote an SQL select query. However, I executed this command by using a different method, named ExecuteReader(), which returns an SQLiteDataReader object. I used this object to read the results of the query and display it in the console. The Read() method of the reader moves the reader to the next row.

Conclusion

SQLite has few disadvantage, too. It's not suited for a multi-threaded or a multi-process application. But, SQLite is very suitable for memory-constrained systems. That's all for today. Happy Coding!



About the Author

Tapas Pal

I am working in Microsoft Technology for last 15 years and presently working with Cognizant Technology Solutions, India as Senior Architect. I have completed TOGAF 9.1,Microsoft Certification on .NET 1.1 , .NET 2.0, SQL Server 2005 and Sharepoint. Please visit my Blog - http://tapas-pal.blogspot.com/

Related Articles

Comments

  • There are no comments yet. Be the first to comment!

  • You must have javascript enabled in order to post comments.

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • As all sorts of data becomes available for storage, analysis and retrieval - so called 'Big Data' - there are potentially huge benefits, but equally huge challenges...
  • The agile organization needs knowledge to act on, quickly and effectively. Though many organizations are clamouring for "Big Data", not nearly as many know what to do with it...
  • Cloud-based integration solutions can be confusing. Adding to the confusion are the multiple ways IT departments can deliver such integration...

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date