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);
}
