コード例 #1
0
        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);
        }
コード例 #2
0
        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);
        }
コード例 #3
0
        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);
        }
コード例 #4
0
        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);
        }
コード例 #5
0
        //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);
        }
コード例 #6
0
        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);
        }