面试被问到了,应该是和Android的RecycleView一样的东西,对长列表,只渲染需要显的部分。作用于ListItem和TreeItem。

怎么实现没看明白,留份样板备用。

源码在这里:WPFControls

public class VirtualizingWrapPanel : VirtualizingPanel, IScrollInfo
{
    private TranslateTransform trans = new TranslateTransform();

    public VirtualizingWrapPanel()
    {
        this.RenderTransform = trans;
    }

    #region DependencyProperties
    public static readonly DependencyProperty ChildWidthProperty = DependencyProperty.RegisterAttached("ChildWidth", typeof(double), typeof(VirtualizingWrapPanel), new FrameworkPropertyMetadata(200.0, FrameworkPropertyMetadataOptions.AffectsMeasure | FrameworkPropertyMetadataOptions.AffectsArrange));

    public static readonly DependencyProperty ChildHeightProperty = DependencyProperty.RegisterAttached("ChildHeight", typeof(double), typeof(VirtualizingWrapPanel), new FrameworkPropertyMetadata(200.0, FrameworkPropertyMetadataOptions.AffectsMeasure | FrameworkPropertyMetadataOptions.AffectsArrange));

    //鼠标每一次滚动 UI上的偏移
    public static readonly DependencyProperty ScrollOffsetProperty = DependencyProperty.RegisterAttached("ScrollOffset", typeof(int), typeof(VirtualizingWrapPanel), new PropertyMetadata(10));

    public int ScrollOffset
    {
        get { return Convert.ToInt32(GetValue(ScrollOffsetProperty)); }
        set { SetValue(ScrollOffsetProperty, value); }
    }
    public double ChildWidth
    {
        get => Convert.ToDouble(GetValue(ChildWidthProperty));
        set => SetValue(ChildWidthProperty, value);
    }
    public double ChildHeight
    {
        get => Convert.ToDouble(GetValue(ChildHeightProperty));
        set => SetValue(ChildHeightProperty, value);
    }
    #endregion

