Simple Sqlite tutorial

I,SQLiteIntroduction

SQLite is an ACID-compliant relational database management system, which is contained in a relatively small C library. It is a public project established by D. Richard IPP.

Unlike the common client-server example, the SQLite engine is not an independent process for communications between programs, but a major part of the connection to programs. Therefore, the main communication protocol is direct API calls in programming languages. This plays a positive role in total consumption, latency, and overall simplicity. The entire database (definition, table, index, and data itself) is stored in a single file on the host. Its simple design is completed by locking the entire data file at the beginning of a transaction.

This introduction is not mentioned. It is a small SQL database, which is a bit similar to ACCESS. Let's try its functions first.

2. Create an sqlite Database

1. Create an empty sqlite database.

// You can directly specify the suffix of the database name, even without a suffix
// Method 1: Create an empty sqlite database in IO Mode
FileStream fs = File. Create ("c: \ test. db");
// Method 2: Use SQLiteConnection
SQLiteConnection. CreateFile ("c: \ test. db");

The created database is a 0-byte file.

2. Create an encrypted empty sqlite Database

// Create an empty sqlite database with a password
SQLiteConnection. CreateFile ("c: \ test2.db");
SQLiteConnection cnn = new SQLiteConnection ("Data Source = c: \ test2.db");
SQLiteConnection cnn = new SQLiteConnection ("Data Source = D: \ test2.db");
Cnn. Open ();
Cnn. ChangePassword ("password");

3. Encrypt unencrypted Databases

SQLiteConnection cnn = new SQLiteConnection ("Data Source = c: \ test. db");
Cnn. Open ();
Cnn. ChangePassword ("password");

4. Open the encrypted sqlite Database

// Method 1
SQLiteConnection cnn = new SQLiteConnection ("Data Source = c: \ test2.db");
Cnn. SetPassword ("password");
Cnn. Open ();
// Method 2
SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder ();
Builder. DataSource = @ "c: \ test. db";
Builder. Password = @ "password";
SQLiteConnection cnn = new SQLiteConnection (builder. ConnectionString );
Cnn. Open ();

Paging

Select * from messages limit 10,100;

This indicates that 10 rows are skipped and the returned results of 100 rows are taken.

 

III,Preliminary Test

After searching by multiple parties, I found the SQLite support library on the. NET platform and click here to download it. By default, the installation path is "c: // program files/sqlite.net". After installation, enter this folder, which contains many files. We only need "System. data. SQLite. DLL ", very small, only 605 K. Open VS and create a new console program sqlitetest. Reference the DLL file just mentioned. Then reference the "System. Data. SQLite" namespace. Now you can try it.

I have found some information on the Internet. I can use it now.

Step 1: Create a database file.

I declare a global string variable "dbName", which will be used later.

The code for creating a database file is:

 

SQLiteConnection. CreateFile (dbName );

 

Is it super simple. Next, the connection is established. It is also the SQLiteConnection class. This class has three constructor functions, one being a null parameter, and the other being the SQLiteConnection type, that is, copying a connection, and the last being a string (connection string ), the connection string contains the database information and the password (this lightweight database also supports the password, which is amazing). I still don't know what the format is. First try the default constructor. On the Internet, SQLiteConnectionStringBuilder is used to construct the connection string. It has many attributes. The DataSource attribute is used to assign dbName to it. There is also the Password attribute. If there is no Password, assign it. Then open the database. All code is as follows:

// Establish a database connection and open the database
SQLiteConnection conn = new SQLiteConnection ();
SQLiteConnectionStringBuilder connsb = new SQLiteConnectionStringBuilder ();
Connsb. DataSource = dbName;
Connsb. Password = "";
Conn. ConnectionString = connsb. ToString ();
Conn. Open ();

 

Next, you should try inserting a table.

SQLite has a SQLiteCommand similar to SQLCommand, and its usage is similar. This is not much to say. Let's give the code directly:

// Add a table
SQLiteCommand cmd = new SQLiteCommand (conn );
String literal text = "create table test (ID int, name varchar (20 ))";
Cmd. CommandText = plain text;
Cmd. ExecuteNonQuery ();

 

The code for inserting data is also pasted together, all of which are the same:

 

// Insert Test Data
Cmd. CommandText = "insert into [TEST] (ID, name) VALUES (1, 'acen ')";
Cmd. ExecuteNonQuery ();
Cmd. CommandText = "insert into [TEST] (ID, name) VALUES (2, 'unique ')";
Cmd. ExecuteNonQuery ();

 

Next we need to query the data to see how the results are. The online tutorial uses SQLiteReader. Is it similar to SQLReader? Yes. Actually, the usage is the same. The Code is as follows:

