// GET api/executesql/5 public string Get(string id) { string sql = id; DatabaseConnection dc = new DatabaseConnection(); if (dc.InsertUpdateDelete(sql)) return "Success"; else return "Fail"; }
private static string CheckStatus(int responseCode, string projectName, string liveUrl, string projectId) { try { dc = new DatabaseConnection(); string checkIssueIdQuery = "Select Isssue_id , Enabled, Project_id from IssueStatusTracking where Project_id='" + projectId + "'"; DataTable dt = new DataTable(); dt = dc.GetDataTable(checkIssueIdQuery); int issue = 0; bool flag = false; if (dt != null) { if (dt.Rows.Count > 0) { int.TryParse(dt.Rows[0]["Isssue_id"].ToString(), out issue);//IssueTrack[0] = issue id bool.TryParse(dt.Rows[0]["Enabled"].ToString(), out flag); } } if (responseCode != 200)//check ping status { if (issue <= 0) { //insert defect //post call in redmine string issueId = ""; issueId = CreateIssue(projectName, liveUrl, 1); int createdIssueId = 0; int.TryParse(issueId, out createdIssueId); if (createdIssueId > 0) { string insertQuery = "Insert into IssueStatusTracking (Isssue_id, Enabled, Project_id) values ('" + issueId + "','" + true + "','" + projectId + "')"; dc.InsertUpdateDelete(insertQuery); string watchersConfig = System.Configuration.ConfigurationManager.AppSettings["watchers"]; string[] watcherArray = watchersConfig.Split(','); PostData.AddWatchers(issueId, watcherArray); string[] data = { "Website ping failed: " + DateTime.Now.ToString("dd-MM-yyyy hh:mm:ss") + " Url: " + liveUrl }; string[] element = { "notes" }; PostData.UpdateIssue(createdIssueId.ToString(), data, element); } } else if (issue > 0 && flag == true) { //update notes in redmine for previous issue string[] data = { "Website ping continues to fail: " + DateTime.Now.ToString("dd-MM-yyyy hh:mm:ss") + " Url: " + liveUrl }; string[] element = { "notes" }; PostData.UpdateIssue(issue.ToString(), data, element); } else { string updateQuery = "Update IssueStatusTracking set Enabled='True' where Project_id='" + projectId + "'"; dc.InsertUpdateDelete(updateQuery); string[] data = { "Website ping continues to fail: " + DateTime.Now.ToString("dd-MM-yyyy hh:mm:ss") + " Url: " + liveUrl }; string[] element = { "notes" }; PostData.UpdateIssue(issue.ToString(), data, element); } } else if (responseCode == 200 && flag == true) { //close issue in redmine string[] data = { "5" };//status id for closed issue string[] element = { "status_id" }; PostData.UpdateIssue(issue.ToString(), data, element); //update issue in database string updateQuery = "Update IssueStatusTracking set Enabled='False' where Project_id='" + projectId + "'"; dc.InsertUpdateDelete(updateQuery); } else if (responseCode == 200 && issue > 0 && flag == false) { //delete issueId in sql string deleteIssueQuery = "Delete from IssueStatusTracking where Isssue_id='" + issue + "' and Enabled='false'"; dc.InsertUpdateDelete(deleteIssueQuery); } return "Completed"; } catch (Exception ex) { ErrorLogging.WriteErrorLog("Exception CheckStatus: " + ex.ToString()); return "Failed"; } }
public HttpResponseMessage UpdateProjectDynamic(ProjectVM data) { string query = @"UPDATE [dbo].[Project_Main] SET "; ; List<SqlParameter> list = new List<SqlParameter>(); SqlParameter param; if (data.id != 0) { //return ;//cant update project without id query += @" [Project_Id] = @Project_Id "; if (data.name != null) { query += @",[Name] = @Name"; param = new SqlParameter("@Name", SqlDbType.VarChar) { Value = data.name }; list.Add(param); } if (data.exit_date != null) { query += @",[Exit_Date] = @Exit_Date"; param = new SqlParameter("@Exit_Date", SqlDbType.Date) { Value = Convert.ToDateTime(data.exit_date).ToString("yyyy-MM-dd") }; list.Add(param); } if (data.project_cost != 0.0) { query += @",[Project_Cost] = @Project_Cost"; param = new SqlParameter("@Project_Cost", SqlDbType.Decimal) { Value = data.project_cost }; list.Add(param); } if (data.producer_name != null) { query += @",[Producer_Name] = @Producer_Name"; param = new SqlParameter("@Producer_Name", SqlDbType.VarChar) { Value = data.producer_name }; list.Add(param); } if (data.market != null) { query += @",[Market] = @Market"; param = new SqlParameter("@Market", SqlDbType.VarChar) { Value = data.market }; list.Add(param); } if (data.hosting_platform != null) { query += @",[Hosting_Platform] = @Hosting_Platform"; param = new SqlParameter("@Hosting_Platform", SqlDbType.VarChar) { Value = data.hosting_platform }; list.Add(param); } if (data.status != null) { query += @",[Phase_Status] = @Phase_Status"; param = new SqlParameter("@Phase_Status", SqlDbType.VarChar) { Value = data.status }; list.Add(param); } if (data.progress != 0.0) { query += @",[Progress] = @Progress"; param = new SqlParameter("@Progress", SqlDbType.Decimal) { Value = data.progress }; list.Add(param); } if (data.live_url != null) { query += @",[Live_Url] = @Live_Url"; param = new SqlParameter("@Live_Url", SqlDbType.VarChar) { Value = data.live_url }; list.Add(param); } if (data.development_start_date != null) { query += @",[Development_Start_Date] = @Development_Start_Date"; param = new SqlParameter("@Development_Start_Date", SqlDbType.Date) { Value = Convert.ToDateTime(data.development_start_date).ToString("yyyy-MM-dd") }; list.Add(param); } if (data.launch_date != null) { query += @",[Launch_Date] = @Launch_Date"; param = new SqlParameter("@Launch_Date", SqlDbType.Date) { Value = Convert.ToDateTime(data.launch_date).ToString("yyyy-MM-dd") }; list.Add(param); } if (data.due_date != null) { query += @",[due_date] = @due_date"; param = new SqlParameter("@due_date", SqlDbType.Date) { Value = Convert.ToDateTime(data.due_date).ToString("yyyy-MM-dd") }; list.Add(param); } if (data.pending_with != null) { query += @",[pending_with] = @pending_with"; param = new SqlParameter("@pending_with", SqlDbType.VarChar) { Value = data.pending_with }; list.Add(param); } if (data.image_url != null) { query += @",[image_url] = @image_url"; param = new SqlParameter("@image_url", SqlDbType.VarChar) { Value = data.image_url }; list.Add(param); } query += @" WHERE [Project_Id] = @Project_Id"; param = new SqlParameter("@Project_Id", SqlDbType.BigInt) { Value = data.id }; list.Add(param); } SqlParameter[] sqlParam = list.ToArray(); DatabaseConnection dc = new DatabaseConnection(); dc.InsertUpdateDeleteDynamic(query, sqlParam); //int id = 0; //string dateFormat = "yyyy-MM-dd"; //string exit_date = string.Empty; //double project_cost = 0.0; //string producer_name = string.Empty; //string market = string.Empty; //string hosting_platform = string.Empty; //string status = string.Empty; //double progress = 0.0; //string live_url = string.Empty; //string development_start_date = string.Empty; //string launch_date = string.Empty; //string post_result = string.Empty; //string due_date = string.Empty; //string image_url = string.Empty; //double estimateTime = 0.0; //foreach (PropertyInfo property in properties) //{ // object propertyValue = property.GetValue(data, null); // Type propType = property.PropertyType; // string propName = property.Name; // switch (propName) // { // case "id": // break; // } // //if (propType == typeof(int)) // //{ // // Int64 interger = Convert.ToInt64(propertyValue); // // if (interger != 0) // // listOfProperties += property.Name.ToString() + ","; // //} // //if (propType == typeof(double)) // //{ // // double doubleVal = Convert.ToDouble(propertyValue); // // if (doubleVal != Double.MinValue) // // listOfProperties += property.Name.ToString() + ","; // //} // //if (propType == typeof(string)) // //{ // // string stringVal = Convert.ToString(propertyValue); // // if (!string.IsNullOrEmpty(stringVal)) // // listOfProperties += property.Name.ToString() + ","; // //} //} return Request.CreateResponse(HttpStatusCode.OK, "");//"Authentication error!! Please try again"); }
/// <summary> /// <para>Update Campaign table </para> /// <para>returns "Project VM Null" string if parameter is null </para> /// <para>returns "Project ID Invalid" string if id is null </para> /// <para>returns "Success" or "Fail" string on update </para> /// <para>returns string which contains Exception: for uncaught exception</para> /// </summary> /// <param name="proVM">PojectVM class</param> /// <returns>string value regarding status of update</returns> public string UpdateCampaignDynamicParam(ProjectVM data) { try { string query = @"UPDATE [dbo].[Project_Main] SET "; ; List<SqlParameter> list = new List<SqlParameter>(); SqlParameter param; if (data.id != 0) { //return ;//cant update project without id query += @" [Project_Id] = @Project_Id "; if (!string.IsNullOrEmpty(data.name)) { query += @",[Name] = @Name"; param = new SqlParameter("@Name", SqlDbType.VarChar) { Value = data.name }; list.Add(param); } if (!string.IsNullOrEmpty(data.exit_date) && Convert.ToDateTime(data.exit_date) != DateTime.MinValue) { query += @",[Exit_Date] = @Exit_Date"; param = new SqlParameter("@Exit_Date", SqlDbType.Date) { Value = Convert.ToDateTime(data.exit_date).ToString("yyyy-MM-dd") }; list.Add(param); } if (data.project_cost != 0.0) { query += @",[Project_Cost] = @Project_Cost"; param = new SqlParameter("@Project_Cost", SqlDbType.Decimal) { Value = data.project_cost }; list.Add(param); } if (!string.IsNullOrEmpty(data.producer_name)) { query += @",[Producer_Name] = @Producer_Name"; param = new SqlParameter("@Producer_Name", SqlDbType.VarChar) { Value = data.producer_name }; list.Add(param); } if (!string.IsNullOrEmpty(data.market)) { query += @",[Market] = @Market"; param = new SqlParameter("@Market", SqlDbType.VarChar) { Value = data.market }; list.Add(param); } if (!string.IsNullOrEmpty(data.hosting_platform)) { query += @",[Hosting_Platform] = @Hosting_Platform"; param = new SqlParameter("@Hosting_Platform", SqlDbType.VarChar) { Value = data.hosting_platform }; list.Add(param); } if (!string.IsNullOrEmpty(data.status)) { query += @",[Phase_Status] = @Phase_Status"; param = new SqlParameter("@Phase_Status", SqlDbType.VarChar) { Value = data.status }; list.Add(param); } if (data.progress != 0.0) { query += @",[Progress] = @Progress"; param = new SqlParameter("@Progress", SqlDbType.Decimal) { Value = data.progress }; list.Add(param); } if (data.EstimatedTime != 0.0) { query += @",[EstimatedTime] = @EstimatedTime"; param = new SqlParameter("@EstimatedTime", SqlDbType.Decimal) { Value = data.EstimatedTime }; list.Add(param); } if (data.TotalTimeSpent != 0.0) { query += @",[TotalTimeSpent] = @TotalTimeSpent"; param = new SqlParameter("@TotalTimeSpent", SqlDbType.Decimal) { Value = data.TotalTimeSpent }; list.Add(param); } if (!string.IsNullOrEmpty(data.live_url)) { query += @",[Live_Url] = @Live_Url"; param = new SqlParameter("@Live_Url", SqlDbType.VarChar) { Value = data.live_url }; list.Add(param); } if (data.development_start_date != null && Convert.ToDateTime(data.development_start_date) != DateTime.MinValue) { query += @",[Development_Start_Date] = @Development_Start_Date"; param = new SqlParameter("@Development_Start_Date", SqlDbType.Date) { Value = Convert.ToDateTime(data.development_start_date).ToString("yyyy-MM-dd") }; list.Add(param); } if (data.launch_date != null && Convert.ToDateTime(data.launch_date) != DateTime.MinValue) { query += @",[Launch_Date] = @Launch_Date"; param = new SqlParameter("@Launch_Date", SqlDbType.Date) { Value = Convert.ToDateTime(data.launch_date).ToString("yyyy-MM-dd") }; list.Add(param); } if (data.due_date != null && Convert.ToDateTime(data.due_date) != DateTime.MinValue) { query += @",[due_date] = @due_date"; param = new SqlParameter("@due_date", SqlDbType.Date) { Value = Convert.ToDateTime(data.due_date).ToString("yyyy-MM-dd") }; list.Add(param); } if (!string.IsNullOrEmpty(data.pending_with)) { query += @",[pending_with] = @pending_with"; param = new SqlParameter("@pending_with", SqlDbType.VarChar) { Value = data.pending_with }; list.Add(param); } if (!string.IsNullOrEmpty(data.image_url)) { query += @",[image_url] = @image_url"; param = new SqlParameter("@image_url", SqlDbType.VarChar) { Value = data.image_url }; list.Add(param); } if (!string.IsNullOrEmpty(data.qa_url)) { query += @",[qa_url] = @qa_url"; param = new SqlParameter("@qa_url", SqlDbType.VarChar) { Value = data.qa_url }; list.Add(param); } query += @" WHERE [Project_Id] = @Project_Id"; param = new SqlParameter("@Project_Id", SqlDbType.BigInt) { Value = data.id }; list.Add(param); } SqlParameter[] sqlParam = list.ToArray(); DatabaseConnection dc = new DatabaseConnection(); if (dc.InsertUpdateDeleteDynamic(query, sqlParam)) return "Success"; else return "Failed"; } catch (Exception ex) { Logger.Error("SqlQueries.Project_Main_Tbl.UpdateCampaignDynamicParam : " + ex.ToString()); return "Exception: " + ex.ToString(); } }