The use of SQLite and tool class in C #

Time:2021-7-8

Introduction to SQLite

SQLite is a lightweight database,A database is a fileFor details, please refer to the official website:https://www.sqlite.org/index.html

The SQLite data type is a property that specifies the data type of any object. Each column, variable and expression in SQLite has a related data type.
You can use these data types while creating tables. SQLite uses a more generalDynamic type system。 In SQLite, the data type of a value is related to the value itself, not to its container.

SQLite data is different from common mysql, SQL and other databases. It isDynamic type databaseThe storage space of each value in the database is determined according to the size of the value, and attention should be paid to the data type when using.

Storage class

Each value stored in the SQLite database has the followingStorage classone of:

Storage class describe
NULL Value is a null value.
INTEGER A value is a signed integer stored in 1, 2, 3, 4, 6, or 8 bytes depending on the size of the value.
REAL The value is a floating-point value stored as an 8-byte IEEE floating-point number.
TEXT The value is a text string stored in a database encoding (UTF-8, utf-16be, or utf-16le).
BLOB The value is a blob data, stored entirely according to its input.

Affinity type

SQLite supports the concept of affinity type of columns. Any column can still store any type of data,When data is inserted, the data of this field will be preferentially stored in affinity type
Various data type names and corresponding affinity types that can be used when creating SQLite3 tables are as follows:

data type Affinity type
INT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8
INTEGER: for a field whose kinship type is integer, the rule is equivalent to numeric, and the only difference is when the cast expression is executed.
CHARACTER(20)
VARCHAR(255)
VARYING CHARACTER(255)
NCHAR(55)
NATIVE CHARACTER(70)
NVARCHAR(100)
TEXT
CLOB
TEXT: before inserting numerical data, it needs to be converted to text format, and then inserted into the target field.
BLOB
no datatype specified
NONE: store the data directly in the data type without any conversion.
REAL
DOUBLE
DOUBLE PRECISION
FLOAT
REAL: its rules are basically equivalent to numeric, the only difference is that it will not convert text data such as “30000.0” to integer storage mode.
NUMERIC
DECIMAL(10,5)
BOOLEAN
DATE
DATETIME
NUMERIC
When text data is inserted into a field whose affinity is numeric:
If the conversion operation will not cause data information loss and complete reversibility, SQLite will convert the text data into data of type integer or real;
If the conversion fails, SQLite still stores the data as text.

For new data of null or blob type, SQLite will store the data in the form of null or blob without any conversion.

Note: for constant text in floating-point format, such as “30000.0”, if the value can be converted to integer without losing numerical information, SQLite will convert it to the storage mode of integer.

Reference system.data.sqlite.dll

Using SQLite database in C # needs referenceSystem.Data.SQLite.dll, download link:http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Note:System. Data. SQLite is the ADO. Net provider of SQLiteThey are two different open source projects. Now the development and maintenance of system. Data. SQLite are mostly carried out by the SQLite development team.

There are too many options on the download page of system.data.sqlite. Most people don’t know what to download when they come in. Here is a brief introduction to the software package in the download interface.

Package classification

Download content is divided intoInstallation packageNon statically connected binary packagesandStatically connected binary packageThe differences are as follows:

  • The installation package is only used to install on the developer’s computer, and then install it only when the design time component of visual studio is needed, and it is not recommended to install it on the client machine (theoretically, it can also be installed).
  • The installation package will install the relevant dynamic libraries into the system, andRegister with GAC(Global Assembly Cache)。
  • Binary packages are designed for use by developersIn order to get the assembly binaries needed for development, andDeploy its application to the client through xcopy deployment
  • The difference between the two binary packages is that the unmanaged part is connected in different ways,The binary package of non static connection needs the support of VC runtime library when it is used

Note: if all target machines have visual c + + runtime installed or can be easily deployed, you shouldAvoid “static” packages

