public delegate void Dele_MySqliteMessage(string s);
public class MySqliteHelper
{
#region constants
private const string DEFAULT_MY_INI_MAX = @"
[client]
port= %PORT%
socket= /tmp/mysql.sock
[mysqld]
port= " + PortSymbol + @"
basedir=" +BaseDirSymbol+@"
datadir=" + DataDirSymbol + @"
socket= /tmp/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8
log-bin=mysql-bin
server-id = 1
[mysql]
no-auto-rehash";
private const string PACKAGE_NAME = "mysql.binary.zip";
private const string APP_NAME = "mysqld.exe";
private const string ADMIN_NAME = "mysqladmin.exe";
public const int DefaultPort = 63306;
public const string ApplicationFolderSymbol = "%APP%";
public const string PortSymbol = "%PORT%";
public const string BaseDirSymbol = "%BASEDIR%";
public const string DataDirSymbol = "%DATADIR%";
public const string DefaultFolder = ApplicationFolderSymbol + "/MySQL";
public const string DefaultServiceName = "mysqlite";
#endregion
#region ctors
public event Dele_MySqliteMessage OnMessage;
private static MySqliteHelper m_instance=new MySqliteHelper();
public static MySqliteHelper Instance
{
get
{
return m_instance;
}
}
private MySqliteHelper()
{
this.Port = AdjustPort(-1);
this.RootFolder = AdjustFolder(null);
this.SeviceName = AdjustSeviceName(null);
}
private MySqliteHelper(string strRootFolder, int nPort)
{
this.Port = AdjustPort(nPort);
this.RootFolder = AdjustFolder(strRootFolder);
this.SeviceName = AdjustSeviceName(null);
}
private MySqliteHelper(string strRootFolder, int nPort, string strServiceName)
{
this.Port = AdjustPort(nPort);
this.RootFolder = AdjustFolder(strRootFolder);
this.SeviceName = AdjustSeviceName(strServiceName);
}
#endregion
#region properties
private bool m_ExitMySQLWhenApplicationExiting = false;
public bool ExitMySQLWhenApplicationExiting
{
get
{
return m_ExitMySQLWhenApplicationExiting;
}
set
{
Application.ApplicationExit -= this.Application_ApplicationExit;
if (value)
{
Application.ApplicationExit += this.Application_ApplicationExit;
}
m_ExitMySQLWhenApplicationExiting = value;
}
}
public string SeviceName { get; set; }
public string RootFolder { get; private set; }
public int Port { get; set; }
public string MySQLBaseDir { get; private set; }
public string MySQLDataDir { get; private set; }
public string MySQLAdminExe { get; private set; }
private string MySQLDExe { get; set; }
private void ShowMessage(string s)
{
if (null != this.OnMessage)
{
OnMessage(s);
}
}
public bool FilesExist
{
get
{
return File.Exists(Path.Combine(this.MySQLBaseDir, "my.ini"))
&&
null != FindAppByName(this.RootFolder, APP_NAME);
}
}
public bool ServiceRunning
{
get
{
ManagementClass mc = new ManagementClass("Win32_Service");
ManagementObjectCollection moc = mc.GetInstances();
foreach (ManagementObject mo in moc)
{
string name = mo["Name"] as string;
string state = mo["State"] as string;
if (null != name && name.Equals(this.SeviceName, StringComparison.CurrentCultureIgnoreCase))
{
if (state != null
&& false == state.Equals("Stopped", StringComparison.CurrentCultureIgnoreCase)
&& false == state.Equals("Paused", StringComparison.CurrentCultureIgnoreCase))
{
return true;
}
}
}
return false;
}
}
public bool ServiceExisting
{
get
{
ManagementClass mc = new ManagementClass("Win32_Service");
ManagementObjectCollection moc = mc.GetInstances();
foreach (ManagementObject mo in moc)
{
string name = mo["Name"] as string;
if (null != name &&
name.Equals(this.SeviceName, StringComparison.CurrentCultureIgnoreCase))
{
return true;
}
}
return false;
}
}
#endregion
public bool InstallService()
{
ExtractAndConfig();
lock (this)
{
if (this.ServiceExisting)
{
return true;
}
Process p = new Process();
p.StartInfo.FileName = this.MySQLDExe;
p.StartInfo.Arguments = "--install-manual " + this.SeviceName+
" --defaults-file=\"" + Path.Combine(this.MySQLBaseDir, "my.ini") + "\"";
p.StartInfo.UseShellExecute = false;
p.StartInfo.RedirectStandardInput = true;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.RedirectStandardError = true;
p.StartInfo.CreateNoWindow = true;
p.Start();
string strRst = p.StandardOutput.ReadToEnd();
p.WaitForExit();
p.Close();
return null != strRst && strRst.IndexOf("successfully", StringComparison.CurrentCultureIgnoreCase) >= 0;
}
}
public bool RemoveService()
{
ExtractAndConfig();
lock (this)
{
if (this.ServiceExisting == false)
{
return true;
}
Process p = new Process();
p.StartInfo.FileName = this.MySQLDExe;
p.StartInfo.Arguments = " --remove " + this.SeviceName;
p.StartInfo.UseShellExecute = false;
p.StartInfo.RedirectStandardInput = true;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.RedirectStandardError = true;
p.StartInfo.CreateNoWindow = true;
p.Start();
string strRst = p.StandardOutput.ReadToEnd();
p.WaitForExit();
p.Close();
return null != strRst && strRst.IndexOf("successfully", StringComparison.CurrentCultureIgnoreCase) >= 0;
}
}
public void ExtractAndConfig()
{
lock (this)
{
ShowMessage("查找主程序...");
this.MySQLDExe = FindAppByName(this.RootFolder, APP_NAME);
if (this.MySQLDExe == null)
{
ShowMessage("解压主程序...");
Type type = typeof(MySqliteHelper);
Assembly asm = type.Assembly;
string strPackageFile = WriteResourceStreamToFile(type, asm, this.RootFolder
, type.Namespace + ".MYSQL." + PACKAGE_NAME, PACKAGE_NAME);
UnZip(strPackageFile, this.RootFolder, null);
try
{
ShowMessage("删除临时文件...");
File.Delete(strPackageFile);
}
catch
{
}
this.MySQLDExe = FindAppByName(this.RootFolder, APP_NAME);
}
if (null == this.MySQLDExe)
{
return;
}
ShowMessage("初始化系统参数...");
this.MySQLBaseDir = (new FileInfo(this.MySQLDExe)).Directory.Parent.FullName;
this.MySQLDataDir = Path.Combine(this.MySQLBaseDir, "data");
this.MySQLAdminExe = FindAppByName(this.RootFolder, ADMIN_NAME);
//Configure
string strIniFileName = Path.Combine(this.MySQLBaseDir, "my.ini");
if (File.Exists(strIniFileName))
{
File.Delete(strIniFileName);
}
ShowMessage("重置数据库配置信息...");
File.WriteAllText(strIniFileName, DEFAULT_MY_INI_MAX
.Replace(PortSymbol, "" + this.Port)
.Replace(BaseDirSymbol, "\"" + this.MySQLBaseDir.Replace('\\', '/') + "\"")
.Replace(DataDirSymbol, "\"" + this.MySQLDataDir.Replace('\\', '/') + "\""));
}
}
private bool StopService()
{
lock (this)
{
if (this.ServiceExisting == false)
{
return true;
}
ShowMessage("卸载服务....");
ManagementClass mc = new ManagementClass("Win32_Service");
ManagementObjectCollection moc = mc.GetInstances();
foreach (ManagementObject mo in moc)
{
string name = mo["Name"] as string;
bool state = (bool)mo["AcceptStop"];
if (null != name && name.Equals(this.SeviceName, StringComparison.CurrentCultureIgnoreCase))
{
if (state == false)
{
break;
}
mo.InvokeMethod("StopService", null);
break;
}
}
}
return !this.ServiceRunning;
}
public bool StartService()
{
ShowMessage("尝试连接....");
if (this.TestConnection())
{
return true;
}
if (this.ServiceExisting == false)
{
ShowMessage("安装系统服务....");
InstallService();
}
lock (this)
{
ShowMessage("启动服务....");
ManagementClass mc = new ManagementClass("Win32_Service");
ManagementObjectCollection moc = mc.GetInstances();
foreach (ManagementObject mo in moc)
{
string name = mo["Name"] as string;
string state = mo["State"] as string;
if (null != name && name.Equals(this.SeviceName, StringComparison.CurrentCultureIgnoreCase))
{
if (null == state)
{
break;
}
if (state.Equals("Stopped", StringComparison.CurrentCultureIgnoreCase))
{
mo.InvokeMethod("StartService", null);
}
else if (state.Equals("Paused", StringComparison.CurrentCultureIgnoreCase))
{
mo.InvokeMethod("ResumeService", null);
}
break;
}
}
}
return this.ServiceRunning;
}
private void StopProcess()
{
try
{
ShowMessage("正常停止服务....");
//Normal Stop
Process p = new Process();
p.StartInfo.FileName = this.MySQLAdminExe;
p.StartInfo.Arguments = "-u root"
+(this.Password!=null && this.Password.Length>0?(" -p"+this.Password):"")
+" --port "+this.Port
+" shutdown";
p.StartInfo.UseShellExecute = false;
p.StartInfo.RedirectStandardInput = true;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.RedirectStandardError = true;
p.StartInfo.CreateNoWindow = true;
p.Start();
string strRst = p.StandardOutput.ReadToEnd();
p.WaitForExit();
p.Close();
}
catch(Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
}
try
{
ShowMessage("强制删除服务....");
if (null == m_mySqlProcess)
{
return;
}
//m_mySqlProcess.Close();
m_mySqlProcess.Kill();//We have to use this !!
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
}
}
public void StartProcess()
{
StartProcess(false);
}
private Process m_mySqlProcess = null;
public void StartProcess(bool isShowWindow)
{
ShowMessage("测试是否能够连接....");
if (this.TestConnection())
{
return;
}
ShowMessage("尝试解压文件....");
ExtractAndConfig();
lock (this)
{
ShowMessage("启动数据库....");
m_mySqlProcess = new Process();
m_mySqlProcess.StartInfo.FileName = this.MySQLDExe;
m_mySqlProcess.StartInfo.Arguments = "--defaults-file=\"" + Path.Combine(this.MySQLBaseDir, "my.ini") + "\"";
m_mySqlProcess.StartInfo.UseShellExecute = false;
//m_mySqlProcess.StartInfo.RedirectStandardInput = true;
//m_mySqlProcess.StartInfo.RedirectStandardOutput = true;
//m_mySqlProcess.StartInfo.RedirectStandardError = true;
m_mySqlProcess.StartInfo.CreateNoWindow = isShowWindow == false;
m_mySqlProcess.StartInfo.WindowStyle = isShowWindow == false
? ProcessWindowStyle.Hidden
: ProcessWindowStyle.Minimized;
m_mySqlProcess.Start();
//string strRst = m_mySqlProcess.StandardOutput.ReadToEnd();
//m_mySqlProcess.WaitForExit();
//m_mySqlProcess.Close();
if (this.ExitMySQLWhenApplicationExiting)
{
Application.ApplicationExit += new EventHandler(Application_ApplicationExit);
}
}
}
public void Stop()
{
StopProcess();
StopService();
}
private void Application_ApplicationExit(object sender, EventArgs e)
{
Application.ApplicationExit -= Application_ApplicationExit;
StopProcess();
StopService();
}
#region helper function
private static bool UnZip(string FileToUpZip, string ZipedFolder, string Password)
{
if (!File.Exists(FileToUpZip))
{
return false;
}
if (!Directory.Exists(ZipedFolder))
{
Directory.CreateDirectory(ZipedFolder);
}
ZipInputStream zipStream = null;
ZipEntry zipEntry = null;
try
{
zipStream = new ZipInputStream(File.OpenRead(FileToUpZip));
zipStream.Password = Password;
while ((zipEntry = zipStream.GetNextEntry()) != null)
{
if (zipEntry.Name != String.Empty)
{
string fileName = Path.Combine(ZipedFolder, zipEntry.Name);
if (fileName.EndsWith("/") || fileName.EndsWith("\\"))
{
Directory.CreateDirectory(fileName);
continue;
}
FileStream streamWriter = File.Create(fileName);
try
{
byte[] data = new byte[8192];
while (true)
{
int size = zipStream.Read(data, 0, data.Length);
if (size > 0)
{
streamWriter.Write(data, 0, size);
}
else
{
break;
}
}
streamWriter.Close();
streamWriter = null;
}
catch (Exception excep)
{
throw excep;
}
finally
{
if (streamWriter != null)
{
streamWriter.Close();
streamWriter = null;
}
}
}
}
}
finally
{
if (zipEntry != null)
{
zipEntry = null;
}
if (zipStream != null)
{
zipStream.Close();
zipStream = null;
}
GC.Collect();
GC.Collect(1);
}
return true;
}
private int AdjustPort(int nPort)
{
if (nPort < 1 || nPort > 65535)
{
nPort = DefaultPort;
}
return nPort;
}
private string AdjustSeviceName(string strServiceName)
{
if (null == strServiceName || strServiceName.Trim().Length < 1)
{
strServiceName = DefaultServiceName;
}
return strServiceName;
}
private string AdjustFolder(string strFolderName)
{
if (null == strFolderName || strFolderName.Trim().Length < 1)
{
strFolderName = DefaultFolder;
}
string strApplicationFolder = Path.GetDirectoryName(Application.ExecutablePath);
if (strFolderName.IndexOf(ApplicationFolderSymbol, StringComparison.CurrentCultureIgnoreCase) >= 0)
{
strFolderName = strFolderName.Replace(ApplicationFolderSymbol, strApplicationFolder);
}
strFolderName = (new DirectoryInfo(strFolderName)).FullName;
if (Directory.Exists(strFolderName) == false)
{
Directory.CreateDirectory(strFolderName);
}
return strFolderName;
}
private string FindAppByName(string strFolder, string strName)
{
DirectoryInfo d = new DirectoryInfo(strFolder);
if (d.Exists == false)
{
return null;
}
else
{
foreach (FileInfo f in d.GetFiles())
{
if (f.Name.Equals(strName, StringComparison.CurrentCultureIgnoreCase))
{
return f.FullName;
}
}
foreach (DirectoryInfo f in d.GetDirectories())
{
string x = FindAppByName(f.FullName, strName);
if (x != null)
{
return x;
}
}
return null;
}
}
private string WriteResourceStreamToFile(Type type
, Assembly asm
, string strDestination
, string strResourceName
, string filename)
{
string strRealName = filename;
string strFileName = strRealName;
string strFoldername = "";
if (null == strRealName || strRealName.Trim().Length < 1)
{
strRealName = strResourceName;
if (strRealName.StartsWith(type.Namespace + "."))
{
strRealName = strResourceName.Substring((type.Namespace + ".").Length);
}
string[] fileNameParts = strRealName.Split(new char[] { '.' });
strFileName = strRealName;
if (fileNameParts.Length > 2)
{
for (int n = 0; n < fileNameParts.Length - 2; n++)
{
if (strFoldername.Length > 0)
{
strFoldername += "/";
}
strFoldername += fileNameParts[n];
}
strFileName = fileNameParts[fileNameParts.Length - 2] + "." + fileNameParts[fileNameParts.Length - 1];
}
}
strFoldername = Path.Combine(strDestination, strFoldername);
if (Directory.Exists(strFoldername) == false)
{
Directory.CreateDirectory(strFoldername);
}
strFileName = Path.Combine(strFoldername, strFileName);
if (File.Exists(strFileName))
{
File.Delete(strFileName);
}
FileStream fs = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Write,
FileShare.Read);
Stream src = asm.GetManifestResourceStream(strResourceName);
int nLength = 32 * 1024;
byte[] buf = new byte[nLength];
int nx = 0;
while ((nx = src.Read(buf, 0, nLength)) > 0)
{
fs.Write(buf, 0, nx);
fs.Flush();
}
fs.Close();
src.Close();
src.Dispose();
return strFileName;
}
public string ReadResourceStreamToString(Assembly asm, string strResourceName)
{
if (null == strResourceName)
{
return null;
}
if (null == asm)
{
asm = Assembly.GetEntryAssembly();
}
System.IO.StreamReader buf = new StreamReader(
asm.GetManifestResourceStream(strResourceName),
System.Text.Encoding.Default);
string strReturn = buf.ReadToEnd();
buf.Close();
return strReturn;
}
#endregion
#region generic operational
public string Password { get; set; }
public bool TestConnection()
{
try
{
Sqlca sqlca = GetConnection(null, null, null);
sqlca.CloseAll();
return true;
}
catch
{
return false;
}
}
public Sqlca GetConnection(string strDatabase)
{
return GetConnection(null,this.Password,strDatabase);
}
public String GetConnectionString(string strDatabase)
{
return GetConnectionString(null, this.Password, strDatabase);
}
public string GetConnectionString(String strUser, string password, String strDatabase)
{
if (null == strDatabase || strDatabase.Trim().Length < 1)
{
strDatabase = "mysql";
}
if (null == strUser || strUser.Trim().Length < 1)
{
strUser = "root";
}
if (null == password || password.Trim().Length < 1)
{
password = this.Password;
}
this.Password = password;
string connectionstring = "User Id=" + strUser + ";"
+ "Host=localhost;"
+ "Database=" + strDatabase + ";"
+ "Port=" + this.Port + ";"
+ "password=" + password + ";"
+ "CharSet=utf8;"
+ "Connection Timeout=60;";
return connectionstring;
}
public Sqlca GetConnection(String strUser,string password,String strDatabase)
{
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(
GetConnectionString(strUser,password,strDatabase));
conn.Open();
return new Sqlca(conn);
}
public bool ChangeRootPassword(string oldpassword, string newpassword)
{
using (Sqlca sqlca = this.GetConnection("root",oldpassword,"mysql"))
{
sqlca.Execute("update user set password=password(\""+newpassword+"\") where user='root'");
sqlca.Execute("flush privileges");
this.Password = newpassword;
sqlca.CloseAll();
}
return true;
}
public bool CreateDatabase(string strDatabaseName)
{
if (null == strDatabaseName || strDatabaseName.Trim().Length < 1)
{
return false;
}
List<string> db=ListDatabases();
if (db.Contains(strDatabaseName.ToUpper()))
{
return true;
}
using (Sqlca sqlca = this.GetConnection("root", this.Password, "mysql"))
{
sqlca.Execute("Create database " + strDatabaseName);
sqlca.CloseAll();
}
return true;
}
public List<string> ListDatabases()
{
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(
GetConnectionString(null, null, "mysql"));
conn.Open();
DataTable dt = conn.GetSchema("Databases");
List<string> ret = new List<string>();
foreach (System.Data.DataRow row in dt.Rows)
{
string t = row["Database_Name"] as string;
if ( t != null && t.Trim().Length > 0)
{
ret.Add(t.ToUpper());
}
}
conn.Close();
return ret;
}
public List<string> ListTables(String strDatabase)
{
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(
GetConnectionString(null,null, strDatabase));
conn.Open();
DataTable dt=conn.GetSchema("Tables");
List<string> ret = new List<string>();
foreach (System.Data.DataRow row in dt.Rows)
{
string s = row["TABLE_SCHEMA"] as string;
string t = row["TABLE_NAME"] as string;
if (s != null && t != null && t.Trim().Length > 0 && s.Equals(strDatabase, StringComparison.CurrentCultureIgnoreCase))
{
ret.Add(t.ToUpper());
}
}
conn.Close();
return ret;
}
//private static void DisplayData(System.Data.DataTable table)
//{
// foreach (System.Data.DataRow row in table.Rows)
// {
// foreach (System.Data.DataColumn col in table.Columns)
// {
// Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
// }
// Console.WriteLine("============================");
// }
//}
#endregion
}