public int SQLExecuteCommand(string sSQL, string cConnectString = "", string cDatabaseType = "", string cProviderName = "")
{
int bRet = 0;
if (string.IsNullOrEmpty(cConnectString))
{
cConnectString = _ConnectString;
}
if (string.IsNullOrEmpty(cDatabaseType))
{
cDatabaseType = _DatabaseType;
}
if (string.IsNullOrEmpty(cProviderName))
{
cProviderName = _DBProvider;
}
try
{
DbProviderFactory iFactory = DBFactoryUtil.GetDbProviderFactoryFromConfigRow();
using (SQLConn = iFactory.CreateConnection())
{
SQLConn.ConnectionString = cConnectString;
SQLConn.Open();
using (DbCommand SQLCmd = SQLConn.CreateCommand())
{
SQLCmd.CommandText = TransformSQL(sSQL, cDatabaseType);
bRet = SQLCmd.ExecuteNonQuery();
}
}
}
catch (DbException DBEx)
{
logger.Log(LogLevel.Error, $"[DBFactory][SQLExecuteCommand] { DBEx.Message } | {sSQL}");
if (DBEx.Message.Contains("appindex_smsq"))
{
bRet = -2;
}
else
{
bRet = -1;
}
}
catch (Exception ex)
{
var logStr = $"[DBFactory][SQLExecuteCommand] Error :: {ex.Message} | { ex.InnerException?.Message } { ex.InnerException?.InnerException?.Message}| {sSQL}";
logger.Log(LogLevel.Error, logStr);
if (ex.Message.Contains("appindex_smsq"))
{
bRet = -2;
}
else
{
bRet = -1;
}
}
return(bRet);
}
public int SQLSelectCount(string sSQL, string cConnectString = "", string cDatabaseType = "", string cProviderName = "")
{
int bRet = 0;
if (string.IsNullOrEmpty(cConnectString))
{
cConnectString = _ConnectString;
}
if (string.IsNullOrEmpty(cDatabaseType))
{
cDatabaseType = _DatabaseType;
}
if (string.IsNullOrEmpty(cProviderName))
{
cProviderName = _DBProvider;
}
try
{
DbProviderFactory iFactory = DBFactoryUtil.GetDbProviderFactoryFromConfigRow();
using (SQLConn = iFactory.CreateConnection())
{
SQLConn.ConnectionString = cConnectString;
SQLConn.Open();
using (DbCommand SQLCmd = SQLConn.CreateCommand())
{
SQLCmd.CommandText = TransformSQL(sSQL, cDatabaseType);
using (DbDataReader RDR = SQLCmd.ExecuteReader())
{
if (RDR.HasRows)
{
while (RDR.Read())
{
bRet = RDR.GetInt32(0);
}
}
}
}
}
}
catch (DbException DBEx)
{
bRet = -1;
logger.Log(LogLevel.Error, $"[DBFactory][SQLSelectCount] { DBEx.Message } | {sSQL}");
}
catch (Exception ex)
{
bRet = -1;
var logStr = $"[DBFactory][SQLSelectCount] Error :: {ex.Message} | { ex.InnerException?.Message } { ex.InnerException?.InnerException?.Message}| {sSQL}";
logger.Log(LogLevel.Error, logStr);
}
return(bRet);
}
public int SQLSelectMultiRow(string sSQL, object[] strParam, ref List <object> strResult, string cConnectString = "", string cDatabaseType = "", string cProviderName = "")
{
int bRet = 0;
int pknt = 0;
if (string.IsNullOrEmpty(cConnectString))
{
cConnectString = _ConnectString;
}
if (string.IsNullOrEmpty(cDatabaseType))
{
cDatabaseType = _DatabaseType;
}
if (string.IsNullOrEmpty(cProviderName))
{
cProviderName = _DBProvider;
}
try
{
DbProviderFactory iFactory = DBFactoryUtil.GetDbProviderFactoryFromConfigRow();
using (SQLConn = iFactory.CreateConnection())
{
SQLConn.ConnectionString = cConnectString;
SQLConn.Open();
using (DbCommand SQLCmd = SQLConn.CreateCommand())
{
SQLCmd.CommandText = TransformSQL(sSQL, cDatabaseType);
pknt = strParam.Length;
DbParameter p = default(DbParameter);
for (int j = 0; j <= pknt - 1; j++)
{
p = iFactory.CreateParameter();
p.DbType = GetDbTypeByName(Convert.IsDBNull(strParam[j]) ? "DBNULL" : strParam[j].GetType().Name);
p.Value = strParam[j];
p.ParameterName = (j + 1).ToString();
SQLCmd.Parameters.Add(p);
}
using (DbDataReader RDR = SQLCmd.ExecuteReader())
{
if (RDR.HasRows)
{
while (RDR.Read())
{
strResult[bRet] = RDR[0].ToString();
bRet += 1;
}
}
}
}
}
}
catch (DbException DBEx)
{
logger.Log(LogLevel.Error, "[SQLSelectSimple-1] " + DBEx.Message + "{" + sSQL + "}");
}
catch (Exception ex)
{
logger.Log(LogLevel.Error, "[SQLSelectSimple-1] " + ex.Message + "{" + sSQL + "}");
}
return(bRet);
}
public bool SQLSelectCountBool(string sSQL, object[] strParam, string cConnectString = "", string cDatabaseType = "", string cProviderName = "")
{
int bRet = 0;
int pknt = 0;
if (string.IsNullOrEmpty(cConnectString))
{
cConnectString = _ConnectString;
}
if (string.IsNullOrEmpty(cDatabaseType))
{
cDatabaseType = _DatabaseType;
}
if (string.IsNullOrEmpty(cProviderName))
{
cProviderName = _DBProvider;
}
try
{
DbProviderFactory iFactory = DBFactoryUtil.GetDbProviderFactoryFromConfigRow();
using (SQLConn = iFactory.CreateConnection())
{
SQLConn.ConnectionString = cConnectString;
SQLConn.Open();
using (DbCommand SQLCmd = SQLConn.CreateCommand())
{
SQLCmd.CommandText = TransformSQL(sSQL, cDatabaseType);
pknt = strParam.Length;
DbParameter p = default(DbParameter);
for (int j = 0; j <= pknt - 1; j++)
{
p = iFactory.CreateParameter();
p.DbType = GetDbTypeByName(Convert.IsDBNull(strParam[j]) ? "DBNULL" : strParam[j].GetType().Name);
p.Value = strParam[j];
p.ParameterName = (j + 1).ToString();
SQLCmd.Parameters.Add(p);
}
using (DbDataReader i = SQLCmd.ExecuteReader())
{
if (i.HasRows)
{
while (i.Read())
{
bRet = i.GetInt32(0);
}
}
}
}
}
}
catch (DbException DBEx)
{
bRet = -1;
logger.Log(LogLevel.Error, $"[SMSCHubClient][SQLSelectIntValue] { DBEx.Message } | {sSQL}");
}
catch (Exception ex)
{
bRet = -1;
var logStr = $"[SMSCHubClient][SQLSelectIntValue] Error :: {ex.Message} | { ex.InnerException?.Message } { ex.InnerException?.InnerException?.Message}| {sSQL}";
logger.Log(LogLevel.Error, logStr);
}
return(bRet == 0 ? false : true);
}
//public bool CheckDBConn(string cConnectString = "", string cDatabaseType = "", string cProviderName = "")
//{
// bool bRet = false;
// string sql = string.Empty;
// if (string.IsNullOrEmpty(cConnectString))
// cConnectString = _ConnectString;
// if (string.IsNullOrEmpty(cDatabaseType))
// cDatabaseType = _DatabaseType;
// if (string.IsNullOrEmpty(cProviderName))
// cProviderName = _DBProvider;
// try
// {
// //2018-05-24 04:52:59.4375 INFO Host=10.132.0.3;Port=5432;Database=smshubdb;Username=smshub;Password=satellite1$; | POSTGRES| Npgsql
// //2018-05-24 04:52:59.6563 INFO Open
// //2018-05-24 04:52:59.6719 INFO select count(1) as checkdb from Setting
// DbProviderFactory iFactory = DBFactoryUtil.GetDbProviderFactoryFromConfigRow();
// using (SQLConn = iFactory.CreateConnection())
// {
// SQLConn.ConnectionString = cConnectString;
// SQLConn.Open();
// using (DbCommand SQLCmd = SQLConn.CreateCommand())
// {
// SQLCmd.CommandText = TransformSQL(SQLLib.GetCheckDBConn, Util._dbType);
// using (DbDataReader i = SQLCmd.ExecuteReader())
// {
// if (i.HasRows)
// {
// while (i.Read())
// {
// bRet = true;
// }
// }
// }
// }
// SQLConn.Close();
// }
// }
// catch (DbException DBEx)
// {
// logger.Log(LogLevel.Error, $"[DBFactory][SQLExecuteCommand] { DBEx.Message } | {sql}");
// }
// catch (Exception ex)
// {
// var logStr = $"[DBFactory][SQLExecuteCommand] Error :: {ex.Message} | { ex.InnerException?.Message } | { ex.InnerException?.InnerException?.Message}| {sql}";
// logger.Log(LogLevel.Error, logStr);
// var logStr2 = $"[DBFactory][SQLExecuteCommand] StackTrace :: {ex.StackTrace}";
// logger.Log(LogLevel.Error, logStr2);
// }
// return bRet;
//}
public int SQLExecuteCommand(string sSQL, object[] strParam, string cConnectString = "", string cDatabaseType = "", string cProviderName = "")
{
int bRet = 0;
int pknt = 0;
string sql = string.Empty;
if (string.IsNullOrEmpty(cConnectString))
{
cConnectString = _ConnectString;
}
if (string.IsNullOrEmpty(cDatabaseType))
{
cDatabaseType = _DatabaseType;
}
if (string.IsNullOrEmpty(cProviderName))
{
cProviderName = _DBProvider;
}
try
{
DbProviderFactory iFactory = DBFactoryUtil.GetDbProviderFactoryFromConfigRow();
StringBuilder sb = new StringBuilder();
using (SQLConn = iFactory.CreateConnection())
{
SQLConn.ConnectionString = cConnectString;
SQLConn.Open();
using (DbCommand SQLCmd = SQLConn.CreateCommand())
{
SQLCmd.CommandText = TransformSQL(sSQL, cDatabaseType);
sql = SQLCmd.CommandText;
pknt = strParam.Length;
DbParameter p = default(DbParameter);
for (int j = 0; j <= pknt - 1; j++)
{
if ((strParam[j] == null))
{
strParam[j] = "null";
}
p = iFactory.CreateParameter();
if (strParam[j].ToString() == "-9")
{
p.DbType = DbType.Int32;
p.Value = DBNull.Value;
}
else if (strParam[j].ToString() == "01-01-01")
{
p.DbType = DbType.DateTime;
p.Value = DBNull.Value;
}
else
{
//p.DbType = GetDbTypeByName(string.IsNullOrEmpty(strParam[j].ToString()) ? "DBNULL" : strParam[j].GetType().Name);
//p.Value = strParam[j] == null ? DBNull.Value : strParam[j];
p.DbType = GetDbTypeByName(strParam[j].ToString() == "null" ? "DBNULL" : strParam[j].GetType().Name);
p.Value = strParam[j].ToString() == "null" ? DBNull.Value : strParam[j];
}
p.ParameterName = (j + 1).ToString();
SQLCmd.Parameters.Add(p);
//sb.Append($"{p.Value.ToString()}:{p.DbType}, ");
}
//logger.Log(LogLevel.Info, sb.ToString());
bRet = SQLCmd.ExecuteNonQuery();
}
}
}
catch (DbException DBEx)
{
if (DBEx.Message.Contains("appindex_smsq"))
{
bRet = -2;
}
else
{
bRet = -1;
logger.Log(LogLevel.Error, $"[DBFactory][SQLExecuteCommand] { DBEx.Message } | {sql}");
}
}
catch (Exception ex)
{
var logStr = $"[DBFactory][SQLExecuteCommand] Error :: {ex.Message} | { ex.InnerException?.Message } { ex.InnerException?.InnerException?.Message}| {sql}";
logger.Log(LogLevel.Error, logStr);
if (ex.Message.Contains("appindex_smsq"))
{
bRet = -2;
}
else
{
bRet = -1;
}
}
//if (bRet == 0)
//{
// string str = "";
// for (int j = 0; j <= strParam.Length - 1; j++)
// {
// str = str + strParam[j].ToString();
// }
// logger.Log(LogLevel.Error, $"{ str}");
// logger.Log(LogLevel.Error, $"{ sql}");
//}
return(bRet);
}
public int SQLExecuteCommandTranx(string[] sSQL, object[] strParam, string cConnectString = "", string cDatabaseType = "", string cProviderName = "")
{
int bRet = 0;
string sql = string.Empty;
if (string.IsNullOrEmpty(cConnectString))
{
cConnectString = _ConnectString;
}
if (string.IsNullOrEmpty(cDatabaseType))
{
cDatabaseType = _DatabaseType;
}
if (string.IsNullOrEmpty(cProviderName))
{
cProviderName = _DBProvider;
}
using (TransactionScope ts = new TransactionScope())
{
try
{
/// DbProviderFactory iFactory = DbProviderFactories.GetFactory(cProviderName);
DbProviderFactory iFactory = DBFactoryUtil.GetDbProviderFactoryFromConfigRow();
StringBuilder sb = new StringBuilder();
using (SQLConn = iFactory.CreateConnection())
{
SQLConn.ConnectionString = cConnectString;
DbCommand[] SQLCmd = new DbCommand[strParam.Length];
for (Int32 i = 0; i < strParam.Length; i++)
{
SQLCmd[i] = SQLConn.CreateCommand();
SQLCmd[i].CommandText = TransformSQL(sSQL[i], cDatabaseType);
if (strParam[i] != null)
{
object[] param = strParam[i] as object[];
SQLCmd[i].Parameters.AddRange(ParamCollection(param, iFactory));
}
}
SQLConn.Open();
try
{
for (Int32 i = 0; i < strParam.Length; i++)
{
if (SQLCmd[i] != null)
{
SQLCmd[i].ExecuteNonQuery();
}
}
ts.Complete();
bRet = 1;
}
catch (InvalidCastException)
{
ts.Dispose();
SQLConn.Close();
throw;
}
catch (Exception)
{
ts.Dispose();
SQLConn.Close();
throw;
}
}
}
catch (DbException ex)
{
bRet = -1;
var logStr = $"[DBFactory][SQLExecuteCommandTranx] Error :: {ex.Message} | { ex.InnerException?.Message } { ex.InnerException?.InnerException?.Message}| {sql}";
logger.Log(LogLevel.Error, logStr);
SQLConn.Close();
throw;
}
catch (Exception ex)
{
bRet = -1;
var logStr = $"[DBFactory][SQLExecuteCommandTranx] Error :: {ex.Message} | { ex.InnerException?.Message } { ex.InnerException?.InnerException?.Message}| {sql}";
logger.Log(LogLevel.Error, logStr);
SQLConn.Close();
throw;
}
}
return(bRet);
}
