The summary of SQLite and its use in C #

These days touched a bit of SQLite, is a little harvest it, so summarize.

About SQLite:

SQLite is a database engine written in C and can be run on Linux, Windows, Mac platforms.

SQLite installation is simple, download, unzip the configuration environment variable is good, then you can use the command line to operate.

Each database in SQLite is in the form of a single file, and it is rumored that a database has a maximum capacity of 2T.

In terms of things, multiple processes are allowed to read data from the same database at the same time, but only one can write.

When SQLite is used, the database reads and writes directly from the disk, and he does not support remote links.

Usage Scenarios for SQLite:

The most common is the development of Android and iOS, because SQLite occupies a small space and memory, at most, also hundreds of K, to save some data locally, not through the network and remote database interaction.

I also thought of an example of a conversation with an iOS-developed comrade for a while, and in iOS development, I asked the user name, the password, whether it was stored in SQLite as well. This he replied, no, because iOS has a dedicated list collection Nsuserdefaults, This set is a key-value pair, save some basic information, you can save a lot of data types. This I think and H5 inside of the local storage, like the original Appcan this development mixed application of the app, there is also a localstorage such things, specifically store some such as account password information. Of course, iOS now has a more bull- coredata, is object-oriented, better use.

SQLite command operation

1. Execute the sqlite3 command.
When the command is executed, no arguments are passed to indicate that the default connection is to a memory database, and the database is automatically destroyed when the program is exited.
2. Exit the command:. Quit or. Exit.
3. Create a database: Sqlite3 test.db
  Note : After the database is created, the corresponding file is not immediately generated on disk, and the database file is not written to disk until the object is created in the database. Or you can use the. databases command, too.

4. Set the query statement display style:
. Mode column--sets the display of data in columns
. Headers on--Displays the column name when the query is set
. Echo on--sets the echo when executing SQL statements, the command just executed
. nullvalue null--Displays all empty values as null when setting the display

. databases--Displays all databases currently attached to the manager

. Tables displays all the tables that you can see in the current manager

. Schema display script for content in database

. Schema object name displays a script for an object

5. Create an index
Create INDEX Ix_mytable_name on mytable (name);

--View all the indexes
. Indices

Limit n; equivalent to top in T-SQL

offset N; Skip the first few.

6. Date function:
Select Date ()
Select time ();
Select time (' Now ', ' localtime '); --17:51:16
Select DateTime (' Now ', ' localtime ');--2017-07-06 17:51:35

Introduction of a SQLite management tool: Sqlitestudio

SQLite is used in C #

  SQLite provides a variety of language-operating APIs, and of course, C #. The method of writing is almost the same as that of ADO. But to quote System.Data.SQLite.dll, we create a new project and use NuGet to install System.Data.SQLite directly, so you can. You can also go to the official website to download the installation package, after the installation is complete, to refer to this DLL.

Table structure:

 
 
CREATE TABLE [mytable] (

[id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,

[value] text  NULL

, email text   null);

C # code:

/ / Create a connection string
             String constr = "Data Source=C:/SQLite/test.db;";
             / / Create a connection object
             Using (SQLiteConnection con = new SQLiteConnection(constr))
             {

                 / / Create a SQL statement
                 String sql = "select * from mytable";
                 / / Create a command object
                 Using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
                 {

                     con.Open();
                     Using (SQLiteDataReader reader = cmd.ExecuteReader())
                     {

                         If (reader.HasRows)
                         {
                             While (reader.Read())
                             {
                                 Console.WriteLine(reader.GetInt32(0)+" , "+reader.GetString(1));

                             }
                         }
                     }
                 }
             }

Note : You may experience the inability to load DLL "SQLite.Interop.dll": This error is not found for the specified module . Workaround, the project references SQLite.Interop.dll, if it doesn't work, replace the System.Data.SQLite.dll version.

Finally, also encapsulated a own class library, about SQLite, later, should slowly accumulate their own class library.

Http://git.oschina.net/sdadx/leiku/blob/master/SqlLibrary/SqliteHelper.cs

The summary of SQLite and its use in C #