For each type. net versionDivided into groups, each. Net version is further divided into32 bitand64 bitTwo groups

  • The supported. Net versions are2.0 SP2 、 3.5 SP2 、4.0 、 4.5 、 4.5.1 、4.6
  • The choice of 32-bit or 64 bit is determined by the use of the system, such as 64 bit (using 64 bit DLL) system when developing and running on 32-bit (using 32-bit DLL) system after publishing.

Note: Although. Net higher version is compatible with lower version, it is highly recommendedSelect the package that matches the target. Net Framework version

In each. Net version – digit group, there are two packages, one with the word “bundle” and the other without:

  • With “bundle”The dynamic library is based on theMixed mode compilationYes, in useOnly system. Data. SQLite. DLL is requiredThat’s it.
  • Without “bundle”What is more important is that theUnmanaged and managed parts are compiled separatelySystem.data.sqlite.dll can’t be used independently, and sqlite.interop.dll is needed

Note: unless absolutely necessaryAvoid using “bundle” packages

According to the above, if the development machine and the client machine may have different processor architectures, more than one binary package may be required.

Preloading with native libraries

The native library preloading function is available from version 1.0.80.0, and is enabled by default. It can automatically adapt to the number of bits in the current system. To take advantage of this feature,Separate managed and interoperable assemblies must be used with xcopy deployment(mixed mode assembly, installation package deployment does not support this feature).
When using the native library preload feature, the application deployment looks like this(Bin represents the directory where the application binaries will be deployed on the target computer):

  • Bin \ \ app.exe (optional, managed application executable assembly only)
  • Bin \ \ app.dll (optional, only managed application library assemblies)
  • bin \ System.Data.SQLite.dll(essential, managed core assemblies only)
  • Bin \ \ system.data.sqlite.linq.dll (optional, only managed LINQ assemblies)
  • Bin \ \ system.data.sqlite.ef6.dll (optional, managed ef6 assemblies only)
  • bin \ x86 \ SQLite.Interop.dll(essential, x86 native interop assembly)
  • bin \ x64 \ SQLite.Interop.dll(essential, x64 native interop assembly)

When native library preloading is enabled and the application deployment above is displayed, system.data.sqlite only managed assemblies will attempt toAutomatically detects the processor architecture of the current process and preloads the appropriate native libraries, at this timeRegardless of whether the client machine is 64 bit or 32-bit

Common deployment packages

I reassemble the corresponding software packages of. Net version 4.0 and 4.5 according to the requirements of the preloading function of the local library, and directly copy them to the debug directory when using them

Note:The official recommendation is not to use static binary packagesPersonally, I like to use static binary package, so I don’t need to consider whether the corresponding VC runtime library is installed on the client machine.

Tools

Most of the content of tools comes fromC # SQLite help classConsidering that SQLite is a database, a file and a project may need multiple databases, I change the tool class to operate the database through object instances and provide a static object instance dictionary.
The code of tool class is as follows:

public class SQLiteHelper
{
    /// 
    ///Database list
    /// 
    public static Dictionary DataBaceList = new Dictionary();

    /// 
    ///Constructors
    /// 
    ///Database file name
    public SQLiteHelper(string filename=null) 
    {
        DataSource = filename;                     
    }

    /// 
    ///Database address
    /// 
    public string DataSource { get; set; }        

    /// 
    ///Create a database and ignore if the database file exists
    /// 
    public void CreateDataBase() 
    {
        string path = Path.GetDirectoryName(DataSource);
        if ((!string.IsNullOrWhiteSpace(path)) && (!Directory.Exists(path))) Directory.CreateDirectory(path);
        if (!File.Exists(DataSource)) SQLiteConnection.CreateFile(DataSource);          
    }



    /// 
    ///Get connection object
    /// 
    /// SQLiteConnection       
    public SQLiteConnection GetSQLiteConnection()
    {
        string connStr =string.Format("Data Source={0}", DataSource);            
        var con = new SQLiteConnection(connStr);
        return con;
    }

