public OutReForm(string nm, OutRecord rec, bool ro)
{
InitializeComponent();
this.record = rec;
this.name = nm;
this.readoly = ro;
}
//
public static bool ModifyOutRec(string name, OutRecord rec)
{
string connString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=""E:\c++练习\MyMoneyAgent - 副本 (2) - 副本\MyMoneyAgent\MyMoney.mdf"";Integrated Security=True";
SqlConnection conn = new SqlConnection(connString);//创建链接对象
try
{
conn.Open();
//插入新支出记录命令
string cmdTxt;
cmdTxt = string.Format("UPDATE [支出记录] SET [支出时间]='{0}', [记录时间]='{1}', [支出方式]='{2}', [支出类型]='{3}', 金额={4}, [银行卡号]='{5}', [支出说明]='{6}' WHERE ([支出编号]={7} AND [姓名]='{8}')",
rec.OutTime, rec.RecordTime, rec.OutType, rec.OutUsage,
rec.Amount, rec.BankCard, rec.Description, rec.ID, name);
//查询命令
SqlCommand comm = new SqlCommand(cmdTxt, conn);
comm.ExecuteNonQuery();
return(true);
}
catch (Exception)
{
return(false);
}
finally
{
//关闭连接
conn.Close();
}
}
//
public static bool AddOutRec(string name, OutRecord rec)
{
string connString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=""E:\c++练习\MyMoneyAgent - 副本 (2) - 副本\MyMoneyAgent\MyMoney.mdf"";Integrated Security=True";
SqlConnection conn = new SqlConnection(connString);//创建链接对象
try
{
conn.Open();
//插入新支出记录命令
string cmdTxt;
cmdTxt = string.Format("INSERT INTO [支出记录] VALUES({0}, '{1}', '{2}', '{3}', '{4}', '{5}', {6}, '{7}', '{8}')",
rec.ID, name, rec.OutTime, rec.RecordTime, rec.OutType,
rec.OutUsage, rec.Amount, rec.BankCard, rec.Description);
//查询命令
SqlCommand comm = new SqlCommand(cmdTxt, conn);
comm.ExecuteNonQuery();
return(true);
}
catch (Exception e)
{
return(false);
}
finally
{
//关闭连接
conn.Close();
}
}
//
public static List <OutRecord> LoadOutRcdListSearch(string name, string date1, string date2)
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet("MyMoney");
string connString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=""E:\c++练习\MyMoneyAgent - 副本 (2) - 副本\MyMoneyAgent\MyMoney.mdf"";Integrated Security=True";
SqlConnection conn = new SqlConnection(connString);//创建链接对象
try
{
conn.Open();
//查询数据库中
string cmdTxt = "SELECT [支出编号], [姓名], [支出时间], [记录时间], [支出方式], " +
"[支出类型], [金额], [银行卡号], [支出说明] FROM [支出记录] " +
"WHERE [姓名]='" + name + "' AND ([支出时间] >=#" + date1 + "# AND [支出时间] <=#" + date2 + "#)";
SqlCommand comm = new SqlCommand(cmdTxt, conn);
da.SelectCommand = comm;
SqlCommandBuilder builder = new SqlCommandBuilder(da);
da.Fill(ds, "支出记录");
List <OutRecord> recList = new List <OutRecord>();
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
OutRecord rec = new OutRecord();
rec.ID = dr.GetInt32(0);
rec.OutTime = dr.GetDateTime(2);
rec.RecordTime = dr.GetDateTime(3);
rec.OutType = dr.GetString(4);
rec.OutUsage = dr.GetString(5);
rec.Amount = dr.GetDecimal(6);
rec.BankCard = dr.GetString(7);
rec.Description = dr.GetString(8);
recList.Add(rec);
}
return(recList);
}
catch (Exception)
{
return(null);
}
finally
{
//关闭连接
conn.Close();
}
}
private void btnAddNewOutRec_Click(object sender, EventArgs e)
{
//创建用来保存新记录的对象
int newId = OutSQL.GetUsableOutID(this.curUser.Name);
if (newId == 0)
{
MessageBox.Show(this, "无法获取可用的支出记录编号，请确保数据库连接正确！", "提示",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
OutRecord rec = new OutRecord();
rec.ID = newId;
rec.OutTime = DateTime.Now;
rec.RecordTime = DateTime.Now;
//创建支出记录编辑对话框
OutReForm dlg = new OutReForm(this.curUser.Name, rec, false);
if (dlg.ShowDialog(this) == DialogResult.OK)
{
//添加记录成功，则添加到界面
if (OutSQL.AddOutRec(this.curUser.Name, rec))
{
//添加一行到界面
int newRowIndex = this.dgvOutRec.Rows.Add();
this.dgvOutRec.Rows[newRowIndex].Tag = rec;
//显示信息到新添加的行
this.dgvOutRec.Rows[newRowIndex].Cells["colOutID"].Value = rec.ID;
this.dgvOutRec.Rows[newRowIndex].Cells["colOutTime"].Value = rec.OutTime;
this.dgvOutRec.Rows[newRowIndex].Cells["colOutType"].Value = rec.OutType;
this.dgvOutRec.Rows[newRowIndex].Cells["colOutUsage"].Value = rec.OutUsage;
this.dgvOutRec.Rows[newRowIndex].Cells["colOutRecTime"].Value = rec.RecordTime;
this.dgvOutRec.Rows[newRowIndex].Cells["colOutDes"].Value = rec.Description;
this.dgvOutRec.Rows[newRowIndex].Cells["colOutBank"].Value = rec.BankCard;
this.dgvOutRec.Rows[newRowIndex].Cells["colOutAmount"].Value = rec.Amount;
}
else
{
MessageBox.Show(this, "添加记录失败！", "提示",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
