public Boolean isValidAdvice(Advice adviceObj, out string errorMessage)
{
errorMessage = "";
if (adviceObj.AdviceTitle == "")
{
errorMessage += "Please enter advice title" + "<br/>";
}
if (adviceObj.AdviceDescription == "")
{
errorMessage += "Please enter advice description";
}
if (errorMessage.Equals(""))
return true;
else
return false;
}
public DataSet SelectAdvice(Advice adviceObj, string connString)
{
DataSet dsAdvice = new DataSet();
SqlParameter pAdviceId = new SqlParameter("@AdviceId", SqlDbType.Int, 0);
pAdviceId.Value = adviceObj.AdviceId;
SqlCommand cmdSQL = new SqlCommand();
cmdSQL.CommandType = CommandType.StoredProcedure;
cmdSQL.CommandText = "usp_GetAdvice";
cmdSQL.Parameters.Add(pAdviceId);
DataLayer datalayerObj = new DataLayer();
dsAdvice = datalayerObj.GetQuery(cmdSQL, connString);
return dsAdvice;
}
public Advice InsertAdvice(Advice adviceObj, string connString)
{
//declaration of parameters
string errorMessage;
if (isValidAdvice(adviceObj, out errorMessage))
{
SqlParameter pQueryId = new SqlParameter("@QueryId", SqlDbType.Int);
SqlParameter pUsername = new SqlParameter("@Username", SqlDbType.NVarChar, 50);
SqlParameter pLikes = new SqlParameter("@Likes", SqlDbType.Int);
SqlParameter pDislikes = new SqlParameter("@Dislikes", SqlDbType.Int);
SqlParameter pAdviceDescription = new SqlParameter("@AdviceDescription", SqlDbType.NVarChar, 400);
SqlParameter pAdviceTitle = new SqlParameter("@AdviceTitle", SqlDbType.NVarChar, 50);
SqlParameter pAdviceDateTime = new SqlParameter("@AdviceDateTime", SqlDbType.DateTime);
//initialization
pQueryId.Value = adviceObj.QueryId;
pUsername.Value = adviceObj.Username;
pLikes.Value = adviceObj.Likes;
pDislikes.Value = adviceObj.Dislikes;
pAdviceDescription.Value = adviceObj.AdviceDescription;
pAdviceTitle.Value = adviceObj.AdviceTitle;
pAdviceDateTime.Value = adviceObj.AdviceDateTime;
//adding params to query
SqlCommand cmdSql = new SqlCommand();
cmdSql.CommandType = CommandType.StoredProcedure;
cmdSql.CommandText = "usp_InsertAdvice";
cmdSql.Parameters.Add(pQueryId);
cmdSql.Parameters.Add(pUsername);
cmdSql.Parameters.Add(pLikes);
cmdSql.Parameters.Add(pDislikes);
cmdSql.Parameters.Add(pAdviceDescription);
cmdSql.Parameters.Add(pAdviceTitle);
cmdSql.Parameters.Add(pAdviceDateTime);
//storage in data layer
DataLayer datalayerObj = new DataLayer();
cmdSql = datalayerObj.Insert(cmdSql, connString);
//updating query status 'IsAttendedTo'
SqlParameter pIsAttendedTo = new SqlParameter("@IsAttendedTo", SqlDbType.Bit);
SqlParameter pStatus = new SqlParameter("@Status", SqlDbType.VarChar, 30);
SqlParameter pQuery1 = new SqlParameter("@QueryId", SqlDbType.Int);
pQuery1.Value = pQueryId.Value;
pIsAttendedTo.Value = true;
pStatus.Value = "Answered";
//first command
SqlCommand cmdSql1 = new SqlCommand();
cmdSql1.CommandType = CommandType.StoredProcedure;
cmdSql1.CommandText = "usp_UpdateQueryAttended";
cmdSql1.Parameters.Add(pQuery1);
cmdSql1.Parameters.Add(pIsAttendedTo);
datalayerObj = new DataLayer();
cmdSql1 = datalayerObj.Update(cmdSql1, connString);
//second command
SqlCommand cmdSql2 = new SqlCommand();
cmdSql2.CommandType = CommandType.StoredProcedure;
cmdSql2.CommandText = "usp_UpdateQueryStatus";
cmdSql2.Parameters.Add(pStatus);
datalayerObj = new DataLayer();
cmdSql2 = datalayerObj.Update(cmdSql2, connString);
}
else
{
throw new Exception(errorMessage);
}
return adviceObj;
}