예제 #1
0
/// <summary>
/// Create the temp table for the list if needed
/// </summary>
/// <param name="schema"></param>
/// <param name="name"></param>

        public static bool CreateListTableIfNeeded(
            string dsName,
            string listName)
        {
            DbCommandMx cmd     = null;
            bool        created = true;
            string      fullTableName;

            DataSourceMx ds = GetDataSourceInfo(ref dsName, ref listName, out fullTableName);

            if (Lists.ContainsKey(fullTableName))
            {
                return(false);
            }

            string sql = @"
				create global temporary table <schema.name> (
						rowPos integer,
						intKey integer,
						stringKey varchar2(256))
					on commit preserve rows"                    ;

            sql = sql.Replace("<schema.name>", fullTableName);

            try
            {
                DateTime t0 = DateTime.Now;
                cmd = new DbCommandMx();
                DbConnectionMx dbc = DbConnectionMx.GetConnection(dsName);
                cmd.MxConn = dbc;
                cmd.Prepare(sql);
                cmd.ExecuteNonReader();
                created = true;
                int tDelta = (int)TimeOfDay.Delta(t0);
                //DebugLog.Message("Create Table time: " + tDelta);
            }

            catch (Exception ex)
            {
                if (!ex.Message.Contains("ORA-xxx"))
                {
                    throw ex;                    // if other than already exists then throw exception
                }
                created = false;                 // already exists
            }

            finally { if (cmd != null)
                      {
                          cmd.Dispose();
                      }
            }

            TempDbList list = new TempDbList();

            list.DataSource      = ds;
            list.Name            = listName;
            Lists[fullTableName] = list;
            return(created);
        }
예제 #2
0
        /// <summary>
        /// Get the data source info and full name for the temp table
        /// </summary>
        /// <param name="dsName"></param>
        /// <param name="listName"></param>
        /// <param name="fullTableName"></param>
        /// <returns></returns>

        static DataSourceMx GetDataSourceInfo(
            ref string dsName,
            ref string listName,
            out string fullTableName)
        {
            dsName   = dsName.ToUpper();
            listName = listName.ToUpper();
            DataSourceMx ds = DataSourceMx.DataSources[dsName];

            fullTableName = ds.UserName.ToUpper() + "." + listName;
            return(ds);
        }
예제 #3
0
        public string DataSourceName = ""; // datasource name


        /// <summary>
        /// Get the DataSourceMx associated with a schema name
        /// </summary>
        /// <param name="schemaName"></param>
        /// <returns></returns>

        public static DataSourceMx GetDataSourceForSchemaName(string schemaName)
        {
            DbSchemaMx schema = GetSchemaInfoFromName(schemaName);

            string dsName = schema?.DataSourceName;

            if (Lex.IsUndefined(dsName))
            {
                return(null);
            }

            if (!DataSourceMx.DataSources.ContainsKey(dsName))
            {
                return(null);                                         // unknown datasource
            }
            DataSourceMx ds = DataSourceMx.DataSources[dsName];

            return(ds);
        }
예제 #4
0
/// <summary>
/// Be sure the table is truncated
/// </summary>
/// <param name="schema"></param>
/// <param name="listName"></param>

        public static void TruncateListTable(
            string dsName,
            string listName)
        {
            DbCommandMx cmd = null;
            string      fullTableName;

            DataSourceMx ds = GetDataSourceInfo(ref dsName, ref listName, out fullTableName);

            string sql = "truncate table " + fullTableName;

            try
            {
                DateTime t0 = DateTime.Now;
                cmd = new DbCommandMx();
                DbConnectionMx dbc = DbConnectionMx.GetConnection(dsName);
                cmd.MxConn = dbc;
                cmd.Prepare(sql);
                cmd.ExecuteNonReader();
                int tDelta = (int)TimeOfDay.Delta(t0);
                //DebugLog.Message("Truncate Table time: " + tDelta);
            }

            catch (Exception ex)             // if truncate failed see if need to create
            {
                if (!ex.Message.Contains("ORA-00942"))
                {
                    throw ex;                                                    // if other than already exists then throw exception
                }
                bool created = CreateListTableIfNeeded(dsName, listName);
            }

            finally { if (cmd != null)
                      {
                          cmd.Dispose();
                      }
            }

            return;
        }
