C# EA.SQLQuery方法代码示例

        public static EA.Element GetElementFromName(EA.Repository rep, string elementName, string elementType)
        {
            EA.Element el = null;
            string query = @"select o.ea_guid AS EA_GUID
                      from t_object o 
                      where o.name = '" + elementName + "' AND " +
                            "o.Object_Type = '" + elementType + "' ";
            string str = rep.SQLQuery(query);
            var xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(str);

            XmlNode operationGuidNode = xmlDoc.SelectSingleNode("//EA_GUID");
            if (operationGuidNode != null)
            {
                string guid = operationGuidNode.InnerText;
                el = rep.GetElementByGuid(guid);
            }

            return el;
        }

        public static EA.Method GetMethodFromMethodName(EA.Repository rep, string methodName)
        {
            EA.Method method = null;
            string query = @"select op.ea_guid AS EA_GUID
                      from t_operation op 
                      where op.name = '" + methodName + "' ";
            string str = rep.SQLQuery(query);
            var xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(str);

            XmlNode operationGuidNode = xmlDoc.SelectSingleNode("//EA_GUID");
            if (operationGuidNode != null)
            {
                string guid = operationGuidNode.InnerText;
                method = rep.GetMethodByGuid(guid);
            }

            return method;
        }

        // Find the operation from Activity / State Machine
        // it excludes operations in state machines
        public static Method GetOperationFromBrehavior(EA.Repository rep, EA.Element el)
        {
            Method method = null;
            string query = "";
            string conString = GetConnectionString(rep); // due to shortcuts
            if (conString.Contains("DBType=3"))
            {   // Oracle DB
                query = 
                    @"select op.ea_guid AS EA_GUID
                      from t_operation op 
                      where Cast(op.Behaviour As Varchar2(38)) = '" + el.ElementGUID + "' "+
                            " AND (Type is Null or Type not in ('do','entry','exit'))";
            }
            if (conString.Contains("DBType=1"))
                // SQL Server
            {    query = 
                      @"select op.ea_guid AS EA_GUID
                        from t_operation op 
                        where Substring(op.Behaviour,1,38) = '" + el.ElementGUID + "'" +
                       " AND (Type is Null or Type not in ('do','entry','exit'))";

            }

            if (conString.Contains(".eap"))
                // SQL Server
            {     query = 
                      @"select op.ea_guid AS EA_GUID
                        from t_operation op 
                        where op.Behaviour = '" + el.ElementGUID + "'" +
                       " AND ( Type is Null or Type not in ('do','entry','exit'))";

            }
            if ((! conString.Contains("DBType=1")) &&  // SQL Server, DBType=0 MySQL
               (!  conString.Contains("DBType=3")) &&  // Oracle
               (!  conString.Contains(".eap")))// Access
            {
                query =
                  @"select op.ea_guid AS EA_GUID
                        from t_operation op 
                        where op.Behaviour = '" + el.ElementGUID + "'" +
                       " AND (Type is Null or Type not in ('do','entry','exit'))";

            }

            string str = rep.SQLQuery(query);
            var xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(str);

            XmlNode operationGuidNode = xmlDoc.SelectSingleNode("//EA_GUID");
            if (operationGuidNode != null)
            {
                string guid = operationGuidNode.InnerText;
                method = rep.GetMethodByGuid(guid);
            }
            return method;
        }

//.........这里部分代码省略.........
//               (!rep.ConnectionString.Contains(".eap")))// Access
//            {
//                query =
//                  @"select op.ea_guid AS EA_GUID
//                        from t_operation op 
//                        where op.Behaviour = '" + el.ElementGUID + "'";

//            }

//            string str = rep.SQLQuery(query);
//            XmlDocument XmlDoc = new XmlDocument();
//            XmlDoc.LoadXml(str);

