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