public void UpdatePlanBook(PlanBook plan, string isbn) //修改预定教材
{
using (SqlConnection conn = new SqlConnection(DBHelpers.Connection))
{
conn.Open();
string sql = "update PlanBook set CourseID=@CourseID,MajorInfoID=@MajorInfoID,CollegeID=@CollegeID,StudentClassID=@StudentClassID,SchoolTermID=@SchoolTermID,CourseName=@CourseName,BookName=@BookName,ISBN=@ISBN,Author=@Author,Publish=@Publish,Price=@Price,YearMonth=@YearMonth,TeacherBookNum=@TeacherBookNum,StudentBookNum=@StudentBookNum,BookTotalNum=@BookTotalNum where ISBN=@isb";
SqlCommand scm = new SqlCommand(sql, conn);
scm.Parameters.AddWithValue("@CourseID", plan.CourseID);
scm.Parameters.AddWithValue("@MajorInfoID", plan.MajorInfoID);
scm.Parameters.AddWithValue("@CollegeID", plan.CollegeID);
scm.Parameters.AddWithValue("@StudentClassID", plan.StudentClassID);
scm.Parameters.AddWithValue("@SchoolTermID", plan.SchoolTermID);
scm.Parameters.AddWithValue("@CourseName", plan.CourseName);
scm.Parameters.AddWithValue("@BookName", plan.BookName);
scm.Parameters.AddWithValue("@ISBN", plan.ISBN);
scm.Parameters.AddWithValue("@Author", plan.Author);
scm.Parameters.AddWithValue("@Publish", plan.Publish);
scm.Parameters.AddWithValue("@Price", plan.Price);
scm.Parameters.AddWithValue("@YearMonth", plan.YearMonth);
scm.Parameters.AddWithValue("@TeacherBookNum", plan.TeacherBookNum);
scm.Parameters.AddWithValue("@StudentBookNum", plan.StudentBookNum);
scm.Parameters.AddWithValue("@BookTotalNum", plan.BookTotalNum);
scm.Parameters.AddWithValue("@isb", isbn);
scm.ExecuteNonQuery();
}
}
/// <summary>
/// 添加发放明细的方法
/// </summary>
/// <param name="book">PlanBook实体类</param>
/// <param name="nums">自定义的一个int类型的数组</param>
public void InsertGrantBook(PlanBook book, int[] nums)
{
string str = string.Join(",", nums);
string sql = string.Format("INSERT INTO GrantBook(PlanBookID, StudentInfoID, SchoolTermID, GetBookNum) SELECT {0} AS Expr1, StudentInfo.StudentInfoID , {1} AS Expr1, 1 AS Expr1 FROM StudentInfo where StudentInfo.StudentInfoID in({2})", book.PlanBookID, book.SchoolTermID, str);
DBHelpers.UpdateInfo(sql);
}
/// <summary>
/// 添加教材明细按钮的单击事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnAddGrantBook_Click(object sender, EventArgs e)
{
int planbookid = Convert.ToInt32(dgvBookStock.CurrentRow.Cells["教材编号"].Value); //获取教材库存信息列表dgvBookStock中当前选中行的 列名为教材编号 的值
List <int> nums = new List <int>(); //自定义一个int类型的数组
for (int i = 0; i < dgvStudentInfo.Rows.Count; i++) //遍历学生信息列表dgvStudentInfo的所有行
{
try
{
if (Convert.ToBoolean(dgvStudentInfo.Rows[i].Cells["选择"].Value)) //选中学生信息列表dgvStudentInfo中 列名为选择 的值 为真值
{
//MessageBox.Show(dgvStudentInfo.CurrentRow.Cells["学生信息编号"].Value.ToString());
nums.Add(Convert.ToInt32(dgvStudentInfo.Rows[i].Cells["学生信息编号"].Value)); //添加符合条件的 信息列表dgvStudentInfo中 列名为学生信息编号 的值 到数组中
}
}
catch (Exception)
{
//throw;
}
}
PlanBook book = new PlanBook(); //实例化教材预定实体类
book.PlanBookID = Convert.ToInt32(dgvBookStock.CurrentRow.Cells["教材编号"].Value); //为字段赋值
book.SchoolTermID = Convert.ToInt32(dgvBookStock.CurrentRow.Cells["学期编号"].Value); //为字段赋值
bllbookbusiness.InsertGrantBook(book, nums.ToArray()); //调用业务逻辑层中的 添加教材发放明细InsertGrantBook()方法
dgvGrantBookAllInfo(); //重新绑定数据源
}
public void InsertPlanBook(PlanBook plan) //添加教材预定
{
using (SqlConnection conn = new SqlConnection(DBHelpers.Connection))
{
conn.Open();
string sql1 = "insert into PlanBook values(@CourseID,@MajorInfoID,@CollegeID,@StudentClassID,@SchoolTermID,@CourseName,@BookName,@ISBN,@Author,@Publish,@Price,@YearMonth,@TeacherBookNum,@StudentBookNum,@BookTotalNum,'false',null)";
SqlCommand scm = new SqlCommand(sql1, conn);
scm.Parameters.AddWithValue("@CourseID", plan.CourseID);
scm.Parameters.AddWithValue("@MajorInfoID", plan.MajorInfoID);
scm.Parameters.AddWithValue("@CollegeID", plan.CollegeID);
scm.Parameters.AddWithValue("@StudentClassID", plan.StudentClassID);
scm.Parameters.AddWithValue("@SchoolTermID", plan.SchoolTermID);
scm.Parameters.AddWithValue("@CourseName", plan.CourseName);
scm.Parameters.AddWithValue("@BookName", plan.BookName);
scm.Parameters.AddWithValue("@ISBN", plan.ISBN);
scm.Parameters.AddWithValue("@Author", plan.Author);
scm.Parameters.AddWithValue("@Publish", plan.Publish);
scm.Parameters.AddWithValue("@Price", plan.Price);
scm.Parameters.AddWithValue("@YearMonth", plan.YearMonth);
scm.Parameters.AddWithValue("@TeacherBookNum", plan.TeacherBookNum);
scm.Parameters.AddWithValue("@StudentBookNum", plan.StudentBookNum);
scm.Parameters.AddWithValue("@BookTotalNum", plan.BookTotalNum);
scm.ExecuteNonQuery();
conn.Close();
}
}
/// <summary>
/// 获取文本框的值赋给实体类里面变量的方法
/// </summary>
/// <returns></returns>
private PlanBook txtText()
{
PlanBook plan = new PlanBook(); //初始化实体类PlanBook
plan.CourseName = txtCourseName.Text.Trim(); //为实体类里面的字段赋值
plan.BookName = txtBookName.Text.Trim();
plan.Author = txtAuthor.Text.Trim();
plan.ISBN = txtISBN.Text.Trim();
plan.Price = float.Parse(txtPrice.Text.Trim());
plan.Publish = txtPublish.Text.Trim();
plan.StudentBookNum = int.Parse(txtStudentBookNum.Text.Trim());
plan.TeacherBookNum = int.Parse(txtTeacherBookNum.Text.Trim());
StudentClass student = common.GetAllCollegeInfo_Class(cmbStudentClass.Text); //以下拉框班级的文本值作参数调用方法GetAllCollegeInfo_Class（）并赋值给实体类
plan.MajorInfoID = student.MajorInfoID; //获取实体类StudentClass中的专业编号赋值给实体类PlanBook中的专业编号
plan.StudentClassID = student.StudentClassID; //获取实体类StudentClass中的学院编号赋值给实体类PlanBook中的学院编号
plan.SchoolTermID = cmbSchoolTerm.SelectedIndex; //获取下拉框学年学期的索引值并赋值给实体类PlanBook里面的SchoolTermID字段
plan.CourseID = cmbCourseType.SelectedIndex; //获取下拉框课程类型的索引值并赋值给实体类PlanBook里面的CourseID字段
plan.CollegeID = cmbCollege.SelectedIndex; //获取下拉框学院名称的索引值并赋值给实体类PlanBook里面的CollegeID字段
plan.YearMonth = DateTime.Now;
plan.BookTotalNum = plan.StudentBookNum + plan.TeacherBookNum;
return(plan);
}
/// <summary>
/// 向数据库中添加一条记录的方法
/// </summary>
private void InsertPlanBook()
{
PlanBook plan = new PlanBook(); //初始化实体类PlanBook
try
{ //txtText()方法为本类中返回类型是PlanBook实体类的方法
planbook.InsertPlanBook(txtText()); //以本类中的txtText()方法为参数调用业务逻辑层中类BLLPlanBook中的方法UpdatePlanBook
}
catch (Exception)
{
MessageBox.Show("请检查信息是否填写完整或者格式是否错误");
//throw;
}
}
/// <summary>
/// 添加发放明细的方法
/// </summary>
/// <param name="book">PlanBook实体类</param>
/// <param name="nums">自定义的一个int类型的数组</param>
public void InsertGrantBook(PlanBook book, int[] nums)
{
getbookbusiness.InsertGrantBook(book, nums);
}
public void UpdatePlanBook(PlanBook plan, string isbn) //删除预定教材
{
planbook.UpdatePlanBook(plan, isbn);
}
public void InsertPlanBook(PlanBook plan)
{
planbook.InsertPlanBook(plan);
}
