public static string DataTableToJson(string myobId) { string command = "SELECT * FROM Property WHERE pro_myobid = '" + myobId + "'"; SQLData dataTable = new SQLData(); DataTable table = dataTable.GetSQLData(command); string JSONString = string.Empty; JSONString = JsonConvert.SerializeObject(table); return(JSONString); }
protected void btnSubmit_Click(object sender, EventArgs e) { try { string myobId = txtMyobId.Text; SQLData sqlData = new SQLData(); if (sqlData.CheckPropertyExists("SELECT * FROM Property WHERE pro_myobid = '" + myobId + "'")) { DataSet dataSet = new DataSet(); DataTable dataTable = new DataTable(); dataTable = sqlData.GetSQLData("SELECT * FROM Property WHERE pro_myobid = '" + myobId + "'"); DataTable fbuDataTable = new DataTable(); fbuDataTable = sqlData.GetSQLData("SELECT * FROM FinanceBrokerUpdate"); dataSet.Tables.Add(dataTable); dataSet.Tables.Add(fbuDataTable); SqlDataAdapter sqlDA = new SqlDataAdapter("SELECT * FROM Property", sqlData.ConnectionString); SqlCommandBuilder sqlCB = new SqlCommandBuilder(sqlDA); SqlDataAdapter sql_FBU_DA = new SqlDataAdapter("SELECT * FROM FinanceBrokerUpdate", sqlData.ConnectionString); SqlCommandBuilder sql_FBU_CB = new SqlCommandBuilder(sql_FBU_DA); var propertyRecord = dataSet.Tables[0].Rows[0]; var existingLenRecAccByClient = dataSet.Tables[0].Rows[0]["pro_lendingrecacceptbyclient"].ToString(); var existingFinanceLodged = dataSet.Tables[0].Rows[0]["pro_financelodged"].ToString(); var existingFinanceAssessed = dataSet.Tables[0].Rows[0]["pro_financeassessed"].ToString(); var existingConditionalApproval = dataSet.Tables[0].Rows[0]["pro_conditionalapproval"].ToString(); var existingValuationOrdered = dataSet.Tables[0].Rows[0]["pro_valuationordered"].ToString(); var existingValuationReturned = dataSet.Tables[0].Rows[0]["pro_valuationreturned"].ToString(); //var existingFormalApproval = dataSet.Tables[0].Rows[0]["pro_financeapproveddate"].ToString(); var existingFinanceDeclined = dataSet.Tables[0].Rows[0]["pro_financedeclined"].ToString(); var existingMortgageDocsReturned = dataSet.Tables[0].Rows[0]["pro_mortgagedocsreturned"].ToString(); //var existingSettlementDate = dataSet.Tables[0].Rows[0]["pro_settlementdate"].ToString(); var existingFinanceNotes = dataSet.Tables[0].Rows[0]["pro_financenotes"].ToString(); propertyRecord.BeginEdit(); propertyRecord["pro_lendingrecacceptbyclient"] = (txtLendRecAccByClient.Text == string.Empty) ? (String.IsNullOrEmpty(existingLenRecAccByClient) ? (object)DBNull.Value : (object)DateTime.Parse(existingLenRecAccByClient)) : (object)DateTime.Parse(txtLendRecAccByClient.Text); propertyRecord["pro_financelodged"] = (txtFinanceLodged.Text == string.Empty) ? (String.IsNullOrEmpty(existingFinanceLodged) ? (object)DBNull.Value : (object)DateTime.Parse(existingFinanceLodged)) : (object)DateTime.Parse(txtFinanceLodged.Text); propertyRecord["pro_financeassessed"] = (txtFinanceAssessed.Text == string.Empty) ? (String.IsNullOrEmpty(existingFinanceAssessed) ? (object)DBNull.Value : (object)DateTime.Parse(existingFinanceAssessed)) : (object)DateTime.Parse(txtFinanceAssessed.Text); propertyRecord["pro_conditionalapproval"] = (txtConditionalApproval.Text == string.Empty) ? (String.IsNullOrEmpty(existingConditionalApproval) ? (object)DBNull.Value : (object)DateTime.Parse(existingConditionalApproval)) : (object)DateTime.Parse(txtConditionalApproval.Text); propertyRecord["pro_valuationordered"] = (txtValuationOrdered.Text == string.Empty) ? (String.IsNullOrEmpty(existingValuationOrdered) ? (object)DBNull.Value : (object)DateTime.Parse(existingValuationOrdered)) : (object)DateTime.Parse(txtValuationOrdered.Text); propertyRecord["pro_valuationreturned"] = (txtValuationReturned.Text == string.Empty) ? (String.IsNullOrEmpty(existingValuationReturned) ? (object)DBNull.Value : (object)DateTime.Parse(existingValuationReturned)) : (object)DateTime.Parse(txtValuationReturned.Text); //propertyRecord["pro_financeapproveddate"] = // (txtFormalApproval.Text == string.Empty) ? // (String.IsNullOrEmpty(existingFormalApproval) ? (object)DBNull.Value : (object)DateTime.Parse(existingFormalApproval)) // : (object)DateTime.Parse(txtFormalApproval.Text); propertyRecord["pro_financedeclined"] = (txtFinanceDeclined.Text == string.Empty) ? (String.IsNullOrEmpty(existingFinanceDeclined) ? (object)DBNull.Value : (object)DateTime.Parse(existingFinanceDeclined)) : (object)DateTime.Parse(txtFinanceDeclined.Text); propertyRecord["pro_mortgagedocsreturned"] = (txtMortgageDocsReturned.Text == string.Empty) ? (String.IsNullOrEmpty(existingMortgageDocsReturned) ? (object)DBNull.Value : (object)DateTime.Parse(existingMortgageDocsReturned)) : (object)DateTime.Parse(txtMortgageDocsReturned.Text); //propertyRecord["pro_settlementdate"] = // (txtSettlementDate.Text == string.Empty) ? // (String.IsNullOrEmpty(existingSettlementDate) ? (object)DBNull.Value : (object)DateTime.Parse(existingSettlementDate)) // : (object)DateTime.Parse(txtSettlementDate.Text); propertyRecord["pro_financenotes"] = (string.IsNullOrEmpty(existingFinanceNotes) && string.IsNullOrEmpty(txtFinanceNotes.Text)) ? (object)DBNull.Value : (string.IsNullOrEmpty(txtFinanceNotes.Text) ? existingFinanceNotes : DateTime.Now.ToString("dd-MM-yyyy") + " :" + txtFinanceNotes.Text + Environment.NewLine + existingFinanceNotes); propertyRecord.EndEdit(); int propertyId = int.Parse(propertyRecord["pro_propertyid"].ToString()); //Inserting Updates into the FinanceBrokerUpdate table DataRow dataRow; dataRow = dataSet.Tables[1].NewRow(); dataRow["fbu_propertyid"] = propertyId; dataRow["fbu_lendingrecacceptbyclient"] = (txtLendRecAccByClient.Text == string.Empty) ? (object)DBNull.Value : (object)DateTime.Parse(txtLendRecAccByClient.Text); dataRow["fbu_financelodged"] = (txtFinanceLodged.Text == string.Empty) ? (object)DBNull.Value : (object)DateTime.Parse(txtFinanceLodged.Text); dataRow["fbu_financeassessed"] = (txtFinanceAssessed.Text == string.Empty) ? (object)DBNull.Value : (object)DateTime.Parse(txtFinanceAssessed.Text); dataRow["fbu_conditionalapproval"] = (txtConditionalApproval.Text == string.Empty) ? (object)DBNull.Value : (object)DateTime.Parse(txtConditionalApproval.Text); dataRow["fbu_valuationordered"] = (txtValuationOrdered.Text == string.Empty) ? (object)DBNull.Value : (object)DateTime.Parse(txtValuationOrdered.Text); dataRow["fbu_valuationreturned"] = (txtValuationReturned.Text == string.Empty) ? (object)DBNull.Value : (object)DateTime.Parse(txtValuationReturned.Text); //dataRow["fbu_financeapproveddate"] = (txtFormalApproval.Text == string.Empty) ? (object)DBNull.Value : (object)DateTime.Parse(txtFormalApproval.Text); dataRow["fbu_financedeclined"] = (txtFinanceDeclined.Text == string.Empty) ? (object)DBNull.Value : (object)DateTime.Parse(txtFinanceDeclined.Text); dataRow["fbu_mortgagedocsreturned"] = (txtMortgageDocsReturned.Text == string.Empty) ? (object)DBNull.Value : (object)DateTime.Parse(txtMortgageDocsReturned.Text); //dataRow["fbu_settlementdate"] = (txtSettlementDate.Text == string.Empty) ? (object)DBNull.Value : (object)DateTime.Parse(txtSettlementDate.Text); dataRow["fbu_largefinancenotes"] = (txtFinanceNotes.Text == string.Empty) ? (object)DBNull.Value : txtFinanceNotes.Text; dataRow["fbu_createddate"] = DateTime.Now; dataSet.Tables[1].Rows.Add(dataRow); sqlDA.Update(dataSet.Tables[0]); sql_FBU_DA.Update(dataSet.Tables[1]); lblResultMessage.Text = "Success : Finance date(s) for property updated successfully."; lblResultMessage.CssClass = "alert alert-success"; } else { lblResultMessage.Text = "Client ID could not be found; please try again."; lblResultMessage.CssClass = "alert alert-danger"; } } catch (Exception ex) { lblResultMessage.Text = "Oops! Something went wrong. Please try again." + ex.Message; lblResultMessage.CssClass = "alert alert-danger"; } }