SQLite操作總結

標籤：

引用：

System.Data.SQLite.dll

System.Data.SqlClient.dll 

 

注意：：：：SQLite.Interop.076.dll 這樣的dll必須放在wince裝置上（“電腦\BT-W_Series\\\Windows“或者“程式根目錄“）

using System;using System.Data.SQLite;namespace Framework.DataAccess{    public class SQLiteMager    {        //資料庫連接        SQLiteConnection m_dbConnection;        static void Main(string[] args)        {            SQLiteMager p = new SQLiteMager();        }        public SQLiteMager()        {            createNewDatabase();            connectToDatabase();            createTable();            fillTable();            printHighscores();        }        //建立一個空的資料庫        public void createNewDatabase()        {            SQLiteConnection.CreateFile("MyDatabase.sqlite");        }        //建立一個串連到指定資料庫        public void connectToDatabase()        {            m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");                   m_dbConnection.Open();        }        //在指定資料庫中建立一個table        public void createTable()        {            string sql = "create table highscores (name varchar(20), score int)";            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);            command.ExecuteNonQuery();        }        //插入一些資料        public void fillTable()        {            string sql = "insert into highscores (name, score) values (‘Me‘, 3000)";            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);            command.ExecuteNonQuery();            sql = "insert into highscores (name, score) values (‘Myself‘, 6000)";            command = new SQLiteCommand(sql, m_dbConnection);            command.ExecuteNonQuery();            sql = "insert into highscores (name, score) values (‘And I‘, 9001)";            command = new SQLiteCommand(sql, m_dbConnection);            command.ExecuteNonQuery();        }        //使用sql查詢語句，並顯示結果        public void printHighscores()        {            string sql = "select * from highscores order by score desc";            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);            SQLiteDataReader reader = command.ExecuteReader();            while (reader.Read())                Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);            Console.ReadLine();        }    }}

　　

eg：：

#region SQLite 操作（檢查資料庫和表格是否存在，不存在則建立）

//,如果資料庫不存在，則建立資料庫 MyDatabase
if (!File.Exists("MyDatabase.sqlite"))   SQLiteConnection.CreateFile("MyDatabase.sqlite");

//建立資料庫MyDatabase的串連
SQLiteHelper _SQLiteHelper = new SQLiteHelper("Data Source=MyDatabase.sqlite;Version=3;DateTimeFormat=Ticks;");

//在MyDatabase資料庫 執行-建立表格
_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "CREATE TABLE IF NOT EXISTS highscores (name varchar(20), score int)");

////DataSet obj2 = _SQLiteHelper.ExecuteDataSet(CommandType.Text, "select * from highscores order by score desc");

////int dt2 = obj2.Tables[0].Rows.Count;

//////在MyDatabase資料庫 執行-插入資料
////_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values (‘Me‘, 3000)");
////_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values (‘Me2‘, 3002)");
////_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values (‘Me‘, 3000)");
////_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values (‘Me2‘, 3002)");
////_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values (‘Me‘, 3000)");
////_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values (‘Me2‘, 3002)");
////_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values (‘Me‘, 3000)");
////_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values (‘Me2‘, 3002)");
////_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values (‘Me‘, 3000)");
////_SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into highscores (name, score) values (‘Me2‘, 3002)");
//////在MyDatabase資料庫 執行-查詢
////DataSet obj = _SQLiteHelper.ExecuteDataSet(CommandType.Text, "select * from highscores order by score desc");

//// int dt= obj.Tables[0].Rows.Count;
//////SQLiteMager ddds = new SQLiteMager();

#endregion

SQLite操作總結