public ReturnCode SettingDelete(int IdFolder, string IdSetting)
{
if (FolderNotFound(IdFolder))
{
return(FolderNotFound());
}
ReturnCode code = ReturnCodeFactory.Success($"Setting has been deleted: {IdSetting}");
if (IdSetting.Trim().Length < 1)
{
return(ReturnCodeFactory.Error((int)Errors.SettingNameNotSpecified, "Setting name not specified"));
}
int count = 0;
using (SQLiteConnection connection = GetSqliteConnection())
using (SQLiteCommand command = new SQLiteCommand(connection))
{
count = command.ZzOpenConnection().ZzAdd("@IdFolder", IdFolder).ZzAdd("@IdSetting", IdSetting).ZzGetScalarInteger(DbManager.SqlSettingCount);
if (count != 1)
{
return(ReturnCodeFactory.Error((int)Errors.SettingNotFound, "Setting not found"));
}
count = command.ZzExecuteNonQuery(DbManager.SqlSettingDelete);
if (count != 1)
{
return(ReturnCodeFactory.Error((int)Errors.Unknown, "Error trying to delete a setting"));
}
}
return(code);
}
public ReturnCode FolderRename(int IdFolder, string NameFolder)
{
if (FolderNotFound(IdFolder))
{
return(FolderNotFound());
}
ReturnCode code = ReturnCodeFactory.Success($"Folder has been renamed: {NameFolder}");
int count = 0;
using (SQLiteConnection connection = GetSqliteConnection())
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.ZzOpenConnection().ZzText(DbManager.SqlFolderCountByIdFolder).ZzAdd("@IdFolder", IdFolder).ZzAdd("@NameFolder", NameFolder);
count = command.ZzGetScalarInteger();
if (count != 0)
{
return(ReturnCodeFactory.Error((int)Errors.FolderAlreadyExists, "A folder with the same name already exists"));
}
count = command.ZzExecuteNonQuery(DbManager.SqlFolderRename);
if (count < 1)
{
return(ReturnCodeFactory.Error((int)Errors.Unknown, "Error trying to rename a folder"));
}
}
if (IdFolder == DbManager.IdFolderRoot)
{
RootFolderName = GetRootFolderName();
}
return(code);
}
private void EventLoadDataFromDatabaseFile(bool LoadDataFirstTimeFromThisFile)
{
DataTable table = null; bool Error = false;
ReturnCode ConnectToDatabase = ReturnCodeFactory.Success();
try
{
ConnectToDatabase = DbSettings.ConnectToDatabase(TxDatabaseFile.Text);
if (ConnectToDatabase.Error)
{
throw new DatabaseStructureCheckException(ConnectToDatabase.StringValue);
}
table = DbSettings.GetTableFolders();
}
catch (DatabaseStructureCheckException ex)
{
Error = true;
Ms.Message("Checking database structure", ex.Message).Control(TxDatabaseFile).Error();
}
catch (Exception ex)
{
Error = true;
if (ex.Message.Contains("not a database"))
{
Ms.Message("Failed to read data from the file", "This file is not a database of the specified type").Control(TxDatabaseFile).Error();
}
else
{
Ms.Error("Failed to read data from the file you specified", ex).Control(TxDatabaseFile).Create();
}
}
if (Error == false)
{
try
{
if (TableFolders != null)
{
TableFolders.Clear(); //TableFolders.Columns.Clear();// TableFolders.Dispose();
}
DbSettings.FillTreeView(TvFolders, table);
TvManager.SetFontAndImageForAllNodes();
TableFolders = table;
}
catch (Exception ex)
{
Error = true;
Ms.Error("An error occurred while trying to read the settings structure from the file", ex).Control(TxDatabaseFile).Create();
}
}
if (Error == false)
{
Program.ApplicationSettings.SettingsDatabaseLocation = TxDatabaseFile.Text;
if (LoadDataFirstTimeFromThisFile)
{
EventLoadDataFromFileFirstTime();
}
}
}
public ReturnCode DeleteAllSettingsOfOneFolder(int IdFolder)
{
if (FolderNotFound(IdFolder))
{
return(FolderNotFound());
}
ReturnCode code = ReturnCodeFactory.Success($"Settings has been deleted");
int count = 0;
using (SQLiteConnection connection = GetSqliteConnection())
using (SQLiteCommand command = new SQLiteCommand(connection))
{
count = command.ZzOpenConnection().ZzAdd("@IdFolder", IdFolder).ZzExecuteNonQuery(DbManager.SqlDeleteAllSettingsOfOneFolder);
if (count == 0)
{
code = ReturnCodeFactory.Success($"No any settings were deleted");
}
count = command.ZzGetScalarInteger(DbManager.SqlAllSettingsCount);
if (count != 0)
{
return(ReturnCodeFactory.Error((int)Errors.Unknown, "Error trying to delete all settings of the folder"));
}
}
return(code);
}
public ReturnCode SwapRank(int IdFolder, Setting settingOne, Setting settingTwo)
{
if (FolderNotFound(IdFolder))
{
return(FolderNotFound());
}
ReturnCode code = ReturnCodeFactory.Success($"Setting rank exchange completed");
if ((settingOne == null) || (settingTwo == null))
{
return(ReturnCodeFactory.Error("At least one parameter is null"));
}
int count = 0;
using (SQLiteConnection connection = GetSqliteConnection())
using (SQLiteCommand command = new SQLiteCommand(connection))
{
count = command.ZzOpenConnection().ZzAdd("@IdFolder", IdFolder).ZzAdd("@IdSetting", settingOne.IdSetting).ZzAdd("@Rank", settingTwo.Rank).ZzExecuteNonQuery(DbManager.SqlSetRank);
if (count != 1)
{
return(ReturnCodeFactory.Error("Error trying to change a rank of a setting"));
}
command.Parameters.Clear();
count = command.ZzAdd("@IdFolder", IdFolder).ZzAdd("@IdSetting", settingTwo.IdSetting).ZzAdd("@Rank", settingOne.Rank).ZzExecuteNonQuery(DbManager.SqlSetRank);
if (count != 1)
{
return(ReturnCodeFactory.Error("Error trying to change a rank of a setting"));
}
}
return(code);
}
public ReturnCode SettingUpdate(int IdFolder, string IdSetting, int IdType, string value)
{
if (FolderNotFound(IdFolder))
{
return(FolderNotFound());
}
ReturnCode code = ReturnCodeFactory.Success($"Changes saved: {IdSetting}");
if (IdSetting.Trim().Length < 1)
{
return(ReturnCodeFactory.Error((int)Errors.SettingNameNotSpecified, "Setting name not specified"));
}
int count = 0;
using (SQLiteConnection connection = GetSqliteConnection())
using (SQLiteCommand command = new SQLiteCommand(connection))
{
count = command.ZzOpenConnection().ZzAdd("@IdFolder", IdFolder).ZzAdd("@IdSetting", IdSetting).ZzGetScalarInteger(DbManager.SqlSettingCount);
if (count != 1)
{
return(ReturnCodeFactory.Error((int)Errors.SettingDoesNotExist, "The setting with the specified name does not exist"));
}
count = command.ZzAdd("@IdType", IdType).ZzAdd("@SettingValue", value).ZzExecuteNonQuery(DbManager.SqlSettingUpdate);
if (count != 1)
{
return(ReturnCodeFactory.Error((int)Errors.SettingUpdateFailed, "Error trying to change a value of the setting"));
}
code.StringNote = value;
}
return(code);
}
public ReturnCode SettingCreate(int IdFolder, string IdSetting, int IdType, string value)
{
//IdSetting = IdSetting.RemoveSpecialCharacters();
if (FolderNotFound(IdFolder))
{
return(FolderNotFound());
}
ReturnCode code = ReturnCodeFactory.Success($"New setting has been created: {IdSetting}");
if (IdSetting.Trim().Length < 1)
{
return(ReturnCodeFactory.Error((int)Errors.SettingNameNotSpecified, "Setting name not specified"));
}
int count = 0;
using (SQLiteConnection connection = GetSqliteConnection())
using (SQLiteCommand command = new SQLiteCommand(connection))
{
count = command.ZzOpenConnection().ZzAdd("@IdFolder", IdFolder).ZzAdd("@IdSetting", IdSetting).ZzGetScalarInteger(DbManager.SqlSettingCount);
if (count > 0)
{
return(ReturnCodeFactory.Error((int)Errors.SettingAlreadyExists, "A setting with the same name already exists"));
}
count = command.ZzAdd("@IdType", IdType).ZzAdd("@SettingValue", value).ZzExecuteNonQuery(DbManager.SqlSettingInsert);
if (count != 1)
{
return(ReturnCodeFactory.Error((int)Errors.SettingInsertFailed, "Error trying to add a new setting"));
}
code.StringNote = value;
}
return(code);
}
public ReturnCode SettingRename(int IdFolder, string IdSettingOld, string IdSettingNew)
{
if (FolderNotFound(IdFolder))
{
return(FolderNotFound());
}
ReturnCode code = ReturnCodeFactory.Success($"Setting has been renamed: {IdSettingNew}");
if (IdSettingNew.Trim().Length < 1)
{
return(ReturnCodeFactory.Error((int)Errors.SettingNameNotSpecified, "New setting name not specified"));
}
int count = 0;
using (SQLiteConnection connection = GetSqliteConnection())
using (SQLiteCommand command = new SQLiteCommand(connection))
{
count = command.ZzOpenConnection().ZzAdd("@IdFolder", IdFolder).ZzAdd("@IdSetting", IdSettingNew).ZzGetScalarInteger(DbManager.SqlSettingCount);
if (count > 0)
{
return(ReturnCodeFactory.Error((int)Errors.SettingAlreadyExists, "A setting with the same name already exists"));
}
command.Parameters.Clear();
count = command.ZzAdd("@IdFolder", IdFolder).ZzAdd("@IdSettingOld", IdSettingOld).ZzAdd("@IdSettingNew", IdSettingNew).ZzExecuteNonQuery(DbManager.SqlSettingRename);
if (count != 1)
{
return(ReturnCodeFactory.Error((int)Errors.Unknown, "Error trying to rename a setting"));
}
}
return(code);
}
public ReturnCode FolderInsert(int IdParent, string NameFolder)
{
if (FolderNotFound(IdParent))
{
return(FolderNotFound());
}
ReturnCode code = ReturnCodeFactory.Success($"New folder has been created: {NameFolder}");
int IdNewFolder = -1;
using (SQLiteConnection connection = GetSqliteConnection())
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.ZzOpenConnection().ZzText(DbManager.SqlFolderCountByIdParent).ZzAdd("@IdParent", IdParent).ZzAdd("@NameFolder", NameFolder);
int count = command.ZzGetScalarInteger();
if (count != 0)
{
return(ReturnCodeFactory.Error((int)Errors.FolderAlreadyExists, "A folder with the same name already exists"));
}
count = command.ZzExecuteNonQuery(DbManager.SqlFolderInsert);
if (count != 1)
{
return(ReturnCodeFactory.Error((int)Errors.Unknown, "Error trying to add a new folder"));
}
IdNewFolder = command.ZzGetScalarInteger(DbManager.SqlGetIdFolder);
if (IdNewFolder > 0)
{
code.IdObject = IdNewFolder;
}
else
{
return(ReturnCodeFactory.Error((int)Errors.Unknown, "Error trying to add a new folder"));
}
}
return(code);
}
public ReturnCode CheckIdSettingCharacters(string IdSetting)
{
if (IdSetting.Trim() == string.Empty)
{
return(ReturnCodeFactory.Error("Incorrect name of the setting."));
}
string CorrectedIdSetting = IdSetting.RemoveSpecialCharacters();
if (CorrectedIdSetting != IdSetting)
{
return(ReturnCodeFactory.Error($"Special characters are not allowed in the setting name. Allowed name may be = {CorrectedIdSetting}"));
}
return(ReturnCodeFactory.Success());
}
private ReturnCode CheckDatabaseStructure()
{
ReturnCode code = ReturnCodeFactory.Success($"Database structure is ok");
int CheckObjectCount = 4;
using (SQLiteConnection connection = GetSqliteConnection())
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.ZzOpenConnection().ZzText(DbManager.SqlCheckDatabaseStructure);
if (command.ZzGetScalarInteger() != CheckObjectCount)
{
code = ReturnCodeFactory.Error(ReturnCodeFactory.NcError, "The database structure is not compliant with the standard. When working with the database errors may occur.");
}
}
return(code);
}
private ReturnCode SettingCreateOrUpdate(string FolderPath, string IdSetting, TypeSetting type, string value)
{
int IdFolder = GetIdFolder(FolderPath);
if (FolderNotFound(IdFolder))
{
return(FolderNotFound());
}
int IdType = (int)type;
//IdSetting = IdSetting.RemoveSpecialCharacters();
ReturnCode code = ReturnCodeFactory.Success($"New setting has been created: {IdSetting}");
if (IdSetting.Trim().Length < 1)
{
return(ReturnCodeFactory.Error((int)Errors.SettingNameNotSpecified, "Setting name not specified"));
}
int count = 0;
using (SQLiteConnection connection = GetSqliteConnection())
using (SQLiteCommand command = new SQLiteCommand(connection))
{
count = command.ZzOpenConnection().ZzAdd("@IdFolder", IdFolder).ZzAdd("@IdSetting", IdSetting).ZzGetScalarInteger(DbManager.SqlSettingCount);
if (count > 0) // UPDATE value of existing setting //
{
code = ReturnCodeFactory.Success($"Changes saved: {IdSetting}");
count = command.ZzAdd("@IdType", IdType).ZzAdd("@SettingValue", value).ZzExecuteNonQuery(DbManager.SqlSettingUpdate);
if (count != 1)
{
return(ReturnCodeFactory.Error((int)Errors.SettingUpdateFailed, "Error trying to change a value of the setting"));
}
code.StringNote = value;
}
else // ADD a new value of setting //
{
count = command.ZzAdd("@IdType", IdType).ZzAdd("@SettingValue", value).ZzExecuteNonQuery(DbManager.SqlSettingInsert);
if (count != 1)
{
return(ReturnCodeFactory.Error((int)Errors.SettingInsertFailed, "Error trying to add a new setting"));
}
code.StringNote = value;
}
}
return(code);
}
public ReturnCode ConnectToDatabase(string PathToDatabase = "")
{
if (PathToDatabase == string.Empty)
{
PathToDatabase = Path.Combine(Path.Combine(Application.StartupPath, DefaultFolder), DefaultFileName);
}
//MessageBox.Show(PathToDatabase);
SetPathToDatabase(PathToDatabase);
ReturnCode code;
try
{
code = CheckDatabaseStructure();
}
catch
{
return(ReturnCodeFactory.Error("The file you specified is not a SQLite database."));
}
if (code.Error)
{
return(code);
}
if (TableTypes != null)
{
TableTypes.Clear();
}
try
{
TableTypes = GetTable(DbManager.TnTypes);
RootFolderName = GetRootFolderName();
}
catch (Exception ex)
{
RootFolderName = string.Empty;
return(ReturnCodeFactory.Error("Could not read data from the database file.", ex.Message));
}
return(ReturnCodeFactory.Success());
}
public ReturnCode FolderDelete(int IdFolder, string NameFolder)
{
if (FolderNotFound(IdFolder))
{
return(FolderNotFound());
}
ReturnCode code = ReturnCodeFactory.Success($"Folder has been deleted: {NameFolder}");
int count = 0;
using (SQLiteConnection connection = GetSqliteConnection())
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.ZzOpenConnection().ZzText(DbManager.SqlFolderCountSimple).ZzAdd("@IdFolder", IdFolder);
count = command.ZzGetScalarInteger();
if (count != 1)
{
return(ReturnCodeFactory.Error((int)Errors.FolderNotFound, "The folder you specified was not found"));
}
count = command.ZzGetScalarInteger(DbManager.SqlCountChildFolder);
if (count != 0)
{
return(ReturnCodeFactory.Error((int)Errors.FolderHasChildFolder, "You cannot delete a folder that has other folders inside"));
}
count = command.ZzGetScalarInteger(DbManager.SqlCountChildSettings);
if (count != 0)
{
return(ReturnCodeFactory.Error((int)Errors.FolderHasSettings, "You cannot delete a folder that contains settings"));
}
count = command.ZzExecuteNonQuery(DbManager.SqlFolderDelete);
if (count == 0)
{
return(ReturnCodeFactory.Error((int)Errors.Unknown, "Error trying to delete a folder"));
}
}
return(code);
}
public static ReceivedValueText Success(string value) => new ReceivedValueText(ReturnCodeFactory.Success(), value);
public static ReceivedValueBoolean Success(bool value) => new ReceivedValueBoolean(ReturnCodeFactory.Success(), value);
public static ReceivedValueInteger64 Success(long value) => new ReceivedValueInteger64(ReturnCodeFactory.Success(), value);
public ReturnCode CreateNewDatabase(string FileName)
{
string sql = string.Empty;
ReturnCode code = ReturnCodeFactory.Success();
try
{
SQLiteConnection.CreateFile(FileName);
using (SQLiteConnection connection = new SQLiteConnection($"Data Source={FileName};Version=3;"))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(connection))
{
sql = @"CREATE TABLE FOLDERS
(
IdFolder INTEGER CONSTRAINT PK_FOLDERS PRIMARY KEY ON CONFLICT ROLLBACK AUTOINCREMENT NOT NULL ON CONFLICT ROLLBACK,
IdParent INTEGER NOT NULL ON CONFLICT ROLLBACK CONSTRAINT FK_FOLDERS REFERENCES FOLDERS (IdFolder)
ON DELETE RESTRICT ON UPDATE CASCADE, NameFolder TEXT (255) NOT NULL
);";
command.ZzExecuteNonQuery(sql);
sql = $@"
INSERT INTO FOLDERS VALUES({IdFolderRoot}, {IdFolderRoot}, 'application');
INSERT INTO FOLDERS VALUES(1, {IdFolderRoot}, 'local_database');";
command.ZzExecuteNonQuery(sql);
sql = @"
CREATE TABLE TYPES (IdType INTEGER PRIMARY KEY NOT NULL, NameType TEXT (255) NOT NULL, Note TEXT (4000));";
command.ZzExecuteNonQuery(sql);
sql = @"
INSERT INTO TYPES VALUES(0,'unknown',NULL);
INSERT INTO TYPES VALUES(1,'boolean',NULL);
INSERT INTO TYPES VALUES(2,'datetime',NULL);
INSERT INTO TYPES VALUES(3,'integer',NULL);
INSERT INTO TYPES VALUES(4,'text',NULL);
INSERT INTO TYPES VALUES(5,'password',NULL);
INSERT INTO TYPES VALUES(6,'folder name',NULL);
INSERT INTO TYPES VALUES(7,'file name',NULL);
INSERT INTO TYPES VALUES(8,'font',NULL);
INSERT INTO TYPES VALUES(9,'color',NULL);";
command.ZzExecuteNonQuery(sql);
sql = @"CREATE TABLE SETTINGS (
IdFolder INTEGER NOT NULL,
IdSetting TEXT (255) NOT NULL,
IdType INTEGER NOT NULL,
SettingValue TEXT,
Rank INTEGER NOT NULL,
CONSTRAINT PK_SETTINGS PRIMARY KEY (IdFolder, IdSetting)
ON CONFLICT ROLLBACK, CONSTRAINT FK_SETTINGS_FOLDERS FOREIGN KEY (IdFolder)
REFERENCES FOLDERS (IdFolder) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT FK_SETTINGS_TYPES FOREIGN KEY (IdType) REFERENCES TYPES (IdType)
ON DELETE RESTRICT ON UPDATE CASCADE);";
command.ZzExecuteNonQuery(sql);
sql = @"DELETE FROM sqlite_sequence;";
command.ZzExecuteNonQuery(sql);
sql = @"INSERT INTO sqlite_sequence VALUES('FOLDERS',2);";
command.ZzExecuteNonQuery(sql);
sql = @"CREATE UNIQUE INDEX UX_NameFolder ON FOLDERS(IdParent, NameFolder);";
command.ZzExecuteNonQuery(sql);
sql = @"CREATE VIEW V_SETTINGS AS
SELECT A.IdFolder,
A.IdSetting,
A.IdType,
B.NameType,
A.SettingValue,
A.Rank,
CASE WHEN A.IdType = 1 THEN A.SettingValue ELSE '' END AS BooleanValue
FROM SETTINGS A
LEFT JOIN TYPES B
ON A.IdType = B.IdType
ORDER BY A.Rank, A.IdSetting;";
command.ZzExecuteNonQuery(sql);
connection.Close();
}
}
}
catch (Exception ex)
{
code = ReturnCodeFactory.Error(ex.Message, "Could not create a new database");
}
return(code);
}
public static ReceivedValueInteger32 Success(int value) => new ReceivedValueInteger32(ReturnCodeFactory.Success(), value);