mysql系统获取相关函数

通过服务判断MySql是否安装

public bool IsMySqlExist(string strSerVice)
        {
            bool flag = false;
            //需引用system.serviceProcess
            ServiceController[] services = ServiceController.GetServices();
            foreach (ServiceController service in services)
            {
                if (service.DisplayName.ToString() == strSerVice)
                {
                    flag = true;
                }
            }
            return flag;
        }

判断mysql服务是否在运行

public bool isServiceRunning(string strSerVice)
        {
            bool flag = false;
            //需引用system.serviceProcess
            ServiceController[] services = ServiceController.GetServices();
            foreach (ServiceController service in services)
            {
                if (service.DisplayName.ToString() == strSerVice)
                {
                    if (service.Status == ServiceControllerStatus.Running)
                        flag = true;
                }
            }
            return flag;
        }

启动服务

public bool StartService(string strSerVice)
        {
            bool bo = false;
            ServiceController[] services = ServiceController.GetServices();
            foreach (ServiceController service in services)
            {
                if (service.DisplayName.ToString() == strSerVice)
                {
                    if (service.Status != ServiceControllerStatus.Running)
                    {
                        try
                        {
                            service.Start();
                            bo = true;
                        }
                        catch (Exception)
                        {
                            MessageBox.Show("未能启动MySQL57服务，请尝试以管理员身份重新运行", "软件提示");
                            bo = false;
                        }
                    }
                    else
                        bo = true;
                }
            }
            return bo;
        }

停止服务

public bool StopService(string strSerVice)
        {
            bool bo = false;
            ServiceController[] services = ServiceController.GetServices();
            foreach (ServiceController service in services)
            {
                if (service.DisplayName.ToString() == strSerVice)
                {
                    if (service.Status != ServiceControllerStatus.Stopped)
                    {
                        service.Stop();
                        bo = true;
                    }
                    else
                        bo = true;
                }
            }
            return bo;
        }

获取mySql安装路径

public string GetMySqlInstallPath()
        {
            string strPath = "";
            string strSql = "SELECT @@basedir AS basePath From dual";
            object obj = GetSingleObject(strSql);

            if (obj != null)
            {
                strPath = obj.ToString();
                strPath = strPath.Replace("/", "\\");
                strPath += "bin";
            }

            return strPath;
        }

通过注册表获取my.ini文件位置

