389 lines
17 KiB
C#
389 lines
17 KiB
C#
|
using System;
|
|||
|
|
using System.Collections;
|
|||
|
|
using System.ComponentModel;
|
|||
|
|
using System.Data;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Web;
|
|||
|
|
using System.Web.Services;
|
|||
|
|
using System.Web.Services.Protocols;
|
|||
|
|
using System.Xml.Linq;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using MySql.Data.MySqlClient;
|
|||
|
|
using DBToolControls;
|
|||
|
|
using System.IO;
|
|||
|
|
|
|||
|
|
namespace DBAdminService {
|
|||
|
|
/// <summary>
|
|||
|
|
/// Zusammenfassungsbeschreibung für MySqlService
|
|||
|
|
/// </summary>
|
|||
|
|
[WebService(Namespace = "http://tempuri.org")]
|
|||
|
|
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
|
|||
|
|
[ToolboxItem(false)]
|
|||
|
|
// Um das Aufrufen dieses Webdiensts aus einem Skript mit ASP.NET-AJAX zuzulassen, heben Sie die Auskommentierung der folgenden Zeile auf.
|
|||
|
|
// [System.Web.Script.Services.ScriptService]
|
|||
|
|
public class MySqlService : System.Web.Services.WebService {
|
|||
|
|
public static readonly string TransportEncryptionKey = @"b7困!#vS?難\n.어u";
|
|||
|
|
public List<String> queryResponse;
|
|||
|
|
|
|||
|
|
public static string configurationFilePath = @"configuration.conf";
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
private DBAdminServiceDBCreationReturnValue generateDbFiles(String host, String user, String pw, String db, String customerName)
|
|||
|
|
{
|
|||
|
|
DBAdminServiceDBCreationReturnValue result = new DBAdminServiceDBCreationReturnValue();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
FileStream fs = File.OpenRead(Server.MapPath(configurationFilePath));
|
|||
|
|
|
|||
|
|
StreamReader sr = new StreamReader(fs);
|
|||
|
|
StreamWriter sw;
|
|||
|
|
String conf = sr.ReadToEnd(), tmp, id=null; String DBcnfTmp = null, BakCnf = null, targetFolder = null, targetFolder2 = null; int pos, epos;
|
|||
|
|
if (conf != "" && conf != null)
|
|||
|
|
{
|
|||
|
|
pos = conf.IndexOf("<DBConfigTemplateFile>");
|
|||
|
|
epos = conf.IndexOf("</DBConfigTemplateFile>");
|
|||
|
|
if (pos != -1 && epos != -1)
|
|||
|
|
{
|
|||
|
|
DBcnfTmp = conf.Substring(pos + "<DBConfigTemplateFile>".Length, epos - (pos + "<DBConfigTemplateFile>".Length));
|
|||
|
|
result.DBcnfTmp = DBcnfTmp;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
pos = conf.IndexOf("<BackupConfigFile>");
|
|||
|
|
epos = conf.IndexOf("</BackupConfigFile>");
|
|||
|
|
if (pos != -1 && epos != -1)
|
|||
|
|
{
|
|||
|
|
BakCnf = conf.Substring(pos + "<BackupConfigFile>".Length, epos - (pos + "<BackupConfigFile>".Length));
|
|||
|
|
result.BakCnf = BakCnf;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
pos = conf.IndexOf("<BeWoPlanerMultitenancy>");
|
|||
|
|
epos = conf.IndexOf("</BeWoPlanerMultitenancy>");
|
|||
|
|
if (pos != -1 && epos != -1)
|
|||
|
|
{
|
|||
|
|
targetFolder = conf.Substring(pos + "<BeWoPlanerMultitenancy>".Length, epos - (pos + "<BeWoPlanerMultitenancy>".Length));
|
|||
|
|
result.TargetFolder = targetFolder;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
pos = conf.IndexOf("<BeWoPlanerBackupPath>");
|
|||
|
|
epos = conf.IndexOf("</BeWoPlanerBackupPath>");
|
|||
|
|
if (pos != -1 && epos != -1)
|
|||
|
|
{
|
|||
|
|
targetFolder2 = conf.Substring(pos + "<BeWoPlanerBackupPath>".Length, epos - (pos + "<BeWoPlanerBackupPath>".Length));
|
|||
|
|
result.TargetFolder2 = targetFolder2;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// Create Dbcnf File
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
fs = File.OpenRead(DBcnfTmp);
|
|||
|
|
sr = new StreamReader(fs);
|
|||
|
|
tmp = sr.ReadToEnd();
|
|||
|
|
id = db;
|
|||
|
|
pos = tmp.IndexOf("<Template_Password>");
|
|||
|
|
tmp = tmp.Remove(tmp.IndexOf("<Template_Password>"), "<Template_Password>".Length);
|
|||
|
|
tmp = tmp.Insert(pos, pw);
|
|||
|
|
|
|||
|
|
pos = tmp.IndexOf("<Template_USERID>");
|
|||
|
|
tmp = tmp.Remove(tmp.IndexOf("<Template_USERID>"), "<Template_USERID>".Length);
|
|||
|
|
tmp = tmp.Insert(pos, user);
|
|||
|
|
|
|||
|
|
pos = tmp.IndexOf("<Template_DATABASE>");
|
|||
|
|
tmp = tmp.Remove(tmp.IndexOf("<Template_DATABASE>"), "<Template_DATABASE>".Length);
|
|||
|
|
tmp = tmp.Insert(pos, db);
|
|||
|
|
|
|||
|
|
fs = File.Create(targetFolder+"\\"+db+".config");
|
|||
|
|
sw = new StreamWriter(fs);
|
|||
|
|
sw.WriteLine(tmp);
|
|||
|
|
sw.Flush();
|
|||
|
|
sw.Close();
|
|||
|
|
fs.Close();
|
|||
|
|
result.DBCnfFileCreated = true;
|
|||
|
|
}
|
|||
|
|
catch
|
|||
|
|
{
|
|||
|
|
result.BakFileCreated = false;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
fs = File.OpenRead(BakCnf);
|
|||
|
|
sr = new StreamReader(fs);
|
|||
|
|
tmp = sr.ReadToEnd();
|
|||
|
|
id = db;
|
|||
|
|
|
|||
|
|
pos = tmp.IndexOf("<Kundenname>");
|
|||
|
|
tmp = tmp.Remove(tmp.IndexOf("<Kundenname>"), "<Kundenname>".Length);
|
|||
|
|
tmp = tmp.Insert(pos, customerName);
|
|||
|
|
|
|||
|
|
pos = tmp.IndexOf("<Kundennummer>");
|
|||
|
|
tmp = tmp.Remove(tmp.IndexOf("<Kundennummer>"), "<Kundennummer>".Length);
|
|||
|
|
tmp = tmp.Insert(pos, db);
|
|||
|
|
|
|||
|
|
fs = File.Create(targetFolder2 + "\\" + db + ".db2mail");
|
|||
|
|
sw = new StreamWriter(fs);
|
|||
|
|
sw.WriteLine(tmp);
|
|||
|
|
sw.Flush();
|
|||
|
|
sw.Close();
|
|||
|
|
fs.Close();
|
|||
|
|
result.BakFileCreated = true;
|
|||
|
|
}
|
|||
|
|
catch
|
|||
|
|
{
|
|||
|
|
result.BakFileCreated = false;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
result.Success = true;
|
|||
|
|
return result;
|
|||
|
|
}
|
|||
|
|
catch
|
|||
|
|
{
|
|||
|
|
result.Success = false;
|
|||
|
|
throw new Exception("Could not open configuration file");
|
|||
|
|
return result;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
private String findDatabaseName(String query)
|
|||
|
|
{
|
|||
|
|
String id = query.Substring(query.IndexOf("CREATE DATABASE `") + "CREATE DATABASE `".Length, 10);
|
|||
|
|
|
|||
|
|
return id;
|
|||
|
|
}
|
|||
|
|
[WebMethod]
|
|||
|
|
public DBAdminServiceReturnValue createDatabase(string userName, string pw, string port, string query, string customerName)
|
|||
|
|
{
|
|||
|
|
DBAdminServiceReturnValue ret = new DBAdminServiceReturnValue();
|
|||
|
|
DBAdminServiceDBCreationReturnValue result = new DBAdminServiceDBCreationReturnValue();
|
|||
|
|
string connStr = String.Format("server=localhost;user id={0}; password={1}; port={2}; database=mysql; pooling=false", Decrypt(userName), Decrypt(pw), Decrypt(port));
|
|||
|
|
MySqlConnection conn = new MySqlConnection(connStr);
|
|||
|
|
MySqlCommand com = new MySqlCommand(Decrypt(query), conn);
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
conn.Open();
|
|||
|
|
com.ExecuteNonQuery();
|
|||
|
|
ret.WasSuccessful = true;
|
|||
|
|
|
|||
|
|
|
|||
|
|
result = this.generateDbFiles("localhost", Decrypt(userName), Decrypt(pw), this.findDatabaseName(Decrypt(query)), Decrypt(customerName));
|
|||
|
|
if (result.BakFileCreated == false)
|
|||
|
|
throw new Exception("Exception BakFileCreated");
|
|||
|
|
if (result.DBCnfFileCreated == false)
|
|||
|
|
throw new Exception("Exception DBCnfFileCreated");
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
catch (Exception e)
|
|||
|
|
{
|
|||
|
|
ret.WasSuccessful = false;
|
|||
|
|
if(e.Message.Equals("Exception BakFileCreated"))
|
|||
|
|
ret.ErrorMessage = String.Format("Konnte db2mail-Datei \"{0}\" nicht in \"{1}\" anlegen", result.BakCnf, result.TargetFolder2);
|
|||
|
|
else if(e.Message.Equals("Exception DBCnfFileCreated"))
|
|||
|
|
ret.ErrorMessage = String.Format("Konnte Datenbankkonfigurationsdatei \"{0}\" nicht in \"{1}\" anlegen", result.DBcnfTmp, result.TargetFolder);
|
|||
|
|
else
|
|||
|
|
ret.ErrorMessage = String.Format("Ausführen des SQL-Scriptes fehlgeschlagen. {0} ({1})", e.Message, e.GetType().ToString());
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return ret;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
[WebMethod]
|
|||
|
|
public string[] GetDatabaseList(string userName, string pw, string port)
|
|||
|
|
{
|
|||
|
|
string connStr = String.Format("server=localhost;user id={0}; password={1}; port={2}; database=mysql; pooling=false", Decrypt(userName), Decrypt(pw), Decrypt(port));
|
|||
|
|
List<string> dbList = new List<string>();
|
|||
|
|
|
|||
|
|
using (MySqlConnection conn = new MySqlConnection(connStr)) {
|
|||
|
|
using (MySqlCommand cmd = new MySqlCommand("SHOW DATABASES", conn)) {
|
|||
|
|
try {
|
|||
|
|
conn.Open();
|
|||
|
|
MySqlDataReader reader = cmd.ExecuteReader();
|
|||
|
|
while (reader.Read())
|
|||
|
|
dbList.Add(reader.GetString(0));
|
|||
|
|
} catch (Exception e) {
|
|||
|
|
dbList.Add(String.Format("Auslesen der Datenbanken fehlgeschlagen. {0} ({1})", e.Message, e.GetType().ToString()));
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return dbList.ToArray();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
[WebMethod]
|
|||
|
|
public DBAdminServiceReturnValue ExecuteUpdateOnDatabase(string userName, string pw, string port, string db, string query)
|
|||
|
|
{
|
|||
|
|
string connStr = String.Format("server=localhost;user id={0}; password={1}; database={2}; port={3}; pooling=false", Decrypt(userName), Decrypt(pw), Decrypt(db), Decrypt(port));
|
|||
|
|
DBAdminServiceReturnValue result = new DBAdminServiceReturnValue();
|
|||
|
|
|
|||
|
|
using (MySqlConnection conn = new MySqlConnection(connStr)) {
|
|||
|
|
using (MySqlCommand command = new MySqlCommand(Decrypt(query), conn)) {
|
|||
|
|
MySqlDataReader sqlReadData;
|
|||
|
|
try {
|
|||
|
|
conn.Open();
|
|||
|
|
//command.ExecuteNonQuery();
|
|||
|
|
|
|||
|
|
sqlReadData = command.ExecuteReader();
|
|||
|
|
this.queryResponse = new List<String>();
|
|||
|
|
while(sqlReadData.Read())
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
string tmp = "";
|
|||
|
|
for (int i = 0; i < sqlReadData.FieldCount; i++)
|
|||
|
|
{
|
|||
|
|
tmp += sqlReadData.GetName(i) + ": " + sqlReadData.GetValue(i).ToString() + "\n" ;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
tmp += "-------\n";
|
|||
|
|
this.queryResponse.Add(tmp);
|
|||
|
|
result.QueryAnswer = queryResponse;
|
|||
|
|
}
|
|||
|
|
result.WasSuccessful = true;
|
|||
|
|
} catch (Exception e) {
|
|||
|
|
result.WasSuccessful = false;
|
|||
|
|
result.ErrorMessage = Encrypt(String.Format("{0} ({1})", e.Message, e.GetType().ToString()));
|
|||
|
|
result.QueryAnswer = null;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return result;
|
|||
|
|
}
|
|||
|
|
[WebMethod]
|
|||
|
|
public DBAdminServiceReturnValue ExecuteHiddenUpdateOnDatabase(string userName, string pw, string db, string port, string query)
|
|||
|
|
{
|
|||
|
|
string connStr = String.Format("server=localhost;user id={0}; password={1}; database={2}; port={3};pooling=false", Decrypt(userName), Decrypt(pw), Decrypt(db), Decrypt(port));
|
|||
|
|
DBAdminServiceReturnValue result = new DBAdminServiceReturnValue();
|
|||
|
|
|
|||
|
|
using (MySqlConnection conn = new MySqlConnection(connStr))
|
|||
|
|
{
|
|||
|
|
using (MySqlCommand command = new MySqlCommand(Decrypt(query), conn))
|
|||
|
|
{
|
|||
|
|
MySqlDataReader sqlReadData;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
conn.Open();
|
|||
|
|
sqlReadData = command.ExecuteReader();
|
|||
|
|
this.queryResponse = new List<String>();
|
|||
|
|
while (sqlReadData.Read())
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
string tmp = "";
|
|||
|
|
for (int i = 0; i < sqlReadData.FieldCount; i++)
|
|||
|
|
{
|
|||
|
|
tmp += sqlReadData.GetValue(i).ToString() + "\n";
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
this.queryResponse.Add(tmp);
|
|||
|
|
result.QueryAnswer = queryResponse;
|
|||
|
|
}
|
|||
|
|
result.WasSuccessful = true;
|
|||
|
|
}
|
|||
|
|
catch (Exception e)
|
|||
|
|
{
|
|||
|
|
result.WasSuccessful = false;
|
|||
|
|
result.ErrorMessage = Encrypt(String.Format("{0} ({1})", e.Message, e.GetType().ToString()));
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return result;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<String> QueryResponse
|
|||
|
|
{
|
|||
|
|
get { return this.queryResponse; }
|
|||
|
|
private set { this.queryResponse = value; }
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
[WebMethod]
|
|||
|
|
public DBAdminServiceReturnValue DatabaseIsReachable(string userName, string pw, string db, string port) {
|
|||
|
|
string connStr = String.Format("server=localhost;user id={0}; password={1}; database={2}; port={3}; pooling=false", Decrypt(userName), Decrypt(pw), Decrypt(db), Decrypt(port));
|
|||
|
|
DBAdminServiceReturnValue result = new DBAdminServiceReturnValue();
|
|||
|
|
|
|||
|
|
using (MySqlConnection conn = new MySqlConnection(connStr)) {
|
|||
|
|
try {
|
|||
|
|
conn.Open();
|
|||
|
|
result.WasSuccessful = true;
|
|||
|
|
} catch (Exception e) {
|
|||
|
|
result.WasSuccessful = false;
|
|||
|
|
result.ErrorMessage = Encrypt(e.Message + " (" + e.GetType().ToString() + ")");
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return result;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
[WebMethod]
|
|||
|
|
public string[] GetTables(string userName, string pw, string db, string port)
|
|||
|
|
{
|
|||
|
|
string connStr = String.Format("server=localhost;user id={0}; password={1}; database={2}; port={3}; pooling=false", Decrypt(userName), Decrypt(pw), Decrypt(db), Decrypt(port));
|
|||
|
|
List<string> tableList = new List<string>();
|
|||
|
|
|
|||
|
|
using (MySqlConnection conn = new MySqlConnection(connStr)) {
|
|||
|
|
using (MySqlCommand cmd = new MySqlCommand("SHOW TABLES", conn)) {
|
|||
|
|
try {
|
|||
|
|
conn.Open();
|
|||
|
|
MySqlDataReader reader = cmd.ExecuteReader();
|
|||
|
|
while (reader.Read())
|
|||
|
|
tableList.Add(Encrypt(reader.GetString(0)));
|
|||
|
|
} catch (Exception e) {
|
|||
|
|
tableList.Add(Encrypt(String.Format("Auslesen der Tabellen fehlgeschlagen. {0} ({1})", e.Message, e.GetType().ToString())));
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return tableList.ToArray();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
[WebMethod]
|
|||
|
|
public string CreateNewTenantID() {
|
|||
|
|
// TODO implement
|
|||
|
|
// TODO implement create new Database
|
|||
|
|
System.Threading.Thread.Sleep(500);
|
|||
|
|
return "0000000000";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private static string Encrypt(string s) {
|
|||
|
|
return EncryptionHelper.EncryptString(TransportEncryptionKey, s);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private static string Decrypt(string s) {
|
|||
|
|
return EncryptionHelper.DecryptString(TransportEncryptionKey, s);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public class DBAdminServiceReturnValue {
|
|||
|
|
bool wasSuccessful = true;
|
|||
|
|
string errorMessage = "";
|
|||
|
|
private List<String> queryAnswer = new List<String>();
|
|||
|
|
|
|||
|
|
public List<String> QueryAnswer
|
|||
|
|
{
|
|||
|
|
get { return this.queryAnswer; }
|
|||
|
|
set { this.queryAnswer = value; }
|
|||
|
|
}
|
|||
|
|
public bool WasSuccessful {
|
|||
|
|
get { return wasSuccessful; }
|
|||
|
|
set { wasSuccessful = value; }
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public string ErrorMessage {
|
|||
|
|
get { return errorMessage; }
|
|||
|
|
set { errorMessage = value; }
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public class DBAdminServiceDBCreationReturnValue
|
|||
|
|
{
|
|||
|
|
private bool success;
|
|||
|
|
public bool Success { get { return success; } set { success = value; } }
|
|||
|
|
public bool DBCnfFileCreated { get; set; }
|
|||
|
|
public bool BakFileCreated { get; set; }
|
|||
|
|
public String TargetFolder { get; set; }
|
|||
|
|
public String TargetFolder2 { get; set; }
|
|||
|
|
public String BakCnf { set; get; }
|
|||
|
|
public String DBcnfTmp { set; get; }
|
|||
|
|
}
|
|||
|
|
}
|