简介
SQLite是一个轻量级、跨平台的关系型数据库，在小型项目中，方便，易用，同时支持多种开发语言，下面是我用C#语言对SQLite 的一个封装。
Winfrom界面如下：
代码还需要改进部分：
下面的代码我不觉得是完美的，读者可以自己去实现这些功能：
1.如果不想用多线程可以去掉UsingLock.cs ，在SQLiteHelper.cs中删除对应的引用即可。
2.创建数据库文件，可以从代码中分离成单独的方法。比如创建有密码的数据库文件。
3.在执行SQL语句时，有些语句执行成功，也不会有影响行数。比如，创建表，删除表，此时执行SQL语句返回的影响行数就为0。
4.只要SQL语句不报错就是执行成功的，在方法的返回值可以改为多个，比如用
Tuple<bool, string, int> 第一个参数 bool 代表执行结果，第二个参数 string 代表错误信息，第三个参数 int 代表影响的行数。
同样，也可以这样：
Tuple<bool, string, DataSet> 第一个参数 bool 代表执行结果，第二个参数 string 代表错误信息，第三个参数 DataSet 代表返回的表单数据。
主要代码
SQLiteHelper.cs
|
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
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
|
using System;using System.Collections.Generic;using System.Data;using System.Data.Common;using System.Data.SQLite;using System.IO;using System.Text; namespace MySQLiteHelper{ public class SQLiteHelper { #region 字段 /// <summary> /// 事务的基类 /// </summary> private DbTransaction DBtrans; /// <summary> /// 使用静态变量字典解决多线程实例本类，实现一个数据库对应一个clslock /// </summary> private static readonly Dictionary<string, ClsLock> RWL = new Dictionary<string, ClsLock>(); /// <summary> /// 数据库地址 /// </summary> private readonly string mdataFile; /// <summary> /// 数据库密码 /// </summary> private readonly string mPassWord; private readonly string LockName = null; /// <summary> /// 数据库连接定义 /// </summary> private SQLiteConnection mConn; #endregion #region 构造函数 /// <summary> /// 根据数据库地址初始化 /// </summary> /// <param name="dataFile">数据库地址</param> public SQLiteHelper(string dataFile) { this.mdataFile = dataFile ?? throw new ArgumentNullException("dataFile=null"); //this.mdataFile = AppDomain.CurrentDomain.BaseDirectory + dataFile; this.mdataFile = dataFile; if (!RWL.ContainsKey(dataFile)) { LockName = dataFile; RWL.Add(dataFile, new ClsLock()); } } /// <summary> /// 使用密码打开数据库 /// </summary> /// <param name="dataFile">数据库地址</param> /// <param name="PassWord">数据库密码</param> public SQLiteHelper(string dataFile, string PassWord) { this.mdataFile = dataFile ?? throw new ArgumentNullException("dataFile is null"); this.mPassWord = PassWord ?? throw new ArgumentNullException("PassWord is null"); //this.mdataFile = AppDomain.CurrentDomain.BaseDirectory + dataFile; this.mdataFile = dataFile; if (!RWL.ContainsKey(dataFile)) { LockName = dataFile; RWL.Add(dataFile, new ClsLock()); } } #endregion #region 打开/关闭 数据库 /// <summary> /// 打开 SQLiteManager 使用的数据库连接 /// </summary> public void Open() { if (string.IsNullOrWhiteSpace(mPassWord)) { mConn = OpenConnection(this.mdataFile); } else { mConn = OpenConnection(this.mdataFile, mPassWord); } Console.WriteLine("打开数据库成功"); } /// <summary> /// 关闭连接 /// </summary> public void Close() { if (this.mConn != null) { try { this.mConn.Close(); if (RWL.ContainsKey(LockName)) { RWL.Remove(LockName); } } catch { Console.WriteLine("关闭失败"); } } Console.WriteLine("关闭数据库成功"); } #endregion #region 事务 /// <summary> /// 开始事务 /// </summary> public void BeginTrain() { EnsureConnection(); DBtrans = mConn.BeginTransaction(); } /// <summary> /// 提交事务 /// </summary> public void DBCommit() { try { DBtrans.Commit(); } catch (Exception) { DBtrans.Rollback(); } } #endregion #region 工具 /// <summary> /// 打开一个SQLite数据库文件，如果文件不存在，则创建（无密码） /// </summary> /// <param name="dataFile"></param> /// <returns>SQLiteConnection 类</returns> private SQLiteConnection OpenConnection(string dataFile) { if (dataFile == null) { throw new ArgumentNullException("dataFiledataFile=null"); } if (!File.Exists(dataFile)) { SQLiteConnection.CreateFile(dataFile); } SQLiteConnection conn = new SQLiteConnection(); SQLiteConnectionStringBuilder conStr = new SQLiteConnectionStringBuilder { DataSource = dataFile }; conn.ConnectionString = conStr.ToString(); conn.Open(); return conn; } /// <summary> /// 打开一个SQLite数据库文件，如果文件不存在，则创建（有密码） /// </summary> /// <param name="dataFile"></param> /// <param name="Password"></param> /// <returns>SQLiteConnection 类</returns> private SQLiteConnection OpenConnection(string dataFile, string Password) { if (dataFile == null) { throw new ArgumentNullException("dataFile=null"); } if (!File.Exists(Convert.ToString(dataFile))) { SQLiteConnection.CreateFile(dataFile); } try { SQLiteConnection conn = new SQLiteConnection(); SQLiteConnectionStringBuilder conStr = new SQLiteConnectionStringBuilder { DataSource = dataFile, Password = Password }; conn.ConnectionString = conStr.ToString(); conn.Open(); return conn; } catch (Exception) { return null; } } /// <summary> /// 读取 或 设置 SQLiteManager 使用的数据库连接 /// </summary> public SQLiteConnection Connection { get { return mConn; } private set { mConn = value ?? throw new ArgumentNullException(); } } /// <summary> /// 确保数据库是连接状态 /// </summary> /// <exception cref="Exception"></exception> protected void EnsureConnection() { if (this.mConn == null) { throw new Exception("SQLiteManager.Connection=null"); } if (mConn.State != ConnectionState.Open) { mConn.Open(); } } /// <summary> /// 获取数据库文件的路径 /// </summary> /// <returns></returns> public string GetDataFile() { return this.mdataFile; } /// <summary> /// 判断表 table 是否存在 /// </summary> /// <param name="table"></param> /// <returns>存在返回true，否则返回false</returns> public bool TableExists(string table) { if (table == null) { throw new ArgumentNullException("table=null"); } EnsureConnection(); SQLiteDataReader reader = ExecuteReader("SELECT count(*) as c FROM sqlite_master WHERE type='table' AND name=@tableName ", new SQLiteParameter[] { new SQLiteParameter("tableName", table) }); if (reader == null) { return false; } reader.Read(); int c = reader.GetInt32(0); reader.Close(); reader.Dispose(); //return false; return c == 1; } /// <summary> /// VACUUM 命令（通过复制主数据库中的内容到一个临时数据库文件，然后清空主数据库，并从副本中重新载入原始的数据库文件） /// </summary> /// <returns></returns> public bool Vacuum() { try { using (SQLiteCommand Command = new SQLiteCommand("VACUUM", Connection)) { Command.ExecuteNonQuery(); } return true; } catch (System.Data.SQLite.SQLiteException) { return false; } } #endregion #region 执行SQL /// <summary> /// 执行SQL, 并返回 SQLiteDataReader 对象结果 /// </summary> /// <param name="sql"></param> /// <param name="paramArr">null 表示无参数</param> /// <returns></returns> public SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] paramArr) { if (sql == null) { throw new ArgumentNullException("sql=null"); } EnsureConnection(); using (RWL[LockName].Read()) { using (SQLiteCommand cmd = new SQLiteCommand(sql, Connection)) { if (paramArr != null) { cmd.Parameters.AddRange(paramArr); } try { SQLiteDataReader reader = cmd.ExecuteReader(); cmd.Parameters.Clear(); return reader; } catch (Exception) { return null; } } } } /// <summary> /// 执行查询，并返回dataset对象 /// </summary> /// <param name="sql">SQL查询语句</param> /// <param name="paramArr">参数数组</param> /// <returns></returns> public DataSet ExecuteDataSet(string sql, SQLiteParameter[] paramArr) { if (sql == null) { throw new ArgumentNullException("sql=null"); } this.EnsureConnection(); using (RWL[LockName].Read()) { using (SQLiteCommand cmd = new SQLiteCommand(sql, this.Connection)) { if (paramArr != null) { cmd.Parameters.AddRange(paramArr); } try { SQLiteDataAdapter da = new SQLiteDataAdapter(); DataSet ds = new DataSet(); da.SelectCommand = cmd; da.Fill(ds); cmd.Parameters.Clear(); da.Dispose(); return ds; } catch (Exception) { return null; } } } } /// <summary> /// 执行SQL查询，并返回dataset对象。 /// </summary> /// <param name="strTable">映射源表的名称</param> /// <param name="sql">SQL语句</param> /// <param name="paramArr">SQL参数数组</param> /// <returns></returns> public DataSet ExecuteDataSet(string strTable, string sql, SQLiteParameter[] paramArr) { if (sql == null) { throw new ArgumentNullException("sql=null"); } this.EnsureConnection(); using (RWL[LockName].Read()) { using (SQLiteCommand cmd = new SQLiteCommand(sql, this.Connection)) { if (paramArr != null) { cmd.Parameters.AddRange(paramArr); } try { SQLiteDataAdapter da = new SQLiteDataAdapter(); DataSet ds = new DataSet(); da.SelectCommand = cmd; da.Fill(ds, strTable); cmd.Parameters.Clear(); da.Dispose(); return ds; } catch (Exception) { return null; } } } } /// <summary> /// 执行SQL，返回受影响的行数，可用于执行表创建语句，paramArr == null 表示无参数 /// </summary> /// <param name="sql"></param> /// <returns></returns> public int ExecuteNonQuery(string sql, SQLiteParameter[] paramArr) { if (sql == null) { throw new ArgumentNullException("sql=null"); } this.EnsureConnection(); using (RWL[LockName].Read()) { try { using (SQLiteCommand cmd = new SQLiteCommand(sql, Connection)) { if (paramArr != null) { foreach (SQLiteParameter p in paramArr) { cmd.Parameters.Add(p); } } int c = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return c; } } catch (SQLiteException) { return 0; } } } /// <summary> /// 执行SQL，返回结果集第一行，如果结果集为空，那么返回空 List(List.Count=0)， /// rowWrapper = null 时，使用 WrapRowToDictionary /// </summary> /// <param name="sql"></param> /// <param name="paramArr"></param> /// <returns></returns> public object ExecuteScalar(string sql, SQLiteParameter[] paramArr) { if (sql == null) { throw new ArgumentNullException("sql=null"); } this.EnsureConnection(); using (RWL[LockName].Read()) { using (SQLiteCommand cmd = new SQLiteCommand(sql, Connection)) { if (paramArr != null) { cmd.Parameters.AddRange(paramArr); } try { object reader = cmd.ExecuteScalar(); cmd.Parameters.Clear(); cmd.Dispose(); return reader; } catch (Exception) { return null; } } } } /// <summary> /// 查询一行记录，无结果时返回 null，conditionCol = null 时将忽略条件，直接执行 select * from table /// </summary> /// <param name="table">表名</param> /// <param name="conditionCol"></param> /// <param name="conditionVal"></param> /// <returns></returns> public object QueryOne(string table, string conditionCol, object conditionVal) { if (table == null) { throw new ArgumentNullException("table=null"); } this.EnsureConnection(); string sql = "select * from " + table; if (conditionCol != null) { sql += " where " + conditionCol + "=@" + conditionCol; } object result = ExecuteScalar(sql, new SQLiteParameter[] { new SQLiteParameter(conditionCol, conditionVal) }); return result; } #endregion #region 增 删 改 /// <summary> /// 执行 insert into 语句 /// </summary> /// <param name="table"></param> /// <param name="entity"></param> /// <returns></returns> public int InsertData(string table, Dictionary<string, object> entity) { if (table == null) { throw new ArgumentNullException("table=null"); } this.EnsureConnection(); string sql = BuildInsert(table, entity); return this.ExecuteNonQuery(sql, BuildParamArray(entity)); } /// <summary> /// 执行 update 语句，注意：如果 where = null，那么 whereParams 也为 null， /// </summary> /// <param name="table">表名</param> /// <param name="entity">要修改的列名和列名的值</param> /// <param name="where">查找符合条件的列</param> /// <param name="whereParams">where条件中参数的值</param> /// <returns></returns> public int Update(string table, Dictionary<string, object> entity, string where, SQLiteParameter[] whereParams) { if (table == null) { throw new ArgumentNullException("table=null"); } this.EnsureConnection(); string sql = BuildUpdate(table, entity); SQLiteParameter[] parameter = BuildParamArray(entity); if (where != null) { sql += " where " + where; if (whereParams != null) { SQLiteParameter[] newArr = new SQLiteParameter[(parameter.Length + whereParams.Length)]; Array.Copy(parameter, newArr, parameter.Length); Array.Copy(whereParams, 0, newArr, parameter.Length, whereParams.Length); parameter = newArr; } } return this.ExecuteNonQuery(sql, parameter); } /// <summary> /// 执行 delete from table 语句，where不必包含'where'关键字，where = null 时将忽略 whereParams /// </summary> /// <param name="table"></param> /// <param name="where"></param> /// <param name="whereParams"></param> /// <returns></returns> public int Delete(string table, string where, SQLiteParameter[] whereParams) { if (table == null) { throw new ArgumentNullException("table=null"); } this.EnsureConnection(); string sql = "delete from " + table + " "; if (where != null) { sql += "where " + where; } return ExecuteNonQuery(sql, whereParams); } /// <summary> /// 将 Dictionary 类型数据 转换为 SQLiteParameter[] 类型 /// </summary> /// <param name="entity"></param> /// <returns></returns> private SQLiteParameter[] BuildParamArray(Dictionary<string, object> entity) { List<SQLiteParameter> list = new List<SQLiteParameter>(); foreach (string key in entity.Keys) { list.Add(new SQLiteParameter(key, entity[key])); } if (list.Count == 0) { return null; } return list.ToArray(); } /// <summary> /// 将 Dictionary 类型数据 转换为 插入数据 的 SQL语句 /// </summary> /// <param name="table">表名</param> /// <param name="entity">字典</param> /// <returns></returns> private string BuildInsert(string table, Dictionary<string, object> entity) { StringBuilder buf = new StringBuilder(); buf.Append("insert into ").Append(table); buf.Append(" ("); foreach (string key in entity.Keys) { buf.Append(key).Append(","); } buf.Remove(buf.Length - 1, 1); // 移除最后一个, buf.Append(") "); buf.Append("values("); foreach (string key in entity.Keys) { buf.Append("@").Append(key).Append(","); // 创建一个参数 } buf.Remove(buf.Length - 1, 1); buf.Append(") "); return buf.ToString(); } /// <summary> /// 将 Dictionary 类型数据 转换为 修改数据 的 SQL语句 /// </summary> /// <param name="table">表名</param> /// <param name="entity">字典</param> /// <returns></returns> private string BuildUpdate(string table, Dictionary<string, object> entity) { StringBuilder buf = new StringBuilder(); buf.Append("update ").Append(table).Append(" set "); foreach (string key in entity.Keys) { buf.Append(key).Append("=").Append("@").Append(key).Append(","); } buf.Remove(buf.Length - 1, 1); buf.Append(" "); return buf.ToString(); } #endregion }} |
UsingLock.cs
|
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
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
|
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Threading; namespace MySQLiteHelper{ /// <summary> /// 使用using代替lock操作的对象，可指定写入和读取锁定模式 /// </summary> public sealed class ClsLock { #region 内部类 /// <summary> /// 利用IDisposable的using语法糖方便的释放锁定操作内部类 /// </summary> private struct Lock : IDisposable { /// <summary> /// 读写锁对象 /// </summary> private readonly ReaderWriterLockSlim _Lock; /// <summary> /// 是否为写入模式 /// </summary> private bool _IsWrite; /// <summary> /// 利用IDisposable的using语法糖方便的释放锁定操作构造函数 /// </summary> /// <param name="rwl">读写锁</param> /// <param name="isWrite">写入模式为true，读取模式为false</param> public Lock(ReaderWriterLockSlim rwl, bool isWrite) { _Lock = rwl; _IsWrite = isWrite; } /// <summary> /// 释放对象时退出指定锁定模式 /// </summary> public void Dispose() { if (_IsWrite) { if (_Lock.IsWriteLockHeld) { _Lock.ExitWriteLock(); } } else { if (_Lock.IsReadLockHeld) { _Lock.ExitReadLock(); } } } } /// <summary> /// 空的可释放对象，免去了调用时需要判断是否为null的问题内部类 /// </summary> private class Disposable : IDisposable { /// <summary> /// 空的可释放对象 /// </summary> public static readonly Disposable Empty = new Disposable(); /// <summary> /// 空的释放方法 /// </summary> public void Dispose() { } } #endregion /// <summary> /// 读写锁 /// </summary> private readonly ReaderWriterLockSlim _LockSlim = new ReaderWriterLockSlim(); /// <summary> /// 使用using代替lock操作的对象，可指定写入和读取锁定模式构造函数 /// </summary> public ClsLock() { Enabled = true; } /// <summary> /// 是否启用，当该值为false时，Read()和Write()方法将返回 Disposable.Empty /// </summary> public bool Enabled { get; set; } /// <summary> /// 进入读取锁定模式，该模式下允许多个读操作同时进行， /// 退出读锁请将返回对象释放，建议使用using语块, /// Enabled为false时，返回Disposable.Empty， /// 在读取或写入锁定模式下重复执行，返回Disposable.Empty; /// </summary> public IDisposable Read() { if (Enabled == false || _LockSlim.IsReadLockHeld || _LockSlim.IsWriteLockHeld) { return Disposable.Empty; } else { _LockSlim.EnterReadLock(); return new Lock(_LockSlim, false); } } /// <summary> /// 进入写入锁定模式,该模式下只允许同时执行一个读操作， /// 退出读锁请将返回对象释放，建议使用using语块， /// Enabled为false时，返回Disposable.Empty， /// 在写入锁定模式下重复执行，返回Disposable.Empty /// </summary> /// <exception cref="NotImplementedException">读取模式下不能进入写入锁定状态</exception> public IDisposable Write() { if (Enabled == false || _LockSlim.IsWriteLockHeld) { return Disposable.Empty; } else if (_LockSlim.IsReadLockHeld) { throw new NotImplementedException("读取模式下不能进入写入锁定状态"); } else { _LockSlim.EnterWriteLock(); return new Lock(_LockSlim, true); } } } } |
Form1.cs
|
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
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
|
using MySQLiteHelper;using System;using System.Collections.Generic;using System.Data;using System.Data.SQLite;using System.Windows.Forms; namespace SQLiteDemo{ public partial class Form1 : Form { public Form1() { InitializeComponent(); } private SQLiteHelper SQLiteHelpers = null; private const string DBAddress = "D:\\SQLiteData\\test_record.db3"; private void Form1_Load(object sender, EventArgs e) { SQLiteHelpers = new SQLiteHelper(DBAddress,"123456"); } /// <summary> /// 打开数据库 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Button_OpenDB_Click(object sender, EventArgs e) { SQLiteHelpers.Open(); Label_DBOpenState.Text = "打开"; } /// <summary> /// 关闭数据库 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Button_CloseDB_Click(object sender, EventArgs e) { SQLiteHelpers.Close(); Label_DBOpenState.Text = "关闭"; } /// <summary> /// 查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Button_Query_Click(object sender, EventArgs e) { SQLiteParameter[] parameter = new SQLiteParameter[] { new SQLiteParameter("address", "济南") }; string sql = "SELECT * FROM student WHERE address = @address"; DataSet dataSet = SQLiteHelpers.ExecuteDataSet(sql, parameter); if (dataSet != null) { dataGridView1.DataSource = dataSet.Tables[0]; } } /// <summary> /// 插入数据 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Button_Add_Click(object sender, EventArgs e) { Dictionary<string, object> dic = new Dictionary<string, object>(); dic.Add("ID", 6); dic.Add("name", "王二麻子"); dic.Add("age", 44); dic.Add("address", "陕西"); int result = SQLiteHelpers.InsertData("student", dic); Console.WriteLine("插入结果，受影响的行数：" + result); } /// <summary> /// 修改数据 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Button_Modify_Click(object sender, EventArgs e) { Dictionary<string, object> dic = new Dictionary<string, object>(); //将列名 name 的值改为 “猴子” dic.Add("name", "猴子"); //将列名 address 的值改为 花果山 dic.Add("address", "花果山"); //where条件 string where = "ID = @ID AND age = @Age"; //where条件中对应的参数 SQLiteParameter[] parameter = new SQLiteParameter[] { new SQLiteParameter("ID", 4), new SQLiteParameter("Age",23) }; int result = SQLiteHelpers.Update("student", dic, where, parameter); Console.WriteLine("修改结果，受影响的行数：" + result); } /// <summary> /// 删除数据 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Button_Delete_Click(object sender, EventArgs e) { //where条件 string where = "ID = @ID"; //where条件中对应的参数 SQLiteParameter[] parameter = new SQLiteParameter[] { new SQLiteParameter("ID", 6), }; int result = SQLiteHelpers.Delete("student", where, parameter); Console.WriteLine("删除结果，受影响的行数：" + result); } /// <summary> /// 判断表是否存在 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Button_TableExists_Click(object sender, EventArgs e) { string title = "dddd"; bool result = SQLiteHelpers.TableExists(title); Console.WriteLine(string.Format("{0} 表是否存在，结果：{1}", title, result)); } //输出各表中的数据 //public static void PrintValues(DataSet ds) //{ // foreach (DataTable table in ds.Tables) // { // Console.WriteLine("表名称：" + table.TableName); // foreach (DataRow row in table.Rows) // { // foreach (DataColumn column in table.Columns) // { // Console.Write(row[column] + ""); // } // Console.WriteLine(); // } // } //} }} |
以上就是C#操作SQLite实现数据的增删改查的详细内容，更多关于C# SQLite增删改查的资料请关注服务器之家其它相关文章！
原文链接：https://blog.csdn.net/qq_38693757/article/details/122582071
