C# SQLite Database operation learning

Operating environment: Window7 64bit NetFramework4.61，C# 7.0； Editor: Wu Longhari 2017-03-19

---

reference resources:

• SQLite official website
• SQL As Understood By SQLite
• System. Data. SQLite
• Novice Tutorial
• SQL Tutorial

Section:

1. Download and install
2. data type
3. Create Database
4. Delete Database
5. Create Table
6. Delete Table
7. Query Table Structure
8. Change table name
9. Add Column (Field)
10. Read the SQL statement creating the table
11. Change Column Name
12. Delete Column
13. insert data
14. Replace Data
15. Update Data
16. Delete Data
17. Query Data
18. Get the number of rows of query data (how many records)
19. Transaction
20. Organize database

---

Body:

1、 Download and install

During the learning of C # programming at this time, I wanted to write a simple purchase, sales and storage program. I thought of using a database, which requires a simple and portable desktop database. I want to write it myself. My skills are too weak, and it can be used as a training project in the future. Microsoft doesn't know where Foxpro used to be. After searching the Internet, it found that only Access and Sqlite were available. After many comparisons, it decided to learn to use Sqlite.

stay System. Data. SQLite official website Setups for 64 bit Windows (. NET Framework 4.6) in download of sqlite-netFx46-setup-x64-2015-1.0.104.0.exe (17.99 MiB) Download and run the installation.
It is easier to enter: install package system.data.sqlite.x64 in NuGet of Visual Studio 2017.

In the visualization tool of sqlite database, SqliteExpert Good, download SQLite Expert Personal 4.x 。

We have all the tools. Since we know that the system data sqlite above is encapsulated in C #, let's open Visual Studio 2017, open a new project Project → Add Reference → Browse Go to the installation directory of Sqlite and select System Data. SQLite.dll, a 305k link library. After referencing Solution Explorer See the System referenced in Data. In the reference property of SQlite Copy to local Is it true? If not, make it true. Add using statement at the beginning of the project:

using System. Data. SQLite;

Many online tutorials are ok here, but in my actual operation, I found that SQLite. Interop.dll It is also copied to the running directory of the current program (it cannot be referenced, but can only be copied directly). It is unknown whether it is the requirement of the new version.

(ps: There is a detailed help document SQLite. NET. chm in the installation directory of sqlite)

2、 Data type

There are five types of data stored:

Storage class	describe
NULL	A NULL value
INTERGER	Signed integer, automatically stored as 1,2,3,4,5,8 bytes according to the size of the value
REAL	Floating point number, stored as IEEE 8byte floating point number
TEXT	Text string, the default encoding is utf-8
BLOG	Blob data, variable length

Attention, The storage width of SQLite is automatically adjusted according to the input This is different from the foxpro I used before. For example, even if you set a field varchar (4) 4-byte wide string in the create data table, but you enter a string of five widths of "hello", it will not be truncated into "hello", but will be completely stored as "hello". The same is true for numeric types.

More interesting is that it has a Type Affinity function, such as:

CREATE TABLE t1(a INT, b VARCHAR(10));
INSERT INTO t1(a,b) VALUES('123',456);

It will use the Type Affinity function to automatically and correctly convert "123" into numbers and 456 into "456" strings. Please refer to the help file in the installation directory or SQLite Affinity Type 。

3、 Create Database

SQLite is a file type database. It is easy to create. You can directly specify a database file name. The suffix does not have to be ". sqlite", but ". db". Running SQLiteConnection. open will create an empty database file with the specified name. Because it is a file type, we can also use System directly IO.File. Create() to create an empty file.

using System. Data. SQLite;
//---Create Database
static void CreateDB()
{
     string path = @"d:\test\123.sqlite";
    SQLiteConnection cn = new SQLiteConnection("data source=" + path);
    cn. Open();
    cn. Close();
}

4、 Delete Database

The sqlite command does not seem to provide the command to delete the entire database, but because it is a file, we directly use System IO.File. Delete (string path) method to delete a file.

//---Delete Database
static void DeleteDB()
{
     string path = @"d:\test\123.sqlite";
     if (System.IO.File.Exists(path))
    {
        System. IO.File. Delete(path);
    }
}

5、 Create Table

