public static int DeleteReplicate()
{
int rowsAffected = DBL.ExecuteDML($"DELETE FROM ConnectionRequest WHERE ConnectionRequestId not in (SELECT MIN(ConnectionRequestId) FROM ConnectionRequest GROUP BY FK_UserFromId, FK_UserToId)");
return(rowsAffected);
}
public static int De_Endorse(string SkillId)
{
int rowsAffected = DBL.ExecuteDML($"UPDATE Skill SET SkillCounter = SkillCounter - 1 WHERE SkillId = {SkillId}");
return(rowsAffected);
}
public static int InsertNewCountry(string countryName)
{
int rowsAffected = DBL.ExecuteDML($"insert into Country(Name) values('{countryName}')");
return(rowsAffected);
}
public static int RemovebyParams(string FK_SkillId, Guid FK_UserFromId, Guid FK_UserToId)
{
int rowsAffected = DBL.ExecuteDML($"delete from Endorsement where(FK_SkillId = {FK_SkillId} and FK_UserFromId = '{FK_UserFromId}' and FK_UserToId = '{FK_UserToId}')");
return(rowsAffected);
}
public static int Add(string SkillName, string FK_SkillLevelId, Guid FK_UserId)
{
int rowsAffected = DBL.ExecuteDML($"insert into Skill(SkillName, FK_SkillLevelId, FK_UserId, SkillCounter) values('{SkillName}',{FK_SkillLevelId},'{FK_UserId}',0)");
return(rowsAffected);
}
public static int UpdateUserfullData(Guid FK_UserId, string PostalCode, string FK_CountryId, string Summary)
{
int rowsAffected = DBL.ExecuteDML($"Update UserFull set PostalCode='{PostalCode}',FK_CountryId={FK_CountryId},Summary='{Summary}' WHERE FK_UserId = '{FK_UserId}'");
return(rowsAffected);
}
public static int UpdateUserSummary(Guid FK_UserId, string Summary)
{
int rowsAffected = DBL.ExecuteDML($"Update UserFull set Summary = '{Summary}' WHERE FK_UserId = '{FK_UserId}'");
return(rowsAffected);
}
//public static int GetLatestId()
//{
// object PicId = DBL.ExecuteScalar("select PicturesDB.Id from PicturesDB where PicturesDB.Id=(select max(PicturesDB.Id) from PicturesDB)");
// return Convert.ToInt32(PicId);
//}
//public static int AddandGetId(string fileName, int fileSize, byte[] bytes)
//{
// string procName = "sqlUploadPicture";
// int newPicId = DBL.AddImage(procName, fileName, fileSize, bytes);
// return newPicId;
//}
//public static int Update(int Id, int fileSize, byte[] bytes)
//{
// int rowsAffected = DBL.ExecuteDML("update Pictures set Size='" + fileSize + "' , ImageData=" + bytes + " where Id=" + Id);
// return rowsAffected;
//}
public static int Remove(int Id)
{
int rowsAffected = DBL.ExecuteDML("delete from Education where FK_UserId =" + Id);
return(rowsAffected);
}
public static int AddProject(Guid userId, string projectName, string description, string startDate, string endDate)
{
int rowsAffected = DBL.ExecuteDML($"insert into Project (ProjectName, Description, StartDate, EndDate, FK_UserId) values ('{projectName}', '{description}', '{startDate}', '{endDate}', '{userId}') ");
return(rowsAffected);
}
public static int insertUserAward(string AwardName, string Date, string Provider, Guid UserId)
{
int rowsAffected = DBL.ExecuteDML("insert into Award (AwardName,AwardDate,AwardProvider,FK_UserId) values('" + AwardName + "','" + Date + "','" + Provider + "','" + UserId + "' ) ");
return(rowsAffected);
}
public static int InsertUserEducation(string SchoolName, string StartDate, string EndDate, string SchoolLocation, string specialization, Guid UserId)
{
int rowsAffected = DBL.ExecuteDML("insert into Education (SchoolName,StartDate,EndDate,SchoolLocation,Specialization,FK_UserId) values('" + SchoolName + "','" + StartDate + "','" + EndDate + "','" + SchoolLocation + "','" + specialization + "','" + UserId + "' ) ");
return(rowsAffected);
}
public static int DeleteConnectionFromTo(Guid fromId, Guid toId)
{
int rowsAffected = DBL.ExecuteDML($"DELETE FROM ConnectionRequest WHERE (FK_UserFromId = '{fromId}' and FK_UserToId = '{toId}')");
return(rowsAffected);
}
public static int DeleteConnectionById(int connId)
{
int rowsAffected = DBL.ExecuteDML($"DELETE FROM ConnectionRequest WHERE ConnectionRequestId = {connId}");
return(rowsAffected);
}
public static int DeleteConnectionBetween(Guid id1, Guid id2)
{
int rowsAffected = DBL.ExecuteDML($"DELETE FROM ConnectionRequest WHERE (FK_UserFromId = '{id1}' and FK_UserToId = '{id2}') or (FK_UserFromId = '{id2}' and FK_UserToId = '{id1}')");
return(rowsAffected);
}
public static int Remove(string Id)
{
int rowsAffected = DBL.ExecuteDML("delete from PicturesDB where Id=" + Id);
return(rowsAffected);
}
public static int Update(int projectId, string projectName, string description, string startDate, string endDate)
{
int rowsAffected = DBL.ExecuteDML($"update Project set ProjectName = '{projectName}' , Description = '{description}', StartDate = '{startDate}', EndDate = '{endDate}' where ProjectId = '{projectId}' ");
return(rowsAffected);
}
public static int InsertUserfullData(Guid FK_UserId, string PostalCode, string FK_CountryId, string Summary)
{
int rowsAffected = DBL.ExecuteDML($"insert into UserFull(FK_UserId, PostalCode, FK_CountryId, Summary) values('{FK_UserId}','{PostalCode}','{FK_CountryId}','{Summary}')");
return(rowsAffected);
}
public static int Delete(int projectId)
{
int rowsAffected = DBL.ExecuteDML($"Delete from Project where ProjectId = '{projectId}'");
return(rowsAffected);
}
public static int InsertUserSummary(Guid FK_UserId, string Summary)
{
int rowsAffected = DBL.ExecuteDML($"insert into UserFull(FK_UserId, Summary) values('{FK_UserId}','{Summary}')");
return(rowsAffected);
}
public static int DeleteUserLanguagee(string FK_LanguageId)
{
int rowsAffected = DBL.ExecuteDML("delete UserLanguage where UserLanguage.Fk_LanguageId=" + FK_LanguageId);
return(rowsAffected);
}
public static int Add(string FK_SkillId, Guid FK_UserFromId, Guid FK_UserToId)
{
int rowsAffected = DBL.ExecuteDML($"insert into Endorsement(FK_SkillId, FK_UserFromId, FK_UserToId) values({FK_SkillId},'{FK_UserFromId}','{FK_UserToId}')");
return(rowsAffected);
}
public static int Updatelang(int FK_LanguageId, int FK_LanguageLevel, int UserLanguageId)
{
int rowsAffected = DBL.ExecuteDML($"update UserLanguage set FK_LanguageId = '{FK_LanguageId}' , FK_LanguageLevel = '{FK_LanguageLevel}' where UserLanguageId = '{UserLanguageId}' ");
return(rowsAffected);
}
public static int RemovebyId(string EndorsementId)
{
int rowsAffected = DBL.ExecuteDML($"delete from Endorsement where EndorsementId={EndorsementId}");
return(rowsAffected);
}
public static int insertUserLanguage(Guid Fk_UserId, string FK_LanguageId)
{
int rowsAffected = DBL.ExecuteDML("insert into UserLanguage (Fk_UserId, FK_LanguageId) values ('" + Fk_UserId + "', " + FK_LanguageId + ")");
return(rowsAffected);
}
public static int Update(string SkillName, string FK_SkillLevelId, string SkillId)
{
int rowsAffected = DBL.ExecuteDML($"Update Skill set SkillName = '{SkillName}', FK_SkillLevelId = {FK_SkillLevelId} WHERE SkillId = {SkillId}");
return(rowsAffected);
}
public static int InsertUserCountryAndPostalCode(Guid UserId, int postalCode, string CountryId)
{
int rowsAffected = DBL.ExecuteDML($"insert into UserFull (Fk_UserId,PostalCode, FK_CountryId) values ('{UserId}', '{postalCode}', {CountryId})");
return(rowsAffected);
}
public static int Remove(string SkillId)
{
int rowsAffected = DBL.ExecuteDML($"delete from Skill where SkillId = {SkillId}");
return(rowsAffected);
}
public static int UpdateUserCoverPic(Guid userId, int picId)
{
int rowsAffected = DBL.ExecuteDML($"update UserFull set FK_ProfilePhotoId = {picId} where FK_UserId = '{userId}'");
return(rowsAffected);
}
//Insert All At User Work Experience
public static int InsertNewUserExperience(Guid FK_UserId, string Country, string Company, string Title, string StartingDate)
{
int rowsAffected = DBL.ExecuteDML("insert into UserWorkExperience (FK_UserId,Country,Company,Title,StartingDate) values('" + FK_UserId + "','" + Country + "','" + Company + "','" + Title + "','" + StartingDate + "')");
return(rowsAffected);
}
public static int ModifyConnectionRejected(Guid fromId, Guid toId)
{
int rowsAffected = DBL.ExecuteDML($"update ConnectionRequest set [Status] = 'Disconnected' where( FK_UserFromId = '{fromId}' and FK_UserToId = '{toId}')");
return(rowsAffected);
}