C# SQLite數據庫入門使用說明

2019-10-29 19:58:00

字體：大中小

來源：轉載

供稿：網友

前言

我們在開發應用是經常會需要用到一些數據的存儲，存儲的方式有多種，使用數據庫是一種比較受大家歡迎的方式。但是對于一些小型的應用，如一些移動APP，通常的數據庫過于龐大，而輕便的SQLite則能解決這一問題。不但操作方便，而且只需要要一個文件即可，在這里我們來說一說使用C#語言操作SQLite數據庫。

一、NuGet引入SQLite庫

在VS菜單：工具→NuGet包管理器→管理解決方案的NuGet程序包打開NuGet解決方案窗口。

搜索 sqlite，選擇官方的庫安裝到指定的項目中。：

C#,SQLite,數據庫

提示：System.Data.SQLite 分為 x86 和 x64 版本，這里推薦使用NuGet自動安裝。使用 Any CPU 編譯的時候，會自動拷貝32位和64位 Interop DLL文件到子目錄中。程序運行的時候會根據電腦的運行環境自動選擇合適的dll。

C#,SQLite,數據庫

二、DBHelper類庫

using System.Collections.Generic;using System.Data;using System.Data.SQLite;using System.Configuration;using System.Data.SqlClient;//using MySql.Data.MySqlClient;namespace ConsoleApp5{ public class DBHelper {  private readonly static string connStr = ConfigurationManager.ConnectionStrings["Data Source=mesclient.sqlite;Version=3"].ConnectionString;  //獲取 appsetting 設置的值  //private readonly static string appStr = ConfigurationManager.AppSettings["TestKey"];  //獲取 connection 對象  public static IDbConnection CreateConnection()  {   IDbConnection conn = new SQLiteConnection(connStr);//MySqlConnection //SqlConnection   conn.Open();   return conn;  }  //執行非查詢語句  public static int ExecuteNonQuery(IDbConnection conn, string sql, Dictionary<string, object> parameters)  {   using (IDbCommand cmd = conn.CreateCommand())   {    cmd.CommandText = sql;    foreach (KeyValuePair<string, object> keyValuePair in parameters)    {     IDbDataParameter parameter = cmd.CreateParameter();     parameter.ParameterName = keyValuePair.Key;     parameter.Value = keyValuePair.Value;     cmd.Parameters.Add(parameter);    }    return cmd.ExecuteNonQuery();   }  }  //執行非查詢語句-獨立連接  public static int ExecuteNonQuery(string sql, Dictionary<string, object> parameters)  {   using (IDbConnection conn = CreateConnection())   {    return ExecuteNonQuery(conn, sql, parameters);   }  }  //查詢首行首列  public static object ExecuteScalar(IDbConnection conn, string sql, Dictionary<string, object> parameters)  {   using (IDbCommand cmd = conn.CreateCommand())   {    cmd.CommandText = sql;    foreach (KeyValuePair<string, object> keyValuePair in parameters)    {     IDbDataParameter parameter = cmd.CreateParameter();     parameter.ParameterName = keyValuePair.Key;     parameter.Value = keyValuePair.Value;     cmd.Parameters.Add(parameter);    }    return cmd.ExecuteScalar();   }  }  //查詢首行首列-獨立連接  public static object ExecuteScalar(string sql, Dictionary<string, object> parameters)  {   using (IDbConnection conn = CreateConnection())   {    return ExecuteScalar(conn, sql, parameters);   }  }  //查詢表  public static DataTable ExecuteQuery(IDbConnection conn, string sql, Dictionary<string, object> parameters)  {   DataTable dt = new DataTable();   using (IDbCommand cmd = conn.CreateCommand())   {    cmd.CommandText = sql;    foreach (KeyValuePair<string, object> keyValuePair in parameters)    {     IDbDataParameter parameter = cmd.CreateParameter();     parameter.ParameterName = keyValuePair.Key;     parameter.Value = keyValuePair.Value;     cmd.Parameters.Add(parameter);    }    using (IDataReader reader = cmd.ExecuteReader())    {     dt.Load(reader);    }   }   return dt;  }  //查詢表--獨立連接  public static DataTable ExecuteQuery(string sql, Dictionary<string, object> parameters)  {   using (IDbConnection conn = CreateConnection())   {    return ExecuteQuery(conn, sql, parameters);   }  } }}