    /// 
    ///Prepare operation command parameters
    /// 
    /// SQLiteCommand
    /// SQLiteConnection
    ///SQL command text
    ///Parameter array
    private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, Dictionary data)
    {
        if (conn.State != ConnectionState.Open)
            conn.Open();
        cmd.Parameters.Clear();
        cmd.Connection = conn;
        cmd.CommandText = cmdText;
        cmd.CommandType = CommandType.Text;
        cmd.CommandTimeout = 30;
        if (data != null && data.Count >= 1)
        {
            foreach (KeyValuePair val in data)
            {
                cmd.Parameters.AddWithValue(val.Key, val.Value);
            }
        }
    }

    /// 
    ///Query, return dataset
    /// 
    ///SQL command text
    ///Parameter array
    /// DataSet
    public DataSet ExecuteDataset(string cmdText, Dictionary data = null)
    {
        var ds = new DataSet();
        using (SQLiteConnection connection = GetSQLiteConnection())
        {
            var command = new SQLiteCommand();
            PrepareCommand(command, connection, cmdText, data);
            var da = new SQLiteDataAdapter(command);
            da.Fill(ds);
        }
        return ds;
    }

    /// 
    ///Query, return datatable
    /// 
    ///SQL command text
    ///Parameter array
    /// DataTable
    public DataTable ExecuteDataTable(string cmdText, Dictionary data = null)
    {
        var dt = new DataTable();
        using (SQLiteConnection connection = GetSQLiteConnection())
        {
            var command = new SQLiteCommand();
            PrepareCommand(command, connection, cmdText, data);
            SQLiteDataReader reader = command.ExecuteReader();
            dt.Load(reader);
        }
        return dt;
    }

    /// 
    ///Return a row of data
    /// 
    ///SQL command text
    ///Parameter array
    /// DataRow
    public DataRow ExecuteDataRow(string cmdText, Dictionary data = null)
    {
        DataSet ds = ExecuteDataset(cmdText, data);
        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            return ds.Tables[0].Rows[0];
        return null;
    }

    /// 
    ///Perform database operations
    /// 
    ///SQL command text
    ///Parameters passed in
    ///Returns the number of rows affected
    public int ExecuteNonQuery(string cmdText, Dictionary data=null)
    {
        using (SQLiteConnection connection = GetSQLiteConnection())
        {
            var command = new SQLiteCommand();
            PrepareCommand(command, connection, cmdText, data);
            return command.ExecuteNonQuery();
        }
    }

    /// 
    ///Returns the SqlDataReader object
    /// 
    ///SQL command text
    ///Parameters passed in
    /// SQLiteDataReader
    public SQLiteDataReader ExecuteReader(string cmdText, Dictionary data = null)
    {
        var command = new SQLiteCommand();
        SQLiteConnection connection = GetSQLiteConnection();
        try
        {
            PrepareCommand(command, connection, cmdText, data);
            SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }
        catch
        {
            connection.Close();
            command.Dispose();
            throw;
        }
    }

    /// 
    ///Returns the first row and first column in the result set, ignoring other rows or columns
    /// 
    ///SQL command text
    ///Parameters passed in
    /// object
    public object ExecuteScalar(string cmdText, Dictionary data = null)
    {
        using (SQLiteConnection connection = GetSQLiteConnection())
        {
            var cmd = new SQLiteCommand();
            PrepareCommand(cmd, connection, cmdText, data);
            return cmd.ExecuteScalar();
        }
    }

    /// 
    ///Paging query
    /// 
    ///Total records
    ///Page pull
    ///Page size
    ///SQL command text
    ///SQL text to query the total number of records
    ///Command parameters
    /// DataSet
    public DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, Dictionary data = null)
    {
        if (recordCount < 0)
            recordCount = int.Parse(ExecuteScalar(countText, data).ToString());
        var ds = new DataSet();
        using (SQLiteConnection connection = GetSQLiteConnection())
        {
            var command = new SQLiteCommand();
            PrepareCommand(command, connection, cmdText, data);
            var da = new SQLiteDataAdapter(command);
            da.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "result");
        }
        return ds;
    }

    /// 
    ///Reorganize the database: Vacuum reorganizes the database from scratch
    /// 
    public void ResetDataBass()
    {
        using (SQLiteConnection conn = GetSQLiteConnection())
        {
            var cmd = new SQLiteCommand();

            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Parameters.Clear();
            cmd.Connection = conn;
            cmd.CommandText = "vacuum";
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = 30;
            cmd.ExecuteNonQuery();
        }
    }

}

