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)); } }