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 }
【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！