C# Sql类代码示例

        public IEnumerable<UserSettings> GetAll()
        {
            var db = UmbracoContext.Application.DatabaseContext.Database;
            var query = new Sql().Select("*").From("ec_usettings");

            return db.Fetch<UserSettings>(query);
        }

 public IEnumerable<Schedule> GetAllSchedules()
 {
     var query = new Sql()
         .Select("*")
         .From(SchedulerConstants.Database.ScheduleTable);
     return DatabaseContext.Database.Fetch<Schedule>(query);
 }

 public IEnumerable<Models.Forum> GetForums(int rootId)
 {
     var sql = new Sql();
     sql.Where<Models.Forum>(x => x.ParentId == rootId);
     sql.OrderBy<Models.Forum>(x => x.SortOrder);
     return _databaseContext.Database.Fetch<Models.Forum>(sql);
 }

        internal static PostsByTagModel GetContentByTag(this UmbracoHelper helper, IMasterModel masterModel, string tag, string tagGroup, string baseUrlName)
        {
            //TODO: Use the new 7.1.2 tags API to do this

            //TODO: Umbraco core needs to have a method to get content by tag(s), in the meantime we
            // need to run a query
            var sql = new Sql().Select("cmsTagRelationship.nodeId, cmsTagRelationship.tagId, cmsTags.tag")
                .From("cmsTagRelationship")
                .InnerJoin("cmsTags")
                .On("cmsTagRelationship.tagId = cmsTags.id")
                .Where("cmsTags.tag = @tagName AND cmsTags." + SqlSyntaxContext.SqlSyntaxProvider.GetQuotedColumnName("group") + " = @tagGroup", new
                {
                    tagName = tag,
                    tagGroup = tagGroup
                });

            var taggedContent = ApplicationContext.Current.DatabaseContext.Database.Fetch<TagDto>(sql);
            return taggedContent.GroupBy(x => x.TagId)
                .Select(x => new PostsByTagModel(
                    helper.TypedContent(
                        x.Select(t => t.NodeId).Distinct())
                        .Select(c => new PostModel(c)).OrderByDescending(c => c.PublishedDate),
                    x.First().Tag,
                    masterModel.RootBlogNode.Url.EnsureEndsWith('/') + baseUrlName + "/" + x.First().Tag.ToLowerInvariant()))
                .FirstOrDefault();
        }

        public static DbCommand GetCommand(this MapperDb db, Sql sql)
        {
            var command = db.DbFactory.CreateCommand();
            if (command == null)
            {
                throw new MapperException("Could not create database command.");
            }

            command.CommandText = sql.GetQuery();
            var parameters = sql.GetParameterValues();

            //Console.WriteLine(sql.GetQuery());
            //Console.WriteLine(string.Join(",", sql.GetParameterValues()));

            foreach (int arg in sql.GetParameters())
            {
                var parameter = db.DbFactory.CreateParameter();

                if (parameter == null)
                {
                    throw new MapperException("Could not create database command.");
                }

                parameter.ParameterName = "@" + arg;
                parameter.Value = parameters[arg] ?? DBNull.Value;
                command.Parameters.Add(parameter);
            }

            return command;
        }

        public IEnumerable<object> GetAll(string typeName, string sortColumn, string sortOrder)
        {
            var currentType = Type.GetType(typeName);
            var tableName = (TableNameAttribute)Attribute.GetCustomAttribute(currentType, typeof(TableNameAttribute));
            var uioMaticAttri = (UIOMaticAttribute)Attribute.GetCustomAttribute(currentType, typeof(UIOMaticAttribute));
            var strTableName = tableName.Value;

            var db = (Database)DatabaseContext.Database;
            if(!string.IsNullOrEmpty(uioMaticAttri.ConnectionStringName))
                db = new Database(uioMaticAttri.ConnectionStringName);

            if (strTableName.IndexOf("[") < 0)
            {
                strTableName = "[" + strTableName + "]";
            }

            var query = new Sql().Select("*").From(strTableName);


            if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortOrder))
            {
                var strSortColumn = sortColumn;
                if (strSortColumn.IndexOf("[") < 0)
                {
                    strSortColumn = "[" + strSortColumn + "]";
                }

                query.OrderBy(strSortColumn + " " + sortOrder);
            }

            foreach (dynamic item in db.Fetch<dynamic>(query))
            {
                // get settable public properties of the type
                var props = currentType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
                    .Where(x => x.GetSetMethod() != null);

                // create an instance of the type
                var obj = Activator.CreateInstance(currentType);
                

                // set property values using reflection
                var values = (IDictionary<string, object>)item;
                foreach (var prop in props)
                {
                    var columnAttri =
                           prop.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));

                    var propName = prop.Name;
                    if (columnAttri.Any())
                        propName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
                    if(values.ContainsKey(propName))
                        prop.SetValue(obj, values[propName]);
                }

                yield return obj;
            }
            

            
        }

        public static EventLocation GetLocation(int id)
        {
            var db = ApplicationContext.Current.DatabaseContext.Database;
            var query = new Sql().Select("*").From("ec_locations").Where<EventLocation>(x => x.Id == id);

            return db.Fetch<EventLocation>(query).FirstOrDefault();
        }

        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);
        }

        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 virtual async Task NonQueryAsync(MapperDb db, Sql sql)
 {
     using (var command = db.GetCommand(sql))
     {
         await this.NonQueryAsync(db, command).ConfigureAwait(false);
     }
 }

        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);
        }

        public static IEnumerable<RecurringEvent> GetAllEvents()
        {
            var db = ApplicationContext.Current.DatabaseContext.Database;
            var query = new Sql().Select("*").From("ec_recevents");

            return db.Fetch<RecurringEvent>(query);
        }

    public IEnumerable<Person> GetAll()
    {
        var db = UmbracoContext.Application.DatabaseContext.Database;
        var query = new Sql().Select("*").From("people");

        return db.Fetch<Person>(query);
    }

    public IEnumerable<Ingredient> GetAll()
    {
        var db = UmbracoContext.Application.DatabaseContext.Database;
        var query = new Sql().Select("*").From("ingredient");

        return db.Fetch<Ingredient>(query);
    }