//            XmlNode operationGUIDNode = XmlDoc.SelectSingleNode("//EA_GUID");
//            if (operationGUIDNode != null)
//            {
//                string GUID = operationGUIDNode.InnerText;
//                method = rep.GetMethodByGuid(GUID);
//            }
//            return method;
//        }



        public static Method GetOperationFromConnector(EA.Repository rep, EA.Connector con)
        {
            Method method = null;
            string query = "";
            if (GetConnectionString(rep).Contains("DBType=3"))
                //pdat3: 'Activity','Sequence', (..)
            {   // Oracle DB
                query =
                    @"select description AS EA_GUID
                      from t_xref x 
                      where Cast(x.client As Varchar2(38)) = '" + con.ConnectorGUID + "'" +
                                                                " AND Behavior = 'effect' ";
            }
            if (GetConnectionString(rep).Contains("DBType=1"))
            {   // SQL Server

                query =
                      @"select description AS EA_GUID
                        from t_xref x 
                        where Substring(x.client,1,38) = " + "'" + con.ConnectorGUID + "'" +
                           " AND Behavior = 'effect' "                   
                                          ;
            }
            if (GetConnectionString(rep).Contains(".eap"))
            {

                query =
                      @"select description AS EA_GUID
                        from t_xref x 
                        where client = " + "'" + con.ConnectorGUID + "'" +
                           " AND Behavior = 'effect' "
                                          ;
            }
            if ((! GetConnectionString(rep).Contains("DBType=1")) &&  // SQL Server, DBType=0 MySQL
                (! GetConnectionString(rep).Contains("DBType=3")) &&  // Oracle
                (! GetConnectionString(rep).Contains(".eap")))// Access
            {
                query =
                @"select description AS EA_GUID
                        from t_xref x 
                        where client = " + "'" + con.ConnectorGUID + "'" +
                             " AND Behavior = 'effect' "
                                            ;

            }


            string str = rep.SQLQuery(query);
            var xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(str);

            //string type = "";
            //XmlNode pdat3Node = XmlDoc.SelectSingleNode("//PDAT3");
            //if (pdat3Node != null)
            //{
            //    type = pdat3Node.InnerText;
                
            //}
            //if ( type.EndsWith(")")) // Operation
            //{ 
            string guid = null;
                XmlNode operationGuidNode = xmlDoc.SelectSingleNode("//EA_GUID");
                if (operationGuidNode != null)
                {
                    guid = operationGuidNode.InnerText;
                    method = rep.GetMethodByGuid(guid);
                }
                if (method == null)
                {

                     if (guid != null) OpenBehaviorForElement(rep, rep.GetElementByGuid(guid));
                }
            //}

            return method;
        }

        // Gets the composite element for a diagram GUID
        public static string GetElementFromCompositeDiagram(EA.Repository rep, string diagramGuid)
        {
            string query = @"select o.ea_guid AS COMPOSITE_GUID
                      from t_xref x INNER JOIN t_object o on (x.client = o.ea_guid and type = 'element property')
                      where x.supplier = '" + diagramGuid + "'    ";
            string str = rep.SQLQuery(query);
            var xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(str);

            XmlNode operationGuidNode = xmlDoc.SelectSingleNode("//COMPOSITE_GUID");
            diagramGuid = "";
            if (operationGuidNode != null)
            {
                diagramGuid = operationGuidNode.InnerText;
            }
            return diagramGuid;
        }

        private static string GetSingleSqlValue(EA.Repository rep, string query, string attributeName)
        {
            string s = "";
            string str = rep.SQLQuery(query);
            var xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(str);

            XmlNode node = xmlDoc.SelectSingleNode("//"+attributeName);
            if (node != null)
            {
                s = node.InnerText;
            }
            return s;
        }

        // Find the calling operation from a Call Operation Action
        public static string GetClassifierGuid(EA.Repository rep,string guid)
        {
            string query = @"select o.Classifier_guid AS CLASSIFIER_GUID
                      from t_object o 
                      where o.EA_GUID = '" + guid + "'";
            string str = rep.SQLQuery(query);
            var xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(str);

            XmlNode operationGuidNode = xmlDoc.SelectSingleNode("//CLASSIFIER_GUID");
            guid = "";
            if (operationGuidNode != null)
            {
                guid = operationGuidNode.InnerText;
            }
            return guid;
        }

        // Gets the signal associated with the element
        public static string GetSignal(EA.Repository rep, string guid)
        {
            string query = @"select x.Description AS SIGNAL_GUID
                      from t_xref x 
                      where x.Client = '" + guid + "'    AND behavior = 'event' ";
            string str = rep.SQLQuery(query);
            var xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(str);

            XmlNode operationGuidNode = xmlDoc.SelectSingleNode("//SIGNAL_GUID");
            guid = "";
            if (operationGuidNode != null)
            {
                guid = operationGuidNode.InnerText;
            }
            return guid;
        }

        // Find the calling operation from a Call Operation Action
        public static string GetParameterType(EA.Repository rep, string actionPinGuid)
        {
            string query = @"SELECT par.type AS OPTYPE 
			    from t_object o  inner join t_operationparams par on (o.classifier_guid = par.ea_guid)
                where o.ea_guid = '" + actionPinGuid + "' ";
            string str = rep.SQLQuery(query);
            var xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(str);

            XmlNode typeGuidNode = xmlDoc.SelectSingleNode("//OPTYPE");
            if (typeGuidNode != null)
            {
                return typeGuidNode.InnerText;
                
            }
            return "";
        }

        // Find the calling operation from a Call Operation Action
        public static Method GetOperationFromCallAction(EA.Repository rep, EA.Element obj)
        {
            string wildCard = GetWildCard(rep);
            string query = @"SELECT op.ea_guid AS OPERATION from (t_object o inner join t_operation op on (o.classifier_guid = op.ea_guid))
               inner join t_xref x on (x.client = o.ea_guid)
			   where x.name = 'CustomProperties' and
			             x.description like '"+ wildCard + "CallOperation" + wildCard + 
                         "' and o.object_id = " + obj.ElementID;
            string str = rep.SQLQuery(query);
            var xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(str);

            XmlNode operationGuidNode = xmlDoc.SelectSingleNode("//OPERATION");
            if (operationGuidNode != null)
            {
                var guid = operationGuidNode.InnerText;
                return rep.GetMethodByGuid(guid);
            }
            return null;
        }

        // Find the calling operation from a Call Operation Action
        public static Method GetOperationFromAction(EA.Repository rep, EA.Element action)
        {
            Method method = null;
            string query = @"select o.Classifier_guid AS CLASSIFIER_GUID
                      from t_object o 
                      where o.Object_ID = " + action.ElementID;
            string str = rep.SQLQuery(query);
            var xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(str);

            XmlNode operationGuidNode = xmlDoc.SelectSingleNode("//CLASSIFIER_GUID");
            if (operationGuidNode != null)
            {
                string guid = operationGuidNode.InnerText;
                method = rep.GetMethodByGuid(guid);
            }
            return method;
        }

        //------------------------------------------------------------------------------------------------------------------------------------
        // Find the Parameter of a Activity
        //------------------------------------------------------------------------------------------------------------------------------------
        // par Parameter of Operation (only if isReturn = false)
        // act Activity
        // Parameter wird aufgrund des Alias-Namens gefunden
        //
        // 
        public static EA.Element  GetParameterFromActivity(EA.Repository rep, EA.Parameter par, EA.Element act, bool isReturn = false)
        {

            string aliasName;
            if (isReturn)
            {
                aliasName = "return:";
            }
            else
            {
                aliasName = "par_" + par.Position;
            }

            EA.Element parTrgt = null;
            string query = @"select o2.ea_guid AS CLASSIFIER_GUID
                      from t_object o1 INNER JOIN t_object o2 on ( o2.parentID = o1.object_id)
                      where o1.Object_ID = " + act.ElementID + 
                             " AND  o2.Alias like '"+ aliasName + GetWildCard(rep) + "'";
            string str = rep.SQLQuery(query); 
            var xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(str);

            XmlNode operationGuidNode = xmlDoc.SelectSingleNode("//CLASSIFIER_GUID");
            if (operationGuidNode != null)
            {
                string guid = operationGuidNode.InnerText;
                parTrgt = rep.GetElementByGuid(guid);
            }
            return parTrgt;
        }

        // Find type for name
        // 1. Search for name (if type contains a '*' search for type with '*' and for type without '*'
        // 2. Search for Synonyms
        public static int GetTypeId(EA.Repository rep, string name)
        {
            int intReturn = 0;
            //Boolean isPointer = false;
            //if (name.Contains("*")) isPointer = true;
            //
            // delete an '*' at the end of the type name

            // remove a 'const ' from start of string
            // remove a 'volatile ' from start of string
            name = name.Replace("const", "");
            name = name.Replace("volatile", "");
            //name = name.Replace("*", "");
            name = name.Trim();

//            if (isPointer) {
//                string queryIsPointer = @"SELECT o.object_id As OBJECT_ID
//                            FROM  t_object  o
//                            INNER  JOIN  t_objectproperties  p ON  o.object_id  =  p.object_id
//                            where property = 'typeSynonyms' AND
//                                  Object_Type in ('Class','PrimitiveType','DataType','Enumeration')  AND
//                                  p.value = '" + name + "*' " +
//                            @" UNION
//                               Select o.object_id
//                               From t_object o
//                                        where Object_Type in ('Class','PrimitiveType','DataType','Enumeration') AND name = '" + name + "*' ";
//                string strIsPointer = rep.SQLQuery(queryIsPointer);
//                XmlDocument XmlDocIsPointer = new XmlDocument();
//                XmlDocIsPointer.LoadXml(strIsPointer);

//                XmlNode operationGUIDNodeIsPointer = XmlDocIsPointer.SelectSingleNode("//OBJECT_ID");
//                if (operationGUIDNodeIsPointer != null)
//                {
//                    intReturn = Convert.ToInt32(operationGUIDNodeIsPointer.InnerText);
//                }     
//            }

            if (intReturn == 0)
            {
                //if (name.Equals("void") || name.Equals("void*")) return 0;
                string query = @"SELECT o.object_id As OBJECT_ID
                            FROM  t_object  o
                            INNER  JOIN  t_objectproperties  p ON  o.object_id  =  p.object_id
                            where property = 'typeSynonyms' AND
                                  Object_Type in ('Class','PrimitiveType','DataType','Enumeration')  AND
                                  p.value = '" + name + "' " +
                                @" UNION
                               Select o.object_id
                               From t_object o
                                        where Object_Type in ('Class','PrimitiveType','DataType','Enumeration') AND name = '" + name + "' ";
                string str = rep.SQLQuery(query);
                var xmlDoc = new XmlDocument();
                xmlDoc.LoadXml(str);

                XmlNode operationGuidNode = xmlDoc.SelectSingleNode("//OBJECT_ID");
                if (operationGuidNode != null)
                {
                    intReturn = Convert.ToInt32(operationGuidNode.InnerText);
                }
            }

            
            return intReturn;
        }

        public static int GetHighestSequenceNoFromDiagram(EA.Repository rep, EA.Diagram dia)
        {
            int sequenceNumber = 0;
            string query = @"select sequence from t_diagramobjects do " +
                            "  where do.Diagram_ID = "+ dia.DiagramID +
                            "  order by 1 desc";
            string str = rep.SQLQuery(query);
            var xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(str);

            XmlNode operationGuidNode = xmlDoc.SelectSingleNode("//SEQUENCE_NUMBER");
            if (operationGuidNode != null)
            {
                sequenceNumber = Convert.ToInt32(operationGuidNode.InnerText);
            }
            return sequenceNumber;
        }

        // Find the operation from Activity / State Machine
        // it excludes operations in state machines
        public static EA.Package GetModelDocumentFromPackage(EA.Repository rep, EA.Package pkg)
        {
            EA.Package pkg1 = null;
            string repositoryType = "JET";// rep.RepositoryType();

            // get object_ID of package
            var query = @"select pkg.ea_GUID AS EA_GUID " +
                           @" from (((t_object o  INNER JOIN t_attribute a on (o.object_ID = a.Object_ID AND a.type = 'Package')) " +
                           @"     INNER JOIN t_package pkg on (pkg.Package_ID = o.Package_ID)) " +
                           @"		  INNER JOIN t_object o1 on (cstr(o1.object_id) = a.classifier)) " +
                           @" where o1.ea_guid = '" + pkg.PackageGUID + "' ";
            
						 
            if (repositoryType== "JET")
            {
                query = @"select pkg.ea_GUID AS EA_GUID " +
                    @" from (((t_object o  INNER JOIN t_attribute a on (o.object_ID = a.Object_ID AND a.type = 'Package')) " +
                    @"     INNER JOIN t_package pkg on (pkg.Package_ID = o.Package_ID)) " +
                    @"		  INNER JOIN t_object o1 on (cstr(o1.object_id) = a.classifier)) " +
                    @" where o1.ea_guid = '" + pkg.PackageGUID + "' ";
            }
            if (repositoryType == "SQLSVR")
            // SQL Server
            {
                query = @"select pkg.ea_GUID AS EA_GUID " +
                    @" from (((t_object o  INNER JOIN t_attribute a on (o.object_ID = a.Object_ID AND a.type = 'Package')) " +
                    @"     INNER JOIN t_package pkg on (pkg.Package_ID = o.Package_ID)) " +
                    @"		  INNER JOIN t_object o1 on o1.object_id = Cast(a.classifier As Int)) " +
                    @" where o1.ea_guid = '" + pkg.PackageGUID + "' ";
                
            }
            
           

            string str = rep.SQLQuery(query);
            var xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(str);

            XmlNode operationGuidNode = xmlDoc.SelectSingleNode("//EA_GUID");

            if (operationGuidNode != null)
            {
                string guid = operationGuidNode.InnerText;
                pkg1 = rep.GetPackageByGuid(guid);
            }
            return pkg1;
        }