SQLite with Visual Studio 2005

採用 System.Data.SQLite(http://sqlite.phxsoftware.com/) -An open source ADO.NET provider for the SQLite database engine

請至 http://sourceforge.net/projects/sqlite-dotnet2/files/ 下載安裝檔案，我下載的是2010/04/18的版本SQLite-1.0.66.0-setup.exe(下載最新的就對了)。

1. 安裝，重點就是一直按 Next，安裝目錄用預設值，把你現有的開發環境勾起來(我的是Visual Studio 2005)，就會自動裝好了。

2. 測試

2.1 開啟一個新的 C# Console Application Project

2.2 專案開啟後，首先最重要的一步，就是 Add Reference，將System.Data.SQLite和System.Data.SQLite.Linq加進專案中。

2.3 主要程式碼如下

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SQLite;

using System.IO;

namespace SQLiteTest1

{

    class Program

    {

        public static SQLiteCommand cmd;

        public static void CreateTable()

        {

            string sql = "CREATE TABLE user(" +

                            "id INTEGER PRIMARY KEY, " +

                            "name text, " +

                            "sex int(1) NOT NULL DEFAULT 0 " +

                         ")";

            cmd.CommandText = sql;

            cmd.ExecuteNonQuery();

        }

        public static void DropTable()

        {

            string sql = "DROP TABLE user";

            cmd.CommandText = sql;

            cmd.ExecuteNonQuery();

        }

        public static void AddUser(string name, int sex)

        {

            string sql = " INSERT INTO user (name, sex) " +

                         " VALUES ('" + name + "', " + sex + ") ";

            cmd.CommandText = sql;

            cmd.ExecuteNonQuery();

        }

        public static void ListAllUser()

        {

            SQLiteDataReader sdr;

            string sql = "SELECT * from user";

            cmd.CommandText = sql;

            sdr = cmd.ExecuteReader();

            Console.WriteLine("ID\tNAME\tSEX");

            Console.WriteLine("------------------------------------------------");

            while (sdr.Read())

            {

                Console.WriteLine("{0}\t{1}\t{2}",

                    sdr["id"],

                    sdr["name"],

                    sdr["sex"]);

            }

        }

        static void Main(string[] args)

        {

            SQLiteConnection conn = null;

            try

            {

                string strDatabaseRoot = Directory.GetCurrentDirectory() + "\\SQLiteTest1.db";

                if (!File.Exists(strDatabaseRoot))

                {

                    SQLiteConnection.CreateFile(strDatabaseRoot);

                }

                conn = new SQLiteConnection("Data Source=" + strDatabaseRoot);

                conn.Open();

                cmd = conn.CreateCommand();

                DropTable();

                CreateTable();

                AddUser("Dick", 1);

                AddUser("Mary", 0);

                AddUser("GiGi", 0);

                ListAllUser();

            }

            catch (Exception e)

            {

                Console.WriteLine(e.Message);

            }

            finally

            {

                if (conn != null)

                {

                    conn.Close();

                }

            }

            Console.ReadLine();

        }

    }

}

Output: