public static string LoginStyleStoreProcedure(string mssv, string pass)
{
try
{
Connect_Helper cnn = new Connect_Helper();
cnn.OpenSection();
string procName = "STUDENT_LOGIN";
SqlCommand sql = new SqlCommand(procName);
sql.CommandType = CommandType.StoredProcedure;
sql.Connection = cnn.connect;
sql.Parameters.Add("@id", SqlDbType.Char).Value = mssv;
sql.Parameters.Add("@pwd", SqlDbType.NVarChar).Value = pass;
sql.Parameters.Add("@stringKQ", SqlDbType.NVarChar).Direction = ParameterDirection.Output;
int KQ = sql.ExecuteNonQuery();
if (KQ > 0)
{
return((string)sql.Parameters["@tringKQ"].Value);
}
else
{
return("EXECUTE FAILED");
}
}
catch
{
return("CATCH");
}
}
public static bool XacMinh(string username, string password)
{
try
{
Connect_Helper cnn = new Connect_Helper();
cnn.OpenSection();
string procedureName = "ADMIN_LOGIN";
SqlCommand cmd = new SqlCommand(procedureName, cnn.connect);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ad_name", SqlDbType.VarChar).Value = username;
cmd.Parameters.Add("@pwd", SqlDbType.NVarChar).Value = password;
cmd.Parameters.Add("@KQ", SqlDbType.Int).Direction = ParameterDirection.Output;
int KQ = cmd.ExecuteNonQuery();
bool isad = false;
if (KQ > 0)
{
if (Convert.ToInt32(cmd.Parameters["@KQ"].Value) == 1)
{
isad = true;
}
}
return(isad);
}
catch
{
// throw ex;
return(false);
}
//finally
//{
//}
}
public static bool Disable_Subject(string start, string end, string mamon)
{
try
{
Connect_Helper cnn = new Connect_Helper();
cnn.OpenSection();
string procedureName = "DISABLE_SUBJECT";
SqlCommand cmd = new SqlCommand(procedureName, cnn.connect);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@disablefrom", SqlDbType.Date).Value = start;
cmd.Parameters.Add("@disableto", SqlDbType.Date).Value = end;
cmd.Parameters.Add("@MaMon", SqlDbType.Char).Value = mamon;
int KQ = cmd.ExecuteNonQuery();
if (KQ > 0)
{
return(true);
}
return(false);
}
catch
{
// throw ex;
return(false);
}
}
public static DataTable laySV(string mssv)
{
string sql = string.Format(@"SELECT MSSV,TENSV, SDT FROM SINHVIEN WHERE MSSV LIKE '%{0}%'", mssv);
Connect_Helper cnn = new Connect_Helper();
return(cnn.GetDataTable(sql));
}
public static int dangkiNhom(CHITIETNHOMDTO ctDTO)
{
try
{
Connect_Helper cnn = new Connect_Helper();
cnn.OpenSection();
string ProcName = "";
SqlParameter[] prs =
{
new SqlParameter("@MaNhom", SqlDbType.Int),
new SqlParameter("@MaDoAn", SqlDbType.Int),
new SqlParameter("@MaSV", SqlDbType.VarChar)
};
prs[0].Value = ctDTO.MaNhom;
prs[1].Value = ctDTO.MaDA;
prs[2].Value = ctDTO.MSSV;
SqlCommand sqlcmd = new SqlCommand(ProcName, cnn.connect);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddRange(prs);
int KQ = sqlcmd.ExecuteNonQuery();
cnn.CloseSection();
if (KQ > 0)
{
return(1);
}
else
{
return(0);
}
}
catch
{
return(-1);
}
}
//int maNhom, string MSSV, int MaDA
public static int huyNhom(int maNhom, string mssv)
{
try
{
Connect_Helper cnn = new Connect_Helper();
cnn.OpenSection();
string ProcName = "";
SqlParameter[] prs = { new SqlParameter("@MaNhom", SqlDbType.Int), new SqlParameter("@MaSV", SqlDbType.VarChar) };
prs[0].Value = maNhom;
prs[1].Value = mssv;
SqlCommand sqlcmd = new SqlCommand(ProcName, cnn.connect);
sqlcmd.CommandType = CommandType.StoredProcedure;
int KQ = sqlcmd.ExecuteNonQuery();
cnn.CloseSection();
if (KQ > 0)
{
return(1);
}
else
{
return(0);
}
}
catch
{
return(-1);
}
}
public static int TaoNhomMoi(string tennhom, int madoan, string truongnhom)
{
try
{
Connect_Helper cnn = new Connect_Helper();
cnn.OpenSection();
string ProcName = "CREATE_GROUP";
SqlParameter[] prs =
{
new SqlParameter("@MaDA", SqlDbType.Int),
new SqlParameter("@TenNhom", SqlDbType.NVarChar),
new SqlParameter("@TruongNhom", SqlDbType.Char)
};
prs[0].Value = madoan;
prs[1].Value = tennhom;
prs[2].Value = truongnhom;
SqlCommand sqlcmd = new SqlCommand(ProcName, cnn.connect);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddRange(prs);
int KQ = sqlcmd.ExecuteNonQuery();
cnn.CloseSection();
if (KQ > 0)
{
return(1);
}
else
{
return(0);
}
}
catch
{
return(-1);
}
}
public static bool Create_Update_Subject(MONHOCDTO mhDTO, string opt)
{
try
{
Connect_Helper cnn = new Connect_Helper();
cnn.OpenSection();
string procedureName = opt;
SqlCommand cmd = new SqlCommand(procedureName, cnn.connect);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MaMon", SqlDbType.Char).Value = mhDTO.MaMon;
cmd.Parameters.Add("@TenMon", SqlDbType.NVarChar).Value = mhDTO.TenMon;
cmd.Parameters.Add("@SoTinChi", SqlDbType.Int).Value = mhDTO.SoTinChi;
cmd.Parameters.Add("@NgayDB", SqlDbType.Date).Value = mhDTO.NgayBD;
cmd.Parameters.Add("@NgayKT", SqlDbType.Date).Value = mhDTO.NgayKT;
int KQ = cmd.ExecuteNonQuery();
if (KQ > 0)
{
return(true);
}
return(false);
}
catch
{
// throw ex;
return(false);
}
}
public static DataTable layCTNhom(int manhom)
{
string sql = string.Format(@"
SELECT sv.MSSV, sv.TENSV, sv.SDT
FROM dbo.CHITIET_NHOM ct,
dbo.SINHVIEN sv
WHERE ct.MaNhom = {0}
AND
ct.MaSV = sv.MSSV
", manhom
);
Connect_Helper cnn = new Connect_Helper();
return(cnn.GetDataTable(sql));
}
public static int maxMemberNhom(int manhom, int madoan)
{
try
{
Connect_Helper cnn = new Connect_Helper();
cnn.OpenSection();
string ProcName = "CHECK_EXCEED_NUMBER_MEMBER_Of_GROUP";
//SqlCommand sqlcmd = new SqlCommand(ProcName, cnn.connect);
SqlCommand sqlcmd = new SqlCommand(ProcName);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Connection = cnn.connect;
//SqlParameter[] prs =
//{
// new SqlParameter("@MaDoAn", SqlDbType.Int),
// new SqlParameter("@KQ", SqlDbType.Int)
//};
//prs[0].Value = madoan;
//prs[1].Direction = ParameterDirection.Output;
//sqlcmd.Parameters.AddRange(prs);
sqlcmd.Parameters.Add("@MaDoAn", SqlDbType.Int).Value = madoan;
sqlcmd.Parameters.Add("@MaNhom", SqlDbType.Int).Value = manhom;
sqlcmd.Parameters.Add("@KQ", SqlDbType.Int).Direction = ParameterDirection.Output;
int n = sqlcmd.ExecuteNonQuery();
if (n > 0)
{
//int KQ = Convert.ToInt32(prs[1].Value.ToString());
int KQ = Convert.ToInt32(sqlcmd.Parameters["@KQ"].Value);
cnn.CloseSection();
if (KQ == 2)
{
return(1);
}
else
{
return(0);
}
}
else
{
return(-1);
}
}
catch
{
return(-2);
}
}
public static string Laytruongnhom(int madoan, int manhom)
{
try
{
Connect_Helper cnn = new Connect_Helper();
cnn.OpenSection();
string ProcName = "RETRIEVE_LEADER_GROUP";
SqlCommand sqlcmd = new SqlCommand(ProcName);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Connection = cnn.connect;
//SqlParameter[] prs =
//{
// new SqlParameter("@MaDoAn", SqlDbType.Int),
// new SqlParameter("@KQ", SqlDbType.Int)
//};
//prs[0].Value = madoan;
//prs[1].Direction = ParameterDirection.Output;
//sqlcmd.Parameters.AddRange(prs);
sqlcmd.Parameters.Add("@MaDA", SqlDbType.Int).Value = madoan;
sqlcmd.Parameters.Add("@MaNhom", SqlDbType.Int).Value = madoan;
sqlcmd.Parameters.Add("@stringKQ", SqlDbType.Int).Direction = ParameterDirection.Output;
int n = sqlcmd.ExecuteNonQuery();
if (n > 0)
{
//int KQ = Convert.ToInt32(prs[1].Value.ToString());
string KQ = Convert.ToString(sqlcmd.Parameters["@stringKQ"].Value);
cnn.CloseSection();
if (KQ != "")
{
return(KQ);
}
else
{
return("");
}
}
else
{
return("");
}
}
catch
{
return("");
}
}
public static int LaTruongNhom(int MaDA, string MSSV)
{
try
{
Connect_Helper cnn = new Connect_Helper();
cnn.OpenSection();
string ProcName = "";
SqlCommand sqlcmd = new SqlCommand(ProcName, cnn.connect);
sqlcmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] prs =
{
new SqlParameter("@MSSV", SqlDbType.Char),
new SqlParameter("@MaDA", SqlDbType.Int),
new SqlParameter("@KQ", SqlDbType.Int)
};
prs[0].Value = MaDA;
prs[1].Value = MSSV;
prs[2].Direction = ParameterDirection.Output;
int n = sqlcmd.ExecuteNonQuery();
if (n > 0)
{
int KQ = Convert.ToInt32(prs[2].Value.ToString());
cnn.CloseSection();
if (KQ == 2)
{
return(1);
}
else
{
return(0);
}
}
else
{
return(-1);
}
}
catch
{
return(-2);
}
}
public static DataTable GET_ALL_SUBJECT()
{
Connect_Helper cnn = new Connect_Helper();
try
{
cnn.OpenSection();
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand("SELECT_ALL_SUBJECT", cnn.connect);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
cnn.CloseSection();
return(dt);
}
catch
{
return(null);
}
}
public static DataTable _LAYDSMONHOC(string maSV)
{
Connect_Helper cnn = new Connect_Helper();
string sql = string.Format(@"
SELECT mh.MaMon,
mh.TenMon,
mh.SoTinChi,
mh.NgayDB,
mh.NgayKT
FROM
dbo.MONHOC mh,
dbo.SINHVIEN_MONHOC sv_mh
WHERE
sv_mh.MSSV = '{0}'
AND
sv_mh.MaMon = mh.MaMon
", maSV
);
return(cnn.GetDataTable(sql));
}
public static GIAOVIENDTO LayGV(string username, string password)
{
GIAOVIENDTO gvDTO = null;
try
{
Connect_Helper cnn = new Connect_Helper();
cnn.OpenSection();
string query = string.Format(@"Select *
FROM GIAOVIEN
Where
MaGV = '{0}'
AND
PasswordHash = HASHBYTES('SHA2_512', '{1}'+CAST(Salt AS NVARCHAR(36)))"
, username, password
);
SqlCommand cmd = new SqlCommand(query, cnn.connect);
SqlDataReader dr = null;
dr = cmd.ExecuteReader();
while (dr.Read())
{
gvDTO = new GIAOVIENDTO();
gvDTO.MaGV = Convert.ToString(dr["MaGV"]);
gvDTO.TenGV = Convert.ToString(dr["TenGV"]);
gvDTO.STD = Convert.ToString(dr["SDT"]);
}
return(gvDTO);
}
catch
{
// throw ex;
return(null);
}
//finally
//{
//}
}
public static bool Remove_Subject(string mamon)
{
try
{
Connect_Helper cnn = new Connect_Helper();
cnn.OpenSection();
string procedureName = "REMOVE_SUBJECT";
SqlCommand cmd = new SqlCommand(procedureName, cnn.connect);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MaMon", SqlDbType.Char).Value = mamon;
int KQ = cmd.ExecuteNonQuery();
if (KQ > 0)
{
return(true);
}
return(false);
}
catch
{
// throw ex;
return(false);
}
}
public static SINHVIENDTO laySinhVien(string username, string password)
{
SINHVIENDTO svDTO = null;
try
{
Connect_Helper cnn = new Connect_Helper();
cnn.OpenSection();
string query = string.Format(@"Select *
FROM SINHVIEN
Where
MSSV = '{0}'
AND
PasswordHash = HASHBYTES('SHA2_512', '{1}'+CAST(Salt AS NVARCHAR(36)))"
, username, password
);
SqlCommand cmd = new SqlCommand(query, cnn.connect);
SqlDataReader dr = null;
dr = cmd.ExecuteReader();
while (dr.Read())
{
svDTO = new SINHVIENDTO();
svDTO.MSSV = Convert.ToString(dr["MSSV"]);
svDTO.TenSV = Convert.ToString(dr["TenSV"]);
svDTO.STD = Convert.ToString(dr["SDT"]);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
}
return(svDTO);
}
public static string DangNhap(string magv, string password)
{
// Connect_Helper cnn = new Connect_Helper();
// cnn.OpenSection();
// string ProcName = "TEACHER_LOGIN";
// SqlCommand sqlcmd = new SqlCommand(ProcName);
// sqlcmd.CommandType = CommandType.StoredProcedure;
// sqlcmd.Connection = cnn.connect;
// //SqlParameter[] prs =
// //{
// // new SqlParameter("@id", SqlDbType.Char),
// // new SqlParameter("@pwd", SqlDbType.NVarChar),
// // new SqlParameter("@stringKQ", SqlDbType.NVarChar)
// //};
// //prs[0].Value = magv;
// //prs[1].Value = password;
// //prs[2].Direction = ParameterDirection.Output;
// //sqlcmd.Parameters.AddRange(prs);
// sqlcmd.Parameters.Add("@id", SqlDbType.Char).Value=magv;
// sqlcmd.Parameters.Add("@pwd", SqlDbType.NVarChar).Value=password;
// sqlcmd.Parameters.Add("@stringKQ", SqlDbType.NVarChar).Direction = ParameterDirection.Output;
// int n = sqlcmd.ExecuteNonQuery();
// string KQ;
// if (n > 0)
// {
// KQ = (string)sqlcmd.Parameters["stringKQ"].Value;
// cnn.CloseSection();
// }
// else KQ = "";
// return KQ;
//}
//catch
//{
// return "";
//}
try
{
Connect_Helper connectDB = new Connect_Helper();
connectDB.OpenSection();
string procedureName = "TEACHER_LOGIN";
SqlCommand cmd = new SqlCommand(procedureName);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = connectDB.connect;
cmd.Parameters.Add("@id", SqlDbType.Char).Value = magv;
cmd.Parameters.Add("@pwd", SqlDbType.NVarChar).Value = password;
cmd.Parameters.Add("@stringKQ", SqlDbType.NVarChar).Direction = ParameterDirection.Output;
int KQ = cmd.ExecuteNonQuery();
string str = "";
if (KQ > 0)
{
str = (string)cmd.Parameters["stringKQ"].Value;
}
connectDB.CloseSection();
return(str);
}
catch
{
return("");
}
}
public static DataTable laytatcaSV()
{
Connect_Helper cnn = new Connect_Helper();
return(cnn.GetDataTable("SELECT MSSV,TENSV, SDT FROM SINHVIEN"));
}
public static DataTable LocDSDA(string mamon, string mssv, int option = 0)
{
string sql = "";
switch (option)
{
default: break;
case 0:
sql = string.Format(@"
SELECT da.MaDoAn, da.TenDoAn, da.LoaiDoAn,nh.TenNhom
FROM dbo.DOAN AS da,
dbo.NHOM AS nh,
dbo.MONHOC mh,
dbo.CHITIET_NHOM ctm
WHERE ctm.maSV = '{0}'
AND
ctm.MaNhom = nh.MaNhom
AND
ctm.MaDA = da.MaDoAn
AND
da.MaMon = mh.MaMon
AND
mh.MaMon = '{1}'
", mssv, mamon);
break;
case 1:
sql = string.Format(@"
SELECT da.MaDoAn,da.TenDoAn, da.LoaiDoAn,nh.TenNhom
FROM dbo.DOAN AS da,
dbo.NHOM AS nh,
dbo.MONHOC mh,
dbo.CHITIET_NHOM ctm
WHERE ctm.maSV = '{0}'
AND
ctm.MaNhom = nh.MaNhom
AND
ctm.MaDA = da.MaDoAn AND da.LoaiDoAn = N'Bài tập cộng điểm'
AND
da.MaMon = mh.MaMon
AND
mh.MaMon = '{1}'
", mssv, mamon);
break;
case 2:
sql = string.Format(@"
SELECT da.MaDoAn,da.TenDoAn, da.LoaiDoAn,nh.TenNhom
FROM dbo.DOAN AS da,
dbo.NHOM AS nh,
dbo.MONHOC mh,
dbo.CHITIET_NHOM ctm
WHERE ctm.maSV = '{0}'
AND
ctm.MaNhom = nh.MaNhom
AND
ctm.MaDA = da.MaDoAn AND da.LoaiDoAn = N'Giữa Kỳ'
AND
da.MaMon = mh.MaMon
AND
mh.MaMon = '{1}'
", mssv, mamon);
break;
case 3:
sql = string.Format(@"
SELECT da.MaDoAn,da.TenDoAn, da.LoaiDoAn,nh.TenNhom
FROM dbo.DOAN AS da,
dbo.NHOM AS nh,
dbo.MONHOC mh,
dbo.CHITIET_NHOM ctm
WHERE ctm.maSV = '{0}'
AND
ctm.MaNhom = nh.MaNhom
AND
ctm.MaDA = da.MaDoAn AND da.LoaiDoAn = N'Cuối Kỳ'
AND
da.MaMon = mh.MaMon
AND
mh.MaMon = '{1}'
", mssv, mamon);
break;
}
Connect_Helper cnn = new Connect_Helper();
return(cnn.GetDataTable(sql));
}