private void RunUpdateSQLForDeleteSoftAttribute(string Query, ResourceDataVerbose Data) { using (SqlCommand com = new SqlCommand(Query, FilerDB2Connection)) { com.Parameters.AddWithValue("@Name", Data.Name); com.Parameters.AddWithValue("@Class", Data.Class); com.Parameters.AddWithValue("@Cookie", Data.Cookie); com.ExecuteNonQuery(); } }
public int AddLink(ResourceDataVerbose data) { int dataID = 0; //This will hold the dataID for this specific file. string Query = "INSERT INTO Links (Link, Name, Date) VALUES (@Link, @LinkName, @Date)"; using (SqlCommand com = new SqlCommand(Query, FilerDB2Connection)) { com.Parameters.AddWithValue("@Link", data.Contents); com.Parameters.AddWithValue("@LinkName", data.Name); com.Parameters.AddWithValue("@Date", data.Date); using (SqlDataReader reader = com.ExecuteReader()) { reader.Read(); dataID = reader.GetInt32(0); } } return(dataID); }
private string GetUpdateQueryForInsertingSoftAtt(string ColumnName, ResourceDataVerbose Data) { if (Data.IsLink.Equals("false")) { if (ColumnName.Equals("Unit")) { string Query = "Declare @DID INT; " + "Declare @C INT " + "SET @DID = (SELECT Files.DataID FROM Files JOIN Classes ON Files.DataID = Classes.DataID " + "WHERE Files.Name = @Name AND Classes.Class = @Class INTERSECT " + "SELECT UserIDs.DataID FROM UserIDs JOIN Cookies ON UserIDs.UserID = Cookies.UserID " + "WHERE Cookies.Cookie = @Cookie); " + " " + "SET @C = (SELECT COUNT(*) FROM Units WHERE DataID = @DID); " + " " + "IF @C = 1 " + "BEGIN " + " UPDATE Units " + " SET Unit = @UpdatedUnit " + " " + " WHERE DataID = @DID " + "END " + "ELSE " + "BEGIN " + " INSERT INTO Units VALUES(@DID, @UpdatedUnit) " + "END "; return(Query); } if (ColumnName.Equals("Type")) { string Query = "Declare @DID INT; " + "Declare @C INT " + "SET @DID = (SELECT Files.DataID FROM Files JOIN Classes ON Files.DataID = Classes.DataID " + "WHERE Files.Name = @Name AND Classes.Class = @Class INTERSECT " + "SELECT UserIDs.DataID FROM UserIDs JOIN Cookies ON UserIDs.UserID = Cookies.UserID " + "WHERE Cookies.Cookie = @Cookie); " + " " + "SET @C = (SELECT COUNT(*) FROM Types WHERE DataID = @DID); " + " " + "IF @C = 1 " + "BEGIN " + " UPDATE Types " + " SET Type = @UpdatedType " + " " + " WHERE DataID = @DID " + "END " + "ELSE " + "BEGIN " + " INSERT INTO Types VALUES(@DID, @UpdatedType) " + "END "; return(Query); } if (ColumnName.Equals("Comments")) { string Query = "Declare @DID INT; " + "Declare @C INT " + "SET @DID = (SELECT Files.DataID FROM Files JOIN Classes ON Files.DataID = Classes.DataID " + "WHERE Files.Name = @Name AND Classes.Class = @Class INTERSECT " + "SELECT UserIDs.DataID FROM UserIDs JOIN Cookies ON UserIDs.UserID = Cookies.UserID " + "WHERE Cookies.Cookie = @Cookie); " + " " + "SET @C = (SELECT COUNT(*) FROM Comments WHERE DataID = @DID); " + " " + "IF @C = 1 " + "BEGIN " + " UPDATE Comments " + " SET Comment = @UpdatedComments " + " " + " WHERE DataID = @DID " + "END " + "ELSE " + "BEGIN " + " INSERT INTO Comments VALUES(@DID, @UpdatedComments) " + "END "; return(Query); } return(null); } else { if (ColumnName.Equals("Unit")) { string Query = "Declare @DID INT; " + "Declare @C INT " + "SET @DID = (SELECT Links.DataID FROM Links JOIN Classes ON Links.DataID = Classes.DataID " + "WHERE Links.Name = @Name AND Classes.Class = @Class INTERSECT " + "SELECT UserIDs.DataID FROM UserIDs JOIN Cookies ON UserIDs.UserID = Cookies.UserID " + "WHERE Cookies.Cookie = @Cookie); " + " " + "SET @C = (SELECT COUNT(*) FROM Units WHERE DataID = @DID); " + " " + "IF @C = 1 " + "BEGIN " + " UPDATE Units " + " SET Unit = @UpdatedUnit " + " " + " WHERE DataID = @DID " + "END " + "ELSE " + "BEGIN " + " INSERT INTO Units VALUES(@DID, @UpdatedUnit) " + "END "; return(Query); } if (ColumnName.Equals("Type")) { string Query = "Declare @DID INT; " + "Declare @C INT " + "SET @DID = (SELECT Links.DataID FROM Links JOIN Classes ON Links.DataID = Classes.DataID " + "WHERE Links.Name = @Name AND Classes.Class = @Class INTERSECT " + "SELECT UserIDs.DataID FROM UserIDs JOIN Cookies ON UserIDs.UserID = Cookies.UserID " + "WHERE Cookies.Cookie = @Cookie); " + " " + "SET @C = (SELECT COUNT(*) FROM Types WHERE DataID = @DID); " + " " + "IF @C = 1 " + "BEGIN " + " UPDATE Types " + " SET Type = @UpdatedType " + " " + " WHERE DataID = @DID " + "END " + "ELSE " + "BEGIN " + " INSERT INTO Types VALUES(@DID, @UpdatedType) " + "END "; return(Query); } if (ColumnName.Equals("Comments")) { string Query = "Declare @DID INT; " + "Declare @C INT " + "SET @DID = (SELECT Links.DataID FROM Links JOIN Classes ON Links.DataID = Classes.DataID " + "WHERE Links.Name = @Name AND Classes.Class = @Class INTERSECT " + "SELECT UserIDs.DataID FROM UserIDs JOIN Cookies ON UserIDs.UserID = Cookies.UserID " + "WHERE Cookies.Cookie = @Cookie); " + " " + "SET @C = (SELECT COUNT(*) FROM Comments WHERE DataID = @DID); " + " " + "IF @C = 1 " + "BEGIN " + " UPDATE Comments " + " SET Comment = @UpdatedComments " + " " + " WHERE DataID = @DID " + "END " + "ELSE " + "BEGIN " + " INSERT INTO Comments VALUES(@DID, @UpdatedComments) " + "END "; return(Query); } return(null); } }
private string GetUpdateQueryForDeletingSoftAttribute(string ColumnName, ResourceDataVerbose Data) { if (ColumnName.Equals("Unit")) { string Query = ""; if (Data.IsLink.Equals("true")) { Query = "Declare @DID INT; " + "SET @DID = (SELECT Links.DataID FROM Links JOIN Classes ON Links.DataID = Classes.DataID " + "WHERE Links.Name = @Name AND Classes.Class = @Class INTERSECT " + "SELECT UserIDs.DataID FROM UserIDs JOIN Cookies ON UserIDs.UserID = Cookies.UserID " + "WHERE Cookies.Cookie = @Cookie); " + "DELETE FROM Units WHERE Units.DataID = @DID; "; } else { Query = "Declare @DID INT; " + "SET @DID = (SELECT Files.DataID FROM Files JOIN Classes ON Files.DataID = Classes.DataID " + "WHERE Files.Name = @Name AND Classes.Class = @Class INTERSECT " + "SELECT UserIDs.DataID FROM UserIDs JOIN Cookies ON UserIDs.UserID = Cookies.UserID " + "WHERE Cookies.Cookie = @Cookie); " + "DELETE FROM Units WHERE Units.DataID = @DID; "; } return(Query); } if (ColumnName.Equals("Type")) { string Query = ""; if (Data.IsLink.Equals("true")) { Query = "Declare @DID INT; " + "SET @DID = (SELECT Links.DataID FROM Links JOIN Classes ON Links.DataID = Classes.DataID " + "WHERE Links.Name = @Name AND Classes.Class = @Class INTERSECT " + "SELECT UserIDs.DataID FROM UserIDs JOIN Cookies ON UserIDs.UserID = Cookies.UserID " + "WHERE Cookies.Cookie = @Cookie); " + "DELETE FROM Types WHERE Types.DataID = @DID; "; } else { Query = "Declare @DID INT; " + "SET @DID = (SELECT Files.DataID FROM Files JOIN Classes ON Files.DataID = Classes.DataID " + "WHERE Files.Name = @Name AND Classes.Class = @Class INTERSECT " + "SELECT UserIDs.DataID FROM UserIDs JOIN Cookies ON UserIDs.UserID = Cookies.UserID " + "WHERE Cookies.Cookie = @Cookie); " + "DELETE FROM Types WHERE Types.DataID = @DID; "; } return(Query); } if (ColumnName.Equals("Comments")) { string Query = ""; if (Data.IsLink.Equals("true")) { Query = "Declare @DID INT; " + "SET @DID = (SELECT Links.DataID FROM Links JOIN Classes ON Links.DataID = Classes.DataID " + "WHERE Links.Name = @Name AND Classes.Class = @Class INTERSECT " + "SELECT UserIDs.DataID FROM UserIDs JOIN Cookies ON UserIDs.UserID = Cookies.UserID " + "WHERE Cookies.Cookie = @Cookie); " + "DELETE FROM Comments WHERE Comments.DataID = @DID; "; } else { Query = "Declare @DID INT; " + "SET @DID = (SELECT Files.DataID FROM Files JOIN Classes ON Files.DataID = Classes.DataID " + "WHERE Files.Name = @Name AND Classes.Class = @Class INTERSECT " + "SELECT UserIDs.DataID FROM UserIDs JOIN Cookies ON UserIDs.UserID = Cookies.UserID " + "WHERE Cookies.Cookie = @Cookie); " + "DELETE FROM Comments WHERE Comments.DataID = @DID; "; } return(Query); } return(null); }
/// <summary> /// Generates an update query for Name, Contents, and Class. Thus ColumnName must be either Name, Contents, or Class. /// </summary> /// <param name="ColumnName"></param> /// <param name="Data"></param> /// <returns></returns> private string GetUpdateQueryForStrongType(string ColumnName, ResourceDataVerbose Data) { if (ColumnName.Equals("Name")) { string Query = ""; if (Data.IsLink.Equals("true")) { Query = "Declare @DID INT; " + "SET @DID = (SELECT Links.DataID FROM Links JOIN Classes ON Links.DataID = Classes.DataID " + "WHERE Links.Name = @Name AND Classes.Class = @Class INTERSECT " + "SELECT UserIDs.DataID FROM UserIDs JOIN Cookies ON UserIDs.UserID = Cookies.UserID " + "WHERE Cookies.Cookie = @Cookie); " + "UPDATE Links " + "SET Name = @UpdatedName " + "WHERE DataID = @DID "; } else { Query = "Declare @DID INT; " + "SET @DID = (SELECT Files.DataID FROM Files JOIN Classes ON Files.DataID = Classes.DataID " + "WHERE Files.Name = @Name AND Classes.Class = @Class INTERSECT " + "SELECT UserIDs.DataID FROM UserIDs JOIN Cookies ON UserIDs.UserID = Cookies.UserID " + "WHERE Cookies.Cookie = @Cookie); " + "UPDATE Files " + "SET Name = @UpdatedName " + "WHERE DataID = @DID "; } return(Query); } if (ColumnName.Equals("Contents")) { string Query = ""; if (Data.IsLink.Equals("true")) { Query = "Declare @DID INT; " + "SET @DID = (SELECT Links.DataID FROM Links JOIN Classes ON Links.DataID = Classes.DataID " + "WHERE Links.Name = @Name AND Classes.Class = @Class INTERSECT " + "SELECT UserIDs.DataID FROM UserIDs JOIN Cookies ON UserIDs.UserID = Cookies.UserID " + "WHERE Cookies.Cookie = @Cookie); " + "UPDATE Links " + "SET Link = @UpdatedContents " + "WHERE DataID = @DID "; } else { Query = "Declare @DID INT; " + "SET @DID = (SELECT Files.DataID FROM Files JOIN Classes ON Files.DataID = Classes.DataID " + "WHERE Files.Name = @Name AND Classes.Class = @Class INTERSECT " + "SELECT UserIDs.DataID FROM UserIDs JOIN Cookies ON UserIDs.UserID = Cookies.UserID " + "WHERE Cookies.Cookie = @Cookie); " + "UPDATE Files " + "SET Archive = @UpdatedContents " + "WHERE DataID = @DID "; } return(Query); } if (ColumnName.Equals("Class")) { string Query = ""; if (Data.IsLink.Equals("true")) { Query = "Declare @DID INT; " + "SET @DID = (SELECT Links.DataID FROM Links JOIN Classes ON Links.DataID = Classes.DataID " + "WHERE Links.Name = @Name AND Classes.Class = @Class INTERSECT " + "SELECT UserIDs.DataID FROM UserIDs JOIN Cookies ON UserIDs.UserID = Cookies.UserID " + "WHERE Cookies.Cookie = @Cookie); " + "UPDATE Classes " + "SET Class = @UpdatedClass " + "WHERE DataID = @DID "; } else { Query = "Declare @DID INT; " + "SET @DID = (SELECT Files.DataID FROM Files JOIN Classes ON Files.DataID = Classes.DataID " + "WHERE Files.Name = @Name AND Classes.Class = @Class INTERSECT " + "SELECT UserIDs.DataID FROM UserIDs JOIN Cookies ON UserIDs.UserID = Cookies.UserID " + "WHERE Cookies.Cookie = @Cookie); " + "UPDATE Classes " + "SET Class = @UpdatedClass " + "WHERE DataID = @DID "; } return(Query); } return(null); }
public void DoUpdate(ResourceDataVerbose[] Data) { ResourceDataVerbose Current = Data[0]; ResourceDataVerbose Updated = Data[1]; if (IsNullOrEmpty(Current.Name) || IsNullOrEmpty(Current.Class) || IsNullOrEmpty(Current.Cookie) || IsNullOrEmpty(Current.IsLink)) { SetStatus(HttpStatusCode.Forbidden); return; } if (!IsNullOrEmpty(Updated.Contents)) { //do query to update the Contens(this will always be an updated query). string Query = GetUpdateQueryForStrongType("Contents", Current); using (SqlCommand com = new SqlCommand(Query, FilerDB2Connection)) { com.Parameters.AddWithValue("@Name", Current.Name); com.Parameters.AddWithValue("@Class", Current.Class); com.Parameters.AddWithValue("@Cookie", Current.Cookie); com.Parameters.AddWithValue("@UpdatedContents", Updated.Contents); com.ExecuteNonQuery(); } } //Updating the unit information. if (IsNullOrEmpty(Updated.Unit)) { //Perform a delete query for the unit(We don't need to worry about if it doesn't exits. If it does it will be deleted. string Query = GetUpdateQueryForDeletingSoftAttribute("Unit", Current); RunUpdateSQLForDeleteSoftAttribute(Query, Current); } else { //Using sql, do an if statement where if the row exists in the unit table, update it. Otherwise, create a row. string Query = GetUpdateQueryForInsertingSoftAtt("Unit", Current); using (SqlCommand com = new SqlCommand(Query, FilerDB2Connection)) { com.Parameters.AddWithValue("@Name", Current.Name); com.Parameters.AddWithValue("@Class", Current.Class); com.Parameters.AddWithValue("@Cookie", Current.Cookie); com.Parameters.AddWithValue("@UpdatedUnit", Updated.Unit); com.ExecuteNonQuery(); } } //Updating the Type information. if (IsNullOrEmpty(Updated.Type)) { //Perform a delete query for the type(We don't need to worry about if it doesn't exits. If it does it will be deleted. string Query = GetUpdateQueryForDeletingSoftAttribute("Type", Current); RunUpdateSQLForDeleteSoftAttribute(Query, Current); } else { //Using sql, do an if statement where if the row exits in the type table, update it. Otherwise, create a row. string Query = GetUpdateQueryForInsertingSoftAtt("Type", Current); using (SqlCommand com = new SqlCommand(Query, FilerDB2Connection)) { com.Parameters.AddWithValue("@Name", Current.Name); com.Parameters.AddWithValue("@Class", Current.Class); com.Parameters.AddWithValue("@Cookie", Current.Cookie); com.Parameters.AddWithValue("@UpdatedType", Updated.Type); com.ExecuteNonQuery(); } } //Updating the Comments information. if (IsNullOrEmpty(Updated.Comments)) { //Perform a delete query for the Comments(We don't need to worry about if it doesn't exits. If it does it will be deleted. string Query = GetUpdateQueryForDeletingSoftAttribute("Comments", Current); RunUpdateSQLForDeleteSoftAttribute(Query, Current); } else { //Using sql, do an if statement where if the row exits in the Comments table, update it. Otherwise, create a row. string Query = GetUpdateQueryForInsertingSoftAtt("Comments", Current); using (SqlCommand com = new SqlCommand(Query, FilerDB2Connection)) { com.Parameters.AddWithValue("@Name", Current.Name); com.Parameters.AddWithValue("@Class", Current.Class); com.Parameters.AddWithValue("@Cookie", Current.Cookie); com.Parameters.AddWithValue("@UpdatedComments", Updated.Comments); com.ExecuteNonQuery(); } } if (!IsNullOrEmpty(Updated.Name)) { if (NameIsTaken(Updated.Name, Current.Class, Current.Cookie, Current.IsLink)) { SetStatus(HttpStatusCode.Conflict); return; } //Do query to update the name(this will always be an update query). string Query = GetUpdateQueryForStrongType("Name", Current); using (SqlCommand com = new SqlCommand(Query, FilerDB2Connection)) { com.Parameters.AddWithValue("@Name", Current.Name); com.Parameters.AddWithValue("@Class", Current.Class); com.Parameters.AddWithValue("@Cookie", Current.Cookie); com.Parameters.AddWithValue("@UpdatedName", Updated.Name); com.ExecuteNonQuery(); } } if (!IsNullOrEmpty(Updated.Class)) { if (NameIsTaken(Current.Name, Updated.Class, Current.Cookie, Current.IsLink)) { SetStatus(HttpStatusCode.Conflict); return; } //Do query to update the class(this will always be an update query). string Query = GetUpdateQueryForStrongType("Class", Current); using (SqlCommand com = new SqlCommand(Query, FilerDB2Connection)) { com.Parameters.AddWithValue("@Name", Current.Name); com.Parameters.AddWithValue("@Class", Current.Class); com.Parameters.AddWithValue("@Cookie", Current.Cookie); com.Parameters.AddWithValue("@UpdatedClass", Updated.Class); com.ExecuteNonQuery(); } } SetStatus(HttpStatusCode.OK); return; }
public void AddResource(ResourceDataVerbose data) { if (IsNullOrEmpty(data.Date) || IsNullOrEmpty(data.Class) || IsNullOrEmpty(data.IsLink) || IsNullOrEmpty(data.Override) || IsNullOrEmpty(data.Cookie) || IsNullOrEmpty(data.Contents) || IsNullOrEmpty(data.Name)) { SetStatus(HttpStatusCode.Forbidden); return; } //Check to make sure the Class,Name,Cookie combo is not already taken. If it is we either delete the current resource or return conflict to client. if (NameAndClassAndCookieAlreadyExists(data.Name, data.Class, data.Cookie)) { if (data.Override.Equals("true")) { //This item that matches this one in the db must be delete to make room for the new one. Delete(new DeleteData { Name = data.Name, Class = data.Class, Cookie = data.Cookie, IsLink = data.IsLink }); } else { SetStatus(HttpStatusCode.Conflict); return; } } int DataID = 0; //Add the file or link to db. if (data.IsLink.Equals("true")) { DataID = AddLink(data); } if (data.IsLink.Equals("false")) { DataID = AddFile(data); } //Add any necessary additional data to db. //The second query involves updating all of the info associated with this file(class, unit, type, comments, cookie). string Query = "INSERT INTO Classes VALUES(@DataID, @Class) " + "INSERT INTO UserIDs VALUES(@DataID, (SELECT UserID FROM Cookies WHERE Cookie = @Cookie)) "; if (!IsNullOrEmpty(data.Unit)) { Query += "INSERT INTO Units VALUES(@DataID, @Unit) "; } if (!IsNullOrEmpty(data.Type)) { Query += "INSERT INTO Types VALUES (@DataID, @Type)"; } if (!IsNullOrEmpty(data.Comments)) { Query += "INSERT INTO Comments VALUES(@DataID, @Comments)"; } using (SqlCommand com = new SqlCommand(Query, FilerDB2Connection)) { com.Parameters.AddWithValue("@Class", data.Class); com.Parameters.AddWithValue("@Cookie", data.Cookie); com.Parameters.AddWithValue("@DataID", DataID); if (!IsNullOrEmpty(data.Unit)) { com.Parameters.AddWithValue("@Unit", data.Unit); } if (!IsNullOrEmpty(data.Type)) { com.Parameters.AddWithValue("@Type", data.Type); } if (!IsNullOrEmpty(data.Comments)) { com.Parameters.AddWithValue("@Comments", data.Comments); } com.ExecuteNonQuery(); //This query inserts the file or link information into the database. } SetStatus(HttpStatusCode.Accepted); }