public void AddCategory(ModelCategory newCategory)
{
int nLastedId = -1;
string sQuery = @"INSERT INTO TB_CATEGORY (PID,PRJID,CODE,DISPNM,DESCRPT,CREATED,UPDATED)
VALUES (@PID ,@PRJID ,@CODE ,@DISPNM ,@DESCRPT ,GETDATE() ,NULL)
SELECT IDENT_CURRENT('TB_CATEGORY')";
UDataQuerySet set = new UDataQuerySet(sQuery);
set.AddParam("@PID", newCategory.ParentId);
set.AddParam("@PRJID", newCategory.ProjectId);
set.AddParam("@CODE", newCategory.Code);
set.AddParam("@DISPNM", newCategory.DisplayName);
set.AddParam("@DESCRPT", newCategory.Description);
try
{
nLastedId = m_agent.GetValue <int>(set);
newCategory.Id = nLastedId;
}
catch (Exception ex)
{
log.Error(ex);
newCategory.Id = -1;
}
}
public void AddRequirement(ModelReqmnt newRequirement)
{
int nLastedId = -1;
UDataQuerySet set = new UDataQuerySet("SP_REQ_INSERT", CommandType.StoredProcedure);
set.AddParam("@PRJID", newRequirement.ProjectId);
set.AddParam("@CATID", newRequirement.CategoryId);
set.AddParam("@IDX", newRequirement.Index);
set.AddParam("@SRCID", newRequirement.SourceId);
set.AddParam("@REQNMT", newRequirement.Requirement.Trim());
try
{
nLastedId = m_agent.GetValue <int>(set);
newRequirement.Id = nLastedId;
foreach (ModelRemark remark in newRequirement.AllRemark)
{
remark.RequirementId = newRequirement.Id;
AddRemark(remark);
}
}
catch (Exception ex)
{
log.Error(ex);
newRequirement.Id = -1;
}
}
public void UpdateRemark(ModelRemark remark)
{
UDataQuerySet set = new UDataQuerySet("SP_REQ_REMARK_UPDATE", CommandType.StoredProcedure);
set.AddParam("@ID", remark.ID);
set.AddParam("@REMARK", remark.Contents.Trim());
try
{
m_agent.ExecuteQuery(set);
}
catch (Exception ex)
{
log.Error(ex);
}
}
public List <ModelCategory> GetAllCategory(ModelProject selectedProject)
{
List <ModelCategory> aryResult = new List <ModelCategory>();
DataTable tbResult = null;
UDataQuerySet set = new UDataQuerySet("SP_CAT_SELECT", CommandType.StoredProcedure);
set.AddParam("@PRJID", selectedProject.ID);
try
{
tbResult = m_agent.GetDataTable(set);
foreach (DataRow row in tbResult.Rows)
{
ModelCategory category = new ModelCategory();
category.WriteData(row);
aryResult.Add(category);
}
}
catch (Exception ex)
{
log.Error(ex);
}
finally
{
if (tbResult != null)
{
tbResult.Dispose();
}
}
return(aryResult);
}
public void AddProject(string sProjectName, string sProjectDescription)
{
UDataQuerySet set = new UDataQuerySet("SP_PROJECT_CREATE", CommandType.StoredProcedure);
set.AddParam("@PRJNM", sProjectName);
set.AddParam("@PRJDESC", sProjectDescription);
try
{
m_agent.ExecuteQuery(set);
}
catch (Exception ex)
{
log.Error(ex);
}
}
internal void DelCategot(ModelCategory selectedCategory)
{
string sQuery = "DELETE TB_CATEGORY WHERE ID = @ID AND PRJID = @PRJID";
UDataQuerySet set = new UDataQuerySet(sQuery);
set.AddParam("@ID", selectedCategory.Id);
set.AddParam("@PRJID", selectedCategory.ProjectId);
try
{
m_agent.ExecuteQuery(set);
}
catch (Exception ex)
{
log.Error(ex);
}
}
public void UpdateRequirement(ModelReqmnt requirement)
{
UDataQuerySet set = new UDataQuerySet("SP_REQ_UPDATE", CommandType.StoredProcedure);
set.AddParam("@ID", requirement.Id);
set.AddParam("@CATID", requirement.CategoryId);
set.AddParam("@IDX", requirement.Index);
set.AddParam("@REQNMT", requirement.Requirement.Trim());
try
{
m_agent.ExecuteQuery(set);
}
catch (Exception ex)
{
log.Error(ex);
}
}
internal List <ModelReqmnt> GetAllRequirements(ModelProject selectedProject, List <ModelCategory> aryAllCategory)
{
List <ModelReqmnt> aryResult = new List <ModelReqmnt>();
DataTable tbResult = null;
DataTable tbResultRemark = null;
UDataQuerySet set = new UDataQuerySet("SP_REQ_SELECT", CommandType.StoredProcedure);
set.AddParam("@PRJID", selectedProject.ID);
UDataQuerySet setRemark = new UDataQuerySet("SP_REQ_REMARK_SELECT", CommandType.StoredProcedure);
setRemark.AddParam("@PRJID", selectedProject.ID);
try
{
tbResult = m_agent.GetDataTable(set);
tbResultRemark = m_agent.GetDataTable(setRemark);
foreach (DataRow row in tbResult.Rows)
{
ModelReqmnt req = new ModelReqmnt();
req.WriteData(row);
req.Category = aryAllCategory.Find(m => m.Id == req.CategoryIdFromDB);
aryResult.Add(req);
DataRow[] aryRemarks = tbResultRemark.Select(string.Format("REQID = {0}", req.Id));
foreach (DataRow rowRemark in aryRemarks)
{
ModelRemark remark = new ModelRemark();
remark.WriteData(rowRemark);
req.AllRemark.Add(remark);
}
}
}
catch (Exception ex)
{
log.Error(ex);
}
finally
{
if (tbResult != null)
{
tbResult.Dispose();
}
if (tbResultRemark != null)
{
tbResultRemark.Dispose();
}
}
return(aryResult);
}
public void AddRemark(ModelRemark remark)
{
if (remark.RequirementId > -1)
{
UDataQuerySet set = new UDataQuerySet("SP_REQ_REMARK_INSERT", CommandType.StoredProcedure);
set.AddParam("@PRJID", remark.ProjectId);
set.AddParam("@REQID", remark.RequirementId);
set.AddParam("@REMARK", remark.Contents.Trim());
try
{
int nRemarkID = m_agent.GetValue <int>(set);
remark.ID = nRemarkID;
}
catch (Exception ex)
{
log.Error(ex);
remark.ID = -1;
}
}
}
private string GetValue(string sKey)
{
string sQuery = @"SELECT VALUE FROM TB_SETTINGS WHERE KEY = @KEY";
UDataQuerySet set = new UDataQuerySet(sQuery);
set.AddParam("@KEY", sKey);
string sResult = string.Empty;
sResult = m_agent.GetValue <string>(set);
return(sResult);
}
internal void DelRequirement(ModelReqmnt selectedRequirement)
{
UDataQuerySet set = new UDataQuerySet("SP_REQ_DELETE", CommandType.StoredProcedure);
set.AddParam("@ID", selectedRequirement.Id);
try
{
m_agent.ExecuteQuery(set);
}
catch (Exception ex)
{
log.Error(ex);
}
}
internal void DeleteRemark(ModelRemark selectedRemark)
{
UDataQuerySet set = new UDataQuerySet("SP_REQ_REMARK_DELETE", CommandType.StoredProcedure);
set.AddParam("@ID", selectedRemark.ID);
try
{
m_agent.ExecuteQuery(set);
}
catch (Exception ex)
{
log.Error(ex);
}
}
internal void UpdateCategory(ModelCategory selectedCategory)
{
string sQuery = "UPDATE TB_CATEGORY SET PID = @PID,CODE = @CODE, DISPNM = @DISPNM,DESCRPT = @DESCRPT, UPDATED = GETDATE() WHERE ID = @ID AND PRJID = @PRJID";
UDataQuerySet set = new UDataQuerySet(sQuery);
set.AddParam("@ID", selectedCategory.Id);
set.AddParam("@PID", selectedCategory.ParentId);
set.AddParam("@PRJID", selectedCategory.ProjectId);
set.AddParam("@CODE", selectedCategory.Code.Trim());
set.AddParam("@DISPNM", selectedCategory.DisplayName.Trim());
set.AddParam("@DESCRPT", selectedCategory.Description.Trim());
try
{
m_agent.ExecuteQuery(set);
}
catch (Exception ex)
{
log.Error(ex);
}
}