public string GetMyIniPath()
        {
            string strReturn = string.Empty;

            RegistryKey key = Registry.LocalMachine.OpenSubKey(@"SYSTEM\CurrentControlSet\services\MySQL57\", false);
            if (key == null) return null;

            string strPath = key.GetValue("ImagePath", "").ToString();

            if (string.IsNullOrWhiteSpace(strPath)) return null;

            string[] str = strPath.Split('"');

            if (str.Length >= 3)
                strReturn = str[3];
            return strReturn;
        }

判断给定数据库在mysql中是否存在

public bool IsDataBaseExist(string strDbName)
        {
            bool isExist = false;

            string strSql = string.Format("SELECT * FROM information_schema.SCHEMATA  WHERE SCHEMA_NAME='{0}'",
                strDbName);

            DataTable dt = GetDataTable(strSql, "information_schema.SCHEMATA");
            if (dt == null) return false;

            if (dt.Rows.Count > 0)
            {
                isExist = true;
            }

            return isExist;
        }

判断给定表名在数据库中是否存在

public bool IsTableExist(string strTable)
        {
            bool isExist = false;

            string strSql = string.Format("SELECT table_name FROM information_schema.TABLES  WHERE table_name='{0}'",
                strTable);

            DataTable dt = GetDataTable(strSql, "information_schema.TABLES");
            if (dt == null) return false;

            if (dt.Rows.Count > 0)
            {
                isExist = true;
            }

            return isExist;
        }

获取指定表格最后修改时间

public string GetUpdateTimeOfTable(string strTable)
        {
            string strTime = GetValueByColumn("information_schema.TABLES", "table_name", strTable, "update_time");
            return strTime;
        }

判断程序在执行期间表格是否有更改

public bool IsTableChanged(string strTable, string strTimeBefore)
        {
            bool isChanged = false;

            string strTemp = GetUpdateTimeOfTable(strTable);

            if (strTemp != strTimeBefore)
                isChanged = true;

            return isChanged;
        }

判断给定视图名在数据库中是否存在

public bool IsViewExist(string strViewName)
        {
            bool isExist = false;
            string strSql = "";
            DataTable dt = null;                                    //声明DataTable引用

            strSql = string.Format("SELECT table_name FROM information_schema.VIEWS  WHERE table_name='{0}'", strViewName);
            try
            {
                dt = GetDataTable(strSql, "information_schema.VIEWS");
                if (dt == null) return false;

                if (dt.Rows.Count > 0)
                {
                    isExist = true;
                }
            }
            catch (Exception e)
            {

                MessageBox.Show(e.Message, "软件提示");             //异常信息提示
                //throw exp;                                            //抛出异常
            }

            return isExist;
        }

根据给定表名，返回该表列名数组

public List<string> GetColumnNamesList(string strTable)
        {
            List<string> columnNameList = null;
            string strSql = "";
            DataTable dt = null;                                    //声明DataTable引用

            strSql = string.Format(@"SELECT column_name FROM information_schema.columns WHERE table_name='{0}' 
                                    AND table_schema='{1}'", strTable, m_DataBaseName);

            dt = GetDataTable(strSql, strTable);
            if (dt == null) return null;

            columnNameList = new List<string>();
            foreach (DataRow dr in dt.Rows)
            {
                string str = dr[0].ToString().Trim();
                columnNameList.Add(str);
            }

            return columnNameList;
        }

根据给定表名，返回该表列名表格

public DataTable GetColumnNamesTable(string strTable)
        {
            string strSql = "";
            DataTable dt = null;                                    //声明DataTable引用

            strSql = string.Format(@"SELECT column_name FROM information_schema.columns WHERE table_name='{0}' 
                                    AND table_schema='{1}'", strTable, m_DataBaseName);

            dt = GetDataTable(strSql, strTable);

            return dt;
        }

获取数据库系统的时间

public DateTime GetDBTime()
        {
            DateTime dtDBTime = DateTime.Now;

            try
            {
                dtDBTime = Convert.ToDateTime(GetSingleObject("SELECT NOW()"));
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, "软件提示");
            }

            return dtDBTime;
        }

获取当前时间戳（秒）

public int GetDBTimesStamp()
        {
            int timesstamp = Convert.ToInt32(GetSingleObject("SELECT unix_timestamp(now())"));
            return timesstamp;
        }

开启远程访问

public void RemoteOn(string strUser = "root")
        {
            string strSql = string.Format("UPDATE mysql.user SET host = '%' WHERE user = '{0}'", strUser);
            if (ExecDataBySql(strSql) > 0)
            {
                ExecDataBySql("flush privileges");//刷新
            }
        }

关闭远程访问

public void RemoteOff(string strUser = "root")
        {
            string strSql = string.Format("UPDATE mysql.user SET host = 'localhost' WHERE user = '{0}' AND host= '%'", strUser);
            if (ExecDataBySql(strSql) > 0)
            {
                ExecDataBySql("flush privileges");//刷新
            }
        }

创建用户

public bool CreateUser(string strUserName = "", string strPw = "123456")
        {
            string strSql = string.Format("CREATE USER '{0}' IDENTIFIED BY '{1}'", strUserName, strPw);

            if (ExecDataBySql(strSql) > 0) return true;
            else return false;
        }

修改端口

public void SetMyINIPort(int port=3006)
        {
            if (port <= 0) return;

            iniFilehelperOfmy.IniWriteValue("client", "port", port.ToString());
        }

设置数据库允许存储文件大小

public void SetAllowedPacket(string packet="20M")
        {
            iniFilehelperOfmy.IniWriteValue("mysqld", "max_allowed_packet", packet);
        }