public static string[] GetMySQLDatabases(ConnectionStringHelper helper)
{
string serverName = helper.ServerName;
string userName = helper.UserName;
string password = helper.Password;
bool trustedConnection = helper.UseIntegratedSecurity;
UniConnection conn;
//if (trustedConnection)
// conn = new UniConnection(string.Format("Provider=MySQL;host=server;user=root;password=root;database=myDB", serverName));
//else
//conn = new UniConnection(string.Format("Provider=MySQL;host={0};user={1};password={2};database=myDB", serverName, userName, password));
conn = new UniConnection(string.Format("Provider=MySQL;direct=true;host={0};user={1};password={2};", serverName, userName, password));
List<string> databaseNames = new List<string>();
try
{
conn.Open();
DataTable table1 = conn.GetSchema("Databases");
foreach (DataRow row in table1.Rows)
{
string dbName = row[0].ToString();
databaseNames.Add(dbName);
}
}
finally
{
conn.Close();
}
return databaseNames.ToArray();
}
public static string[] GetFirebirdDatabases(ConnectionStringHelper helper)
{
string serverName = helper.ServerName;
string userName = helper.UserName;
string password = helper.Password;
int port = helper.Port;
bool trustedConnection = helper.UseIntegratedSecurity;
UniConnection conn = new UniConnection(string.Format("Provider=Firebird;direct=true;Server={0};User={1};Password={2};Port={3};", serverName, userName, password, port));
List<string> databaseNames = new List<string>();
try
{
conn.Open();
DataTable table1 = conn.GetSchema("Databases");
foreach (DataRow row in table1.Rows)
{
string dbName = row[0].ToString();
databaseNames.Add(dbName);
}
}
finally
{
conn.Close();
}
return databaseNames.ToArray();
}
public static string[] GetPostgreSQLDatabases(ConnectionStringHelper helper)
{
string serverName = helper.ServerName;
string userName = helper.UserName;
string password = helper.Password;
bool trustedConnection = helper.UseIntegratedSecurity;
int port = helper.Port;
UniConnection conn = new UniConnection(string.Format("Provider=PostgreSQL;host={0};port={1};user={2};password={3};initial schema=Public;", serverName, port, userName, password));
List<string> databaseNames = new List<string>();
try
{
conn.Open();
using (UniCommand sqlCommand = new UniCommand(@"
SELECT datname
FROM pg_catalog.pg_database
where not datistemplate
ORDER BY datname
", conn))
{
using (UniDataReader dr = sqlCommand.ExecuteReader())
{
while (dr.Read())
databaseNames.Add(dr.GetString(0));
}
}
}
finally
{
conn.Close();
}
return databaseNames.ToArray();
}
/// <summary>
/// Creates a new database on the server, dropping the existing one first if it exists.
/// </summary>
public static void CreateDatabaseFiles()
{
ConnectionStringHelper masterConnStringHelper = new ConnectionStringHelper()
{
CurrentDbType = DatabaseTypes.SQLServer2005,
DatabaseName = "master",
ServerName = ".",
UseFileName = false,
UseIntegratedSecurity = true
};
SQLServer2005DatabaseConnector database = new SQLServer2005DatabaseConnector(masterConnStringHelper);
database.Open();
string sql = File.ReadAllText("CreateDatabase - SQL Server.sql");
sql = sql.Replace("[LOCATION]", Path.GetTempPath());
database.RunNonQuerySQL(sql);
database.Close();
}
public static string[] GetSQlServer2005Databases(ConnectionStringHelper helper)
{
string serverName = helper.ServerName;
string userName = helper.UserName;
string password = helper.Password;
bool trustedConnection = helper.UseIntegratedSecurity;
SqlCommand cmd = new SqlCommand();
SqlConnection conn;
if (trustedConnection)
{
conn = new SqlConnection(string.Format("Server={0};Database=master;Trusted_Connection=True", serverName));
}
else
{
conn = new SqlConnection(string.Format("Server={0};Database=master;User ID={1};Password={2};Trusted_Connection=False", serverName, userName, password));
}
List<string> databaseNames = new List<string>();
try
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "SELECT name FROM master..sysdatabases";
cmd.CommandType = CommandType.Text;
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
while (rdr != null && rdr.Read())
{
databaseNames.Add(rdr.GetString(0));
}
}
finally
{
conn.Close();
}
return databaseNames.ToArray();
}
public SQLServerExpressDatabaseConnector(ConnectionStringHelper connectionStringHelper, string databaseName)
{
try
{
DatabaseName = databaseName;
string connectionString = connectionStringHelper.GetConnectionStringSqlClient(DatabaseTypes.SQLServerExpress);
log.InfoFormat("Opening Database using connection string: {0}", connectionString);
sqlConnection = new SqlConnection(connectionString);
if (connectionStringHelper.UseFileName)
connectionStringHelper.DatabaseName = System.IO.Path.GetFileNameWithoutExtension(connectionStringHelper.FileName);
ConnectionInformation = connectionStringHelper;
}
catch (Exception e)
{
throw new DatabaseLoaderException("Could not open database connection. See inner exception for details.", e);
}
}
public static string[] GetOracleDatabases(ConnectionStringHelper helper)
{
string serverName = helper.ServerName;
string userName = helper.UserName;
string password = helper.Password;
bool trustedConnection = helper.UseIntegratedSecurity;
int port = helper.Port;
UniConnection conn;
//if (trustedConnection)
// conn = new UniConnection(string.Format("Provider=Oracle;host=server;user=root;password=root;database=myDB", serverName));
//else
//conn = new UniConnection(string.Format("Provider=Oracle;host={0};user={1};password={2};database=myDB", serverName, userName, password));
conn = new UniConnection(string.Format("Provider=Oracle;Direct=true;data source={0};user={1};password={2};port={3};", serverName, userName, password, port));
List<string> databaseNames = new List<string>();
try
{
conn.Open();
using (UniCommand sqlCommand = new UniCommand("SELECT DISTINCT OWNER FROM all_tables ORDER BY OWNER", conn))
{
using (UniDataReader dr = sqlCommand.ExecuteReader())
{
while (dr.Read())
databaseNames.Add(dr.GetString(0));
}
}
}
finally
{
conn.Close();
}
return databaseNames.ToArray();
}
public MySQLDatabaseConnector(ConnectionStringHelper connectionStringHelper)
{
try
{
string connectionString = connectionStringHelper.GetConnectionStringSqlClient(DatabaseTypes.MySQL);
log.InfoFormat("Opening Database using connection string: {0}", connectionString);
uniConnection = new UniConnection(connectionString);
DatabaseName = uniConnection.Database;
ConnectionInformation = connectionStringHelper;
}
catch (Exception e)
{
throw new DatabaseLoaderException("Could not open database connection. See inner exception for details.", e);
}
}
private MySQLDatabaseConnector(string connectionString)
{
try
{
ConnectionInformation = new ConnectionStringHelper(connectionString, DatabaseTypes.MySQL);
uniConnection = new UniConnection(ConnectionInformation.GetConnectionStringSqlClient());
DatabaseName = uniConnection.Database;
}
catch (Exception e)
{
throw new DatabaseLoaderException("Could not open database connection. See inner exception for details.", e);
}
}
public static string[] GetPostgreSQLSchemas(ConnectionStringHelper helper)
{
string serverName = helper.ServerName;
string userName = helper.UserName;
string password = helper.Password;
bool trustedConnection = helper.UseIntegratedSecurity;
int port = helper.Port;
string database = helper.DatabaseName;
UniConnection conn = new UniConnection(string.Format("Provider=PostgreSQL;host={0};port={1};user={2};password={3};initial schema=Public;database={4};", serverName, port, userName, password, database));
List<string> databaseNames = new List<string>();
try
{
conn.Open();
using (UniCommand sqlCommand = new UniCommand(@"
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name not like 'pg_catalog%' and
schema_name not like 'pg_toast%' and
schema_name not like 'pg_temp%' and
schema_name not like 'information_schema'", conn))
{
using (UniDataReader dr = sqlCommand.ExecuteReader())
{
while (dr.Read())
databaseNames.Add(dr.GetString(0));
}
}
}
finally
{
conn.Close();
}
return databaseNames.ToArray();
}
private ConnectionStringHelper LoadGenericDatabaseData(XmlNode node, DatabaseTypes databaseType)
{
NodeProcessor proc = new NodeProcessor(node);
var connectionHelper = new ConnectionStringHelper();
if (string.IsNullOrEmpty(connectionHelper.ServerName))
{
switch (databaseType)
{
case DatabaseTypes.SQLCE:
//case DatabaseTypes.SQLServer2000:
case DatabaseTypes.SQLServer2005:
connectionHelper.ServerName = Environment.MachineName;
break;
case DatabaseTypes.SQLServerExpress:
connectionHelper.ServerName = Environment.MachineName + "\\SQLEXPRESS";
break;
case DatabaseTypes.MySQL:
connectionHelper.ServerName = "localhost";
break;
case DatabaseTypes.Oracle:
connectionHelper.ServerName = "localhost";
break;
case DatabaseTypes.PostgreSQL:
connectionHelper.ServerName = "localhost";
break;
case DatabaseTypes.Firebird:
connectionHelper.ServerName = "localhost";
break;
case DatabaseTypes.SQLite:
connectionHelper.ServerName = Environment.MachineName;
break;
default:
throw new NotImplementedException("Database type not handled yet: " + databaseType.ToString());
}
}
connectionHelper.CurrentDbType = databaseType;
if (proc.Exists("ServerName"))
connectionHelper.ServerName = proc.GetString("ServerName");
if (proc.Exists("DatabaseName"))
{
connectionHelper.DatabaseName = proc.GetString("DatabaseName");
connectionHelper.UseFileName = false;
}
if (proc.Exists("FileName"))
{
connectionHelper.FileName = proc.GetString("FileName");
connectionHelper.UseFileName = true;
}
if (proc.Exists("UseIntegratedSecurity"))
connectionHelper.UseIntegratedSecurity = proc.GetBool("UseIntegratedSecurity");
if (proc.Exists("UserName"))
connectionHelper.UserName = proc.GetString("UserName");
if (proc.Exists("Password"))
{
string password = "";
try
{
password = proc.GetString("Password").Decrypt();
}
catch
{
// Do nothing
password = "";
}
connectionHelper.Password = password;
}
if (proc.Exists("Port"))
connectionHelper.Port = proc.GetInt("Port");
if (proc.Exists("ServiceName"))
connectionHelper.ServiceName = proc.GetString("ServiceName");
if (proc.Exists("SchemaName"))
connectionHelper.ServiceName = proc.GetString("SchemaName");
if (proc.Exists("UseDirectConnection"))
connectionHelper.UseDirectConnection = proc.GetBool("UseDirectConnection");
else
connectionHelper.UseDirectConnection = false;
return connectionHelper;
}
public IEnumerable<string> GetSchemaNamesForServer(ConnectionStringHelper serverInfo)
{
if (serverInfo.CurrentDbType == DatabaseTypes.PostgreSQL)
return PostgreSQLHelper.GetPostgreSQLSchemas(serverInfo);
throw new NotImplementedException("This database type not handled yet in GetSchemaNamesForServer(): " + serverInfo.CurrentDbType.ToString());
//return new List<string>();
}
public IEnumerable<string> GetDatabaseNamesForServer(ConnectionStringHelper serverInfo)
{
//if (serverInfo.CurrentDbType == DatabaseTypes.SQLServer2000)
// return SqlServer2005Helper.GetSQlServer2005Databases(serverInfo);
if (serverInfo.CurrentDbType == DatabaseTypes.SQLServer2005)
return SqlServer2005Helper.GetSQlServer2005Databases(serverInfo);
if (serverInfo.CurrentDbType == DatabaseTypes.MySQL)
return MySQLHelper.GetMySQLDatabases(serverInfo);
if (serverInfo.CurrentDbType == DatabaseTypes.Oracle)
return OracleHelper.GetOracleDatabases(serverInfo);
if (serverInfo.CurrentDbType == DatabaseTypes.PostgreSQL)
return PostgreSQLHelper.GetPostgreSQLDatabases(serverInfo);
if (serverInfo.CurrentDbType == DatabaseTypes.SQLServerExpress)
return SqlServerExpressHelper.GetSqlServerExpressDatabases(serverInfo);
if (serverInfo.CurrentDbType == DatabaseTypes.SQLite)
return SQLiteHelper.GetSQLiteDatabases(serverInfo);
throw new NotImplementedException("This database type not handled yet in GetDatabaseNamesForServer(): " + serverInfo.CurrentDbType.ToString());
//return new List<string>();
}
private SQLServerExpressDatabaseConnector(string connectionString, string databaseName)
{
try
{
DatabaseName = databaseName;
sqlConnection = new SqlConnection(connectionString);
ConnectionInformation = new ConnectionStringHelper(connectionString, DatabaseTypes.SQLServerExpress);
}
catch (Exception e)
{
throw new DatabaseLoaderException("Could not open database connection. See inner exception for details.", e);
}
}
public SQLiteDatabaseConnector(ConnectionStringHelper connectionStringHelper)
{
try
{
Init();
string connectionString = connectionStringHelper.GetConnectionStringSqlClient(DatabaseTypes.SQLite);
DatabaseName = connectionStringHelper.FileName;
ConnectionInformation = connectionStringHelper;
}
catch (Exception e)
{
throw new DatabaseLoaderException("Could not open database connection. See inner exception for details.", e);
}
}
public ConnectionStringHelper GetHelper()
{
if (InvokeRequired)
{
Invoke(new MethodInvoker(() => GetHelper()));
return __helper;
}
__helper = new ConnectionStringHelper
{
DatabaseName = comboBoxDatabases.Text,
CurrentDbType = SelectedDatabaseType,
FileName = textBoxFilename.Text,
Password = textBoxPassword.Text,
ServerName = comboBoxServers.Text,
UseFileName = optDBFile.Checked,
UseIntegratedSecurity = checkBoxUseIntegratedSecurity.Checked,
UserName = textBoxUserName.Text,
Port = string.IsNullOrEmpty(textBoxPort.Text) ? int.MinValue : int.Parse(textBoxPort.Text),
ServiceName = textBoxServiceName.Text,
Direct = checkBoxDirect.Checked
};
return __helper;
}
