/// <summary>
/// 添加学生信息
/// </summary>
/// <param name="model">从业务逻辑层传入实体</param>
/// <returns>返回受影响的行数</returns>
public int StInfoAdd(Stmodel model)
{
string cmdText = "insert into Students(Sname, Sbirthday, Sgender, Sheight, Sweight, Saddress) values(@Sname, @Sbirthday, @Sgender, @Sheight, @Sweight, @Saddress)";
SqlParameter[] param ={ new SqlParameter("@Sname",model.Sname), //param不要使用关键字了
new SqlParameter("@Sbirthday",model.Sbirthday),
new SqlParameter("@Sgender",model.Sgender),
new SqlParameter("@Sheight",model.Sheight),
new SqlParameter("@Sweight",model.Sweight),
new SqlParameter("@Saddress",model.Saddress) };
return sqlHelper.ExcuteNonQuery(cmdText, param);//返回插入后受影响的行数
}
/// <summary>
/// 根据id更新当前用户的信息
/// </summary>
/// <param name="model">model的所有属性</param>
/// <returns>是否修改成功bool值</returns>
public bool StInfoUpdate(Stmodel model)//接收StInfoUpdate UI层获取的model实体类
{
if (Dal.StInfoUpdate(model) > 0)//将实体model传递到Dal更新方法中去
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 插入学生的信息
/// </summary>
/// <param name="model">从UI层传递的要插入的实体</param>
/// <returns>是否是插入成功的bool值</returns>
public bool StInfoAdd(Stmodel model)
{
//调用数据访问层
int state = Dal.StInfoAdd(model);
if (state > 0)
{
return true;
}
else
{
return false;
}
}
public List<Stmodel> GetStInfoList()
{
string cmdText = "select Sid, Sname, Sbirthday, Sgender, Sheight, Sweight, Saddress from Students";
DataTable tb = sqlHelper.ExecuteDataTable(cmdText);
List<Stmodel> list = new List<Stmodel>();
if (tb.Rows.Count > 0)
{
foreach (DataRow row in tb.Rows)
{
Stmodel model = new Stmodel();
model.Sid = Convert.ToInt32(row["Sid"]);//不为空
model.Sname = Convert.ToString(row["Sname"]);//不为空
model.Sbirthday = DateTime.Parse(row["Sbirthday"].ToString());//不为空
if (row["Sgender"].ToString().ToLower() == "true")//不为空
{
model.Sgender = true;
}
else
{
model.Sgender = false;
}
//model.Sheight = Convert.IsDBNull(row["Sheight"]) ? null : (decimal?)row["Sheight"];
if (row["Sheight"] == DBNull.Value)
{
model.Sheight = null;
}
else
{
model.Sheight = (decimal?)row["Sheight"];
}
if (row["Sweight"] == DBNull.Value)
{
model.Sweight = null;
}
else
{
model.Sweight = (int?)row["Sweight"];
}
//model.Sweight = Convert.IsDBNull(row["Sweight"]) ? null : (int?)row["Sweight"];
model.Saddress = Convert.IsDBNull(row["Saddress"]) ? null : row["Saddress"].ToString();
list.Add(model);
}
}
return list;
}
/// <summary>
/// 获取用户输入的内容，并封装到model中
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnUpadate_Click(object sender, EventArgs e)
{
Stmodel model = new Stmodel();
#region ***************对非空选项:为空，就报警！*************
if (string.IsNullOrEmpty(this.txtName.Text.Trim()))
{
MessageBox.Show("姓名不能为空！", "SQL提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
string STR_Birthday = this.cmbYear.Text.Trim() + "-" + this.cmbMonth.Text.Trim() + "-" + this.cmbDay.Text.Trim();
if (string.IsNullOrEmpty(cmbYear.Text.Trim()) || string.IsNullOrEmpty(cmbYear.Text.Trim()) || string.IsNullOrEmpty(cmbDay.Text.Trim()))
{
MessageBox.Show("年、月、日不能为空！", "SQL提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (this.radioMan.Checked.Equals(false) && this.radioWomen.Checked.Equals(false))
{
MessageBox.Show("性别不能为空！", "SQL提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
#endregion
#region ********对可空的选型：如果内容为空，不做任何处理；否则，进行格式验证***************
IsOkForm yanzheng = new IsOkForm();
//身高是可以为空的
if (string.IsNullOrEmpty(this.txtHeight.Text))
{
model.Sheight = null;//为空赋值为null
}
else if (!(yanzheng.IsDecimal(this.txtHeight.Text)))//不为空就验证,验证通过就在下面继续赋值
{
MessageBox.Show("身高非数字或格式不正确！", "SQL提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//体重是可以为空的
if (string.IsNullOrEmpty(this.txtWeight.Text))
{
model.Sweight = null;//为空赋值为null
}
else if (!(yanzheng.IsIntNumber(this.txtWeight.Text) && yanzheng.IsIntNumberLength(this.txtWeight.Text)))//不为空就验证,验证通过就在下面继续赋值
{
MessageBox.Show("体重为非数字或格式不正确！", "SQL提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
#endregion
#region 在这里进行这样的故事：1.数据的类型转换（难点五星级）2.文本输入的格式控制（自己百度的！））
string bithday = STR_Birthday;
bool gender = false;
if (this.radioMan.Checked)
{
gender = true;
}
string heightYanZhengHou = this.txtHeight.Text.Trim();
string weightYanZhengHou = this.txtWeight.Text.Trim();
model.Sname = this.txtName.Text.Trim();
model.Sbirthday = DateTime.Parse(Convert.ToDateTime(bithday).ToString("yyyy-MM-dd"));
model.Sgender = gender;
#region 对身高、体重使用Decimal.TryParse(),Int32.TryParse(）这种转化方法，不使用Convert.toDecimal()这种，因为这个转化处理为空的情况，报异常多（能让你抓狂！！）
decimal result = 0;
if (Decimal.TryParse(heightYanZhengHou, out result))
{
model.Sheight = result;
}
else
{
model.Sheight = 0;
}
int result1 = 0;
if (Int32.TryParse(weightYanZhengHou, out result1))
{
model.Sweight = result1;
}
else
{
model.Sweight = 0;
}
#endregion
//model.Sweight = Convert.ToInt32(weightYanZhengHou);//就是这个：不建议使用的方法！！
model.Saddress = this.txtAddress.Text.Trim();
#endregion
#region 调用业务逻辑层的返回值（可以这么讲，有没有添加成功：业务逻辑层StInfoAdd（）方法说了算数；
if (Bll.StInfoAdd(model))//业务逻辑层返回时为"真"就是添加成功
{
MessageBox.Show("添加成功！", "SQL提示", MessageBoxButtons.OK);
this.Hide();
Main main = new Main();
main.Show();
}
else
{
MessageBox.Show("添加失败！", "SQL提示", MessageBoxButtons.OK);
}
#endregion
}
/// <summary>
/// 根据选中MyDataList表中id来更新当前用户的信息
/// </summary>
/// <param name="model">model中的id</param>
/// <returns>返回受影响的函数</returns>
public int StInfoUpdate(Stmodel model)
{
string cmdText = "update Students set Sname=@Sname,Sbirthday=@Sbirthday,Sgender=@Sgender,Sheight=@Sheight,Sweight=@Sweight,Saddress=@Saddress where Sid=@Sid";
SqlParameter[] param ={ new SqlParameter("@Sname",model.Sname),
new SqlParameter("@Sbirthday",model.Sbirthday),
new SqlParameter("@Sgender",model.Sgender),
new SqlParameter("@Sheight",model.Sheight),
new SqlParameter("@Sweight",model.Sweight),
new SqlParameter("@Saddress",model.Saddress),
new SqlParameter("@Sid",model.Sid)};
return sqlHelper.ExcuteNonQuery(cmdText, param);//返回更新过后受影响的行数
}
/// <summary>
/// 根据id从数据库中查询记录信息
/// </summary>
/// <param name="id">从业务逻辑层传过来的id值</param>
/// <returns>这个id对应的数据库中保存的信息</returns>
public Stmodel GetStInfo(int id)
{
//根据id查信息
string cmdText = "select Sid, Sname, Sbirthday, Sgender, Sheight, Sweight, Saddress from Students where Sid=@Sid ";
SqlParameter param = new SqlParameter("@Sid", id);
SqlDataReader record = sqlHelper.ExecuteDataReader(cmdText, param);//一条条记录的查询
//查询结果并且封装到一个实例化的实体model中
Stmodel model = new Stmodel();
while (record.Read())//数据表中记录不是一条，所以要循环读取
{
model.Sid = Convert.ToInt32(record["Sid"]);//不为空
model.Sname = Convert.ToString(record["Sname"]);//不为空
model.Sbirthday = DateTime.Parse(record["Sbirthday"].ToString());//不为空
if (record["Sgender"].ToString().ToLower() == "true")//不为空
{
model.Sgender = true;
}
else
{
model.Sgender = false;
}
model.Sheight = Convert.IsDBNull(record["Sheight"]) ? null : (decimal?)record["Sheight"];
model.Sweight = Convert.IsDBNull(record["Sweight"]) ? null : (int?)record["Sweight"];
model.Saddress = Convert.IsDBNull(record["Saddress"]) ? null : record["Saddress"].ToString();
}
record.Close();//记得关闭这个对象
return model;//将记录中的值封装到了model中，返回model
}
private void btnUpadate_Click(object sender, EventArgs e)
{
//点击“更新”按钮事件：将修该过的每个属性封装为实体model传递到Bll层
Stmodel model = new Stmodel();
IsOkForm yanzheng = new IsOkForm();
#region ***************对非空选项:为空，就报警！*************
if (string.IsNullOrEmpty(this.txtName.Text.Trim()))
{
MessageBox.Show("姓名不能为空！", "SQL提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
string STR_Birthday = cmbYear.Text.Trim() + "-" + cmbMonth.Text.Trim() + "-" + cmbDay.Text.Trim();
//如果年、月、日有一个是空的就报警！
if (string.IsNullOrEmpty(cmbYear.Text.Trim()) || string.IsNullOrEmpty(cmbYear.Text.Trim()) || string.IsNullOrEmpty(cmbDay.Text.Trim()))
{
MessageBox.Show("年、月、日不能为空！", "SQL提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (this.radioMan.Checked.Equals(false) && this.radioWomen.Checked.Equals(false))
{
MessageBox.Show("性别不能为空！", "SQL提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
#endregion
#region ********对可空的选型处理：如果内容为空，不做任何处理；否则，进行格式验证***************
if (string.IsNullOrEmpty(this.txtHeight.Text))
{
model.Sheight = null;//为空赋值为null
}
else if (!(yanzheng.IsDecimal(this.txtHeight.Text)))//不为空就验证,验证通过就在下面继续赋值
{
MessageBox.Show("身高非数字或格式不正确！", "SQL提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//体重是可以为空的
if (string.IsNullOrEmpty(this.txtWeight.Text))
{
model.Sweight = null;//为空赋值为null
}
else if (!(yanzheng.IsIntNumber(this.txtWeight.Text) && yanzheng.IsIntNumberLength(this.txtWeight.Text)))//不为空就验证,验证通过就在下面继续赋值
{
MessageBox.Show("体重为非数字或格式不正确！", "SQL提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
#endregion
string heightYanZhengHou = this.txtHeight.Text.Trim();
string weightYanZhengHou = this.txtWeight.Text.Trim();
model.Sname = this.txtName.Text;
model.Sbirthday = DateTime.Parse(Convert.ToDateTime(STR_Birthday).ToString("yyyy-MM-dd"));
if (this.radioMan.Checked == true)//若是Man被选中，就给model赋值为true
{
model.Sgender.Equals(true);
}
else
{
model.Sgender.Equals(false);
};
#region 对身高、体重使用.TryParse(),不使用Convert.toDecimal()这种，因为这个转化处理为空，报异常多
decimal result = 0;
if (Decimal.TryParse(heightYanZhengHou, out result))
{
model.Sheight = result;
}
else
{
model.Sheight = 0;
}
int result1 = 0;
if (Int32.TryParse(weightYanZhengHou, out result1))
{
model.Sweight = result1;
}
else
{
model.Sweight = 0;
}
#endregion
model.Saddress = this.txtAddress.Text;
model.Sid = Sid;//id还没有，所以要把id传进来,通过构造函数来传递
//通过业务逻辑层中的StInfoUpdate()将封装好的model进行处理，根据Bll处理结果写如下
if (Bll.StInfoUpdate(model))
{
MessageBox.Show("修改成功！", "SQL提示", MessageBoxButtons.OK);
this.Hide();
Main main = new Main();
main.Show();
}
else
{
MessageBox.Show("修改失败！", "SQL提示", MessageBoxButtons.OK);
}
}