public virtual string[] GenerateSqlCreateField()
{
List <string> sqls = new List <string>();
StringBuilder sql = new StringBuilder();
string sNull = AllowNull ? "null" : "not null";
sql.AppendLine("if not exists (select 1 from INFORMATION_SCHEMA.COLUMNS");
sql.AppendLine(" where TABLE_NAME = '" + TableName + "' and COLUMN_NAME = '" + FieldName + "')");
sql.AppendLine(" begin");
sql.AppendLine(" alter table [" + TableName + "] add [" + FieldName + "] " + FieldType + ";");
sql.AppendLine(" end;");
sqls.Add(sql.ToString());
if (!(this is PrimaryKeyFieldSchema))
{
sql = new StringBuilder();
sql.AppendLine("if not exists (");
sql.AppendLine(" select 1");
sql.AppendLine(" from INFORMATION_SCHEMA.COLUMNS");
sql.AppendLine(" where");
sql.AppendLine(" TABLE_NAME = '" + TableName + "' and");
sql.AppendLine(" COLUMN_NAME = '" + FieldName + "' and");
sql.AppendLine(" COLUMN_DEFAULT is not null)");
sql.AppendLine(" begin");
sql.AppendLine(" alter table [" + TableName + "] add constraint [DF_" + TableName + "_" + FieldName + "] default ((" + DatabaseSchema.GetDefaultValue(FieldType, AllowNull) + ")) for [" + FieldName + "];");
sql.AppendLine(" end;");
sqls.Add(sql.ToString());
if (!AllowNull)
{
sqls.Add("update [" + TableName + "] set [" + FieldName + "] = " + DatabaseSchema.GetDefaultValue(FieldType, AllowNull) + " where [" + FieldName + "] is null;");
}
}
sqls.Add(" alter table [" + TableName + "] alter column [" + FieldName + "] " + FieldType + " " + sNull + ";");
return(sqls.ToArray());
}
public void RestoreBackup2(StreamReader reader, DatabaseSchema database, int backupVersion, long totalRowsInDB)
{
char token = ' ';
char lastToken = ' ';
StringBuilder tableHeader = new StringBuilder();
StringBuilder fieldName = new StringBuilder();
StringBuilder sBackupVersion = new StringBuilder();
StringBuilder sDatabaseVersion = new StringBuilder();
TableSchema currentTable = null;
FieldSchema currentField = null;
bool readingTableHeader = false;
bool readingFieldName = false;
string tableName = "";
int rowCount = 0;
DataTable dataTable = new DataTable();
bool readingFieldData = false;
StringBuilder fieldColumn = new StringBuilder();
bool expectingColumnName = false;
char lastLastToken = ' ';
int fieldsRead = 0;
int tableCount = 0;
while (true)
{
if (!reader.EndOfStream)
{
token = (char)reader.Read();
}
if (token == '[' && lastToken != '\\' && !readingTableHeader && !expectingColumnName && !readingFieldData) // Start of table header
{
readingTableHeader = true;
tableHeader = new StringBuilder();
}
else if (token == '[' && lastToken != '\\' && !readingTableHeader && expectingColumnName) // Start of table header
{
fieldName = new StringBuilder();
readingFieldName = true;
expectingColumnName = false;
}
else if (token == '[' && ((lastToken == '¤' && lastLastToken != '\\' && readingFieldData) || (readingFieldData && currentField.FieldType == "bit")) || reader.EndOfStream) // End of column data
{
readingFieldData = false;
string sData;
// Liten fuling
if (reader.EndOfStream)
{
fieldColumn.Append(token);
}
if (currentField.FieldType != "bit")
{
sData = fieldColumn.ToString().Substring(0, fieldColumn.ToString().Length - 1);
}
else
{
sData = fieldColumn.ToString();
}
string[] array = currentField.SplitDataString(sData);
for (int i = 0; i < rowCount; i++)
{
dataTable.Rows[i][fieldName.ToString()] = array[i];
}
fieldsRead++;
if (fieldsRead == database.GetTable(tableName).DatabaseFields.Count)
{
if (currentTable.PrimaryKey.IsIdentity)
{
Connection.Execute("set identity_insert " + currentTable.TableName + " on;");
}
// Save the table in the database
foreach (DataRow dataRow in dataTable.Rows)
{
string comma = "";
StringBuilder commaSeparatedFieldNames = new StringBuilder();
foreach (FieldSchema f in currentTable.DatabaseFields)
{
commaSeparatedFieldNames.Append(comma + f.FieldName);
comma = ", ";
}
comma = "";
StringBuilder commaSeparatedFieldValues = new StringBuilder();
foreach (FieldSchema f in currentTable.DatabaseFields)
{
commaSeparatedFieldValues.Append(comma + f.DeSerializeData((string)dataRow[f.FieldName]));
comma = ", ";
}
string sql = "insert into [" + currentTable.TableName + "] (" + commaSeparatedFieldNames + ") values (" + commaSeparatedFieldValues + ");";
Connection.Execute(sql);
}
if (currentTable.PrimaryKey.IsIdentity)
{
Connection.Execute("set identity_insert " + currentTable.TableName + " off;");
}
tableCount += 1;
// OnProgress(tableCount, database.Tables.Count());
// Reset most values
fieldsRead = 0;
readingTableHeader = true;
tableHeader = new StringBuilder();
}
else
{
readingFieldName = true;
fieldName = new StringBuilder();
fieldColumn = new StringBuilder();
}
if (reader.EndOfStream)
{
break;
}
}
else if (token == ']' && lastToken != '\\' && readingTableHeader) // End of table header
{
readingTableHeader = false;
string[] array = tableHeader.ToString().Split(";".ToCharArray());
tableName = array[0];
currentTable = database.GetTable(tableName);
rowCount = int.Parse(array[1]);
OnProgress(tableCount, database.Tables.Count(), "Reading table '" + tableName + "'");
// Init data table
dataTable = new DataTable();
foreach (FieldSchema field in currentTable.DatabaseFields)
{
dataTable.Columns.Add(field.FieldName, typeof(string));
}
for (int i = 1; i <= rowCount; i++)
{
DataRow dataRow = dataTable.NewRow();
dataTable.Rows.Add(dataRow);
}
if (rowCount > 0)
{
fieldColumn = new StringBuilder();
expectingColumnName = true;
}
}
else if (token == ']' && lastToken != '\\' && readingFieldName) // End of field header
{
readingFieldName = false;
readingFieldData = true;
currentField = currentTable.GetField(fieldName.ToString());
}
else if (readingTableHeader)
{
tableHeader.Append(token);
}
else if (readingFieldName)
{
fieldName.Append(token);
}
else if (readingFieldData)
{
fieldColumn.Append(token);
}
lastLastToken = lastToken;
lastToken = token;
}
reader.Close();
if (tableCount < database.Tables.Count)
{
tableCount = database.Tables.Count;
OnProgress(tableCount, database.Tables.Count, "Restore completed!");
}
// Uppdatera databasen till senaste versionen
//RunSqlUpdateCommands(CollectSqlUpdateDatabase(false));
}
public void CreateBackup2(string fullPath)
{
// Current version of backup system
int backupVersion = 2;
string sql = "select top 1 [DatabaseVersion] from [EasyBaseSystems]";
int databaseVersion = Convert.ToInt32(Connection.GetScalar(sql));
if (databaseVersion != DatabaseDefinition.DatabaseVersion)
{
throw new DatabaseVersionException("Wrong database version. The database's version is " + databaseVersion + ", but the current version of the application is " + DatabaseDefinition.DatabaseVersion + ". Backup is not possible.");
}
DatabaseSchema savedDatabase = new DatabaseSchema(DatabaseDefinition.GetDatabaseXmlDefinition(DatabaseDefinition.DatabaseVersion));
long totalRowsInDB = 0;
sql = "select (";
string plus = "";
foreach (TableSchema table in savedDatabase.Tables)
{
sql = sql + plus + "(select count(*) from [" + table.TableName + "])";
plus = "+";
}
sql += ")";
totalRowsInDB = Convert.ToInt64(Connection.GetScalar(sql));
StreamWriter streamWriter = new StreamWriter(fullPath);
streamWriter.Write("[" + backupVersion + ";" + CurrentApplication.Name + ";" + DatabaseDefinition.DatabaseVersion + ";" + totalRowsInDB + "]");
foreach (TableSchema table in savedDatabase.Tables)
{
StringBuilder commaSeparatedFields = new StringBuilder();
string comma = "";
foreach (FieldSchema field in table.DatabaseFields)
{
commaSeparatedFields.Append(comma + "[" + field.FieldName + "]");
comma = ", ";
}
sql = "select " + commaSeparatedFields + " from [" + table.TableName + "]";
DataTable dataTable = new DataTable();
Connection.GetTable(dataTable, sql);
streamWriter.Write("[" + table.TableName + ";" + dataTable.Rows.Count + "]");
if (dataTable.Rows.Count > 0)
{
foreach (FieldSchema field in table.DatabaseFields)
{
streamWriter.Write("[" + field.FieldName + "]");
foreach (DataRow dataRow in dataTable.Rows)
{
string data = field.SerializeData(dataRow[field.FieldName]);
streamWriter.Write(data);
}
}
}
}
streamWriter.Flush();
streamWriter.Close();
}
public void RestoreBackup(string fullPath, int toDatabaseVersion)
{
var reader = new StreamReader(fullPath);
var header = new StringBuilder();
int backupVersion = 0;
int databaseVersion = 0;
string databaseApplicationName = "";
long totalRowsInDB = 0;
try {
// Read header
char token = ' ';
while (token != ']')
{
token = (char)reader.Read();
if (token != '[' && token != ']')
{
header.Append(token);
}
}
string[] headerArray = header.ToString().Split(";".ToCharArray());
backupVersion = int.Parse(headerArray[0]);
databaseVersion = 0;
databaseApplicationName = "";
if (backupVersion == 1)
{
databaseVersion = int.Parse(headerArray[1]);
}
else
{
databaseApplicationName = headerArray[1];
databaseVersion = int.Parse(headerArray[2]);
totalRowsInDB = long.Parse(headerArray[3]);
}
}
catch (Exception ex) {
throw new BackupException("Backupfilens huvud har ett felaktigt format.", ex);
}
if (backupVersion >= 2)
{
if (databaseApplicationName != CurrentApplication.Name)
{
throw new BackupException("Den aktuella backupen tillhör inte det här programmet. Backupen är en backup för programmet \"" + databaseApplicationName + "\".");
}
}
// Create database
var database = new DatabaseSchema(DatabaseDefinition.GetDatabaseXmlDefinition(databaseVersion));
CreateDatabaseNoConstraints(databaseVersion);
// Call function that reads all table contents from the backup file.
switch (backupVersion)
{
case 1:
RestoreBackup1(reader, database, backupVersion);
break;
case 2:
RestoreBackup2(reader, database, backupVersion, totalRowsInDB);
break;
default:
throw new NotImplementedException("Backup version " + backupVersion + " is not implemented.");
}
// Efter inläsning, uppdatera databasen till senaste versionen
RunSqlUpdateCommands(CollectSqlUpdateDatabase(false, toDatabaseVersion));
}
public static string[] GetCreateSqlScript()
{
DatabaseSchema database = new DatabaseSchema(GetDatabaseDefinitionXml());
return(database.GenerateSqlCreate());
}
