/***************变量声明结束*************************/
private void btnSubmit_Click(object sender, EventArgs e)
{
//接受页面文本框参数
dorm d = new dorm();
d.dormNum = this.txtDormNum.Text;
string reply1 = "亲，宿舍名不能为空哦！";
d.fk_typeId = this.cboDormType.SelectedValue.ToString();
d.fk_buildId = this.cboBuilding.SelectedValue.ToString();
if (Validation.checktxt(d.dormNum, ref reply1))
{
return;
}
//处理宿舍状态下拉框
if (this.cboFlag.Text == "可以入住")
{
d.flag = "1";
}
else if (this.cboFlag.Text == "不可入住")
{
d.flag = "2";
}
//添加进数据库
wsm.insert(d);
//刷新dmf窗体数据
SingleWindow.dmf.flush();
//关闭本窗体
this.Close();
MessageBox.Show("添加成功！", "温馨提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
/***************变量声明结束*************************/
private void dormViewForm_Load(object sender, EventArgs e)
{
//创建实体类接受页面上的id
dorm d = new dorm();
d.dormId = this.txtDormId.Text;
//利用id查询到选中的所有信息
DataTable dt = wsm.findById(d);
//给页面上的lbl赋值
this.txtDormId.Text = dt.Rows[0]["dormId"].ToString();
this.txtDormNum.Text = dt.Rows[0]["dormNum"].ToString();
this.txtPerson.Text = dt.Rows[0]["personCount"].ToString();
this.txtMemo.Text = dt.Rows[0]["memo"].ToString();
//处理Flag状态，使他成为文字显示
if (dt.Rows[0]["flag"].ToString() == "1")
{
this.txtFlag.Text = "可以入住";
}
else if (dt.Rows[0]["flag"].ToString() == "2")
{
this.txtFlag.Text = "不可入住";
}
//处理类型外键
DataTable dtt = wsm.findByType(d);
this.txtType.Text = dtt.Rows[0]["typeName"].ToString();
//处理楼层外键
DataTable ddtt = wsm.findByBuild(d);
this.txtBuilding.Text = ddtt.Rows[0]["buildName"].ToString();
//从上到下打开窗口
}
/// <summary>
/// 专门用来修改数据的方法
/// sql语句可以连接数据库进行操作数据
/// </summary>
/// <param name="d"></param>
public void update(dorm d)
{
SqlConnection conn = new SqlConnection("server=" + MyGlobal.ip + ";database=dormitory;UID=sa;PWD=zyh@197068;Integrated Security=False");
conn.Open();
string mysql = string.Format("update dorm set dormNum = {0},fk_typeId = {1},fk_buildid = {2},personCount = {3},flag = {4},memo = '{5}' where dormId = {6}",
d.dormNum, d.fk_typeId, d.fk_buildId, d.personCount, d.flag, d.memo, d.dormId);
SqlCommand sql = new SqlCommand(mysql, conn);
SqlDataReader reader = sql.ExecuteReader();
reader.Close();
conn.Close();
}
/// <summary>
/// 专门用来添加数据的方法
/// sql语句可以连接数据库进行操作数据
/// </summary>
/// dorm是实体类
/// 用来保存用户提交的参数
/// 方便此方法多次使用且数据不会被覆盖
/// <param name="d"></param>
public void insert(dorm d)
{
SqlConnection conn = new SqlConnection("server=" + MyGlobal.ip + ";database=dormitory;UID=sa;PWD=zyh@197068;Integrated Security=False");
conn.Open();
string mysql = string.Format("insert into dorm(dormNum,fk_typeId,fk_buildId,personCount,flag) values('{0}',{1},{2},0,{3})",
d.dormNum, d.fk_typeId, d.fk_buildId, d.flag);
SqlCommand sql = new SqlCommand(mysql, conn);
SqlDataReader reader = sql.ExecuteReader();
reader.Close();
conn.Close();
}
//查询宿舍所在楼层
public DataTable findByBuild(dorm d)
{
SqlConnection conn = new SqlConnection("server=" + MyGlobal.ip + ";database=dormitory;UID=sa;PWD=zyh@197068;Integrated Security=False");
conn.Open();
string mysql = string.Format("select buildName from dorm inner join building on dorm.fk_buildid = building.buildId where dormId = {0}", d.dormId);
SqlCommand sql = new SqlCommand(mysql, conn);
SqlDataReader reader = sql.ExecuteReader();
DataTable dt = new DataTable();
while (reader.Read())//决定行数
{
dt.Columns.Add(reader[0].ToString());
}
reader.Close();
conn.Close();
return(dt);
}
/// <summary>
/// 按类型名称进行模糊查询
/// 可以查询到用户所需要的数据
/// </summary>
/// <param name="d"></param>
/// <returns></returns>
public int count(dorm d)
{
SqlConnection conn = new SqlConnection("server=" + MyGlobal.ip + ";database=dormitory;UID=sa;PWD=zyh@197068;Integrated Security=False");
conn.Open();
string mysql = string.Format("select * from dorm where dormNum like '%{0}%' and flag = {1}", d.dormNum, d.flag);
SqlCommand sql = new SqlCommand(mysql, conn);
SqlDataReader reader = sql.ExecuteReader();
DataTable dt = new DataTable();
while (reader.Read())//决定行数
{
dt.Columns.Add(reader.ToString());
}
reader.Close();
conn.Close();
return(dt.Rows.Count);
}
/// <summary>
/// 按类型名称进行模糊查询
/// 可以查询到用户所需要的数据
/// </summary>
/// <param name="d"></param>
/// <param name="page"></param>
/// <returns></returns>
public DataTable findAll(dorm d, PageHelper page)
{
SqlConnection conn = new SqlConnection("server=" + MyGlobal.ip + ";database=dormitory;UID=sa;PWD=zyh@197068;Integrated Security=False");
conn.Open();
string mysql = string.Format("select dormId,dormNum,personCount from dorm where dormNum like '%{0}%' and flag = {1} order by dormId asc limit {2},{3}",
d.dormNum, d.flag, page.getBegin(), page.pageSize);
SqlCommand sql = new SqlCommand(mysql, conn);
SqlDataReader reader = sql.ExecuteReader();
DataTable dt = new DataTable();
while (reader.Read())//决定行数
{
dt.Columns.Add(reader.ToString());
}
reader.Close();
conn.Close();
return(dt);
}
/// <summary>
/// 修改,宿舍是否能入住
/// </summary>
/// <param name="d"></param>
public void updateFlag(dorm d)
{
SqlConnection conn = new SqlConnection("server=" + MyGlobal.ip + ";database=dormitory;UID=sa;PWD=zyh@197068;Integrated Security=False");
conn.Open();
string mysql = string.Format("update dorm set flag=2 where dormId={0}", d.dormId);
try
{
SqlCommand sql = new SqlCommand(mysql, conn);
SqlDataReader reader = sql.ExecuteReader();
reader.Close();
conn.Close();
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show("该项正在被使用，无法修改！", "温馨提示", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Asterisk);
}
}
/***************变量声明结束*************************/
private void btnSubmit_Click(object sender, EventArgs e)
{
//接受页面文本框参数
dorm d = new dorm();
d.dormId = this.txtDormId.Text;
string reply1 = "亲，宿舍名不能为空哦！";
string reply2 = "亲，人数不能为空哦！";
string reply3 = "亲，价格不能为空哦！";
if (this.cboFlag.Text == "可以入住")
{
d.flag = "1";
}
else if (this.cboFlag.Text == "不可入住")
{
d.flag = "2";
}
d.memo = this.txtMemo.Text;
//处理下拉框（外键）
d.fk_typeId = this.cboDormType.SelectedValue.ToString();
d.fk_buildId = this.cboBuilding.SelectedValue.ToString();
d.dormNum = this.txtDormNum.Text;
d.personCount = this.txtPersonCount.Text;
if (Validation.checktxt(d.dormNum, ref reply1))
{
return;
}
if (Validation.checktxt(d.personCount, ref reply2))
{
return;
}
//把修改后的数据加入数据库
wsm.update(d);
//刷新manager窗体的dgv
SingleWindow.dmf.flush();
//关闭当前窗体
this.Close();
MessageBox.Show("修改成功！", "温馨提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
/***************变量声明结束*************************/
public void flush()
{
//创建实体类对象
dorm d = new dorm();
//处理界面参数
d.dormNum = this.txtTypeName.Text;
//d.flag = "1";
if (this.cboFlag.Text == "可以入住")
{
d.flag = "1";
}
else if (this.cboFlag.Text == "不可入住")
{
d.flag = "2";
}
page.count = wsm.count(d);
//将数据库中的内容显示到界面上
this.dgvTeacher.DataSource = wsm.findAll(d, page);
//给页面上的控件赋值
this.lblPage.Text = "当前：" + page.nowPage + "页 共：" + page.getTotalPage() + "页 总计：" + page.count + "条";
}