SQLite之C#版 System.Data.SQLite使用方法
SQLite簡(jiǎn)介
SQLite,是一款輕型的關(guān)系型數(shù)據(jù)庫(kù)。它的設(shè)計(jì)目標(biāo)是嵌入式。
它能夠支持Windows/Linux/Unix等等主流的操作系統(tǒng),同時(shí)能夠跟很多程序語(yǔ)言相結(jié)合,比如 C++、C#、Object-C、PHP、Java等。
我是做手游開發(fā)的,在手機(jī)上使用SQLite 存儲(chǔ)數(shù)據(jù)是很方便的。
System.Data.SqLite實(shí)踐前期準(zhǔn)備
- System.Data.SQLite 庫(kù)下載,用于C#操作SQLite的dll文件。下載地址:http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
- SQLite可視化工具下載,用于查看SQLite庫(kù)數(shù)據(jù)表數(shù)據(jù)。下載地址:http://www.sqliteexpert.com/download.html
Tips:
1.根據(jù)自己的電腦是64bit或者32bit 以及使用的.NET Freamwork版本下載相應(yīng)的的System.Data.SQLite。 下載解壓文件如:
安裝在指定目錄后生成多個(gè)dll文件:
本文測(cè)試用的只有 System.Data.SQLite.dll 。對(duì)于SQLite庫(kù)數(shù)據(jù)的增刪改查已經(jīng)足夠用了。
2.SQLite數(shù)據(jù)庫(kù)文件以.db為后綴。安裝SQLite可視化工具后如圖:
mydb是我測(cè)試用的數(shù)據(jù)庫(kù)。
實(shí)踐
對(duì)SQLite的增、刪、改、查。
測(cè)試環(huán)境:VS2015 語(yǔ)言:C#
創(chuàng)建控制臺(tái)新工程,引入System.Data.SQLite.dll 文件。
1.編寫SQLite 操作類, 對(duì)SQLite數(shù)據(jù)操作進(jìn)行封裝為 SqLiteHelper,代碼如下:
/// <summary> /// SQLite 操作類 /// </summary> class SqLiteHelper { /// <summary> /// 數(shù)據(jù)庫(kù)連接定義 /// </summary> private SQLiteConnection dbConnection; /// <summary> /// SQL命令定義 /// </summary> private SQLiteCommand dbCommand; /// <summary> /// 數(shù)據(jù)讀取定義 /// </summary> private SQLiteDataReader dataReader; /// <summary> /// 構(gòu)造函數(shù) /// </summary> /// <param name="connectionString">連接SQLite庫(kù)字符串</param> public SqLiteHelper(string connectionString) { try { dbConnection = new SQLiteConnection(connectionString); dbConnection.Open(); }catch(Exception e) { Log(e.ToString()); } } /// <summary> /// 執(zhí)行SQL命令 /// </summary> /// <returns>The query.</returns> /// <param name="queryString">SQL命令字符串</param> public SQLiteDataReader ExecuteQuery(string queryString) { try { dbCommand = dbConnection.CreateCommand(); dbCommand.CommandText = queryString; dataReader = dbCommand.ExecuteReader(); }catch(Exception e) { Log(e.Message); } return dataReader; } /// <summary> /// 關(guān)閉數(shù)據(jù)庫(kù)連接 /// </summary> public void CloseConnection() { //銷毀Commend if(dbCommand !=null) { dbCommand.Cancel(); } dbCommand = null; //銷毀Reader if(dataReader != null) { dataReader.Close(); } dataReader = null; //銷毀Connection if(dbConnection != null) { dbConnection.Close(); } dbConnection = null; } /// <summary> /// 讀取整張數(shù)據(jù)表 /// </summary> /// <returns>The full table.</returns> /// <param name="tableName">數(shù)據(jù)表名稱</param> public SQLiteDataReader ReadFullTable(string tableName) { string queryString = "SELECT * FROM " + tableName; return ExecuteQuery(queryString); } /// <summary> /// 向指定數(shù)據(jù)表中插入數(shù)據(jù) /// </summary> /// <returns>The values.</returns> /// <param name="tableName">數(shù)據(jù)表名稱</param> /// <param name="values">插入的數(shù)值</param> public SQLiteDataReader InsertValues(string tableName, string[] values) { //獲取數(shù)據(jù)表中字段數(shù)目 int fieldCount = ReadFullTable(tableName).FieldCount; //當(dāng)插入的數(shù)據(jù)長(zhǎng)度不等于字段數(shù)目時(shí)引發(fā)異常 if (values.Length != fieldCount) { throw new SQLiteException("values.Length!=fieldCount"); } string queryString = "INSERT INTO " + tableName + " VALUES (" + "'"+ values[0]+"'"; for (int i = 1; i < values.Length; i++) { queryString += ", " + "'"+values[i] + "'"; } queryString += " )"; return ExecuteQuery(queryString); } /// <summary> /// 更新指定數(shù)據(jù)表內(nèi)的數(shù)據(jù) /// </summary> /// <returns>The values.</returns> /// <param name="tableName">數(shù)據(jù)表名稱</param> /// <param name="colNames">字段名</param> /// <param name="colValues">字段名對(duì)應(yīng)的數(shù)據(jù)</param> /// <param name="key">關(guān)鍵字</param> /// <param name="value">關(guān)鍵字對(duì)應(yīng)的值</param> /// <param name="operation">運(yùn)算符:=,<,>,...,默認(rèn)“=”</param> public SQLiteDataReader UpdateValues(string tableName, string[] colNames, string[] colValues, string key, string value, string operation="=") { //當(dāng)字段名稱和字段數(shù)值不對(duì)應(yīng)時(shí)引發(fā)異常 if (colNames.Length != colValues.Length) { throw new SQLiteException("colNames.Length!=colValues.Length"); } string queryString = "UPDATE " + tableName + " SET " + colNames[0] + "=" + "'" + colValues[0] + "'"; for (int i = 1; i < colValues.Length; i++) { queryString += ", " + colNames[i] + "=" + "'" + colValues[i] + "'"; } queryString += " WHERE " + key + operation + "'" + value + "'"; return ExecuteQuery(queryString); } /// <summary> /// 刪除指定數(shù)據(jù)表內(nèi)的數(shù)據(jù) /// </summary> /// <returns>The values.</returns> /// <param name="tableName">數(shù)據(jù)表名稱</param> /// <param name="colNames">字段名</param> /// <param name="colValues">字段名對(duì)應(yīng)的數(shù)據(jù)</param> public SQLiteDataReader DeleteValuesOR(string tableName, string[] colNames, string[] colValues, string[] operations) { //當(dāng)字段名稱和字段數(shù)值不對(duì)應(yīng)時(shí)引發(fā)異常 if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length) { throw new SQLiteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length"); } string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'"; for (int i = 1; i < colValues.Length; i++) { queryString += "OR " + colNames[i] + operations[0] + "'" + colValues[i] + "'"; } return ExecuteQuery(queryString); } /// <summary> /// 刪除指定數(shù)據(jù)表內(nèi)的數(shù)據(jù) /// </summary> /// <returns>The values.</returns> /// <param name="tableName">數(shù)據(jù)表名稱</param> /// <param name="colNames">字段名</param> /// <param name="colValues">字段名對(duì)應(yīng)的數(shù)據(jù)</param> public SQLiteDataReader DeleteValuesAND(string tableName, string[] colNames, string[] colValues, string[] operations) { //當(dāng)字段名稱和字段數(shù)值不對(duì)應(yīng)時(shí)引發(fā)異常 if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length) { throw new SQLiteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length"); } string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'"; for (int i = 1; i < colValues.Length; i++) { queryString += " AND " + colNames[i] + operations[i] + "'" + colValues[i] + "'"; } return ExecuteQuery(queryString); } /// <summary> /// 創(chuàng)建數(shù)據(jù)表 /// </summary> + /// <returns>The table.</returns> /// <param name="tableName">數(shù)據(jù)表名</param> /// <param name="colNames">字段名</param> /// <param name="colTypes">字段名類型</param> public SQLiteDataReader CreateTable(string tableName, string[] colNames, string[] colTypes) { string queryString = "CREATE TABLE IF NOT EXISTS " + tableName + "( " + colNames[0] + " " + colTypes[0]; for (int i = 1; i < colNames.Length; i++) { queryString += ", " + colNames[i] + " " + colTypes[i]; } queryString += " ) "; return ExecuteQuery(queryString); } /// <summary> /// Reads the table. /// </summary> /// <returns>The table.</returns> /// <param name="tableName">Table name.</param> /// <param name="items">Items.</param> /// <param name="colNames">Col names.</param> /// <param name="operations">Operations.</param> /// <param name="colValues">Col values.</param> public SQLiteDataReader ReadTable(string tableName, string[] items, string[] colNames, string[] operations, string[] colValues) { string queryString = "SELECT " + items[0]; for (int i = 1; i < items.Length; i++) { queryString += ", " + items[i]; } queryString += " FROM " + tableName + " WHERE " + colNames[0] + " " + operations[0] + " " + colValues[0]; for (int i = 0; i < colNames.Length; i++) { queryString += " AND " + colNames[i] + " " + operations[i] + " " + colValues[0] + " "; } return ExecuteQuery(queryString); } /// <summary> /// 本類log /// </summary> /// <param name="s"></param> static void Log(string s) { Console.WriteLine("class SqLiteHelper:::" + s); } }
Tips:1.別忘了引入:using System.Data.SQLite;
2.引入System.Data.SQLite.dll的同時(shí),需要將 System.Data.SQLite.dll.config 文件放在同級(jí)目錄下。否則加載dll文件時(shí)報(bào)錯(cuò)。
2.在工程主函數(shù)Main里面添加測(cè)試代碼
class Program { private static SqLiteHelper sql; static void Main(string[] args) { sql = new SqLiteHelper("data source=mydb.db"); //創(chuàng)建名為table1的數(shù)據(jù)表 sql.CreateTable("table1", new string[] { "ID", "Name", "Age", "Email" }, new string[] { "INTEGER", "TEXT", "INTEGER", "TEXT" }); //插入兩條數(shù)據(jù) sql.InsertValues("table1",new string[]{"1","張三","22","Zhang@163.com" }); sql.InsertValues("table1", new string[] { "2", "李四", "25", "Li4@163.com" }); //更新數(shù)據(jù),將Name="張三"的記錄中的Name改為"Zhang3" sql.UpdateValues("table1", new string[] { "Name" }, new string[] { "ZhangSan" }, "Name", "Zhang3"); //刪除Name="張三"且Age=26的記錄,DeleteValuesOR方法類似 sql.DeleteValuesAND("table1", new string[] { "Name", "Age" }, new string[] { "張三", "22" }, new string[] { "=", "=" }); //讀取整張表 SQLiteDataReader reader = sql.ReadFullTable("table1"); while (reader.Read()) { //讀取ID Log(""+reader.GetInt32(reader.GetOrdinal("ID"))); //讀取Name Log(""+reader.GetString(reader.GetOrdinal("Name"))); //讀取Age Log(""+reader.GetInt32(reader.GetOrdinal("Age"))); //讀取Email Log(reader.GetString(reader.GetOrdinal("Email"))); } while (true) { Console.ReadLine(); } } static void Log(string s) { Console.WriteLine(""+s); } }
Tips:1.別忘了引入:using System.Data.SQLite;
vs2015有NuGet,vs2013默認(rèn)沒有這個(gè)插件。
System.Data.SQLite 可以在VS中直接通過(guò)NuGet獲取到。
到此這篇關(guān)于SQLite之C#版 System.Data.SQLite使用方法的文章就介紹到這了,更多相關(guān)System.Data.SQLite內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
C#使用正則表達(dá)式過(guò)濾html標(biāo)簽
最近在開發(fā)一個(gè)項(xiàng)目,其中有需求要求我們把一段html轉(zhuǎn)換為一般文本返回,使用正則表達(dá)式是明智的選擇,下面小編給介紹下C#使用正則表達(dá)式過(guò)濾html標(biāo)簽,需要的朋友參考下2016-08-08c#和javascript函數(shù)相互調(diào)用示例分享
在webBrowser使用過(guò)程中為了C#和JS通訊,webBrowser必須設(shè)置ObjectForScripting的屬性,它是一個(gè)object,這個(gè)object可以提供給webBrowser控件載入的網(wǎng)頁(yè)上的script訪問(wèn)2014-01-01C# WebApi+Webrtc局域網(wǎng)音視頻通話實(shí)例
這篇文章主要為大家詳細(xì)介紹了C# WebApi+Webrtc局域網(wǎng)音視頻通話實(shí)例,文中示例代碼介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2021-07-07C# lambda表達(dá)式原理定義及實(shí)例詳解
這篇文章主要介紹了C# lambda表達(dá)式原理定義及實(shí)例詳解,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-07-07c#讀取excel數(shù)據(jù)的兩種方法實(shí)現(xiàn)
這篇文章主要介紹了c#讀取excel數(shù)據(jù)的兩種方法實(shí)現(xiàn),文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-12-12