前言
我們?cè)陂_發(fā)應(yīng)用是經(jīng)常會(huì)需要用到一些數(shù)據(jù)的存儲(chǔ)，存儲(chǔ)的方式有多種，使用數(shù)據(jù)庫是一種比較受大家歡迎的方式。但是對(duì)于一些小型的應(yīng)用，如一些移動(dòng)app，通常的數(shù)據(jù)庫過于龐大，而輕便的sqlite則能解決這一問題。不但操作方便，而且只需要要一個(gè)文件即可，在這里我們來說一說使用c#語言操作sqlite數(shù)據(jù)庫。
一、nuget引入sqlite庫
在vs菜單：工具→nuget包管理器→管理解決方案的nuget程序包 打開nuget解決方案窗口。
搜索 sqlite，選擇官方的庫安裝到指定的項(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類庫
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
|
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í)行非查詢語句 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í)行非查詢語句-獨(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ù)文件是否存在
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
/// <summary>/// 檢查數(shù)據(jù)庫是否存在不存在創(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. 判斷表是否存在
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
/// <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; "; //此語句返回結(jié)果為0 int rowcount = cmd.executenonquery(); return true; } else if (tablecount > 1) { return false; } else { return true; } } } catch (exception ex) { return false; }} |
3. 查詢
|
1
2
3
4
5
6
7
8
9
10
11
|
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. 新增/修改
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
//存在更新，不存在插入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. 刪除
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
//刪除記錄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;} |
四、參考文章
- Create SQLite Database and table
- Writing to a SQLite Database in C#
- SQLite with VS2012 and .NET 4.5 — ANY CPU Build
- how to check if a table exists in C#
- SQLite auto increment issue
- Inserting a date to SQLite
- SQLite REPLACE Statement
- sqlite select with condition on date
- Using SQLite how do I index columns in a CREATE TABLE statement?
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對(duì)服務(wù)器之家的支持。
原文鏈接：https://renqiancheng.com/2018/11/C-SQLite-數(shù)據(jù)庫使用說明.html
