C # using System.Data.SQLite to manipulate SQLite

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

Get System.Data.SQLite.dll added to the project reference;

Build tables, insert operations

C # code

2. static void Main (string[] args)
4. {
6. Sqliteconnection conn = null;
10. string dbPath = " Data Source =" + environment.currentdirectory + "/test.db";
12. conn = new Sqliteconnection (DbPath); Create a DB instance, specify the file location
14. Conn. Open (); //Open database, automatically created if file does not exist
18. String sql = "CREATE TABLE IF not EXISTS student (ID integer, name varchar (), sex varchar (2));"; //Build Table statement
20. Sqlitecommand cmdcreatetable = new Sqlitecommand (SQL, conn);
22. Cmdcreatetable.executenonquery (); //If the table does not exist, create a data table
26. Sqlitecommand Cmdinsert = new Sqlitecommand (conn);
28. Cmdinsert.commandtext = "INSERT into student VALUES (1, ' Little Red ', ' male ')"; Insert several data
30. Cmdinsert.executenonquery ();
32. Cmdinsert.commandtext = "INSERT into student VALUES (2, ' Xiao Li ', ' female ')";
34. Cmdinsert.executenonquery ();
36. Cmdinsert.commandtext = "INSERT into student VALUES (3, ' xiaoming ', ' Male ')";
38. Cmdinsert.executenonquery ();
42. Conn. Close ();
44. }

You can use SQLite Database browser to view data:

: http://sourceforge.net/projects/sqlitebrowser/

The table was built successfully.

Of course, this method of inserting data is not efficient, the amount of data is large, use the following method:

C # code

2. static void Main (string[] args)
4. {
6. string dbPath = Environment.currentdirectory + "/test.db"; Specify the database path
10. using (sqliteconnection conn = new Sqliteconnection ("Data Source =" + DbPath)//Create connection
12. {
14. Conn. Open (); //Open Connection
16. using (sqlitetransaction TRAN = conn. BeginTransaction ())//instantiation of a transaction
18. {
20. For (int i = 0; i < 100000; i++)
22. {
24. Sqlitecommand cmd = new Sqlitecommand (conn); Instantiating SQL commands
26. Cmd. Transaction = Tran;
28. Cmd.commandtext = "INSERT into student values (@id, @name, @sex)"; Setting the SQL statement with parameters
30. Cmd. Parameters.addrange (new[] {//Add parameter
32. New Sqliteparameter ("@id", i),
34. New Sqliteparameter ("@name", "Chinese"),
36. New Sqliteparameter ("@sex", "male")
38. });
40. Cmd. ExecuteNonQuery (); //Execute Query
42. }
44. Tran.commit (); //Submit
46. }
48. }
50. }

It only takes about 5 seconds to insert such a 100,000-piece data.

Read data:

C # code

2. static void Main (string[] args)
4. {
6. Sqliteconnection conn = null;
10. string dbPath = " Data Source =" + environment.currentdirectory + "/test.db";
12. conn = new Sqliteconnection (DbPath); Create a DB instance, specify the file location
14. Conn. Open (); //Open database, automatically created if file does not exist
18. String sql = "SELECT * from student";
20. Sqlitecommand CMDQ = new Sqlitecommand (SQL, conn);
24. Sqlitedatareader reader = Cmdq.executereader ();
28. While (reader. Read ())
30. {
32. Console.WriteLine (reader. GetInt32 (0) + "" + Reader. GetString (1) + "" + Reader.  GetString (2));
34. }
36. Conn. Close ();
40. Console.readkey ();
42. }

Data read succeeded.