    int GetItemCount(DependencyObject element)
    {
        var itemsControl = ItemsControl.GetItemsOwner(element);
        return itemsControl.HasItems ? itemsControl.Items.Count : 0;
    }
    int CalculateChildrenPerRow(Size availableSize)
    {
        int childPerRow = 0;
        if (availableSize.Width == double.PositiveInfinity)
            childPerRow = this.Children.Count;
        else
            childPerRow = Math.Max(1, Convert.ToInt32(Math.Floor(availableSize.Width / this.ChildWidth)));
        return childPerRow;
    }
    /// <summary>
    /// width不超过availableSize的情况下,自身实际需要的Size(高度可能会超出availableSize)
    /// </summary>
    /// <param name="availableSize"></param>
    /// <param name="itemsCount"></param>
    /// <returns></returns>
    Size CalculateExtent(Size availableSize, int itemsCount)
    {
        int childPerRow = CalculateChildrenPerRow(availableSize);//现有宽度下 一行可以最多容纳多少个
        return new Size(childPerRow * this.ChildWidth, this.ChildHeight * Math.Ceiling(Convert.ToDouble(itemsCount) / childPerRow));
    }
    /// <summary>
    /// 更新滚动条
    /// </summary>
    /// <param name="availableSize"></param>
    void UpdateScrollInfo(Size availableSize)
    {
        var extent = CalculateExtent(availableSize, GetItemCount(this));//extent 自己实际需要
        if (extent != this.extent)
        {
            this.extent = extent;
            this.ScrollOwner.InvalidateScrollInfo();
        }
        if (availableSize != this.viewPort)
        {
            this.viewPort = availableSize;
            this.ScrollOwner.InvalidateScrollInfo();
        }
    }
    /// <summary>
    /// 获取所有item,在可视区域内第一个item和最后一个item的索引
    /// </summary>
    /// <param name="firstIndex"></param>
    /// <param name="lastIndex"></param>
    void GetVisiableRange(ref int firstIndex, ref int lastIndex)
    {
        int childPerRow = CalculateChildrenPerRow(this.extent);
        firstIndex = Convert.ToInt32(Math.Floor(this.offset.Y / this.ChildHeight)) * childPerRow;
        lastIndex = Convert.ToInt32(Math.Ceiling((this.offset.Y + this.viewPort.Height) / this.ChildHeight)) * childPerRow - 1;
        int itemsCount = GetItemCount(this);
        if (lastIndex >= itemsCount)
            lastIndex = itemsCount - 1;

    }
    /// <summary>
    /// 将不在可视区域内的item 移除
    /// </summary>
    /// <param name="startIndex">可视区域开始索引</param>
    /// <param name="endIndex">可视区域结束索引</param>
    void CleanUpItems(int startIndex, int endIndex)
    {
        var children = this.InternalChildren;
        var generator = this.ItemContainerGenerator;
        for (int i = children.Count - 1; i >= 0; i--)
        {
            var childGeneratorPosi = new GeneratorPosition(i, 0);
            int itemIndex = generator.IndexFromGeneratorPosition(childGeneratorPosi);

            if (itemIndex < startIndex || itemIndex > endIndex)
            {

                generator.Remove(childGeneratorPosi, 1);
                RemoveInternalChildRange(i, 1);
            }
        }
    }
    /// <summary>
    /// scroll/availableSize/添加删除元素 改变都会触发  edit元素不会改变
    /// </summary>
    /// <param name="availableSize"></param>
    /// <returns></returns>
    protected override Size MeasureOverride(Size availableSize)
    {
        this.UpdateScrollInfo(availableSize);//availableSize更新后,更新滚动条
        int firstVisiableIndex = 0, lastVisiableIndex = 0;
        GetVisiableRange(ref firstVisiableIndex, ref lastVisiableIndex);//availableSize更新后,获取当前viewport内可放置的item的开始和结束索引  firstIdnex-lastIndex之间的item可能部分在viewport中也可能都不在viewport中。

        UIElementCollection children = this.InternalChildren;//因为配置了虚拟化,所以children的个数一直是viewport区域内的个数,如果没有虚拟化则是ItemSource的整个的个数
        IItemContainerGenerator generator = this.ItemContainerGenerator;
        //获得第一个可被显示的item的位置
        GeneratorPosition startPosi = generator.GeneratorPositionFromIndex(firstVisiableIndex);
        int childIndex = (startPosi.Offset == 0) ? startPosi.Index : startPosi.Index + 1;//startPosi在chilren中的索引
        using (generator.StartAt(startPosi, GeneratorDirection.Forward, true))
        {
            int itemIndex = firstVisiableIndex;
            while (itemIndex <= lastVisiableIndex)//生成lastVisiableIndex-firstVisiableIndex个item
            {
                bool newlyRealized = false;
                var child = generator.GenerateNext(out newlyRealized) as UIElement;
                if (newlyRealized)
                {
                    if (childIndex >= children.Count)
                        base.AddInternalChild(child);
                    else
                    {
                        base.InsertInternalChild(childIndex, child);
                    }
                    generator.PrepareItemContainer(child);
                }
                else
                {
                    //处理 正在显示的child被移除了这种情况
                    if (!child.Equals(children[childIndex]))
                    {
                        base.RemoveInternalChildRange(childIndex, 1);
                    }
                }
                child.Measure(new Size(this.ChildWidth, this.ChildHeight));
                //child.DesiredSize//child想要的size
                itemIndex++;
                childIndex++;
            }
        }
        CleanUpItems(firstVisiableIndex, lastVisiableIndex);
        return new Size(double.IsInfinity(availableSize.Width) ? 0 : availableSize.Width, double.IsInfinity(availableSize.Height) ? 0 : availableSize.Height);//自身想要的size
    }
    protected override Size ArrangeOverride(Size finalSize)
    {
        Debug.WriteLine("----ArrangeOverride");
        var generator = this.ItemContainerGenerator;
        UpdateScrollInfo(finalSize);
        int childPerRow = CalculateChildrenPerRow(finalSize);
        double availableItemWidth = finalSize.Width / childPerRow;
        for (int i = 0; i <= this.Children.Count - 1; i++)
        {
            var child = this.Children[i];
            int itemIndex = generator.IndexFromGeneratorPosition(new GeneratorPosition(i, 0));
            int row = itemIndex / childPerRow;//current row
            int column = itemIndex % childPerRow;
            double xCorrdForItem = 0;

            xCorrdForItem = column * availableItemWidth + (availableItemWidth - this.ChildWidth) / 2;

            Rect rec = new Rect(xCorrdForItem, row * this.ChildHeight, this.ChildWidth, this.ChildHeight);
            child.Arrange(rec);
        }
        return finalSize;
    }
    protected override void OnRenderSizeChanged(SizeChangedInfo sizeInfo)
    {
        base.OnRenderSizeChanged(sizeInfo);
        this.SetVerticalOffset(this.VerticalOffset);
    }
    protected override void OnClearChildren()
    {
        base.OnClearChildren();
        this.SetVerticalOffset(0);
    }
    protected override void BringIndexIntoView(int index)
    {
        if (index < 0 || index >= Children.Count)
            throw new ArgumentOutOfRangeException();
        int row = index / CalculateChildrenPerRow(RenderSize);
        SetVerticalOffset(row * this.ChildHeight);
    }
    #region IScrollInfo Interface
    public bool CanVerticallyScroll { get; set; }
    public bool CanHorizontallyScroll { get; set; }

