Develop common management tools for SQLite Databases

Although I have been using SQL Server2000 and 2005 for development projects, it is not appropriate to use such databases to develop small applications,

But I always think that ACCESS is a thing of the past and is not very compatible with C. So I searched the internet and found that SQLite is supported. NET development of the green database, directly a System. data. SQLite. DLL can access the SQLite database, perform various operations, and support transactions.

I found a suitable database, but I went to the official website and installed the installation package with a DLL file without a management tool. How can I create a database and a table? It seems that you can only find third-party development tools on the Internet.

There are still a lot of management tools available for searching on the Internet. I think we should introduce them in order of installation.

 

SQLite Administrator was first found. The latest version is 0.8.3.2, but it seems to have been updated a long time ago. After reading the author's introduction, the basic functions of the database are available, and the software looks pretty.

Advantage: this software is easy to use because it has a Chinese language. You can write SQL statements, View table data, and modify table data at any time.

Disadvantage: It seems that the support for Chinese characters is not very good, because I entered Chinese characters in the program and it showed garbled characters, but what I saw in it was garbled characters on the display to the development program.

The first SQLite database was built by myself, and all the tables were created. At the time of development, we found that the support for Chinese was poor. Unfortunately, there was no place for the software to change the language. My software is developed, but debugging is all written by myself.

The interface shows that it is still a little inefficient to change things through management tools. There is no way to search online

 

The second one found is SharpPlus SQlite Developer, which is a commercial development.

Advantage: also supports Chinese interface, and character encoding can support UTF-8, so there is no garbled problem. Because it is commercial, it requires more functions and more convenient operations.

Disadvantage: I feel the interface is a little earthy.

 

The third is SQLite Expert Personal 1.7.13.

This developer is better. It provides a free version. Since the business is collecting money, you can use it for free first.

Advantages: Support UTF-8 encoding, not Chinese garbled, the interface is as beautiful as SQLite Administrator, the operation is very convenient.

Disadvantage: English interface.

Because SQLite does not need servers, but does not support stored procedures, user-defined functions, and other functions, the functions of its management tools are not that complex, but SQLite is not as good as other databases!

1. Download SQLite 3.3.4 from www.sqlite.org
For convenience, I solved the issue and put sqlite3.exe in the Windows directory.
Run Cmd to enter the command line
1)
Create a database file:
> SQLite3 d: \ test. db press ENTER
A test. db is generated on disk D.
In this way, SQLite3 also hangs the test. db
2)
Use. help to see what commands are available
>. Help press Enter.
3) You can enter the SQL statement to create a table. After that, press enter to view the table.
4) Check the number of tables created.
>. Tables
5) view the table structure
>. Schema table name
6) Check the database currently on.
>. Database
7) If you want to output the query to a file
>. Output file name
> Query statement;
The query result is output to the file c: \ query.txt.

Output query results on the screen
>. Output stdout

8) output the table structure and the index will also output
. Dump table name
9) Exit
>. Exit or. quit

2. Download the ado.netdriver from http://sqlite.phxsoftware.com.
Downloaded and installed. The installation directory contains System. Data. SQLite. dll.
We only need to copy this file to the Reference Directory and add the reference to operate on the SQLite database.
All Ado.net objects start with SQLite, such as SQLiteConnection.
The connection string only needs the following method:
Data Source = d: \ test. db or DataSource = test. db -- the directory that the application can automatically find with the application or. net.
The rest is simple ~~

3. SQL syntax
Since SQL Server or ISeries were used in the past, the DDL syntax is very embarrassing.
1) create a table with a single Primary Key
Create table [Admin] (
[UserName] [nvarchar] (20) primary key not null,
[Password] [nvarchar] (50) not null,
[Rank] [smallint] not null,
[MailServer] [nvarchar] (50) not null,
[MailUser] [nvarchar] (50) not null,
[MailPassword] [nvarchar] (50) not null,
[Mail] [nvarchar] (50) NOT NULL
);
2) create a table with multiple Primary keys
Create table [CodeDetail] (
[CdType] [nvarchar] (10) not null,
[CdCode] [nvarchar] (20) not null,
[CdString1] [ntext] not null,
[CdString2] [ntext] not null,
[CdString3] [ntext] not null,
Primary key (CdType, CdCode)

);
3) create an index
Create index [IX_Account] ON [Account] ([IsCheck], [UserName]);

View and so on.
4. Useful SQL statements
Select * from Sqlite_master
Select datetime ('now ')
Select date ('now ')
Select time ('now ')
For more information, see the SQLite wiki.

Oh, some people say that it seems that starting a transaction during batch insertion is n times faster than not starting a transaction.
In addition, it is estimated that automatic Prepare can be used as much as possible using parameterized SQL statements as commercial databases.

==============

Sqlite can directly execute the command under shell/dos command:

Sqlite3 film. db "select * from film ;"

Output HTML table:

Sqlite3-html film. db "select * from film ;"

"Pour out" the database 」:

Sqlite3 film. db ". dump"> output. SQL

Use the output data to create an identical database (with the preceding command, the standard SQL database is backed up ):

Sqlite3 film. db <output. SQL

When inserting a large amount of data, you may need to run the following command first:

Begin;

After inserting the data, remember to execute this command before the data is written into the database:

Commit;

;