/// <summary> /// Method to add a TrancheEvent into database. We use the NullableTypes to make the correspondance between /// nullable int, decimal and double types in database and our own objects /// </summary> /// <param name="trancheEvent">TrancheEvent Object</param> /// <returns>The id of the Tranche Event which has been added</returns> public int Add(TrancheEvent trancheEvent) { const string sqlText = @" INSERT INTO [TrancheEvents] ( [id] ,[interest_rate] ,[amount] ,[maturity] ,[start_date] ,[applied_new_interest]) VALUES (@Id, @InterestRate, @Amount, @Maturity, @StartDate, @applied_new_interest) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) using (var cmd = new OpenCbsCommand(sqlText, conn)) { SetTrancheEvent(cmd, trancheEvent); return int.Parse(cmd.ExecuteScalar().ToString()); } }
public void Delete(SqlTransaction pSqlTransaction) { const string sqltext = "DELETE FROM LoanScale"; using (OpenCbsCommand delete = new OpenCbsCommand(sqltext, pSqlTransaction.Connection, pSqlTransaction)) { delete.ExecuteNonQuery(); } }
public void DeleteAllProvisioningRules(SqlTransaction sqlTransac) { const string sqlText = "DELETE FROM ProvisioningRules"; using (OpenCbsCommand delete = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac)) { delete.ExecuteNonQuery(); } }
public void InsertLoanScale(LoanScaleRate pLoanScaleRate, SqlTransaction pSqlTransaction) { const string sqlText = @"INSERT INTO LoanScale(id,ScaleMin, ScaleMax) VALUES(@number,@Min, @Max)"; using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, pSqlTransaction.Connection, pSqlTransaction)) { SetLoanScale(insert, pLoanScaleRate); insert.ExecuteNonQuery(); } }
public void Add(DateTime pDate, double pRate, Currency pCurrency) { const string q = "INSERT INTO [ExchangeRates] ([exchange_date], [exchange_rate],[currency_id]) " + "VALUES(@date,@rate,@currency)"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { SetExchangeRate(c, pDate.Date, pRate,pCurrency); c.ExecuteNonQuery(); } }
public void DeleteStandardBooking(int id) { const string sqlText = @"DELETE FROM StandardBookings WHERE Id = @Id"; using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand insertIntoTable = new OpenCbsCommand(sqlText, conn)) { insertIntoTable.AddParam("@Id", id); insertIntoTable.ExecuteNonQuery(); } } }
public void CreateStandardBooking(Booking booking) { const string sqlText = @"INSERT INTO StandardBookings([Name], debit_account_id, credit_account_id) VALUES (@name, @debit_account_id, @credit_account_id)"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand insertIntoTable = new OpenCbsCommand(sqlText, conn)) { insertIntoTable.AddParam("@name", booking.Name); insertIntoTable.AddParam("@debit_account_id", booking.DebitAccount.Id); insertIntoTable.AddParam("@credit_account_id", booking.CreditAccount.Id); insertIntoTable.ExecuteNonQuery(); } }
public int Add(Currency pCurrency, SqlTransaction t) { const string q = @"INSERT INTO [Currencies] ([name], [code], [is_pivot], [is_swapped],use_cents) VALUES(@name, @code, @is_pivot, @is_swapped,@use_cents) SELECT SCOPE_IDENTITY()"; using (OpenCbsCommand c = new OpenCbsCommand(q, t.Connection, t)) { c.AddParam("@name", pCurrency.Name); c.AddParam("@code", pCurrency.Code); c.AddParam("@is_pivot", pCurrency.IsPivot); c.AddParam("@is_swapped", pCurrency.IsSwapped); c.AddParam("@use_cents", pCurrency.UseCents); return int.Parse(c.ExecuteScalar().ToString()); } }
public void MakeEventExported(int pSavingEventId, SqlTransaction sqlTransac) { const string q = @"UPDATE [SavingEvents] SET [is_exported] = @is_exported WHERE [id] = @id"; using (OpenCbsCommand c = new OpenCbsCommand(q, sqlTransac.Connection, sqlTransac)) { c.AddParam("@is_exported", true); c.AddParam("@id", pSavingEventId); c.ExecuteNonQuery(); } }
public static int UpdateAccountActive(string pAccountName, bool pActive, SqlConnection pSqlConnection) { const string sqlText = @"UPDATE [Accounts].[dbo].[SqlAccounts] SET active = @active WHERE [account_name] = @accountName"; using (OpenCbsCommand update = new OpenCbsCommand(sqlText, pSqlConnection)) { update.AddParam("@active", pActive); update.AddParam("@accountName", pAccountName); return(update.ExecuteNonQuery()); } }
public int Add(SavingEvent pSavingEvent, int pSavingContractId, SqlTransaction sqlTransac) { const string q = @"INSERT INTO [SavingEvents]( [user_id], [contract_id], [code], [amount], [description], [reference_number], [deleted], [creation_date], [cancelable], [is_fired], [related_contract_code], [fees], [savings_method], [pending], [is_local], [pending_event_id], [teller_id], [loan_event_id]) VALUES( @user_id, @contract_id, @code, @amount, @description, @reference_number, @deleted, @creation_date, @cancelable, @is_fired, @related_contract_code, @fees, @savings_method, @pending, @is_local, @pending_event_id, @teller_id, @loan_event_id) SELECT CONVERT(int, SCOPE_IDENTITY())" ; using (OpenCbsCommand c = new OpenCbsCommand(q, sqlTransac.Connection, sqlTransac)) { SetInsertCommandForSavingEvent(c, pSavingEvent, pSavingContractId); pSavingEvent.Id = Convert.ToInt32(c.ExecuteScalar()); return(pSavingEvent.Id); } }
public void DeleteAccountingRule(IAccountingRule pRule) { const string sqlText = @"UPDATE [AccountingRules] SET [deleted] = 1 WHERE id = @id"; using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand delete = new OpenCbsCommand(sqlText, conn)) { delete.AddParam("@id", pRule.Id); delete.ExecuteNonQuery(); } } }
public int UpdateSelectedParameter(string pName, int pNewValue) { ApplicationSettings.GetInstance(_user.Md5).UpdateParameter(pName, pNewValue); string sql = "UPDATE GeneralParameters SET [value] = @value WHERE upper([key]) = @key"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(sql, conn)) { c.AddParam("@value", pNewValue.ToString()); c.AddParam("@key", pName); return(c.ExecuteNonQuery()); } }
public void UpdateEventDescription(int pSavingEventId, string pDescription) { const string q = @"UPDATE [SavingEvents] SET [description] = @description WHERE [id] = @id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@description", pDescription); c.AddParam("@id", pSavingEventId); c.ExecuteNonQuery(); } }
public int GetContractQuantityByCurrencyId(int currencyId) { const string q = @" SELECT COUNT(Credit.id) AS contract_quantity FROM [dbo].[Currencies] AS Cur INNER JOIN Packages AS Pack ON Pack.currency_id=Cur.id INNER JOIN Credit ON Credit.package_id=Pack.id WHERE Cur.id=@currency_id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@currency_id", currencyId); return (int)c.ExecuteScalar(); } }
public void ChangePendingEventStatus(int pSavingEventId, bool isPending) { const string q = @"UPDATE [SavingEvents] SET [pending] = @pending WHERE [id] = @id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@pending", isPending); c.AddParam("@id", pSavingEventId); c.ExecuteNonQuery(); } }
public double GetNearestRate(DateTime pDate, Currency pCurrency) { const string q = @"SELECT TOP 1 exchange_rate FROM ExchangeRates WHERE exchange_date <= @date AND currency_id = @currency ORDER BY exchange_date DESC"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@currency", pCurrency.Id); c.AddParam("@date", pDate); return(Convert.ToDouble(c.ExecuteScalar())); } }
public void DeleteTiers() { OpenCbsCommand deletePersonGroupBelonging = new OpenCbsCommand("DELETE PersonGroupBelonging", SqlConnection); deletePersonGroupBelonging.ExecuteNonQuery(); OpenCbsCommand deletePersons = new OpenCbsCommand("DELETE Persons", SqlConnection); deletePersons.ExecuteNonQuery(); OpenCbsCommand deleteGroups = new OpenCbsCommand("DELETE Groups", SqlConnection); deleteGroups.ExecuteNonQuery(); OpenCbsCommand deleteTiers = new OpenCbsCommand("DELETE Tiers", SqlConnection); deleteTiers.ExecuteNonQuery(); }
public int Add(Currency pCurrency, SqlTransaction t) { const string q = @"INSERT INTO [Currencies] ([name], [code], [is_pivot], [is_swapped],use_cents) VALUES(@name, @code, @is_pivot, @is_swapped,@use_cents) SELECT SCOPE_IDENTITY()"; using (OpenCbsCommand c = new OpenCbsCommand(q, t.Connection, t)) { c.AddParam("@name", pCurrency.Name); c.AddParam("@code", pCurrency.Code); c.AddParam("@is_pivot", pCurrency.IsPivot); c.AddParam("@is_swapped", pCurrency.IsSwapped); c.AddParam("@use_cents", pCurrency.UseCents); return(int.Parse(c.ExecuteScalar().ToString())); } }
public InstallmentType AddBiWeeklyInstallmentType() { InstallmentType biWeekly = new InstallmentType(); biWeekly.Name = "Bi-Weekly"; biWeekly.NbOfDays = 14; biWeekly.NbOfMonths = 0; string sqlText = "INSERT INTO InstallmentTypes (name,nb_of_days,nb_of_months) VALUES ('" + biWeekly.Name + "'," + biWeekly.NbOfDays + "," + biWeekly.NbOfMonths + ") SELECT SCOPE_IDENTITY()"; OpenCbsCommand insert = new OpenCbsCommand(sqlText, SqlConnection); biWeekly.Id = int.Parse(insert.ExecuteScalar().ToString()); return(biWeekly); }
public static string GetDatabasePath(SqlConnection pSqlConnection) { const string sqlText = @" SELECT physical_name FROM [master].sys.master_files WHERE database_id = 1 AND FILE_ID = 1 "; using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection)) { string dbFilePath = (string)select.ExecuteScalar(); return(Path.GetDirectoryName(dbFilePath)); } }
public double GetMostRecentlyRate(DateTime pDate, Currency pCurrency) { const string q = @"SELECT TOP 1 exchange_rate FROM ExchangeRates WHERE exchange_date <= @date AND currency_id = @currency ORDER BY exchange_date DESC"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@currency",pCurrency.Id); c.AddParam("@date", pDate); return Convert.ToDouble(c.ExecuteScalar()); } }
public List <QueuedEmail> SelectAll() { List <QueuedEmail> qeuedEmails = new List <QueuedEmail>(); const string q = @"SELECT * FROM dbo.QueuedEmails where Deleted <> 1"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) using (OpenCbsReader r = c.ExecuteReader()) { if (r.Empty) { return(qeuedEmails); } while (r.Read()) { var queuedEmail = new QueuedEmail { Id = r.GetInt("Id"), Priority = r.GetInt("Priority"), From = r.GetString("From"), FromName = r.GetString("FromName"), To = r.GetString("To"), ToName = r.GetString("ToName"), ReplyTo = r.GetString("ReplyTo"), ReplyToName = r.GetString("ReplyToName"), CC = r.GetString("CC"), Bcc = r.GetString("Bcc"), Body = r.GetString("Body"), CreatedOnUtc = r.GetDateTime("CreatedOnUtc"), SentOnUtc = r.GetNullDateTime("SentOnUtc"), EmailAccountId = r.GetInt("EmailAccountId"), SentTries = r.GetInt("SentTries"), Subject = r.GetString("Subject"), Deleted = r.GetBool("Deleted"), }; if (queuedEmail.EmailAccountId > 0) { EmailAccountManager _emailAccountManager = new EmailAccountManager(user); queuedEmail.EmailAccount = _emailAccountManager.SelectById(queuedEmail.EmailAccountId); } qeuedEmails.Add(queuedEmail); } } return(qeuedEmails); }
private static string BackupToFile(string fileName, string database, SqlConnection connection) { string backupDirectory; if (TechnicalSettings.UseDemoDatabase) { backupDirectory = @"C:\Users\Public"; } else { // To perform a backup we first need to get the appropriate backup folder, which is a bit tricky. // First, we need to get the service name. const string query = "SELECT @@SERVICENAME AS name"; OpenCbsCommand cmd = new OpenCbsCommand(query, connection); string serviceName = string.Empty; using (OpenCbsReader reader = cmd.ExecuteReader()) { if (reader.Empty) { return(null); } reader.Read(); serviceName = reader.GetString("name"); } // Then get the instance name from the registry const string sqlServerKey = @"SOFTWARE\Microsoft\Microsoft SQL Server"; string key = string.Format(@"{0}\Instance Names\SQL", sqlServerKey); string instanceName = ExtendedRegistry.GetKeyValue(key, serviceName); if (string.IsNullOrEmpty(instanceName)) { return(null); } // Finally, get the backup directory key = string.Format(@"{0}\{1}\MSSQLServer", sqlServerKey, instanceName); backupDirectory = ExtendedRegistry.GetKeyValue(key, "BackupDirectory"); if (string.IsNullOrEmpty(backupDirectory)) { return(null); } } string path = Path.Combine(backupDirectory, fileName); BackupToFileImpl(path, database, connection); return(path); }
public static void AttachDemoDatabase(SqlConnection connection, string path) { var query = String.Format("CREATE DATABASE DemoDB ON " + "(FILENAME = '{0}\\DemoDB.mdf'), " + "(FILENAME = '{0}\\DemoDB.ldf') " + "FOR ATTACH; " + "CREATE DATABASE DemoDB_attachments ON " + "(FILENAME = '{0}\\DemoDB_attachments.mdf'), " + "(FILENAME = '{0}\\DemoDB_attachments.ldf') " + "FOR ATTACH;", path); using (var command = new OpenCbsCommand(query, connection)) { command.ExecuteNonQuery(); } }
public int GetContractQuantityByCurrencyId(int currencyId) { const string q = @" SELECT COUNT(Credit.id) AS contract_quantity FROM [dbo].[Currencies] AS Cur INNER JOIN Packages AS Pack ON Pack.currency_id=Cur.id INNER JOIN Credit ON Credit.package_id=Pack.id WHERE Cur.id=@currency_id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@currency_id", currencyId); return((int)c.ExecuteScalar()); } }
public void DeleteEventInDatabase(SavingEvent pSavingEvent) { const string q = @"UPDATE [SavingEvents] SET [deleted] = 1 , is_exported = 0 ,[cancel_date] = @cancel_date WHERE id = @id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@id", pSavingEvent.Id); c.AddParam("@cancel_date", pSavingEvent.CancelDate.Value); c.ExecuteNonQuery(); } }
public static void AddDatabaseToAccounts(string pAccountName, string pDatabaseName, string pLogin, string pPassword, SqlConnection pSqlConnection) { const string sqlText = @"INSERT INTO [Accounts].[dbo].[SqlAccounts] ([account_name],[database_name],[user_name],[password],[active]) VALUES (@accountName, @databaseName, @login, @password,1)"; using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, pSqlConnection)) { insert.AddParam("@accountName", pAccountName); insert.AddParam("@databaseName", pDatabaseName); insert.AddParam("@login", pLogin); insert.AddParam("@password", pPassword); insert.ExecuteNonQuery(); } }
public static void AddDatabaseToAccounts(string pAccountName, string pDatabaseName, string pLogin, string pPassword, SqlConnection pSqlConnection) { const string sqlText = @"INSERT INTO [Accounts].[dbo].[SqlAccounts] ([account_name],[database_name],[user_name],[password],[active]) VALUES (@accountName, @databaseName, @login, @password,1)"; using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, pSqlConnection)) { insert.AddParam("@accountName", pAccountName); insert.AddParam("@databaseName", pDatabaseName); insert.AddParam("@login", pLogin); insert.AddParam("@password", pPassword); insert.ExecuteNonQuery(); } }
public int AddFundingLineEvent(FundingLineEvent pFundingLineEvent, SqlTransaction pTransac) { const string sqlText = @"INSERT INTO [FundingLineEvents] ([code], [amount], [direction], [fundingline_id], [deleted], [creation_date], [type], user_id, contract_event_id) VALUES (@code, @amount, @direction, @fundingLineId, @deleted, @creationDate, @type, @user_id, @contract_event_id ) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand cmd = pTransac == null ? new OpenCbsCommand(sqlText, conn) : new OpenCbsCommand(sqlText, pTransac.Connection, pTransac)) { cmd.AddParam("@code", pFundingLineEvent.Code); cmd.AddParam("@amount", pFundingLineEvent.Amount); cmd.AddParam("@direction", (int)pFundingLineEvent.Movement); cmd.AddParam("@fundingLineId", pFundingLineEvent.FundingLine.Id); // pFundingLineId); cmd.AddParam("@deleted", false); cmd.AddParam("@creationDate", pFundingLineEvent.CreationDate); cmd.AddParam("@type", (int)pFundingLineEvent.Type); cmd.AddParam("@user_id", _user == null? (object)null:_user.Id); cmd.AddParam("contract_event_id", pFundingLineEvent.AttachTo == null ? (object)null: pFundingLineEvent.AttachTo.Id); pFundingLineEvent.Id = Convert.ToInt32(cmd.ExecuteScalar()); return(pFundingLineEvent.Id); } }
public bool Update(QueuedSMS queuedSMS) { bool updateOk = false; try { const string q = @"UPDATE dbo.QueuedSMS SET [From] = @From, [Recipient] = @Recipient, [RecipientId] = @RecipientId, [ContractId] = @ContractId, [Charged] = @Charged, [Message] = @Message, [CreatedOnUtc] = @CreatedOnUtc, [SentOnUtc] = @SentOnUtc, [SentTries] = @SentTries, [Response] = @Response, [Deleted] = @Deleted WHERE Id = @Id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@Id", queuedSMS.Id); c.AddParam("@From", queuedSMS.From); c.AddParam("@Recipient", queuedSMS.Recipient); c.AddParam("@RecipientId", queuedSMS.RecipientId); c.AddParam("@ContractId", queuedSMS.ContractId); c.AddParam("@Charged", queuedSMS.Charged); c.AddParam("@Message", queuedSMS.Message); c.AddParam("@CreatedOnUtc", queuedSMS.CreatedOnUtc); c.AddParam("@SentOnUtc", queuedSMS.SentOnUtc); c.AddParam("@SentTries", queuedSMS.SentTries); c.AddParam("@Response", queuedSMS.Response); c.AddParam("@Deleted", false); c.ExecuteNonQuery(); updateOk = true; } } catch (System.Exception ex) { throw ex; } return(updateOk); }
public IAccountingRule Select(int pId) { const string sqlText = @"SELECT rule_type FROM [AccountingRules] WHERE deleted = 0 AND id = @id"; IAccountingRule rule = new ContractAccountingRule(); using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { select.AddParam("@id", pId); using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) { return(null); } reader.Read(); if (reader.GetChar("rule_type") == 'C') { rule = new ContractAccountingRule { Id = pId } } ; } } } if (rule is ContractAccountingRule) { rule = SelectContractAccountingRule(rule.Id); } List <Account> accounts = _accountManager.SelectAllAccounts(); rule.DebitAccount = accounts.FirstOrDefault(item => item.Id == rule.DebitAccount.Id); rule.CreditAccount = accounts.FirstOrDefault(item => item.Id == rule.CreditAccount.Id); return(rule); }
/// <summary> /// Executes a SQL script file.<br/> /// Each peace of script delimited by "GO" is executed individually.<br/> /// If any error occurs, the script execution stops and returns an error.<br/> /// Exept after the /** IGNORE ERRORS **/ section where all errors are ignored.<br/> /// Warning : Only /*xxx*/ comments on a single line are supported!<br/> /// </summary> /// <param name="pScriptFile">Scripy file path</param> /// <param name="pDatabaseName"></param> /// <param name="pSqlConnection"></param> /// <returns>Script exec result status</returns> public static void ExecuteScript(string pScriptFile, string pDatabaseName, SqlConnection pSqlConnection) { List <string> queries = new List <string> { string.Format("USE [{0}]", pDatabaseName) }; queries.AddRange(_ParseSqlFile(pScriptFile)); foreach (string query in queries) { OpenCbsCommand command = new OpenCbsCommand(query, pSqlConnection) { CommandTimeout = 480 }; command.ExecuteNonQuery(); } }
public static string GetDatabaseNameForAccount(string pAccountName, SqlConnection pSqlConnection) { const string sqlText = @"SELECT database_name FROM Accounts.dbo.SqlAccounts WHERE account_name = @account"; if (pSqlConnection.State == ConnectionState.Closed) { pSqlConnection.Open(); } using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection)) { select.AddParam("@account", pAccountName); return((string)select.ExecuteScalar()); } }
public void Update(Currency pCurrency, SqlTransaction t) { const string q = @"UPDATE [Currencies] set [name] = @name, [code] = @code, [is_pivot] = @is_pivot, [is_swapped] = @is_swapped, use_cents = @use_cents WHERE [id] = @currencyID"; using (OpenCbsCommand c = new OpenCbsCommand(q, t.Connection, t)) { c.AddParam("@currencyID", pCurrency.Id); c.AddParam("@name", pCurrency.Name); c.AddParam("@code", pCurrency.Code); c.AddParam("@is_pivot", pCurrency.IsPivot); c.AddParam("@is_swapped", pCurrency.IsSwapped); c.AddParam("@use_cents", pCurrency.UseCents); c.ExecuteNonQuery(); } }
public static string GetDatabaseBranchCode(string pDatabaseName, SqlConnection pSqlConnection) { string sqlText = string.Format("USE [{0}] SELECT [value] FROM [GeneralParameters] WHERE [key]='BRANCH_CODE'", pDatabaseName); using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection)) { using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) { return(string.Empty); } reader.Read(); return(reader.GetString("value")); } } }
public Guid?GetGuid() { string query = "SELECT [value] FROM TechnicalParameters WHERE [name] = 'GUID'"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(query, conn)) using (OpenCbsReader r = c.ExecuteReader()) { if (!r.Empty) { r.Read(); string temp = r.GetString("value"); return(new Guid(temp)); } } return(null); }
/// <summary> /// Fills General Settings with values from database /// </summary> /// public void FillGeneralSettings() { ApplicationSettings.GetInstance(_user.Md5).DeleteAllParameters(); string sqlText = "SELECT UPPER([key]) as [key], [value] FROM GeneralParameters"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(sqlText, conn)) using (OpenCbsReader r = c.ExecuteReader()) { while (r.Read()) { ApplicationSettings.GetInstance(_user.Md5).AddParameter(r.GetString("key"), r.GetString("value")); } } }
public void AddCreditInsuranceEvent(CreditInsuranceEvent pEvent, SqlTransaction pSqlTransac) { const string q = @" INSERT INTO [dbo].[CreditInsuranceEvents] ([id] ,[commission] ,[principal]) VALUES (@id ,@commission ,@principal)"; using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac)) { SetCreditInsuranceEvent(pEvent, c); c.ExecuteNonQuery(); } }
public static string GetObjectDropScript(string db, string name, SqlConnection conn) { string q = @"SELECT xtype FROM {0}..sysobjects WHERE name = @name"; q = string.Format(q, db); OpenCbsCommand c = new OpenCbsCommand(q, conn); c.AddParam("@name", name); string xtype = c.ExecuteScalar().ToString().Trim(); string xtypeObject = string.Empty; switch (xtype) { case "FN": case "IF": case "TF": xtypeObject = "FUNCTION"; break; case "P": xtypeObject = "PROCEDURE"; break; case "V": xtypeObject = "VIEW"; break; default: Debug.Fail("Cannot be here."); break; } const string retval = @"IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.{0}') AND type = N'{1}' ) DROP {2} [dbo].[{0}]"; return(string.Format(retval, name, xtype, xtypeObject)); }
public int AddAccountingRule(IAccountingRule pRule) { const string sqlText = @"INSERT INTO [AccountingRules] ( [debit_account_number_id], [credit_account_number_id], [rule_type], [booking_direction], [event_type], [event_attribute_id], [order], [description]) VALUES (@debit_account_number_id, @credit_account_number_id, @rule_type, @booking_direction, @event_type, @event_attribute_id, @order, @description) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, conn)) { insert.AddParam("@debit_account_number_id", pRule.DebitAccount.Id); insert.AddParam("@credit_account_number_id", pRule.CreditAccount.Id); insert.AddParam("@rule_type", pRule is ContractAccountingRule ? 'C' : 'F'); insert.AddParam("@booking_direction", (int)pRule.BookingDirection); insert.AddParam("@event_type", pRule.EventType.EventCode); insert.AddParam("@event_attribute_id", pRule.EventAttribute.Id); insert.AddParam("@order", pRule.Order); insert.AddParam("@description", pRule.Description); pRule.Id = Convert.ToInt32(insert.ExecuteScalar()); } } if (pRule is ContractAccountingRule) { AddContractAccountingRule(pRule as ContractAccountingRule); } return(pRule.Id); }
public void Update(TrancheEvent trancheEvent) { string sqlText = @" UPDATE [TrancheEvents] SET [interest_rate] = @InterestRate, [amount] = @Amount, [maturity] = @Maturity, [start_date] = @StartDate, [applied_new_interest] = @applied_new_interest WHERE id = @Id"; using (SqlConnection conn = GetConnection()) using (var cmd = new OpenCbsCommand(sqlText, conn)) { SetTrancheEvent(cmd, trancheEvent); cmd.ExecuteNonQuery(); } }
public void AddParameter(DictionaryEntry entry) { ApplicationSettings.GetInstance(_user.Md5).AddParameter(entry.Key.ToString(), entry.Value); string sqlText = "INSERT INTO [GeneralParameters]([key], [value])" + " VALUES(@name,@value)"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(sqlText, conn)) { c.AddParam("@name", entry.Key.ToString()); if (entry.Value != null) c.AddParam("@value", entry.Value.ToString()); else c.AddParam("@value", null); c.ExecuteNonQuery(); } }
public void AddProvisioningRate(ProvisioningRate pR, SqlTransaction sqlTransac) { const string sqlText = @"INSERT INTO ProvisioningRules( id, number_of_days_min, number_of_days_max, provisioning_value) VALUES( @number, @numberOfDaysMin, @numberOfDaysMax, @provisioningPercentage) SELECT SCOPE_IDENTITY()"; using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac)) { SetProvisioningRate(insert, pR); insert.ExecuteScalar(); } }
public void SelectLoanScales() { LoanScaleTable loanscaleTable = LoanScaleTable.GetInstance(_user); const string sqlText = @"SELECT id, ScaleMin, ScaleMax FROM LoanScale"; using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) return; while (reader.Read()) { loanscaleTable.AddLoanScaleRate(GetLoanScale(reader)); } } } } }
public static int UpdateAccountActive(string pAccountName, bool pActive, SqlConnection pSqlConnection) { const string sqlText = @"UPDATE [Accounts].[dbo].[SqlAccounts] SET active = @active WHERE [account_name] = @accountName"; using (OpenCbsCommand update = new OpenCbsCommand(sqlText, pSqlConnection)) { update.AddParam("@active", pActive); update.AddParam("@accountName", pAccountName); return update.ExecuteNonQuery(); } }
public static void ShrinkDatabase(string pDatabaseName, SqlConnection pSqlConnection) { string database = TechnicalSettings.DatabaseName; string sql1 = String.Format("ALTER DATABASE {0} SET RECOVERY SIMPLE", database); string sql2 = String.Format("ALTER DATABASE {0} SET AUTO_SHRINK ON", database); try { OpenCbsCommand cmd = new OpenCbsCommand(sql1, pSqlConnection); cmd.ExecuteNonQuery(); cmd = new OpenCbsCommand(sql2, pSqlConnection); cmd.ExecuteNonQuery(); string sql = String.Format("DBCC SHRINKDATABASE ({0})", database); cmd = new OpenCbsCommand(sql, pSqlConnection); cmd.ExecuteNonQuery(); } catch (Exception) { } }
public static bool IsOctopusDatabase(string dbName, SqlConnection connection) { string query = string.Format("USE [{0}] SELECT 1 FROM sys.tables WHERE NAME = 'TechnicalParameters'", dbName); using (OpenCbsCommand command = new OpenCbsCommand(query, connection)) return command.ExecuteScalar() != null; }
public static List<SqlDatabaseSettings> GetOpenCbsDatabases(SqlConnection connection) { var result = new List<SqlDatabaseSettings>(); var query = ReadQuery("OpenCBSDatabases.sql"); query = string.Format(query, WindowsIdentity.GetCurrent().Name); using (var command = new OpenCbsCommand(query, connection)) using (var reader = command.ExecuteReader()) { while (reader.Read()) { result.Add(new SqlDatabaseSettings { Name = reader.GetString("Name"), BranchCode = reader.GetString("BranchCode"), DataFileSize = reader.GetInt("DataFileSize"), LogFileSize = reader.GetInt("LogFileSize"), Version = reader.GetString("Version"), }); } } return result; }
public static string GetObjectDropScript(string db, string name, SqlConnection conn) { string q = @"SELECT xtype FROM {0}..sysobjects WHERE name = @name"; q = string.Format(q, db); OpenCbsCommand c = new OpenCbsCommand(q, conn); c.AddParam("@name", name); string xtype = c.ExecuteScalar().ToString().Trim(); string xtypeObject = string.Empty; switch (xtype) { case "FN": case "IF": case "TF": xtypeObject = "FUNCTION"; break; case "P": xtypeObject = "PROCEDURE"; break; case "V": xtypeObject = "VIEW"; break; default: Debug.Fail("Cannot be here."); break; } const string retval = @"IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.{0}') AND type = N'{1}' ) DROP {2} [dbo].[{0}]"; return string.Format(retval, name, xtype, xtypeObject); }
public static string GetObjectCreateScript(string db, string name, SqlConnection conn) { string q = string.Format("{0}..sp_helptext", db); OpenCbsCommand cmd = new OpenCbsCommand(q, conn).AsStoredProcedure(); cmd.AddParam("@objname", name); var buffer = new StringBuilder(2048); using (OpenCbsReader reader = cmd.ExecuteReader()) { if (null == reader) return string.Empty; while (reader.Read()) { buffer.Append(reader.GetString("Text")); } } return buffer.ToString(); }
public static List<SqlAccountsDatabase> GetListDatabasesIntoAccounts(SqlConnection pSqlConnection) { List<SqlAccountsDatabase> databases = new List<SqlAccountsDatabase>(); const string sqlText = @"SELECT * FROM [Accounts].[dbo].[SqlAccounts]"; if (pSqlConnection.State == ConnectionState.Closed) pSqlConnection.Open(); using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection)) { using (OpenCbsReader reader = select.ExecuteReader()) { if (reader == null || reader.Empty) return databases; while (reader.Read()) { databases.Add(new SqlAccountsDatabase { Account = reader.GetString("account_name"), Database = reader.GetString("database_name"), Login = reader.GetString("user_name"), Password = reader.GetString("password"), Active = reader.GetBool("active") }); } } } foreach (SqlAccountsDatabase db in databases) { db.Version = GetDatabaseVersion(db.Database, pSqlConnection); } return databases; }
public static string GetDatabaseVersion(string database, SqlConnection connection) { if (connection == null) { throw new ArgumentException("Both connection and transaction cannot be null."); } if (!IsOctopusDatabase(database, connection)) return string.Empty; string sqlText = string.Format( "USE [{0}] SELECT [value] FROM [TechnicalParameters] WHERE [name]='version'", database); using (OpenCbsCommand select = new OpenCbsCommand(sqlText, connection)) using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) return string.Empty; reader.Read(); return reader.GetString("value"); } }
public static string GetDatabaseSize(string pDatabase, SqlConnection pSqlConnection) { string sql = string.Format("USE [{0}] EXEC sp_spaceused", pDatabase); using (OpenCbsCommand cmd = new OpenCbsCommand(sql, pSqlConnection)) { using (OpenCbsReader reader = cmd.ExecuteReader()) { if (reader.Empty) return string.Empty; if (reader.Read()) { return reader.GetString("database_size"); } return string.Empty; } } }
public static string GetDatabasePath(SqlConnection pSqlConnection) { const string sqlText = @" SELECT physical_name FROM [master].sys.master_files WHERE database_id = 1 AND FILE_ID = 1 "; using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection)) { string dbFilePath = (string)select.ExecuteScalar(); return Path.GetDirectoryName(dbFilePath); } }
public static string GetDatabaseNameForAccount(string pAccountName, SqlConnection pSqlConnection) { const string sqlText = @"SELECT database_name FROM Accounts.dbo.SqlAccounts WHERE account_name = @account"; if (pSqlConnection.State == ConnectionState.Closed) pSqlConnection.Open(); using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection)) { select.AddParam("@account", pAccountName); return (string)select.ExecuteScalar(); } }
public static string GetDatabaseBranchCodeFromBranches(string pDatabaseName, SqlConnection pSqlConnection) { string sqlText = string.Format("USE [{0}] SELECT TOP 1 [code] FROM [Branches] WHERE [deleted] = 0", pDatabaseName); using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection)) { using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) return string.Empty; reader.Read(); string code = reader.GetString("code"); if (string.IsNullOrEmpty(code)) throw new ApplicationException( string.Format("Emty or no branch code found for database: {0}.",pDatabaseName)); return code; } } }
public static string GetDatabaseBranchCode(string pDatabaseName, SqlConnection pSqlConnection) { string sqlText = string.Format("USE [{0}] SELECT [value] FROM [GeneralParameters] WHERE [key]='BRANCH_CODE'", pDatabaseName); using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection)) { using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) return string.Empty; reader.Read(); return reader.GetString("value"); } } }
public static int ChangeAdminPasswordForAccount(string pAccountName, string pPassword, SqlConnection pSqlConnection) { const string sqlText = @"DECLARE @dbName NVARCHAR(50), @query NVARCHAR(200) SELECT @dbName = database_name FROM Accounts.dbo.SqlAccounts WHERE account_name = @account SET @query = 'UPDATE ' + @dbName + '.dbo.Users SET user_pass = ''' + @password + ''' WHERE [user_name] = ''admin''' EXEC (@query)"; if (pSqlConnection.State == ConnectionState.Closed) pSqlConnection.Open(); using (OpenCbsCommand update = new OpenCbsCommand(sqlText, pSqlConnection)) { update.AddParam("@account", pAccountName); update.AddParam("@password", pPassword); return update.ExecuteNonQuery(); } }
/// <summary> /// Executes a SQL script file.<br/> /// Each peace of script delimited by "GO" is executed individually.<br/> /// If any error occurs, the script execution stops and returns an error.<br/> /// Exept after the /** IGNORE ERRORS **/ section where all errors are ignored.<br/> /// Warning : Only /*xxx*/ comments on a single line are supported!<br/> /// </summary> /// <param name="script">Scripy file path</param> /// <param name="database"></param> /// <param name="connection"></param> /// <returns>Script exec result status</returns> public static void ExecuteScript(string script, string database, SqlConnection connection = null, SqlTransaction transaction = null) { if (connection == null && transaction == null) { throw new ArgumentException("Both connection and transaction cannot be null."); } var queries = new List<string> { string.Format("USE [{0}]", database) }; queries.AddRange(_ParseSqlFile(script)); foreach (var query in queries) { var conn = connection ?? transaction.Connection; var command = new OpenCbsCommand(query, conn, transaction) { CommandTimeout = 480 }; command.ExecuteNonQuery(); } }