The SQL command is needed now. The sequence of creating a table is as follows (It can also be created with the visualization tool SQLiteExpert) ：
1. Establish database connection;
2. Open the database (if there is no database, Open will also create a new database);
3. Declare a SQLiteCommand class, which is mainly used to place and run SQL commands;
4. Connect SQLiteCommand's Connection with SQLiteConnection（ Remember, always forget ^ _ ^! ）；
5. Enter the SQL statement CREATE TABLE statement into the CommandText of the SQLiteCommand. Please refer to the SQLite in the installation directory for details NET.chm or SQLite Create Table ；
6. Call SQLiteCommand The ExcuteNonQuery() method runs.

//---Add Table
static void CreateTable()
{
     string path = @"d:\test\123.sqlite";
    SQLiteConnection cn = new SQLiteConnection("data source="+path);
     if (cn.State!= System.Data.ConnectionState.Open)
    {
        cn. Open();
        SQLiteCommand cmd = new SQLiteCommand();
        cmd. Connection = cn;
        cmd. CommandText = "CREATE TABLE t1(id varchar(4),score int )";
         //cmd. CommandText = "CREATE TABLE IF NOT EXISTS t1(id varchar(4),score int)";
        cmd. ExecuteNonQuery();
    }
    cn. Close();
}

Note the commented CREATE TABEL IF NOT EXISTS sentence above. Generally, it is better to use this sentence. If there is a table with the same name, there will be an error without this sentence. In fact, SQL statements do not need to be all capitalized. All capitalized SQL statements are conventionally used (reminds me of COBOL I learned when I was reading), and all lowercase statements do not make mistakes.

6、 Delete Table

Just like the steps of creating tables, the SQL statement has been changed.

//---Delete Table
static void DeleteTable()
{
     string path = @"d:\test\123.sqlite";
    SQLiteConnection cn = new SQLiteConnection("data source=" + path);
     if (cn.State != System.Data.ConnectionState.Open)
    {
        cn. Open();
        SQLiteCommand cmd = new SQLiteCommand();
        cmd. Connection = cn;
        cmd. CommandText = "DROP TABLE IF EXISTS t1" ;
        cmd. ExecuteNonQuery();
    }
    cn. Close();
}

7、 Query Table Structure

SQLite special PRAGMA command is required. See PRAGMA Statements
PRAGMA table_info(tablename) The tablename is enclosed with or without single quotation marks' '.
The order of data read by SQliteDataReader represents:

subscript	name	describe
zero	cid	S/N
one	name	name
two	type	data type
three	notnull	Can the value be null, 0 can't, 1 can
four	dflt_value	Default
five	pk	Primary key, 0 No, 1 Yes

string path = @"d:\test\123.sqlite";
SQLiteConnection cn = new SQLiteConnection("data source=" + path);
cn.Open();
SQLiteCommand cmd = cn.CreateCommand();

cmd. CommandText= "PRAGMA table_info('t1')";

//Write method 1: use DataAdapter and DataTable classes, remember to use System Data
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
DataTable table = new DataTable();
adapter. Fill(table);
foreach (DataRow r in table. Rows)
{
    Console. WriteLine($"{r["cid"]},{r["name"]},{r["type"]},{r["notnull"]},{r["dflt_value"]},{r["pk"]} ");
}
Console. WriteLine();

//Write method 2: use DataReader, which is more efficient
SQLiteDataReader reader = cmd. ExecuteReader();
while (reader.Read())
{
     for ( int i = 0; i < reader. FieldCount; i++)
    {
        Console. Write($"{reader[i]},");
    }
    Console. WriteLine();
}
reader. Close();

If there is more than one table, the structure of all tables to be traversed is as follows. The special table sqlite_master in SQLite is used. Its structure is as follows:
reference resources:
2.6. Storage Of The SQL Database Schema
CREATE TABLE sqlite_master(
type text,
name text,
tbl_name text,
rootpage integer,
sql text
);
When type=table, the name and tbl_name are the same. For example, when type=index, view, etc., tbl_name is the table name.

//---Traversal query table structure
static void QueryAllTableInfo()
{
     string path = @"d:\test\123.sqlite";
    SQLiteConnection cn = new SQLiteConnection("data source=" + path);
     if (cn.State != System.Data.ConnectionState.Open)
    {
        cn. Open();
        SQLiteCommand cmd = new SQLiteCommand();
        cmd. Connection = cn;
         cmd. CommandText = "SELECT name FROM sqlite_master WHERE TYPE='table' " ;
        SQLiteDataReader sr = cmd. ExecuteReader();
        List< string > tables = new List< string >();
         while (sr.Read())
        {
            tables. Add(sr.GetString(0));
        }
         //The datareader must be closed first, otherwise the commandText cannot be assigned
        sr. Close();
         foreach ( var a in tables)
        {
             cmd. CommandText = $"PRAGMA TABLE_INFO({a})" ;
            sr = cmd. ExecuteReader();
             while (sr.Read())
            {
                Console. WriteLine($"{sr[0]} {sr[1]} {sr[2]} {sr[3]}");
            }
            sr. Close();
        }
    }
    cn. Close();
}

