private void ShowSQLiteData() { SQLiteConnection conn = null; try { string sql = "Select ID_KEY,FILE_NO,FILE_NAME,SUBJECT,PUBLISH_ORG,PUBLISH_DATE,IMPLEMENT_DATE,ABSTRACT from tbl_fgwj"; conn = GetSQLiteData(); SQLiteCommand command = conn.CreateCommand(); command.CommandText = sql; SQLiteDataReader reader = command.ExecuteReader(); fileList.Clear(); while (reader.Read()) { string s0 = reader.GetValue(2).ToString(); string s1 = reader.GetValue(3).ToString(); string s2 = reader.GetValue(4).ToString(); string s3 = reader.GetValue(5).ToString(); AttachFile attachFile = new AttachFile(reader.GetValue(1).ToString(), reader.GetValue(2).ToString(), reader.GetValue(3).ToString(), reader.GetValue(4).ToString(), reader.GetValue(5).ToString(), reader.GetValue(6).ToString(), reader.GetValue(7).ToString()); attachFile.ID = reader.GetString(0); fileList.Add(attachFile); } fileGrid.ItemsSource = fileList; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
//修改窗体 private void ModifySQLiteData(AttachFile file) { SQLiteConnection conn = null; try { conn = GetSQLiteData(); SQLiteCommand cmd = conn.CreateCommand(); //TODO 根据空值来判断要不要更新 string sql = "Update tbl_fgwj Set "; if (!String.IsNullOrEmpty(file.No)) { sql += "FILE_NO =" + "\"" + file.No + "\"" + ","; } if (!String.IsNullOrEmpty(file.Name)) { sql += "FILE_NAME=" + "\"" + file.Name + "\"" + ","; } if (!String.IsNullOrEmpty(file.Theme)) { sql += "SUBJECT=" + "\"" + file.Theme + "\" ,"; } if (!String.IsNullOrEmpty(file.PublishTime)) { sql += "PUBLISH_DATE=" + "\"" + file.PublishTime + "\" ,"; } if (!String.IsNullOrEmpty(file.CarryTime)) { sql += "IMPLEMENT_DATE=" + "\"" + file.CarryTime + "\""; } if (sql.EndsWith(",")) { sql = sql.Substring(0, sql.Length - 1); } sql += " WHERE ID_KEY=" + "\"" + file.ID + "\""; cmd.CommandText = sql; int tag = cmd.ExecuteNonQuery(); if (tag >= 1) { MessageBox.Show("修改成功"); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void AddSQLiteData(AttachFile file) { SQLiteConnection conn = null; try { conn = GetSQLiteData(); SQLiteCommand cmd = conn.CreateCommand(); string sql = String.Format("Insert Into tbl_fgwj(ID_KEY,FILE_NO,FILE_NAME,SUBJECT,PUBLISH_DATE,IMPLEMENT_DATE,ABSTRACT) Values(@0,@1,@2,@3,@4,@5,@6)"); cmd.CommandText = sql; SQLiteParameter parameter = new SQLiteParameter("@0"); // 利用当天日期加当前的时间拼凑出一个字符串作为唯一的 ID_KEY parameter.Value = DateTime.Now.ToString("yyyyMMddHHmmssffff");; cmd.Parameters.Add(parameter); parameter = new SQLiteParameter("@1"); parameter.Value = file.No; cmd.Parameters.Add(parameter); parameter = new SQLiteParameter("@2"); parameter.Value = file.Name; cmd.Parameters.Add(parameter); parameter = new SQLiteParameter("@3"); parameter.Value = file.Theme; cmd.Parameters.Add(parameter); parameter = new SQLiteParameter("@4"); parameter.Value = file.PublishTime; cmd.Parameters.Add(parameter); parameter = new SQLiteParameter("@5"); parameter.Value = file.CarryTime; cmd.Parameters.Add(parameter); parameter = new SQLiteParameter("@6"); parameter.Value = file.Abstract; cmd.Parameters.Add(parameter); int tag = cmd.ExecuteNonQuery(); if (tag >= 1) { MessageBox.Show("新增成功"); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public void addToList(AttachFile file) { AddSQLiteData(file); ShowSQLiteData(); //fileList.Add(file); }
private void Confirm_Click(object sender, RoutedEventArgs e) { AttachFile file = new AttachFile(fileNo.Text, fileName.Text, publishUnit.Text, publishTime.DisplayDate.Date.ToShortDateString(), carryTime.DisplayDate.Date.ToShortDateString(), Abstract.Text); database.addToList(file); }