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 List <ModelProject> GetAllProject()
{
List <ModelProject> aryResult = new List <ModelProject>();
UDataQuerySet set = new UDataQuerySet("SELECT ID ,PRJNM,DESCRPT ,ENABLED FROM TB_PROJECTS WHERE ENABLED = 'Y'");
DataTable tbResult = null;
try
{
tbResult = m_agent.GetDataTable(set);
foreach (DataRow row in tbResult.Rows)
{
ModelProject project = new ModelProject();
project.WriteData(row);
aryResult.Add(project);
}
}
catch (Exception ex)
{
}
finally
{
if (tbResult != null)
{
tbResult.Dispose();
}
}
return(aryResult);
}
public int ExecuteQuery(string sQuery, CommandType commandType)
{
UDataQuerySet set = new UDataQuerySet();
set.Query = sQuery;
set.CmdType = commandType;
return(ExecuteQuery(set));
}
/// <summary>
/// 문자열을 이용하여 트렌젝트 실행을 하고 영향 받는 Row수를 반환 한다.
/// </summary>
/// <param name="query">질의 문자열</param>
/// <returns>영향 받은 Row수</returns>
public int ExecuteQuery(string query, List <SqlParameter> paramters)
{
List <UDataQuerySet> aryQuerySet = new List <UDataQuerySet>();
UDataQuerySet querySet = new UDataQuerySet(query);
querySet.Parameters.AddRange(paramters);
aryQuerySet.Add(querySet);
return(ExecuteQuery(aryQuerySet));
}
internal void InitValidation()
{
string sQuery = @"CREATE TABLE IF NOT EXISTS TB_SETTINGS (
id integer primary KEY,
name varchar(30),
value varchar(100)
)";
UDataQuerySet set = new UDataQuerySet(sQuery);
m_agent.ExecuteQuery(set);
}
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 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 T GetValue <T>(UDataQuerySet set)
{
T value = default(T);
SqlConnection sqlConnection = new SqlConnection(this.ConnectionString);
SqlDataAdapter sqlAdapter = new SqlDataAdapter();
SqlCommand sqlCommand = null;
try
{
sqlConnection.Open();
sqlCommand = new SqlCommand(set.Query, sqlConnection);
sqlCommand.CommandType = set.CmdType;
sqlCommand.CommandTimeout = set.Timeout;
if (set.Parameters.Count > 0)
{
sqlCommand.Parameters.AddRange(set.Parameters.ToArray());
}
object objResult = sqlCommand.ExecuteScalar();
if (objResult != null && DBNull.Value.Equals(objResult) == false)
{
objResult = Convert.ChangeType(objResult, typeof(T));
value = (T)objResult;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlCommand != null)
{
sqlCommand.Dispose();
}
if (sqlAdapter != null)
{
sqlAdapter.Dispose();
}
if (sqlConnection != null)
{
if (sqlConnection.State == ConnectionState.Open)
{
sqlConnection.Close();
}
sqlConnection.Dispose();
}
}
return(value);
}
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);
}
}
public DataTable GetDataTable(UDataQuerySet set)
{
DataTable dtResult = new DataTable();
SqlConnection sqlConnection = new SqlConnection(this.ConnectionString);
SqlDataAdapter sqlAdapter = new SqlDataAdapter();
SqlCommand sqlCommand = null;
try
{
sqlConnection.Open();
sqlCommand = new SqlCommand(set.Query, sqlConnection);
sqlCommand.CommandTimeout = set.Timeout;
sqlCommand.CommandType = set.CmdType;
if (set.Parameters.Count > 0)
{
sqlCommand.Parameters.AddRange(set.Parameters.ToArray());
}
sqlAdapter.SelectCommand = sqlCommand;
sqlAdapter.Fill(dtResult);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlCommand != null)
{
sqlCommand.Dispose();
}
if (sqlAdapter != null)
{
sqlAdapter.Dispose();
}
if (sqlConnection != null)
{
if (sqlConnection.State == ConnectionState.Open)
{
sqlConnection.Close();
}
sqlConnection.Dispose();
}
}
return(dtResult);
}
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 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);
}
}
public object GetValue(UDataQuerySet set)
{
object value = null;
SqlConnection sqlConnection = new SqlConnection(this.ConnectionString);
SqlDataAdapter sqlAdapter = new SqlDataAdapter();
SqlCommand sqlCommand = null;
try
{
sqlConnection.Open();
sqlCommand = new SqlCommand(set.Query, sqlConnection);
sqlCommand.CommandType = set.CmdType;
sqlCommand.CommandTimeout = set.Timeout;
if (set.Parameters.Count > 0)
{
sqlCommand.Parameters.AddRange(set.Parameters.ToArray());
}
value = sqlCommand.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlCommand != null)
{
sqlCommand.Dispose();
}
if (sqlAdapter != null)
{
sqlAdapter.Dispose();
}
if (sqlConnection != null)
{
if (sqlConnection.State == ConnectionState.Open)
{
sqlConnection.Close();
}
sqlConnection.Dispose();
}
}
return(value);
}
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 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);
}
}
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;
}
}
}
internal T GetValue <T>(string query)
{
UDataQuerySet set = new UDataQuerySet(query);
return(GetValue <T>(set));
}
public DataSet GetDataSet(string query)
{
UDataQuerySet set = new UDataQuerySet(query);
return(GetDataSet(set));
}
internal object GetValue(string query)
{
UDataQuerySet set = new UDataQuerySet(query);
return(GetValue(set));
}