/// <summary>
/// 加载ListBoxItem
/// </summary>
private void LoadDataEntryForm()
{
this.DataEntryViewModels = new ObservableCollection<DataEntryViewModel>();
using (SQLiteConnection conn = new SQLiteConnection(DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
IDataServices ids = new SqliteDataServices();
DataTable dt = sh.Select(("select Q_Num from QuestionInfo order by Q_Num"));
DataRow[] rows = dt.Select("1=1");
int[] QuestionNumbers = rows.Select(x => Convert.ToInt32(x[0])).ToArray();
//QuestionInfos = new QuestionInfo[QuestionNumbers.Length];
for (int i = 0; i < QuestionNumbers.Length; i++)
{
QuestionInfo qnf = ids.GetAllQuestionInfo(QuestionNumbers[i], DataSource);
//QuestionInfos[i] = qnf;
DataEntryViewModels.Add(new DataEntryViewModel(qnf, DataBaseFile));
}
conn.Close();
}
}
}
/// <summary>
/// 删除记录
/// 删除记录的同时删除记录的答案
/// </summary>
private void ExecuteDeleteRecordCommand()
{
IDataServices ids = new SqliteDataServices();
int firstQuestionNumber;
int lastQuestionNumber;
int rowCount;
// Config.DataBaseFile = Path.Combine(System.Windows.Forms.Application.StartupPath, "MyData.db");
using (SQLiteConnection conn = new SQLiteConnection(DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
firstQuestionNumber = sh.ExecuteScalar<int>("select Q_Num from QuestionInfo order by Q_Num limit 1");
lastQuestionNumber = sh.ExecuteScalar<int>("select Q_Num from QuestionInfo order by Q_Num desc limit 1");
//previousQuestionNumber = sh.ExecuteScalar<int>(string.Format("select Q_Num from QuestionInfo where Q_Num<{0} order by Q_Num desc limit 1", QuestionInfo.QuestionNumber));
//nextQuestionNumber = sh.ExecuteScalar<int>(string.Format("select Q_Num from QuestionInfo where Q_Num>{0} order by Q_Num limit 1", QuestionInfo.QuestionNumber));
using (new AutoWaitCursor())
{
if (QuestionInfo.QuestionNumber != null && !string.IsNullOrWhiteSpace(QuestionInfo.QuestionNumber))
{
sh.ExecuteScalar(string.Format("delete from QuestionInfo where Q_Num={0}", QuestionInfo.QuestionNumber));
sh.ExecuteScalar(string.Format("delete from QuestionAnwser where Q_Num={0}", QuestionInfo.QuestionNumber));
if (Int32.Parse(QuestionInfo.QuestionNumber) != lastQuestionNumber)
{
ExecuteNextRecordCommand();
}
else if (Int32.Parse(QuestionInfo.QuestionNumber) == lastQuestionNumber)
{
rowCount = sh.ExecuteScalar<int>(string.Format("select count(*) from QuestionInfo where Q_Num<{0} order by Q_Num desc limit 1", Int32.Parse(QuestionInfo.QuestionNumber)));
if (rowCount != 0)
{
ExecutePreviousRecordCommand();
}
else
{
QuestionInfo.QuestionNumber = "";
InitialQuestionInfo();
IsQuestionFieldExist.Instance.IsExist = false;
}
}
else
{
return;
}
}
}
conn.Close();
}
}
InsertRecordCommand.RaiseCanExecuteChanged();
RaiseCanExecuteChanged();
Status.Instance.ShowStatus("已删除");
}
/// <summary>
/// 输入记录号查询记录
/// </summary>
private void ExecuteSearchRecordCommand()
{
IDataServices ids = new SqliteDataServices();
int searchQNumber;
int rowCount;
//Config.DataBaseFile = Path.Combine(System.Windows.Forms.Application.StartupPath, "MyData.db");
using (SQLiteConnection conn = new SQLiteConnection(DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
rowCount = sh.ExecuteScalar<int>(string.Format("select count(*) from QuestionInfo where Q_Num={0}", SearchTextBox.SearchQuestionNumber));
if (rowCount == 0)
{
//MessageBoxWindow mbw = new MessageBoxWindow();
//mbw.Owner = System.Windows.Application.Current.MainWindow;
//mbw.WindowStartupLocation = WindowStartupLocation.CenterOwner;
//mbw.ShowDialog();
MessageBoxPlus.Show(App.Current.MainWindow, "查询记录不存在", "查询结果",MessageBoxButton.OK,MessageBoxImage.Information);
Status.Instance.StatusMessage = "查询记录不存在";
}
else
{
searchQNumber = sh.ExecuteScalar<int>(string.Format("select Q_Num from QuestionInfo where Q_Num={0}", SearchTextBox.SearchQuestionNumber));
this.QuestionInfo = ids.GetAllQuestionInfo(searchQNumber, DataSource);
Status.Instance.StatusMessage = "查询成功";
}
conn.Close();
}
}
PreviousRecordCommand.RaiseCanExecuteChanged();
NextRecordCommand.RaiseCanExecuteChanged();
SaveOrUpdate();
}
/// <summary>
/// 前往下一条记录
/// 如果到达最后一条记录，再往下则初始化一条空记录
/// </summary>
private void ExecuteNextRecordCommand()
{
IDataServices ids = new SqliteDataServices();
int nextQuestionNumber;
int lastQuestionNumber;
int currentRecordNumber;
int rowCount;
//Config.DataBaseFile = Path.Combine(System.Windows.Forms.Application.StartupPath, "MyData.db");
using (SQLiteConnection conn = new SQLiteConnection(DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
if (QuestionInfo.QuestionNumber != null && !string.IsNullOrWhiteSpace(QuestionInfo.QuestionNumber))
{
lastQuestionNumber = sh.ExecuteScalar<int>("select Q_Num from QuestionInfo order by Q_Num desc limit 1");
if (QuestionInfo.QuestionNumber == lastQuestionNumber.ToString())
{
QuestionInfo.QuestionNumber = (Int32.Parse(QuestionInfo.QuestionNumber) + 1).ToString();
InitialQuestionInfo();
IsQuestionFieldExist.Instance.IsExist = false;
Status.Instance.ShowStatus("当前记录(升序)：不存在");
}
else
{
rowCount = sh.ExecuteScalar<int>(string.Format("select count(*) from QuestionInfo where Q_Num>{0} order by Q_Num limit 1", QuestionInfo.QuestionNumber));
if (rowCount != 0)
{
nextQuestionNumber = sh.ExecuteScalar<int>(string.Format("select Q_Num from QuestionInfo where Q_Num>{0} order by Q_Num limit 1", QuestionInfo.QuestionNumber));
this.QuestionInfo = ids.GetAllQuestionInfo(nextQuestionNumber, DataSource);
currentRecordNumber = sh.ExecuteScalar<int>(string.Format("select count(*) from QuestionInfo where Q_Num<={0}", QuestionInfo.QuestionNumber));
Status.Instance.ShowStatus(string.Format("当前记录(升序)：{0}", currentRecordNumber));
}
}
}
conn.Close();
}
}
RaiseCanExecuteChanged();
}
/// <summary>
/// 前往前一条记录
/// </summary>
private void ExecutePreviousRecordCommand()
{
IDataServices ids = new SqliteDataServices();
int previousQuestionNumber;
int currentRecordNumber;
using (SQLiteConnection conn = new SQLiteConnection(DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
previousQuestionNumber = sh.ExecuteScalar<int>(string.Format("select Q_Num from QuestionInfo where Q_Num<{0} order by Q_Num desc limit 1", QuestionInfo.QuestionNumber));
this.QuestionInfo = ids.GetAllQuestionInfo(previousQuestionNumber, DataSource);
currentRecordNumber = sh.ExecuteScalar<int>(string.Format("select count(*) from QuestionInfo where Q_Num<={0}", QuestionInfo.QuestionNumber));
conn.Close();
}
}
RaiseCanExecuteChanged();
Status.Instance.ShowStatus(string.Format("当前记录(升序)：{0}", currentRecordNumber));
}
/// <summary>
/// 跳转到最后一条记录
/// </summary>
private void ExecuteLastRecordCommand()
{
IDataServices ids = new SqliteDataServices();
int lastQuestionNumber;
//Config.DataBaseFile = Path.Combine(System.Windows.Forms.Application.StartupPath, "MyData.db");
using (SQLiteConnection conn = new SQLiteConnection(DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
lastQuestionNumber = sh.ExecuteScalar<int>("select Q_Num from QuestionInfo order by Q_Num desc limit 1");
conn.Close();
}
}
this.QuestionInfo = ids.GetAllQuestionInfo(lastQuestionNumber, DataSource);
RaiseCanExecuteChanged();
Status.Instance.ShowStatus("已跳转到最后一条记录");
}