8、 Change table name

Change the t1 table name to t3 with the SQL statement ALTER TABLE:

cmd. CommandText = "ALTER TABLE t1 RENAME TO t3" ;
cmd. ExecuteNonQuery();

Note that the table name is case insensitive and does not need to be enclosed in single quotation marks.

9、 Add Column (Field)

Or use the SQL command ALTER TABLE. In the following example, add a new column named age and with data type of int to table t1:

cmd. CommandText = "ALTER TABLE t1 ADD COLUMN age int" ;
cmd. ExecuteNonQuery();

10、 Read the SQL statement creating the table

Read the SQL statement when creating the table. You can view it in the DDL of SqliteExpert. Read this to prepare for adding delete columns below.

cmd. CommandText = "SELECT sql FROM sqlite_master WHERE TYPE='table'" ;
SQLiteDataReader sr = cmd. ExecuteReader();
while (sr.Read())
{
    Console. WriteLine(sr[0].ToString());
}
sr.Close();

11、 Change Column Name

SQLite does not provide commands to directly change column names or delete columns. There are two ways,
The first is:
1. Rename the target table;
2. Create a new table with a new column name;
3. Copy the old table data to the new table (remember Connection. BeginTransaction()).

The second is to change the schema in sqlite_master, which is easy to damage the database.
The basis is that every time SQLite connects, the column information is dynamically created based on the CREATE TABLE statement when each table is created in the schema. As long as the data type and location of the column remain unchanged, the column information can be changed by changing the CREATE TABLE statement. Specific reference How do I rename a column in a SQLite database table? 。 Let's write down the two methods below.

Mode 1:

//---Change Column Name 1
//General idea: rename the old table, create a new table with a new column name, and copy data
//Params string []: 0 database name, 1 table name, 2 old column name, 3 new column name
static void RenameColumn1(params string [] str)
{
    SQLiteConnection cn = new SQLiteConnection("data source=" + str[0]);
    cn. Open();
    SQLiteCommand cmd = new SQLiteCommand();
    cmd. Connection = cn;
    
     //SQL statement for creating a table that obtains the str [1] table name
    cmd. CommandText = "SELECT name,sql FROM sqlite_master WHERE TYPE='table' ORDER BY name";
    SQLiteDataReader sr = cmd. ExecuteReader();

     string _sql = "";
     while (sr.Read())
    {
         if ( string .Compare(sr.GetString(0), str[1], true ) == 0)
        {
            _sql = sr.GetString(1);
             break ;
        }
    }
    sr. Close();

     //Change the old table name to be with _old
     string _old = str[1] + "_old";
    cmd. CommandText = $"ALTER TABLE {str[1]} RENAME TO {_old}";
    cmd. ExecuteNonQuery();

     //Create a new table. Assume that the old column name entered is the same as the case of the column name in the table. If it is not written, it can be fault-tolerant
    _sql = _sql. Replace(str[2],str[3]);
    cmd. CommandText = _sql;
    cmd. ExecuteNonQuery();

     //Copy data
     using (SQLiteTransaction tr = cn.BeginTransaction())
    {
        cmd. CommandText = $"INSERT INTO {str[1]} SELECT * FROM {_old}";
        cmd. ExecuteNonQuery();
        cmd. CommandText = $"DROP TABLE {_old}";
        cmd. ExecuteNonQuery();
        tr. Commit();
    }
    cn. Close();
}

Mode 2:

