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
}