public void InsertStudent(Student student)
{
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spAddStudents";
cmd.Connection = conn;
cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = student.Name;
cmd.Parameters.Add("@dob", SqlDbType.Date).Value = student.DOB;
cmd.Parameters.Add("@gpa", SqlDbType.Decimal).Value = student.GradePointAverage;
cmd.Parameters.Add("@isActive", SqlDbType.Bit).Value = student.IsActive;
conn.Open();
cmd.ExecuteNonQuery();
}
/// <summary>
/// 获得数据列表
/// </summary>
public static List<Student> GetStudentList()
{
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
SqlDataReader sqlDataReader = null;
List<Student> studentList = new List<Student>();
StringBuilder strSql = new StringBuilder();
strSql.Append("select Stu_Number,Stu_Name,Extra ");
strSql.Append(" FROM Students ");
//if (strWhere.Trim() != "")
//{
// strSql.Append(" where " + strWhere);
//}
SqlCommand cmd = new SqlCommand(strSql.ToString(), conn);
sqlDataReader = cmd.ExecuteReader();
while (sqlDataReader.Read())
{
//循环读出所有学生
Student student = new Student((string)sqlDataReader["Stu_Number"]);
student.Name = (string)sqlDataReader["Stu_Name"];
student.Extra = (string)sqlDataReader["Extra"];
studentList.Add(student); //加入学生集合
}
conn.Close();
return studentList;
}
/// <summary>
/// 更新一条数据
/// </summary>
public static bool UpdateStudent(Student model)
{
int number;
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
SqlCommand cmd = null;
StringBuilder strSql = new StringBuilder();
strSql.Append("update Students set ");
strSql.Append("Stu_Name=@Stu_Name,");
strSql.Append("Extra=@Extra");
strSql.Append(" where Stu_Number=@Stu_Number ");
SqlParameter[] parameters = {
new SqlParameter("@Stu_Name", SqlDbType.NVarChar,20),
new SqlParameter("@Extra", SqlDbType.Text),
new SqlParameter("@Stu_Number", SqlDbType.NVarChar,20)};
parameters[0].Value = model.Name;
parameters[1].Value = model.Extra;
parameters[2].Value = model.Number;
cmd = new SqlCommand(strSql.ToString(), conn);
foreach (SqlParameter para in parameters)
{
cmd.Parameters.Add(para);
}
number = (int)cmd.ExecuteNonQuery();
conn.Close();
if (number == 0)
{
return false;
}
else
{
return true;
}
}
/// <summary>
/// 制造一个学生对象
/// </summary>
/// <returns></returns>
private Student CreateAStudent()
{
Student student = new Student(txtStudentNumber.Text);
student.Name = txtStudentName.Text;
student.Extra = txtStudentExtra.Text;
return student;
}