[C#] Helper 封裝系列 -- SQLiteHelper

  1 using System;
  2 using System.Data;
  3 using System.Data.SQLite;
  4 
  5 namespace SqliteConsoleApp
  6 {
  7     /// <summary>
  8     /// SQLite 助手
  9     /// </summary>
 10     public sealed class SQLiteHelper
 11     {
 12         private const string ConnectionStr = "Data Source=recipes.s3db";
 13         private static SQLiteHelper _instance;
 14         private static readonly object Locker = new object ();
 15 
 16         private SQLiteHelper()
 17         {
 18         }
 19 
 20         /// <summary>
 21         /// 獲取實例
 22         /// </summary>
 23         /// <returns></returns>
 24         public static SQLiteHelper GetInstance()
 25         {
 26             if (_instance == null )
 27             {
 28                 lock (Locker)
 29                 {
 30                     if (_instance == null )
 31                     {
 32                         _instance = new SQLiteHelper ();
 33                     }
 34                 }
 35             }
 36 
 37             return _instance;
 38         }
 39 
 40         /// <summary>
 41         /// 獲取數據表
 42         /// </summary>
 43         /// <param name="cmdText"> 需要執行的命令文本 </param>
 44         /// <returns> 一個數據表集合 </returns>
 45         public DataTable GetDataTable( string cmdText)
 46         {
 47             var dt = new DataTable();
 48 
 49             try
 50             {
 51                 using (var conn = new SQLiteConnection (ConnectionStr))
 52                 {
 53                     conn.Open();
 54                     var cmd = new SQLiteCommand(conn) {CommandText = cmdText};
 55                     using (var reader = cmd.ExecuteReader())
 56                     {
 57                         dt.Load(reader);
 58                     }
 59                 }
 60             }
 61             catch (Exception e)
 62             {
 63                 throw new Exception(e.Message);
 64             }
 65 
 66             return dt;
 67         }
 68 
 69         /// <summary>
 70         /// 執行非查詢命令
 71         /// </summary>
 72         /// <param name="cmdText"> 需要執行的命令文本 </param>
 73         /// <returns> 返回更新的行數 </returns>
 74         public int ExecuteNonQuery( string cmdText)
 75         {
 76             using (var conn = new SQLiteConnection (ConnectionStr))
 77             {
 78                 conn.Open();
 79                 var cmd = new SQLiteCommand(conn) {CommandText = cmdText};
 80                 var rowsUpdated = cmd.ExecuteNonQuery();
 81 
 82                 return rowsUpdated;
 83             }
 84         }
 85 
 86         /// <summary>
 87         /// 執行檢索單項命令
 88         /// </summary>
 89         /// <param name="cmdText"> 需要執行的命令文本 </param>
 90         /// <returns> 一個字元串 </returns>
 91         public string ExecuteScalar( string cmdText)
 92         {
 93             using (var conn = new SQLiteConnection (ConnectionStr))
 94             {
 95                 conn.Open();
 96                 var cmd = new SQLiteCommand(conn) {CommandText = cmdText};
 97                 var value = cmd.ExecuteScalar();
 98 
 99                 if (value != null )
100                 {
101                     return value.ToString();
102                 }
103             }
104 
105             return "" ;
106         }
107     }
108 }