class MysqlHelper : IDBHelper { /// <summary> /// 连接字符串 /// </summary> private string connString; /// <summary> /// 数据库连接对象 /// </summary> private MySqlConnection conn; /// <summary> /// MySqlDataReader /// </summary> private MySqlDataReader reader; private MySqlCommand command; /// <summary> /// 初始化 /// </summary> public MysqlHelper() { this.connString = InitConnString();//初始化连接字符串 } /// <summary> /// 获取数据库连接 /// </summary> /// <returns></returns> public void GetConn() { MySqlConnection conn = null; try { //判断连接字符串时候已经准备好 if (this.connString.Length == 0) { this.InitConnString(); } this.conn = new MySqlConnection(this.connString); this.conn.Open(); } catch (MySqlException ex) { throw ex; } } /// <summary> /// 初始化连接字符串 /// </summary> /// <returns></returns> public string InitConnString() { string connString = ""; connString += "server=" + ConfigurationManager.AppSettings.Get("ip"); connString += ";uid=" + ConfigurationManager.AppSettings.Get("user"); connString += ";pwd=" + ConfigurationManager.AppSettings.Get("pwd"); connString += ";database=" + ConfigurationManager.AppSettings.Get("database"); connString += ";CharSet=" + ConfigurationManager.AppSettings.Get("encoding"); return connString; } /// <summary> /// 执行插入操作 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="placeHolders">占位符集合</param> /// <param name="param">代替占位符的数据（顺序须和占位符顺序一致）</param> /// <param name="returnFieldName">返回列名</param> /// <returns>插入后的主键ID值</returns> public int ExcuteInsert(string sql, string[] placeHolders, object[] param, string returnFieldName) { int returnInt = 0; try { command = new MySqlCommand(sql, conn); int paramsNum = placeHolders.Count(); for (int i = 0; i < paramsNum; i++) { command.Parameters.AddWithValue(placeHolders[i], param[i]); } if (reader != null) { reader.Close(); } reader = command.ExecuteReader(); while (reader.Read()) { returnInt = reader.GetInt32(returnFieldName); } } catch (Exception ex) { throw new MysqlException(ex.Message, ex); } return returnInt; } /// <summary> /// 执行更新删除操作 /// </summary> /// <param name="sql">sql语句</param> /// <param name="placeHolders">占位符数组</param> /// <param name="param">占位符对应的值数组（顺序要一致）</param> /// <returns>影响的行数</returns> public int ExcuteUpdte(string sql, string[] placeHolders, object[] param) { int ret = 0; try { if (reader != null) { reader.Close(); } command = new MySqlCommand(sql, conn); int paramsNum = placeHolders.Count(); for (int i = 0; i < paramsNum; i++) { command.Parameters.AddWithValue(placeHolders[i], param[i]); } ret = command.ExecuteNonQuery(); } catch (Exception ex) { throw new MysqlException(ex.Message, ex); } return ret; } /// <summary> /// 执行查询语句 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="placeHolders">占位符数组</param> /// <param name="param">占位符对应的值数组（顺序要一致）</param> /// <returns><code>MySqlDataReader</code></returns> public MySqlDataReader excuteQuery(string sql, string[] placeHolders, object[] param) { try { command = new MySqlCommand(sql, conn); int paramsNum = placeHolders.Count(); for (int i = 0; i < paramsNum; i++) { command.Parameters.AddWithValue(placeHolders[i], param[i]); } if (reader != null) { reader.Close(); } reader = command.ExecuteReader(); } catch (Exception ex) { throw new MysqlException(ex.Message, ex); } return reader; } /// <summary> /// 关闭数据库连接 /// </summary> public void Close() { if (this.reader != null && (!this.reader.IsClosed)) { reader.Close(); } if (this.conn != null) { this.conn.Close(); } } }调用：public class Template { private IDBHelper dbHelper = DBFactory.GetDbHelper();//数据库帮助类 /// <summary> /// 列表查询，返回对应实体对象的列表 /// </summary> /// <param name="sql">sql语句</param> /// <param name="mapping">实体映射</param> /// <param name="placeHolders">占位符数组</param> /// <param name="param">占位符对应的值数组</param> /// <returns>对应实体的列表</returns> public IList<object> ListQuery(string sql, IEntityMapping mapping, string[] placeHolders, object[] param) { IList<object> retList = new List<object>(); MySqlDataReader reader = null; try { dbHelper.GetConn(); reader = dbHelper.excuteQuery(sql, placeHolders, param); while (reader.Read()) { retList.Add(mapping.mapping(reader)); } } catch (Exception ex) { throw new MysqlException(ex.Message, ex); } finally { dbHelper.Close(); } return retList; } /// <summary> /// 获取单个实体对象 /// </summary> /// <param name="sql">sql语句</param> /// <param name="mapping">实体映射</param> /// <param name="placeHolders">占位符数组</param> /// <param name="param">占位符对应的值数组</param> /// <returns>对应实体的列表</returns> public object objectQuery(string sql, IEntityMapping mapping, string[] placeHolders, object[] param) { try { IList<object> objectList = ListQuery(sql, mapping, placeHolders, param); if (objectList.Count == 1) { return objectList[0]; } else { return null; } } catch (Exception ex) { throw new MysqlException(ex.Message, ex); } finally { dbHelper.Close(); } } /// <summary> /// 插入数据 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="mapping">实体映射</param> /// <param name="placeHolders">占位符数组</param> /// <param name="param">占位符对应的值数组</param> /// <param name="returnFieldName">返回列名</param> /// <returns>插入成功后生成的主键ID</returns> public int Insert(string sql, IEntityMapping mapping, string[] placeHolders, object[] param, string returnFieldName) { int retInt = 0; try { dbHelper.GetConn(); retInt = dbHelper.ExcuteInsert(sql, placeHolders, param, returnFieldName); } catch (Exception ex) { throw new MysqlException(ex.Message, ex); } finally { dbHelper.Close(); } return retInt; } /// <summary> /// 执行更新操作 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="mapping">实体映射</param> /// <param name="placeHolders">占位符数组</param> /// <param name="param">占位符对应的值数组</param> /// <returns>影响的行数</returns> public int update(string sql, IEntityMapping mapping, string[] placeHolders, object[] param) { int retInt = 0; try { dbHelper.GetConn(); retInt = dbHelper.ExcuteUpdte(sql, placeHolders, param); } catch (Exception ex) { throw new MysqlException(ex.Message, ex); } finally { dbHelper.Close(); } return retInt; } }mapping例子：
/// <summary> /// 入口地址参数映射实现 /// </summary> public class EntryAddrParamsMapping : IEntityMapping { public object mapping(MySqlDataReader reader) { int i = 0; return new EntryUrlParams(reader.GetInt32(i++), reader.GetInt32(i++), reader.GetInt32(i++), reader.GetInt32(i++), reader.GetFloat(i++)); } }
0 0
