/// <summary> /// Skapar en tom databas med samma namn som i kopplingssträngen på webbservern. /// </summary> public void CreateDatabaseFromScratch() { string input = Settings.ConnectionString; string regex = "Initial Catalog=([^;]+)"; Match match = Regex.Match(input, regex); string databaseName = ""; if (match.Success) { databaseName = match.Groups[1].Value; } string connectionString = Regex.Replace(Settings.ConnectionString, "Initial Catalog=[^;]+", "Initial Catalog=master", RegexOptions.IgnoreCase); string sql = "create database " + databaseName; EasyBaseSqlConnection connection = new EasyBaseSqlConnection(connectionString); try { connection.Open(); connection.Execute(sql); } finally { connection.Close(); } }
public void DoUpdate(EasyBaseSqlConnection connection) { if (Type == CommandType.SqlString) { foreach (string sql in SqlCommands) { connection.Execute(sql); } } else { // Run the update function Function(connection); } }
private static void ConvertDatabaseToVersion3(EasyBaseSqlConnection connection) { // Ta bort alla ingående saldo-rader och sätt värdet på kontot istället DataTable accountTable = new DataTable(); string sql = "select No from Accounts where BalanceBroughtForwardAmount = 0"; connection.GetTable(accountTable, sql); foreach (DataRow accountRow in accountTable.Rows) { int accountNo = (int)accountRow["No"]; sql = "update Accounts set BalanceBroughtForwardAmount = (select isnull(sum(Amount), 0) from AccountTransactions where Type = 1 and AccountNo = " + accountNo + ") where No = " + accountNo; connection.Update(sql); } // Ta bort alla ingående balans-rader try { sql = "delete from AccountTransactions where Type = 1"; connection.Delete(sql); } catch { } // Sätt rätt typ på tillgångs- och skuldkonton sql = @" update Accounts set Type = ( select case when ( select Accounts.BalanceBroughtForwardAmount + sum(Amount) from AccountTransactions where AccountNo = Accounts.No) < 0 then 1 else 2 end )"; connection.Update(sql); DataTable accountTransactionTable = new DataTable(); // Skapa verifikationer åt alla kontotransaktioner accountTransactionTable = new DataTable(); bool alreadyDone = false; try { sql = "select * from AccountTransactions where SourceAccountTransactionNo is null order by TransactionTime asc, No asc"; connection.GetTable(accountTransactionTable, sql); } catch { // Fältet SourceAccountTransactionNo finns inte, alltså har uppdateringen avbrutits en gång efter hela den här konverteringen... alreadyDone = true; } if (!alreadyDone) { sql = "delete from Verifications"; connection.Delete(sql); int verificationNo = 1; int verificationSerialNumber = 1; int lastYear = 0; connection.Execute("set identity_insert Verifications on"); foreach (DataRow accountTransactionDataRow in accountTransactionTable.Rows) { int accountTransactionNo = (int)accountTransactionDataRow["No"]; DateTime time = (DateTime)accountTransactionDataRow["TransactionTime"]; if (time.Year > lastYear) { verificationSerialNumber = 1; } sql = "insert into Verifications (No, Year, SerialNo, Date) values (" + verificationNo + ", " + time.Year + ", " + verificationSerialNumber + ", '" + time.ToString("yyyy-MM-dd HH:mm:ss") + "')"; connection.Insert(sql); sql = "update AccountTransactions set VerificationNo = " + verificationNo + " where No = " + accountTransactionNo; connection.Update(sql); sql = "select No from CashBookTransactions where SourceAccountTransactionNo = " + accountTransactionNo; DataTable cashBookTransactionDataTable = new DataTable(); connection.GetTable(cashBookTransactionDataTable, sql); foreach (DataRow cashBookTransactionsDataRow in cashBookTransactionDataTable.Rows) { int cashBookTransactionNo = (int)cashBookTransactionsDataRow["No"]; sql = "update CashBookTransactions set VerificationNo = " + verificationNo + " where No = " + cashBookTransactionNo; connection.Update(sql); } sql = "select No from AccountTransactions where SourceAccountTransactionNo = " + accountTransactionNo; DataTable accountTransactionDataTable2 = new DataTable(); connection.GetTable(accountTransactionDataTable2, sql); foreach (DataRow accountTransactionsDataRow in accountTransactionDataTable2.Rows) { int accountTransactionNo2 = (int)accountTransactionsDataRow["No"]; sql = "update accountTransactions set VerificationNo = " + verificationNo + " where No = " + accountTransactionNo2; connection.Update(sql); } verificationNo++; verificationSerialNumber++; lastYear = time.Year; } connection.Execute("set identity_insert Verifications off"); } }