//**************************************************************************************************
public void DeleteGroup(Group group)
{
using (XwDbCommand sql = new XwDbCommand(Config.GetConnectionString(), "Data.SQLite"))
{
sql.AddParameter("ID", group.ID);
sql.ExecuteTX("UPDATE servers SET GroupID=0 WHERE GroupID=@ID", false);
sql.ExecuteTX("DELETE FROM groups WHERE ID=@ID");
}
Refresh();
}
//*************************************************************************************************************
private void UnlinkFolders()
{
//TODO: Move this into the Config
using (XwDbCommand sql = new XwDbCommand(Config.GetConnectionString(), "Data.SQLite"))
{
sql.ExecuteTX($"UPDATE Pins SET LinkTo=NULL WHERE ServerID={server.ID} AND Local=1 AND Path='{LocalPath.Text}'");
sql.ExecuteTX($"UPDATE Pins SET LinkTo=NULL WHERE ServerID={server.ID} AND Local=0 AND Path='{RemotePath.Text}'");
LinkPath.Image = Resources.link_break;
linkTip.SetToolTip(LinkPath, "Link local and remote folders");
LinkPath.Tag = false;
}
}
//***********************************************************************************
static public void SQLITE_FORMAT_QUERY()
{
using (XwDbCommand sql = new XwDbCommand("DBCONN_LITETEST"))
{
if (!sql.TableExists("Tester"))
{
sql.ExecuteTX(@"CREATE TABLE [Tester] (
[id] TEXT,
[client] INTEGER,
[objid] INTEGER,
[objtype] TEXT,
[mediatype] TEXT,
[mediaext] TEXT,
PRIMARY KEY (id,objtype,mediatype,client,objid)
);");
}
/*
* sql.AddParameter("@Name", "%A%");
* sql.ExecuteTX("SELECT * FROM Tester WHERE Name LIKE @Name");
* Console.WriteLine(sql.GetTextCommand());
* while (sql.Read())
* {
* Console.WriteLine(sql.Value("Name").ToString());
* }
*/
}
}
public void ProcessException()
{
string msg = "";
//Test XwDbCommand Exception
try
{
using (XwDbCommand sql = new XwDbCommand("Data Source=:memory:;Version=3;New=True;", "Data.SQLite"))
{
sql.ExecuteTX("SELECT test");
}
}
catch (Exception ex)
{
msg += "\n\n##################################################################################################";
msg += ex.ProcessException();
}
//Test File Exception
try
{
File.ReadAllText("no file");
}
catch (Exception ex)
{
msg += "\n\n##################################################################################################";
msg += ex.ProcessException();
}
Trace.Write(msg); //check output
Assert.IsFalse(condition: msg.IsEmpty());
}
//********************************************************************************************
private async void CheckPin()
{
if (!CheckLick)
{
return;
}
//TODO: Move this into the Config
using (XwDbCommand sql = new XwDbCommand(Config.GetConnectionString(), "Data.SQLite"))
{
sql.ExecuteTX($"SELECT * FROM pins WHERE Local=1 AND path='{CurrentDirectory}' AND ServerID={form.server.ID}");
if (sql.Read())
{
form.LocalPin.Image = Resources.PinDown;
form.localPinTip.SetToolTip(form.LocalPin, "Unpin Folder");
}
else
{
form.LocalPin.Image = Resources.PinUp;
form.localPinTip.SetToolTip(form.LocalPin, "Pin Folder for future use");
}
await form.CheckLink(CurrentDirectory, true);
}
CheckLick = false;
}
//*************************************************************************************************************
private void LinkFolders()
{
int LocalPinID = LocalList.PinFolder();
int RemotePinID = RemoteList.PinFolder();
//TODO: Move this into the Config
using (XwDbCommand sql = new XwDbCommand(Config.GetConnectionString(), "Data.SQLite"))
{
sql.ExecuteTX($"UPDATE Pins SET LinkTo=NULL WHERE LinkTo={LocalPinID}");
sql.ExecuteTX($"UPDATE Pins SET LinkTo=NULL WHERE LinkTo={RemotePinID}");
sql.ExecuteTX($"UPDATE Pins SET LinkTo={LocalPinID} WHERE ID={RemotePinID}");
sql.ExecuteTX($"UPDATE Pins SET LinkTo={RemotePinID} WHERE ID={LocalPinID}");
LinkPath.Image = Resources.link;
linkTip.SetToolTip(LinkPath, "Unlink folders");
LinkPath.Tag = true;
}
}
//*************************************************************************************************************
public void UnpinFolder()
{
using (XwDbCommand sql = new XwDbCommand(Config.GetConnectionString(), "Data.SQLite"))
{
int index = form.RemotePath.FindStringExact(CurrentDirectory);
int ID = ((ListItem)form.RemotePath.Items[index]).ID;
sql.ExecuteTX($"DELETE FROM Pins WHERE ID={ID}");
sql.ExecuteTX($"UPDATE Pins SET LinkTo=NULL WHERE LinkTo={ID}");
LoadPins();
form.RemotePin.Image = Resources.PinUp;
form.remotePinTip.SetToolTip(form.RemotePin, "Pin Folder for future use");
form.LinkPath.Tag = false;
form.LinkPath.Image = Resources.link_break;
form.linkTip.SetToolTip(form.LinkPath, "Link local and remote folders");
}
}
//**************************************************************************************************
public void ExpandGroup(Group group)
{
group.Expanded = true;
using (XwDbCommand sql = new XwDbCommand(Config.GetConnectionString(), "Data.SQLite"))
{
sql.AddParameter("ID", group.ID);
sql.ExecuteTX("UPDATE groups SET Expanded=1 WHERE ID=@ID");
}
}
//**************************************************************************************************
public void SetServerFavourite(Server server)
{
using (XwDbCommand sql = new XwDbCommand(Config.GetConnectionString(), "Data.SQLite"))
{
sql.AddParameter("ID", server.ID);
sql.ExecuteTX("UPDATE servers SET Favorite=1 WHERE id=@ID");
}
Refresh();
}
//**************************************************************************************************
public void DeleteServer(Server server)
{
using (XwDbCommand sql = new XwDbCommand(Config.GetConnectionString(), "Data.SQLite"))
{
sql.AddParameter("ID", server.ID);
sql.ExecuteTX("DELETE FROM servers WHERE ID=@ID");
}
Refresh();
}
//**************************************************************************************************
public void SetValue(string key, string value)
{
values[key] = value;
using (XwDbCommand sql = new XwDbCommand(Config.GetConnectionString(), "Data.SQLite"))
{
sql.AddParameter(@"KEY", key);
sql.AddParameter(@"VALUE", value);
sql.ExecuteTX(@"INSERT OR REPLACE INTO System (SystemKey,SystemValue) VALUES (@KEY,@VALUE)");
}
}
//**************************************************************************************************
private void LoadValues()
{
values.Clear();
using (XwDbCommand sql = new XwDbCommand(Config.GetConnectionString(), "Data.SQLite"))
{
sql.ExecuteTX("SELECT SystemKey, SystemValue FROM System");
while (sql.Read())
{
values.Add(sql.Value("SystemKey").ToString(), sql.Value("SystemValue").ToString());
}
}
}
//***********************************************************************************
static public void MYSQL_FORMAT_QUERY()
{
using (XwDbCommand sql = new XwDbCommand("DBCONN_MYTEST"))
{
sql.AddParameter("@Name", "%A%");
sql.ExecuteTX("SELECT * FROM Tester WHERE Name LIKE @Name");
Console.WriteLine(sql.GetDebugCommand());
while (sql.Read())
{
Console.WriteLine(sql.Value("Name").ToString());
}
}
}
//*************************************************************************************************************
public int PinFolder()
{
using (XwDbCommand sql = new XwDbCommand(Config.GetConnectionString(), "Data.SQLite"))
{
sql.ExecuteTX($"SELECT id FROM Pins WHERE ServerID={form.server.ID} AND Local=0 AND Path='{CurrentDirectory}'");
if (sql.Read())
{
return(sql.Value(0).ToInt32());
}
else
{
sql.ExecuteTX($"INSERT INTO Pins (ServerID, Local, Path) VALUES ({form.server.ID}, 0, '{CurrentDirectory}')");
sql.ExecuteTX($"SELECT MAX(id) FROM Pins");
sql.Read();
int id = sql.Value(0).ToInt32();
LoadPins();
form.RemotePin.Image = Resources.PinDown;
form.remotePinTip.SetToolTip(form.RemotePin, "Unpin Folder");
return(id);
}
}
}
//*************************************************************************************************************
private void LoadPins()
{
ComboBox box = form.RemotePath;
box.Items.Clear();
using (XwDbCommand sql = new XwDbCommand(Config.GetConnectionString(), "Data.SQLite"))
{
sql.ExecuteTX($"SELECT ID,path FROM Pins WHERE Local=0 AND ServerID={form.server.ID} ORDER BY Path");
while (sql.Read())
{
box.Items.Add(new ListItem(sql.Value("ID").ToInt32(), sql.Value("Path").ToString()));
}
}
}
//**************************************************************************************************
private void LoadGroups()
{
groups.Clear();
using (XwDbCommand sql = new XwDbCommand(Config.GetConnectionString(), "Data.SQLite"))
{
sql.ExecuteTX("SELECT id,name,expanded FROM groups ORDER BY name COLLATE NOCASE");
while (sql.Read())
{
Group group = new Group();
group.ID = sql.Value("id").ToInt32();
group.Name = sql.Value("name").ToString();
group.Expanded = sql.Value("expanded").ToBoolean();
groups.Add(group);
}
}
}
//********************************************************************************************
private void Connect()
{
loadingCircle1.Active = true;
loadingCircle1.InnerCircleRadius = 15;
loadingCircle1.OuterCircleRadius = 30;
loadingCircle1.SpokeThickness = 5;
loadingCircle1.Top = (this.Height / 2) + 10;
loadingCircle1.Left = (this.Width / 2) - 40;
loadingCircle1.Visible = true;
loadingCircle1.BringToFront();
SetStatusText("Connecting...");
connection = new XwDbCommand(connectionString, connectionProvider);
//TODO: Connect
connection.ExecuteTX("SELECT 1");
loadingCircle1.Visible = false;
statusLabel.Visible = false;
splitContainer.Visible = true;
}
//*************************************************************************************************************
public async Task CheckLink(string path, bool local)
{
if (SkipCheckLink)
{
SkipCheckLink = false;
return;
}
//TODO: Move this into the Config
using (XwDbCommand sql = new XwDbCommand(Config.GetConnectionString(), "Data.SQLite"))
{
sql.AddParameter("Local", local);
sql.ExecuteTX($@"SELECT l.[path] FROM pins p
INNER JOIN pins l ON l.ID=p.LinkTo
WHERE p.Local=@Local AND p.path='{path}' AND p.ServerID={server.ID}");
if (sql.Read())
{
LinkPath.Tag = true;
LinkPath.Image = Resources.link;
string link = sql.Value(0).ToString();
linkTip.SetToolTip(LinkPath, "Unlink folders");
if (local)
{
await RemoteList.LoadList(link);
}
else
{
LocalList.LoadList(link);
}
}
else
{
LinkPath.Tag = false;
LinkPath.Image = Resources.link_break;
linkTip.SetToolTip(LinkPath, "Link local and remote folders");
}
}
}
//*************************************************************************************************************
private async void CheckPin()
{
using (XwDbCommand sql = new XwDbCommand(Config.GetConnectionString(), "Data.SQLite"))
{
sql.ExecuteTX($"SELECT * FROM pins WHERE Local=0 AND path='{CurrentDirectory}' AND ServerID={form.server.ID}");
if (sql.Read())
{
form.RemotePin.Image = Resources.PinDown;
form.remotePinTip.SetToolTip(form.RemotePin, "Unpin Folder");
if (CheckLick)
{
await form.CheckLink(CurrentDirectory, false);
}
}
else
{
form.RemotePin.Image = Resources.PinUp;
form.remotePinTip.SetToolTip(form.RemotePin, "Pin Folder for future use");
}
}
CheckLick = false;
}
//**************************************************************************************************
//This could be condensed in fewer commands, but would breake compatibility with previous versions
private void UpgradeConfigDB()
{
//Get password ?!?
if (File.Exists(GetDBPath()))
{
using (XwDbCommand tryOne = new XwDbCommand(GetConnectionString(), "Data.SQLite"))
{
try
{
tryOne.ExecuteTX("SELECT * FROM System");
}
catch (Exception ex1)
{
if (ex1.Message.Contains("file is not a database"))
{
AskPassword ask = new AskPassword();
ask.ShowDialog();
Config.MasterPassword = ask.PasswordBox.Text;
using (XwDbCommand tryTwo = new XwDbCommand(GetConnectionString(), "Data.SQLite"))
{
try
{
tryTwo.ExecuteTX("SELECT * FROM System");
}
catch (Exception ex2)
{
if (ex2.Message.Contains("file is not a database"))
{
MessageBox.Show("Bad password or config is corrupted.", "XwRemote", MessageBoxButtons.OK, MessageBoxIcon.Stop);
Environment.Exit(1);
}
}
}
}
}
}
}
using (XwDbCommand sql = new XwDbCommand(GetConnectionString(), "Data.SQLite"))
{
if (!sql.TableExists("System"))
{
sql.ExecuteTX(@"CREATE TABLE [System] (
[SystemKey] TEXT,
[SystemValue] TEXT,
CONSTRAINT [] PRIMARY KEY ([SystemKey]));");
}
if (!sql.TableExists("Groups"))
{
sql.ExecuteTX(@"CREATE TABLE [Groups] (
[ID] INTEGER PRIMARY KEY AUTOINCREMENT,
[Name] TEXT,
[Icon] INTEGER);");
}
if (!sql.TableExists("Servers"))
{
sql.ExecuteTX(@"CREATE TABLE [Servers] (
[ID] INTEGER PRIMARY KEY AUTOINCREMENT,
[Name] TEXT,
[Host] TEXT,
[Port] INTEGER,
[X] INTEGER,
[Y] INTEGER,
[Username] TEXT,
[Password] TEXT,
[Color] INTEGER,
[SendKeys] BOOLEAN DEFAULT (1),
[GroupID] INTEGER CONSTRAINT [Servers_GroupID] REFERENCES [Groups]([ID]),
[Sound] BOOLEAN,
[Drives] BOOLEAN,
[ServerType] INTEGER,
[Favorite] BOOLEAN,
[Console] BOOLEAN DEFAULT (0),
[AutoScale] BOOLEAN DEFAULT (1),
[SSH1] BOOLEAN DEFAULT (0));");
}
if (!sql.TableExists("Pins"))
{
sql.ExecuteTX(@"CREATE TABLE [Pins] (
[ID] INTEGER PRIMARY KEY AUTOINCREMENT,
[ServerID] INTEGER CONSTRAINT [Pins_ServerID] REFERENCES [Servers]([ID]),
[Local] BOOLEAN DEFAULT (0),
[Path] TEXT);");
}
if (!sql.ColumnExists("Pins", "LinkTo"))
{
sql.ExecuteTX("ALTER TABLE Pins ADD COLUMN LinkTo INTEGER;");
}
if (!sql.ColumnExists("Servers", "Passive"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN Passive BOOLEAN;");
sql.ExecuteTX("UPDATE Servers SET Passive=1 WHERE ServerType=3;");
}
if (!sql.ColumnExists("Servers", "Themes"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN Themes BOOLEAN;");
sql.ExecuteTX("UPDATE Servers SET Themes=0");
}
if (!sql.ColumnExists("Servers", "Certificates"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN Certificates BOOLEAN;");
sql.ExecuteTX("UPDATE Servers SET Certificates=0");
}
if (!sql.ColumnExists("Servers", "UseHtmlLogin"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN UseHtmlLogin BOOLEAN;");
sql.ExecuteTX("UPDATE Servers SET UseHtmlLogin=0");
}
if (!sql.ColumnExists("Servers", "HtmlUserBox"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN HtmlUserBox TEXT;");
sql.ExecuteTX("UPDATE Servers SET HtmlUserBox=''");
}
if (!sql.ColumnExists("Servers", "HtmlPassBox"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN HtmlPassBox TEXT;");
sql.ExecuteTX("UPDATE Servers SET HtmlPassBox=''");
}
if (!sql.ColumnExists("Servers", "HtmlLoginBtn"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN HtmlLoginBtn TEXT;");
sql.ExecuteTX("UPDATE Servers SET HtmlLoginBtn=''");
}
if (!sql.ColumnExists("Servers", "TabColor"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN TabColor INTEGER;");
sql.ExecuteTX("UPDATE Servers SET TabColor=-4144960");
}
if (!sql.ColumnExists("Servers", "TabColorTmp"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN TabColorTmp INTEGER;");
sql.ExecuteTX("UPDATE Servers SET TabColor=-4144960 WHERE TabColor=-5192482");
}
if (!sql.ColumnExists("Servers", "Notes"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN Notes TEXT;");
sql.ExecuteTX("UPDATE Servers SET Notes=''");
}
if (!sql.ColumnExists("Servers", "SshTerminal"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN SshTerminal INTEGER;");
sql.ExecuteTX("UPDATE Servers SET SshTerminal=1");
}
if (!sql.ColumnExists("Groups", "Expanded"))
{
sql.ExecuteTX("ALTER TABLE Groups ADD COLUMN Expanded BOOLEAN;");
sql.ExecuteTX("UPDATE Groups SET Expanded=0");
}
if (!sql.ColumnExists("Servers", "Encryption"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN Encryption BOOLEAN;");
sql.ExecuteTX("UPDATE Servers SET Encryption=0");
}
if (!sql.ColumnExists("Servers", "SshKey"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN SshKey TEXT;");
sql.ExecuteTX("UPDATE Servers SET SshKey=''");
}
}
}
//**************************************************************************************************
private void LoadServers()
{
Main.servers.Clear();
using (XwDbCommand sql = new XwDbCommand(Config.GetConnectionString(), "Data.SQLite"))
{
sql.ExecuteTX($@"SELECT
id
,name
,host
,ServerType
,Username
,Password
,port
,Sendkeys
,sound
,drives
,favorite
,groupid
,color
,X
,Y
,Console
,AutoScale
,SSH1
,Passive
,Themes
,Certificates
,UseHtmlLogin
,HtmlUserBox
,HtmlPassBox
,HtmlLoginBtn
,TabColor
,Notes
,SshTerminal
,Encryption
,SshKey
FROM Servers
ORDER BY name
COLLATE NOCASE");
while (sql.Read())
{
ServerType type = (ServerType)sql.Value("ServerType").ToInt32();
var server = Server.GetServerInstance(type);
server.ID = sql.Value("id").ToInt32();
server.Type = type;
server.Name = sql.Value("name").ToString();
server.Host = sql.Value("host").ToString();
server.Username = sql.Value("username").ToString();
server.Password = sql.Value("password").ToString();
server.Port = sql.Value("port").ToInt32();
server.SendKeys = sql.Value("sendkeys").ToBoolean();
server.UseSound = sql.Value("sound").ToBoolean();
server.ConnectDrives = sql.Value("drives").ToBoolean();
server.IsFavorite = sql.Value("favorite").ToBoolean();
server.GroupID = sql.Value("GroupID").ToInt32();
server.Color = sql.Value("Color").ToInt32();
server.Width = sql.Value("X").ToInt32();
server.Height = sql.Value("Y").ToInt32();
server.Console = sql.Value("Console").ToBoolean();
server.AutoScale = sql.Value("AutoScale").ToBoolean();
server.SSH1 = sql.Value("SSH1").ToBoolean();
server.Passive = sql.Value("Passive").ToBoolean();
server.Themes = sql.Value("Themes").ToBoolean();
server.Certificates = sql.Value("Certificates").ToBoolean();
server.UseHtmlLogin = sql.Value("UseHtmlLogin").ToBoolean();
server.HtmlUserBox = sql.Value("HtmlUserBox").ToString();
server.HtmlPassBox = sql.Value("HtmlPassBox").ToString();
server.HtmlLoginBtn = sql.Value("HtmlLoginBtn").ToString();
server.TabColor = sql.Value("TabColor").ToInt32();
server.Notes = sql.Value("Notes").ToString();
server.SshTerminal = sql.Value("SshTerminal").ToInt32();
server.Encryption = sql.Value("Encryption").ToBoolean();
server.SshKey = sql.Value("SshKey").ToString();
Main.servers.Add(server);
}
}
}