1   public class SqliteHelper
  2     {
  3         private readonly string connectionStr;
  4         public SqliteHelper(string dbPath)
  5         {
  6             if (System.IO.File.Exists(dbPath))
  7                 this.connectionStr = $"Data Source = {dbPath}";
  8             else
  9                 throw new ArgumentException($"数据库路径{dbPath}不存在！");
 10         }
 11         /// <summary>
 12         /// return an DataTable 
 13         /// </summary>
 14         /// <param name="commandText">a SQL clause like "SELECT * FROM DJDY_NaturalResources"</param>
 15         /// <param name="tableName"></param>
 16         /// <returns></returns>
 17         public DataTable ExcuteDataTable(string commandText,string tableName)
 18         {
 19             using SQLiteConnection connection = new SQLiteConnection(this.connectionStr);
 20             using SQLiteCommand command = new SQLiteCommand(connection);
 21             command.CommandText = commandText;
 22             DataTable table = new DataTable(tableName);
 23             SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(command);
 24             dataAdapter.Fill(table);
 25             return table;
 26         }
 27 
 28         /// <summary>
 29         /// 
 30         /// </summary>
 31         /// <param name="commandTexts">a SQL clause like "SELECT * FROM DJDY_Info;",the last word must be table name! CUZ it will be name of ds.Table.<para />
 32         /// e.g. get table name from SQl clause "SELECT * FROM DJDY_Info;",the result is "DJDY_Info".
 33         /// </param>
 34         /// <returns></returns>
 35         public DataSet ExcuteDataSet (params string[] commandTexts)
 36         {
 37             using SQLiteConnection connection = new SQLiteConnection(this.connectionStr);
 38             using SQLiteCommand command = new SQLiteCommand(connection);
 39             StringBuilder @string = new StringBuilder();
 40             List<string> names = new List<string>();
 41             foreach (var item in commandTexts)
 42             {
 43                 @string.Append(item);
 44                 names.Add(item.ToLower().Split(new string[] { "from" }, StringSplitOptions.RemoveEmptyEntries)[1].Replace(";","").Trim());
 45             }
 46             command.CommandText = @string.ToString();
 47             SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(command);
 48             for (int i = 0; i < names.Count; i++)
 49             {
 50                 string tableName = i == 0 ? "Table" : $"Table{i}";
 51                 dataAdapter.TableMappings.Add(tableName, names[i]);
 52                 //System.Windows.Forms.MessageBox.Show(tableName + "__" + names[i]);
 53             }
 54             DataSet dataSet = new DataSet();
 55             dataAdapter.Fill(dataSet);
 56             return dataSet;
 57         }
 58         /// <summary>
 59         /// 
 60         /// </summary>
 61         /// <param name="commandTexts"> a sql clause.<para />
 62         ///  inser clause like "INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  VALUES(value1, value2, value3,...valueN);",[] means this param is optional.<para />
 63         ///  if insert vale contains all columns, you can use insert clause like "INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);".
 64         /// </param>
 65         public void ExcuteNoneQuery(params string[] commandTexts)
 66         {
 67             using SQLiteConnection connection = new SQLiteConnection(this.connectionStr);
 68             connection.Open();
 69             using SQLiteCommand command = new SQLiteCommand(connection);
 70             using SQLiteTransaction transaction = connection.BeginTransaction();
 71             command.Transaction = transaction;
 72             try
 73             {
 74                 foreach (var item in commandTexts)
 75                 {
 76                     command.CommandText = item;
 77                     command.ExecuteNonQuery();
 78                 }
 79                 transaction.Commit();
 80             }
 81             catch (SQLiteException exp)
 82             {
 83                 transaction.Rollback();
 84                 throw new Exception("SQL语句执行错误。", exp);
 85             }
 86             finally
 87             { connection.Close(); }
 88         }
 89         /// <summary>
 90         /// clear all tables data.
 91         /// </summary>
 92         /// <returns></returns>
 93         public bool ClearTables()
 94         {
 95             using SQLiteConnection connection = new SQLiteConnection(this.connectionStr);
 96             connection.Open();
 97             using SQLiteCommand command = new SQLiteCommand(connection);
 98             using SQLiteTransaction transaction = connection.BeginTransaction();
 99             command.Transaction = transaction;
100             DataTable dataTable = new DataTable("TableNames");
101             string commandText = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";
102             command.CommandText = commandText;
103             SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(command);
104             dataAdapter.Fill(dataTable);
105             command.Reset();
106             try
107             {
108                 for (int i = 0; i < dataTable.Rows.Count; i++)
109                 {
110                     commandText = $"delete from {dataTable.Rows[i][0].ToString()}";
111                     command.CommandText = commandText;
112                     command.ExecuteNonQuery();
113                 }
114                 transaction.Commit();
115                 return true;
116             }
117             catch (SQLiteException exp)
118             {
119                 transaction.Rollback();
120                 throw new Exception("SQL语句执行错误。", exp);
121             }
122             finally
123             { connection.Close(); }
124         }
125         /// <summary>
126         /// test for whether the given table have records.
127         /// </summary>
128         /// <param name="tableName"></param>
129         /// <returns> if records==0 true,else false</returns>
130         public bool TableNotHaveRecord(string tableName)
131         {
132             bool rslt=false;
133             using SQLiteConnection connection = new SQLiteConnection(this.connectionStr);
134             connection.Open();
135             try
136             {
137                 using SQLiteCommand command = new SQLiteCommand(connection);
138                 string commandText = $"select count(*) from {tableName}";
139                 command.CommandText = commandText;
140                 rslt = Convert.ToInt32(command.ExecuteScalar()) == 0;
141                 return rslt;
142             }
143             catch (SQLiteException exp)
144             {
145                 throw new Exception("SQL语句执行错误。", exp);
146             }
147             finally
148             {
149                 connection.Close();
150             }
151         }
152     }