//提取所有学生的信息 public List <StdInfoModel> GetAllInformation() { string cmd = "SELECT [StdNum], [Name], [Sex], [Nation], [E_Mail], [PhoneNumber] FROM [T_StdInfo]"; DataTable dt = SQLCommand.ExecuteQuery(cmd); return(DateToList(dt)); }
//查询管理员账户信息的方法 public List <AdminInfoModel> GetAdminInfo() { string cmd = "SELECT [AdminName], [Password] FROM [T_Admin]"; DataTable dt = SQLCommand.ExecuteQuery(cmd); return(DateToList(dt)); }
//删除数据的方法 public int DeleteInfoByNum(string stdNum) { //拼接SQL语句 string cmdText = "DELETE FROM T_StdInfo WHERE StdNum=@Stdnum"; //创建SqlParameter对象 SqlParameter sp = new SqlParameter("@Stdnum", SqlDbType.Char, 12); sp.Value = stdNum; //返回影响行数 return(SQLCommand.ExecuteNonQuery(cmdText, sp)); }
//删除管理员账户的方法 public int DeleteAddmin(string userName) { //拼接SQL语句 string cmdText = "DELETE FROM T_Admin WHERE AdminName=@AdminName"; //创建SqlParameter对象 SqlParameter sp = new SqlParameter("@AdminName", SqlDbType.NVarChar, 24); sp.Value = userName; //返回影响行数 return(SQLCommand.ExecuteNonQuery(cmdText, sp)); }
//CRUD操作 //创建管理员账户的方法 public int AddAdmin(string userName, string password) { //拼接SQL语句 string cmdText = @"INSERT INTO T_Admin VALUES(@AdminName,@Password)"; //创建SqlParameter对象,获取参数 SqlParameter[] sp = new SqlParameter[2]; sp[0] = new SqlParameter("@AdminName", SqlDbType.NVarChar, 24); sp[0].Value = userName; sp[1] = new SqlParameter("@Password", SqlDbType.NVarChar, 20); sp[1].Value = password; //返回影响行数 return(SQLCommand.ExecuteNonQuery(cmdText, sp)); }
//更新数据的方法 public int UpdatePN(string phoneNumber, string name) { //拼接SQL语句 string cmdText = @"UPDATE T_StdInfo SET PhoneNumber=@NewPN WHERE Name=@Name"; //SqlParameter对象,获取参数“姓名” SqlParameter spName = new SqlParameter("@Name", SqlDbType.NVarChar, 6); spName.Value = name; //SqlParameter对象,获取参数“新电话号码” SqlParameter spPN = new SqlParameter("@NewPN", SqlDbType.Char, 11); spPN.Value = phoneNumber; //返回影响行数 return(SQLCommand.ExecuteNonQuery(cmdText, spPN, spName)); }
//查询数据的方法 public StdInfoModel GetInfoByNum(string stdNum) { string cmdText = @"SELECT * FROM T_StdInfo WHERE StdNum=@StdNum"; SqlParameter sp = new SqlParameter("@StdNum", SqlDbType.Char, 12); sp.Value = stdNum; DataSet ds = SQLCommand.GetDataSet(cmdText, sp); StdInfoDAL pd = new StdInfoDAL(); if (ds.Tables.Count > 0) { return(pd.DataRowToModel(ds.Tables[0].Rows[0])); } else { return(null); } }
public int UpdateInfo(string oldName, string stdNum, string name, string e_mail, string pn) { //拼接SQL语句 string cmdText = @"UPDATE T_StdInfo SET StdNum=@NewStdNum, Name=@NewName, E_Mail=@NewE_Mail, PhoneNumber=@NewPN WHERE Name=@OldName"; SqlParameter spStdNum = new SqlParameter("@NewStdNum", SqlDbType.NVarChar, 12); spStdNum.Value = stdNum; SqlParameter spName = new SqlParameter("@NewName", SqlDbType.NVarChar, 6); spName.Value = name; SqlParameter spEmail = new SqlParameter("@NewE_Mail", SqlDbType.NChar, 100); spEmail.Value = e_mail; SqlParameter spPN = new SqlParameter("@NewPN", SqlDbType.Char, 11); spPN.Value = pn; SqlParameter spOldName = new SqlParameter("@OldName", SqlDbType.NVarChar, 6); spOldName.Value = oldName; return(SQLCommand.ExecuteNonQuery(cmdText, spStdNum, spName, spEmail, spPN, spOldName)); }
//CRUD操作 //添加数据的方法 public int AddInfo(string stdNum, string name, string sex, string nation, string e_mail, string pn) { //拼接SQL语句 string cmdText = @"INSERT INTO T_StdInfo VALUES(@StdNum,@Name,@Sex,@Nation,@E_Mail,@PhoneNumber)"; //创建SqlParameter对象,获取参数 SqlParameter[] sp = new SqlParameter[6]; sp[0] = new SqlParameter("@StdNum", SqlDbType.Char, 12); sp[0].Value = stdNum; sp[1] = new SqlParameter("@Name", SqlDbType.NVarChar, 6); sp[1].Value = name; sp[2] = new SqlParameter("@Sex", SqlDbType.NChar, 2); sp[2].Value = sex; sp[3] = new SqlParameter("@Nation", SqlDbType.NVarChar, 10); sp[3].Value = nation; sp[4] = new SqlParameter("@E_Mail", SqlDbType.NVarChar, 100); sp[4].Value = e_mail; sp[5] = new SqlParameter("@PhoneNumber", SqlDbType.Char, 11); sp[5].Value = pn; //返回影响行数 return(SQLCommand.ExecuteNonQuery(cmdText, sp)); }