/// <summary> /// addRow: 添加树节点 /// </summary> /// <param name="_table">表名</param> /// <param name="_keys">字段列表</param> /// <param name="_values">值列表</param> /// <returns></returns> public string addRow(string _table, string _keys, string _values) { if (Native.isEmpty(_keys) || Native.isEmpty(_values)) { return(""); } string _result = String.Empty; string sql = MySqlString.getInsertStr(_table, _keys, _values, true); try { DBUtil.onExecSqlBefore(_CLASS, "addRow", sql); cmd.CommandText = @sql; state = ConnectionState.Executing; object _rObj = cmd.ExecuteScalar(); if (_rObj != null) { _result = _rObj.ToString(); } } catch (Exception e) { _result = Native.getErrorMsg(e.Message); DBUtil.onExecSqlError(_CLASS, "addRow", sql, e.Message); } return(_result); }
/// <summary> /// execHashJson: 普通执行Sql语句 /// </summary> /// <param name="sql">要执行的sql语句</param> /// <returns></returns> public override Json execHashJson(string sql, string key) { Json hash = null; try { DBUtil.onExecSqlBefore(_CLASS, "execJsonList", sql); using (MySqlConnection sqlConn = new MySqlConnection(dbInfo.toMyConnectionString())) { sqlConn.Open(); MySqlCommand sqlComm = sqlConn.CreateCommand(); sqlComm.CommandText = sql; using (IDataReader dr = sqlComm.ExecuteReader(CommandBehavior.Default)) { hash = DBUtil.DataReaderToHashJson(dr, key); dr.Close(); } sqlConn.Close(); } } catch (Exception ex) { DBUtil.onExecSqlError(_CLASS, "execJsonList", sql, ex.Message); } return(hash); }
/// <summary> /// getArrayResult: 得到数组结果集 /// </summary> /// <param name="sql">要执行的sql语句</param> /// <returns></returns> public override ArrayList getArrayListResult(string sql) { ArrayList dataSrc = null; DBUtil.onExecSqlBefore(_CLASS, "getArrayListResult", sql); try { using (MySqlConnection sqlConn = new MySqlConnection(dbInfo.toMyConnectionString())) { sqlConn.Open(); MySqlCommand sqlComm = sqlConn.CreateCommand(); sqlComm.CommandText = sql; using (IDataReader dr = sqlComm.ExecuteReader(CommandBehavior.Default)) { dataSrc = DBUtil.DataReaderToArrayList(dr); dr.Close(); } sqlConn.Close(); } } catch (Exception ex) { DBUtil.onExecSqlError(_CLASS, "getArrayListResult", sql, ex.Message); } return(dataSrc); }
/// <summary> /// execHashJson: 普通执行Sql语句 /// </summary> /// <param name="sql">要执行的sql语句</param> /// <returns></returns> public override ArrayList execJsonList(string sql) { ArrayList list = null; try { DBUtil.onExecSqlBefore(_CLASS, "execJsonList", sql); using (SqlConnection sqlConn = new SqlConnection(dbInfo.toMsConnectionString())) { sqlConn.Open(); SqlCommand sqlComm = sqlConn.CreateCommand(); sqlComm.CommandText = sql; using (SqlDataReader dr = sqlComm.ExecuteReader(CommandBehavior.Default)) { list = DBUtil.DataReaderToJsonList(dr); dr.Close(); } sqlConn.Close(); } } catch (Exception ex) { DBUtil.onExecSqlError(_CLASS, "execJsonList", sql, ex.Message); } return(list); }
public void rollback() { try { if (sqlTrans != null && sqlTrans.Connection != null) { sqlTrans.Rollback(); } close(); } catch (System.Data.SqlClient.SqlException e) { DBUtil.onExecSqlError(_CLASS, "rollback", "rollback", e.Message.ToString() + ", 实务回滚失败"); } }
public int execNonQuery(string sql) { int _result = 0; try { DBUtil.onExecSqlBefore(_CLASS, "execNonQuery", sql); cmd.CommandText = @sql; state = ConnectionState.Executing; _result = cmd.ExecuteNonQuery(); } catch (Exception e) { DBUtil.onExecSqlError(_CLASS, "execNonQuery", sql, e.Message); } return(_result); }
/// <summary> /// 普通执行Sql语句 -- 只适用于update, insert, delete, 存储过程 四种 /// </summary> /// <param name="sql">执行sql语言</param> /// <returns> /// 1: 如果执行成功则返回字符串1 /// 2: 如果执行失败则返回执行失败信息 /// </returns> public override string execTrans(string sql, string dataType, string reSplit, string rSplit, string cSplit) { DBUtil.onExecSqlBefore(_CLASS, "execTrans", sql); MySqlTransaction sqlTrans = null; MySqlConnection connection = getDbConnecton(); string value = String.Empty; try { sqlTrans = connection.BeginTransaction(); // 创建一个执行SQL事务的对象, 开启事务 MySqlCommand cmd = new MySqlCommand(); cmd.Connection = connection; cmd.Transaction = sqlTrans; //将执行事务的对象赋值给sqlCommand cmd.CommandText = @sql; using (IDataReader dr = cmd.ExecuteReader(CommandBehavior.Default)) { switch (dataType.ToLower()) { case "json": value = DBUtil.DataReaderToJsonArrayString(dr, reSplit); break; case "ary": case "array": value = DBUtil.DataReaderToArrayString(dr, reSplit); break; case "html": value = DBUtil.DataReaderToHtml(dr, rSplit, cSplit, reSplit); break; } dr.Close(); } sqlTrans.Commit(); //提交事务 connection.Close(); } catch (Exception ex) { if (sqlTrans != null) { sqlTrans.Rollback(); connection.Close(); } value = Native.getErrorMsg(ex.Message); DBUtil.onExecSqlError(_CLASS, "execTrans", sql, ex.Message); } return(value); }
/// <summary> /// execArrayList: 执行sql语句并得到ArrayList形式的结果集, ArrayList中每一个元素都是一个ArrayList的对象, 二这个对象中又是每一行数据是字符串数组(string [] row)的集合 /// </summary> /// <param name="sql">要执行的sql语句</param> /// <returns>ArrayList对象</returns> public ArrayList execArrayList(string sql) { ArrayList dataSrc = null; try { DBUtil.onExecSqlBefore(_CLASS, "execArrayList", sql); cmd.CommandText = @sql; state = ConnectionState.Executing; using (MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.Default)) { dataSrc = DBUtil.DataReaderToArrayList(dr); dr.Close(); } }catch (Exception e) { DBUtil.onExecSqlError(_CLASS, "execArrayList", sql, e.Message); } return(dataSrc); }
/// <summary> /// getQueryResultByType: 根据不同的dataType得到相应的查询结果 /// </summary> /// <param name="sql">要执行查询的sql语句</param> /// <param name="dataType">数据类型</param> /// <param name="reSplit">结果集分隔符</param> /// <param name="rSplit">行分隔符</param> /// <param name="cSplit">列分隔符</param> /// <returns>查询结果根据dataType而得到的字符串数据</returns> public override string getQueryResultByType(string sql, string dataType, string reSplit, string rSplit, string cSplit) { string value = String.Empty; DBUtil.onExecSqlBefore(_CLASS, "getQueryResultByType", sql); try { using (MySqlConnection sqlConn = new MySqlConnection(dbInfo.toMyConnectionString())) { sqlConn.Open(); MySqlCommand sqlComm = sqlConn.CreateCommand(); sqlComm.CommandText = sql; using (IDataReader dr = sqlComm.ExecuteReader(CommandBehavior.Default)) { switch (dataType.ToLower()) { case "json": value = DBUtil.DataReaderToJsonArrayString(dr, reSplit); break; case "json-object": value = DBUtil.DataReaderToJsonArrayString(dr, reSplit); break; case "ary": case "array": value = DBUtil.DataReaderToArrayString(dr, reSplit); break; case "html": value = DBUtil.DataReaderToHtml(dr, rSplit, cSplit, reSplit); break; } dr.Close(); } sqlConn.Close(); } } catch (Exception ex) { value = Native.getErrorMsg(ex.Message); DBUtil.onExecSqlError(_CLASS, "getQueryResultByType", sql, ex.Message); } return(value); }
/// <summary> /// 执行sql语句并得到每一行是Json节点的Json对象 /// </summary> /// <param name="sql">要执行的sql语句</param> /// <returns>HashJson</returns> public Json execHashJson(string sql, string key) { Json _json = null; try { DBUtil.onExecSqlBefore(_CLASS, "execHashJson", sql); cmd.CommandText = @sql; state = ConnectionState.Executing; using (MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.Default)) { _json = DBUtil.DataReaderToHashJson(dr, key); } } catch (Exception e) { DBUtil.onExecSqlError(_CLASS, "execHashJson", sql, e.Message); } return(_json); }
/// <summary> /// execDataSet: 执行sql语句并得到DataSet结果集 /// </summary> /// <param name="sql">要执行的sql语句</param> /// <returns>DataSet对象</returns> public DataSet execDataSet(string sql) { DataSet ds = new DataSet(); try { DBUtil.onExecSqlBefore(_CLASS, "execDataSet", sql); cmd.CommandText = @sql; state = ConnectionState.Executing; using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)) { da.Fill(ds); } } catch (Exception e) { DBUtil.onExecSqlError(_CLASS, "execDataSet", sql, e.Message); } return(ds); }
public string execScalar(string sql) { string _result = String.Empty; try { DBUtil.onExecSqlBefore(_CLASS, "execScalar", sql); cmd.CommandText = @sql; state = ConnectionState.Executing; object _rObj = cmd.ExecuteScalar(); if (_rObj != null) { _result = _rObj.ToString(); } } catch (Exception e) { _result = Native.getErrorMsg(e.Message); DBUtil.onExecSqlError(_CLASS, "execScalar", sql, e.Message); } return(_result); }
public override string execScalar(string sql) { string _result = String.Empty; try { DBUtil.onExecSqlBefore(_CLASS, "execScalar", sql); using (MySqlConnection sqlConn = new MySqlConnection(dbInfo.toMyConnectionString())) { sqlConn.Open(); MySqlCommand sqlComm = sqlConn.CreateCommand(); sqlComm.CommandText = sql; _result = sqlComm.ExecuteScalar().ToString(); sqlConn.Close(); } } catch (Exception ex) { DBUtil.onExecSqlError(_CLASS, "execScalar", sql, ex.Message); _result = Native.getErrorMsg(ex.Message); } return(_result); }
/// <summary> /// 普通执行Sql查询语句 -- 只适用于query, 存储过程 四种 /// </summary> /// <param name="sql">执行sql查询语言</param> /// <returns> /// 1: 如果执行成功则返回字符串1 /// 2: 如果执行失败则返回执行失败信息 /// </returns> public override DataSet execDataSet(string sql) { DataSet ds = new DataSet(); try { DBUtil.onExecSqlBefore(_CLASS, "execDataSet", sql); using (MySqlConnection sqlConn = new MySqlConnection(dbInfo.toMyConnectionString())) { sqlConn.Open(); MySqlCommand sqlComm = sqlConn.CreateCommand(); sqlComm.CommandText = sql; MySqlDataAdapter dr = new MySqlDataAdapter(sqlComm); dr.Fill(ds); sqlConn.Close(); } } catch (Exception ex) { DBUtil.onExecSqlError(_CLASS, "execDataSet", sql, ex.Message); } return(ds); }
/// <summary> /// 普通执行Sql语句 -- 只适用于update, insert, delete, 存储过程 四种 /// </summary> /// <param name="sql">执行sql语言</param> /// <returns> /// 1: 如果执行成功则返回字符串1 /// 2: 如果执行失败则返回执行失败信息 /// </returns> public override int execNonQuery(string sql) { int _result = 0; try { DBUtil.onExecSqlBefore(_CLASS, "execNonQuery", sql); using (MySqlConnection sqlConn = new MySqlConnection(dbInfo.toMyConnectionString())) { sqlConn.Open(); MySqlCommand sqlComm = sqlConn.CreateCommand(); sqlComm.CommandText = sql; _result = sqlComm.ExecuteNonQuery(); sqlConn.Close(); } } catch (Exception ex) { DBUtil.onExecSqlError(_CLASS, "execNonQuery", sql, ex.Message); _result = -1; } return(_result); }