public static DbConnection connect(sqlservers server, string database = null)
{
var con = new DbConnectionFactory(server, ConnectionStringFactory.CreateConnectionString(server, database)).Create();
con.Open();
return(con);
}
internal static DataTable GetDatabasePrimaryKeysDataSource(sqlservers server, DbConnection con)
{
DataTable data = new DataTable();
DataColumn c0 = new DataColumn("Column");
DataColumn c1 = new DataColumn("Table");
data.Columns.Add(c0);
data.Columns.Add(c1);
List <string> tables = DbDataHelper.getTables(con);
foreach (string table in tables)
{
string table_info = new SqlBuilderFactory(server).Create(con.Database).describeTableSql(table);
using (var r = new DbCommandFactory(con, table_info).Create().ExecuteReader())
{
while (r.Read())
{
if (r.GetInt32(5) >= 1)
{
DataRow row = data.NewRow();
row["Column"] = r.GetString(1);
row["Table"] = table;
data.Rows.Add(row);
}
}
}
}
return(data);
}
public static List <string> getTables(sqlservers server, string database, DbConnection con = null)
{
List <string> data = null;
string sql = "";
try
{
if (con == null)
{
using (con = DB.connect(server, database))
{
sql = new SqlBuilderFactory(con).Create(database).showTablesSql();
data = getStringData(con, sql);
Terminal.MainTerminal.AppendText(sql);
}
return(data);
}
sql = new SqlBuilderFactory(con).Create(database).showTablesSql();
data = getStringData(con, sql);
}
catch (DbException ex)
{
sql = ex.Message;
#if DEBUG
Console.WriteLine(ex.Message);
#endif
}
Terminal.MainTerminal.AppendText(sql);
return(data);
}
internal static string CreateConnectionString(sqlservers server, string database = null)
{
if (server.db_type == (int)DbType.MYSQL || server.db_type == (int)DbType.MARIADB)
{
return(MySqlConnectionStringBuilder.connectionStringBuilder(server, database));
}
else if (server.db_type == (int)DbType.ORACLE)
{
}
else if (server.db_type == (int)DbType.POSTGRES)
{
}
else if (server.db_type == (int)DbType.SQLITE)
{
return(SqliteConnectionStringFactory.ConnectionStringBuilder(server.path, server.password));
}
else if (server.db_type == (int)DbType.SQLSERVER)
{
}
else if (server.db_type == (int)DbType.DB2)
{
}
else if (server.db_type == (int)DbType.FIREBIRD)
{
}
throw new SqlException("Database Not Supported!");
}
//-------------------DELETE METHODS---------------------------------
public void deleteMysqlServer(sqlservers server)
{
using (firedumpdbEntities1 contextdb = new firedumpdbEntities1())
{
contextdb.sqlservers.Remove(server);
contextdb.SaveChanges();
}
}
public sqlservers getMysqlServerById(long id)
{
using (firedumpdbEntities1 contextdb = new firedumpdbEntities1())
{
sqlservers server = contextdb.sqlservers.Find(id);
return(server);
}
}
//return the new id
public int saveMysqlServer(sqlservers server)
{
using (firedumpdbEntities1 contextdb = new firedumpdbEntities1())
{
contextdb.sqlservers.Add(server);
contextdb.SaveChanges();
return((int)server.id);
}
}
//-------------------UPDATE METHODS---------------------------------
public void updateMysqlServer(sqlservers mysqlserver)
{
using (firedumpdbEntities1 context = new firedumpdbEntities1())
{
sqlservers original = context.sqlservers.Find(mysqlserver.id);
context.Entry(original).CurrentValues.SetValues(mysqlserver);
context.SaveChanges();
}
}
public AnalyzeDbForm(sqlservers server, string database)
{
InitializeComponent();
this.server = server;
this.database = database;
setInfoTab();
setTableNamesTab();
setColumnNamesTab();
setIndexesTab();
}
public SqlDbViewerForm(sqlservers server, string database)
{
InitializeComponent();
try {
if (DB.TestConnection(server).wasSuccessful)
{
this.server = server;
this.database = database;
List <string> tables = DbUtils.getTables(this.server, this.database);
MysqlWords.tables = tables;
TreeNode[] nodearray = new TreeNode[tables.Count];
for (int i = 0; i < tables.Count; i++)
{
TreeNode treenode = new TreeNode(tables[i]);
treenode.ImageIndex = 1;
nodearray[i] = treenode;
}
for (int i = 0; i < MysqlWords.tables.Count; i++)
{
MysqlWords.tables[i] = MysqlWords.tables[i].ToUpper();
}
ImageList imagelist = new ImageList();
imagelist.Images.Add(Bitmap.FromFile("resources\\icons\\databaseimage.bmp"));
imagelist.Images.Add(Bitmap.FromFile("resources\\icons\\tableimage.bmp"));
TreeNode rootNode = new TreeNode("database:" + database, nodearray);
rootNode.ImageIndex = 1;
rootNode.ImageIndex = 0;
rootNode.Expand();
treeView1.Nodes.Add(rootNode);
treeView1.ImageIndex = 0;
treeView1.ImageList = imagelist;
}
else
{
MessageBox.Show("Couldent OldMySqlConnect to " + database + " database");
}
}catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
richTextBox1.Text = "";
for (int i = 0; i < limits.Length; i++)
{
toolStripComboBox1.Items.Add(limits[i]);
}
toolStripComboBox1.SelectedIndex = 2;
}
internal static string CreateConnectionString(sqlservers server, string database = null)
{
if (server.db_type == (int)DbTypeEnum.MYSQL || server.db_type == (int)DbTypeEnum.MARIADB)
{
return(MySqlConnectionStringBuilder.connectionStringBuilder(server, database));
}
else if (server.db_type == (int)DbTypeEnum.ORACLE)
{
}
throw new SqlException("Database Not Supported!");
}
public static List <string> getTables(sqlservers server, string database, DbConnection con = null)
{
if (con == null)
{
List <string> data = null;
using (con = DB.connect(server, database))
{
data = getStringData(con, new SqlBuilderFactory(con).Create(database).showTablesSql());
}
return(data);
}
return(getStringData(con, new SqlBuilderFactory(con).Create(database).showTablesSql()));
}
internal static int getTableRowCount(sqlservers server, string database, string tablename, DbConnection con = null)
{
if (con == null)
{
int res = 0;
using (con = DB.connect(server, database))
{
res = getIntSingleResult(con, "SELECT COUNT(*) FROM " + database + "." + tablename);
}
return(res);
}
return(getIntSingleResult(con, "SELECT COUNT(*) FROM " + database + "." + tablename));
}
/**
* The user can give his own connection, that case i dont have to close it.
* Its up to user to handle the connection flow.
* OR if the user dont give any connection i create one and Dispose/Close it after using it to prevent connections leak/and others.
*/
public static List <string> getDatabases(sqlservers server, DbConnection con = null)
{
if (con == null)
{
List <string> data = null;
using (con = DB.connect(server))
{
data = getStringData(con, new SqlBuilderFactory(con).Create(null).getDatabases());
}
return(data);
}
return(getStringData(con, new SqlBuilderFactory(con).Create(null).getDatabases()));
}
internal static List <Table> getTablesInfo(sqlservers server, DbConnection con)
{
var list = new List <Table>();
using (var r = new DbCommandFactory(con, new SqlBuilderFactory(server).Create(con.Database).getAllFieldsFromAllTablesInDb()).Create().ExecuteReader())
{
while (r.Read())
{
list.Add(new Table(r.GetString(0), r.GetString(1), r.GetString(2), r.GetString(3),
r.GetValue(4) != DBNull.Value ? r.GetInt64(4) : default(long)));
}
}
return(list);
}
private sqlservers createMySqlServerInfoCreds()
{
if (!this.performChecks())
{
return(null);
}
sqlservers s = new sqlservers();
s.port = int.Parse(tbPort.Text);
s.host = tbHost.Text;
s.username = tbUsername.Text;
s.password = tbPassword.Text;
return(s);
}
public static ConnectionResultSet TestConnection(sqlservers server, string database = null)
{
try
{
using (var con = new DbConnectionFactory(server, ConnectionStringFactory.CreateConnectionString(server, database)).Create())
{
con.Open();
}
}
catch (Exception ex)
{
return(new ConnectionResultSet(ex));
}
return(new ConnectionResultSet());
}
//returns all fields from all tables
internal static List <Table> getTablesInfo(sqlservers server, DbConnection con)
{
var list = new List <Table>();
try
{
if (sql.Utils.IsDbEmbedded(server.db_type))
{
//case of embedded like sqlite the process to get all fields from all tables is very different.
//first get all tables
List <string> tables = getTables(con);
//and then for every each one get table fields
foreach (string table in tables)
{
string table_info = new SqlBuilderFactory(server).Create(con.Database).describeTableSql(table);
using (var r = new DbCommandFactory(con, table_info).Create().ExecuteReader())
{
while (r.Read())
{
list.Add(new Table(table, r.GetString(1), r.GetString(2), r.GetInt32(3) == 0 ? "YES" : "NO", 0));
}
}
Terminal.MainTerminal.AppendText(table_info);
}
}
else
{
string sql = new SqlBuilderFactory(server).Create(con.Database).getAllFieldsFromAllTablesInDb();
using (var r = new DbCommandFactory(con, sql).Create().ExecuteReader())
{
while (r.Read())
{
list.Add(new Table(r.GetString(0), r.GetString(1), r.GetString(2), r.GetString(3),
r.GetValue(4) != DBNull.Value ? r.GetInt64(4) : default));
}
}
Terminal.MainTerminal.AppendText(sql);
}
}
catch (DbException ex)
{
Terminal.MainTerminal.AppendText(ex.Message);
#if DEBUG
Console.WriteLine(ex.Message);
#endif
}
return(list);
}
private void bSave_Click(object sender, EventArgs e)
{
firedumpdbDataSetTableAdapters.sql_serversTableAdapter adapter = new firedumpdbDataSetTableAdapters.sql_serversTableAdapter();
if (string.IsNullOrEmpty(tbName.Text))
{
MessageBox.Show("Type a name for the new server", "Test Connection", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if ((Int64)adapter.numberOfOccurances(tbName.Text) == 0 || isUpdate)
{
if (!performChecks())
{
return;
}
sqlservers server = createMySqlServerInfoCreds();
String passwd = EncryptionUtils.sEncrypt(server.password);
string path = null;
DbType type = Firedump.core.sql.Utils._convert(((ToolStripItemDbType)this.comboBoxDbTypes.SelectedItem).db_type);
if (Firedump.core.sql.Utils.IsDbEmbedded(type))
{
path = textBoxPath.Text;
}
if (isUpdate)
{
adapter.UpdateMySqlServerById(tbName.Text, server.port, server.host, server.username, passwd, tbDatabase.Text, mysqlserver.id);
}
else
{
adapter.InsertQuery(tbName.Text, server.port, server.host, server.username, passwd, tbDatabase.Text, (int)type, path);
}
int id = Convert.ToInt32((Int64)adapter.GetIdByName(tbName.Text));
onReloadServerData(id);
this.Close();
return;
}
MessageBox.Show("Name " + tbName.Text + " already exists", "Test Connection", MessageBoxButtons.OK, MessageBoxIcon.Warning);
tbPassword.Text = "";
tbName.Text = "";
isUpdate = false;
}
private void bSave_Click(object sender, EventArgs e)
{
firedumpdbDataSetTableAdapters.sql_serversTableAdapter adapter = new firedumpdbDataSetTableAdapters.sql_serversTableAdapter();
if (string.IsNullOrEmpty(tbName.Text))
{
MessageBox.Show("Type a name for the new server", "Test Connection", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if ((Int64)adapter.numberOfOccurances(tbName.Text) == 0 || isUpdate)
{
if (!performChecks())
{
return;
}
sqlservers server = createMySqlServerInfoCreds();
String passwd = EncryptionUtils.sEncrypt(server.password);
if (isUpdate)
{
adapter.UpdateMySqlServerById(tbName.Text, server.port, server.host, server.username, passwd, tbDatabase.Text, mysqlserver.id);
}
else
{
adapter.InsertQuery(tbName.Text, server.port, server.host, server.username, passwd, tbDatabase.Text, 0); //prepei na bei kai
}
//adapter.Insert(tbName.Text, server.port, server.host, server.username, server.password, tbDatabase.Text); //prepei na bei kai database
int id = Convert.ToInt32((Int64)adapter.GetIdByName(tbName.Text));
onReloadServerData(id);
this.Close();
return;
}
MessageBox.Show("Name " + tbName.Text + " already exists", "Test Connection", MessageBoxButtons.OK, MessageBoxIcon.Warning);
tbUsername.Text = "";
tbPassword.Text = "";
tbHost.Text = "";
tbName.Text = "";
tbDatabase.Text = "";
isUpdate = false;
}
private sqlservers createMySqlServerInfoCreds()
{
sqlservers s = new sqlservers();
if (!string.IsNullOrEmpty(tbPort.Text))
{
s.port = int.Parse(tbPort.Text);
}
else
{
s.port = 0;
}
if (string.IsNullOrEmpty(tbHost.Text))
{
s.host = "localhost";
}
else
{
s.host = tbHost.Text;
}
if (string.IsNullOrEmpty(tbUsername.Text))
{
s.username = "******";
}
else
{
s.username = tbUsername.Text;
}
s.password = tbPassword.Text;
s.db_type = ((ToolStripItemDbType)this.comboBoxDbTypes.SelectedItem).db_type;
s.path = textBoxPath.Text;
return(s);
}
public DbConnectionFactory(sqlservers s)
{
this.Server = s;
}
//return the new id
public int saveMysqlServer(sqlservers server)
{
contextdb.sqlservers.Add(server);
contextdb.SaveChanges();
return((int)server.id);
}
public SqlBuilderFactory(sqlservers s)
{
server = s;
}
public sqlservers getMysqlServerById(int id)
{
sqlservers server = contextdb.sqlservers.Find(id);
return(server);
}
public void deleteMysqlServer(sqlservers server)
{
contextdb.sqlservers.Remove(server);
contextdb.SaveChanges();
}
internal static string connectionStringBuilder(sqlservers server, string database = null) => connectionStringBuilder(server.host, server.username, server.password, database, server.port);
public ConnectionEventArgs(DbConnection c, sqlservers s)
{
this.server = s;
this.con = c;
}
public DbConnectionFactory(sqlservers s, string connectionString) : this(s)
{
this.ConnectionString = connectionString;
}
public void TestMysqlserversGetSave()
{
Firedump.tests.TestContext context = new Firedump.tests.TestContext();
FiredumpContextTest service = new FiredumpContextTest(context);
for (int i = 0; i <= 100; i++)
{
sqlservers server = new sqlservers();
server.port = -10000 + i;
server.host = "HOST" + i;
server.name = "N" + i;
server.password = "******" + i;
server.username = "******" + i;
server.id = i;
service.saveMysqlServer(server);
}
List <sqlservers> servers = service.getAllMySqlServers();
Assert.AreEqual(101, servers.Count);
TestMysql_serverDbSet mysqlserverContext = new TestMysql_serverDbSet();
service = new FiredumpContextTest(mysqlserverContext);
for (int i = 0; i <= 100; i++)
{
sqlservers server = new sqlservers();
server.port = -10000 + i;
server.host = "HOST" + i;
server.name = "N" + i;
server.password = "******" + i;
server.username = "******" + i;
server.id = i;
service.saveMysqlServer(server);
}
servers = service.getAllMySqlServers();
Assert.AreEqual(101, servers.Count);
for (int i = 0; i < servers.Count; i++)
{
sqlservers temps = mysqlserverContext.Find((int)servers[i].id);
Assert.AreEqual(servers[i], temps);
Assert.AreEqual(servers[i].id, temps.id);
Assert.AreEqual(servers[i].host, temps.host);
Assert.AreEqual(servers[i].name, temps.name);
Assert.AreEqual(servers[i].password, temps.password);
Assert.AreEqual(servers[i].port, temps.port);
}
sqlservers mysqlserver = service.getAllMySqlServers()[0];
schedules schedule = new schedules();
schedule.hours = 1;
schedule.activated = 0;
schedule.date = new DateTime();
schedule.name = "scheduleName";
schedule.server_id = mysqlserver.id;
service.saveSchedule(schedule);
servers = service.getAllMySqlServers();
List <schedules> schedules = service.getSchedules();
for (int i = 0; i < servers.Count; i++)
{
}
}
