To create an SQLite console application using C #

This article belongs to the original, reproduced please indicate the source, thank you!

First, the development environment

Operating system: Windows ten X64

Development environment: VS2015

Programming Language: C #

. NET version:. NET Framework 4.0

Target platform: X86

Second, preface

Before using the software's access to create a local-based database software, found in the use of the process, found that access is less memory, run slower than SQLite, and the other most important is that access encrypted files are easily cracked, so, Now turn to use SQLite instead of access.

Third, the download of SQLite

1, because it is based on. NET C # programming, need to download System.Data.SQLite.dll for reference, download the following URL:

/ http System.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

2, because the use of the. NET Framework 4.0, and the target platform for Software development is X86, so download the Sqlite-netfx40-setup-x86-2010-1.0.105.2.exe software, download the following URL:

/ http System.data.sqlite.org/downloads/1.0.105.2/sqlite-netfx40-setup-x86-2010-1.0.105.2.exe

In addition, there is a sqlite-netfx40-setup-bundle-x86-2010-1.0.105.2.exe, and the difference between it and Sqlite-netfx40-setup-x86-2010-1.0.105.2.exe is that It contains VS2010 runtime components, because the use of the VS2015, all the software installed with the bundle is not really useful, if you are using VS2010, you can download the installation, download the following URL:

/ http System.data.sqlite.org/downloads/1.0.105.2/sqlite-netfx40-setup-bundle-x86-2010-1.0.105.2.exe

3, the download is the setup package, so just follow the next step (next) or installation (install), until the installation is complete.

Iv. creating an encrypted version of the SQLite database

The SQLite database management software used here is SQLite Studio, version 3.1.1, the download URL is as follows:

https:// Sqlitestudio.pl/files/sqlitestudio3/complete/win32/sqlitestudio-3.1.1.zip

Some people will ask, how not to use Navicat for SQLite, this software not only has the full Chinese version, but also has the detailed use instruction, is not better?

In fact, originally wanted to use this software to manage the SQLite database, but when creating an encrypted version of the SQLite database, When you discover that using System.Data.SQLite.dll to manipulate the database, it pops up a hint like the database file is encrypted or not a database file, but not an encrypted version, so you can use Navicat for SQLite operates on non-encrypted sqlite data, but if you need to use an encrypted SQLite database, it is recommended to use SQLite Studio, as it generates an encrypted version of the SQLite database that can be System.Data.SQLite.dll recognized.

The reason why you cannot use Navicat for SQLite to encrypt database memory operations is because NAVICAT uses a different encryption algorithm than SQLite studio.

1. Open SQLite Studio and click on the "Add Database" button as shown in:

2, data type select "System.Data.SQLite", click the Green Add button, save the file to D:\Test.db, and enter the password in the password field: 2017 (if you do not use the password, leave it blank), and then click "OK" button to create an encrypted version of the SQLite db file as shown in:

3, double-click the "Test" database, will display its sub-nodes, select "Tables", and click on the toolbar "new Table" button, as shown in:

4. In the Table Name text box, enter the form name info and click on the "Add Columns (Ins)" icon to add the column as shown in:

5. Add the first field, the field name is ID, the data type is text, tick the checkbox before the primary key, and click the "OK" button as shown:

6, repeat the 4th step, click on the "Add Columns (Ins)" icon to add another column, the field name is name, the data type is text, the non-unprecedented check box tick, and click the "OK" button, as shown in:

7. Click on the "Commit structure changes" icon to save the table and fields as shown in:

8. Click the "OK" button in the popup dialog to complete the creation of the form, as shown in:

9. Select the "Data" tab and click on the "Insert Line (INS)" icon button as follows:

10, enter two data in the Line data edit field, the first Data ID is 2017, name is LSB, the second data ID is 2011, name is CNC, as shown:

11. After adding the data, click the "Submit (Ctrl+return)" icon button to submit the two data you just entered, as shown in:

12. Turn off SQLite Studio database management software.

V. Accessing the SQLite database using System.Data.SQLite.dll

1. Create a new console application named "Sqlitetest", as shown in:

2. In the "Sqlitetest" project, right-click on "References" and select "Add Reference (R)" In the popup context menu, as shown in:

3. Add a reference to the path "C:\Program Files (x86) \system.data.sqlite\2010\gac\system.data.sqlite.dll", as shown in:

4. In the project properties setting, change the target platform in the Build tab to "X86", otherwise the software will not run because the X86 version of System.Data.SQLite.dll is used, as shown in:

5. In main function, enter the following code as follows:

 
using System; using System.Data.SQLite; using System.Data; namespace SQLiteTest
{ class Program
    { static void Main(string[] args)
        { using (SQLiteConnection connection = new SQLiteConnection(@"Data Source = D:\test.db;Password = 2017"))
            { using (SQLiteCommand command = new SQLiteCommand(connection)
                { CommandText = "SELECT * FROM [main].[Info]" })
                {
                    DataTable table = new DataTable("Info");
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    adapter.Fill(table); foreach(DataRow row in table.Rows)
                    { foreach(DataColumn col in table.Columns)
                        {
                            Console.Write($"{row[col]}\t");
                        }
                        Console.WriteLine();
                    }
                }
            }
            Console.ReadKey();
        }
    }
}

6. The result of operation is as follows:

Attached source code:

https:// PAN.BAIDU.COM/S/1BPEXXVX

To create an SQLite console application using C #