C#操作SQLite數(shù)據(jù)庫幫助類詳解

更新時間：2017年07月11日 09:31:40 作者：Only Version

這篇文章主要介紹了C#操作SQLite數(shù)據(jù)庫幫助類,詳細(xì)分析了C#針對sqlite數(shù)據(jù)庫的連接、查詢、分頁等各種常見操作的實現(xiàn)與封裝技巧,需要的朋友可以參考下

本文實例講述了C#操作SQLite數(shù)據(jù)庫幫助類。分享給大家供大家參考，具體如下：

最近有WPF做客戶端，需要離線操作存儲數(shù)據(jù)，在項目中考慮使用Sqlite嵌入式數(shù)據(jù)庫，在網(wǎng)上找了不少資料，最終整理出一個公共的幫助類。

Sqlite是一個非常小巧的數(shù)據(jù)庫，基本上具備關(guān)系型數(shù)據(jù)庫操作的大多數(shù)功能，Sql語法也大同小異。下面是我整理的幫助類代碼：

1.獲取 SQLiteConnection 對象，傳入數(shù)據(jù)庫有地址即可。

/// <summary>
/// 獲得連接對象
/// </summary>
/// <returns>SQLiteConnection</returns>
public static SQLiteConnection GetSQLiteConnection()
{
 //Sqlite數(shù)據(jù)庫地址
 string str = AppDomain.CurrentDomain.BaseDirectory;
 var con = new SQLiteConnection("Data Source=" + str + "DataBass\\InfoServiceDbB.db");
 return con;
}

2.準(zhǔn)備操作命令參數(shù)，構(gòu)造SQLiteCommand 對象：

/// <summary>
/// 準(zhǔn)備操作命令參數(shù)
/// </summary>
/// <param name="cmd">SQLiteCommand</param>
/// <param name="conn">SQLiteConnection</param>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">參數(shù)數(shù)組</param>
private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, Dictionary<String, String> data)
{
 if (conn.State != ConnectionState.Open)
  conn.Open();
 cmd.Parameters.Clear();
 cmd.Connection = conn;
 cmd.CommandText = cmdText;
 cmd.CommandType = CommandType.Text;
 cmd.CommandTimeout = 30;
 if (data!=null&&data.Count >= 1)
 {
  foreach (KeyValuePair<String, String> val in data)
  {
   cmd.Parameters.AddWithValue(val.Key, val.Value);
  }
 }
}

3.查詢，返回DataSet

/// <summary>
/// 查詢，返回DataSet
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">參數(shù)數(shù)組</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataset(string cmdText, Dictionary<string, string> data)
{
 var ds = new DataSet();
 using (SQLiteConnection connection = GetSQLiteConnection())
 {
  var command = new SQLiteCommand();
  PrepareCommand(command, connection, cmdText, data);
  var da = new SQLiteDataAdapter(command);
  da.Fill(ds);
 }
 return ds;
}

4.查詢，返回DataTable

/// <summary>
/// 查詢，返回DataTable
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">參數(shù)數(shù)組</param>
/// <returns>DataTable</returns>
public static DataTable ExecuteDataTable(string cmdText, Dictionary<string, string> data)
{
 var dt = new DataTable();
 using (SQLiteConnection connection = GetSQLiteConnection())
 {
  var command = new SQLiteCommand();
  PrepareCommand(command, connection, cmdText, data);
  SQLiteDataReader reader = command.ExecuteReader();
  dt.Load(reader);
 }
 return dt;
}

5.返回一行數(shù)據(jù) DataRow

/// <summary>
/// 返回一行數(shù)據(jù)
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">參數(shù)數(shù)組</param>
/// <returns>DataRow</returns>
public static DataRow ExecuteDataRow(string cmdText, Dictionary<string, string> data)
{
 DataSet ds = ExecuteDataset(cmdText, data);
 if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
  return ds.Tables[0].Rows[0];
 return null;
}

6.執(zhí)行數(shù)據(jù)庫操作