    private Size extent = new Size(0, 0);
    public double ExtentWidth => this.extent.Width;

    public double ExtentHeight => this.extent.Height;

    private Size viewPort = new Size(0, 0);
    public double ViewportWidth => this.viewPort.Width;

    public double ViewportHeight => this.viewPort.Height;

    private Point offset;
    public double HorizontalOffset => this.offset.X;

    public double VerticalOffset => this.offset.Y;

    public ScrollViewer ScrollOwner { get; set; }

    public void LineDown()
    {
        this.SetVerticalOffset(this.VerticalOffset + this.ScrollOffset);
    }

    public void LineLeft()
    {
        throw new NotImplementedException();
    }

    public void LineRight()
    {
        throw new NotImplementedException();
    }

    public void LineUp()
    {
        this.SetVerticalOffset(this.VerticalOffset - this.ScrollOffset);
    }

    public Rect MakeVisible(Visual visual, Rect rectangle)
    {
        return new Rect();
    }

    public void MouseWheelDown()
    {
        this.SetVerticalOffset(this.VerticalOffset + this.ScrollOffset);
    }

    public void MouseWheelLeft()
    {
        throw new NotImplementedException();
    }

    public void MouseWheelRight()
    {
        throw new NotImplementedException();
    }

    public void MouseWheelUp()
    {
        this.SetVerticalOffset(this.VerticalOffset - this.ScrollOffset);
    }

    public void PageDown()
    {
        this.SetVerticalOffset(this.VerticalOffset + this.viewPort.Height);
    }

    public void PageLeft()
    {
        throw new NotImplementedException();
    }

    public void PageRight()
    {
        throw new NotImplementedException();
    }

    public void PageUp()
    {
        this.SetVerticalOffset(this.VerticalOffset - this.viewPort.Height);
    }

    public void SetHorizontalOffset(double offset)
    {
        throw new NotImplementedException();
    }

    public void SetVerticalOffset(double offset)
    {
        if (offset < 0 || this.viewPort.Height >= this.extent.Height)
            offset = 0;
        else
            if (offset + this.viewPort.Height >= this.extent.Height)
            offset = this.extent.Height - this.viewPort.Height;

        this.offset.Y = offset;
        this.ScrollOwner?.InvalidateScrollInfo();
        this.trans.Y = -offset;
        this.InvalidateMeasure();
        //接下来会触发MeasureOverride()
    }
    #endregion
}


久了没写WPF会忘了

WPF控件模板

动画

属性值更改时触发动画

