/// <summary>
/// 执行SQL语句,返回只读数据集
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="transaction">事务</param>
/// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
/// <param name="commandText">SQL语句或存储过程名称</param>
/// <param name="parms">查询参数</param>
/// <returns>返回只读数据集</returns>
private static MySqlDataReader ExecuteDataReader(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
{
#region 开始时间
Stopwatch stopwatch = Stopwatch.StartNew();
#endregion 开始时间
MySqlCommand command = new MySqlCommand();
PrepareCommand(command, connection, transaction, commandType, commandText, parms);
MySqlDataReader dr = null;
try
{
dr = command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
string strLog = "/*" + DateTime.Now.ToString("HH:mm:ss.fff") + "\t" + ex.Message.Replace("\r\n", String.Empty) + "*/ " + MySqlParameterCache.FormatSQLScript(String.Empty, commandType, commandText, parms).Replace("\r\n", String.Empty);
LogLogic.Write(strLog, ERROR_WRITE_LOG_PATH, ERROR_WRITE_LOG_EXTENSION);
}
#region 结束时间
stopwatch.Stop();
#endregion 结束时间
#region 输出SQLScript
if (MYSQL_SCRIPT_WRITE_LOG)
{
string strTestSQL = MySqlParameterCache.FormatSQLScript(String.Empty, commandType, commandText, parms);
SQLScriptWriteLog(stopwatch.ElapsedMilliseconds, strTestSQL.Replace("\r\n", String.Empty));
}
#endregion 输出SQLScript
return(dr);
}
/// <summary>
/// 执行SQL语句,返回结果集中的第一行第一列
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="transaction">事务</param>
/// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
/// <param name="commandText">SQL语句或存储过程名称</param>
/// <param name="parms">查询参数</param>
/// <returns>返回结果集中的第一行第一列</returns>
private static object ExecuteScalar(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
{
#region 开始时间
Stopwatch stopwatch = Stopwatch.StartNew();
#endregion 开始时间
MySqlCommand command = new MySqlCommand();
PrepareCommand(command, connection, transaction, commandType, commandText, parms);
object retval = command.ExecuteScalar();
command.Parameters.Clear();
#region 结束时间
stopwatch.Stop();
#endregion 结束时间
#region 输出SQLScript
if (MYSQL_SCRIPT_WRITE_LOG)
{
string strTestSQL = MySqlParameterCache.FormatSQLScript(String.Empty, commandType, commandText, parms);
SQLScriptWriteLog(stopwatch.ElapsedMilliseconds, strTestSQL.Replace("\r\n", String.Empty));
}
#endregion 输出SQLScript
return(retval);
}
/// <summary>
/// 执行SQL语句,返回结果集
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="transaction">事务</param>
/// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
/// <param name="commandText">SQL语句或存储过程名称</param>
/// <param name="parms">查询参数</param>
/// <returns>返回结果集</returns>
private static DataSet ExecuteDataSet(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
{
#region 开始时间
Stopwatch stopwatch = Stopwatch.StartNew();
#endregion 开始时间
MySqlCommand command = new MySqlCommand();
PrepareCommand(command, connection, transaction, commandType, commandText, parms);
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
DataSet ds = new DataSet();
try
{
adapter.Fill(ds);
}
catch (Exception ex)
{
string strLog = "/*" + DateTime.Now.ToString("HH:mm:ss.fff") + "\t" + ex.Message.Replace("\r\n", String.Empty) + "*/ " + MySqlParameterCache.FormatSQLScript(String.Empty, commandType, commandText, parms).Replace("\r\n", String.Empty);
LogLogic.Write(strLog, ERROR_WRITE_LOG_PATH, ERROR_WRITE_LOG_EXTENSION);
}
if (commandText.IndexOf("@") > 0)
{
string strCommandText = commandText.ToLower();
int index = strCommandText.IndexOf("where ");
if (index < 0)
{
index = strCommandText.IndexOf("\nwhere");
}
if (index > 0)
{
ds.ExtendedProperties.Add("SQL", strCommandText.Substring(0, index - 1)); //将获取的语句保存在表的一个附属数组里，方便更新时生成CommandBuilder
}
else
{
ds.ExtendedProperties.Add("SQL", strCommandText); //将获取的语句保存在表的一个附属数组里，方便更新时生成CommandBuilder
}
}
else
{
ds.ExtendedProperties.Add("SQL", commandText); //将获取的语句保存在表的一个附属数组里，方便更新时生成CommandBuilder
}
foreach (DataTable dt in ds.Tables)
{
dt.ExtendedProperties.Add("SQL", ds.ExtendedProperties["SQL"]);
}
command.Parameters.Clear();
#region 结束时间
stopwatch.Stop();
#endregion 结束时间
#region 输出SQLScript
if (MYSQL_SCRIPT_WRITE_LOG)
{
string strTestSQL = MySqlParameterCache.FormatSQLScript(String.Empty, commandType, commandText, parms);
SQLScriptWriteLog(stopwatch.ElapsedMilliseconds, strTestSQL.Replace("\r\n", String.Empty));
}
#endregion 输出SQLScript
return(ds);
}