C# Sql.Select方法代码示例

        public void Can_Verify_Base_Where_With_Version_Clause()
        {
            var NodeObjectType = new Guid(Constants.ObjectTypes.Document);
            var versionId = new Guid("2b543516-a944-4ee6-88c6-8813da7aaa07");

            var expected = new Sql();
            expected.Select("*")
                .From("[cmsDocument]")
                .InnerJoin("[cmsContentVersion]").On("[cmsDocument].[versionId] = [cmsContentVersion].[VersionId]")
                .InnerJoin("[cmsContent]").On("[cmsContentVersion].[ContentId] = [cmsContent].[nodeId]")
                .InnerJoin("[umbracoNode]").On("[cmsContent].[nodeId] = [umbracoNode].[id]")
                .Where("[umbracoNode].[nodeObjectType] = 'c66ba18e-eaf3-4cff-8a22-41b16d66a972'")
                .Where("[umbracoNode].[id] = 1050")
                .Where("[cmsContentVersion].[VersionId] = '2b543516-a944-4ee6-88c6-8813da7aaa07'")
                .OrderBy("[cmsContentVersion].[VersionDate] DESC");

            var sql = new Sql();
            sql.Select("*")
                .From<DocumentDto>()
                .InnerJoin<ContentVersionDto>()
                .On<DocumentDto, ContentVersionDto>(left => left.VersionId, right => right.VersionId)
                .InnerJoin<ContentDto>()
                .On<ContentVersionDto, ContentDto>(left => left.NodeId, right => right.NodeId)
                .InnerJoin<NodeDto>()
                .On<ContentDto, NodeDto>(left => left.NodeId, right => right.NodeId)
                .Where<NodeDto>(x => x.NodeObjectType == NodeObjectType)
                .Where<NodeDto>(x => x.NodeId == 1050)
                .Where<ContentVersionDto>(x => x.VersionId == versionId)
                .OrderByDescending<ContentVersionDto>(x => x.VersionDate);

            Assert.That(sql.SQL, Is.EqualTo(expected.SQL));

            Console.WriteLine(sql.SQL);
        }

        /// <summary>
        /// Loads the data value from the database.
        /// </summary>
        protected virtual void LoadValueFromDatabase()
        {
            var sql = new Sql();
            sql.Select("*")
               .From<PropertyDataDto>()
               .InnerJoin<PropertyTypeDto>()
               .On<PropertyTypeDto, PropertyDataDto>(x => x.Id, y => y.PropertyTypeId)
               .InnerJoin<DataTypeDto>()
               .On<DataTypeDto, PropertyTypeDto>(x => x.DataTypeId, y => y.DataTypeId)
               .Where<PropertyDataDto>(x => x.Id == _propertyId);
            var dto = Database.Fetch<PropertyDataDto, PropertyTypeDto, DataTypeDto>(sql).FirstOrDefault();

            if (dto != null)
            {
                //the type stored in the cmsDataType table
                var strDbType = dto.PropertyTypeDto.DataTypeDto.DbType;
                //get the enum of the data type
                var dbType = BaseDataType.GetDBType(strDbType);
                //get the column name in the cmsPropertyData table that stores the correct information for the data type
                var fieldName = BaseDataType.GetDataFieldName(dbType);
                //get the value for the data type, if null, set it to an empty string
                _value = dto.GetValue;
                //now that we've set our value, we can update our BaseDataType object with the correct values from the db
                //instead of making it query for itself. This is a peformance optimization enhancement.
                _dataType.SetDataTypeProperties(fieldName, dbType);
            }
        }

        public void Can_Verify_Base_Where_Clause()
        {
            var NodeObjectType = new Guid("A2CB7800-F571-4787-9638-BC48539A0EFB");

            var expected = new Sql();
            expected.Select("*")
                .From("[cmsDocumentType]")
                .RightJoin("[cmsContentType]")
                .On("[cmsContentType].[nodeId] = [cmsDocumentType].[contentTypeNodeId]")
                .InnerJoin("[umbracoNode]")
                .On("[cmsContentType].[nodeId] = [umbracoNode].[id]")
                .Where("[umbracoNode].[nodeObjectType] = 'a2cb7800-f571-4787-9638-bc48539a0efb'")
                .Where("[cmsDocumentType].[IsDefault] = 1")
                .Where("[umbracoNode].[id] = 1050");

            var sql = new Sql();
            sql.Select("*")
                .From<DocumentTypeDto>()
                .RightJoin<ContentTypeDto>()
                .On<ContentTypeDto, DocumentTypeDto>(left => left.NodeId, right => right.ContentTypeNodeId)
                .InnerJoin<NodeDto>()
                .On<ContentTypeDto, NodeDto>(left => left.NodeId, right => right.NodeId)
                .Where<NodeDto>(x => x.NodeObjectType == NodeObjectType)
                .Where<DocumentTypeDto>(x => x.IsDefault == true)
                .Where<NodeDto>(x => x.NodeId == 1050);

            Assert.That(sql.SQL, Is.EqualTo(expected.SQL));

            Console.WriteLine(sql.SQL);
        }

        /// <summary>
        /// Return the content override object for this content type and property type.
        /// </summary>
        /// <param name="contentTypeAlias"></param>
        /// <param name="propertyTypeAlias"></param>
        /// <param name="type"></param>
        /// <param name="configAlias"></param>
        /// <returns></returns>
        public static ContentOverrideDto GetContentOverride(
            string contentTypeAlias,
            string propertyTypeAlias,
            string archetypeAlias = null,
            int? nodeId = null,
            ContentOverrideType type = ContentOverrideType.Config,
            string configAlias = null
            )
        {
            var typeValue = type.ToString();

            var sql = new Sql();
            sql.Select("*")
                .From<ContentOverrideDto>()
                .Where<ContentOverrideDto>(x => x.ContentTypeAlias == contentTypeAlias)
                .Where<ContentOverrideDto>(x => x.PropertyTypeAlias == propertyTypeAlias)
                .Where<ContentOverrideDto>(x => x.Type == typeValue);

            if (!string.IsNullOrWhiteSpace(archetypeAlias))
            {
                sql.Where<ContentOverrideDto>(x => x.ArchetypeAlias == archetypeAlias);
            }

            if (nodeId.HasValue)
            {
                sql.Where<ContentOverrideDto>(x => x.NodeId == nodeId.Value);
            }

            if (type == ContentOverrideType.Config && !string.IsNullOrWhiteSpace(configAlias))
            {
                sql.Where<ContentOverrideDto>(x => x.ConfigAlias == configAlias);
            }

            return Database.SingleOrDefault<ContentOverrideDto>(sql);
        }

        public void Can_Verify_Base_Clause()
        {
            var NodeObjectType = new Guid(Constants.ObjectTypes.Document);

            var expected = new Sql();
            expected.Select("*")
                .From("[cmsDocument]")
                .InnerJoin("[cmsContentVersion]").On("[cmsDocument].[versionId] = [cmsContentVersion].[VersionId]")
                .InnerJoin("[cmsContent]").On("[cmsContentVersion].[ContentId] = [cmsContent].[nodeId]")
                .InnerJoin("[umbracoNode]").On("[cmsContent].[nodeId] = [umbracoNode].[id]")
                .Where("[umbracoNode].[nodeObjectType] = 'c66ba18e-eaf3-4cff-8a22-41b16d66a972'");

            var sql = new Sql();
            sql.Select("*")
                .From<DocumentDto>()
                .InnerJoin<ContentVersionDto>()
                .On<DocumentDto, ContentVersionDto>(left => left.VersionId, right => right.VersionId)
                .InnerJoin<ContentDto>()
                .On<ContentVersionDto, ContentDto>(left => left.NodeId, right => right.NodeId)
                .InnerJoin<NodeDto>()
                .On<ContentDto, NodeDto>(left => left.NodeId, right => right.NodeId)
                .Where<NodeDto>(x => x.NodeObjectType == NodeObjectType);

            Assert.That(sql.SQL, Is.EqualTo(expected.SQL));

            Console.WriteLine(sql.SQL);
        }

        void MigrationRunner_Migrated(MigrationRunner sender, Core.Events.MigrationEventArgs e)
        {
            var target = new Version(6, 0, 0);
            if (e.ConfiguredVersion < target)
            {
                var sql = new Sql();
                sql.Select("*")
                    .From<DocumentDto>()
                    .InnerJoin<ContentVersionDto>()
                    .On<DocumentDto, ContentVersionDto>(left => left.VersionId, right => right.VersionId)
                    .InnerJoin<ContentDto>()
                    .On<ContentVersionDto, ContentDto>(left => left.NodeId, right => right.NodeId)
                    .InnerJoin<NodeDto>()
                    .On<ContentDto, NodeDto>(left => left.NodeId, right => right.NodeId)
                    .Where<NodeDto>(x => x.NodeObjectType == new Guid(Constants.ObjectTypes.Document))
                    .Where<NodeDto>(x => x.Path.StartsWith("-1"));

                var uow = PetaPocoUnitOfWorkProvider.CreateUnitOfWork();

                var dtos = uow.Database.Fetch<DocumentDto, ContentVersionDto, ContentDto, NodeDto>(sql);
                var toUpdate = new List<DocumentDto>();
                var versionGroup = dtos.GroupBy(x => x.NodeId);
                foreach (var grp in versionGroup)
                {
                    var published = grp.FirstOrDefault(x => x.Published);
                    var newest = grp.FirstOrDefault(x => x.Newest);

                    if (newest != null)
                    {
                        double timeDiff = new TimeSpan(newest.UpdateDate.Ticks - newest.ContentVersionDto.VersionDate.Ticks).TotalMilliseconds;
                        var hasPendingChanges = timeDiff > 2000;

                        if (hasPendingChanges == false && published != null)
                        {
                            published.Published = false;
                            toUpdate.Add(published);
                            newest.Published = true;
                            toUpdate.Add(newest);
                        }
                    }
                }

                //Commit the updated entries for the cmsDocument table
                using (var transaction = uow.Database.GetTransaction())
                {
                    //Loop through the toUpdate
                    foreach (var dto in toUpdate)
                    {
                        uow.Database.Update(dto);
                    }

                    transaction.Complete();
                }
            }
        }

        public void Can_Get_A_Page_Of_Keys()
        {
            var sql = new Sql();
            sql.Select("*").Append("FROM merchInvoice");

            var page = _db.Page<KeyDto>(1, 10, sql);

            Assert.NotNull(page);
            Assert.AreEqual(15, page.TotalItems);
            Assert.AreEqual(2, page.TotalPages);
        }

        public void Can_Use_Where_Predicate()
        {
            var expected = new Sql();
            expected.Select("*").From("[cmsContent]").Where("[cmsContent].[nodeId] = 1045");

            var sql = new Sql();
            sql.Select("*").From<ContentDto>().Where<ContentDto>(x => x.NodeId == 1045);

            Assert.That(sql.SQL, Is.EqualTo(expected.SQL));

            Console.WriteLine(sql.SQL);
        }

        public void Can_GroupBy_With_Type()
        {
            var expected = new Sql();
            expected.Select("*").From("[cmsContent]").GroupBy("[contentType]");

            var sql = new Sql();
            sql.Select("*").From<ContentDto>().GroupBy<ContentDto>(x => x.ContentTypeId);

            Assert.That(sql.SQL, Is.EqualTo(expected.SQL));

            Console.WriteLine(sql.SQL);
        }

        public void Can_Select_From_With_Type()
        {
            var expected = new Sql();
            expected.Select("*").From("[cmsContent]");

            var sql = new Sql();
            sql.Select("*").From<ContentDto>();

            Assert.That(sql.SQL, Is.EqualTo(expected.SQL));

            Console.WriteLine(sql.SQL);
        }

        /// <summary>
        /// Gets a list of Ids for each of the items in the Recycle Bin.
        /// </summary>
        /// <param name="nodeObjectType"></param>
        /// <returns></returns>
        public List<int> GetIdsOfItemsInRecycleBin(Guid nodeObjectType)
        {
            var db = _unitOfWork.Database;

            var idsSql = new Sql();
            idsSql.Select("DISTINCT(id)")
                .From<NodeDto>()
                .Where<NodeDto>(x => x.Trashed && x.NodeObjectType == nodeObjectType);

            var ids = db.Fetch<int>(idsSql);
            return ids;
        }

        public void Dates_Formatted_Properly()
        {
            var sql = new Sql();
            sql.Select("*").From<DocumentDto>();

            var dt = new DateTime(2013, 11, 21, 13, 25, 55);

            var query = Query<IContent>.Builder.Where(x => x.ExpireDate <= dt);
            var translator = new SqlTranslator<IContent>(sql, query);

            var result = translator.Translate();

            Assert.IsTrue(result.SQL.Contains("[expireDate] <= '2013-11-21 13:25:55'"));
        }

        static void Main(string[] args)
        {
            var db = new Database("HA");

            var sql = new Sql<HouseData>("T");
            sql.Select(t => t.RegionName);
            var list = db.Fetch(sql);

            //var sql = new Sql<BaseDataDataModel>("T1");
            //sql.Select(t=>t.RowStatus).From();
            //sql.InnerJoin<BaseDataGroupDataModel>("T2").On((t1, t2) => t1.RowStatus == t2.RowStatus || t1.Name == t2.Name || 0 == t2.RowStatus);
            //sql.Where(t => t.RowStatus != 0 && !(t.Name == "eeee" || t.GroupCode == "tt"));
            //sql.Where(t => t.Id == 1000);
            //sql.Where("[email protected]", 100);
            //sql.OrderByDescending(t => t.Id, t => t.CreatedBy).OrderBy(t => t.GroupCode);

            //var list=db.Fetch(sql);

            //var group = new BaseDataGroupDataModel
            //{
            //    Code = "BD_Region",
            //    Name = "地区",
            //    CreatedBy = "system",
            //    CreatedOn = DateTime.Now
            //};
            //var bdList = new List<BaseDataDataModel>
            //{
            //    new BaseDataDataModel
            //    {
            //        Code = "051201",
            //        Name = "吴中区",
            //        CreatedBy = "system",
            //        CreatedOn = DateTime.Now,
            //        GroupCode = "BD_Region",
            //        Path = "/1/"
            //    },
            //    new BaseDataDataModel
            //    {
            //        Code = "051200",
            //        Name = "苏州市",
            //        CreatedBy = "system",
            //        CreatedOn = DateTime.Now,
            //        GroupCode = "BD_Region",
            //        Path = "/"
            //    }
            //};
            //db.Insert(group);
            //db.BulkInsert(bdList);
        }

		private UserDto GetUserDto(XmlNode xmlData)
		{
			var appName = this.GetAttributeValue(xmlData, "appName");
			var userLogin = this.GetAttributeValue(xmlData, "userLogin");

			if (string.Equals(userLogin, "$currentUser", StringComparison.OrdinalIgnoreCase))
				userLogin = UmbracoContext.Current.UmbracoUser.LoginName;

			var sql = new Sql();
			sql.Select("*")
				.From<UserDto>()
				.Where<UserDto>(x => x.Login == userLogin);

			return _database.Fetch<UserDto>(sql).FirstOrDefault();
		}

        public void Can_InnerJoin_With_Types()
        {
            var expected = new Sql();
            expected.Select("*")
                .From("[cmsDocument]")
                .InnerJoin("[cmsContentVersion]")
                .On("[cmsDocument].[versionId] = [cmsContentVersion].[VersionId]");

            var sql = new Sql();
            sql.Select("*").From<DocumentDto>()
                .InnerJoin<ContentVersionDto>()
                .On<DocumentDto, ContentVersionDto>(left => left.VersionId, right => right.VersionId);

            Assert.That(sql.SQL, Is.EqualTo(expected.SQL));

            Console.WriteLine(sql.SQL);
        }