예제 #5
0
        public bool CanCreateTempTables = false;                            // true if the associated account can create temp tables

        /// <summary>
        /// Load metadata describing connections and associated schemas
        /// </summary>
        /// <param name="dsFileName"></param>
        /// <returns></returns>

        public static int LoadMetadata()
        {
            XmlAttributeCollection atts;

            // Get some inifile parameters first

            if (ServicesIniFile.IniFile == null)
            {
                throw new Exception("ServicesIniFile.IniFile is null");
            }

            SessionConnection.Pooling            = ServicesIniFile.ReadBool("Pooling", true);
            SessionConnection.MinPoolSize        = ServicesIniFile.ReadInt("MinPoolSize", 1);
            SessionConnection.IncrPoolSize       = ServicesIniFile.ReadInt("IncrPoolSize", 5);
            SessionConnection.MaxPoolSize        = ServicesIniFile.ReadInt("MaxPoolSize", 100);
            SessionConnection.DecrPoolSize       = ServicesIniFile.ReadInt("DecrPoolSize", 1);
            SessionConnection.ConnectionLifetime = ServicesIniFile.ReadInt("ConnectionLifetime", 0);

            string tok     = ServicesIniFile.Read("KeyListPredType", "Parameterized");
            int    enumInt = EnumUtil.Parse(typeof(KeyListPredTypeEnum), tok);

            if (enumInt >= 0)
            {
                DbCommandMx.DefaultKeyListPredType = (KeyListPredTypeEnum)enumInt;
            }

            DbCommandMx.DbFetchRowCount = ServicesIniFile.ReadInt("DbFetchRowCount", DbCommandMx.DbFetchRowCount);
            DbCommandMx.DbFetchSizeMax  = ServicesIniFile.ReadInt("DbFetchSizeMax", DbCommandMx.DbFetchSizeMax);

            DataSources = new Dictionary <string, DataSourceMx>(StringComparer.OrdinalIgnoreCase);
            Schemas     = new Dictionary <string, DbSchemaMx>(StringComparer.OrdinalIgnoreCase);

            string dsFileName = ServicesDirs.MetaDataDir + @"\" + "DataSources.xml";

            StreamReader sr  = new StreamReader(dsFileName);
            XmlDocument  doc = new XmlDocument();

            doc.Load(sr);
            XmlNode node = doc.FirstChild;

            while (node != null)
            {
                if (node.NodeType == XmlNodeType.Element)
                {
                    break;
                }
                node = node.NextSibling;
                if (node == null)
                {
                    throw new Exception("No initial element found");
                }
            }

            if (!Lex.Eq(node.Name, "DataSources"))
            {
                throw new Exception("Expected DataSources node: " + node.Name);
            }

            atts = node.Attributes;             // datasources attributes
            for (int i = 0; i < atts.Count; i++)
            {
                XmlNode att = atts.Item(i);

                if (Lex.Eq(att.Name, "DblinkConnName"))
                {
                }                                                           // obsolete key word

                else
                {
                    throw new Exception
                              ("Unexpected DataSources attribute: " + att.Name);
                }
            }

            node = node.FirstChild;
            while (node != null)
            {
                if (node.NodeType != XmlNodeType.Element)
                {
                    ;                                                       // ignore non-elements
                }
                else if (Lex.Eq(node.Name, "DataSource") ||                 // connection element
                         Lex.Eq(node.Name, "Connection"))
                {
                    DataSourceMx cd = new DataSourceMx();

                    atts = node.Attributes;
                    for (int i = 0; i < atts.Count; i++)
                    {
                        XmlNode att = atts.Item(i);

                        if (Lex.Eq(att.Name, "DatabaseType") || Lex.Eq(att.Name, "DbType"))
                        {
                            if (!Enum.TryParse <DatabaseType>(att.Value, true, out cd.DbType))
                            {
                                throw new Exception("Invalid Database type: " + att.Value);
                            }
                        }

                        else if (Lex.Eq(att.Name, "Name"))
                        {
                            cd.DataSourceName = att.Value.ToUpper().Trim();
                        }

                        else if (Lex.Eq(att.Name, "DatabaseName") || Lex.Eq(att.Name, "OracleName"))
                        {
                            cd.DatabaseLocator = att.Value.ToUpper().Trim();
                        }

                        else if (Lex.Eq(att.Name, "ConnectionString"))                         // keep case if connection string
                        {
                            cd.DatabaseLocator = att.Value.Trim();
                        }

                        else if (Lex.Eq(att.Name, "UserName"))
                        {
                            cd.UserName = att.Value.Trim();                             // keep case as is (Oracle 11g)
                        }
                        else if (Lex.Eq(att.Name, "Password"))
                        {
                            cd.Password = att.Value.Trim();                           // keep case as is (Oracle 11g)
                        }
                        else if (Lex.Eq(att.Name, "MdlInit"))                         // mdl initialization
                        {
                            cd.InitCommand = "select cdcaux.ctenvinit('" + att.Value.Trim() + "') from dual";
                        }

                        else if (Lex.Eq(att.Name, "InitCommand"))
                        {
                            cd.InitCommand = att.Value.Trim();
                        }

                        else if (Lex.Eq(att.Name, "KeyDataSource"))
                        {
                            bool.TryParse(att.Value.Trim(), out cd.IsKeyDataSource);
                        }

                        else if (Lex.Eq(att.Name, "CanCreateTempTables"))
                        {
                            bool.TryParse(att.Value.Trim(), out cd.CanCreateTempTables);
                        }

                        else if (Lex.Eq(att.Name, "AlwaysUseDbLink"))
                        {
                        }                                                                         // obsolete

                        else
                        {
                            throw new Exception
                                      ("Unexpected Connection attribute: " + att.Name);
                        }
                    }

                    if (cd.DataSourceName == "")
                    {
                        DebugLog.Message("Connection is missing name: " + cd.DatabaseLocator);
                    }
                    else if (DataSourceMx.DataSources.ContainsKey(cd.DataSourceName))
                    {
                        DebugLog.Message("Data source defined twice: " + cd.DataSourceName);
                    }
                    else
                    {
                        DataSourceMx.DataSources.Add(cd.DataSourceName, cd);
                    }
                }

                else if (Lex.Eq(node.Name, "SchemaToDataSource") ||                 // schema map element
                         Lex.Eq(node.Name, "SchemaToConnection"))
                {
                    DbSchemaMx schema = new DbSchemaMx();
                    atts = node.Attributes;
                    for (int i = 0; i < atts.Count; i++)
                    {
                        XmlNode att = atts.Item(i);

                        if (Lex.Eq(att.Name, "Schema"))
                        {
                            schema.Name = att.Value.ToUpper().Trim();
                        }

                        else if (Lex.Eq(att.Name, "AliasFor"))
                        {
                            schema.AliasFor = att.Value.ToUpper().Trim();
                        }

                        else if (Lex.Eq(att.Name, "Connection"))
                        {
                            schema.DataSourceName = att.Value.ToUpper().Trim();
                        }

                        else if (Lex.Eq(att.Name, "Label") || Lex.Eq(att.Name, "L"))
                        {
                            schema.Label = att.Value.Trim();
                        }

                        else
                        {
                            throw new Exception
                                      ("Unexpected Connection attribute: " + att.Name);
                        }
                    }

                    if (schema.Name == "")
                    {
                        throw new Exception("Missing schema name");
                    }
                    if (schema.DataSourceName == "")
                    {
                        throw new Exception("Missing data source/connection name");
                    }
                    Schemas[schema.Name] = schema;
                }

                else
                {
                    throw new Exception("Expected Connection or SchemaToDataSource element but saw " +
                                        node.Name);
                }

                node = node.NextSibling;
            }
            sr.Close();

            return(DataSources.Count);
        }
