public bool CheckDataExists(string valuetocheck) { if (valuetocheck == null) { throw new ArgumentNullException("Value to check can't be null"); } var sqlText = $"SELECT bank_rating_code from t_rpt_computation_bank_rating_setup WHERE bank_rating_code = @bank_rating_code"; using (SqlCommand cmd = new SqlCommand(sqlText, DatabaseOps.OpenSqlConnection())) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@bank_rating_code", valuetocheck); string value = _databaseOperations.GetData(cmd); if (string.IsNullOrWhiteSpace(value)) { return(false); } else { return(true); } } }
public TRPComputationBankRatingSetupObject GetBankRatingSetupDetails(string bankratingcode) { var sqlText = "SELECT * FROM t_rpt_computation_bank_rating_setup WHERE bank_rating_code = @bank_rating_code"; using (SqlCommand cmd = new SqlCommand(sqlText, DatabaseOps.OpenSqlConnection())) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@bank_rating_code", bankratingcode); _resultTable = _databaseOperations.GetDataTable(cmd); if (_resultTable.Rows.Count > 0) { foreach (DataRow row in _resultTable.Rows) { _computationSetupObj.bank_rating_code = row.Field <string>("bank_rating_code"); _computationSetupObj.ri_type_id = row.Field <int>("ri_type_id"); _computationSetupObj.param = row.Field <string>("param"); _computationSetupObj.description = row.Field <string>("description"); _computationSetupObj.component_weight = row["component_weight"].ToString(); _computationSetupObj.start_validity_date = row.Field <DateTime>("start_validity_date"); _computationSetupObj.end_validity_date = row.Field <DateTime?>("end_validity_date"); } } } return(_computationSetupObj); }
public Dictionary <int, string> GetFormulars(string returncode, int itemcode, int querylevel = 0) { Dictionary <int, string> cleanedFormulaData = new Dictionary <int, string>(); try { var sqlWhiteList = BuildQuery(returncode, itemcode, querylevel); using (SqlCommand cmd = new SqlCommand(sqlWhiteList, DatabaseOps.OpenSqlConnection())) { var resultDataTable = _databaseOps.GetDataTable(cmd); var Counter = resultDataTable.Rows.Count; if (Counter == 1 || Counter >= 1) { foreach (DataRow row in resultDataTable.Rows) { var formula = row["formula"].ToString(); formula = CleanFormular(formula); cleanedFormulaData.Add(Convert.ToInt32(row["item_code"].ToString()), formula); } } } } catch (Exception ex) { throw ex; } return(cleanedFormulaData); }
public int SaveComputationBankRatingSetup(TRPTLiqStressTestScoringObject scoreobj) { if (scoreobj == null) { throw new ArgumentNullException("Computation bank rating setup can't be null"); } var sqlText = "INSERT INTO t_rpt_liq_stress_test_scoring(item_code, item_description, ri_id, valuation_date, amount, last_modified, modified_by) VALUES(@item_code, @item_description, @ri_id, @valuation_date, @amount, @last_modified, @modified_by)"; using (SqlCommand cmd = new SqlCommand(sqlText, DatabaseOps.OpenSqlConnection())) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@item_code", scoreobj.item_code); if (string.IsNullOrWhiteSpace(scoreobj.item_description)) { cmd.Parameters.Add("@item_description", SqlDbType.NVarChar).SqlValue = DBNull.Value; } else { cmd.Parameters.AddWithValue("@item_description", scoreobj.item_description); } cmd.Parameters.AddWithValue("@ri_id", scoreobj.ri_id); cmd.Parameters.Add("@valuation_date", SqlDbType.DateTime).SqlValue = scoreobj.valuation_date; cmd.Parameters.AddWithValue("@amount", scoreobj.amount); cmd.Parameters.Add("@last_modified", SqlDbType.DateTime).SqlValue = DBNull.Value; cmd.Parameters.Add("@modified_by", SqlDbType.DateTime).SqlValue = DBNull.Value; _rowsAffected = cmd.ExecuteNonQuery(); } return(_rowsAffected); }
public TRPTLiqStressTestDataDefnHandler(TRPTLiqStressTestDataDefnObject testdefnobject) { _testDefnObject = testdefnobject; _databaseOps = new DatabaseOps(); _dataTable = new DataTable(); _util = new Utility(); }
public Dictionary <int, string> GetFormularDependencies(string returncode, String itemcode) { Dictionary <int, string> cleanedFormulaData = new Dictionary <int, string>(); try { var procName = "p_rpt_computation_rule_adjustment_get_item_code_dependencies"; using (SqlCommand cmd = new SqlCommand(procName, DatabaseOps.OpenSqlConnection())) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@return_code", returncode); cmd.Parameters.AddWithValue("@item_code", itemcode); var resultDataTable = _databaseOps.GetDataTable(cmd); var Counter = resultDataTable.Rows.Count; if (Counter > 0) { foreach (DataRow row in resultDataTable.Rows) { cleanedFormulaData.Add(Convert.ToInt32(row["item_code"].ToString()), CleanFormular(row["formula"].ToString())); } } } } catch (Exception ex) { throw ex; } return(cleanedFormulaData); }
public Dictionary <int, string> GetDependencies(string returncode, int itemcode, int querylevel = 0) { try { var sqlWhiteList = BuildQuery(returncode, itemcode, querylevel); using (SqlCommand cmd = new SqlCommand(sqlWhiteList, DatabaseOps.OpenSqlConnection())) { var resultDataTable = _databaseOps.GetDataTable(cmd); var Counter = resultDataTable.Rows.Count; if (Counter == 1 || Counter > 1) { foreach (DataRow row in resultDataTable.Rows) { if (String.IsNullOrWhiteSpace(row["item_code"].ToString())) { break; } cleanedFormulaData.Add(Convert.ToInt32(row["item_code"].ToString()), CleanFormular(row["formula"].ToString())); GetDependencies(returncode, Convert.ToInt32(row["item_code"]), 0); } } } } catch (Exception ex) { throw ex; } return(cleanedFormulaData); }
public TRPTComputationBankRatingScoringHandler(TRPTComputationBankRatingScoringObject scoring) { this._computationSscoring = scoring; _resultTable = new DataTable(); _databaseOperations = new DatabaseOps(); _util = new Utility(); }
public TRPTLiqStressTestDataDefnHandler() { _databaseOps = new DatabaseOps(); _dataTable = new DataTable(); _testDefnObject = new TRPTLiqStressTestDataDefnObject(); _util = new Utility(); }
public void BindAllReturnInstitutions(System.Web.UI.WebControls.DropDownList ddplist, int ritypeid) { DataTable dataTable = new DataTable(); if (ritypeid > 0) { List <QueryStore> result = new List <QueryStore>(); string sqlQuery = String.Format("SELECT a.ri_id, a.fullname FROM t_core_reporting_institution a JOIN t_core_ri_mapping b ON a.ri_id = b.ri_id JOIN t_core_ri_type c ON b.ri_type_id = c.ri_type_id WHERE c.ri_type_id = @ritypeid"); using (SqlCommand command = new SqlCommand(sqlQuery, DatabaseOps.OpenSqlConnection())) { command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@ritypeid", ritypeid); SqlDataAdapter adapter = new SqlDataAdapter(command); adapter.Fill(dataTable); ddplist.DataSource = dataTable; ddplist.DataValueField = "ri_id"; ddplist.DataTextField = "fullname"; ddplist.DataBind(); ddplist.Items.Insert(0, "-Select Institution-"); } } }
public Dictionary <string, string> GetVersionIdNRunId(string comprulename, int ritypeid, int riid, string frequency, int scheduleid, DateTime wcdate) { Dictionary <string, string> returnedvalues = new Dictionary <string, string>(); string sqlText = $"SELECT run_id, version_id FROM t_rpt_computation_value_table WHERE computation_rule = '{comprulename}' AND ri_type_id = {ritypeid} and ri_id = {riid} and freq_unit = '{frequency}' and schedule_id = {scheduleid}"; sqlText = $"SELECT DISTINCT run_id, version_id FROM t_rpt_computation_value_table WHERE computation_rule = @computation_rule AND ri_type_id = @ri_type_id AND ri_id = @ri_id AND freq_unit = @freq_unit AND schedule_id = @schedule_id"; //AND work_collection_date = @work_collection_date"; using (SqlCommand cmd = new SqlCommand(sqlText, DatabaseOps.OpenSqlConnection())) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@computation_rule", comprulename); cmd.Parameters.AddWithValue("@ri_type_id", ritypeid); cmd.Parameters.AddWithValue("@ri_id", riid); cmd.Parameters.AddWithValue("@freq_unit", frequency); cmd.Parameters.AddWithValue("@schedule_id", scheduleid); //cmd.Parameters.Add("@work_collection_date", SqlDbType.DateTime).Value = wcdate; var reader = _databaseOperations.ReadData(cmd); while (reader.Read()) { if (reader.HasRows) { returnedvalues.Add("run_id", reader["run_id"].ToString()); returnedvalues.Add("version_id", reader["version_id"].ToString()); } } reader.Close(); return(returnedvalues); } }
public static void Run(string projectFolder, SystemDevelopmentConfiguration configuration, Logger log) { var outputFilePath = Path.Combine(projectFolder, "GeneratedCode", "TypedDataLayer.cs"); log.Info("Writing generated code to " + outputFilePath); var outputDir = Path.GetDirectoryName(outputFilePath); log.Debug("Creating directory: " + outputDir); Directory.CreateDirectory(outputDir); var baseNamespace = configuration.LibraryNamespaceAndAssemblyName + ".DataAccess"; using (var writer = new StreamWriter(outputFilePath)) { writeUsingStatements(writer); var databaseInfo = DatabaseOps.CreateDatabase( DatabaseFactory.CreateDatabaseInfo(configuration.databaseConfiguration.DatabaseType, configuration.databaseConfiguration.ConnectionString)); ExecuteDatabaseUpdatesScript.Run(projectFolder, databaseInfo, log); generateDataAccessCodeForDatabase( log, databaseInfo, projectFolder, writer, baseNamespace, configuration); } }
public void OptionBinder(DropDownList controlid, string sqlcommand, params string[] datafields) { try { controlid.Items.Clear(); if (datafields.Length < 1) { throw new ArgumentException("Data text and value field are required"); } using (SqlCommand cmd = new SqlCommand(sqlcommand, DatabaseOps.OpenSqlConnection())) { cmd.CommandType = CommandType.Text; _resultTable = _databaseOperations.GetDataTable(cmd); controlid.DataSource = _resultTable; controlid.DataTextField = datafields[0]; controlid.DataValueField = datafields[1]; controlid.DataBind(); } } catch (Exception ex) { throw ex; } }
public TCoreRiType() { //_conString = ConnectionString.GetConnectionString(); _resultTable = new DataTable(); _databaseOperations = new DatabaseOps(); _util = new Utility(); }
internal static void Generate(DBConnection cn, TextWriter writer, string baseNamespace, Database database) { writer.WriteLine("namespace " + baseNamespace + "." + database.SecondaryDatabaseName + "TableConstants {"); foreach (var table in DatabaseOps.GetDatabaseTables(database)) { CodeGenerationStatics.AddSummaryDocComment(writer, "This object represents the constants of the " + table + " table."); writer.WriteLine("public class " + StandardLibraryMethods.GetCSharpSafeClassName(table.TableNameToPascal(cn)) + "Table {"); CodeGenerationStatics.AddSummaryDocComment(writer, "The name of this table."); writer.WriteLine("public const string Name = \"" + table + "\";"); foreach (var column in new TableColumns(cn, table, false).AllColumnsExceptRowVersion) { CodeGenerationStatics.AddSummaryDocComment(writer, "Contains schema information about this column."); writer.WriteLine("public class " + StandardLibraryMethods.GetCSharpSafeClassName(column.PascalCasedNameExceptForOracle) + "Column {"); CodeGenerationStatics.AddSummaryDocComment(writer, "The name of this column."); writer.WriteLine("public const string Name = \"" + column.Name + "\";"); CodeGenerationStatics.AddSummaryDocComment( writer, "The size of this column. For varchars, this is the length of the biggest string that can be stored in this column."); writer.WriteLine("public const int Size = " + column.Size + ";"); writer.WriteLine("}"); } writer.WriteLine("}"); } writer.WriteLine("}"); }
public void ProcessStream(string id) { Video video = DatabaseOps.GetVideoRecord(id); HttpContextBase ctx = this.HttpContext; VideoStream vStream = new VideoStream(video, ctx, Request, Response); vStream.OpenStream(); }
public TCoreUsers() { _tcoreUserObject = new TCoreUsersObject(); _databaseOperations = new DatabaseOps(); _resultTable = new DataTable(); _tcoreUserObjectList = new List <TCoreDeptUnit>(); _vRTNUserDeptRoleForQbObjects = new List <VRTNUserDeptRoleForQbObject>(); }
public int GetGroupId(int userid) { var sqlText = ""; RealData = DatabaseOps.GetData(sqlText) ?? null; int groupId = (string.IsNullOrWhiteSpace(RealData) ? 0 : Convert.ToInt32(RealData)); return(groupId); }
public void ProcessStream(string id) { string filepath = DatabaseOps.GetVideoPath(id); string filename = id.Replace(" ", "_"); HttpContextBase ctx = this.HttpContext; VideoStream vStream = new VideoStream(filepath, filename, ctx, Request, Response); vStream.OpenStream(); }
public DataTable GetBankRatingSetupRiTypes() { var sqlText = "Select distinct a.ri_type_id, a.description from t_core_ri_type a inner join t_rpt_computation_bank_rating_setup b on a.ri_type_id = b.ri_type_id"; using (SqlCommand cmd = new SqlCommand(sqlText, DatabaseOps.OpenSqlConnection())) { cmd.CommandType = CommandType.Text; _resultTable = _databaseOperations.GetDataTable(cmd); return(_resultTable); } }
void Database.PerformMaintenance() { ExecuteDbMethod( delegate(DBConnection cn) { foreach (var tableName in DatabaseOps.GetDatabaseTables(this)) { executeLongRunningCommand(cn, "ALTER INDEX ALL ON " + tableName + " REBUILD"); executeLongRunningCommand(cn, "UPDATE STATISTICS " + tableName); } }); }
public DataTable GetBankRatingSetupDetails() { var sqlText = "SELECT * FROM t_rpt_computation_bank_rating_setup"; sqlText = "SELECT bank_rating_code AS 'Bank Rating Code', param AS 'Param', description AS 'Description', component_weight AS 'Component Weight',start_validity_date AS 'Start Validity Date' FROM t_rpt_computation_bank_rating_setup"; using (SqlCommand cmd = new SqlCommand(sqlText, DatabaseOps.OpenSqlConnection())) { cmd.CommandType = CommandType.Text; _resultTable = _databaseOperations.GetDataTable(cmd); return(_resultTable); } }
/// <summary> /// Return reporting institution code by id /// </summary> /// <param name="ritypeid"></param> /// <returns></returns> public String GetRICodeByID(String ritypeid) { String returnCode = null; var sqlQuery = $"SELECT ri_type_code FROM t_core_ri_type where ri_type_id = {ritypeid}"; using (SqlCommand command = new SqlCommand(sqlQuery, DatabaseOps.OpenSqlConnection())) { returnCode = _databaseOperations.GetData(command); } return(returnCode); }
public int SaveComputationBankRatingScore(TRPTComputationBankRatingScoringObject scoring) { if (scoring == null) { throw new ArgumentNullException("Computation bank rating scoring can't be null"); } var sqlText = "INSERT INTO t_rpt_computation_bank_rating_scoring(bank_rating_code, ri_type_id, ri_id, rating_date, rating_score, last_modified, modified_by) VALUES(@bank_rating_code, @ri_type_id, @ri_id, @rating_date, @rating_score, @last_modified, @modified_by)"; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required)) { using (SqlCommand cmd = new SqlCommand(sqlText, DatabaseOps.OpenSqlConnection())) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@bank_rating_code", scoring.bank_rating_code); cmd.Parameters.AddWithValue("@ri_type_id", scoring.ri_type_id); cmd.Parameters.AddWithValue("@ri_id", scoring.ri_id); cmd.Parameters.AddWithValue("@rating_date", scoring.rating_date); if (string.IsNullOrWhiteSpace(scoring.rating_score)) { cmd.Parameters.AddWithValue("@rating_score", SharedConst.CONST_MONEY_INT_VALUE); } else { cmd.Parameters.AddWithValue("@rating_score", scoring.rating_score); } if (scoring.last_modified == null || scoring.last_modified == DateTime.MinValue) { cmd.Parameters.AddWithValue("@last_modified", DateTime.Now); } else { cmd.Parameters.AddWithValue("@last_modified", scoring.last_modified); } if (string.IsNullOrWhiteSpace(scoring.modified_by)) { cmd.Parameters.AddWithValue("@modified_by", DBNull.Value); } else { cmd.Parameters.AddWithValue("@modified_by", scoring.modified_by); } _rowsAffected = cmd.ExecuteNonQuery(); } scope.Complete(); } return(_rowsAffected); }
public ActionResult StreamView(string id) { if (RequestValidation.HasNullParams(id)) { return(RedirectToAction("Index", "Home")); } else { Video requestedVid = DatabaseOps.GetVideoRecord(id); System.Diagnostics.Debug.WriteLine("Video Title: " + requestedVid.Title); return(View(requestedVid)); } }
public List <string> IsRestrictedItem() { DatabaseOps ops = new DatabaseOps(); List <string> canEdit = new List <string>(); var commandQuery = "select item_code from t_dis_submission_reference_calc"; using (SqlCommand cmd = new SqlCommand(commandQuery, DatabaseOps.OpenSqlConnection())) { cmd.CommandType = CommandType.Text; canEdit = DatabaseOps.ConvertToStringList(ops.GetDataTable(cmd)); } return(canEdit); }
public int SaveSimpleRule(TRPTComputationValueTableObject trptcomputationvaluetableobject) { //using (TransactionScope sc = new TransactionScope(TransactionScopeOption.Required)) //{ var sqlText = @"INSERT INTO t_rpt_computation_value_table_temp (computation_rule, version_id, ri_type_id, ri_id, work_collection_date, freq_unit, schedule_id, run_id, row_code, column_1, comment,last_modified, modified_by) VALUES(@computation_rule, @version_id, @ri_type_id, @ri_id, @work_collection_date, @freq_unit, @schedule_id, @run_id, @row_code, @column_1, @comment, @last_modified, @modified_by)"; using (SqlCommand cmd = new SqlCommand(sqlText, DatabaseOps.OpenSqlConnection())) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@computation_rule", trptcomputationvaluetableobject.computation_rule); cmd.Parameters.AddWithValue("@version_id", trptcomputationvaluetableobject.version_id); cmd.Parameters.AddWithValue("@ri_type_id", trptcomputationvaluetableobject.ri_type_id); cmd.Parameters.AddWithValue("@ri_id", trptcomputationvaluetableobject.ri_id); cmd.Parameters.Add("@work_collection_date", SqlDbType.DateTime).Value = trptcomputationvaluetableobject.work_collection_date; cmd.Parameters.AddWithValue("@freq_unit", trptcomputationvaluetableobject.freq_unit); cmd.Parameters.AddWithValue("@schedule_id", trptcomputationvaluetableobject.schedule_id); cmd.Parameters.AddWithValue("@run_id", trptcomputationvaluetableobject.run_id); cmd.Parameters.AddWithValue("@row_code", trptcomputationvaluetableobject.row_code); cmd.Parameters.AddWithValue("@column_1", trptcomputationvaluetableobject.column_1); if (string.IsNullOrWhiteSpace(trptcomputationvaluetableobject.comment)) { cmd.Parameters.AddWithValue("@comment", DBNull.Value); } else { cmd.Parameters.AddWithValue("@comment", trptcomputationvaluetableobject.comment); } cmd.Parameters.Add("@last_modified", SqlDbType.DateTime).Value = DateTime.Now.ToShortDateString(); cmd.Parameters.AddWithValue("@modified_by", trptcomputationvaluetableobject.modified_by); rowsAffected = cmd.ExecuteNonQuery(); //Call the comprulemerge procedure to for deep merging of column values mergeAffected = _ruleVarianceAdjustment.CompuRuleMerge(); if (rowsAffected > 0 && mergeAffected == 0) { return(rowsAffected + mergeAffected); } else { return(0); } } //sc.Complete(); }
internal static void Generate(DBConnection cn, TextWriter writer, string baseNamespace, Database database) { writer.WriteLine("namespace " + baseNamespace + "." + database.SecondaryDatabaseName + "CommandConditions {"); foreach (var table in DatabaseOps.GetDatabaseTables(database)) { // Write the interface for all of the table's conditions. writer.WriteLine("public interface " + GetTableConditionInterfaceName(cn, table) + ": TableCondition {}"); writeEqualityConditionClasses(cn, writer, table); writeInequalityConditionClasses(cn, writer, table); writeInConditionClasses(cn, writer, table); writeLikeConditionClasses(cn, writer, table); } writer.WriteLine("}"); // namespace }
public int Save(TSysConfigurationIrsObject obj) { var sqlText = "Update t_sys_configuration_irs set default_value = @default_value, config_value = @config_value, enabled = @enabled where config_id = @config_id"; using (SqlCommand command = new SqlCommand(sqlText, base.Connection)) { command.Parameters.AddWithValue("@default_value", obj.default_value); command.Parameters.AddWithValue("@config_value", obj.config_value); command.Parameters.AddWithValue("@enabled", obj.enabled); command.Parameters.AddWithValue("@config_id", obj.config_id); RowsAffected = DatabaseOps.ExecuteNonQuery(command); } return(RowsAffected); }
public int UpdateComputationBankRatingSetup(TRPComputationBankRatingSetupObject setup) { if (setup == null) { throw new ArgumentNullException("Computation bank rating scoring can't be null"); } var sqlText = "UPDATE t_rpt_computation_bank_rating_setup SET ri_type_id = @ri_type_id, param = @param, description = @description, component_weight = @component_weight, start_validity_date = @start_validity_date, end_validity_date = @end_validity_date, last_modified = @last_modified, modified_by = @modified_by WHERE bank_rating_code = @bank_rating_code"; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required)) { using (SqlCommand cmd = new SqlCommand(sqlText, DatabaseOps.OpenSqlConnection())) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@ri_type_id", setup.ri_type_id); cmd.Parameters.AddWithValue("@param", setup.param); cmd.Parameters.AddWithValue("@description", setup.description); cmd.Parameters.AddWithValue("@component_weight", setup.component_weight); cmd.Parameters.AddWithValue("@start_validity_date", setup.start_validity_date); cmd.Parameters.AddWithValue("@end_validity_date", setup.end_validity_date); if (setup.start_validity_date == null || setup.start_validity_date == DateTime.MinValue) { cmd.Parameters.Add("@start_validity_date", SqlDbType.DateTime).SqlValue = DateTime.MinValue; } else { cmd.Parameters.Add("@start_validity_date", SqlDbType.DateTime).SqlValue = setup.start_validity_date; } if (setup.end_validity_date == null || setup.end_validity_date == DateTime.MinValue) { cmd.Parameters.Add("@end_validity_date", SqlDbType.DateTime).SqlValue = DBNull.Value; } else { cmd.Parameters.Add("@end_validity_date", SqlDbType.DateTime).SqlValue = setup.end_validity_date; } cmd.Parameters.AddWithValue("@bank_rating_code", setup.bank_rating_code); _rowsAffected = cmd.ExecuteNonQuery(); } scope.Complete(); } return(_rowsAffected); }