// Query results
Cmd. CommandText = "SELECT * FROM [TEST]";
SQLiteDataReader dr = cmd. ExecuteReader ();
StringBuilder sb = new StringBuilder ();
While (dr. Read ())
{
Sb. Append (dr. GetInt32 (0). Append ("" n "). Append (dr. GetString (1 ));
}
Console. WriteLine (sb );

 

It's okay if you have used ADO. NET to understand the above Code. All right, let's run it!

Wait. Oh, an error occurred: Invalid ConnectionString format for parameter "Password". It turns out there is a problem with this sentence. Okay, delete it and try again. All right, huh, it's successful! Let's take a look at the article on the Internet and comment on "set password", which means to set a password in this step? Try. Now, let's test the related things.

Okay, the first battle is successful. Now let's test it further! Paste the complete code.

 

Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Text;
Using System. Data. SQLite;

Namespace sqlitetest
{
Class Program
{
Private const string dbName = "sqlitetest. db ";
Static void Main (string [] args)
{
// Create a database file
SQLiteConnection. CreateFile (dbName );
// Establish a database connection and open the database
SQLiteConnection conn = new SQLiteConnection ();
SQLiteConnectionStringBuilder connsb = new SQLiteConnectionStringBuilder ();
Connsb. DataSource = dbName;
Connsb. Password = "acen ";
Conn. ConnectionString = connsb. ToString ();
Conn. Open ();

// Add a table
SQLiteCommand cmd = new SQLiteCommand (conn );
String literal text = "create table test (ID int, name varchar (20 ))";
Cmd. CommandText = plain text;
Cmd. ExecuteNonQuery ();

// Insert Test Data
Cmd. CommandText = "insert into [TEST] (ID, name) VALUES (1, 'acen ')";
Cmd. ExecuteNonQuery ();
Cmd. CommandText = "insert into [TEST] (ID, name) VALUES (2, 'unique ')";
Cmd. ExecuteNonQuery ();

// Query results
Cmd. CommandText = "SELECT * FROM [TEST]";
SQLiteDataReader dr = cmd. ExecuteReader ();
StringBuilder sb = new StringBuilder ();
While (dr. Read ())
{
Sb. Append (dr. GetInt32 (0). Append ("" n "). Append (dr. GetString (1 ));
}
Console. WriteLine (sb );
Console. ReadKey ();
}
}
}

 

III,Connection test

The second part is based on an article on the Internet. Now I want to test it myself. You can't create any more databases this time. Okay. Start by deleting the database file and table. Run the command directly. There are four records. It indicates that the file connection is normal and has played a role in storage.

Study the content of SQLiteConnection.

The first is its constructors. No matter what the replication connection is. Check the remaining constructor to see the format. The test is not feasible. Do you need to debug it in one step? Instead, convert the value of connsb (SQLiteConnectionStringBuilder) to string and assign it to conn (SQLiteConnection). Let's take a look at the value of connsb, yes, it is "Data Source = sqlitetest. db; Password = acen ", this should be the case. Try. If the test passes, remove the password and try again ." File opened that is not a database file. file is encrypted or is not a database ". As expected, an error occurred. When I checked the SQLiteConnection attribute, I saw a DataSource item. Can I directly assign values using SQLiteConnectionStringBuilder? Hey, don't be proud too early. This is read-only and won't work. What should I do if I want to change my password? Let's take a closer look. It must be impossible to connect strings. Let's take a look at the SQLiteConnection class. There is a SetPassword method, which has two parameters, one of which is a byte array, the other is the string format. Use the second one. Change it to "imacen" to run. The problem is that it can be run multiple times. Isn't the password changed? An error occurred while adding the new password to the connection string. I just put it before opening the database. Can't I do this? Put it at the end, or wrong, prompting that the password should be set before the database starts. Is this just the password used to set the connection? Hi, I am wrong. I didn't take it seriously. It also has a changePassword method. This is the correct method, and it should be placed after the database is opened. Then I wondered why there is no setDataSource METHOD FOR THE setPassword method? Delete the password of the connection string and try the setPassword method. If it succeeds, it is actually set the connection password. I think why should it be added to this method? It may take into account new security issues. Others use plain text, just speculation. That's all about this part. If you can connect to it, you have.

Summary:

1. You can use the SQLiteConnectionStringBuilder class to construct a link string and convert it to the ConnectionString attribute assigned to SQLiteConnection. If the password is not set, do not write it.

2. When constructing SQLiteConnection, use the connection string in the format of "Data Source = xxx; Password = xxx". The Password can be set in the setPassword method.

3. Change the password and use the changePassword method of SQLiteConnection. After the database is opened.

IV,Insert data test

SQLite does not support stored procedures. Although its class SQLiteCommand has a CommandType attribute, it clearly states that only CommandType. Text is supported currently. What should I test? Parameter Function. Start!

Remember to INSERT data in the previous Code. Now, replace the INSERT statement with "insert into [TEST] (ID, name) VALUES (@ ID, @ name )"; there is the Parameters attribute. Yes, I'm sorry. even Add and AddWithValue are available. Well, use AddWithValue for the simplest try.

 

// Insert Test Data
Cmd. CommandText = "insert into [TEST] (ID, name) VALUES (@ ID, @ name )";
Cmd. Parameters. AddWithValue ("@ ID", 5 );
Cmd. Parameters. AddWithValue ("@ name", "testtest ");
Cmd. ExecuteNonQuery ();

 

Run, apparently. Then let's compare it with SQLCommand. After a rough look, some sqlitecommands in SQLCommand are available. Does SQLiteCommand inherit from SQLCommand? Or the two are inherited from the same interface. From the perspective of "System. Data. SQLite" and "System. Data. SQLClient", the two are inherited from the same interface. See if you can find evidence.

Obviously, they actually inherit from the same class or interface, so there is nothing to say, and this test is almost the same. Other reads, it should be the same for updating those.

Later, I also analyzed that this SQLite should be to rewrite each class, and then compress SQL Server functions into DLL files. I have never used ACCESS, but I think SQLite and ACCESS should be similar. I dare not assert this.

The advantage of using SQLite is that it can execute most SQL statements, and online comments think that its performance is quite high. Another important reason is that it is very small, the smaller size than 1 MB is very convenient for small data applications.

Select * from messages limit 10,100;

This indicates that 10 rows are skipped and the returned results of 100 rows are taken.