/// <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);
}