//---Change column name 2 and overwrite the sql statement when creating tables in the schema
//Principle: Every time sqlite is opened, column information is dynamically created based on the sql statement used to create the table
static void RenameColumn2(params string [] str)
{
    SQLiteConnection cn = new SQLiteConnection("data source=" + str[0]);
    cn. Open();
    SQLiteCommand cmd = new SQLiteCommand();
    cmd. Connection = cn;

     //SQL statement for creating a table that obtains the str [1] table name
    cmd. CommandText = $"SELECT sql FROM sqlite_master WHERE TYPE='table' AND name='{str[1]}'";
    SQLiteDataReader sr = cmd. ExecuteReader();
    sr. Read();
     string _sql = sr.GetString(0);
    sr. Close();
     //Pay attention to single quotation marks'
    _sql = $"UPDATE sqlite_master SET sql='{_sql.Replace(str[2],str[3])}' WHERE name= '{str[1]}' " ;

     //Set writable_schema to true and prepare to overwrite the schema
    cmd. CommandText = "pragma writable_schema=1";
    cmd. ExecuteNonQuery();
    cmd. CommandText = _sql;
    cmd. ExecuteNonQuery();
     //Set writable_schema to false.
    cmd. CommandText = "pragma writable_schema=0";
    cmd. ExecuteNonQuery();

    cn. Close();
}

12、 Delete Column

SQLite also does not provide the command to delete columns. As above, there are two ways.
First, rename the target table, create a new table without columns (fields) to be deleted, and then copy the data of the old table to the new table.
Second, directly modify the SQL statements for creating tables in the schema.
The most important thing is to save all the information of the columns in the table, such as indexes and default values. The following example uses the second method.

//---Delete column 2, string [], 0 database path, 1 table name, 2 column name to delete
static void DeleteColumn2(params string [] str)
{
    SQLiteConnection cn = new SQLiteConnection("data source=" + str[0]);
    cn. Open();
    SQLiteCommand cmd = new SQLiteCommand();
    cmd. Connection = cn;
     //SQL statement for creating a table that obtains the str [1] table name
    cmd. CommandText = $"SELECT sql FROM sqlite_master WHERE TYPE='table' AND name='{str[1]}'";
    SQLiteDataReader sr = cmd. ExecuteReader();
    sr. Read();
     string _sql = sr.GetString(0);
    sr. Close();

     //Get the definition of the column
     //The new feature of C # 7.0, the syntax sugar of Tuple<>, requires NuGet install package system.valuetuple
    List<( string name, string define)> list = GetColumnDefine(_sql);
     //Get the sequence number of the column to be deleted
     int _index = list. IndexOf(list.Where(x => x.name == str[2]). First());
     //Create a new sql statement
    StringBuilder sb = new StringBuilder();
    sb. Append($"CREATE TABLE {str[1]}(");
     for ( int i = 0; i < list. Count; i++)
    {
         if (i != _index)
        {
            sb. Append($"{list[i].define},");
        }
    }
    sb. Remove(sb.Length - 1, 1);
    sb. Append(")");
     //Overwrite schema
    _sql = $"UPDATE sqlite_master SET sql='{sb.ToString()}' WHERE name='{str[1]}'";
     //Set writable_schema to true and prepare to overwrite the schema
    cmd. CommandText = "pragma writable_schema=1";
    cmd. ExecuteNonQuery();
    cmd. CommandText = _sql;
    cmd. ExecuteNonQuery();
     //Set writable_schema to false.
    cmd. CommandText = "pragma writable_schema=0";
    cmd. ExecuteNonQuery();

    cn. Close();
}
//---Get the definition of the column
static List<( string , string )> GetColumnDefine( string SqlStr)
{
     int n = 0;
     int _start = 0;
     string _columnStr = "";
     for ( int i = 0; i < SqlStr. Length; i++)
    {
         if (SqlStr[i] == '(')
        {
             if (n++ == 0) { _start = i; }
        }
        else
        {
             if (SqlStr[i] == ')')
            {
                 if (--n == 0)
                {
                    _columnStr = SqlStr. Substring(_start + 1, i - _start - 1);
                     break ;
                }
            }

        }
    }
     string [] ss = _columnStr. Split( new char [] { ',' }, StringSplitOptions. RemoveEmptyEntries);
     //The new feature of C # 7.0, the syntax sugar of Tuple<>, requires NuGet install package system.valuetuple
    List<( string name, string define)> reslut = new List<( string name, string define)>();
     foreach ( var a in ss)
    {
         string s = a.Trim();
        n = 0;
         for ( int i = 0; i < s.Length; i++)
        {
             if (s[i] == ' ')
            {
                reslut. Add((s.Substring(0, i), s));
                 break ;
            }
        }
    }
     return reslut;
}

13、 Insert Data

Insert data mainly by using the SQL statement INSERT INTO

Example 1 (simple insertion):

