1 using System;
2 using System.Data;
3 using System.Data.SQLite;
4
5 namespace SqliteConsoleApp
6 {
7 /// <summary>
8 /// SQLite 助手
9 /// </summary>
10 public sealed class SQLiteHelper
11 {
12 private const string ConnectionStr = "Data Source=recipes.s3db";
13 private static SQLiteHelper _instance;
14 private static readonly object Locker = new object ();
15
16 private SQLiteHelper()
17 {
18 }
19
20 /// <summary>
21 /// 获取实例
22 /// </summary>
23 /// <returns></returns>
24 public static SQLiteHelper GetInstance()
25 {
26 if (_instance == null )
27 {
28 lock (Locker)
29 {
30 if (_instance == null )
31 {
32 _instance = new SQLiteHelper ();
33 }
34 }
35 }
36
37 return _instance;
38 }
39
40 /// <summary>
41 /// 获取数据表
42 /// </summary>
43 /// <param name="cmdText"> 需要执行的命令文本 </param>
44 /// <returns> 一个数据表集合 </returns>
45 public DataTable GetDataTable( string cmdText)
46 {
47 var dt = new DataTable();
48
49 try
50 {
51 using (var conn = new SQLiteConnection (ConnectionStr))
52 {
53 conn.Open();
54 var cmd = new SQLiteCommand(conn) {CommandText = cmdText};
55 using (var reader = cmd.ExecuteReader())
56 {
57 dt.Load(reader);
58 }
59 }
60 }
61 catch (Exception e)
62 {
63 throw new Exception(e.Message);
64 }
65
66 return dt;
67 }
68
69 /// <summary>
70 /// 执行非查询命令
71 /// </summary>
72 /// <param name="cmdText"> 需要执行的命令文本 </param>
73 /// <returns> 返回更新的行数 </returns>
74 public int ExecuteNonQuery( string cmdText)
75 {
76 using (var conn = new SQLiteConnection (ConnectionStr))
77 {
78 conn.Open();
79 var cmd = new SQLiteCommand(conn) {CommandText = cmdText};
80 var rowsUpdated = cmd.ExecuteNonQuery();
81
82 return rowsUpdated;
83 }
84 }
85
86 /// <summary>
87 /// 执行检索单项命令
88 /// </summary>
89 /// <param name="cmdText"> 需要执行的命令文本 </param>
90 /// <returns> 一个字符串 </returns>
91 public string ExecuteScalar( string cmdText)
92 {
93 using (var conn = new SQLiteConnection (ConnectionStr))
94 {
95 conn.Open();
96 var cmd = new SQLiteCommand(conn) {CommandText = cmdText};
97 var value = cmd.ExecuteScalar();
98
99 if (value != null )
100 {
101 return value.ToString();
102 }
103 }
104
105 return "" ;
106 }
107 }
108 }
