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 }
SQLite-C#-帮助类
标签：tables 执行 cep throw region 密码 dap ack rem
