using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Xml;
using Row = System.Collections.Generic.Dictionary<string, string>;

namespace NeowayFixPlatform
{
    
    class MysqlFunction
    {
        public static string table_fix = "nw_fix_record";

        public bool IsConnected()
        {
            return (mysql.State == ConnectionState.Open);
        }
        public MysqlFunction()
        {
            if (mysql.State != ConnectionState.Open)
                mysql.Open();
        }

        ~MysqlFunction()
        {
            if (mysql.State == ConnectionState.Open)
                mysql.Dispose();
        }
        private static String mysqlcon = GetConfigString();
        MySqlConnection mysql = new MySqlConnection(mysqlcon);


        public string quote(ref string value)
        {
            value = value.Replace(@"\", @"\\").Replace("'", @"\'");
            return value;
        }
        public StringBuilder quote(ref StringBuilder value)
        {
            value = value.Replace(@"\", @"\\").Replace("'", @"\'");
            return value;
        }

        public StringBuilder GetPairSQLString(Row row)
        {
            StringBuilder strSQL = new StringBuilder();

            foreach (KeyValuePair<string, string> pair in row)
            {
                string value = pair.Value.ToString();
                this.quote(ref value);
                strSQL.AppendFormat("{0}='{1}',", pair.Key, value);
            }
            strSQL.Remove(strSQL.Length-1, 1);
            return strSQL;
        }


        public bool UpdateOne(string table,Row row,string format,params object[] args)
        {
            StringBuilder condition = new StringBuilder();
            condition.AppendFormat(format, args);
            return this.UpdateOne(table, row, condition.ToString());
        }


        public bool UpdateOne(string strTable, Row row, string strQuery)
        {
            StringBuilder strSQL = new StringBuilder();
            try
            {
                strSQL.AppendFormat("UPDATE {0} set {1} WHERE {2} LIMIT 1", strTable, GetPairSQLString(row), strQuery);
                //mysql.Open();
                MySqlCommand cmd = new MySqlCommand(strSQL.ToString(), mysql);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                return true;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return false;
        }
        

        public Row FetchOne(string strSQL)
        {
            Row row = new Row();
            try
            {
                
                //mysql.Open();
                MySqlCommand cmd = new MySqlCommand(strSQL.ToString(),mysql);
                MySqlDataReader reader = cmd.ExecuteReader();
                reader.Read();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    if (reader.HasRows)
                        row[reader.GetName(i)] = reader[i].ToString();
                }
                reader.Close();
                cmd.Dispose();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return row;
        }


        #region 获取登陆字符
        /// <summary>
        /// 获取登陆字符
        /// </summary>
        /// <returns></returns>
        public static string GetConfigString()
        {
            string spath = Application.StartupPath;
            spath += "\\config.xml";
            XmlDocument xml = new XmlDocument();
            xml.Load(spath);
            XmlNode config = xml.SelectSingleNode("config");
            XmlNode dbname = config.SelectSingleNode("dbname");
            XmlNode ipaddr = config.SelectSingleNode("ipaddr");
            XmlNode username = config.SelectSingleNode("username");
            XmlNode password = config.SelectSingleNode("password");

            return string.Format("database={0};Password={1};User ID={2};server={3}", dbname.InnerText, password.InnerText, username.InnerText, ipaddr.InnerText);
            
        }
        #endregion



        public bool BindData(string sql,ref DataGridView dgv)
        {
            DataSet dataset = new DataSet();
            DataTable datatable = new DataTable();
            try
            {
                MySqlDataAdapter mdap = new MySqlDataAdapter(sql, mysql);
                mdap.Fill(dataset);
                datatable = dataset.Tables[0];
                dgv.DataSource = datatable;
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return false;
        }


        #region 用户登录
        /// <summary>
        /// 系统登录
        /// </summary>
        /// <returns></returns>
        public bool Login(cUserInfo userinfo)
        {
            DataSet dataset = new DataSet();
            DataTable datatable = new DataTable();
            try
            {
                //MySqlDataAdapter 数据库执行函数，查询，插入，删除都可使用
               MySqlDataAdapter mdap = new MySqlDataAdapter("select * from nw_user where user_name = '" + userinfo.User_name + "' and user_pwd = '" + userinfo.User_password + "'", mysql);
               mdap.Fill(dataset);
               datatable = dataset.Tables[0];

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            if (datatable.Rows.Count == 1)
            {
                DataRow datarow = datatable.Rows[0];
                userinfo.User_power = datarow["user_power"].ToString();
                return true;
            }
            return false;
 
        }
        #endregion

        public bool SaveFix(Dictionary<string,string> row,bool bNew)
        {
            StringBuilder strSQL = new StringBuilder();
            try
            {
                if (bNew)
                    strSQL.Append("INSERT INTO nw_fix_record set ");
                else
                    strSQL.Append("UPDATE nw_fix_record set ");
                strSQL.AppendFormat("{0}",GetPairSQLString(row));

                if (!bNew)
                {
                    strSQL.AppendFormat(" WHERE IMEI_MEID='{0}' limit 1", row["IMEI_MEID"]);
                }
                //mysql.Open();

                MySqlCommand cmd = new MySqlCommand(strSQL.ToString(), mysql);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
            return true;
        }

        #region 获取所有产品型号
        /// <summary>
        /// 获取所有产品型号
        /// </summary>
        /// <returns></returns>
        public string[] GetProducts()
        {
            DataSet dataset = new DataSet();
            DataTable datatable = new DataTable();
            string[] rows = null;
            try
            {

                MySqlDataAdapter mdap = new MySqlDataAdapter("select * from nw_product where 1", mysql);
                mdap.Fill(dataset);
                datatable = dataset.Tables[0];

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return rows;
            }

            int row_count = datatable.Rows.Count;
            if (row_count>0)
            {
                rows = new string[row_count];
                for (int i = 0; i < row_count; i++ )
                {
                    rows[i] = datatable.Rows[i][0].ToString();
                }
            }
            return rows;
        }
        #endregion


        #region 获取所有客户名称
        /// <summary>
        /// 获取所有客户名称
        /// </summary>
        /// <returns></returns>
        public string[] GetAllClientName()
        {
            DataSet dataset = new DataSet();
            DataTable datatable = new DataTable();
            string[] rows = null;
            try
            {

                MySqlDataAdapter mdap = new MySqlDataAdapter("select client_name from nw_client where 1", mysql);
                mdap.Fill(dataset);
                datatable = dataset.Tables[0];

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return rows;
            }

            int row_count = datatable.Rows.Count;
            if (row_count > 0)
            {
                rows = new string[row_count];
                for (int i = 0; i < row_count; i++)
                {
                    rows[i] = datatable.Rows[i][0].ToString();
                }
            }
            return rows;
        }
        #endregion


        #region 获取所有维修人员
        /// <summary>
        /// 获取所有维修人员
        /// </summary>
        /// <returns></returns>
        public string[] GetAllRepairEr()
        {
            DataSet dataset = new DataSet();
            DataTable datatable = new DataTable();
            string[] rows = null;
            try
            {

                MySqlDataAdapter mdap = new MySqlDataAdapter("select user_name from nw_user where user_power='维修员' order by user_id", mysql);
                mdap.Fill(dataset);
                datatable = dataset.Tables[0];

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return rows;
            }

            int row_count = datatable.Rows.Count;
            if (row_count > 0)
            {
                rows = new string[row_count];
                for (int i = 0; i < row_count; i++)
                {
                    rows[i] = datatable.Rows[i][0].ToString();
                }
            }
            return rows;
        }

    }
    #endregion

    #region 定义用户表
    public class cUserInfo
        {
            private int user_id;

            public int User_id
            {
                get { return user_id; }
                set { user_id = value; }
            }

            private string user_name;

            public string User_name
            {
                get { return user_name; }
                set { user_name = value; }
            }
            private string user_password;

            public string User_password
            {
                get { return user_password; }
                set { user_password = value; }
            }
            private string user_power;

            public string User_power
            {
                get { return user_power; }
                set { user_power = value; }
            }
        }
   #endregion
   
}

天下 2015-07-30 19:59 发表评论