前言
我們?cè)陂_(kāi)發(fā)應(yīng)用是經(jīng)常會(huì)需要用到一些數(shù)據(jù)的存儲(chǔ),存儲(chǔ)的方式有多種,使用數(shù)據(jù)庫(kù)是一種比較受大家歡迎的方式。但是對(duì)于一些小型的應(yīng)用,如一些移動(dòng)APP,通常的數(shù)據(jù)庫(kù)過(guò)于龐大,而輕便的SQLite則能解決這一問(wèn)題。不但操作方便,而且只需要要一個(gè)文件即可,在這里我們來(lái)說(shuō)一說(shuō)使用C#語(yǔ)言操作SQLite數(shù)據(jù)庫(kù)。
一、NuGet引入SQLite庫(kù)
在VS菜單:工具→NuGet包管理器→管理解決方案的NuGet程序包 打開(kāi)NuGet解決方案窗口。
搜索 sqlite,選擇官方的庫(kù)安裝到指定的項(xiàng)目中。:
提示:System.Data.SQLite 分為 x86 和 x64 版本,這里推薦使用NuGet自動(dòng)安裝。使用 Any CPU 編譯的時(shí)候,會(huì)自動(dòng)拷貝32位和64位 Interop DLL文件到子目錄中。程序運(yùn)行的時(shí)候會(huì)根據(jù)電腦的運(yùn)行環(huán)境自動(dòng)選擇合適的dll。
二、DBHelper類(lèi)庫(kù)
using System.Collections.Generic;using System.Data;using System.Data.SQLite;using System.Configuration;using System.Data.SqlClient;//using MySql.Data.MySqlClient;namespace ConsoleApp5{ public class DBHelper { private readonly static string connStr = ConfigurationManager.ConnectionStrings["Data Source=mesclient.sqlite;Version=3"].ConnectionString; //獲取 appsetting 設(shè)置的值 //private readonly static string appStr = ConfigurationManager.AppSettings["TestKey"]; //獲取 connection 對(duì)象 public static IDbConnection CreateConnection() { IDbConnection conn = new SQLiteConnection(connStr);//MySqlConnection //SqlConnection conn.Open(); return conn; } //執(zhí)行非查詢語(yǔ)句 public static int ExecuteNonQuery(IDbConnection conn, string sql, Dictionary<string, object> parameters) { using (IDbCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; foreach (KeyValuePair<string, object> keyValuePair in parameters) { IDbDataParameter parameter = cmd.CreateParameter(); parameter.ParameterName = keyValuePair.Key; parameter.Value = keyValuePair.Value; cmd.Parameters.Add(parameter); } return cmd.ExecuteNonQuery(); } } //執(zhí)行非查詢語(yǔ)句-獨(dú)立連接 public static int ExecuteNonQuery(string sql, Dictionary<string, object> parameters) { using (IDbConnection conn = CreateConnection()) { return ExecuteNonQuery(conn, sql, parameters); } } //查詢首行首列 public static object ExecuteScalar(IDbConnection conn, string sql, Dictionary<string, object> parameters) { using (IDbCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; foreach (KeyValuePair<string, object> keyValuePair in parameters) { IDbDataParameter parameter = cmd.CreateParameter(); parameter.ParameterName = keyValuePair.Key; parameter.Value = keyValuePair.Value; cmd.Parameters.Add(parameter); } return cmd.ExecuteScalar(); } } //查詢首行首列-獨(dú)立連接 public static object ExecuteScalar(string sql, Dictionary<string, object> parameters) { using (IDbConnection conn = CreateConnection()) { return ExecuteScalar(conn, sql, parameters); } } //查詢表 public static DataTable ExecuteQuery(IDbConnection conn, string sql, Dictionary<string, object> parameters) { DataTable dt = new DataTable(); using (IDbCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; foreach (KeyValuePair<string, object> keyValuePair in parameters) { IDbDataParameter parameter = cmd.CreateParameter(); parameter.ParameterName = keyValuePair.Key; parameter.Value = keyValuePair.Value; cmd.Parameters.Add(parameter); } using (IDataReader reader = cmd.ExecuteReader()) { dt.Load(reader); } } return dt; } //查詢表--獨(dú)立連接 public static DataTable ExecuteQuery(string sql, Dictionary<string, object> parameters) { using (IDbConnection conn = CreateConnection()) { return ExecuteQuery(conn, sql, parameters); } } }}
三、基本使用
1. 判斷數(shù)據(jù)文件是否存在
/// <summary>/// 檢查數(shù)據(jù)庫(kù)是否存在不存在創(chuàng)建/// </summary>/// <returns></returns>public static bool CheckDataBase(){ try { //判斷數(shù)據(jù)文件是否存在 bool dbExist = File.Exists("mesclient.sqlite"); if (!dbExist) { SQLiteConnection.CreateFile("mesclient.sqlite"); } return true; } catch (Exception) { return false; }}
2. 判斷表是否存在
/// <summary>/// 檢查數(shù)據(jù)表是否存在,不存在創(chuàng)建/// </summary>/// <returns></returns>public static bool CheckDataTable(string connStr){ try { using (SQLiteConnection conn = new SQLiteConnection(connStr)) using (SQLiteCommand cmd = conn.CreateCommand()) { conn.Open(); cmd.CommandText = "SELECT count(*) FROM sqlite_master WHERE type = 'table' AND name = 'serverinfo'"; object ob = cmd.ExecuteScalar(); long tableCount = Convert.ToInt64(ob); if (tableCount == 0) { //創(chuàng)建表 cmd.CommandText = @" BEGIN; create table serverinfo (Id INTEGER PRIMARY KEY AUTOINCREMENT,Name TEXT, Url text,DelayTime integer,UsageCounter INTEGER, Status integer,CreateTime DATETIME); CREATE UNIQUE INDEX idx_serverInfo ON serverinfo (Name); COMMIT; "; //此語(yǔ)句返回結(jié)果為0 int rowCount = cmd.ExecuteNonQuery(); return true; } else if (tableCount > 1) { return false; } else { return true; } } } catch (Exception ex) { return false; }}
3. 查詢
string sql = "SELECT * FROM serverinfo WHERE Name =@ServerName AND Url = @Url and date(CreateTime)=date(@Date);";Dictionary<string, object> parameters = new Dictionary<string, object>();parameters.Add("ServerName",endpointElement.Name);parameters.Add("Url", endpointElement.Address);parameters.Add("Date", DateTime.Now.ToString("yyyy-MM-dd"));DataTable dt=SqliteHelper.ExecuteQuery(connStr, sql, parameters);if (dt.Rows.Count>0){ UsageCounter = dt.Rows[0].Field<long>("UsageCounter"); GetTime = dt.Rows[0].Field<DateTime>("CreateTime");}
4. 新增/修改
//存在更新,不存在插入string updateSql = "REPLACE INTO serverinfo(Name,Url,DelayTime,UsageCounter, Status,CreateTime) VALUES(@Name,@Url,@DelayTime,@UsageCounter,@Status, @CreateTime)";Dictionary<string, object> ups = new Dictionary<string, object>();ups.Add("Name", name);ups.Add("Url", url);ups.Add("DelayTime", delayTime);ups.Add("UsageCounter", usageCounter);ups.Add("Status", status);ups.Add("CreateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));int count= SqliteHelper.ExecuteNonQuery(connStr, updateSql, ups);if (count>0){ return true;}else{ return false;}
5. 刪除
//刪除記錄string updateSql = "DELETE FROM serverinfo where content=@Content and flag=@Flag;";Dictionary<string, object> updateParameters = new Dictionary<string, object>();updateParameters.Add("Content", Content);updateParameters.Add("Flag", Flag);int count = SqliteHelper.ExecuteNonQuery(connStr, updateSql, updateParameters);if (count > 0){ return true;}else{ return false;}
四、參考文章
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,如果有疑問(wèn)大家可以留言交流,謝謝大家對(duì)VEVB武林網(wǎng)的支持。
|
新聞熱點(diǎn)
疑難解答
圖片精選
網(wǎng)友關(guān)注