/// <summary> /// 通过查询语句取数据到表 /// </summary> /// <param name="_selectStr"></param> /// <param name="_tableName"></param> /// <returns></returns> public static async Task <DataTable> Get_Data(string _selectStr, string _tableName) { DbDataReader rdr; DataTable _dt = new DataTable(_tableName); using (MySqlConnection cn = OpenConnection()) { try { MySqlCommand _cmd = new MySqlCommand(_selectStr, cn); using (rdr = await _cmd.ExecuteReaderAsync()) { FillTableByReader(_dt, rdr); } } catch (Exception e) { string _errmsg = string.Format("执行SQL语句出错,错误信息为:{0}!\n查询语句为:{1}\n:", e.Message, _selectStr); MysqlLogWriter.WriteSystemLog(_errmsg, "ERROR"); throw e; } finally { cn.Close(); } } return(_dt); }
/// <summary> /// 通过查询语句填充表 /// </summary> /// <param name="cn"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="commandParameters"></param> /// <returns></returns> public static async Task <DataTable> FillDataTable(MySqlConnection cn, MySqlTransaction txn, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) { MySqlCommand cmd = new MySqlCommand(); DataTable _dt = new DataTable("ResultTable"); try { PrepareCommand(cmd, cn, txn, cmdType, cmdText, commandParameters); DbDataReader rdr = await cmd.ExecuteReaderAsync(); FillTableByReader(_dt, rdr); rdr.Close(); } catch (Exception e) { string _errmsg = string.Format("执行SQL语句出错,错误信息为:{0}!\n类型:{1}\n查询语句为:{2}\n参数:", e.Message, cmdType, cmdText); if (commandParameters != null) { foreach (MySqlParameter _p in commandParameters) { _errmsg += string.Format("{0}={1}\n", _p.ParameterName, _p.Value.ToString()); } } MysqlLogWriter.WriteSystemLog(_errmsg, "ERROR"); throw; } return(_dt); }
public static MySqlDataReader ExecuteReader(MySqlConnection mySqlConnection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) { //Create the command and connection MySqlCommand cmd = new MySqlCommand(); try { //Prepare the command to execute PrepareCommand(cmd, mySqlConnection, null, cmdType, cmdText, commandParameters); //Execute the query, stating that the connection should close when the resulting datareader has been read MySqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return(rdr); } catch (Exception e) { string _errmsg = string.Format("执行SQL语句出错,错误信息为:{0}!\n类型:{1}\n查询语句为:{2}\n参数:", e.Message, cmdType, cmdText); if (commandParameters != null) { foreach (MySqlParameter _p in commandParameters) { _errmsg += string.Format("{0}={1}\n", _p.ParameterName, _p.Value.ToString()); } } MysqlLogWriter.WriteSystemLog(_errmsg, "ERROR"); //mySqlConnection.Close(); throw e; } }
public static async Task <object> ExecuteScalar(MySqlConnection connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) { try { MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, connectionString, null, cmdType, cmdText, commandParameters); object val = await cmd.ExecuteScalarAsync(); cmd.Parameters.Clear(); return(val); } catch (Exception e) { string _errmsg = string.Format("执行SQL语句出错,错误信息为:{0}!\n类型:{1}\n查询语句为:{2}\n参数:", e.Message, cmdType, cmdText); if (commandParameters != null) { foreach (MySqlParameter _p in commandParameters) { _errmsg += string.Format("{0}={1}\n", _p.ParameterName, _p.Value.ToString()); } } MysqlLogWriter.WriteSystemLog(_errmsg, "ERROR"); throw e; } }
public static int testpro() { try { //MySqlParameter[] parameters = { // new MySqlParameter("@id", MySqlDbType.Decimal), // new MySqlParameter("@cnt", MySqlDbType.Decimal), // }; //parameters[0].Value = 23423; //parameters[0].Direction = ParameterDirection.Input; //parameters[1].Value = null; //parameters[1].Direction = ParameterDirection.Output; //var t = MysqlDBHelper.RunProcedure("testpro", parameters); //return 1; using (MySqlConnection cn = MysqlDBHelper.OpenConnection()) { MySqlCommand myComm = new MySqlCommand("testpro", cn); myComm.CommandType = CommandType.StoredProcedure; var t = 0; MySqlParameter myParameter; myParameter = new MySqlParameter("@id", MySqlDbType.Decimal); myParameter.Value = 233; myParameter.Direction = ParameterDirection.Input; myComm.Parameters.Add(myParameter); MySqlParameter par; par = new MySqlParameter("@cnt", MySqlDbType.Decimal); par.Value = null; par.Direction = ParameterDirection.Output; myComm.Parameters.Add(par); myComm.ExecuteNonQuery(); var ret = par.Value; return(0); } } catch (Exception ex) { MysqlLogWriter.WriteSystemLog(ex.Message, "ERROR"); return(0); } }