The usage of tools is as follows:

static void Main(string[] args)
{
    SQLiteHelper testDb = new SQLiteHelper("test.db");
    SQLiteHelper.DataBaceList.Add("TEST", testDb);

    //Database building
    testDb.CreateDataBase();

    //Table building            
    StringBuilder sbr = new StringBuilder();
    sbr.AppendLine("CREATE TABLE IF NOT EXISTS `test_table`(");
    sbr.AppendLine("`id` INTEGER PRIMARY KEY AUTOINCREMENT,");// Autoincrement ID primary key
    sbr.AppendLine("`name` VARCHAR(100) NOT NULL,");
    sbr.AppendLine("`password` VARCHAR(40) NOT NULL,");
    sbr.AppendLine("`create_time` datetime DEFAULT CURRENT_TIMESTAMP,");
    sbr.AppendLine("`update_time` datetime DEFAULT CURRENT_TIMESTAMP );");            
    sbr.AppendLine();

    sbr.AppendLine("CREATE TRIGGER  IF NOT EXISTS `trigger_ test_ table_ update_ time` ");// Trigger - automatic update_ time
    sbr.AppendLine("AFTER UPDATE ON `test_table` ");
    sbr.AppendLine("FOR EACH ROW ");
    sbr.AppendLine("BEGIN ");
    sbr.AppendLine("UPDATE `test_table` SET `update_time` = CURRENT_TIMESTAMP WHERE id = old.id; ");
    sbr.AppendLine("END;");

    string cmdText = sbr.ToString();
    int val = testDb.ExecuteNonQuery(cmdText);            
    Console. Writeline ("number of affected lines): + VAL)";

    //Increase
    sbr.Clear();
    sbr.Append("INSERT INTO test_table (name,password) VALUES ");
    sbr.Append("(11,111), ");
    sbr.Append("(12,222); ");
    cmdText = sbr.ToString();
    val = testDb.ExecuteNonQuery(cmdText);
    Console. Writeline ("number of affected lines): + VAL)";

    //Delete
    sbr.Clear();
    sbr.Append("DELETE FROM test_table ");
    sbr.Append("WHERE id=1;");
    cmdText = sbr.ToString();
    val = testDb.ExecuteNonQuery(cmdText);
    Console. Writeline ("number of affected lines): + VAL)";

    //Change
    sbr.Clear();
    sbr.Append("UPDATE test_table SET ");
    sbr.Append("name='13', ");
    sbr.Append("password='333' ");
    sbr.Append("WHERE [email protected];");
    cmdText = sbr.ToString();
    Dictionary data = new Dictionary();
    data.Add("@id", "2");           
    val = testDb.ExecuteNonQuery(cmdText, data);
    Console. Writeline ("number of affected lines): + VAL)";

    //Check
    sbr.Clear();
    sbr.Append("SELECT name,password FROM test_table ");
    sbr.Append("WHERE [email protected];");
    cmdText = sbr.ToString();
    DataTable dt = testDb.ExecuteDataTable(cmdText, data);
    Console. Writeline ("number of result rows): + DT. Rows. Count)";                                   

    //Delete除表
    sbr.Clear();
    sbr.Append("DROP TABLE test_table;");
    cmdText = sbr.ToString();
    val = SQLiteHelper.DataBaceList["TEST"].ExecuteNonQuery(cmdText);
    Console. Writeline ("number of affected lines): + VAL)";

    //Reorganize database
    SQLiteHelper.DataBaceList["TEST"].ResetDataBass();

    Console.ReadKey();
}

reference material