SoFunction
Updated on 2025-03-08

Detailed explanation of C# operation SQLite method example

This article describes the C# operation SQLite method. Share it for your reference. The specific analysis is as follows:

address:

Get it. . .

First import/using:

Copy the codeThe code is as follows:
using ;

Connection and Command:

private SQLiteConnection conn; 
private SQLiteCommand cmd;

Connect to db:

conn = new SQLiteConnection("Data Source=c:\\"); 
 ();

INSERT/UPDATE:

cmd = (); 
 = "INSERT INTO user(email,name) VALUES ('email','name')"; 
(); 
 
 = "UPDATE userSET name = 'Codelicious' WHERE ID = 1"; 
();

SELECT:

 = "SELECT ID, name FROM user"; 
SQLiteDataReader reader = (); 
if () 
{ 
  while (()) 
  { 
   ("ID: " + reader.GetInt16(0)); 
   ("name: " + (1)); 
  } 
}

Template program:

using System; 
using ; 
using ; 
using ; 
namespace SQLiteQueryBrowser 
{ 
   /// <summary> 
   /// Description: This is a general class for general database operations encapsulation.   /// </summary> 
   public class SQLiteDBHelper 
   { 
     private string connectionString = ; 
     /// <summary> 
     /// Constructor     /// </summary> 
     /// <param name="dbPath">SQLite database file path</param>     public SQLiteDBHelper(string dbPath) 
     { 
        = "Data Source=" + dbPath; 
     } 
     /// &lt;summary&gt; 
     /// Create SQLite database file     /// &lt;/summary&gt; 
     /// <param name="dbPath">Path of SQLite database file to be created</param>     public static void CreateDB(string dbPath) 
     { 
       using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath)) 
       { 
         (); 
         using (SQLiteCommand command = new SQLiteCommand(connection)) 
         { 
            = "CREATE TABLE Demo(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE)"; 
           (); 
            = "DROP TABLE Demo"; 
           (); 
         } 
       } 
     } 
     /// &lt;summary&gt; 
     /// Perform the addition, deletion and modification operation on the SQLite database to return the number of affected rows.     /// &lt;/summary&gt; 
     /// <param name="sql">Additional SQL statements to be executed</param>     /// <param name="parameters">The parameters required to execute the add-deletion and modification statements must be in the order they are in the SQL statement</param>     /// &lt;returns&gt;&lt;/returns&gt; 
     public int ExecuteNonQuery(string sql, SQLiteParameter[] parameters) 
     { 
       int affectedRows = 0; 
       using (SQLiteConnection connection = new SQLiteConnection(connectionString)) 
       { 
         (); 
         using (DbTransaction transaction = ()) 
         { 
           using (SQLiteCommand command = new SQLiteCommand(connection)) 
           { 
              = sql; 
             if (parameters != null) 
             { 
               (parameters); 
             } 
             affectedRows = (); 
           } 
           (); 
         } 
       } 
       return affectedRows; 
     } 
     /// &lt;summary&gt; 
     /// Execute a query statement to return an associated SQLiteDataReader instance     /// &lt;/summary&gt; 
     /// <param name="sql">Query statement to be executed</param>     /// <param name="parameters">The parameters required to execute SQL query statements must be in the order they are in the SQL statement</param>     /// &lt;returns&gt;&lt;/returns&gt; 
     public SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] parameters) 
     { 
       SQLiteConnection connection = new SQLiteConnection(connectionString); 
       SQLiteCommand command = new SQLiteCommand(sql, connection); 
       if (parameters != null) 
       { 
         (parameters); 
       } 
       (); 
       return (); 
     } 
     /// &lt;summary&gt; 
     /// Execute a query statement and return a DataTable containing the query results     /// &lt;/summary&gt; 
     /// <param name="sql">Query statement to be executed</param>     /// <param name="parameters">The parameters required to execute SQL query statements must be in the order they are in the SQL statement</param>     /// &lt;returns&gt;&lt;/returns&gt; 
     public DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters) 
     { 
       using (SQLiteConnection connection = new SQLiteConnection(connectionString)) 
       { 
         using (SQLiteCommand command = new SQLiteCommand(sql, connection)) 
         { 
           if (parameters != null) 
           { 
             (parameters); 
           } 
           SQLiteDataAdapter adapter = new SQLiteDataAdapter(command); 
           DataTable data = new DataTable(); 
           (data); 
           return data; 
         } 
       } 
     } 
     /// &lt;summary&gt; 
     /// Execute a query statement to return the first row and first column of the query result     /// &lt;/summary&gt; 
     /// <param name="sql">Query statement to be executed</param>     /// <param name="parameters">The parameters required to execute SQL query statements must be in the order they are in the SQL statement</param>     /// &lt;returns&gt;&lt;/returns&gt; 
     public Object ExecuteScalar(string sql, SQLiteParameter[] parameters) 
     { 
       using (SQLiteConnection connection = new SQLiteConnection(connectionString)) 
       { 
         using (SQLiteCommand command = new SQLiteCommand(sql, connection)) 
         { 
           if (parameters != null) 
           { 
             (parameters); 
           } 
           SQLiteDataAdapter adapter = new SQLiteDataAdapter(command); 
           DataTable data = new DataTable(); 
           (data); 
           return data; 
         } 
       } 
     } 
     /// &lt;summary&gt; 
     /// Query all data type information in the database     /// &lt;/summary&gt; 
     /// &lt;returns&gt;&lt;/returns&gt; 
     public DataTable GetSchema() 
     { 
       using (SQLiteConnection connection = new SQLiteConnection(connectionString)) 
       { 
         (); 
         DataTable data=("TABLES"); 
         (); 
         //foreach (DataColumn column in ) 
         //{ 
         //  (); 
         //} 
         return data; 
       } 
     } 
   } 
}

