Skip to content
Permalink
Fetching contributors…
Cannot retrieve contributors at this time
849 lines (708 sloc) 29 KB
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace LearningLibrary
{
public class SqlUtil
{
//main SQL
public static SqlConnection OLPDConn()
{
return new SqlConnection(connectionString: @"Data Source=198.71.225.146,1433;Initial Catalog=amagee_d_OLP; User ID=db_d_OLP;Password=esports19;");
}
//logging
public static void AppLog(string Username, string message)
{
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("INSERT INTO [AppLog] ([LogDate], [Username], [LogMsg]) VALUES (GETDATE(), @Usern, @Msg);", conn))
{
comm.Parameters.Add(new SqlParameter("@Usern", SqlDbType.VarChar)).Value = Username;
comm.Parameters.Add(new SqlParameter("@Msg", SqlDbType.VarChar)).Value = message;
comm.ExecuteNonQuery();
comm.Dispose();
}
conn.Dispose();
}
}
catch //nothing
{
//nope
}
}
//User
public static bool UserCreateNewAccount(string FullName, string UserName, string EmailAddr, string Password)
{
bool retval = false;
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("INSERT INTO Accounts " +
"(FullName, UserName, EmailAddr, PasswHash, AccountLvl, AccountCreatedOn, ProfileImgFileName) " +
"VALUES (@fullname, @usern, @emailaddr, HASHBYTES('SHA2_512', @psswd), 0, GETDATE(), null); SELECT SCOPE_IDENTITY();", conn))
{
comm.Parameters.Add(new SqlParameter("@fullname", SqlDbType.VarChar)).Value = FullName.Trim();
comm.Parameters.Add(new SqlParameter("@usern", SqlDbType.VarChar)).Value = UserName.Trim();
comm.Parameters.Add(new SqlParameter("@psswd", SqlDbType.VarChar)).Value = Password.Trim();
comm.Parameters.Add(new SqlParameter("@emailaddr", SqlDbType.VarChar)).Value = EmailAddr.Trim();
comm.ExecuteNonQuery();
retval = true;
comm.Dispose();
}
conn.Dispose();
}
}
catch (Exception ex)
{ //err return false
AppLog("UserCreateNewAccount Error", ex.ToString());
}
return retval;
}
public static bool LoginUser(string Username, string Password)
{
bool retval = false;
//SqlParameter returnParameter = new SqlParameter();
int returned = 0; //default as failed (anything other than 1). 0 is also returned if Null is returned from the db
try
{
using (SqlConnection conn = OLPDConn())
{
using (SqlCommand comm = new SqlCommand("SELECT COUNT(AccountID) FROM Accounts WHERE UserName= @usern AND PasswHash = HASHBYTES('SHA2_512', @passwd)", conn))
{
conn.Open();
comm.Parameters.Add(new SqlParameter("@usern", SqlDbType.VarChar)).Value = Username.Trim();
comm.Parameters.AddWithValue("@passwd", Password.Trim());
//comm.Parameters.Add(new SqlParameter("@passwd", SqlDbType.VarChar)).Value = Password.Trim(); //Doesnt work? //SqlDataType wrong
returned = Convert.ToInt32(comm.ExecuteScalar()); //if count 1, there is a match, if not, too bad, go cry about it you arent gonna log in
comm.Dispose();
}
conn.Dispose();
}
}
catch (Exception ex)
{ //err return false
AppLog("", ex.ToString());
}
if (returned == 1) //login success
{
AppLog(Username, "Logged in");
retval = true;
}
return retval;
}
public static bool CheckUname(string InputUsername) //check if uname exists (account creation)
{
bool retval;
string select = "";
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("SELECT TOP 1 UserName FROM Accounts WHERE UserName = @usern;", conn))
{
comm.Parameters.Add(new SqlParameter("@usern", SqlDbType.VarChar)).Value = InputUsername.Trim();
select = (string)comm.ExecuteScalar();
conn.Close();
comm.Dispose();
conn.Dispose();
}
}
}
catch (Exception ex)
{
AppLog("APP - UnameCheck", ex.ToString());
}
if (select != null)
{
retval = true; //uname exists, cant use it
}
else
{
retval = false; //uname can be used, exists = false
}
return retval;
}
public static string GetUnameFromEmail(string email, string Username)
{
string uname = null;
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("SELECT TOP 1 [UserName] FROM [Accounts] WHERE [EmailAddr] = @Email;", conn))
{
comm.Parameters.Add(new SqlParameter("@Email", SqlDbType.VarChar)).Value = email;
comm.Parameters.AddWithValue("@Email", email);
uname = comm.ExecuteScalar().ToString();
comm.Dispose();
}
conn.Dispose();
}
}
catch (Exception ex)
{
AppLog(Username, ex.ToString());
}
return uname;
}
public static int GetUserIDFromUsername(string Usern)
{
int un = 0;
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("SELECT [AccountID] FROM [Accounts] WHERE [UserName] = @Username;", conn))
{
comm.Parameters.Add(new SqlParameter("@Username", SqlDbType.VarChar)).Value = Usern;
un = int.Parse(comm.ExecuteScalar().ToString());
comm.Dispose();
}
conn.Dispose();
}
}
catch (Exception ex)
{
AppLog("APP - CheckEmail()", ex.ToString());
}
return un;
}
public static bool CheckEmail(string EmailAddr) //check if email already exists (account creation)
{
string select = null;
bool retval = false;
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("SELECT TOP 1 [EmailAddr] FROM [Accounts] WHERE [EmailAddr] = @email ;", conn))
{
comm.Parameters.Add(new SqlParameter("@email", SqlDbType.VarChar)).Value = EmailAddr;
select = (string)comm.ExecuteScalar();
conn.Close();
comm.Dispose();
}
conn.Dispose();
}
}
catch (Exception ex)
{
AppLog("APP - CheckEmail()", ex.ToString());
}
if (select != null)
{
retval = true; //email exists, cant use it
}
return retval;
}
public static void UserChangePassword()
{
//TODO
}
public static int GetCountOfCoursesByUsername(string CreatedBy)
{
int ret = 0;
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("SELECT COUNT([CreatedBy]) FROM [Courses] WHERE [CreatedBy] = @CreatedBy ;", conn))
{
comm.Parameters.Add(new SqlParameter("@CreatedBy", SqlDbType.VarChar)).Value = CreatedBy;
ret = int.Parse(comm.ExecuteScalar().ToString());
comm.Dispose();
}
conn.Dispose();
}
}
catch (Exception ex)
{ //use same arg for name bc its called from MyProfile
AppLog(CreatedBy, ex.ToString());
}
return ret;
}
public static bool CheckIfUserCanUpload(int UserID, string Username)
{
bool ret = false;
int SelectLvl = 0;
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("SELECT [canUpload] FROM [Accounts] WHERE [AccountID] = " + UserID.ToString(), conn))
{
//comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int)).Value = UserID;
SelectLvl = (int)comm.ExecuteScalar();
comm.Dispose();
}
conn.Dispose();
}
}
catch (SqlException ex)
{
AppLog(Username, ex.ToString());
}
if (SelectLvl == 1)
{
ret = true;
}
return ret;
}
//public static bool UserRequestUploadPriv(int UserId, string Username) //TODO: Add a new table for this, add an admin view that shows / logic, currently uses an email submission
//{
// bool ret = false;
// return ret;
//}
public static bool SetUserAsUploader(int UserID, string Username) //TODO add this to an admin dashboard?
{
bool ret = false;
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("UPDATE [Accounts] SET [AccountLvl] = 1 WHERE [AccountID] = @UserID ;", conn))
{
comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar)).Value = UserID;
ret = true;
comm.Dispose();
}
conn.Dispose();
}
}
catch (SqlException ex)
{
AppLog(Username, ex.ToString());
}
return ret;
}
public static bool RevokeUserUploadPriv(int UserID, string Username) //admin use only, do not expose on normal site
{
bool ret = false;
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("UPDATE [Accounts] SET [AccountLvl] = 0 WHERE [AccountID] = @UserID ;", conn))
{
comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar)).Value = UserID;
ret = true;
comm.Dispose();
}
conn.Dispose();
}
}
catch (SqlException ex)
{
AppLog(Username, ex.ToString());
}
return ret;
}
//course
public static Course GetCourse(string CourseID, string Username)
{
DataTable dt = new DataTable();
Course selectedCourse = null;
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("SELECT TOP 1 [CourseID] ,[CourseName] ,[CourseSubject] ,[CourseDesc] ,[CreatedBy] ,[CourseNumber] " +
", [CourseCoverImgPath] FROM [Courses] WHERE [CourseID] = @CourseID ;", conn))
{
comm.Parameters.Add(new SqlParameter("@CourseID", SqlDbType.Int)).Value = CourseID;
SqlDataAdapter da = new SqlDataAdapter(comm);
da.Fill(dt);
comm.Dispose();
da.Dispose();
}
conn.Dispose();
}
}
catch (SqlException SqEx)
{
AppLog(Username, SqEx.ToString());
}
foreach (DataRow dr in dt.Rows)
{
selectedCourse = new Course(
int.Parse(dr["CourseID"].ToString()),
dr["CourseName"].ToString(),
dr["CourseSubject"].ToString(),
dr["CourseDesc"].ToString(),
dr["CreatedBy"].ToString(),
0, //CourseNumber, leave this 0 until needed
dr["CourseCoverImgPath"].ToString()
);
}
AppLog(Username, "Viewed course: CourseID=" + CourseID);
return selectedCourse;
}
public static bool CourseCreateNewCourse(string CourseName, string CourseSubject, string CourseDesc, string CreatedBy)
{
bool retval = false;
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
string commText = "INSERT INTO Courses (CourseName, CourseSubject, CourseDesc, CreatedBy) " +
"VALUES (@CourseName, @CourseSubject, @CourseDesc, @CreatedBy);";
using (SqlCommand comm = new SqlCommand(commText, conn))
{
comm.Parameters.Add(new SqlParameter("@CourseName", SqlDbType.VarChar)).Value = CourseName;
comm.Parameters.Add(new SqlParameter("@CourseSubject", SqlDbType.VarChar)).Value = CourseSubject;
comm.Parameters.Add(new SqlParameter("@CourseDesc", SqlDbType.VarChar)).Value = CourseDesc;
comm.Parameters.Add(new SqlParameter("@CreatedBy", SqlDbType.VarChar)).Value = CreatedBy;
comm.ExecuteNonQuery();
comm.Dispose();
conn.Dispose();
}
}
retval = true;
AppLog(CreatedBy, "Created new course: " + CourseName);
}
catch (Exception ex)
{
AppLog(CreatedBy, ex.ToString());
}
AppLog(CreatedBy, "Created new course: " + CourseName);
return retval;
}
public static bool CourseModify(string CourseID, string CourseName, string CourseSubject, string CourseDesc, string CoverImgPath, string Username) //TODO - parameterize
{
bool retval = false;
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("UPDATE [Courses] SET [CourseName] = '" + CourseName + "', " +
"[CourseSubject] = '" + CourseSubject + "', [CourseDesc] = '" + CourseDesc + "', [CourseCoverImgPath] = '"+ CoverImgPath + "' " +
"WHERE [CourseID] = " + CourseID + ";", conn))
{
comm.ExecuteNonQuery();
comm.Dispose();
conn.Dispose();
}
}
}
catch (Exception ex)
{
AppLog(Username, ex.ToString());
}
AppLog(Username, "Edited Course, CourseID: " + CourseID);
return retval;
}
public static bool CourseStoreVideoData(string ForCourse, string FilePath, string VideoTitle, string VideoDesc, string TimeLength, string Username) //TODO: parameterize
{
bool retval;
//save the data
try
{
int CourseIndex = GetTotalVideosInCourse(ForCourse, Username) + 1;
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("INSERT INTO [Videos] ([ForCourse], [FilePath], [VideoTitle], [VideoDesc], [VideoTimeLength], [CourseIndex]) VALUES ('" + ForCourse + "','" + FilePath + "', '" + VideoTitle + "', '" +
VideoDesc + "', '" + TimeLength + "', '" + CourseIndex + "');", conn))
{
comm.ExecuteNonQuery();
comm.Dispose();
conn.Dispose();
retval = true;
}
}
}
catch (SqlException sqlEx)
{
AppLog(Username, sqlEx.ToString());
retval = false;
}
AppLog(Username, "Uploaded video: " + VideoTitle + " for course: " + ForCourse);
return retval;
}
public static int GetTotalVideosInCourse(string ForCourse, string Username)
{
int vidCount = 0;
//int CourseNumber = int.Parse(ForCourse);
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("SELECT COUNT([VidID]) FROM [Videos] WHERE [ForCourse] = @ForCourse ;", conn))
{
comm.Parameters.Add(new SqlParameter("@ForCourse", SqlDbType.Int)).Value = ForCourse;
vidCount = (int)comm.ExecuteScalar();
conn.Close();
comm.Dispose();
conn.Dispose();
}
}
}
catch (SqlException SqEx)
{
AppLog(Username, SqEx.ToString());
}
return vidCount;
}
public static int GetVideoIDFromForCourseAndCourseIndex(int CourseIndex, int ForCourse)
{
int VidID = 0;
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("SELECT VidID FROM Videos WHERE ForCourse = @ForCourse AND CourseIndex = @CourseIndex ;", conn))
{
comm.Parameters.Add(new SqlParameter("@ForCourse", SqlDbType.Int)).Value = ForCourse;
comm.Parameters.Add(new SqlParameter("@CourseIndex", SqlDbType.Int)).Value = CourseIndex;
VidID = (int)comm.ExecuteScalar();
comm.Dispose();
conn.Dispose();
}
}
}
catch (SqlException SqEx)
{
AppLog("", SqEx.ToString());
}
return VidID;
}
public static bool CourseVideoIndexChange(string Uname, int ForCourse, int VideoOne, string Direction)
{
bool r = false;
int VideoTwo = 0; //the is the video that is either above or below the video being rearranged
if (Direction == "UP")
{
VideoTwo = VideoOne - 1; //this will be CourseIndex later on
}
if (Direction == "DOWN")
{
VideoTwo = VideoOne + 1; //this will be CourseIndex later on
}
//Get video IDs
int VidID1 = GetVideoIDFromForCourseAndCourseIndex(VideoOne, ForCourse);
int VidID2 = GetVideoIDFromForCourseAndCourseIndex(VideoTwo, ForCourse);
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("UPDATE Videos SET " +
"CourseIndex = @Index2 WHERE ForCourse = @ForCourse AND VidID = @VidID1 " +
"UPDATE Videos SET CourseIndex = @Index1 WHERE ForCourse = @ForCourse AND VidID = @VidID2", conn))
{
comm.Parameters.Add(new SqlParameter("@Index1", SqlDbType.Int)).Value = VideoOne;
comm.Parameters.Add(new SqlParameter("@Index2", SqlDbType.Int)).Value = VideoTwo;
comm.Parameters.Add(new SqlParameter("@VidID1", SqlDbType.Int)).Value = VidID1;
comm.Parameters.Add(new SqlParameter("@VidID2", SqlDbType.Int)).Value = VidID2;
comm.Parameters.Add(new SqlParameter("@ForCourse", SqlDbType.Int)).Value = ForCourse;
comm.ExecuteNonQuery();
comm.Dispose();
}
using (SqlCommand comm = new SqlCommand("UPDATE Videos SET CourseIndex = "
+ VideoOne + " WHERE ForCourse = " + ForCourse + " AND VidID = " + VidID2 + "", conn))
{
comm.ExecuteNonQuery();
comm.Dispose();
}
conn.Dispose();
conn.Close();
}
}
catch (Exception ex)
{ //err return false
AppLog(Uname, ex.ToString());
}
return r;
}
public static bool CourseDeleteVideo(int VidId, string Username) //TODO: reference this and test it (not used yet)
{
bool didDelete = false;
SqlParameter VidIDParam = new SqlParameter("@VidId", SqlDbType.Int)
{
Value = VidId
};
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("DELETE FROM [Videos] WHERE [VidID] = @VidId;", conn))
{
comm.Parameters.Add(VidIDParam);
comm.Dispose();
}
conn.Dispose();
}
didDelete = true;
}
catch (Exception ex)
{
AppLog(Username, ex.ToString());
}
return didDelete;
}
public static List<CourseVideo> GetCourseVideos(int CourseID, string Username)
{
List<CourseVideo> VideoList = new List<CourseVideo>();
DataTable dt = new DataTable();
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("SELECT [VidID] ,[ForCourse] ,[FilePath] ,[VideoTitle] ," +
"[VideoDesc] ,[VideoTimeLength] ," +
"[CourseIndex] FROM [Videos] WHERE [ForCourse] = " + CourseID, conn))
{
SqlDataAdapter da = new SqlDataAdapter(comm);
da.Fill(dt);
comm.Dispose();
da.Dispose();
}
conn.Dispose();
}
}
catch (SqlException SqEx)
{
AppLog(Username, SqEx.ToString());
}
foreach (DataRow dr in dt.Rows)
{
CourseVideo v = null;
v = new CourseVideo(
int.Parse(dr["VidID"].ToString()),
int.Parse(dr["ForCourse"].ToString()),
dr["FilePath"].ToString(),
dr["VideoTitle"].ToString(),
dr["VideoDesc"].ToString(),
dr["VideoTimeLength"].ToString(),
int.Parse(dr["CourseIndex"].ToString())
);
VideoList.Add(v);
}
return VideoList;
}
public static List<Course> GetCourses(string Username)
{
List<Course> CourseList = new List<Course>();
DataTable dt = new DataTable();
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("SELECT [CourseID] ,[CourseName] ,[CourseSubject] ,[CourseDesc] ," +
"[CreatedBy] ,[CourseNumber], [CourseCoverImgPath] FROM [Courses] ORDER BY [CourseID] DESC", conn))
{
SqlDataAdapter da = new SqlDataAdapter(comm);
da.Fill(dt);
comm.Dispose();
da.Dispose();
}
conn.Dispose();
}
}
catch (SqlException SqEx)
{
AppLog(Username, SqEx.ToString());
}
foreach (DataRow dr in dt.Rows)
{
Course selectedCourse = new Course(
int.Parse(dr["CourseID"].ToString()),
dr["CourseName"].ToString(),
dr["CourseSubject"].ToString(),
dr["CourseDesc"].ToString(),
dr["CreatedBy"].ToString(),
0, //leave as 0 till needed
dr["CourseCoverImgPath"].ToString()
);
CourseList.Add(selectedCourse);
}
return CourseList;
}
//video
public static Video GetVideoDetailsById(int VidId, string uname)
{
Video SelectedVid = null;
DataTable dt = new DataTable();
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("SELECT [VidID], [ForCourse], [FilePath], [VideoTitle], [VideoDesc], [VideoTimeLength], [CourseIndex] FROM [Videos] WHERE [VidID] = @VIDID;", conn))
{
comm.Parameters.Add(new SqlParameter("@VIDID", SqlDbType.Int)).Value = VidId;
SqlDataAdapter da = new SqlDataAdapter(comm);
da.Fill(dt);
comm.Dispose();
da.Dispose();
comm.Dispose();
}
conn.Dispose();
}
}
catch (SqlException ex)
{
AppLog(uname, ex.ToString());
}
foreach (DataRow dr in dt.Rows)
{
SelectedVid = new Video(
int.Parse(dr["VidID"].ToString()),
int.Parse(dr["ForCourse"].ToString()),
dr["FilePath"].ToString(),
dr["VideoTitle"].ToString(),
dr["VideoDesc"].ToString(),
dr["VideoTimeLength"].ToString(),
int.Parse(dr["CourseIndex"].ToString())
);
}
AppLog(uname, "Viewed Video: " + SelectedVid.VideoTitle);
return SelectedVid;
}
public static string GetVideoPath(int VidId, string uname)
{
string p = "";
try
{
using (SqlConnection conn = OLPDConn())
{
conn.Open();
using (SqlCommand comm = new SqlCommand("SELECT [FilePath] FROM [Videos] WHERE [VidID] = @VIDID ;", conn))
{
comm.Parameters.Add(new SqlParameter("@VIDID", SqlDbType.Int)).Value = VidId;
p = comm.ExecuteScalar().ToString();
comm.Dispose();
}
conn.Dispose();
}
}
catch (SqlException ex)
{
AppLog(uname, ex.ToString());
}
return p;
}
}
}
You can’t perform that action at this time.