/// <summary>
/// 插入数据
/// </summary>
/// <param name="t">模型类</param>
/// <returns>受影响的行数</returns>
public int Add(teacher t)
{
string sql = "INSERT INTO teacher(tcid,tname,tProfession,tpassword) VALUES(@tcid,@tname,@tProfession,@tpassword);";
SqlParameter[] param =
{
new SqlParameter("@tcid", SqlDbType.Int)
{
Value = t.tcid
},
new SqlParameter("@tname", SqlDbType.VarChar, 20)
{
Value = t.tname
},
new SqlParameter("@tProfession", SqlDbType.Char, 1)
{
Value = t.tProfession
},
new SqlParameter("@tpassword", SqlDbType.Char, 6)
{
Value = t.tpassword
}
};
return(ADOTools.ExecuteNonQuery(sql, param));
}
//SqlParameter 方法
public int AddStudentParams(Student s)
{
//判断选中的性别radioButton进行性别变量赋值
int sex = 0; //存储性别值
if (radioButtonNan.Checked == true & radioButtonNv.Checked == false)
{
sex = 1;
} //男
else if (radioButtonNv.Checked == true & radioButtonNan.Checked == false)
{
sex = 0;
} //女
string strSql = "INSERT INTO student VALUES('" + textBoxSname.Text + "'," + sex + "," + Convert.ToInt32(textBoxSage.Text) + ",'" + dateTimePickerSbirthday.Text + "','" + textBoxSphone.Text + "','" + textBoxSaddress.Text + "','" + this.comboBoxSbanji.SelectedIndex + 1 + "'); ";
SqlParameter[] sp =
{
new SqlParameter("@sname", SqlDbType.VarChar),
new SqlParameter("@ssex", SqlDbType.Int),
new SqlParameter("@sage", SqlDbType.Int),
new SqlParameter("@sbirthday", SqlDbType.VarChar),
new SqlParameter("@sphone", SqlDbType.VarChar),
new SqlParameter("@saddress", SqlDbType.VarChar),
new SqlParameter("@cid", SqlDbType.Int)
};
sp[0].Value = s.sname;
sp[1].Value = sex;
sp[2].Value = s.sage;
sp[3].Value = s.sbirthday;
sp[4].Value = s.sphone;
sp[5].Value = s.saddress;
sp[6].Value = s.scid;
return(ADOTools.ExcuteNoQuery(strSql, CommandType.Text, sp));
}
/// <summary>
/// 分页查询数据
/// </summary>
/// <param name="pageSize">每页展示数据的条数</param>
/// <param name="pageIndex">当前的页码数</param>
/// <returns></returns>
public DataTable GetDataByPage(int pageSize, int pageIndex)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT TOP " + pageSize + " ");
strSql.Append(" S.sid AS '学生ID', ");
strSql.Append(" S.sname AS '学生姓名', ");
strSql.Append(" ( CASE S.ssex ");
strSql.Append(" WHEN'1' THEN'男' ");
strSql.Append(" WHEN'0' THEN'女' ");
strSql.Append(" ELSE '双性人' ");
strSql.Append(" END) AS '学生性别', ");
strSql.Append(" S.sage AS '学生年龄', ");
strSql.Append(" (CAST(YEAR(S.sbirthday) AS CHAR(4)) + '年' + ");
strSql.Append(" CAST(MONTH(S.sbirthday) AS VARCHAR(2)) + '月' + ");
strSql.Append(" CAST(DAY(S.sbirthday) AS VARCHAR(2)) + '日' ) AS '学生生日', ");
strSql.Append(" S.sphone AS '学生电话', ");
strSql.Append(" S.saddress AS '学生地址', ");
strSql.Append(" C.cname AS '学生班级', ");
strSql.Append(" S.spassword AS '学生登录密码' ");
strSql.Append(" FROM student AS S ");
strSql.Append(" LEFT JOIN classInfo AS C ON C.cid = S.scid ");
strSql.Append(" WHERE sid NOT IN( ");
strSql.Append(" SELECT TOP (" + pageSize + "*(" + pageIndex + "-1)) sid FROM student ORDER BY sid ASC ");
strSql.Append(" ) ORDER BY sid ASC ");
return(ADOTools.Query(strSql.ToString(), null));
}
/// <summary>
/// 读取数据
/// </summary>
/// <param name="dt">临时数据表</param>
/// <returns>包含数据的临时数据表</returns>
public DataTable GetDB(DataTable dt)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT ");
strSql.Append(" S.sid AS '学生ID', ");
strSql.Append(" S.sname AS '学生姓名', ");
strSql.Append(" ( CASE S.ssex ");
strSql.Append(" WHEN'1' THEN'男' ");
strSql.Append(" WHEN'0' THEN'女' ");
strSql.Append(" ELSE '双性人' ");
strSql.Append(" END) AS '学生性别', ");
strSql.Append(" S.sage AS '学生年龄', ");
strSql.Append(" (CAST(YEAR(S.sbirthday) AS CHAR(4)) + '年' + ");
strSql.Append(" CAST(MONTH(S.sbirthday) AS VARCHAR(2)) + '月' + ");
strSql.Append(" CAST(DAY(S.sbirthday) AS VARCHAR(2)) + '日' ) AS '学生生日', ");
strSql.Append(" S.sphone AS '学生电话', ");
strSql.Append(" S.saddress AS '学生地址', ");
strSql.Append(" C.cname AS '学生班级', ");
strSql.Append(" S.spassword AS '学生登录密码' ");
strSql.Append(" FROM student AS S ");
strSql.Append(" LEFT JOIN classInfo AS C ON C.cid = S.scid ");
dt = ADOTools.ExcuteDataTable(strSql.ToString());
return(dt);
}
/// <summary>
/// 更新数据
/// </summary>
/// <param name="sc">模型类</param>
/// <returns>受影响的行数</returns>
public int Update(SC_Mapping sc)
{
string sql = "UPDATE SC_Mapping SET sid = @sid,cid = @cid,score = @score WHERE scid = @scid;";
SqlParameter[] param =
{
new SqlParameter("@scid", SqlDbType.Int)
{
Value = sc.scid
},
new SqlParameter("@sid", SqlDbType.Int)
{
Value = sc.sid
},
new SqlParameter("@cid", SqlDbType.Int)
{
Value = sc.cid
},
new SqlParameter("@score", SqlDbType.Int)
{
Value = sc.score
}
};
return(ADOTools.ExecuteNonQuery(sql, param));
}
/// <summary>
/// 更新数据
/// </summary>
/// <param name="t">模型类</param>
/// <returns>受影响的行数</returns>
public int Update(teacher t)
{
string sql = "UPDATE teacher SET tcid = @tcid,tname = @tname,tProfession = @tProfession,tpassword = @tpassword WHERE tid = @tid;";
SqlParameter[] param =
{
new SqlParameter("@tid", SqlDbType.Int)
{
Value = t.tid
},
new SqlParameter("@tcid", SqlDbType.Int)
{
Value = t.tcid
},
new SqlParameter("@tname", SqlDbType.VarChar, 20)
{
Value = t.tname
},
new SqlParameter("@tProfession", SqlDbType.Char, 1)
{
Value = t.tProfession
},
new SqlParameter("@tpassword", SqlDbType.Char, 6)
{
Value = t.tpassword
}
};
return(ADOTools.ExecuteNonQuery(sql, param));
}
/// <summary>
/// 读取数据
/// </summary>
/// <param name="dt">临时数据表</param>
/// <returns>包含数据的临时数据表</returns>
public DataTable GetDB(DataTable dt)
{
string strSql = @" SELECT * FROM Course ";
dt = ADOTools.ExcuteDataTable(strSql);
return(dt);
}
/// <summary>
/// 读取数据
/// </summary>
/// <param name="dt">临时数据表</param>
/// <returns>包含数据的临时数据表</returns>
public DataTable GetDB(DataTable dt)
{
string strSql = @" SELECT * FROM system_users ";
dt = ADOTools.ExcuteDataTable(strSql);
return(dt);
}
//清空数据表数据
public void DelDateTable()
{
string strSql = " TRUNCATE TABLE student; ";
int flag = ADOTools.SqlDataReader(strSql);
MessageBox.Show("数据清空完成!!\n表内为 " + (flag + 1) + " 行数据。", "执行成功", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
//调用ADOTools工具类执行方法
public void ATGetDataSet()
{
string strSql = "SELECT * FROM student";
SqlDataReader reader = ADOTools.ExcuteReader(strSql);;
List <Student> list = new List <Student>();
while (reader.Read())
{
Student s = new Student()
{
sid = Convert.ToInt32(reader["sid"]),
sname = reader["sname"].ToString(),
ssex = Convert.ToInt32(reader["ssex"]),
sage = Convert.ToInt32(reader["sage"]),
sbirthday = reader["sbirthday"].ToString(),
sphone = reader["sphone"].ToString(),
saddress = reader["saddress"].ToString(),
scid = Convert.ToInt32(reader["scid"])
};
list.Add(s);
}
DataTable dt = ADOTools.ExcuteDataTable(strSql);
//统计读取到数据的行数量(表达式：总行数减一行空白行)
int Listindex = (list.Count) - 1; //List总行数
int DataTableindex = (list.Count) - 1; //DateTable总行数
MessageBox.Show("读取数据完成!!\nList内共读取到 " + Listindex + " 行数据。\nDateTable内共读取到 " + DataTableindex + " 行数据。", "执行成功", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
/// <summary>
/// 分页查询数据
/// </summary>
/// <param name="pageSize">每页展示数据的条数</param>
/// <param name="pageIndex">当前的页码数</param>
/// <returns></returns>
public DataTable GetDataByPage(int pageSize, int pageIndex)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT TOP " + pageSize + " * FROM Course ");
strSql.Append(" WHERE cid NOT IN( ");
strSql.Append(" SELECT TOP (" + pageSize + "*(" + pageIndex + "-1)) cid FROM Course ORDER BY cid ASC ");
strSql.Append(" ) ORDER BY cid ASC ");
return(ADOTools.Query(strSql.ToString(), null));
}
/// <summary>
/// 查询数据总行数
/// </summary>
/// <returns></returns>
public int GetDBRows()
{
string sql = " SELECT COUNT(*) FROM Course ";
object ob = ADOTools.ExecuteScalar(sql, null);
if (ob != null)
{
return(int.Parse(ob.ToString()));
}
return(0);
}
/// <summary>
/// 按ID删除数据
/// </summary>
/// <param name="uid">要删除的ID值</param>
/// <returns>受影响的行数</returns>
public int Delete(int uid)
{
string sql = "DELETE FROM system_users WHERE uid=@uid;";
SqlParameter[] param =
{
new SqlParameter("@uid", SqlDbType.Int)
{
Value = uid
}
};
return(ADOTools.ExecuteNonQuery(sql, param));
}
/// <summary>
/// 按ID删除数据
/// </summary>
/// <param name="cid">要删除的ID值</param>
/// <returns>受影响的行数</returns>
public int Delete(int cid)
{
string sql = "DELETE FROM Course WHERE cid=@cid;";
SqlParameter[] param =
{
new SqlParameter("@cid", SqlDbType.Int)
{
Value = cid
}
};
return(ADOTools.ExecuteNonQuery(sql, param));
}
/// <summary>
/// 按ID删除数据
/// </summary>
/// <param name="scid">要删除的ID值</param>
/// <returns>受影响的行数</returns>
public int Delete(int scid)
{
string sql = "DELETE FROM SC_Mapping WHERE scid=@scid;";
SqlParameter[] param =
{
new SqlParameter("@scid", SqlDbType.Int)
{
Value = scid
}
};
return(ADOTools.ExecuteNonQuery(sql, param));
}
/// <summary>
/// 按ID删除数据
/// </summary>
/// <param name="tid">要删除的ID值</param>
/// <returns>受影响的行数</returns>
public int Delete(int tid)
{
string sql = "DELETE FROM teacher WHERE tid=@tid;";
SqlParameter[] param =
{
new SqlParameter("@tid", SqlDbType.Int)
{
Value = tid
}
};
return(ADOTools.ExecuteNonQuery(sql, param));
}
//拼接方法
public int AddStudentPinJie(Student s)
{
//判断选中的性别radioButton进行性别变量赋值
int sex = 0; //存储性别值
if (radioButtonNan.Checked == true & radioButtonNv.Checked == false)
{
sex = 1;
} //男
else if (radioButtonNv.Checked == true & radioButtonNan.Checked == false)
{
sex = 0;
} //女
string strSql = "INSERT INTO student VALUES('" + textBoxSname.Text + "'," + sex + "," + Convert.ToInt32(textBoxSage.Text) + ",'" + dateTimePickerSbirthday.Text + "','" + textBoxSphone.Text + "','" + textBoxSaddress.Text + "','" + this.comboBoxSbanji.SelectedIndex + 1 + "'); ";
return(ADOTools.ExcuteNoQuerys(strSql));
}
/// <summary>
/// 插入数据
/// </summary>
/// <param name="c">模型类</param>
/// <returns>受影响的行数</returns>
public int Add(course c)
{
string sql = "INSERT INTO Course(ctid,cname,climit) VALUES(@ctid,@cname,0);";
SqlParameter[] param =
{
new SqlParameter("@ctid", SqlDbType.Int)
{
Value = c.ctid
},
new SqlParameter("@cname", SqlDbType.NVarChar, 20)
{
Value = c.cname
}
};
return(ADOTools.ExecuteNonQuery(sql, param));
}
/// <summary>
/// 插入数据
/// </summary>
/// <param name="su">模型类</param>
/// <returns>受影响的行数</returns>
public int Add(system_users su)
{
string sql = "INSERT INTO system_users(ucode,upwd) VALUES(@ucode,@upwd);";
SqlParameter[] param =
{
new SqlParameter("@ucode", SqlDbType.VarChar, 10)
{
Value = su.ucode
},
new SqlParameter("@upwd", SqlDbType.VarChar, 10)
{
Value = su.upwd
}
};
return(ADOTools.ExecuteNonQuery(sql, param));
}
/// <summary>
/// 插入数据
/// </summary>
/// <param name="c">模型类</param>
/// <returns>受影响的行数</returns>
public int Add(classInfo c)
{
string sql = "INSERT INTO classInfo(cid,cname) VALUES(@cid,@cname);";
SqlParameter[] param =
{
new SqlParameter("@cid", SqlDbType.Int)
{
Value = c.cid
},
new SqlParameter("@cname", SqlDbType.NVarChar, 20)
{
Value = c.cname
}
};
return(ADOTools.ExecuteNonQuery(sql, param));
}
/// <summary>
/// 更新数据
/// </summary>
/// <param name="c">模型类</param>
/// <returns>受影响的行数</returns>
public int Update(classInfo c)
{
string sql = "UPDATE classInfo SET cname = @cname WHERE cid=@cid;";
SqlParameter[] param =
{
new SqlParameter("@cid", SqlDbType.Int)
{
Value = c.cid
},
new SqlParameter("@cname", SqlDbType.NVarChar, 20)
{
Value = c.cname
}
};
return(ADOTools.ExecuteNonQuery(sql, param));
}
/// <summary>
/// 更新数据
/// </summary>
/// <param name="s">模型类</param>
/// <returns>受影响的行数</returns>
public int Update(Student s)
{
string sql = "UPDATE student SET sname = @sname,ssex = @ssex,sage = @sage,sbirthday = @sbirthday,sphone = @sphone,saddress = @saddress,scid = @scid,spassword = @spassword WHERE sid=@sid;";
SqlParameter[] param =
{
new SqlParameter("@sid", SqlDbType.Int)
{
Value = s.sid
},
new SqlParameter("@sname", SqlDbType.VarChar, 20)
{
Value = s.sname
},
new SqlParameter("@ssex", SqlDbType.Int)
{
Value = s.ssex
},
new SqlParameter("@sage", SqlDbType.Int)
{
Value = s.sage
},
new SqlParameter("@sbirthday", SqlDbType.Date)
{
Value = s.sbirthday
},
new SqlParameter("@sphone", SqlDbType.VarChar, 20)
{
Value = s.sphone
},
new SqlParameter("@saddress", SqlDbType.VarChar, 50)
{
Value = s.saddress
},
new SqlParameter("@scid", SqlDbType.Int)
{
Value = s.scid
},
new SqlParameter("@spassword", SqlDbType.VarChar, 6)
{
Value = s.spassword
},
};
return(ADOTools.ExecuteNonQuery(sql, param));
}
/// <summary>
/// 插入数据
/// </summary>
/// <param name="s">模型类</param>
/// <returns>受影响的行数</returns>
public int Add(Student s)
{
string sql = "INSERT INTO student(sid,sname,ssex,sage,sbirthday,sphone,saddress,scid,spassword) VALUES(@sid,@sname,@ssex,@sage,@sbirthday,@sphone,@saddress,@scid,@spassword);";
SqlParameter[] param =
{
new SqlParameter("@sid", SqlDbType.Int)
{
Value = s.sid
},
new SqlParameter("@sname", SqlDbType.VarChar, 20)
{
Value = s.sname
},
new SqlParameter("@ssex", SqlDbType.Int)
{
Value = s.ssex
},
new SqlParameter("@sage", SqlDbType.Int)
{
Value = s.sage
},
new SqlParameter("@sbirthday", SqlDbType.Date)
{
Value = s.sbirthday
},
new SqlParameter("@sphone", SqlDbType.VarChar, 20)
{
Value = s.sphone
},
new SqlParameter("@saddress", SqlDbType.VarChar, 50)
{
Value = s.saddress
},
new SqlParameter("@scid", SqlDbType.Int)
{
Value = s.scid
},
new SqlParameter("@spassword", SqlDbType.VarChar, 6)
{
Value = s.spassword
},
};
return(ADOTools.ExecuteNonQuery(sql, param));
}
/// <summary>
/// 更新数据
/// </summary>
/// <param name="su">模型类</param>
/// <returns>受影响的行数</returns>
public int Update(system_users su)
{
string sql = "UPDATE system_users SET ucode = @ucode,upwd = @upwd WHERE uid = @uid;";
SqlParameter[] param =
{
new SqlParameter("@uid", SqlDbType.Int)
{
Value = su.uid
},
new SqlParameter("@ucode", SqlDbType.VarChar, 10)
{
Value = su.ucode
},
new SqlParameter("@upwd", SqlDbType.VarChar, 10)
{
Value = su.upwd
}
};
return(ADOTools.ExecuteNonQuery(sql, param));
}
/// <summary>
/// 插入数据
/// </summary>
/// <param name="sc">模型类</param>
/// <returns>受影响的行数</returns>
public int Add(SC_Mapping sc)
{
string sql = "INSERT INTO SC_Mapping(sid,cid,score) VALUES(@sid,@cid,@score);";
SqlParameter[] param =
{
new SqlParameter("@sid", SqlDbType.Int)
{
Value = sc.sid
},
new SqlParameter("@cid", SqlDbType.Int)
{
Value = sc.cid
},
new SqlParameter("@score", SqlDbType.Int)
{
Value = sc.score
}
};
return(ADOTools.ExecuteNonQuery(sql, param));
}