C# SqlQuery.Where方法代码示例

    void databaseWork()
    {
        int rows = 100;

        // create table
        string tableName = Guid.NewGuid().ToString().Replace("-", "");
        var fields = new DataValueType[] { DataValueType.IntegerType, DataValueType.DateTimeType, DataValueType.ShortStringType, DataValueType.LongStringType };
        var createActions = new List<DbAction>();
        createActions.Add(DbAction.EnsureTable(tableName, new string[] { "id" }, new DataValueType[] { DataValueType.IntegerType }));
        createActions.Add(DbAction.AddField(tableName, "f1", DataValueType.BooleanType, true, new BooleanDataValue(false)));
        createActions.Add(DbAction.AddField(tableName, "f2", DataValueType.DateTimeType, false, new DateTimeDataValue(DateTime.Now)));
        createActions.Add(DbAction.AddField(tableName, "f3", DataValueType.FloatType, false, new FloatDataValue(0)));
        createActions.Add(DbAction.AddField(tableName, "f4", DataValueType.ShortStringType, true, new ShortStringDataValue("test")));
        createActions.Add(DbAction.AddField(tableName, "f5", DataValueType.LongStringType, false, new LongStringDataValue("test")));
        WAFRuntime.Engine.Dao.DbProvider.UpdateDatabase(createActions);

        // inserting records
        var table = new SqlTable(tableName);
        var id = new SqlFieldInteger("id", table);
        var f1 = new SqlFieldBoolean("f1", table);
        var f2 = new SqlFieldDateTime("f2", table);
        var f3 = new SqlFieldFloat("f3", table);
        var f4 = new SqlFieldShortString("f4", table);
        var f5 = new SqlFieldLongString("f5", table);
        {
            for (int r = 0; r < rows; r++) {
                SqlQuery q = new SqlQuery(WAFRuntime.Engine.Dao);
                q.Insert(table);
                q.Values(id, r);
                q.Values(f1, false);
                q.Values(f2, DateTime.Now);
                q.Values(f3, (double)r);
                q.Values(f4, "My test string " + r);
                q.Values(f5, "My long long long long long long long long long long long long long long long long long long long long long long long long long long long long long long long long long test string " + r);
                q.ExecuteNonQuery();
            }
        }
        {
            // quering 10 records at the time
            for (int r = 0; r < rows; r++) {
                SqlQuery q = new SqlQuery(WAFRuntime.Engine.Dao);
                q.From(table);
                q.Select(f1);
                q.Select(f2);
                q.Select(f3);
                q.Select(f4);
                q.Where(id > r);
                using (var dr = q.ExecuteReader()) {
                    while (dr.Read()) { }
                }
            }
        }

        // deleting 1/10 of the records
        for (int r = 0; r < rows / 10; r++) {
            SqlQuery q = new SqlQuery(WAFRuntime.Engine.Dao);
            q.Delete(table);
            q.Where(id == r);
            q.ExecuteNonQuery();
        }

        // dropping table
        WAFRuntime.Engine.Dao.DbProvider.UpdateDatabase(new DbAction[] { DbAction.DeleteTable(tableName) }.ToList());
    }

 public override NextCall Invoke(WorkflowMethod invoker)
 {
     var dvs = from d in Engine.Definition.DataValue.Values where d.DataValueType == DataValueType.DateTimeType select d.SqlFieldDateTime;
     var fields = new List<SqlFieldDateTime>();
     foreach (var t in DbTable.GetAllTables(Engine.Definition)) {
         foreach (var f in from f in t.Fields where f.DataValueType == DataValueType.DateTimeType select f) fields.Add((SqlFieldDateTime)f);
     }
     int rowsAffected = 0;
     Info.InBackgroundMode = false;
     int fieldCount = 0;
     int tableCount = dvs.Count();
     foreach (var f in fields) {
         Info.Caption = "Updating field " + fieldCount + " of " + fields.Count + " fields...";
         SqlQuery q = new SqlQuery();
         q.Update(f.Table);
         q.Where(f < Utils.DateTimeNull);
         q.Set(f, Utils.DateTimeNull);
         rowsAffected += q.ExecuteNonQuery();
     }
     Session.Notify(DialogueIcon.Info, "Complete", "Updated " + rowsAffected + " date values in " + tableCount + " fields. ");
     return null;
 }

    void ensureInegrity(object stateInfo)
    {
        try {
            setThreadDescription("Ensuring data uniqueness");
            SqlQuery query = new SqlQuery(WAFRuntime.Engine.Dao);
            DataAccessObject dao = WAFRuntime.Engine.Dao;

            // Node table
            setThreadDescription("Fixing Node table..."); int fixCount = 0;
            foreach (int nodeId in dao.GetDuplicateNodeRecords()) {
                if (dao.FixDuplicateNodeRecords(nodeId)) fixCount++;
            }
            //if (fixCount > 0) WFContext.Notify("Fixed " + fixCount + " duplicate records in the node table. ");

            // NodeCsd table
            setThreadDescription("Fixing Node Csd table..."); fixCount = 0;
            foreach (int[] ids in dao.GetDuplicateNodeCsdRecords()) {
                if (dao.FixDuplicateNodeCsdRecords(ids[0], ids[1])) fixCount++;
            }
            //if (fixCount > 0) WFContext.Notify("Fixed " + fixCount + " duplicate records in the nodeCsd table. ");

            // Content table
            setThreadDescription("Fixing Content table..."); fixCount = 0;
            foreach (int[] ids in dao.GetDuplicateContentBaseRecords()) {
                if (dao.FixDuplicateContentBaseRecords(ids[0], ids[1], ids[2])) fixCount++;
            }
            //if (fixCount > 0) WAFRuntime.Notify("Fixed " + fixCount + " duplicate records in the content table. ");

            // Class tables
            foreach (MemDefContentClass classDef in WAFRuntime.Engine.Definition.ContentClass.Values) {
                setThreadDescription("Fixing Class table '" + classDef.ClassName + "'..."); fixCount = 0;
                List<int> contentIds = dao.GetDuplicateContentClassRecords(classDef.Id);
                foreach (int contentId in contentIds) {
                    if (dao.FixDuplicateContentClassRecords(classDef.Id, contentId)) fixCount++;
                }
            }
            //if (fixCount > 0) WAFRuntime.Notify("Fixed " + fixCount + " duplicate records in class tables. ");

            foreach (MemDefRelation relDef in WAFRuntime.Engine.Definition.Relation.Values) {
            }

            foreach (MemDefProperty propDef in WAFRuntime.Engine.Definition.Property.Values) {
                if (propDef.BasePropertyClassId == PropertyBaseClass.InnerContents) {
                }
            }

            AqlAlias alias = new AqlAlias();
            alias.IgnoreSessionCulture = true;
            alias.IgnoreSessionRevision = true;
            alias.IncludeDeletedContents = true;
            alias.IncludeDeletedNodes = true;

            setThreadDescription("Refreshing derived flag...");
            foreach (int siteId in WAFRuntime.Engine.GetAllSiteIds()) {
                foreach (int lcid in WAFRuntime.Engine.GetSiteAllLCIDs(siteId)) {
                    UniqueList<int> cultInSite = new UniqueList<int>(WAFRuntime.Engine.GetSiteLCIDs(siteId));
                    if (!cultInSite.Contains(lcid)) {
                        // if lcid is not in site, set all contents in this lcis to derived...
                        // get all nodes in site
                        SqlAliasNode node = new SqlAliasNode();
                        SqlAliasContent content = new SqlAliasContent();
                        SqlJoinExpression joinExp = new SqlJoinExpression();
                        joinExp.Add(node.Id, content.NodeId);
                        SqlFromInnerJoin join = new SqlFromInnerJoin(node, content, joinExp);
                        SqlQuery select = new SqlQuery(WAFRuntime.Engine.Dao);
                        select.Select(content.ContentId);
                        select.From(join);
                        select.Where(node.SiteId == siteId);
                        select.Where(content.LCID == lcid);
                        select.Where(content.IsDerived == false);
                        List<int> cIds = new List<int>();
                        using (SqlDataReader dr = select.ExecuteReader()) {
                            while (dr.Read()) cIds.Add(dr.GetInt(0));
                        }
                        foreach (int contentId in cIds) {
                            SqlQuery update = new SqlQuery(WAFRuntime.Engine.Dao);
                            update.Update(Sql.Table.Content);
                            update.Set(Sql.Field.Content.IsDerived, true);
                            update.Where(Sql.Field.Content.LCID == lcid);
                            update.Where(Sql.Field.Content.ContentId == contentId);
                            update.ExecuteNonQuery();
                        }
                        if (isThreadCancelled()) return;
                    }
                }
            }

            setThreadDescription("Retrieving node ids...");
            SqlQuery sqlQuery = new SqlQuery(WAFRuntime.Engine.Dao);
            List<int> nodeIds = new List<int>();
            List<int> classIds = new List<int>();
            sqlQuery.From(Sql.Table.Node);
            sqlQuery.Distinct = true;
            sqlQuery.Select(Sql.Field.Node.Id);
            sqlQuery.Select(Sql.Field.Node.ContentClassId);
            using (SqlDataReader sqlDr = sqlQuery.ExecuteReader()) {
                while (sqlDr.Read()) {
                    nodeIds.Add(sqlDr.GetInt(0));
                    classIds.Add(sqlDr.GetInt(1));
                }
//.........这里部分代码省略.........

 void deleteUnusedNodeIdsIn_NodeCsd(HashSet<int> legalNodeIds)
 {
     var illegalNodeIds = new HashSet<int>();
     {
         var q = new SqlQuery(Engine.Dao);
         q.From(Sql.Table.NodeCsd);
         var rNodeId = q.Select(Sql.Field.NodeCsd.NodeId);
         using (var rs = q.ExecuteReader()) {
             while (rs.Read()) {
                 if (Info.BreakExecution) return;
                 if (!legalNodeIds.Contains(rNodeId.Value) && !illegalNodeIds.Contains(rNodeId.Value)) illegalNodeIds.Add(rNodeId.Value);
             }
         }
     }
     {
         foreach (var nodeId in illegalNodeIds) {
             if (Info.BreakExecution) return;
             addDeleteCount();
             if (_delete) {
                 var q = new SqlQuery(Engine.Dao);
                 q.Delete(Sql.Table.NodeCsd);
                 q.Where(Sql.Field.NodeCsd.NodeId == nodeId);
                 q.ExecuteNonQuery();
             }
         }
     }
 }

    void deleteUnusedNodeIdsIn_RelationTable(int relationId, HashSet<int> legalNodeIds)
    {
        var rel = Engine.Definition.Relation[relationId];
        var table = new SqlTable(rel.TableName);
        var nodeIdField1 = new SqlFieldInteger(rel.NameParents, table);
        var nodeIdField2 = new SqlFieldInteger(rel.NameChildren, table);

        var illegalNodeIds = new HashSet<int>();
        {
            var q = new SqlQuery(Engine.Dao);
            q.From(table);
            var rNodeId1 = q.Select(nodeIdField1);
            var rNodeId2 = q.Select(nodeIdField2);
            using (var rs = q.ExecuteReader()) {
                while (rs.Read()) {
                    if (Info.BreakExecution) return;
                    if (!legalNodeIds.Contains(rNodeId1.Value) && !illegalNodeIds.Contains(rNodeId1.Value)) illegalNodeIds.Add(rNodeId1.Value);
                    if (!legalNodeIds.Contains(rNodeId2.Value) && !illegalNodeIds.Contains(rNodeId2.Value)) illegalNodeIds.Add(rNodeId2.Value);
                }
            }
        }
        {
            foreach (var nodeId in illegalNodeIds) {
                if (Info.BreakExecution) return;
                addDeleteCount();
                if (_delete) {
                    var q = new SqlQuery(Engine.Dao);
                    q.Delete(table);
                    q.Where(nodeIdField1 == nodeId | nodeIdField2 == nodeId);
                    q.ExecuteNonQuery();
                }
            }
        }
    }

 void deleteUnusedLanguagesIn_NodeCsd(List<int> legalCultures)
 {
     var lcids = new List<int>();
     var nodeIds = new List<int>();
     {
         var q = new SqlQuery(Engine.Dao);
         q.From(Sql.Table.NodeCsd);
         var rNodeId = q.Select(Sql.Field.NodeCsd.NodeId);
         var rLCID = q.Select(Sql.Field.NodeCsd.LCID);
         q.Where(!Sql.In(Sql.Field.NodeCsd.LCID, legalCultures));
         using (var rs = q.ExecuteReader()) {
             while (rs.Read()) {
                 if (Info.BreakExecution) return;
                 nodeIds.Add(rNodeId.Value);
                 lcids.Add(rLCID.Value);
             }
         }
     }
     {
         for (int i = 0; i < nodeIds.Count; i++) {
             if (Info.BreakExecution) return;
             addDeleteCount();
             if (_delete) {
                 var q = new SqlQuery(Engine.Dao);
                 q.Delete(Sql.Table.NodeCsd);
                 q.Where(Sql.Field.NodeCsd.NodeId == nodeIds[i] & Sql.Field.NodeCsd.LCID == lcids[i]);
                 q.ExecuteNonQuery();
             }
         }
     }
 }

    void deleteUnusedIRelRecords(HashSet<int> allContentIdInClassTables)
    {
        foreach (var propDef in Engine.Definition.Property.Values.Where(p =>
            p.BasePropertyClassId == PropertyBaseClass.FileFolderProperty
            || p.BasePropertyClassId == PropertyBaseClass.InnerContents)) {

            var illegalContentId = new HashSet<int>();
            var illegalParentContentId = new HashSet<int>();

            {
                var q = new SqlQuery(Engine.Dao);
                var table = new SqlTable(propDef.GetInnerContentTableName());
                q.From(table);
                var rParentContentId = q.Select(new SqlFieldInteger("parent_content_id", table));
                var rContentId = q.Select(new SqlFieldInteger("content_id", table));
                using (var rs = q.ExecuteReader()) {
                    while (rs.Read()) {
                        if (Info.BreakExecution) return;
                        if (!allContentIdInClassTables.Contains(rContentId.Value)) {
                            if (!illegalContentId.Contains(rContentId.Value)) {
                                illegalContentId.Add(rContentId.Value);
                            }
                        }
                        if (!allContentIdInClassTables.Contains(rParentContentId.Value)) {
                            if (!illegalParentContentId.Contains(rParentContentId.Value)) {
                                illegalParentContentId.Add(rParentContentId.Value);
                            }
                        }
                    }
                }
            }

            {
                foreach (var contentId in illegalContentId) {
                    if (Info.BreakExecution) return;
                    addDeleteCount();
                    if (_delete) {
                        var q = new SqlQuery(Engine.Dao);
                        var table = new SqlTable(propDef.GetInnerContentTableName());
                        q.Delete(table);
                        q.Where(new SqlFieldInteger("content_id", table) == contentId);
                        q.ExecuteNonQuery();
                    }
                }
                foreach (var parentContentId in illegalParentContentId) {
                    if (Info.BreakExecution) return;
                    addDeleteCount();
                    if (_delete) {
                        var q = new SqlQuery(Engine.Dao);
                        var table = new SqlTable(propDef.GetInnerContentTableName());
                        q.Delete(table);
                        q.Where(new SqlFieldInteger("parent_content_id", table) == parentContentId);
                        q.ExecuteNonQuery();
                    }
                }
            }

        }
    }

 HashSet<int> deleteUnusedClassTableRecords(Dictionary<int, int> classIdByLegalContentId)
 {
     HashSet<int> allContentIdInClassTables = new HashSet<int>();
     foreach (var classDef in Engine.Definition.ContentClass.Values) {
         bool includeClassTable = true;
         if (!_includeInner) {
             includeClassTable = classDef.ClassType == ContentClassType.ContentClass;
         }
         if (classDef.Id == 0) includeClassTable = false;
         if (includeClassTable) {
             var illegalContentIds = new List<int>();
             {
                 var q = new SqlQuery(Engine.Dao);
                 var table = new SqlTable(classDef.TableName);
                 q.From(table);
                 var rContentId = q.Select(new SqlFieldInteger("content_id", table));
                 using (var rs = q.ExecuteReader()) {
                     while (rs.Read()) {
                         if (Info.BreakExecution) return allContentIdInClassTables;
                         var contentId = rContentId.Value;
                         if (classIdByLegalContentId.ContainsKey(rContentId.Value)) {
                             // ok, so it is known, check if class of this table inherits the other
                             int legalClassId = classIdByLegalContentId[rContentId.Value];
                             var classDefLegal = Engine.Definition.ContentClass[legalClassId];
                             if (!classDefLegal.AllParentsIncThis.Contains(classDef.Id)) { // this class table does not inherit from the class content it belongs to
                                 illegalContentIds.Add(contentId);
                             } else { // ok, so leave, and store in list of legal content ids to relate to:
                                 if (!allContentIdInClassTables.Contains(contentId)) allContentIdInClassTables.Add(contentId);
                             }
                         } else { // completely unknown
                             illegalContentIds.Add(contentId);
                         }
                     }
                 }
             }
             {
                 foreach (var contentId in illegalContentIds) {
                     if (Info.BreakExecution) return allContentIdInClassTables;
                     addDeleteCount();
                     if (_delete) {
                         var q = new SqlQuery(Engine.Dao);
                         var table = new SqlTable(classDef.TableName);
                         q.Delete(table);
                         q.Where(new SqlFieldInteger("content_id", table) == contentId);
                         q.ExecuteNonQuery();
                     }
                 }
             }
         }
     }
     return allContentIdInClassTables;
 }

 void addContentIdsFromInnerRelations(Dictionary<int, int> allClassIdByContentId, Dictionary<int, int> classIdByContentId, int callDepth)
 {
     if (Info.BreakExecution) return;
     if (classIdByContentId.Count == 0) return;
     if (callDepth > 20) return; // throw new Exception("Looping references in inner contents");
     var newClassIdByContentId = new Dictionary<int, int>();
     foreach (var contentId in classIdByContentId.Keys) {
         updateDescription("Collecting valid content ids " + allClassIdByContentId.Count.ToString("### ### ##0") + ".");
         // then for each content_id, based on class look for all possible
         int classId = classIdByContentId[contentId];
         var classDef = Engine.Definition.ContentClass[classId];
         foreach (var propId in classDef.GetAllInnerPropertyIds()) {
             var propDef = Engine.Definition.Property[propId];
             var q = new SqlQuery(Engine.Dao);
             var table = new SqlTable(propDef.GetInnerContentTableName());
             q.From(table);
             q.Where(new SqlFieldInteger("parent_content_id", table) == contentId);
             var rInnerContentId = q.Select(new SqlFieldInteger("content_id", table));
             var rInnerClassId = q.Select(new SqlFieldInteger("content_class_id", table));
             using (var rs = q.ExecuteReader()) {
                 if (Info.BreakExecution) return;
                 while (rs.Read()) {
                     if (!newClassIdByContentId.ContainsKey(rInnerContentId.Value)) {
                         newClassIdByContentId.Add(rInnerContentId.Value, rInnerClassId.Value);
                     }
                     if (!allClassIdByContentId.Keys.Contains(rInnerContentId.Value)) {
                         allClassIdByContentId.Add(rInnerContentId.Value, rInnerClassId.Value);
                     }
                 }
             }
         }
     }
     addContentIdsFromInnerRelations(allClassIdByContentId, newClassIdByContentId, ++callDepth);
 }

 public override NextCall Invoke(WorkflowMethod invoker)
 {
     _action = GetSecureInput<AddressAction>();
     List<int> nodeIds = new List<int>();
     List<int> lcids = new List<int>();
     {
         var q = Session.CreateQuery();
         var sql = new SqlQuery(Session.Engine.Dao);
         sql.From(Sql.Table.Content);
         var rNodeId = sql.Select(Sql.Field.Content.NodeId);
         var rLcid = sql.Select(Sql.Field.Content.LCID);
         sql.Where(Sql.Field.Content.Revision == 0);
         sql.Where(Sql.Field.Content.IsDerived == false);
         using (var rs = sql.ExecuteReader()) {
             while (rs.Read()) {
                 if (rNodeId.Value > 0) {
                     nodeIds.Add(rNodeId.Value);
                     lcids.Add(rLcid.Value);
                 }
             }
         }
     }
     switch (_action) {
         case AddressAction.RegeneratingAutomaticAddresses: WFContext.Caption = "Regenerating automatic addresses..."; break;
         case AddressAction.SettingAddressesToAutomatic: WFContext.Caption = "Setting addresses to automatic..."; break;
         case AddressAction.EnsuringAddressesAreValid: WFContext.Caption = "Ensuring addresses are valid..."; break;
         default: break;
     }
     WFContext.InBackgroundMode = false;
     WFContext.EstimateProgress = true;
     int pos = 0;
     int batchSize = 500;
     int count = 0;
     int changed = 0;
     while (pos < nodeIds.Count) {
         if (batchSize + pos > nodeIds.Count) batchSize = nodeIds.Count - pos;
         var keys = new List<CKeyNLR>();
         for (int i = pos; i < pos + batchSize; i++) keys.Add(new CKeyNLR(nodeIds[i], lcids[i], 0));
         try {
             fixAddress(keys, ref count, ref changed, nodeIds.Count);
         } catch {
             foreach (var k in keys) {
                 try { // do one by one...
                     fixAddress(new List<CKeyNLR>(new CKeyNLR[] { k }), ref count, ref changed, nodeIds.Count);
                 } catch { }
                 if (WFContext.BreakExecution) return null;
             }
         }
         foreach (var k in keys) { // to prevent cache from loading all contents
             Session.Engine.RemoveNodeFromCache(k.NodeId);
         }
         pos += batchSize;
         if (WFContext.BreakExecution) return null;
     }
     Session.Engine.ClearCache();
     WFContext.Notify(changed + (changed == 1 ? " content was changed. " : " contents were changed. "), Utils.GetFriendlyName(_action.ToString()) + " completed successfully");
     return null;
 }