網頁

2010年5月28日 星期五

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.SQLiteSystem.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:

沒有留言:

張貼留言