C#操作SQLite資料庫協助類——SQLiteHelper

標籤：c#   sqlite資料庫   sqlitehelper   

using System;using System.Collections.Generic;using System.Data.SQLite;using System.Data;namespace Com.ZCWL.Rock.Helper{public class SQLiteHelper{private static string connectionString = string.Empty;/// <summary>/// 根據資料來源、密碼、版本號碼設定連接字串。/// </summary>/// <param name="datasource">資料來源。</param>/// <param name="password">密碼。</param>/// <param name="version">版本號碼（預設為3）。</param>public static void SetConnectionString(string datasource, string password, int version = 3){connectionString = string.Format("Data Source={0};Version={1};password={2}", datasource, version, password);}/// <summary>/// 建立一個資料庫檔案。如果存在同名資料庫檔案，則會覆蓋。/// </summary>/// <param name="dbName">資料庫檔案名。為null或空串時不建立。</param>/// <param name="password">（可選）資料庫密碼，預設為空白。</param>/// <exception cref="Exception"></exception>public static void CreateDB(string dbName){if (!string.IsNullOrEmpty(dbName)){try { SQLiteConnection.CreateFile(dbName); }catch (Exception) { throw; }}}/// <summary> /// 對SQLite資料庫執行增刪改操作，返回受影響的行數。 /// </summary> /// <param name="sql">要執行的增刪改的SQL語句。</param> /// <param name="parameters">執行增刪改語句所需要的參數，參數必須以它們在SQL語句中的順序為準。</param> /// <returns></returns> /// <exception cref="Exception"></exception>public int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters){int affectedRows = 0;using (SQLiteConnection connection = new SQLiteConnection(connectionString)){using (SQLiteCommand command = new SQLiteCommand(connection)){try{connection.Open();command.CommandText = sql;if (parameters.Length != 0){command.Parameters.AddRange(parameters);}affectedRows = command.ExecuteNonQuery();}catch (Exception) { throw; }}}return affectedRows;}/// <summary>/// 批量處理資料動作陳述式。/// </summary>/// <param name="list">SQL語句集合。</param>/// <exception cref="Exception"></exception>public void ExecuteNonQueryBatch(List<KeyValuePair<string, SQLiteParameter[]>> list){using (SQLiteConnection conn = new SQLiteConnection(connectionString)){try { conn.Open(); }catch { throw; }using (SQLiteTransaction tran = conn.BeginTransaction()){using (SQLiteCommand cmd = new SQLiteCommand(conn)){try{foreach (var item in list){cmd.CommandText = item.Key;if (item.Value != null){cmd.Parameters.AddRange(item.Value);}cmd.ExecuteNonQuery();}tran.Commit();}catch (Exception) { tran.Rollback(); throw; }}}}}/// <summary>/// 執行查詢語句，並返回第一個結果。/// </summary>/// <param name="sql">查詢語句。</param>/// <returns>查詢結果。</returns>/// <exception cref="Exception"></exception>public object ExecuteScalar(string sql, params SQLiteParameter[] parameters){using (SQLiteConnection conn = new SQLiteConnection(connectionString)){using (SQLiteCommand cmd = new SQLiteCommand(conn)){try{conn.Open();cmd.CommandText = sql;if (parameters.Length != 0){cmd.Parameters.AddRange(parameters);}return cmd.ExecuteScalar();}catch (Exception) { throw; }}}}/// <summary> /// 執行一個查詢語句，返回一個包含查詢結果的DataTable。 /// </summary> /// <param name="sql">要執行的查詢語句。</param> /// <param name="parameters">執行SQL查詢語句所需要的參數，參數必須以它們在SQL語句中的順序為準。</param> /// <returns></returns> /// <exception cref="Exception"></exception>public DataTable ExecuteQuery(string sql, params SQLiteParameter[] parameters){using (SQLiteConnection connection = new SQLiteConnection(connectionString)){using (SQLiteCommand command = new SQLiteCommand(sql, connection)){if (parameters.Length != 0){command.Parameters.AddRange(parameters);}SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);DataTable data = new DataTable();try { adapter.Fill(data); }catch (Exception) { throw; }return data;}}}/// <summary> /// 執行一個查詢語句，返回一個關聯的SQLiteDataReader執行個體。 /// </summary> /// <param name="sql">要執行的查詢語句。</param> /// <param name="parameters">執行SQL查詢語句所需要的參數，參數必須以它們在SQL語句中的順序為準。</param> /// <returns></returns> /// <exception cref="Exception"></exception>public SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] parameters){SQLiteConnection connection = new SQLiteConnection(connectionString);SQLiteCommand command = new SQLiteCommand(sql, connection);try{if (parameters.Length != 0){command.Parameters.AddRange(parameters);}connection.Open();return command.ExecuteReader(CommandBehavior.CloseConnection);}catch (Exception) { throw; }}/// <summary> /// 查詢資料庫中的所有資料類型資訊。/// </summary> /// <returns></returns> /// <exception cref="Exception"></exception>public DataTable GetSchema(){using (SQLiteConnection connection = new SQLiteConnection(connectionString)){try{connection.Open();return connection.GetSchema("TABLES");}catch (Exception) { throw; }}} }}

     注意：在使用該類的時候，需要引用System.Data.SQLite程式集。

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。

C#操作SQLite資料庫協助類——SQLiteHelper