代碼: using System; using System.Collections; using System.Collections.Generic; using System.ComponentModel; using System.Configuration; using System.Da ...
MySqlHelper代碼:
using System; using System.Collections; using System.Collections.Generic; using System.ComponentModel; using System.Configuration; using System.Data; using MySql.Data.MySqlClient; using System.Linq; using System.Reflection; using System.Text; using System.Web; using System.Xml.Linq; using System.Data.Objects.DataClasses; using Models; using System.Text.RegularExpressions; namespace DBHelper { /// <summary> /// MySql操作類 /// 2015年6月20日 /// 寫程式之前,首先引用MySql.Data.MySqlClient /// </summary> public class MySqlHelper { #region 靜態變數 /// <summary> /// 資料庫連接字元串 /// </summary> private static string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString(); #endregion #region MySqlConnection 獲取資料庫連接 /// <summary> /// 獲取資料庫連接 /// </summary> private static MySqlConnection GetConn() { MySqlConnection connection = null; string key = "Simpo2016_MySqlConnection"; if (HttpContext.Current.Items[key] == null) { connection = new MySqlConnection(connectionString); connection.Open(); HttpContext.Current.Items[key] = connection; } else { connection = (MySqlConnection)HttpContext.Current.Items[key]; } return connection; } #endregion #region MySqlTransaction 獲取事務對象 /// <summary> /// 獲取事務對象 /// </summary> private static MySqlTransaction GetTran() { MySqlTransaction tran = null; string key = "Simpo2016_MySqlTransaction"; if (HttpContext.Current.Items[key] == null) { tran = GetConn().BeginTransaction(); HttpContext.Current.Items[key] = tran; } else { tran = (MySqlTransaction)HttpContext.Current.Items[key]; } return tran; } #endregion #region 開起事務標誌 /// <summary> /// 事務標誌 /// </summary> private static string tranFlagKey = "Simpo2016_MySqlTransaction_Flag"; /// <summary> /// 添加事務標誌 /// </summary> public static void AddTranFlag() { HttpContext.Current.Items[tranFlagKey] = true; } /// <summary> /// 移除事務標誌 /// </summary> public static void RemoveTranFlag() { HttpContext.Current.Items[tranFlagKey] = false; } /// <summary> /// 事務標誌 /// </summary> public static bool TranFlag { get { bool tranFlag = false; if (HttpContext.Current.Items[tranFlagKey] != null) { tranFlag = (bool)HttpContext.Current.Items[tranFlagKey]; } return tranFlag; } } #endregion #region 用於查詢的資料庫連接 /// <summary> /// 用於查詢的資料庫連接 /// </summary> private MySqlConnection m_Conn; #endregion #region 構造函數 public MySqlHelper() { m_Conn = new MySqlConnection(connectionString); } #endregion #region 基礎方法 #region 執行簡單SQL語句 #region Exists public bool Exists(string sqlString) { using (MySqlCommand cmd = new MySqlCommand(sqlString, m_Conn)) { try { m_Conn.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return false; } else { return true; } } catch (Exception ex) { throw ex; } finally { cmd.Dispose(); m_Conn.Close(); } } } #endregion #region 執行SQL語句,返回影響的記錄數 /// <summary> /// 執行SQL語句,返回影響的記錄數 /// </summary> /// <param name="sqlString">SQL語句</param> /// <returns>影響的記錄數</returns> public int ExecuteSql(string sqlString) { MySqlConnection connection = GetConn(); using (MySqlCommand cmd = new MySqlCommand(sqlString, connection)) { try { if (connection.State != ConnectionState.Open) connection.Open(); if (TranFlag) cmd.Transaction = GetTran(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (Exception ex) { throw new Exception(ex.Message); } finally { cmd.Dispose(); if (!TranFlag) connection.Close(); } } } #endregion #region 執行一條計算查詢結果語句,返回查詢結果 /// <summary> /// 執行一條計算查詢結果語句,返回查詢結果(object) /// </summary> /// <param name="sqlString">計算查詢結果語句</param> /// <returns>查詢結果(object)</returns> public object GetSingle(string sqlString) { using (MySqlCommand cmd = new MySqlCommand(sqlString, m_Conn)) { try { m_Conn.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (Exception ex) { throw ex; } finally { cmd.Dispose(); m_Conn.Close(); } } } #endregion #region 執行查詢語句,返回SQLiteDataReader /// <summary> /// 執行查詢語句,返回SQLiteDataReader ( 註意:調用該方法後,一定要對SqlDataReader進行Close ) /// </summary> /// <param name="sqlString">查詢語句</param> /// <returns>SQLiteDataReader</returns> public MySqlDataReader ExecuteReader(string sqlString) { MySqlConnection connection = new MySqlConnection(connectionString); MySqlCommand cmd = new MySqlCommand(sqlString, connection); try { connection.Open(); MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return myReader; } catch (Exception ex) { throw ex; } } #endregion #region 執行查詢語句,返回DataSet /// <summary> /// 執行查詢語句,返回DataSet /// </summary> /// <param name="sqlString">查詢語句</param> /// <returns>DataSet</returns> public DataSet Query(string sqlString) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); MySqlDataAdapter command = new MySqlDataAdapter(sqlString, connection); command.Fill(ds, "ds"); } catch (Exception ex) { throw ex; } finally { connection.Close(); } return ds; } } #endregion #endregion #region 執行帶參數的SQL語句 #region 執行SQL語句,返回影響的記錄數 /// <summary> /// 執行SQL語句,返回影響的記錄數 /// </summary> /// <param name="SQLString">SQL語句</param> /// <returns>影響的記錄數</returns> public int ExecuteSql(string SQLString, params MySqlParameter[] cmdParms) { MySqlConnection connection = GetConn(); using (MySqlCommand cmd = new MySqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); if (TranFlag) cmd.Transaction = GetTran(); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (Exception ex) { throw ex; } finally { cmd.Dispose(); if (!TranFlag) connection.Close(); } } } #endregion #region 執行查詢語句,返回SQLiteDataReader /// <summary> /// 執行查詢語句,返回SQLiteDataReader ( 註意:調用該方法後,一定要對SqlDataReader進行Close ) /// </summary> /// <param name="strSQL">查詢語句</param> /// <returns>SQLiteDataReader</returns> public MySqlDataReader ExecuteReader(string sqlString, params MySqlParameter[] cmdParms) { MySqlCommand cmd = new MySqlCommand(); try { PrepareCommand(cmd, m_Conn, null, sqlString, cmdParms); MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return myReader; } catch (Exception ex) { throw ex; } } #endregion #region 執行查詢語句,返回DataSet /// <summary> /// 執行查詢語句,返回DataSet /// </summary> /// <param name="sqlString">查詢語句</param> /// <returns>DataSet</returns> public DataSet Query(string sqlString, params MySqlParameter[] cmdParms) { MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, m_Conn, null, sqlString, cmdParms); using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (Exception ex) { throw ex; } finally { cmd.Dispose(); m_Conn.Close(); } return ds; } } #endregion #region PrepareCommand private void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text; if (cmdParms != null) { foreach (MySqlParameter parm in cmdParms) { cmd.Parameters.Add(parm); } } } #endregion #endregion #endregion #region 增刪改查 #region 獲取最大編號 /// <summary> /// 獲取最大編號 /// </summary> /// <typeparam name="T">實體Model</typeparam> /// <param name="key">主鍵</param> public int GetMaxID<T>(string key) { Type type = typeof(T); string sql = string.Format("SELECT Max({0}) FROM {1}", key, type.Name); using (MySqlCommand cmd = new MySqlCommand(sql, m_Conn)) { try { m_Conn.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return 1; } else { return int.Parse(obj.ToString()) + 1; } } catch (Exception ex) { throw ex; } finally { cmd.Dispose(); m_Conn.Close(); } } } #endregion #region 添加 /// <summary> /// 添加 /// </summary> public void Insert(object obj) { StringBuilder strSql = new StringBuilder(); Type type = obj.GetType(); CacheHelper.Remove(type);//刪除緩存 strSql.Append(string.Format("insert into {0}(", type.Name)); PropertyInfo[] propertyInfoList = GetEntityProperties(type); List<string> propertyNameList = new List<string>(); foreach (PropertyInfo propertyInfo in propertyInfoList) { propertyNameList.Add(propertyInfo.Name); } strSql.Append(string.Format("{0})", string.Join(",", propertyNameList.ToArray()))); strSql.Append(string.Format(" values ({0})", string.Join(",", propertyNameList.ConvertAll<string>(a => "@" + a).ToArray()))); MySqlParameter[] parameters = new MySqlParameter[propertyInfoList.Length]; for (int i = 0; i < propertyInfoList.Length; i++) { PropertyInfo propertyInfo = propertyInfoList[i]; object val = propertyInfo.GetValue(obj, null); MySqlParameter param = new MySqlParameter("@" + propertyInfo.Name, val == null ? DBNull.Value : val); parameters[i] = param; } ExecuteSql(strSql.ToString(), parameters); } #endregion #region 修改 /// <summary> /// 修改 /// </summary> public void Update(object obj) { object oldObj = Find(obj, false); if (oldObj == null) throw new Exception("無法獲取到舊數據"); StringBuilder strSql = new StringBuilder(); Type type = obj.GetType(); CacheHelper.Remove(type);//刪除緩存 strSql.Append(string.Format("update {0} ", type.Name)); PropertyInfo[] propertyInfoList = GetEntityProperties(type); List<string> propertyNameList = new List<string>(); int savedCount = 0; foreach (PropertyInfo propertyInfo in propertyInfoList) { object oldVal = propertyInfo.GetValue(oldObj, null); object val = propertyInfo.GetValue(obj, null); if (!object.Equals(oldVal, val)) { propertyNameList.Add(propertyInfo.Name); savedCount++; } } strSql.Append(string.Format(" set ")); MySqlParameter[] parameters = new MySqlParameter[savedCount]; StringBuilder sbPros = new StringBuilder(); int k = 0; for (int i = 0; i < propertyInfoList.Length; i++) { PropertyInfo propertyInfo = propertyInfoList[i]; object oldVal = propertyInfo.GetValue(oldObj, null); object val = propertyInfo.GetValue(obj, null); if (!object.Equals(oldVal, val)) { sbPros.Append(string.Format(" {0}=@{0},", propertyInfo.Name)); MySqlParameter param = new MySqlParameter("@" + propertyInfo.Name, val == null ? DBNull.Value : val); parameters[k++] = param; } } if (sbPros.Length > 0) { strSql.Append(sbPros.ToString(0, sbPros.Length - 1)); } strSql.Append(string.Format(" where {0}='{1}'", GetIdName(obj.GetType()), GetIdVal(obj).ToString())); if (savedCount > 0) { ExecuteSql(strSql.ToString(), parameters); } } #endregion #region 刪除 /// <summary> /// 根據Id刪除 /// </summary> public void Delete<T>(int id) { Type type = typeof(T); CacheHelper.Remove(type);//刪除緩存 StringBuilder sbSql = new StringBuilder(); sbSql.Append(string.Format("delete from {0} where {2}='{1}'", type.Name, id, GetIdName(type))); ExecuteSql(sbSql.ToString()); } /// <summary> /// 根據Id集合刪除 /// </summary> public void BatchDelete<T>(string ids) { if (string.IsNullOrWhiteSpace(ids)) return; Type type = typeof(T); CacheHelper.Remove(type);//刪除緩存 StringBuilder sbSql = new StringBuilder(); sbSql.Append(string.Format("delete from {0} where {2} in ({1})", type.Name, ids, GetIdName(type))); ExecuteSql(sbSql.ToString()); } /// <summary> /// 根據條件刪除 /// </summary> public void Delete<T>(string conditions) { if (string.IsNullOrWhiteSpace(conditions)) return; Type type = typeof(T); CacheHelper.Remove(type);//刪除緩存 StringBuilder sbSql = new StringBuilder(); sbSql.Append(string.Format("delete from {0} where {1}", type.Name, conditions)); ExecuteSql(sbSql.ToString()); } #endregion #region 獲取實體 #region 根據實體獲取實體 /// <summary> /// 根據實體獲取實體 /// </summary> private object Find(object obj, bool readCache = true) { Type type = obj.GetType(); object result = Activator.CreateInstance(type); bool hasValue = false; IDataReader rd = null; string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, GetIdVal(obj), GetIdName(obj.GetType())); //獲取緩存 if (readCache && CacheHelper.Exists(type, sql)) { return CacheHelper.Get(type, sql); } try { rd = ExecuteReader(sql); PropertyInfo[] propertyInfoList = GetEntityProperties(type); int fcnt = rd.FieldCount; List<string> fileds = new List<string>(); for (int i = 0; i < fcnt; i++) { fileds.Add(rd.GetName(i).ToUpper()); } while (rd.Read()) { hasValue = true; IDataRecord record = rd; foreach (PropertyInfo pro in propertyInfoList) { if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value) { continue; } pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null); } } } catch (Exception ex) { throw ex; } finally { if (rd != null && !rd.IsClosed) { rd.Close(); rd.Dispose(); } } if (hasValue) { CacheHelper.Add(type, sql, result);//添加緩存 return result; } else { return null; } } #endregion #region 根據Id獲取實體 /// <summary> /// 根據Id獲取實體 /// </summary> private object FindById(Type type, int id) { object result = Activator.CreateInstance(type); IDataReader rd = null; bool hasValue = false; string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type)); //獲取緩存 if (CacheHelper.Exists(type, sql)) { return CacheHelper.Get(type, sql); } try { rd = ExecuteReader(sql); PropertyInfo[] propertyInfoList = GetEntityProperties(type); int fcnt = rd.FieldCount; List<string> fileds = new List<string>(); for (int i = 0; i < fcnt; i++) { fileds.Add(rd.GetName(i).ToUpper()); } while (rd.Read()) { hasValue = true; IDataRecord record = rd; foreach (PropertyInfo pro in propertyInfoList) { if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value) { continue; } pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null); } } } catch (Exception ex) { throw ex; } finally { if (rd != null && !rd.IsClosed) { rd.Close(); rd.Dispose(); } } if (hasValue) { CacheHelper.Add(type, sql, result);//添加緩存 return result; } else { return null; } } #endregion #region 根據Id獲取實體 /// <summary> /// 根據Id獲取實體 /// </summary> public T FindById<T>(string id) where T : new() { Type type = typeof(T); T result = (T)Activator.CreateInstance(type); IDataReader rd = null; bool hasValue = false; string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type)); //獲取緩存 if (CacheHelper.Exists(type, sql)) { return (T)CacheHelper.Get(type, sql); } try { rd = ExecuteReader(sql); PropertyInfo[] propertyInfoList = GetEntityProperties(type); int fcnt = rd.FieldCount; List<string> fileds = new List<string>(); for (int i = 0; i < fcnt; i++) { fileds.Add(rd.GetName(i).ToUpper()); } while (rd.Read()) { hasValue = true; IDataRecord record = rd; foreach (PropertyInfo pro in propertyInfoList) { if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value) { continue; } pro.SetVal