一个简单的C# Sqlite帮助类
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 }
//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)});" +
ArcGis交流群
