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);
}
