public void UpdateQuery(Query queryObj, string connString)
{
SqlParameter pQueryId = new SqlParameter("@QueryId", SqlDbType.Int);
SqlParameter pUsername = new SqlParameter("@Username", SqlDbType.NVarChar, 50);
SqlParameter pQueryAreaOfInterest = new SqlParameter("@QueryAreaOfInterest", SqlDbType.NVarChar, 100);
SqlParameter pSymptoms = new SqlParameter("@Symptoms", SqlDbType.NVarChar, 200);
SqlParameter pMedicalHistory = new SqlParameter("@MedicalHistory", SqlDbType.NVarChar, 500);
SqlParameter pStatus = new SqlParameter("@Status", SqlDbType.NVarChar, 20);
SqlParameter pIsAttendedTo = new SqlParameter("@IsAttendedTo", SqlDbType.Bit);
SqlParameter pPostedDateTime = new SqlParameter("@PostedDateTime", SqlDbType.DateTime);
pQueryId.Direction = ParameterDirection.Output;
pQueryAreaOfInterest.Value = queryObj.QueryAreaOfInterest;
pUsername.Value = queryObj.Username;
pSymptoms.Value = queryObj.Symptoms;
pMedicalHistory.Value = queryObj.MedicalHistory;
pStatus.Value = queryObj.Status;
//the boolean value
pIsAttendedTo.Value = queryObj.IsAttendedTo;
//the date time value
pPostedDateTime.Value = queryObj.PostedDatetime;
//adding params to query
SqlCommand cmdSql = new SqlCommand();
cmdSql.CommandType = CommandType.StoredProcedure;
cmdSql.CommandText = "usp_UpdateQuery";
cmdSql.Parameters.Add(pQueryAreaOfInterest);
cmdSql.Parameters.Add(pUsername);
cmdSql.Parameters.Add(pSymptoms);
cmdSql.Parameters.Add(pMedicalHistory);
cmdSql.Parameters.Add(pStatus);
cmdSql.Parameters.Add(pIsAttendedTo);
cmdSql.Parameters.Add(pPostedDateTime);
cmdSql.Parameters.Add(pQueryId);
DataLayer datalayerObj = new DataLayer();
datalayerObj.Update(cmdSql, connString);
queryObj.QueryId = int.Parse(pQueryId.Value.ToString());
}
public DataSet SelectTopTenAdvice(Query queryObj, string connString)
{
DataSet dsTopAdvice = new DataSet();
SqlParameter pAreaOfInterest = new SqlParameter("@QueryAreaOfInterest", SqlDbType.NVarChar, 100);
pAreaOfInterest.Value = queryObj.QueryAreaOfInterest;
SqlCommand cmdSQL = new SqlCommand();
cmdSQL.CommandType = CommandType.StoredProcedure;
cmdSQL.CommandText = "usp_GetTopTenAdvice";
cmdSQL.Parameters.Add(pAreaOfInterest);
DataLayer dataLayerObj = new DataLayer();
dsTopAdvice = dataLayerObj.GetQuery(cmdSQL, connString);
return dsTopAdvice;
}
public DataSet SelectQuery(Query queryObj, string connString)
{
DataSet dsQuery = new DataSet();
SqlParameter pQueryId = new SqlParameter("@QueryId", SqlDbType.Int, 0);
pQueryId.Value = queryObj.QueryId;
SqlCommand cmdSQL = new SqlCommand();
cmdSQL.CommandType = CommandType.StoredProcedure;
cmdSQL.CommandText = "usp_GetQueryDetails";
cmdSQL.Parameters.Add(pQueryId);
DataLayer datalayerObj = new DataLayer();
dsQuery = datalayerObj.GetQuery(cmdSQL, connString);
return dsQuery;
}
public Boolean isValidQuery(Query queryObj, out string errorMessage)
{
if (queryObj.MedicalHistory == "")
{
errorMessage = "Please enter medical history";
return false;
}
errorMessage = "";
return true;
}
//insert Query
public Query InsertQuery(Query queryObj, string connString)
{
string errorMessage;
//declaration of parameters
if (isValidQuery(queryObj, out errorMessage))
{
SqlParameter pQueryId = new SqlParameter("@QueryId", SqlDbType.Int);
SqlParameter pQueryAreaOfInterest = new SqlParameter("@QueryAreaOfInterest", SqlDbType.NVarChar, 100);
SqlParameter pUsername = new SqlParameter("@Username", SqlDbType.NVarChar, 50);
SqlParameter pSymptoms = new SqlParameter("@Symptoms", SqlDbType.NVarChar, 50);
SqlParameter pMedicalHistory = new SqlParameter("@MedicalHistory", SqlDbType.NVarChar, 500);
SqlParameter pStatus = new SqlParameter("@Status", SqlDbType.NVarChar, 20);
SqlParameter pIsAttendedTo = new SqlParameter("@IsAttendedTo", SqlDbType.Bit);
SqlParameter pPostedDateTime = new SqlParameter("@PostedDateTime", SqlDbType.DateTime);
//initialization
//strings
pQueryId.Direction = ParameterDirection.Output;
pQueryAreaOfInterest.Value = queryObj.QueryAreaOfInterest;
pUsername.Value = queryObj.Username;
pSymptoms.Value = queryObj.Symptoms;
pMedicalHistory.Value = queryObj.MedicalHistory;
pStatus.Value = queryObj.Status;
//the boolean value
pIsAttendedTo.Value = queryObj.IsAttendedTo;
//the date time value
pPostedDateTime.Value = queryObj.PostedDatetime;
//adding params to query
SqlCommand cmdSql = new SqlCommand();
cmdSql.CommandType = CommandType.StoredProcedure;
cmdSql.CommandText = "usp_InsertQuery";
cmdSql.Parameters.Add(pQueryAreaOfInterest);
cmdSql.Parameters.Add(pUsername);
cmdSql.Parameters.Add(pSymptoms);
cmdSql.Parameters.Add(pMedicalHistory);
cmdSql.Parameters.Add(pStatus);
cmdSql.Parameters.Add(pIsAttendedTo);
cmdSql.Parameters.Add(pPostedDateTime);
cmdSql.Parameters.Add(pQueryId);
//storage in data layer
DataLayer datalayerObj = new DataLayer();
datalayerObj.Insert(cmdSql, connString);
queryObj.QueryId = int.Parse(pQueryId.Value.ToString());
}
else
{
throw new Exception(errorMessage);
}
//queryObj.QueryId = int.Parse(cmdSql.Parameters["@QueryId"].Value.ToString());
return queryObj;
}
public DataSet GetQueryWithFurtherUserDetails(Query queryObj, string connString)
{
DataSet dsQuery = new DataSet();
SqlParameter pQueryUsername = new SqlParameter("@Username", SqlDbType.NVarChar, 50);
SqlParameter pQueryStatus = new SqlParameter("@Status", SqlDbType.NVarChar, 20);
pQueryUsername.Value = queryObj.Username;
pQueryStatus.Value = "Needs More Info";
SqlCommand cmdSQL = new SqlCommand();
cmdSQL.CommandType = CommandType.StoredProcedure;
cmdSQL.CommandText = "usp_GetQueryWithFurtherUserDetail";
cmdSQL.Parameters.Add(pQueryUsername);
cmdSQL.Parameters.Add(pQueryStatus);
DataLayer datalayerObj = new DataLayer();
dsQuery = datalayerObj.GetQuery(cmdSQL, connString);
return dsQuery;
}