C# SQLite資料庫操作

標籤：

WinCE項目開發   VS2008

自己寫的SQLite資料庫管理類代碼如下：

SQLiteManager.cs 

using System.Data;using System.Data.SQLite;using System.IO;using System.Reflection;namespace TeachHMI{    public class SQLiteManager    {        public SQLiteManager(string DataBaseName,string DataTableName)        {            this.dbName = DataBaseName;            this.dtName = DataTableName;        }        //資料庫檔案名        string dbName;        //資料表名        string dtName;        string appPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);        //資料庫連接語句        string ConnStr;        //資料庫連接        public SQLiteConnection conn;        //SQLiteCommand執行個體，用於對SQLite發出指令        SQLiteCommand command;        //SQLiteDataAdapter執行個體，用於將資料庫資料填充至DataSet        SQLiteDataAdapter adapter;        //SQLiteDataReader執行個體，用於讀取資料表中資料        SQLiteDataReader reader;        /// <summary>        /// 資料庫連接        /// </summary>        public void InitializeSQLite()        {            this.ConnStr = "Data Source=" + appPath + "\\" + dbName + ";Pooling=true;FailIfMissing=false";            conn = new SQLiteConnection(ConnStr);            //開啟串連            conn.Open();        }        /// <summary>        /// 建立資料表        /// </summary>        /// <param name="CreateCommand"></param>        public void CreateTable(string CreateCommand)        {            command = new SQLiteCommand(conn);            command.CommandText = "CREATE TABLE IF NOT EXISTS " + dtName + CreateCommand;            command.ExecuteNonQuery();        }        /// <summary>        /// 執行SQL命令，返回受影響的行數        /// </summary>        /// <param name="Command">命令語句</param>        public void ExeNonQueryCmd(string Command)        {            command = new SQLiteCommand(conn);            command.CommandText = Command;            command.ExecuteNonQuery();        }        /// <summary>        /// 執行SQL命令，返回SQLiteDataReader        /// </summary>        /// <param name="Command"></param>        /// <returns></returns>        public SQLiteDataReader ExeReaderCmd(string Command)        {            command = new SQLiteCommand(conn);            command.CommandText = Command;            reader = command.ExecuteReader();  //傳回型別為SQLiteDataReader            return reader;        }        /// <summary>        /// 填充DataSet        /// </summary>        /// <param name="ds"></param>        public void DataFill(DataSet ds)        {            adapter = new SQLiteDataAdapter("SELECT * FROM " + dtName, conn);            adapter.Fill(ds, dtName);        }    }}

讀取資料表中某儲存格內資料：

public static SQLiteManager SQLlog;public static SQLiteDataReader Reader;int Num = int.Parse(dataGrid1[dataGrid1.CurrentRowIndex, 0].ToString());string SearchCmd = "SELECT * FROM InfoLog WHERE 序號=" + Num + "";Reader = SQLlog.ExeReaderCmd(SearchCmd);Reader.Read();listView1.Items[0].SubItems[1].Text = Reader.GetString(7);  //得到資料表中序號為 Num 的這條記錄中索引為 7 的列中資料listView1.Items[1].SubItems[1].Text = Reader.GetString(8);Reader.Close();

插入指令：INSERT INTO UserInfo VALUES 1,小李
更新指令：UPDATE UserInfo SET 使用者ID=1
刪除指令：DELETE FROM UserInfo WHERE 使用者ID=2,姓名=‘小李‘

C# SQLite資料庫操作