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 DB instance, specify 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 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); //Instantiate SQL command
26. Cmd. Transaction = Tran;
28. Cmd.commandtext = "INSERT into student values (@id, @name, @sex)"; //Set 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 DB instance, specify 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 .