233 //conn.Open(); 234 //cmd.CommandText = sql; 235 //if (parameters.Length != 0) 236 //{ 237 // cmd.Parameters.AddRange(parameters); 238 //} 239 return cmd.ExecuteScalar(); 240 } 241 catch (Exception) { throw; } 242 } 243 } 244 } 245 #endregion 查询 返回第一个 246 247 #region 查询 返回DT 248 /// <summary> 249 /// 执行一个查询语句，返回一个包含查询结果的DataTable。 250 /// </summary> 251 /// <param name="sql">要执行的查询语句。</param> 252 /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 253 /// <returns></returns> 254 /// <exception cref="Exception"></exception> 255 public static DataTable ExecuteQuery(string sql, params SQLiteParameter[] parameters) 256 { 257 using (SQLiteConnection connection = new SQLiteConnection(connectionString)) 258 { 259 using (SQLiteCommand command = new SQLiteCommand(sql, connection)) 260 { 261 PrepareCommand(connection, command, sql, parameters); 262 //if (parameters != null) 263 //{ 264 // if (parameters.Length != 0) 265 // { 266 // command.Parameters.AddRange(parameters); 267 // } 268 //} 269 270 SQLiteDataAdapter adapter = new SQLiteDataAdapter(command); 271 DataTable data = new DataTable(); 272 try { adapter.Fill(data); } 273 catch (Exception) { throw; } 274 return data; 275 } 276 } 277 } 278 #endregion 查询 返回DT 279 280 #region 查询 返回SQLiteDataReader 281 /// <summary> 282 /// 执行一个查询语句，返回一个关联的SQLiteDataReader实例。 283 /// </summary> 284 /// <param name="sql">要执行的查询语句。</param> 285 /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 286 /// <returns></returns> 287 /// <exception cref="Exception"></exception> 288 public static SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] parameters) 289 { 290 SQLiteConnection connection = new SQLiteConnection(connectionString); 291 SQLiteCommand command = new SQLiteCommand(sql, connection); 292 try 293 { 294 PrepareCommand(connection, command, sql, parameters); 295 //if (parameters.Length != 0) 296 //{ 297 // command.Parameters.AddRange(parameters); 298 //} 299 //connection.Open(); 300 return command.ExecuteReader(CommandBehavior.CloseConnection); 301 } 302 catch (Exception) { throw; } 303 } 304 #endregion 查询 返回SQLiteDataReader 305 306 #region 查询数据库中的所有数据类型信息 307 /// <summary> 308 /// 查询数据库中的所有数据类型信息。 309 /// </summary> 310 /// <returns></returns> 311 /// <exception cref="Exception"></exception> 312 public static DataTable GetSchema() 313 { 314 using (SQLiteConnection connection = new SQLiteConnection(connectionString)) 315 { 316 try 317 { 318 connection.Open(); 319 return connection.GetSchema("TABLES"); 320 } 321 catch (Exception) { throw; } 322 } 323 } 324 325 #endregion 查询数据库中的所有数据类型信息 326 327 #region 判断表是否存在 328 public static bool IsTableExist(string tableName) 329 { 330 bool isTableExist = true; 331 using (SQLiteConnection connection = new SQLiteConnection(connectionString)) 332 { 333 string sql = "SELECT name FROM sqlite_master WHERE type='table' AND name = '"; 334 sql += tableName; 335 sql += "'"; 336 using (SQLiteCommand command = new SQLiteCommand(sql, connection)) 337 { 338 PrepareCommand(connection, command, sql, null); 339 int count = command.ExecuteNonQuery(); 340 if (count <= 0) isTableExist = false; 341 } 342 } 343 return isTableExist; 344 345 } 346 #endregion 判断表是否存在 347 348 #endregion 数据库操作 349 350 #region 整理数据库 351 /// <summary> 352 /// 重新组织数据库 353 /// SQLite 的自带命令 VACUUM。用来重新整理整个数据库达到紧凑之用，比如把删除的彻底删掉等等 354 /// </summary> 355 public static void ResetDataBass() 356 { 357 using (SQLiteConnection conn = new SQLiteConnection(connectionString)) 358 { 359 var cmd = new SQLiteCommand(); 360 361 if (conn.State != ConnectionState.Open) 362 conn.Open(); 363 cmd.Parameters.Clear(); 364 cmd.Connection = conn; 365 cmd.CommandText = "vacuum"; 366 cmd.CommandType = CommandType.Text; 367 cmd.CommandTimeout = 30; 368 cmd.ExecuteNonQuery(); 369 } 370 } 371 #endregion 整理数据库 372 }