cmd. CommandText = "INSERT INTO t1 VALUES('99999',11)";
cmd. ExecuteNonQuery();

Example 2 (variable insertion, to reference System. Data):

using System. Data;

string s = "123456";
int n = 10;
cmd. CommandText = "INSERT INTO t1(id,age) VALUES(@id,@age)";
cmd. Parameters. Add("id", DbType.String). Value = s;
cmd. Parameters. Add("age", DbType.Int32). Value = n;
cmd. ExecuteNonQuery();

14、 Replace Data

The SQL command INSERT INTO.
In the following example, the id in table t1 is the primary key, and the same primary key value is UPDATE, otherwise INSERT

string s = "123456";
int n = 30;
cmd. CommandText = "REPLACE INTO t1(id,age) VALUES(@id,@age)";
cmd. Parameters. Add("id", DbType.String). Value = s;
cmd. Parameters. Add("age", DbType.Int32). Value = n;
cmd. ExecuteNonQuery();

15、 Update Data

SQL command UPDATE tablename SET column1=value, column2=value WHERE condition

string s = "333444";
int n = 30;
cmd. CommandText = "UPDATE t1 SET id=@id,age=@age WHERE id='0123456789'";
cmd. Parameters. Add("id", DbType.String). Value = s;
cmd. Parameters. Add("age", DbType.Int32). Value = n;
cmd. ExecuteNonQuery();

16、 Delete Data

SQL command: DELETE FROM tablename WHERE condition

cmd. CommandText = "DELETE FROM t1 WHERE id='99999'";
cmd. ExecuteNonQuery();

17、 Query Data

SQL command: SELECT statement. Please refer to SQL Tutorial 。

//Query the first record. It is not safe. Rowid is not continuous, but it is related to the insertion at that time
cmd. CommandText = "SELECT * FROM t1 WHERE rowid=1";
SQLiteDataReader sr = cmd. ExecuteReader();
while (sr.Read())
{
    Console. WriteLine($"{sr.GetString(0)} {sr.GetInt32(1).ToString()}");
}
sr.Close();
//Run the following to know that rowid does not represent the number of rows
cmd. CommandText = "SELECT rowid FROM t1 ";
sr = cmd. ExecuteReader();
while (sr.Read())
{
    Console. WriteLine($"{sr.GetString(0)} {sr.GetInt32(1).ToString()}");
}
sr.Close();

18、 Get the number of rows of query data (how many records)

From the above example, we know that rowid is not the correct number of rows (number of records), but the related number of B-Tree when INSERT.
To know the number of rows (records) in the table, the following should be done:

cmd. CommandText = "SELECT count(*) FROM t1";
sr = cmd. ExecuteReader();
sr.Read();
Console. WriteLine(sr.GetInt32(0). ToString());
sr.Close();

19、 Transaction

A transaction is a set of execution units that operate on a database in a logical order. The advantage of using transactions is that mature databases optimize intensive disk IO operations, and can also perform rollback operations. In fact, it was used in the above example of changing the column name.

//---Transaction
static void TransActionOperate(SQLiteConnection cn,SQLiteCommand cmd)
{
     using (SQLiteTransaction tr = cn.BeginTransaction())
    {
         string s = "";
         int n = 0;
        cmd. CommandText = "INSERT INTO t2(id,score) VALUES(@id,@score)";
        cmd. Parameters. Add("id", DbType.String);
        cmd. Parameters. Add("score", DbType.Int32);
         for ( int i = 0; i < 10; i++)
        {
            s = i.ToString();
            n = i;
            cmd. Parameters[0]. Value = s;
            cmd. Parameters[1]. Value = n;
            cmd. ExecuteNonQuery();
        }
        tr. Commit();
    }
}

20、 Organize database

SQLite comes with the command VACUUM. It is used to reorganize the entire database for compact use, such as deleting the deleted ones completely.

cmd. CommandText = "VACUUM";
cmd. ExecuteNonQuery();

---

At this point, the SQLite database can basically be operated. As for those who need to install ORM using linq operations, I thought about it for a while, and I will learn about it next time. For my small project, it is simple to carry two DLLs that add up to less than 1.5M.
SQLite is also a database. It is mainly the call of various SQL statements. Focusing on learning SQL statements is my goal in the next period of time.
When I saw the SQLiteHelper on the street, I thought for a moment. At my level, I would not teach others how to use it. Even though I would write it secretly, it would be convenient to call it.