C# SQLite 教學 SQLite tutorial

SQLite 是一個功能強大的資料庫 open source，而且安裝空間小，

是我目前最常用的資料庫 lib ，輕巧方便攜帶性高，

美中不足的地方是目前 C# 版只有支援到 .Net 3.5 和 .Net 2.0


編譯環境

windows7 64bit

.Net Framework 3.5

visual studio 2012 express


1.下載 ADO.NET 2.0 Provider for SQLite 並安裝它

2.引用 System.Data.SQLite.dll

   32 bit : ../bin/System.Data.SQLite.dll

   64 bit: ../bin/x64/System.Data.SQLite.dll

3.Code

//  Created by vince 
//  Copyright (c) 2013年 vince. All rights reserved.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.IO;// for check file exist
using System.Data.SQLite;//for SQLite

namespace c_sharp_sqlite
{
    public partial class Form1 : Form
    {
        private SQLiteConnection sqlite_connect;
        private SQLiteCommand sqlite_cmd;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (!File.Exists(Application.StartupPath + @"\myData.db"))
            {
                SQLiteConnection.CreateFile("myData.db");
            }

            string name = textBox1.Text;
            string phone_number = textBox2.Text;

            sqlite_connect = new SQLiteConnection("Data source=myData.db");
            //建立資料庫連線

            sqlite_connect.Open();// Open
            sqlite_cmd = sqlite_connect.CreateCommand();//create command
            
            sqlite_cmd.CommandText = @"CREATE TABLE IF NOT EXISTS phone (num INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, phone_number TEXT)";
            //create table header
            //INTEGER PRIMARY KEY AUTOINCREMENT=>auto increase index
            sqlite_cmd.ExecuteNonQuery(); //using behind every write cmd

            sqlite_cmd.CommandText = "INSERT INTO phone VALUES (null, '" + name + "','" + phone_number + "');";
            sqlite_cmd.ExecuteNonQuery();//using behind every write cmd

            sqlite_connect.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            sqlite_connect = new SQLiteConnection("Data source=myData.db");
            //建立資料庫連線
            sqlite_connect.Open();// Open
            sqlite_cmd = sqlite_connect.CreateCommand();//create command

            sqlite_cmd.CommandText = "SELECT * FROM phone"; //select table

            SQLiteDataReader sqlite_datareader = sqlite_cmd.ExecuteReader();

            while (sqlite_datareader.Read()) //read every data
            {
                String name_load = sqlite_datareader["name"].ToString();
                String phone_number_load = sqlite_datareader["phone_number"].ToString();
                MessageBox.Show(name_load + ":" + phone_number_load);
            }
            sqlite_connect.Close();
        }
    }
}

5.執行結果

write:

read:

可以到release資料夾裡，會發現建立的myData.db跑出來了喔，

如果想要看它裡面的內容，可以參考這篇 : SQLite Database Browser 可以看資料庫的.db檔案