reference resources
SQLite official website
SQL As Understood By SQLite
System.Data.SQLite
Rookie tutorial
chapter
1, Download and install
During this period of time, when learning C# programming, I wanted to write a simple purchase, sales and inventory program, so I thought of using the database. I need a simple and portable desktop database. I want to write one myself. My skills are too shallow and can be used as a project for future hands-on learning. The original Foxpro has been left somewhere by Microsoft. I searched the Internet and found that only Access and Sqlite are available. After reading a lot of comparisons, I decided to learn to use Sqlite.
In system Data. Setups for 64 bit windows (. Net framework 4.6) sqlite-netfx46-setup-x64-2015-1.0.104.0 Exe (17.99 MIB) download and run the installation.
More simply, in NuGet of Visual Studio 2017, enter: install package system data. sqlite. x64.
SqliteExpert is a good visual tool for sqlite database. Download sqlite expert personal 4 x .
The tools are ready. Knowing that the above System data Sqlite is C# encapsulated, let's open Visual Studio 2017 and start a new project. In the menu "project" → "Add Reference" → "Browse", go to the Sqlite installation directory and select system Data. Sqlite. DLL, only 305k link library. After referencing, look at the referenced system. In the "Solution Explorer" in the upper right corner of VS Data. Whether the "copy to local" in the reference attribute of Sqlite is true or not. If not, it will be true. Add a using statement at the beginning of the project:
using System.Data.SQLite;
Many online tutorials are ok, but in my actual operation, I found that SQLite Interop. DLL is also copied to the current program running directory (can not be referenced, can only be copied directly). I don't know whether it is the requirement of the new version.
(ps: there is a detailed help document sqlite. Net. CHM in the sqlite installation directory)
2, Data type
There are five types of data stored:
Note: the storage width of SQLite is automatically adjusted according to the input, which is different from the foxpro I used previously. For example, even if you set a string with the width of varchar (4) 4 bytes in the create data table, when you enter a string with the width of "hello", it will not be intercepted as "hello", but completely stored as "hello". The same is true for numeric types.
What's more interesting is that it has a Type Affinity function, such as the following:
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" string. For this Type Affinity, please refer to the help file in the installation directory or SQLite affinity type. 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", and the suffix can be named ". db". Run sqliteconnection Open creates an empty database file with the specified name. Because it is a file type, we can also directly use system 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 type, we directly use system IO. File. Delete (string path) method to delete the 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
Start using SQL commands. The order of creating a table is as follows (you can also create it with the visualizer SQLiteExpert):
1. Establish database connection;
2. Open the database (if there is no database, open will 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, often forget!);
5. Enter the SQL statement CREATE TABLE statement into the CommandText of SQLiteCommand. For details, refer to SQLite NET. CHM or SQLite create tables;
6. Call sqlitecommand The excetenonquery () 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 table if not exists in the above sentence. Generally, this sentence is better. 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 conventional (reminds me of COBOL I learned when I was reading), and all lowercase statements will not make mistakes.
6, Delete table
It is the same as the steps of creating a table, except that SQL The sentence 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
The SQLite special PRAGMA command is required. See PRAGMA Statements
PRAGMA table_info(tablename) and tablename are the same with or without single quotation marks' '.
The data sequence read by SQliteDataReader represents:
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')";
//Writing 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();
//Writing 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 traversing all tables is as follows. The special table SQLite in SQLite is used_ Master, its structure is as follows: 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, name and tbl_name is the same. When other types, such as type =index and view, 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));
}
//datareader must be closed first, otherwise 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
//Use the SQL statement ALTER TABLE to change the t1 table name to t3: cmd.CommandText = "ALTER TABLE t1 RENAME TO t3"; cmd.ExecuteNonQuery(); //Note that table names are case insensitive and do not need to be enclosed in single quotes.
9, Add column (field)
//Or use the SQL command ALTER TABLE to add a new column named age and data type int to the t1 table in the following example: cmd.CommandText = "ALTER TABLE t1 ADD COLUMN age int"; cmd.ExecuteNonQuery();
10, Read the SQL statement that created the table
//Read the SQL statement when creating the table. You can view it in the DDL in SqliteExpert. Reading this is to prepare for adding and deleting 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 to connect. Begintransaction()).
The second is to change SQLite_ The schema in the master can easily damage the database.
The reason is that each time SQLite connects, the column information is dynamically established according to the CREATE TABLE statement when each table in the schema is created. 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. For details, refer to How do I rename a column in a SQLite database table?. Let's take a look at both methods below.
Mode 1:
//---Change column name 1
//General idea: rename the old table, create a new table with new column names, 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 table to get 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 old table name to band_ old
string _old = str[1] + "_old";
cmd.CommandText = $"ALTER TABLE {str[1]} RENAME TO {_old}";
cmd.ExecuteNonQuery();
//To create a new table, it is assumed that the old column name entered is completely consistent with 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 the column name 2 and rewrite the sql statement when creating a table in the schema
//Principle: every time sqlite is opened, the column information is dynamically established according to the sql statement when creating 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 table to get 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();
//Note single quote '
_sql =$"UPDATE sqlite_master SET sql='{_sql.Replace(str[2],str[3])}' WHERE name= '{str[1]}' ";
//Set writable_schema is true, ready to overwrite schema
cmd.CommandText = "pragma writable_schema=1";
cmd.ExecuteNonQuery();
cmd.CommandText = _sql;
cmd.ExecuteNonQuery();
//Set writable_schema is false.
cmd.CommandText = "pragma writable_schema=0";
cmd.ExecuteNonQuery();
cn.Close();
}
12, Delete column
SQLite There is also no command to delete columns. Like the 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, direct modification schema Table under construction SQL sentence.
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 be deleted
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 table to get 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();
//Gets the definition of the column
//The new feature of C#7.0, tuple < > syntax sugar, requires nuget install package system valuetuple
List<(string name, string define)> list = GetColumnDefine(_sql);
//Gets the sequence number of the column to delete
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 is true, ready to overwrite schema
cmd.CommandText = "pragma writable_schema=1";
cmd.ExecuteNonQuery();
cmd.CommandText = _sql;
cmd.ExecuteNonQuery();
//Set writable_schema is false.
cmd.CommandText = "pragma writable_schema=0";
cmd.ExecuteNonQuery();
cn.Close();
}
//---Gets 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, tuple < > syntax sugar, 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
The SQL statement INSERT INTO is mainly used to insert data
//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 the t1 table is the primary key, and those with the same primary key value are updated, otherwise they are inserted
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 the SQL tutorial for details.
//Query the first record. This is not safe. The rowid is not continuous, but 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 (records), but the correlation number of B-Tree at the time of INSERT.
To know the number of rows (records) in the table, you should:
cmd.CommandText = "SELECT count(*) FROM t1"; sr = cmd.ExecuteReader(); sr.Read(); Console.WriteLine(sr.GetInt32(0).ToString()); sr.Close();
19, Business
A transaction is a set of execution units that operate logically on a database. The advantage of using transactions is that mature databases optimize intensive disk IO operations, and can also withdraw and rollback operations. In fact, it was used in the example of changing column names above.
//---Business
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's built-in command VACUUM. It is used to reorganize the whole database to achieve compact use, such as completely deleting the deleted ones, etc.
cmd.CommandText = "VACUUM"; cmd.ExecuteNonQuery();
Note: the address of the above reference resources is https://www.cnblogs.com/leemano/p/6578050.html#c1
If the infringement contact can be deleted.
[example demo]
The following is the source code for your own operation. As a record, the following dll is used. Download address:
//insert data
string StrConnect=@"Data Source=.\DataBase.s3db;Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10";//Database address
var connection = new SQLiteConnection(StrConnect);//Database connection
var compiler = new QueryBuilder.Compilers.SqliteCompiler();
var db1 = new QueryFactory(connection, compiler);
connection.Open();//open
IDbTransaction trans = db1.Connection.BeginTransaction();
try
{
var query = db1.Query(tableName);
foreach (var item in pList)
{
query.Insert(new
{
ID = item.ID,
BDCDYH = item.BDCDYH,
ZDDM=item.ZDDM
}, trans);
trans.Commit();//Submit
}
catch (Exception ex)
{
trans.Rollback();//RollBACK
}
finally
{
connection.Close();//Close database connection
}