SQLite 读写/类库
NewGet System.Data.SQLite(1.0.115.5)
SQLite类库
1 1 using System; 2 2 using System.Collections.Generic; 3 3 using System.Data; 4 4 using System.Data.SQLite; 5 5 using System.IO; 6 6 using System.Windows; 7 7 8 8 namespace TempCommunication.SQLite 9 9 { 10 10 public class SQLiteHelper 11 11 { 12 12 /// <summary> 13 13 /// 链接套接字 14 14 /// </summary> 15 15 static SQLiteConnection m_dbConnection { get; set; } 16 16 17 17 /// <summary> 18 18 /// 生成链接字符串 19 19 /// </summary> 20 20 /// <param name="db"></param> 21 21 /// <returns></returns> 22 22 private static string CreateConnectionString(string db) 23 23 { 24 24 SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder(); 25 25 connectionString.DataSource = @"data/" + db;//此处文件名可以使用变量表示 26 26 string conStr = connectionString.ToString(); 27 27 return conStr; 28 28 29 29 } 30 30 31 31 /// <summary> 32 32 /// 链接到数据库 33 33 /// </summary> 34 34 /// <param name="db"></param> 35 35 /// <returns></returns> 36 36 private static SQLiteConnection dbConnection(string db) 37 37 { 38 38 m_dbConnection = new SQLiteConnection(CreateConnectionString(db)); 39 39 m_dbConnection.Open(); 40 40 return m_dbConnection; 41 41 } 42 42 43 43 /// <summary> 44 44 /// 创建数据库文件，如：text.db 45 45 /// </summary> 46 46 /// <param name="fileName">文件名</param> 47 47 public static bool CreateDBFile(string fileName) 48 48 { 49 49 int floge = 0; 50 50 string path = Environment.CurrentDirectory + @"/Data/"; 51 51 if (!Directory.Exists(path)) 52 52 { 53 53 Directory.CreateDirectory(path); 54 54 floge++; 55 55 } 56 56 string databaseFileName = path + fileName; 57 57 if (!File.Exists(databaseFileName)) 58 58 { 59 59 SQLiteConnection.CreateFile(databaseFileName); 60 60 floge++; 61 61 } 62 62 if (floge >0) 63 63 { 64 64 floge = 0; 65 65 return true; 66 66 67 67 } 68 68 else 69 69 { 70 70 return false; 71 71 } 72 72 } 73 73 74 74 /// <summary> 75 75 /// 在指定数据库中创建一个table 76 76 /// 如: create table mytab (ID INT PRIMARY KEY NOT NULL, text TEXT NOT NULL) 77 77 /// </summary> 78 78 /// <param name="db"></param> 79 79 /// <param name="sql"></param> 80 80 /// <returns></returns> 81 81 public static bool CreateTable(string db,string sql) 82 82 { 83 83 try 84 84 { 85 85 SQLiteCommand command = new SQLiteCommand(sql,dbConnection(db)); 86 86 command.ExecuteNonQuery(); 87 87 return true; 88 88 } 89 89 catch (Exception ex) 90 90 { 91 91 MessageBox.Show("ExecuteNonQuery("+sql+")Err:" +ex.Message); 92 92 return false; 93 93 } 94 94 finally 95 95 { 96 96 CloseConn(); 97 97 } 98 98 } 99 99 100 100 /// <summary> 101 101 /// 返回记录集查询 102 102 /// </summary> 103 103 /// <param name="db">数据库名称</param> 104 104 /// <param name="sql">sql查询语言</param> 105 105 /// <returns>返回查询结果</returns> 106 106 public static DataTable SqlTable(string db, string sql) 107 107 { 108 108 try 109 109 { 110 110 SQLiteCommand command = new SQLiteCommand(sql, dbConnection(db));//sql语句 111 111 command.CommandTimeout = 120; 112 112 SQLiteDataReader reader = command.ExecuteReader(); 113 113 DataTable dt = new DataTable(); 114 114 if (reader != null) 115 115 { 116 116 dt.Load(reader,LoadOption.PreserveChanges,null); 117 117 } 118 118 return dt; 119 119 120 120 } 121 121 catch (Exception ex) 122 122 { 123 123 124 124 MessageBox.Show("SqlReader("+(sql)+")Err:" + ex.Message); 125 125 return null; 126 126 } 127 127 finally 128 128 { 129 129 CloseConn(); 130 130 } 131 131 } 132 132 133 133 134 134 /// <summary> 135 135 /// 执行增删改查操作 136 136 /// </summary> 137 137 /// <param name="db"></param> 138 138 /// <param name="sql"></param> 139 139 /// <returns></returns> 140 140 public static int ExecuteNonQuery(string db, string sql) 141 141 { 142 142 try 143 143 { 144 144 SQLiteCommand cmd; 145 145 cmd = new SQLiteCommand(sql,dbConnection(db)); 146 146 cmd.ExecuteNonQuery().ToString(); 147 147 return 1; 148 148 } 149 149 catch (Exception ex) 150 150 { 151 151 MessageBox.Show("ExecuteNonQuery("+sql+")Err:" +ex.Message); 152 152 return 0; 153 153 } 154 154 finally 155 155 { 156 156 CloseConn(); 157 157 } 158 158 } 159 159 160 160 /// <summary> 161 161 /// 删除数据库 162 162 /// </summary> 163 163 /// <param name="fileName"></param> 164 164 public static bool DeleteDBFile(string fileName) 165 165 { 166 166 string path = Environment.CurrentDirectory + @"/Data/" + fileName; 167 167 if (File.Exists(path)) 168 168 { 169 169 File.Delete(path); 170 170 return true; 171 171 } 172 172 else 173 173 { 174 174 return false; 175 175 } 176 176 } 177 177 178 178 /// <summary> 179 179 /// 删除一张表 180 180 /// </summary> 181 181 /// <param name="db"></param> 182 182 /// <param name="tableName">表名称</param> 183 183 /// <returns></returns> 184 184 public static bool DeleteTable(string db, string tableName) 185 185 { 186 186 try 187 187 { 188 188 SQLiteCommand cmd = new SQLiteCommand("DROP TABLE IF EXISTS" + tableName, dbConnection(db)); 189 189 cmd.ExecuteNonQuery(); 190 190 return true; 191 191 } 192 192 catch (Exception ex) 193 193 { 194 194 MessageBox.Show("ExecuteNonQuery(DROP TABLE IF EXISTS"+(tableName)+")Err" +ex.Message); 195 195 return false; 196 196 } 197 197 finally 198 198 { 199 199 CloseConn(); 200 200 } 201 201 } 202 202 203 203 /// <summary> 204 204 /// 返回一条记录查询 205 205 /// </summary> 206 206 /// <param name="db"></param> 207 207 /// <param name="sql"></param> 208 208 /// <returns></returns> 209 209 public static List<string> SqlROW(string db,string sql) 210 210 { 211 211 try 212 212 { 213 213 SQLiteCommand sqlcom = new SQLiteCommand(sql, dbConnection(db)); 214 214 SQLiteDataReader reader = sqlcom.ExecuteReader(); 215 215 //从结果集中读取下一行 返回bool 216 216 if (!reader.Read()) 217 217 { 218 218 return null; 219 219 } 220 220 List<string> Row = new List<string>(); 221 221 for (int i = 0; i < reader.FieldCount; i++) 222 222 { 223 223 Row.Add(reader[i].ToString()); 224 224 } 225 225 reader.Close(); 226 226 return Row; 227 227 } 228 228 catch (Exception ex) 229 229 { 230 230 MessageBox.Show("SqlRow(" + sql + ")Err:" + ex.Message); 231 231 return null; 232 232 } 233 233 finally 234 234 { 235 235 CloseConn(); 236 236 }; 237 237 } 238 238 239 239 /// <summary> 240 240 /// 在指定表中添加列 241 241 /// </summary> 242 242 /// <param name="db"></param> 243 243 /// <param name="tableName">表名</param> 244 244 /// <param name="columnName">列名</param> 245 245 /// <param name="ctype">列的值类型</param> 246 246 /// <returns></returns> 247 247 public static bool AddColumn(string db, string tableName, string columnName, string ctype) 248 248 { 249 249 try 250 250 { 251 251 SQLiteCommand cmd = new SQLiteCommand("ALTER TABLE " + tableName + " ADD COLUMN " + columnName + " " + ctype, dbConnection(db)); 252 252 cmd.ExecuteNonQuery(); 253 253 return true; 254 254 } 255 255 catch (Exception ex) 256 256 { 257 257 MessageBox.Show("ExecuteNonQuery(ALTER TABLE " + tableName + " ADD COLUMN " + columnName + " " + ctype +")Err: "+ex.Message); 258 258 return false; 259 259 } 260 260 finally 261 261 { 262 262 CloseConn(); 263 263 } 264 264 } 265 265 266 266 /// <summary> 267 267 /// 唯一结果查询 268 268 /// </summary> 269 269 /// <param name="db"></param> 270 270 /// <param name="sql">sql查询语句</param> 271 271 /// <returns></returns> 272 272 public static string SqlOne(string db,string sql) 273 273 { 274 274 try 275 275 { 276 276 SQLiteCommand cmd = new SQLiteCommand(sql,dbConnection(db)); 277 277 return cmd.ExecuteScalar().ToString(); 278 278 } 279 279 catch 280 280 { 281 281 return ""; 282 282 } 283 283 finally 284 284 { 285 285 CloseConn(); 286 286 } 287 287 } 288 288 289 289 /// <summary> 290 290 /// 获取一列数据 291 291 /// </summary> 292 292 /// <typeparam name="T"></typeparam> 293 293 /// <param name="db"></param> 294 294 /// <param name="sql"></param> 295 295 /// <returns></returns> 296 296 public static List<T> SqlColumn<T>(string db,string sql) 297 297 { 298 298 try 299 299 { 300 300 List<T> Colmun = new(); 301 301 SQLiteCommand sqlcmd = new SQLiteCommand(sql,dbConnection(db)); 302 302 SQLiteDataReader reader = sqlcmd.ExecuteReader(); 303 303 while (reader.Read()) 304 304 { 305 305 Colmun.Add((T)reader[0]); 306 306 } 307 307 reader.Close(); 308 308 return Colmun; 309 309 } 310 310 catch (Exception ex) 311 311 { 312 312 MessageBox.Show("SqlColumn("+sql+")Err:"+ex.Message); 313 313 return null; 314 314 } 315 315 finally 316 316 { 317 317 CloseConn(); 318 318 } 319 319 } 320 320 321 321 /// <summary> 322 322 /// 关闭数据库连接 323 323 /// </summary> 324 324 public static void CloseConn() 325 325 { 326 326 try 327 327 { 328 328 if (m_dbConnection.State == ConnectionState.Open) 329 329 { 330 330 m_dbConnection.Close(); 331 331 } 332 332 else if(m_dbConnection.State == ConnectionState.Broken) 333 333 { 334 334 m_dbConnection.Close(); 335 335 } 336 336 } 337 337 catch (Exception ex) 338 338 { 339 339 340 340 MessageBox.Show("CloseConnErr:" +ex); 341 341 } 342 342 } 343 343 } 344 344 }
调用SQLite类库，
1 #region 数据库操作 2 3 #region 创建数据库 4 public DelegateCommand CreateDBCommand { get; set; } 5 private void CreateDB() 6 { 7 //创建数据库文件 8 bool floge = SQLiteHelper.CreateDBFile("mydb.db"); 9 if (floge) 10 { 11 MessageBox.Show("数据库创建成功"); 12 } 13 } 14 #endregion 15 16 17 #region 创建表 18 public DelegateCommand CreateTabCommand { get; set; } 19 private void CreateTab() 20 { 21 bool flag = SQLiteHelper.CreateTable("mydb.db", "create table MyTab (ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,Title TEXT,SVSetting INT,InHTemp INT,InLTemp INT,Sensors INT,ControlModel INT,PB INT,Ti INT,Td INT)"); 22 if (flag) 23 { 24 MessageBox.Show("数据表创建成功"); 25 } 26 else 27 { 28 MessageBox.Show("数据表创建失败"); 29 } 30 } 31 #endregion 32 33 #region 删除数据库 34 public DelegateCommand DeleteDBFileCommand { get; set; } 35 private void DeleteDBFile() 36 { 37 bool flag = SQLiteHelper.DeleteDBFile("mydb.db"); 38 if (flag) 39 { 40 MessageBox.Show("数据删除成功"); 41 } 42 else 43 { 44 MessageBox.Show("数据删除失败"); 45 } 46 } 47 #endregion 48 49 #region 删除表 50 public DelegateCommand DeleteTabFileCommand { get; set; } 51 private void DeleteTab() 52 { 53 bool flag = SQLiteHelper.DeleteTable("mydb.db", "MyTab"); 54 if (flag) 55 { 56 MessageBox.Show("删除表成功"); 57 } 58 else 59 { 60 MessageBox.Show("删除表失败"); 61 } 62 } 63 #endregion 64 65 #region 根据ID查询数据 66 public DelegateCommand SqlRowCommand { get; set; } 67 private void SqlRow() 68 { 69 70 string strSql = $"select * from MyTab where ID=0"; 71 List<string> ds = SQLiteHelper.SqlROW("mydb.db", strSql); 72 73 //if (string.IsNullOrWhiteSpace(Com_Sql.Text)) 74 //{ 75 // MessageBox.Show("请选择要读取的数据名称！"); 76 // return; 77 //} 78 //string strSql = $"select * from Lamps_Data where Name='{Com_Sql.Text}'"; 79 //LampsUnity = SqlLiteHeiper.SqlRow("mydb.db", strSql); 80 //Display_Data(LampsUnity); 81 } 82 #endregion 83 84 #region 查询整张表 85 public DelegateCommand SqlTabCommand { get; set; } 86 private void SqlTab() 87 { 88 string strSql = $"select * from MyTab"; 89 DataTable ds = SQLiteHelper.SqlTable("mydb.db", strSql); 90 } 91 #endregion 92 93 #region 添加一行数据 94 public DelegateCommand InsertCommand { get; set; } 95 private void Insert() 96 { 97 if (string.IsNullOrEmpty(Title)) 98 { 99 MessageBox.Show("数据名称不能为空"); 100 return; 101 } 102 string sql = $"insert into mytab(Title ,SVSetting ,InHTemp ,InLTemp ,Sensors ,ControlModel ,PB ,Ti ,Td ) " + 103 $"values('{Title}',{SVSetting},{InHTemp},{InLTemp},{Sensors},{ControlModel},{PB},{Ti},{Td})"; 104 int flag = SQLiteHelper.ExecuteNonQuery("mydb.db", sql); 105 if (flag >= 1) 106 { 107 MessageBox.Show("数据添加成功"); 108 } 109 else 110 { 111 MessageBox.Show("数据添加失败"); 112 } 113 114 } 115 #endregion 116 117 #region 根据列名返回一行数据 118 public DelegateCommand GetColumnSqlCommand { get; set; } 119 private void GetColumnSql() 120 { 121 if (Titles == null) 122 { 123 Column(); 124 } 125 else 126 { 127 string result = Titles[TitlesIndex]; 128 string strSql = $"select * from mytab where Title='{result}'"; 129 var ds = SQLiteHelper.SqlROW("mydb.db", strSql); 130 if (ds != null) 131 { 132 SVSetting = short.Parse(ds[2]); 133 InHTemp = short.Parse(ds[3]); 134 InLTemp = short.Parse(ds[4]); 135 Sensors = short.Parse(ds[5]); 136 ControlModel = short.Parse(ds[6]); 137 PB = ushort.Parse(ds[7]); 138 Ti = ushort.Parse(ds[8]); 139 Td = ushort.Parse(ds[9]); 140 } 141 142 } 143 } 144 #endregion 145 146 #region 返回某一列的数据 147 private void Column() 148 { 149 string strSql = "select Title from mytab"; 150 List<string> dl = SQLiteHelper.SqlColumn<string>("mydb.db", strSql); 151 Titles = dl; 152 } 153 #endregion 154 155 private int titlesIndex; 156 /// <summary> 157 /// 选择标题 158 /// </summary> 159 public int TitlesIndex 160 { 161 get { return titlesIndex; } 162 set { titlesIndex = value; RaisePropertyChanged(); } 163 } 164 165 166 #region 删除一行数据 167 public DelegateCommand DeleteCommand { get; set; } 168 private void Delete() 169 { 170 string result = Titles[TitlesIndex]; 171 if (result != null) 172 { 173 string sql = $"DELETE FROM mytab where Title='{result}'"; 174 int flag = SQLiteHelper.ExecuteNonQuery("mydb.db", sql); 175 if (flag > 0) 176 { 177 MessageBox.Show("数据删除成功"); 178 Column(); 179 } 180 else 181 { 182 MessageBox.Show("数据删除失败"); 183 } 184 } 185 } 186 #endregion 187 188 #region 更新一行数据 189 public DelegateCommand UpDateCommand { get; set; } 190 private void UpDate() 191 { 192 string result = Titles[TitlesIndex]; 193 string sql = $"update mytab set SVSetting={SVSetting},InHTemp={InHTemp},Sensors={Sensors},ControlModel={ControlModel},PB={PB},Ti={Ti},Td={Td} where Title='{result}'"; 194 int flag = SQLiteHelper.ExecuteNonQuery("mydb.db", sql); 195 if (flag > 0) 196 { 197 MessageBox.Show("数据更新成功"); 198 Column(); 199 } 200 else 201 { 202 MessageBox.Show("数据更新失败"); 203 } 204 } 205 #endregion 206 #endregion
浙公网安备 33010602011771号