public List <StudentOrder> GetStudentOrders() { SqlDbHelper dbHelper = new SqlDbHelper(); string sql = "SELECT S.Sno, S.Sname, A.GradeSum " + "FROM Student S INNER JOIN (" + "SELECT Sno, SUM(Grade) GradeSum FROM SC " + "GROUP BY Sno) A ON S.Sno = A.Sno " + "ORDER BY A.GradeSum DESC"; SqlDataReader reader = dbHelper.ExecuteReader(sql); List <StudentOrder> studentOrders = new List <StudentOrder>(); int count = 0, order = 0; double gradeSumLast = -1; while (reader.Read()) { string sno = reader["Sno"].ToString().Trim(); string sname = reader["Sname"].ToString().Trim(); double gradeSum = Convert.ToDouble(reader["GradeSum"]); count++; if (gradeSum != gradeSumLast || order <= 0) { order = count; } studentOrders.Add(new StudentOrder(order, sno, sname, gradeSum)); } reader.Close(); return(studentOrders); }
public List <long> Getuidlist(string where) { List <long> listid = new List <long>(); try { StringBuilder strSql = new StringBuilder(); strSql.Append("select uid "); strSql.Append(" from member "); if (where.Trim().Length > 0) { strSql.Append(" where " + where); } using (SqlDataReader dr = SqlDbHelper.ExecuteReader(Config.SqlConnection, strSql.ToString(), CommandType.Text)) { while (dr.Read()) { long uid = int.Parse(dr["uid"] != DBNull.Value ? dr["uid"].ToString() : "0"); listid.Add(uid); } } } catch { } return(listid); }
public List <SCAll> GetNOSCAll() { SqlDbHelper dbHelper = new SqlDbHelper(); string sql = "SELECT S.Sno, S.Sname, S.Ssex, S.Sage, S.Sdept, " + "C.Cno, C.Cname, C.Cpno, C.Credit, SC.Grade " + "FROM Student S INNER JOIN SC ON S.Sno=SC.Sno " + "INNER JOIN Course C ON SC.Cno=C.Cno"; SqlDataReader reader = dbHelper.ExecuteReader(sql); List <SCAll> scAlls = new List <SCAll>(); while (reader.Read()) { string sno = reader["Sno"].ToString().Trim(); string sname = reader["Sname"].ToString().Trim(); string ssex = reader["Ssex"].ToString().Trim(); int sage = Convert.ToInt32(reader["Sage"]); string sdept = reader["Sdept"].ToString().Trim(); Student s = new Student(sno, sname, ssex, sage, sdept); int cno = Convert.ToInt32(reader["Cno"]); string cname = reader["Cname"].ToString().Trim(); int cpno = Convert.IsDBNull(reader["Cpno"]) ? -1 : Convert.ToInt32(reader["Cpno"]); int credit = Convert.ToInt32(reader["Credit"]); Course c = new Course(cno, cname, cpno, credit); double grade = Convert.ToDouble(reader["Grade"]); SC sc = new SC(sno, cno, grade); scAlls.Add(new SCAll(s, c, sc)); } reader.Close(); return(scAlls); }
public memberInfo GetModel(long uid) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("select * "); strSql.Append(" from member "); strSql.Append(" where uid=@uid"); SqlParameter[] parameters = { new SqlParameter("@uid", SqlDbType.Int, 4) }; parameters[0].Value = uid; memberInfo info = new memberInfo(); using (SqlDataReader dr = SqlDbHelper.ExecuteReader(Config.SqlConnection, strSql.ToString(), CommandType.Text, parameters)) { if (dr.Read()) { //info = GetInfoByDr(dr); info = BS.Components.Data.Entity.EntityHelper.GetDataReaderObject <memberInfo>("*", dr); } } return(info); } catch { } return(null); }
public Companys_costlogInfo GetModel(int id) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("select * "); strSql.Append(" from Companys_costlog "); strSql.Append(" where id=@id"); SqlParameter[] parameters = { new SqlParameter("@id", SqlDbType.Int, 4) }; parameters[0].Value = id; Companys_costlogInfo info = new Companys_costlogInfo(); using (SqlDataReader dr = SqlDbHelper.ExecuteReader(Config.SqlConnection, strSql.ToString(), CommandType.Text, parameters)) { if (dr.Read()) { info = GetInfoByDr(dr); } } return(info); } catch { } return(null); }
public List <Companys_costlogInfo> GetList(int Top, string strWhere, string filedOrder) { List <Companys_costlogInfo> list = new List <Companys_costlogInfo>(); try { StringBuilder strSql = new StringBuilder(); strSql.Append("select "); if (Top > 0) { strSql.Append(" top " + Top.ToString()); } strSql.Append(" * "); strSql.Append(" FROM Companys_costlog "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } if (filedOrder.Trim() != "") { strSql.Append(" order by " + filedOrder); } using (SqlDataReader dr = SqlDbHelper.ExecuteReader(Config.SqlConnection, strSql.ToString(), CommandType.Text)) { while (dr.Read()) { Companys_costlogInfo info = GetInfoByDr(dr); list.Add(info); } } return(list); } catch { } return(null); }
public CompanysInfo getModel(string username, string password) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("select * "); strSql.Append(" from Companys "); //strSql.Append(" where uname=@uname and password=@password and companyid=@companyid"); strSql.Append(" where username=@username and password=@password"); SqlParameter[] parameters = { new SqlParameter("@username", SqlDbType.VarChar, 80), new SqlParameter("@password", SqlDbType.VarChar, 80), }; parameters[0].Value = username; parameters[1].Value = password; CompanysInfo info = new CompanysInfo(); using (SqlDataReader dr = SqlDbHelper.ExecuteReader(Config.SqlConnection, strSql.ToString(), CommandType.Text, parameters)) { if (dr.Read()) { info = BS.Components.Data.Entity.EntityHelper.GetDataReaderObject <CompanysInfo>("*", dr); } } return(info); } catch { } return(null); }
public List <int> GetSubListID(int id) { List <int> list = new List <int>(); try { StringBuilder strSql = new StringBuilder(); strSql.Append("select id FROM article_category where parentid=@id"); SqlParameter[] parameters = { new SqlParameter("@id", SqlDbType.Int, 4) }; parameters[0].Value = id; using (SqlDataReader dr = SqlDbHelper.ExecuteReader(Config.SqlConnection, strSql.ToString(), CommandType.Text, parameters)) { while (dr.Read()) { int _id = int.Parse(dr["id"] != DBNull.Value ? dr["id"].ToString() : ""); list.Add(_id); } } return(list); } catch { } return(null); }
public List <SC> GetSCs() { SqlDbHelper dbHelper = new SqlDbHelper(); string sql = "select * from SC"; SqlDataReader reader = dbHelper.ExecuteReader(sql); List <SC> scs = new List <SC>(); while (reader.Read()) { string sno = reader["Sno"].ToString().Trim(); int cno = Convert.ToInt32(reader["Cno"]); double grade = Convert.ToDouble(reader["Grade"]); SC sc = new SC(sno, cno, grade); scs.Add(new SC(sno, cno, grade)); } reader.Close(); return(scs); }
public int Getidbyuno(string uno) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("select uid "); strSql.Append(" from member "); strSql.Append(" where uno=@uno"); SqlParameter[] parameters = { new SqlParameter("@uno", SqlDbType.VarChar, 80), }; parameters[0].Value = uno; object obj = SqlDbHelper.ExecuteReader(Config.SqlConnection, strSql.ToString(), CommandType.Text, parameters); return(obj != null && obj != DBNull.Value ? Convert.ToInt32(obj) : 0); } catch { } return(0); }
public List <Course> GetCourse() { SqlDbHelper dbHelper = new SqlDbHelper(); string sql = "select * from Course"; SqlDataReader reader = dbHelper.ExecuteReader(sql); List <Course> courses = new List <Course>(); while (reader.Read()) { int no = Convert.ToInt32(reader["Cno"]); string name = reader["Cname"].ToString().Trim(); int pno = Convert.IsDBNull(reader["Cpno"]) ? -1 : Convert.ToInt32(reader["Cpno"]); int credit = Convert.ToInt32(reader["Credit"]); courses.Add(new Course(no, name, pno, credit)); } reader.Close(); return(courses); }
public List <Student> GetStudents() { SqlDbHelper dbHelper = new SqlDbHelper(); string sql = "select * from Student"; SqlDataReader reader = dbHelper.ExecuteReader(sql); List <Student> students = new List <Student>(); while (reader.Read()) { string no = reader["Sno"].ToString().Trim(); string name = reader["Sname"].ToString().Trim(); string sex = reader["Ssex"].ToString().Trim(); int age = Convert.ToInt32(reader["Sage"]); string dept = reader["Sdept"].ToString().Trim(); students.Add(new Student(no, name, sex, age, dept)); } reader.Close(); return(students); }