public JsonResult getActivities(string username) //Function that returns all activities
{
string connString = connectionString;
SqlConnection conn = new SqlConnection(connString);
conn.Open();
string sqlQuery = "Select ID,ActivityName, UserName, Priority, Labels from dbo.ActivityTable where UserName='******'";
SqlCommand command = new SqlCommand(sqlQuery, conn);
SqlDataReader reader = command.ExecuteReader();
List <ActivityLoggingProject.Models.Activity> activities = new List <ActivityLoggingProject.Models.Activity>();
while (reader.Read() == true)
{
ActivityLoggingProject.Models.Activity dto = new ActivityLoggingProject.Models.Activity();
dto.ID = reader.GetInt32(reader.GetOrdinal("ID"));
dto.usrname = reader.GetString(reader.GetOrdinal("username"));
dto.activityname = reader.GetString(reader.GetOrdinal("activityname"));
dto.priority = reader.GetString(reader.GetOrdinal("priority"));
dto.labels = reader.GetString(reader.GetOrdinal("labels"));
activities.Add(dto);
}
return(Json(activities, JsonRequestBehavior.AllowGet));
}
public JsonResult getActivity(int ID) //Function to return a single activity
{
String connString = connectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
//Step 2: Open connection.
conn.Open();
//Step 3" Build the query.
String sqlQuery = "Select ActivityName, UserName from dbo.ActivityTable where ID ='" + ID;
//Step 4: Build command object.
SqlCommand command = new SqlCommand(sqlQuery, conn);
SqlDataReader reader = command.ExecuteReader();
ActivityLoggingProject.Models.Activity activ = new ActivityLoggingProject.Models.Activity();
if (reader.Read())
{
activ.activityname = (string)reader.GetValue(0);
activ.usrname = (string)reader.GetValue(0);
}
return(Json(activ, JsonRequestBehavior.AllowGet));
}
}
public JsonResult AddActivity(string username, string activityname, string priority, string labels = "") //Function to add an activity
{
String connString = connectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
//Step 2: Open connection.
conn.Open();
//Step 3" Build the query.
String sqlQuery = String.Format(@"INSERT INTO dbo.ActivityTable(ActivityName,UserName, Priority, Labels) VALUES('{0}','{1}', '{2}', '{3}')", activityname, username, priority, labels);
//Step 4: Build command object.
SqlCommand command = new SqlCommand(sqlQuery, conn);
int recAff = command.ExecuteNonQuery();
//Step 5: Execute non-query.
SqlConnection conn2 = new SqlConnection(connString);
conn2.Open();
ActivityLoggingProject.Models.Activity dto = new ActivityLoggingProject.Models.Activity();
sqlQuery = "Select ID, ActivityName, UserName, Priority, Labels from dbo.ActivityTable where UserName ='******' and ActivityName='" + activityname + "'";
SqlCommand command2 = new SqlCommand(sqlQuery, conn2);
SqlDataReader reader = command2.ExecuteReader();
if (reader.Read())
{
dto.ID = reader.GetInt32(reader.GetOrdinal("ID"));
dto.usrname = reader.GetString(reader.GetOrdinal("username"));
dto.activityname = reader.GetString(reader.GetOrdinal("activityname"));
dto.priority = reader.GetString(reader.GetOrdinal("priority"));
dto.labels = reader.GetString(reader.GetOrdinal("labels"));
}
else
{
}
return(Json(dto, JsonRequestBehavior.AllowGet));
}
}