/// <summary>
/// 执行带参数的SQL语句，获取首行首列
/// </summary>
public object ExecuteScalar(string _ISQL, Dictionary <string, string> _IParms, bool _IsRealData = false)
{
object reVal = null;
using (MySqlConnection SqlConn = new MySqlConnection(ConnectionString))
{
using (MySqlCommand cmd = new MySqlCommand())
{
try
{
string _HashKey = ConnectionString + "-ExecuteScalar-" + _ISQL;
foreach (string _FParm in _IParms.Keys)
{
_HashKey += "-" + _FParm + "-" + _IParms[_FParm];
}
OwnCacheManage.ClearCache <object>(_CacheOBJ, _CacheOBJTime, CacheConfig.DBSelectTimeOut, _HashKey);
if (!_IsRealData && _CacheOBJ.ContainsKey(_HashKey))
{
reVal = _CacheOBJ[_HashKey];
}
else
{
PrepareCommand(cmd, SqlConn, null, CommandType.Text, _ISQL, _IParms);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (!((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))))
{
reVal = obj;
OwnCacheManage.SaveCache <object>(_CacheOBJ, _CacheOBJTime, _HashKey, reVal);
}
}
}
catch (MySqlException ex)
{
string strParms = string.Empty;
foreach (string Key in _IParms.Keys)
{
strParms += Key + ":" + _IParms[Key] + "\r\n";
}
ex.Data.Add("Sql", _ISQL);
ex.Data.Add("SqlParms", strParms);
throw ex;
}
finally
{
cmd.Dispose();
if (SqlConn.State != ConnectionState.Closed)
{
SqlConn.Close();
}
}
}
}
return(reVal);
}
/// <summary>
/// 执行带参数的存储过程，获取首行首列
/// </summary>
/// <param name="_IStoredProcName">存储过程名称</param>
/// <param name="_IParms">参数列表</param>
public object RunProcedureScalar(string _IStoredProcName, DbParameter[] _IParms, bool _IsRealData = false)
{
object reVal = null;
using (SqlConnection SqlConn = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
string _HashKey = ConnectionString + "-RunProcedureScalar-" + _IStoredProcName;
foreach (DbParameter _FParm in _IParms)
{
_HashKey += "-" + _FParm.ParameterName + "-" + _FParm.Value;
}
OwnCacheManage.ClearCache <object>(_CacheOBJ, _CacheOBJTime, CacheConfig.DBSelectTimeOut, _HashKey);
if (!_IsRealData && _CacheOBJ.ContainsKey(_HashKey))
{
reVal = _CacheOBJ[_HashKey];
}
else
{
PrepareCommand(cmd, SqlConn, null, CommandType.StoredProcedure, _IStoredProcName, _IParms);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (!((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))))
{
reVal = obj;
OwnCacheManage.SaveCache <object>(_CacheOBJ, _CacheOBJTime, _HashKey, reVal);
}
}
}
catch (SqlException ex)
{
string strParms = string.Empty;
foreach (DbParameter parm in _IParms)
{
strParms += parm.ParameterName + ":" + parm.Value + "\r\n";
}
ex.Data.Add("StoredProcedureName", _IStoredProcName);
ex.Data.Add("SqlParms", _IParms);
throw ex;
}
finally
{
cmd.Dispose();
if (SqlConn.State != ConnectionState.Closed)
{
SqlConn.Close();
}
}
}
}
return(reVal);
}
/// <summary>
/// 执行带参数的存储过程，获取DataTable对象
/// </summary>
public DataTable RunProcedureDataTable(string _IStoredProcName, Dictionary <string, string> _IParms, bool _IsRealData = false)
{
DataTable reVal = new DataTable();
string _HashKey = ConnectionString + "-RunProcedureDataTable-" + _IStoredProcName;
foreach (string _FParm in _IParms.Keys)
{
_HashKey += "-" + _FParm + "-" + _IParms[_FParm];
}
OwnCacheManage.ClearCache <DataTable>(_CacheDataTable, _CacheDataTableDateTime, CacheConfig.DBSelectTimeOut, _HashKey);
if (!_IsRealData && _CacheDataTable.ContainsKey(_HashKey))
{
reVal = _CacheDataTable[_HashKey];
}
else
{
using (MySqlConnection SqlConn = new MySqlConnection(ConnectionString))
{
using (MySqlCommand cmd = new MySqlCommand())
{
try
{
PrepareCommand(cmd, SqlConn, null, CommandType.StoredProcedure, _IStoredProcName, _IParms);
MySqlDataReader _reader = cmd.ExecuteReader();
reVal.Load(_reader);
_reader.Close();
_reader.Dispose();
cmd.Parameters.Clear();
OwnCacheManage.SaveCache <DataTable>(_CacheDataTable, _CacheDataTableDateTime, _HashKey, reVal);
}
catch (MySqlException ex)
{
string strParms = string.Empty;
foreach (string Key in _IParms.Keys)
{
strParms += Key + ":" + _IParms[Key] + "\r\n";
}
ex.Data.Add("StoredProcedureName", _IStoredProcName);
ex.Data.Add("SqlParms", strParms);
throw ex;
}
finally
{
cmd.Dispose();
if (SqlConn.State != ConnectionState.Closed)
{
SqlConn.Close();
}
}
}
}
}
return(reVal);
}
/// <summary>
/// 执行带参数的SQL语句，获取DataTable对象
/// </summary>
public DataTable ExecuteDataTable(string _ISQL, DbParameter[] _IParms, bool _IsRealData = false)
{
DataTable reVal = new DataTable();
string _HashKey = ConnectionString + "-ExecuteDataTable-" + _ISQL;
foreach (DbParameter _FParm in _IParms)
{
_HashKey += "-" + _FParm.ParameterName + "-" + _FParm.Value;
}
OwnCacheManage.ClearCache <DataTable>(_CacheDataTable, _CacheDataTableDateTime, CacheConfig.DBSelectTimeOut, _HashKey);
if (_IsRealData && _CacheDataTable.ContainsKey(_HashKey))
{
reVal = _CacheDataTable[_HashKey];
}
else
{
using (SQLiteConnection sqliteConn = new SQLiteConnection(ConnectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
try
{
PrepareCommand(cmd, sqliteConn, null, CommandType.Text, _ISQL, _IParms);
SQLiteDataReader _reader = cmd.ExecuteReader();
reVal.Load(_reader);
_reader.Close();
_reader.Dispose();
cmd.Parameters.Clear();
OwnCacheManage.SaveCache <DataTable>(_CacheDataTable, _CacheDataTableDateTime, _HashKey, reVal);
}
catch (SQLiteException ex)
{
string strParms = string.Empty;
foreach (DbParameter parm in _IParms)
{
strParms += parm.ParameterName + ":" + parm.Value + "\r\n";
}
ex.Data.Add("Sql", _ISQL);
ex.Data.Add("SqlParms", strParms);
throw ex;
}
finally
{
cmd.Dispose();
if (sqliteConn.State != ConnectionState.Closed)
{
sqliteConn.Close();
}
}
}
}
}
return(reVal);
}
/// <summary>
/// 执行带参数的SQL语句，获取DataSet对象
/// </summary>
public DataSet ExecuteDataSet(string _ISQL, Dictionary <string, string> _IParms, bool _IsRealData = false)
{
DataSet reVal = new DataSet();
string _HashKey = ConnectionString + "-ExecuteDataSet-" + _ISQL;
foreach (string _FParm in _IParms.Keys)
{
_HashKey += "-" + _FParm + "-" + _IParms[_FParm];
}
OwnCacheManage.ClearCache <DataSet>(_CacheDataSet, _CacheDataSetTime, CacheConfig.DBSelectTimeOut, _HashKey);
if (!_IsRealData && _CacheDataSet.ContainsKey(_HashKey))
{
reVal = _CacheDataSet[_HashKey];
}
else
{
using (MySqlConnection SqlConn = new MySqlConnection(ConnectionString))
{
using (MySqlCommand cmd = new MySqlCommand())
{
try
{
PrepareCommand(cmd, SqlConn, null, CommandType.Text, _ISQL, _IParms);
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
adapter.Fill(reVal);
cmd.Parameters.Clear();
OwnCacheManage.SaveCache <DataSet>(_CacheDataSet, _CacheDataSetTime, _HashKey, reVal);
}
catch (MySqlException ex)
{
string strParms = string.Empty;
foreach (string Key in _IParms.Keys)
{
strParms += Key + ":" + _IParms[Key] + "\r\n";
}
ex.Data.Add("Sql", _ISQL);
ex.Data.Add("SqlParms", strParms);
throw ex;
}
finally
{
cmd.Dispose();
if (SqlConn.State != ConnectionState.Closed)
{
SqlConn.Close();
}
}
}
}
}
return(reVal);
}
/// <summary>
/// 执行带参数的存储过程，获取DataSet对象
/// </summary>
public DataSet RunProcedureDataSet(string _IStoredProcName, DbParameter[] _IParms, bool _IsRealData = false)
{
DataSet reVal = new DataSet();
string _HashKey = ConnectionString + "-RunProcedureDataSet-" + _IStoredProcName;
foreach (DbParameter _FParm in _IParms)
{
_HashKey += "-" + _FParm.ParameterName + "-" + _FParm.Value;
}
OwnCacheManage.ClearCache <DataSet>(_CacheDataSet, _CacheDataSetTime, CacheConfig.DBSelectTimeOut, _HashKey);
if (!_IsRealData && _CacheDataSet.ContainsKey(_HashKey))
{
reVal = _CacheDataSet[_HashKey];
}
else
{
using (MySqlConnection SqlConn = new MySqlConnection(ConnectionString))
{
using (MySqlCommand cmd = new MySqlCommand())
{
try
{
PrepareCommand(cmd, SqlConn, null, CommandType.StoredProcedure, _IStoredProcName, _IParms);
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
adapter.Fill(reVal);
cmd.Parameters.Clear();
OwnCacheManage.SaveCache <DataSet>(_CacheDataSet, _CacheDataSetTime, _HashKey, reVal);
}
catch (MySqlException ex)
{
string strParms = string.Empty;
foreach (DbParameter parm in _IParms)
{
strParms += parm.ParameterName + ":" + parm.Value + "\r\n";
}
ex.Data.Add("StoredProcedureName", _IStoredProcName);
ex.Data.Add("SqlParms", _IParms);
throw ex;
}
finally
{
cmd.Dispose();
if (SqlConn.State != ConnectionState.Closed)
{
SqlConn.Close();
}
}
}
}
}
return(reVal);
}
