1 public static class SQLiteHelper
2 {
3 private static string connectionString = string.Empty;
4
5 #region void SetConnectionString(string datasource, string password, int version = 3) 根据数据源、密码、版本号设置连接字符串
6 /// <summary>
7 /// 根据数据源、密码、版本号设置连接字符串。
8 /// </summary>
9 /// <param name="datasource">数据源。</param>
10 /// <param name="password">密码。</param>
11 /// <param name="version">版本号（缺省为3）。</param>
12 public static void SetConnectionString(string datasource, string password, int version = 3)
13 {
14 connectionString = string.Format("Data Source={0};Version={1};password={2};Pooling=true;FailIfMissing=false",
15 datasource, version, password);
16 }
17 #endregion
18
19 #region void CreateDB(string dbName) 创建一个数据库文件。如果存在同名数据库文件，则会覆盖。
20 /// <summary>
21 /// 创建一个数据库文件。如果存在同名数据库文件，则会覆盖。
22 /// </summary>
23 /// <param name="dbName">数据库文件名。为null或空串时不创建。</param>
24 /// <param name="password">（可选）数据库密码，默认为空。</param>
25 /// <exception cref="Exception"></exception>
26 public static void CreateDB(string dbName)
27 {
28 if (!string.IsNullOrEmpty(dbName))
29 {
30 try { SQLiteConnection.CreateFile(dbName); }
31 catch (Exception) { throw; }
32 }
33 }
34 #endregion
35
36 #region void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params SQLiteParameter[] parameters)准备操作命令参数
37 /// <summary>
38 /// 准备操作命令参数
39 /// </summary>
40 /// <param name="cmd">SQLiteCommand</param>
41 /// <param name="conn">SQLiteConnection</param>
42 /// <param name="cmdText">Sql命令文本</param>
43 private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params SQLiteParameter[] parameters)
44 {
45 if (conn.State != ConnectionState.Open)
46 conn.Open();
47 cmd.Parameters.Clear();
48 cmd.Connection = conn;
49 cmd.CommandText = cmdText;
50 cmd.CommandType = CommandType.Text;
51 cmd.CommandTimeout = 30;
52 if (parameters.Length != 0)
53 {
54 cmd.Parameters.AddRange(parameters);
55 }
56 }
57 #endregion
58
59 #region ExecuteNonQuery(string sql, params SQLiteParameter[] parameters) 对SQLite数据库执行增删改操作，返回受影响的行数。
60 /// <summary>
61 /// 对SQLite数据库执行增删改操作，返回受影响的行数。
62 /// </summary>
63 /// <param name="sql">要执行的增删改的SQL语句。</param>
64 /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param>
65 /// <returns></returns>
66 /// <exception cref="Exception"></exception>
67 public static int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters)
68 {
69 int affectedRows = 0;
70 using (SQLiteConnection connection = new SQLiteConnection(connectionString))
71 {
72 using (SQLiteCommand command = new SQLiteCommand())
73 {
74 try
75 {
76 PrepareCommand(command, connection, sql, parameters);
77 affectedRows = command.ExecuteNonQuery();
78 }
79 catch (Exception) { throw; }
80 }
81 }
82 return affectedRows;
83 }
84 #endregion
85
86 #region void ExecuteNonQueryBatch(List<KeyValuePair<string, SQLiteParameter[]>> list) 批量处理数据操作语句
87 /// <summary>
88 /// 批量处理数据操作语句。
89 /// </summary>
90 /// <param name="list">SQL语句集合。</param>
91 /// <exception cref="Exception"></exception>
92 public static void ExecuteNonQueryBatch(List<KeyValuePair<string, SQLiteParameter[]>> list)
93 {
94 using (SQLiteConnection conn = new SQLiteConnection(connectionString))
95 {
96 if (conn.State != ConnectionState.Open)
97 conn.Open();
98 using (SQLiteTransaction tran = conn.BeginTransaction())
99 {
100 using (SQLiteCommand cmd = new SQLiteCommand(conn))
101 {
102 try
103 {
104 foreach (var item in list)
105 {
106 cmd.CommandText = item.Key;
107 if (item.Value != null)
108 {
109 cmd.Parameters.AddRange(item.Value);
110 }
111 cmd.ExecuteNonQuery();
112 }
113 tran.Commit();
114 }
115 catch (Exception) { tran.Rollback(); throw; }
116 }
117 }
118 }
119 }
120 #endregion
121
122 #region object ExecuteScalar(string sql, params SQLiteParameter[] parameters) 执行查询语句，并返回第一个结果。
123 /// <summary>
124 /// 执行查询语句，并返回第一个结果。
125 /// </summary>
126 /// <param name="sql">查询语句。</param>
127 /// <returns>查询结果。</returns>
128 /// <exception cref="Exception"></exception>
129 public static object ExecuteScalar(string sql, params SQLiteParameter[] parameters)
130 {
131 using (SQLiteConnection connection = new SQLiteConnection(connectionString))
132 {
133 using (SQLiteCommand command = new SQLiteCommand())
134 {
135 try
136 {
137 PrepareCommand(command, connection, sql, parameters);
138
139 return command.ExecuteScalar();
140 }
141 catch (Exception) { throw; }
142 }
143 }
144 }
145 #endregion
146
147 #region DataTable ExecuteQuery(string sql, params SQLiteParameter[] parameters)执行一个查询语句，返回一个包含查询结果的DataTable。
148 /// <summary>
149 /// 执行一个查询语句，返回一个包含查询结果的DataTable。
150 /// </summary>
151 /// <param name="sql">要执行的查询语句。</param>
152 /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param>
153 /// <returns></returns>
154 /// <exception cref="Exception"></exception>
155 public static DataTable ExecuteQuery(string sql, params SQLiteParameter[] parameters)
156 {
157 using (SQLiteConnection connection = new SQLiteConnection(connectionString))
158 {
159 using (SQLiteCommand command = new SQLiteCommand())
160 {
161 PrepareCommand(command, connection, sql, parameters);
162
163 SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
164 DataTable data = new DataTable();
165 try { adapter.Fill(data); }
166 catch (Exception) { throw; }
167 return data;
168 }
169 }
170 }
171
172 #endregion
173
174 #region SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] parameters) 执行一个查询语句，返回一个关联的SQLiteDataReader实例。
175 /// <summary>
176 /// 执行一个查询语句，返回一个关联的SQLiteDataReader实例。
177 /// </summary>
178 /// <param name="sql">要执行的查询语句。</param>
179 /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param>
180 /// <returns></returns>
181 /// <exception cref="Exception"></exception>
182 public static SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] parameters)
183 {
184 using (SQLiteConnection connection = new SQLiteConnection(connectionString))
185 {
186 using (SQLiteCommand command = new SQLiteCommand())
187 {
188 try
189 {
190 PrepareCommand(command, connection, sql, parameters);
191 return command.ExecuteReader(CommandBehavior.CloseConnection);
192 }
193 catch (Exception) { throw; }
194 }
195 }
196 }
197
198 #endregion
199
200 #region DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, params SQLiteParameter[] parameters)分页查询
201 /// <summary>
202 /// 分页查询
203 /// </summary>
204 /// <param name="recordCount">总记录数</param>
205 /// <param name="pageIndex">页牵引</param>
206 /// <param name="pageSize">页大小</param>
207 /// <param name="cmdText">Sql命令文本</param>
208 /// <param name="countText">查询总记录数的Sql文本</param>
209 /// <param name="parameters">命令参数</param>
210 /// <returns>DataSet</returns>
211 public static DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, params SQLiteParameter[] parameters)
212 {
213 if (recordCount < 0)
214 recordCount = int.Parse(ExecuteScalar(countText).ToString());
215 var ds = new DataSet();
216 using (SQLiteConnection connection = new SQLiteConnection(connectionString))
217 {
218 using (SQLiteCommand command = new SQLiteCommand())
219 {
220 PrepareCommand(command, connection, cmdText, parameters);
221
222 SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
223 adapter.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "result");
224 }
225 return ds;
226 }
227 }
228 #endregion
229
230 #region DataTable GetSchema()查询数据库中的所有数据类型信息
231 /// <summary>
232 /// 查询数据库中的所有数据类型信息。
233 /// </summary>
234 /// <returns></returns>
235 /// <exception cref="Exception"></exception>
236 public static DataTable GetSchema()
237 {
238 using (SQLiteConnection connection = new SQLiteConnection(connectionString))
239 {
240 try
241 {
242 connection.Open();
243 return connection.GetSchema("TABLES");
244 }
245 catch (Exception) { throw; }
246 }
247 }
248 #endregion
249
250 #region int ResetDataBass() 重置自动增长列，如果执行了删除操作，自动增长列就会变的不连续，通过使用VACUUM方式重置
251 /// <summary>
252 /// 重置自动增长列，如果执行了删除操作，自动增长列就会变的不连续，通过使用VACUUM方式重置
253 /// </summary>
254 public static int ResetDataBass()
255 {
256 using (SQLiteConnection conn = new SQLiteConnection(connectionString))
257 {
258 using (SQLiteCommand command = new SQLiteCommand())
259 {
260 PrepareCommand(command, conn, "vacuum");
261
262 return command.ExecuteNonQuery();
263 }
264 }
265 }
266 #endregion
267 }