<Window x:Class="ControlTemplateDemo.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:ControlTemplateDemo"
        mc:Ignorable="d"
        Title="MainWindow" SizeToContent="WidthAndHeight" MinWidth="250">
    <Window.Resources>
        <ControlTemplate x:Key="roundbutton" TargetType="Button">
            <Grid>
                <VisualStateManager.VisualStateGroups>
                    <!--用VisualState管理动画-->
                    <!--<VisualStateGroup Name="CommonStates">
                        <VisualState Name="Normal">
                            <Storyboard>
                                <ColorAnimation Storyboard.TargetName="backgroundElement" 
                                    Storyboard.TargetProperty="(Shape.Fill).(SolidColorBrush.Color)"
                                    To="{TemplateBinding Background}"
                                    Duration="0:0:0.3"/>
                            </Storyboard>
                        </VisualState>
                        <VisualState Name="MouseOver">
                            <Storyboard>
                                <ColorAnimation Storyboard.TargetName="backgroundElement" 
                                    Storyboard.TargetProperty="(Shape.Fill).(SolidColorBrush.Color)" 
                                    To="Yellow" 
                                    Duration="0:0:0.3"/>
                            </Storyboard>
                        </VisualState>
                    </VisualStateGroup>-->
                </VisualStateManager.VisualStateGroups>
                <Ellipse x:Name="backgroundElement" Fill="{TemplateBinding Background}" Stroke="{TemplateBinding Foreground}" />
                <ContentPresenter HorizontalAlignment="Center" VerticalAlignment="Center" />
            </Grid>
            <ControlTemplate.Triggers>
                <Trigger Property="IsMouseOver" Value="true">
                    <!--用Triggers触发动画-->
                    <Setter Property="Fill" TargetName="backgroundElement" Value="Yellow"/>
                    <Trigger.EnterActions>
                        <BeginStoryboard>
                            <Storyboard>
                                <DoubleAnimation Storyboard.TargetProperty="Opacity"
                                                 From="1"
                                    To="0" Duration="0:0:1" />
                            </Storyboard>
                        </BeginStoryboard>
                    </Trigger.EnterActions>
                    <Trigger.ExitActions>
                        <BeginStoryboard>
                            <Storyboard>
                                <DoubleAnimation Storyboard.TargetProperty="Opacity"
                                                 From="0"
                                    To="1" Duration="0:0:3" />
                            </Storyboard>
                        </BeginStoryboard>
                    </Trigger.ExitActions>
                </Trigger>
            </ControlTemplate.Triggers>
        </ControlTemplate>
    </Window.Resources>
    <StackPanel Margin="10">

        <Label>Unstyled Button</Label>
        <Button>button1</Button>
        <Label>Rounded Button</Label>
        <Button Template="{StaticResource roundbutton}" Width="65" Height="65">
            Button2
            <!--<CheckBox>Checkbox in a button</CheckBox>-->
        </Button>
    </StackPanel>

</Window>

params 修饰符

TryParamsCalls();

static void ParamsModifierExample(params int[] list)
{
    for (int i = 0; i < list.Length; i++)
    {
        System.Console.Write(list[i] + " ");
    }
    System.Console.WriteLine();
}

static void ParamsModifierObjectExample(params object[] list)
{
    for (int i = 0; i < list.Length; i++)
    {
        System.Console.Write(list[i] + " ");
    }
    System.Console.WriteLine();
}

static void TryParamsCalls()
{
    // You can send a comma-separated list of arguments of the
    // specified type.
    ParamsModifierExample(1, 2, 3, 4);
    ParamsModifierObjectExample(1, 'a', "test");

    // A params parameter accepts zero or more arguments.
    // The following calling statement displays only a blank line.
    ParamsModifierObjectExample();

    // An array argument can be passed, as long as the array
    // type matches the parameter type of the method being called.
    int[] myIntArray = { 5, 6, 7, 8, 9 };
    ParamsModifierExample(myIntArray);

    object[] myObjArray = { 2, 'b', "test", "again", myIntArray };
    //输出 2 b test again System.Int32[]
    ParamsModifierObjectExample(myObjArray);

    //输出System.Int32[], 因为myIntArray整体作为一对象,参考上面输出
    ParamsModifierObjectExample(myIntArray);

    // The following call causes a compiler error because the object
    // array cannot be converted into an integer array.
    //ParamsModifierExample(myObjArray);    
}
/*
Output:
    1 2 3 4
    1 a test

    5 6 7 8 9
    2 b test again System.Int32[]
    System.Int32[]

*/

