/// <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");
}
}
public StandardDataLayer()
{
Connection = new EasyBaseSqlConnection(Settings.ConnectionString);
}