Complete program example:

using System; 
using ; 
using ; 
using ; 
using ; 
using ; 
using SQLiteQueryBrowser; 
namespace SQLiteDemo 
{ 
   class Program 
   { 
     static void Main(string[] args) 
     { 
       //CreateTable(); 
       //InsertData(); 
       ShowData(); 
       (); 
     } 
     public static void CreateTable() 
     { 
       string dbPath = "D:\\Demo.db3"; 
       //If there is no database change file, create the database file       if (!(dbPath)) 
       { 
         ("D:\\Demo.db3"); 
       } 
       SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3"); 
       string sql = "CREATE TABLE Test3(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,Name char(3),TypeName varchar(50),addDate datetime,UpdateTime Date,Time time,Comments blob)"; 
       (sql, null); 
     } 
     public static void InsertData() 
     { 
       string sql = "INSERT INTO Test3(Name,TypeName,addDate,UpdateTime,Time,Comments)values(@Name,@TypeName,@addDate,@UpdateTime,@Time,@Comments)"; 
       SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3"); 
       for (char c = "A"; c &lt;= "Z"; c++) 
       { 
         for (int i = 0; i &lt; 100; i++) 
         { 
           SQLiteParameter[] parameters = new SQLiteParameter[]{ 
             new SQLiteParameter("@Name",c+()), 
           new SQLiteParameter("@TypeName",()), 
           new SQLiteParameter("@addDate",), 
           new SQLiteParameter("@UpdateTime",), 
           new SQLiteParameter("@Time",()), 
           new SQLiteParameter("@Comments","Just a Test"+i) 
           }; 
           (sql, parameters); 
         } 
       } 
     } 
     public static void ShowData() 
     { 
       //Inquiry of 20 records starting from 50       string sql = "select * from test3 order by id desc limit 50 offset 20"; 
       SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3"); 
       using (SQLiteDataReader reader = (sql, null)) 
       { 
         while (()) 
         { 
           ("ID:{0},TypeName{1}", reader.GetInt64(0), (1)); 
         } 
       } 
     } 
   } 
 }

In actual situations, it will be a bit slow to insert data in large batches using general classes. This is because if the operations in it are not specified, they will be treated as a thing. If a large number of records need to be written at one time, it is recommended to create a thing explicitly. It is better to complete all operations in this transaction. This is much more efficient than creating a thing at each operation.

Finally, using the functions provided by VS2008, you can see the data inside as follows:

It should be noted that the data type regulations are not very strict. Judging from the SQL statements that create the Test3 table, addDate, UpdateTime, and Time in the table are DateTime, Date, and Time respectively, but in fact, we did not follow this regulation when inserting, and the final displayed result also follows the definition of the database field as much as possible.

Summarize

It is indeed a very small and compact database. As an encapsulation of SQLite (SQLite can be accessed using Java on Android and other types of phones), it is still small in size, has high year-on-year performance, low memory consumption, and can run without installing only one dll (if you need two files on a mobile phone), the only disadvantage is that there is no comparison GUI (grapher interface), but it is because of this that it is small in size.

In actual development, there may be some inconvenience without a graphical user interface. We can use VS to view and operate data. I have also made a small thing myself to facilitate the management and maintenance of data. The interface is as follows:

If you want to develop an application with a data volume of less than 100,000 pieces, I suggest you try it, it may be a good choice.

public static void CreateTable()
{
 string dbPath = "D:\\Demo.db3";
 //If there is no database change file, create the database file if (!(dbPath))
 {
  ("D:\\Demo.db3");
 }
 SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3");
 string sql = "CREATE TABLE Test3(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,Name char(3),TypeName varchar(50),addDate datetime,UpdateTime Date,Time time,Comments blob)";
 (sql, null);
}
public static void InsertData()
{
 string sql = "INSERT INTO Test3(Name,TypeName,addDate,UpdateTime,Time,Comments)values(@Name,@TypeName,@addDate,@UpdateTime,@Time,@Comments)";
 SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3");
 for (char c = "A"; c &lt;= "Z"; c++)
 {
  for (int i = 0; i &lt; 100; i++)
  {
   SQLiteParameter[] parameters = new SQLiteParameter[]{ 
           new SQLiteParameter("@Name",c+()), 
         new SQLiteParameter("@TypeName",()), 
         new SQLiteParameter("@addDate",), 
         new SQLiteParameter("@UpdateTime",), 
         new SQLiteParameter("@Time",()), 
         new SQLiteParameter("@Comments","Just a Test"+i) 
         };
   (sql, parameters);
  }
 }
}
public static void ShowData()
{
 //Inquiry of 20 records starting from 50 string sql = "select * from test3 order by id desc limit 50 offset 20";
 SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3");
 using (SQLiteDataReader reader = (sql, null))
 {
  while (())
  {
   ("ID:{0},TypeName{1}", reader.GetInt64(0), (1));
  }
 }
}

I hope this article will be helpful to everyone's C# programming.