예제 #6
0
/// <summary>
/// Write temporary list
/// </summary>
/// <param name="dsName">Name of datasource</param>
/// <param name="listName">Name of list</param>
/// <param name="listItems">The list of items to write</param>
/// <param name="numeric">Store list items in numeric column if true, text otherwise</param>

        public static string Write(
            string dsName,
            string listName,
            List <string> listItems,
            int firstKeyIdx,
            int keyCount,
            bool numeric)
        {
            DbCommandMx cmd = null;

            Oracle.DataAccess.Client.OracleDbType parmType;
            string fullTableName, sKey;
            int    iKey, i1;
            object o;

            DataSourceMx ds = GetDataSourceInfo(ref dsName, ref listName, out fullTableName);

            TruncateListTable(dsName, listName);
            if (keyCount == 0)
            {
                return(null);                           // nothing to write
            }
            string sql = "insert into <schema.name>  (<cols>) values(:0)";

            sql = sql.Replace("<schema.name>", fullTableName);

            if (numeric)
            {
                sql = sql.Replace("<cols>", "intKey");
            }
            else
            {
                sql = sql.Replace("<cols>", "stringKey");
            }

            object[][] pva = DbCommandMx.NewObjectArrayArray(1, keyCount);             // alloc insert row array

            if (numeric)
            {
                for (i1 = 0; i1 < keyCount; i1++)
                {
                    if (int.TryParse(listItems[firstKeyIdx + i1], out iKey))
                    {
                        pva[0][i1] = iKey;
                    }
                    else
                    {
                        pva[0][i1] = DBNull.Value;
                    }
                }

                parmType = Oracle.DataAccess.Client.OracleDbType.Int32;
            }

            else             // string keys
            {
                for (i1 = 0; i1 < keyCount; i1++)
                {
                    sKey = listItems[firstKeyIdx + i1];
                    if (!Lex.IsNullOrEmpty(sKey))
                    {
                        pva[0][i1] = sKey;
                    }
                    else
                    {
                        pva[0][i1] = DBNull.Value;
                    }
                }

                parmType = Oracle.DataAccess.Client.OracleDbType.Varchar2;
            }

            try
            {
                DateTime t0 = DateTime.Now;
                cmd = new DbCommandMx();
                DbConnectionMx dbc = DbConnectionMx.GetConnection(dsName);
                cmd.MxConn = dbc;
                cmd.Prepare(sql, parmType);
                int insCount = cmd.ExecuteArrayNonReader(pva, ref keyCount);
                int tDelta   = (int)TimeOfDay.Delta(t0);
                if (Debug)
                {
                    DebugLog.Message("Write TempDbList for KeyCount: " + insCount + ", Time: " + tDelta);
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally { cmd.Dispose(); }

            return(fullTableName);
        }