/// <summary>
/// 執(zhí)行數(shù)據(jù)庫操作
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">傳入的參數(shù)</param>
/// <returns>返回受影響的行數(shù)</returns>
public static int ExecuteNonQuery(string cmdText, Dictionary<string, string> data)
{
 using (SQLiteConnection connection = GetSQLiteConnection())
 {
  var command = new SQLiteCommand();
  PrepareCommand(command, connection, cmdText, data);
  return command.ExecuteNonQuery();
 }
}

7.返回SqlDataReader對象

/// <summary>
/// 返回SqlDataReader對象
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">傳入的參數(shù)</param>
/// <returns>SQLiteDataReader</returns>
public static SQLiteDataReader ExecuteReader(string cmdText, Dictionary<string, string> data)
{
 var command = new SQLiteCommand();
 SQLiteConnection connection = GetSQLiteConnection();
 try
 {
  PrepareCommand(command, connection, cmdText, data);
  SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
  return reader;
 }
 catch
 {
  connection.Close();
  command.Dispose();
  throw;
 }
}

8.返回結(jié)果集中的第一行第一列，忽略其他行或列

/// <summary>
/// 返回結(jié)果集中的第一行第一列，忽略其他行或列
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">傳入的參數(shù)</param>
/// <returns>object</returns>
public static object ExecuteScalar(string cmdText, Dictionary<string, string> data)
{
 using (SQLiteConnection connection = GetSQLiteConnection())
 {
  var cmd = new SQLiteCommand();
  PrepareCommand(cmd, connection, cmdText, data);
  return cmd.ExecuteScalar();
 }
}

9.分頁查詢

/// <summary>
/// 分頁查詢
/// </summary>
/// <param name="recordCount">總記錄數(shù)</param>
/// <param name="pageIndex">頁牽引</param>
/// <param name="pageSize">頁大小</param>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="countText">查詢總記錄數(shù)的Sql文本</param>
/// <param name="data">命令參數(shù)</param>
/// <returns>DataSet</returns>
public static DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, Dictionary<string, string> data)
{
 if (recordCount < 0)
  recordCount = int.Parse(ExecuteScalar(countText, data).ToString());
 var ds = new DataSet();
 using (SQLiteConnection connection = GetSQLiteConnection())
 {
  var command = new SQLiteCommand();
  PrepareCommand(command, connection, cmdText, data);
  var da = new SQLiteDataAdapter(command);
  da.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "result");
 }
 return ds;
}

10.重新組織數(shù)據(jù)庫

當(dāng)你從SQLite數(shù)據(jù)庫中刪除數(shù)據(jù)時，未用的磁盤空間將會加入一個內(nèi)部的“自由列表”中。

當(dāng)你下次插入數(shù)據(jù)時，這部分空間可以重用。磁盤空間不會丟失，但也不會返還給操作系統(tǒng)。

如果刪除了大量數(shù)據(jù)，而又想縮小數(shù)據(jù)庫文件占用的空間，執(zhí)行 VACUUM 命令。 VACUUM 將會從頭重新組織數(shù)據(jù)庫

你可以在你的程序中約定一個時間間隔執(zhí)行一次重新組織數(shù)據(jù)庫的操作，節(jié)約空間

public void ResetDataBass()
{
 using (SQLiteConnection conn = GetSQLiteConnection())
 {
  var cmd = new SQLiteCommand();
  if (conn.State != ConnectionState.Open)
   conn.Open();
  cmd.Parameters.Clear();
  cmd.Connection = conn;
  cmd.CommandText = "vacuum";
  cmd.CommandType = CommandType.Text;
  cmd.CommandTimeout = 30;
  cmd.ExecuteNonQuery();
 }
}

更多關(guān)于C#相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《C#常見數(shù)據(jù)庫操作技巧匯總》、《C#常見控件用法教程》、《C#窗體操作技巧匯總》、《C#數(shù)據(jù)結(jié)構(gòu)與算法教程》、《C#面向?qū)ο蟪绦蛟O(shè)計入門教程》及《C#程序設(shè)計之線程使用技巧總結(jié)》

希望本文所述對大家C#程序設(shè)計有所幫助。

您可能感興趣的文章: