public void UpdatePublication()
{
Querry = "";
HelperClasses.SqlParm s1 = new HelperClasses.SqlParm();
s1.Add("Title", Title);
s1.Add("pd", PublishedDate);
s1.Add("desc", Description);
s1.Add("Author", MakeAuthorsString());
s1.Add("pid", PublishedId);
s1.Add("eid", EmployeeId);
if (File == null)
{
Querry = "update Publications set Authors=@Author,Description=@desc,PublishDate=@pd,Title=@Title where id=@pid and Employeeid=@eid";
}
else
{
LeaveBusinessLayer lb = new LeaveBusinessLayer();
FileBytes = lb.GetFileBytes(File);
s1.Add("file", FileBytes);
s1.Add("fn", File.FileName);
Querry = @"update Publications set Authors=@Author,Description=@desc,PublishDate=@pd,Title=@Title where id=@pid and Employeeid=@eid
declare @fileid as int
select @fileid=Fileid from PublicationAttachment inner join Publications
on
Publications.id=PublicationAttachment.PublicationId
where PublicationId=@pid and Employeeid=@eid
if(@fileid is not null)
begin
update Files set Content=@file where FileId=@fileid
update PublicationAttachment set FileName=@fn where Fileid=@fileid and PublicationId=@pid
end";
}
database.ExecuteQuerry(Querry, s1.GetParmList());
}
public void Update(int EmpId)
{
string querry = "update Picture set Picture=@img where EmployeeID=@emp";
HelperClasses.SqlParm sq = new HelperClasses.SqlParm();
sq.Add("img", Image);
sq.Add("emp", EmpId);
database.ExecuteQuerry(querry, sq.GetParmList());
}
public void DeletePublication()
{
Querry = @"DECLARE @fileid AS INT
set @fileid =(select FileId from PublicationAttachment inner join Publications on Publications.id=PublicationAttachment.PublicationId where Publications.id=@PublicationId and Publications.Employeeid=@EmployeeID)
if(@fileid is not null)
begin
delete from Publications where id=@PublicationId and Employeeid=@EmployeeID;
delete from Files where FileId=@fileid
end
";
HelperClasses.SqlParm p1 = new HelperClasses.SqlParm();
p1.Add("EmployeeID", EmployeeId);
p1.Add("PublicationId", PublishedId);
database.ExecuteQuerry(Querry, p1.GetParmList());
}
public void InsertPublication()
{
List <SqlParameter> parm = new List <SqlParameter>();
LeaveBusinessLayer lb = new LeaveBusinessLayer();
FileBytes = lb.GetFileBytes(File);
Querry = @"DECLARE @pid AS INT, @fileid int
insert into Publications(Employeeid,Title,PublishDate,Description,Authors) values(@Employeeid,@Title,@PublishDate,@Description,@Authors)
set @pid = SCOPE_IDENTITY()
Insert into Files(Content) values(@Content)
set @fileid = SCOPE_IDENTITY()
Insert into PublicationAttachment(FileId,PublicationId,FileName) values(@fileid,@pid,@FileName)";
HelperClasses.SqlParm sq = new HelperClasses.SqlParm();
sq.Add("Employeeid", EmployeeId);
sq.Add("Title", Title);
sq.Add("PublishDate", PublishedDate);
sq.Add("Description", Description);
sq.Add("Authors", MakeAuthorsString());
sq.Add("Content", FileBytes);
sq.Add("FileName", File.FileName);
database.ExecuteQuerry(Querry, sq.GetParmList());
}