/// <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
}
/// <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">从业务逻辑层传入实体</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>
/// 根据选中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>
/// 插入学生的信息
/// </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
}
public void StInfoUpdate_Load(object sender, EventArgs e)
{
#region 生日连动选择：代码块（一）；
cmbYear.Items.Clear();
for (int year = 1900; year <= 2020; year++)
{
cmbYear.Items.Add(year);
}
#endregion
Stmodel model = Bll.GetStInfo(this.Sid);
this.txtName.Text = model.Sname;
this.cmbYear.Text = model.Sbirthday.ToString().Substring(0, 4);
#region 将数据库封装好的model中Sbirthday切割出来年份的“月数”显示在combox选项中：
if (model.Sbirthday.ToString().Substring(6, 1) == "/")//当从model返回的月份是一位数的进行剪切前边读到的斜杠“/”
{
cmbMonth.Text = model.Sbirthday.ToString().Substring(5, 1);
}
else
{
cmbMonth.Text = model.Sbirthday.ToString().Substring(5, 2);
}
#endregion
#region 将数据库封装好的model中Sbirthday切割出来每月份的“天数”显示在combox选项中：
if (model.Sbirthday.ToString().Substring(7, 1) == "/" && model.Sbirthday.ToString().Substring(9, 1) == "")//2000/12/1
{
this.cmbDay.Text = model.Sbirthday.ToString().Substring(8, 1);
}
else if (model.Sbirthday.ToString().Substring(6, 1) == "/" && model.Sbirthday.ToString().Substring(8, 1) == "") //2000/1/1
{
this.cmbDay.Text = model.Sbirthday.ToString().Substring(7, 1);
}
else if (model.Sbirthday.ToString().Substring(6, 1) == "/" && model.Sbirthday.ToString().Substring(8, 1) != "") //2000/1/12
{
this.cmbDay.Text = model.Sbirthday.ToString().Substring(7, 2);
}
else//2000/12/12
{
this.cmbDay.Text = model.Sbirthday.ToString().Substring(8, 2);
}
#endregion
if (model.Sgender.Equals(true))//bool值是否是true
{
this.radioMan.Checked = true;
}
else
{
this.radioWomen.Checked = true;
}
if (model.Sheight.ToString() == "0.00")
{
this.txtHeight.Text = null;
}
else
{
this.txtHeight.Text = model.Sheight.ToString();
}
if (model.Sweight.ToString() == "0")
{
this.txtWeight.Text = null;
}
else
{
this.txtWeight.Text = model.Sweight.ToString();
}
this.txtAddress.Text = model.Saddress;
}
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);
}
}