三、基本使用

1. 判斷數據文件是否存在

/// <summary>/// 檢查數據庫是否存在不存在創建/// </summary>/// <returns></returns>public static bool CheckDataBase(){ try {  //判斷數據文件是否存在  bool dbExist = File.Exists("mesclient.sqlite");  if (!dbExist)  {   SQLiteConnection.CreateFile("mesclient.sqlite");  }  return true; } catch (Exception) {  return false; }}

2. 判斷表是否存在

/// <summary>/// 檢查數據表是否存在，不存在創建/// </summary>/// <returns></returns>public static bool CheckDataTable(string connStr){ try {  using (SQLiteConnection conn = new SQLiteConnection(connStr))  using (SQLiteCommand cmd = conn.CreateCommand())  {   conn.Open();   cmd.CommandText = "SELECT count(*) FROM sqlite_master WHERE type = 'table' AND name = 'serverinfo'";   object ob = cmd.ExecuteScalar();   long tableCount = Convert.ToInt64(ob);   if (tableCount == 0)   {    //創建表    cmd.CommandText = @"   BEGIN;    create table serverinfo     (Id INTEGER PRIMARY KEY AUTOINCREMENT,Name TEXT,    Url text,DelayTime integer,UsageCounter INTEGER,     Status integer,CreateTime DATETIME);    CREATE UNIQUE INDEX idx_serverInfo ON serverinfo (Name);   COMMIT;   ";    //此語句返回結果為0    int rowCount = cmd.ExecuteNonQuery();    return true;   }   else if (tableCount > 1)   {    return false;   }   else   {    return true;   }  } } catch (Exception ex) {  return false; }}

3. 查詢

string sql = "SELECT * FROM serverinfo WHERE Name =@ServerName AND Url = @Url and date(CreateTime)=date(@Date);";Dictionary<string, object> parameters = new Dictionary<string, object>();parameters.Add("ServerName",endpointElement.Name);parameters.Add("Url", endpointElement.Address);parameters.Add("Date", DateTime.Now.ToString("yyyy-MM-dd"));DataTable dt=SqliteHelper.ExecuteQuery(connStr, sql, parameters);if (dt.Rows.Count>0){ UsageCounter = dt.Rows[0].Field<long>("UsageCounter"); GetTime = dt.Rows[0].Field<DateTime>("CreateTime");}

4. 新增/修改

//存在更新，不存在插入string updateSql = "REPLACE INTO serverinfo(Name,Url,DelayTime,UsageCounter, Status,CreateTime) VALUES(@Name,@Url,@DelayTime,@UsageCounter,@Status, @CreateTime)";Dictionary<string, object> ups = new Dictionary<string, object>();ups.Add("Name", name);ups.Add("Url", url);ups.Add("DelayTime", delayTime);ups.Add("UsageCounter", usageCounter);ups.Add("Status", status);ups.Add("CreateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));int count= SqliteHelper.ExecuteNonQuery(connStr, updateSql, ups);if (count>0){ return true;}else{ return false;}

5. 刪除

//刪除記錄string updateSql = "DELETE FROM serverinfo where content=@Content and flag=@Flag;";Dictionary<string, object> updateParameters = new Dictionary<string, object>();updateParameters.Add("Content", Content);updateParameters.Add("Flag", Flag);int count = SqliteHelper.ExecuteNonQuery(connStr, updateSql, updateParameters);if (count > 0){ return true;}else{ return false;}

四、參考文章