public Dictionary <string, object> ToDictionary()
{
try
{
this._serializeObject = new Dictionary <string, object>();
this._serializeObject.Add("success", this.Success);
if (this.Total == 0 || this.Total == -1 && this._dataTable != null)
{
this._serializeObject.Add("total", this._dataTable.Rows.Count);
}
else
{
this._serializeObject.Add("total", this.Total);
}
this._serializeObject.Add("message", this.Message);
this._serializeObject.Add("data", ConnectorUtil.DataTableToDictionary(this._dataTable));
foreach (var param in OutputParameters)
{
if (this._serializeObject.ContainsKey(param.Key))
{
this._serializeObject[param.Key] = param.Value;
}
else
{
this._serializeObject.Add(param.Key, param.Value);
}
}
}
catch (Exception ex)
{
this._serializeObject = new Dictionary <string, object>();
this._serializeObject.Add("success", false);
this._serializeObject.Add("total", -1);
this._serializeObject.Add("message", "ไม่สามารถสร้าง Dictionary ได้เนื่องจาก : " + ex.Message);
this._serializeObject.Add("data", null);
//this._serializeObject.Add("error", new Dictionary<string, object>()
//{
// {"source",ex.Source},
// {"stackTrace",ex.StackTrace},
// {"hresult",ex.HResult}
//});
}
return(this._serializeObject);
}
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);
}
protected IDbCommand CreateCommand(string storeName, QueryParameter queryParam, ref StringBuilder logString)
{
IDbDataParameter dbParam = null;
//List<IDbDataParameter> paramList = null;
IDbCommand dbCommand = null;
string[] logHiddenParams = null;
try
{
//paramList = new List<IDbDataParameter>();
if (_dbConnection.State != ConnectionState.Open)
{
_dbConnection.Open();
}
dbCommand = _dbConnection.CreateCommand();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = storeName;
if (_provider.Equals(ProviderFactory.Oracle))
{
var oracleCommandBindByName = _commandType.GetProperty("BindByName");
oracleCommandBindByName.SetValue(dbCommand, true, null);
}
logHiddenParams = AppHttpHandler.AppHttpHandlerConfig.Logs.HideFields.ToArray();
_commandBuilderType.GetMethod("DeriveParameters").Invoke(null, new object[] { dbCommand });
for (int i = 0; i < dbCommand.Parameters.Count; i++)
{
string paramName = string.Empty;
object paramValue = null;
dbParam = dbCommand.Parameters[i] as IDbDataParameter;
switch (dbParam.Direction)
{
case ParameterDirection.Input:
if (queryParam != null && queryParam.Parameter != null)
{
if (dbParam.ParameterName.IndexOf(_inputReplace) != -1)
{
paramName = dbParam.ParameterName.Substring(dbParam.ParameterName.IndexOf(_inputReplace) + _inputReplace.Length);
}
else
{
paramName = dbParam.ParameterName;
}
if (queryParam.Parameter.ContainsKey(paramName.ToUpper()))
{
paramValue = queryParam.Parameter[paramName];
}
if (paramValue != null && ConnectorUtil.DBTypeMap(dbParam.DbType).Equals(typeof(DateTime)) || dbParam.Value is DateTime)
{
if (paramValue is string)
{
long milliseconds;
bool parseResult = long.TryParse((string)paramValue, out milliseconds);
if (parseResult)
{
dbParam.Value = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddMilliseconds(milliseconds).ToLocalTime();
}
else
{
dbParam.Value = ConnectorUtil.StringToDateTime(paramValue.ToString());
}
}
else if (paramValue is int)
{
dbParam.Value = ConnectorUtil.UnixTimeStampToDateTime(int.Parse(paramValue.ToString())).AddMinutes(TimeZoneOffset);
}
else if (paramValue is long)
{
dbParam.Value = ConnectorUtil.UnixTimeStampToDateTime(long.Parse(paramValue.ToString())).AddMinutes(TimeZoneOffset);
}
if (_provider == ProviderFactory.MSSQL)
{
logString.AppendLine(string.Format("{0}: PARSE('{1}' as DATETIME USING 'th-TH')", dbParam.ParameterName, ConnectorUtil.DateTimeToString(dbParam.Value as DateTime?)));
}
else if (_provider == ProviderFactory.Oracle)
{
logString.AppendLine(string.Format("{0}: TO_DATE('{1}','DD/MM/YYYY HH24:MI:SS','NLS_DATE_LANGUAGE=THAI')", dbParam.ParameterName, ConnectorUtil.DateTimeToString(dbParam.Value as DateTime?)));
}
break;
}
else if (paramValue != null)
{
if (dbParam.DbType != DbType.String && !string.IsNullOrEmpty(paramValue.ToString()))
{
dbParam.Value = paramValue;
}
else if (dbParam.DbType == DbType.String)
{
dbParam.Value = paramValue;
}
else
{
}
}
else
{
dbParam.Value = System.DBNull.Value;
}
}
object logValue = dbParam.Value;
if (logHiddenParams != null && logHiddenParams.Contains(dbParam.ParameterName.Replace(_inputReplace, "").ToUpper()))
{
logValue = "-- Hidden Field --";
}
logString.AppendLine(string.Format("{0}: {1}", dbParam.ParameterName, logValue));
break;
case ParameterDirection.InputOutput:
case ParameterDirection.Output:
dbParam.Direction = ParameterDirection.Output;
break;
//paramName = dbParam.ParameterName.Substring(dbParam.ParameterName.IndexOf(_outputReplace) + _outputReplace.Length);
//break;
default:
continue;
}
//paramList.Add(dbParam);
}
return(dbCommand);
}
catch (Exception ex)
{
throw ex;
}
}
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);
}
public DatabaseConnectorClass(string connectionString, ProviderFactory provider)
{
try
{
_connectionString = connectionString;
_provider = provider;
switch (_provider)
{
case ProviderFactory.MSSQL:
_paramNamePrefix = "@";
_connectionType = typeof(System.Data.SqlClient.SqlConnection);
_commandType = typeof(System.Data.SqlClient.SqlCommand);
_commandBuilderType = typeof(System.Data.SqlClient.SqlCommandBuilder);
_parameterType = typeof(System.Data.SqlClient.SqlParameter);
break;
case ProviderFactory.Oracle:
_paramNamePrefix = string.Empty;
_assemblyName = _OracleAssembly;
_connectionTypeName = string.Format("{0}.Client.OracleConnection", _assemblyName);
_commandTypeName = string.Format("{0}.Client.OracleCommand", _assemblyName);
_commandBuilderTypeName = string.Format("{0}.Client.OracleCommandBuilder", _assemblyName);
_parameterTypeName = string.Format("{0}.Client.OracleParameter", _assemblyName);
break;
case ProviderFactory.MySQL:
_paramNamePrefix = string.Empty;
_assemblyName = _MySqlAssembly;
_connectionTypeName = "MySql.Data.MySqlClient.MySqlConnection";
_commandTypeName = "MySql.Data.MySqlClient.MySqlCommand";
_commandBuilderTypeName = "MySql.Data.MySqlClient.MySqlCommandBuilder";
_parameterTypeName = "MySql.Data.MySqlClient.MySqlParameter";
break;
case ProviderFactory.PostgreSQL:
_paramNamePrefix = ":";
_assemblyName = _PostgreSqlAssembly;
_connectionTypeName = "Npgsql.NpgsqlConnection";
_commandTypeName = "Npgsql.NpgsqlCommand";
_commandBuilderTypeName = "Npgsql.NpgsqlCommandBuilder";
_parameterTypeName = "Npgsql.NpgsqlParameter";
break;
}
if (_connectionType == null)
{
if (!_dictAssemblyCache.ContainsKey(_assemblyName))
{
_dictAssemblyCache.TryAdd(_assemblyName, ConnectorUtil.AssemblyFromName(_assemblyName));
}
_connectionType = _dictAssemblyCache[_assemblyName].GetType(_connectionTypeName);
_commandType = _dictAssemblyCache[_assemblyName].GetType(_commandTypeName);
_commandBuilderType = _dictAssemblyCache[_assemblyName].GetType(_commandBuilderTypeName);
_parameterType = _dictAssemblyCache[_assemblyName].GetType(_parameterTypeName);
}
if (string.IsNullOrEmpty(_connectionString) && System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"] != null)
{
_connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
}
if (!string.IsNullOrEmpty(_connectionString))
{
_dbCoreConnection = Activator.CreateInstance(_connectionType, new object[] { _connectionString });
}
else
{
_dbCoreConnection = Activator.CreateInstance(_connectionType);
}
_dbConnection = _dbCoreConnection as IDbConnection;
}
catch (Exception ex)
{
_exceptionTemporary = ex;
}
}
