C # Use System. Data. SQLite to operate SQLite,

C # Use System. Data. SQLite to operate SQLite,

Use System. Data. SQLite: http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Add System. Data. SQLite. dll to the project reference;

Create and insert tables

1. Static void Main (string [] args)
2. {
3. SQLiteConnection conn = null;
5. String dbPath = "Data Source =" + Environment. CurrentDirectory + "/test. db ";
6. Conn = new SQLiteConnection (dbPath); // create a database instance and specify the file location
7. Conn. Open (); // Open the database. It is automatically created if the file does not exist.
9. String SQL = "CREATE TABLE IF NOT EXISTS student (id integer, name varchar (20), sex varchar (2);"; // TABLE creation statement
10. SQLiteCommand created createtable = new SQLiteCommand (SQL, conn );
11. CmdCreateTable. ExecuteNonQuery (); // If the table does not exist, create a data table.
13. SQLiteCommand cmdInsert = new SQLiteCommand (conn );
14. CmdInsert. CommandText = "insert into student VALUES (1, 'red', 'male')"; // INSERT several pieces of data
15. CmdInsert. ExecuteNonQuery ();
16. CmdInsert. CommandText = "insert into student VALUES (2, 'lil', 'femal ')";
17. CmdInsert. ExecuteNonQuery ();
18. CmdInsert. CommandText = "insert into student VALUES (3, 'xiaoming ', 'male ')";
19. CmdInsert. ExecuteNonQuery ();
21. Conn. Close ();
22. }

You can use SQLite Database Browser to view data:

: Http://sourceforge.net/projects/sqlitebrowser/

 

Table created successfully.

Of course, this method is not efficient for data insertion. If the data volume is large, use the following method:

1. Static void Main (string [] args)
2. {
3. String dbPath = Environment. CurrentDirectory + "/test. db"; // specify the database path
5. Using (SQLiteConnection conn = new SQLiteConnection ("Data Source =" + dbPath) // create a connection
6. {
7. Conn. Open (); // Open the connection
8. Using (SQLiteTransaction tran = conn. BeginTransaction () // instantiate a transaction
9. {
10. For (int I = 0; I <100000; I ++)
11. {
12. SQLiteCommand cmd = new SQLiteCommand (conn); // instantiate an SQL command
13. Cmd. Transaction = tran;
14. Cmd. CommandText = "insert into student values (@ id, @ name, @ sex)"; // sets an SQL statement with a parameter
15. Cmd. Parameters. AddRange (new [] {// Add Parameters
16. New SQLiteParameter ("@ id", I ),
17. New SQLiteParameter ("@ name", "Chinese "),
18. New SQLiteParameter ("@ sex", "male ")
19. });
20. Cmd. ExecuteNonQuery (); // execute the query
21. }
22. Tran. Commit (); // submit
23. }
24. }
25. }

 

It takes about 5 seconds to insert such 100,000 data records.

 

Read data:

 

1. Static void Main (string [] args)
2. {
3. SQLiteConnection conn = null;
5. String dbPath = "Data Source =" + Environment. CurrentDirectory + "/test. db ";
6. Conn = new SQLiteConnection (dbPath); // create a database instance and specify the file location
7. Conn. Open (); // Open the database. It is automatically created if the file does not exist.
9. String SQL = "select * from student ";
10. SQLiteCommand cmdQ = new SQLiteCommand (SQL, conn );
12. SQLiteDataReader reader = cmdQ. ExecuteReader ();
14. While (reader. Read ())
15. {
16. Console. WriteLine (reader. GetInt32 (0) + "" + reader. GetString (1) + "" + reader. GetString (2 ));
17. }
18. Conn. Close ();
20. Console. ReadKey ();
21. }

 

Data is read successfully.