一个简单的C# Sqlite帮助类

 

  public class SqliteHelper
    {
        private readonly string connectionStr;
        public SqliteHelper(string dbPath)
        {
            if (System.IO.File.Exists(dbPath))
                this.connectionStr = $"Data Source = {dbPath}";
            else
                throw new ArgumentException($"数据库路径{dbPath}不存在！");
        }
        /// <summary>
        /// return an DataTable 
        /// </summary>
        /// <param name="commandText">a SQL clause like "SELECT * FROM DJDY_NaturalResources"</param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable ExcuteDataTable(string commandText,string tableName)
        {
            using SQLiteConnection connection = new SQLiteConnection(this.connectionStr);
            using SQLiteCommand command = new SQLiteCommand(connection);
            command.CommandText = commandText;
            DataTable table = new DataTable(tableName);
            SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(command);
            dataAdapter.Fill(table);
            return table;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <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 />
        /// e.g. get table name from SQl clause "SELECT * FROM DJDY_Info;",the result is "DJDY_Info".
        /// </param>
        /// <returns></returns>
        public DataSet ExcuteDataSet (params string[] commandTexts)
        {
            using SQLiteConnection connection = new SQLiteConnection(this.connectionStr);
            using SQLiteCommand command = new SQLiteCommand(connection);
            StringBuilder @string = new StringBuilder();
            List<string> names = new List<string>();
            foreach (var item in commandTexts)
            {
                @string.Append(item);
                names.Add(item.ToLower().Split(new string[] { "from" }, StringSplitOptions.RemoveEmptyEntries)[1].Replace(";","").Trim());
            }
            command.CommandText = @string.ToString();
            SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(command);
            for (int i = 0; i < names.Count; i++)
            {
                string tableName = i == 0 ? "Table" : $"Table{i}";
                dataAdapter.TableMappings.Add(tableName, names[i]);
                //System.Windows.Forms.MessageBox.Show(tableName + "__" + names[i]);
            }
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
            return dataSet;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandTexts"> a sql clause.<para />
        ///  inser clause like "INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  VALUES(value1, value2, value3,...valueN);",[] means this param is optional.<para />
        ///  if insert vale contains all columns, you can use insert clause like "INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);".
        /// </param>
        public void ExcuteNoneQuery(params string[] commandTexts)
        {
            using SQLiteConnection connection = new SQLiteConnection(this.connectionStr);
            connection.Open();
            using SQLiteCommand command = new SQLiteCommand(connection);
            using SQLiteTransaction transaction = connection.BeginTransaction();
            command.Transaction = transaction;
            try
            {
                foreach (var item in commandTexts)
                {
                    command.CommandText = item;
                    command.ExecuteNonQuery();
                }
                transaction.Commit();
            }
            catch (SQLiteException exp)
            {
                transaction.Rollback();
                throw new Exception("SQL语句执行错误。", exp);
            }
            finally
            { connection.Close(); }
        }
        /// <summary>
        /// clear all tables data.
        /// </summary>
        /// <returns></returns>
        public bool ClearTables()
        {
            using SQLiteConnection connection = new SQLiteConnection(this.connectionStr);
            connection.Open();
            using SQLiteCommand command = new SQLiteCommand(connection);
            using SQLiteTransaction transaction = connection.BeginTransaction();
            command.Transaction = transaction;
            DataTable dataTable = new DataTable("TableNames");
            string commandText = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";
            command.CommandText = commandText;
            SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(command);
            dataAdapter.Fill(dataTable);
            command.Reset();
            try
            {
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    commandText = $"delete from {dataTable.Rows[i][0].ToString()}";
                    command.CommandText = commandText;
                    command.ExecuteNonQuery();
                }
                transaction.Commit();
                return true;
            }
            catch (SQLiteException exp)
            {
                transaction.Rollback();
                throw new Exception("SQL语句执行错误。", exp);
            }
            finally
            { connection.Close(); }
        }
        /// <summary>
        /// test for whether the given table have records.
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns> if records==0 true,else false</returns>
        public bool TableNotHaveRecord(string tableName)
        {
            bool rslt=false;
            using SQLiteConnection connection = new SQLiteConnection(this.connectionStr);
            connection.Open();
            try
            {
                using SQLiteCommand command = new SQLiteCommand(connection);
                string commandText = $"select count(*) from {tableName}";
                command.CommandText = commandText;
                rslt = Convert.ToInt32(command.ExecuteScalar()) == 0;
                return rslt;
            }
            catch (SQLiteException exp)
            {
                throw new Exception("SQL语句执行错误。", exp);
            }
            finally
            {
                connection.Close();
            }
        }
    }

 

                        //if (djdyh_List.Contains(djdyh))
                        //{
                        //    commandTextZY[i - 1] = $"UPDATE DJDY_SHLZY SET BHTBSL={hlsmSL_NumberAndArea.Number}, SMMJ={Math.Round(hlsmSL_NumberAndArea.Area * 0.0001, 4)} WHERE  DJDYH = '{djdyh}' AND SLLX ='河流水面';" +
                        //        $"UPDATE DJDY_SHLZY SET BHTBSL={hpsmSL_NumberAndArea.Number}, SMMJ={Math.Round(hpsmSL_NumberAndArea.Area * 0.0001, 4)} WHERE  DJDYH = '{djdyh}' AND SLLX ='湖泊水面';" +
                        //        $"UPDATE DJDY_SHLZY SET BHTBSL={sksmSL_NumberAndArea.Number}, SMMJ={Math.Round(sksmSL_NumberAndArea.Area * 0.0001, 4)} WHERE  DJDYH = '{djdyh}' AND SLLX ='水库水面';" +
                        //        $"UPDATE DJDY_SHLZY SET BHTBSL={bcjxSL_NumberAndArea.Number}, SMMJ={Math.Round(bcjxSL_NumberAndArea.Area * 0.0001, 4)} WHERE  DJDYH = '{djdyh}' AND SLLX ='冰川及永久积雪';" +
                        //        //
                        //else
                        //{
                        //    commandTextZY[i - 1] = $"INSERT  INTO DJDY_SHLZY (DJDYH,SLLX,BHTBSL,SMMJ) VALUES ('{djdyh}','河流水面',{hlsmSL_NumberAndArea.Number},{Math.Round(hlsmSL_NumberAndArea.Area * 0.0001, 4)})," +
                        //        $"('{djdyh}','湖泊水面',{hpsmSL_NumberAndArea.Number},{Math.Round(hpsmSL_NumberAndArea.Area * 0.0001, 4)})," +
                        //        $"('{djdyh}','水库水面',{sksmSL_NumberAndArea.Number},{Math.Round(sksmSL_NumberAndArea.Area * 0.0001, 4)})," +
                        //        $"('{djdyh}','冰川及永久积雪',{bcjxSL_NumberAndArea.Number},{Math.Round(bcjxSL_NumberAndArea.Area * 0.0001, 4)});" +