创建 DatabaseLog 表和 DLL 触发器 DDL_DatabaseLog,记录修改表结构操作

USE mjkq;
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'DatabaseLog')
BEGIN
    DROP TABLE  [dbo].[DatabaseLog];
END;
GO
CREATE TABLE [dbo].[DatabaseLog](
    [DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,
    [PostTime] [datetime] NOT NULL,
    [ServerName] [sysname] NOT NULL,
    [LoginName] [sysname] NOT NULL,
    [ClientIp] varchar(20) NULL,
    [DatabaseUser] [sysname] NOT NULL,
    [DatabaseName] [sysname] NOT NULL,
    [Schema] [sysname] NULL,
    [Object] [sysname] NULL,
    [TSQL] [nvarchar](max) NOT NULL,
    [Event] [sysname] NOT NULL,
    [XmlEvent] [xml] NOT NULL,
 CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED 
(
    [DatabaseLogID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
-- 删除现有触发器
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'DDL_DatabaseLog')
BEGIN
    DROP TRIGGER [DDL_DatabaseLog] ON DATABASE;
END;
GO
-- 创建触发器
CREATE TRIGGER [DDL_DatabaseLog]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
    SET NOCOUNT ON;
    
    -- 声明变量
    DECLARE @data XML;
    DECLARE @schema sysname;
    DECLARE @object sysname;
    DECLARE @eventType sysname;
    DECLARE @clientip varchar(20);

    -- 获取事件数据
    SET @data = EVENTDATA();
    SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');
    SELECT @clientip = client_net_address from sys.dm_exec_connections WHERE session_id  = @@SPID;
    
    -- 打印调试信息
    IF @object IS NOT NULL
        PRINT '  ' + @eventType + ' - ' + @schema + '.' + @object;
    ELSE
        PRINT '  ' + @eventType + ' - ' + @schema;
        
    IF @eventType IS NULL
        PRINT CONVERT(nvarchar(max), @data);
    
    -- 插入数据库日志
    INSERT [mjkq].[dbo].[DatabaseLog](
        [PostTime], 
        [ServerName], 
        [LoginName], 
        [ClientIp],
        [DatabaseUser], 
        [DatabaseName],
        [Schema], 
        [Object], 
        [TSQL], 
        [Event], 
        [XmlEvent])
    VALUES(
        GETDATE(), 
        @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname'), 
        @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'), 
        @clientip,
        CONVERT(sysname, CURRENT_USER),
        @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname'),  
        CONVERT(sysname, @schema), 
        CONVERT(sysname, @object), 
        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
        @eventType,  
        @data
    );
END;

GO
CREATE TABLE TestTable(a int)

GO
DROP TABLE TestTable;

SELECT * FROM [DatabaseLog];
GO

创建DDL DDL_TableTrigger 触发器,禁止修改或者删除数据表

--创建DDL触发器:禁止修改或者删除数据表
CREATE TRIGGER DDL_TableTrigger
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
   PRINT '对不起,您不能对数据表进行操作,请联系DBA'
   ROLLBACK ;

SQL Server DDL触发器运用
DLL触发器


SELECT TOP 20 
    total_worker_time/1000 AS [总消耗CPU 时间(ms)],
    execution_count [运行次数],
    qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
    last_execution_time AS [最后一次执行时间],
    min_worker_time /1000 AS [最小执行时间(ms)],
    max_worker_time /1000 AS [最大执行时间(ms)],
    SUBSTRING(qt.text,qs.statement_start_offset/2+1, 
    (CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(qt.text) 
    ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) AS [使用CPU的语法],
    qt.text [完整语法],
    qt.dbid,
    dbname=db_name(qt.dbid),
    qt.objectid,
    object_name(qt.objectid,qt.dbid) AS ObjectName
FROM 
    sys.dm_exec_query_stats qs WITH(nolock)
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE 
    execution_count>1
ORDER BY 
    (qs.total_worker_time/qs.execution_count/1000) DESC;

排查 SQL Server 中的高 CPU 使用率问题