". NET" Learning SQLite (1)

About SQLite

SQLite, is a lightweight database, is to adhere to the Acid relational database management system, its design goals are embedded, and has been used in many embedded products, it occupies very low resources, in embedded devices, may only need hundreds of K of memory is enough. It can support Windows/linux/unix and so on mainstream operating system, and can be combined with many programming languages, such as TCL, C #, PHP, Java, and ODBC interface, also compared to MySQL, PostgreSQL, the two open source world-renowned database management system, is processing faster than they do.

SQLite download installation and database creation

SQLite: http://www.sqlite.org/download.html

Here I downloaded the support Windows system, based on the 32-bit version

Download unzip, you will see on an EXE file without installation, if double-click this file, the DOS window will pop up, prompting "connect to a temporary in-memory database".

If we want to create our own database, then we need to create a folder to save the database, and then we must copy the EXE file to the folder where we save the database, otherwise we cannot create the database. I'm here to create a SQLite folder below the C drive, and then move the Sqlite.exe to this folder.

1. Create a database:

Open the DOS window and first go to the directory we just created:
c:\users\administrator>cd/
C:\>CD C:/sqlite Then we use the Sqlite3 command input: Sqlite3 Test.db, at this point we did not see the test database under the folder, because the database does not have any tables and data, so we do not see the database created, but the database has been created.

2. CREATE TABLE News:

This completes the creation of the table, and when the primary key is set to Integer, the primary key is automatically increased;

3. When inserting data:

Here we can see that as long as we do not enter a semicolon, SQLite does not automatically execute the statements we entered, when we enter the semicolon, we confirm the input is finished, and finally executed.

4, query table data:

5. gets the primary key of the last inserted data:

6. In dos, type CTRL + C, exit database, UNIX, use Ctrl+d

For more SQLite commands, you can look at the official documentation: http://www.sqlite.org/cli.html

SQLite management tool Navicat for SQLite

Nvaicat for SQLite is a paid software, free to use for 1 months, for the students to learn is enough;

: Http://www.navicat.com.cn/products/navicat-for-sqlite

Here is a detailed description of how to operate this SQLite database, but in the course of use, it seems that it is not possible to create a database directly through the software, only the additional database options, here I have just created the database to add up:

Here we can see the database we just created under DOS!! We can operate a simple and compact SQLite database as we do with SQL Server.

Connect to the SQLite database in your project

First we want to download the System.Data.SQLite component: Http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

After the installation, find the installation directory, add a reference to the System.Data.SQLite.dll in the VS project, and then we can operate the SQLite database like SQL Server, there are corresponding database connection classes, adapters and so on.

 SQLiteConnection con = new SQLiteConnection();
            con.ConnectionString = @"Data Source=C:\Sqlite\Test.db";
            con.Open();

            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = con;
            cmd.CommandText = "Select * from Tb_User";
            SQLiteDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                MessageBox.Show(string.Format("{0}\t{1}", reader["Id"], reader["Name"]));
            }
            reader.Close();
            con.Close();

Here, we can also operate sqlite through the EF, but I myself in the experiment, the installation of the first component, in the addition of Entity data type, select the database is not found SQLite one, it is estimated that the version has a problem. Let's just record here.

Reference article: http://www.cnblogs.com/aehyok/p/3981965.html

". NET" Learning SQLite (1)