public QueryResult ExecuteStatement(string statement, QueryParameter queryParam)
{
StringBuilder logString = null;
QueryResult qResult = null;
IDbCommand dbCommand = null;
IDataReader dbReader = null;
IDbDataParameter dbParam = null;
DataTable dt = null;
string paramName = string.Empty;
int cursorNum = 1;
try
{
logString = new StringBuilder();
if (_dbConnection == null)
{
throw new Exception(string.Format("ไม่สามารถโหลด assembly {0} ได้เนื่องจาก\r\n{1}", _assemblyName, _exceptionTemporary.GetBaseException().Message));
}
_dbConnection.ConnectionString = _connectionString;
qResult = new QueryResult();
if (_dbConnection.State != ConnectionState.Open)
{
_dbConnection.Open();
}
dbCommand = CreateCommand(statement);
if (queryParam != null)
{
for (int i = 0; i < dbCommand.Parameters.Count; i++)
{
if (queryParam.Parameter.Count >= i)
{
dbParam = dbCommand.Parameters[i] as IDbDataParameter;
dbParam.Value = queryParam.Parameter[queryParam.Parameter.Keys.ElementAt(i)];
}
else
{
break;
}
}
}
dbReader = dbCommand.ExecuteReader();
while (!dbReader.IsClosed)
{
dt = new DataTable();
dt.Load(dbReader);
if (cursorNum == 1)
{
qResult.DataTable = dt;
}
else
{
qResult.AddOutputParam("data" + cursorNum, ConnectorUtil.DataTableToDictionary(dt, _dateTimeFormat, _cultureInfo));
}
cursorNum++;
}
for (int i = 0; i < dbCommand.Parameters.Count; i++)
{
dbParam = dbCommand.Parameters[i] as IDbDataParameter;
if (!dbParam.Direction.Equals(ParameterDirection.Output))
{
continue;
}
paramName = dbParam.ParameterName;
if (_paramNamePrefix.Length > 0)
{
paramName = paramName.Replace(_paramNamePrefix, string.Empty);
}
if (!_reservedOutputName.Contains(paramName.ToUpper()))
{
paramName = paramName.Replace(_outputReplace, string.Empty);
if (dbParam.Value == null || System.DBNull.Value.Equals(dbParam.Value))
{
qResult.AddOutputParam(paramName, dbParam.Value);
}
else if (dbParam.Value is DateTime)
{
qResult.AddOutputParam(paramName, ConnectorUtil.DateTimeToString((dbParam.Value as DateTime?)));
}
else if (dbParam.Value is IDataReader)
{
dt = new DataTable();
dt.Load(dbParam.Value as IDataReader);
qResult.AddOutputParam(paramName.ToLower(), ConnectorUtil.DataTableToDictionary(dt, _dateTimeFormat, _cultureInfo));
}
else
{
qResult.AddOutputParam(paramName, dbParam.Value);
}
}
else if (paramName.Equals("PO_DATA"))
{
dt = new DataTable();
dt.Load(dbParam.Value as IDataReader);
qResult.DataTable = dt;
}
else if (paramName.Equals("PO_TOTAL"))
{
if (!string.IsNullOrEmpty(dbParam.Value.ToString()))
{
qResult.Total = int.Parse(dbParam.Value.ToString());
}
}
else if (paramName.Equals("PO_STATUS"))
{
if (dbParam.Value.ToString().Equals("1"))
{
qResult.Success = true;
}
else
{
qResult.Success = false;
}
}
else if (paramName.Equals("PO_STATUS_MSG"))
{
qResult.Message = dbParam.Value.ToString();
}
}
logString.AppendLine(string.Format("<LOGFUNCTION>"));
if (queryParam.Parameter.ContainsKey("FN_ID") == true)
{
logString.AppendLine(string.Format("{0}", queryParam.Parameter["FN_ID"]));
}
logString.AppendLine(string.Format("</LOGFUNCTION>"));
if (qResult.Success != true)
{
logString.AppendLine(string.Format("{0}", qResult.Message));
logString.AppendLine(string.Format("<LOGSESSION>"));
logString.Append(LogSession());
logString.AppendLine(string.Format("</LOGSESSION>"));
logString.AppendLine("!!! Error !!!");
ConnectorUtil.WriteLogError(logString.ToString());
}
else
{
logString.AppendLine(string.Format("<LOGSESSION>"));
logString.Append(LogSession());
logString.AppendLine(string.Format("</LOGSESSION>"));
logString.AppendLine("!!! Completed !!!");
ConnectorUtil.WriteLogInfo(logString.ToString());
//Util.WriteLogDebug(qResult.ToJson());
}
}
catch (Exception ex)
{
qResult = new QueryResult(ex);
logString.AppendLine(string.Format("{0}", qResult.Message));
logString.AppendLine(string.Format("<LOGFUNCTION>"));
if (queryParam.Parameter.ContainsKey("FN_ID") == true)
{
logString.AppendLine(string.Format("{0}", queryParam.Parameter["FN_ID"]));
}
logString.AppendLine(string.Format("</LOGFUNCTION>"));
logString.AppendLine(string.Format("<LOGSESSION>"));
logString.Append(LogSession());
logString.AppendLine(string.Format("</LOGSESSION>"));
logString.AppendLine("!!! Error !!!");
ConnectorUtil.WriteLogError(logString.ToString(), ex);
}
finally
{
if (_dbConnection != null && _dbConnection.State == ConnectionState.Open)
{
_dbConnection.Close();
}
}
return(qResult);
}
public QueryResult ExecuteStoredProcedure(string storeName, QueryParameter queryParam)
{
StringBuilder logString = null;
QueryResult qResult = null;
IDbCommand dbCommand = null;
IDataReader dbReader = null;
IDbDataParameter dbParam = null;
//IDbTransaction dbTrans = null;
DataTable dt = null;
string paramName = string.Empty;
int cursorNum = 1;
try
{
logString = new StringBuilder();
logString.AppendLine(string.Format("\tAPP_DATA_PROCEDURE: {0}", storeName));
if (_dbConnection == null)
{
if (_exceptionTemporary != null)
{
throw new Exception(string.Format("ไม่สามารถโหลด assembly {0} ได้เนื่องจาก\r\n{1}", _assemblyName, _exceptionTemporary.GetBaseException().Message));
}
else
{
throw new Exception(string.Format("ไม่สามารถโหลด assembly {0} ได้", _assemblyName));
}
}
_dbConnection.ConnectionString = _connectionString;
if (_provider.Equals(ProviderFactory.MySQL))
{
throw new Exception("Provider ไม่รองรับการ Execute โดยใช้ StoredProcedure");
}
if (_dbConnection.State != ConnectionState.Open)
{
_dbConnection.Open();
}
qResult = new QueryResult();
dbCommand = CreateCommand(storeName, queryParam, ref logString);
//dbTrans = _dbConnection.BeginTransaction();
switch (_provider)
{
case ProviderFactory.MSSQL:
dbReader = dbCommand.ExecuteReader();
while (!dbReader.IsClosed)
{
dt = new DataTable();
dt.Load(dbReader);
if (cursorNum == 1)
{
qResult.DataTable = dt;
}
else
{
qResult.AddOutputParam("data" + cursorNum, ConnectorUtil.DataTableToDictionary(dt, _dateTimeFormat, _cultureInfo));
}
cursorNum++;
}
break;
default:
dbCommand.ExecuteNonQuery();
break;
}
for (int i = 0; i < dbCommand.Parameters.Count; i++)
{
dbParam = dbCommand.Parameters[i] as IDbDataParameter;
if (!dbParam.Direction.Equals(ParameterDirection.Output))
{
continue;
}
paramName = dbParam.ParameterName;
if (_paramNamePrefix.Length > 0)
{
paramName = paramName.Replace(_paramNamePrefix, string.Empty);
}
if (!_reservedOutputName.Contains(paramName.ToUpper()))
{
paramName = paramName.Replace(_outputReplace, string.Empty);
if (dbParam.Value == null || System.DBNull.Value.Equals(dbParam.Value))
{
qResult.AddOutputParam(paramName, dbParam.Value);
}
else if (dbParam.Value is DateTime)
{
qResult.AddOutputParam(paramName, ConnectorUtil.DateTimeToString((dbParam.Value as DateTime?)));
}
else if (dbParam.Value is IDataReader)
{
dt = new DataTable();
if (dbParam.Value != System.DBNull.Value)
{
dt.Load(dbParam.Value as IDataReader);
}
qResult.AddOutputParam(paramName.ToLower(), ConnectorUtil.DataTableToDictionary(dt, _dateTimeFormat, _cultureInfo));
}
else
{
if (paramName.IndexOf("NT_") == 0)
{
qResult.AddNTParam(paramName, dbParam.Value);
}
else
{
qResult.AddOutputParam(paramName, dbParam.Value);
}
}
}
else if (paramName.Equals("PO_DATA"))
{
dt = new DataTable();
if (dbParam.Value != System.DBNull.Value)
{
dt.Load(dbParam.Value as IDataReader);
}
qResult.DataTable = dt;
}
else if (paramName.Equals("PO_TOTAL"))
{
if (!string.IsNullOrEmpty(dbParam.Value.ToString()))
{
qResult.Total = int.Parse(dbParam.Value.ToString());
}
}
else if (paramName.Equals("PO_STATUS"))
{
if (dbParam.Value.ToString().Equals("1"))
{
qResult.Success = true;
}
else
{
qResult.Success = false;
}
}
else if (paramName.Equals("PO_STATUS_MSG"))
{
qResult.Message = dbParam.Value.ToString();
}
}
logString.AppendLine(string.Format("<LOGFUNCTION>"));
if (queryParam.Parameter.ContainsKey("FN_ID") == true)
{
logString.AppendLine(string.Format("{0}", queryParam.Parameter["FN_ID"]));
}
logString.AppendLine(string.Format("</LOGFUNCTION>"));
if (qResult.Success != true)
{
logString.AppendLine(string.Format("{0}", qResult.Message));
logString.AppendLine(string.Format("<LOGSESSION>"));
logString.Append(LogSession());
logString.AppendLine(string.Format("</LOGSESSION>"));
logString.AppendLine("!!! Error !!!");
ConnectorUtil.WriteLogError(logString.ToString());
}
else
{
logString.AppendLine(string.Format("<LOGSESSION>"));
logString.Append(LogSession());
logString.AppendLine(string.Format("</LOGSESSION>"));
logString.AppendLine("!!! Completed !!!");
ConnectorUtil.WriteLogInfo(logString.ToString());
//Util.WriteLogDebug(qResult.ToJson());
}
//try
//{
// dbTrans.Commit();
//}
//catch { }
}
catch (Exception ex)
{
//try
//{
// dbTrans.Rollback();
//}
//catch { }
qResult = new QueryResult(ex);
logString.AppendLine(string.Format("{0}", qResult.Message));
logString.AppendLine(string.Format("<LOGFUNCTION>"));
if (queryParam.Parameter.ContainsKey("FN_ID") == true)
{
logString.AppendLine(string.Format("{0}", queryParam.Parameter["FN_ID"]));
}
logString.AppendLine(string.Format("</LOGFUNCTION>"));
logString.AppendLine(string.Format("<LOGSESSION>"));
logString.Append(LogSession());
logString.AppendLine(string.Format("</LOGSESSION>"));
logString.AppendLine("!!! Error !!!");
ConnectorUtil.WriteLogError(logString.ToString());
}
finally
{
//try
//{
// dbTrans.Dispose();
// dbTrans = null;
//}
//catch { }
if (_dbConnection != null && _dbConnection.State == ConnectionState.Open)
{
_dbConnection.Close();
_dbConnection.Dispose();
}
}
return(qResult);
}