问:
Change a ListView's displayed columns based on a DependencyProperty
I have a wpf control that can be used in two different windows. The control contains a ListView, which is fed by an ObservableCollection of the same class, regardless of which window is hosting the control.

In one window I want to show a certain set of columns, and in the other window a different set of columns.

I have included a trivial example of what I am trying to accomplish. For the purposes of this example, the xml is contained in a window rather than a UserControl.

Here is the xaml that defines the window and its two ListViews:

```<code><Window x:Class="ListTest.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:ListTest"
    mc:Ignorable="d"
    Title="MainWindow" Height="350" Width="525">
<Window.DataContext>
    <local:MainViewModel />
</Window.DataContext>
<Window.Resources>
    <ControlTemplate x:Key="listOne"  TargetType="{x:Type ListView}">
        <ListView Margin="10,30,10,10" ItemsSource="{Binding MyList}">
            <ListView.View>
                <GridView>
                    <GridViewColumn Header="Name" Width="120" DisplayMemberBinding="{Binding Name}" />
                    <GridViewColumn Header="Food" Width="50" DisplayMemberBinding="{Binding Food}" />
                </GridView>
            </ListView.View>
        </ListView>
    </ControlTemplate>
    <ControlTemplate x:Key="listTwo"  TargetType="{x:Type ListView}">
        <ListView Margin="10,30,10,10" ItemsSource="{Binding MyList}">
            <ListView.View>
                <GridView>
                    <GridViewColumn Header="Name" Width="120" DisplayMemberBinding="{Binding Name}" />
                    <GridViewColumn Header="Number" Width="120" DisplayMemberBinding="{Binding Number}" />
                    <GridViewColumn Header="State" Width="50" DisplayMemberBinding="{Binding State}" />
                </GridView>
            </ListView.View>
        </ListView>
    </ControlTemplate>
</Window.Resources>
<Grid>
    <CheckBox x:Name="checkBox" Content="Complex" HorizontalAlignment="Left"
              Margin="10,10,10,10" VerticalAlignment="Top"
              IsChecked="{Binding IsComplex}"/>
    <ListView Margin="10" Name="lvUsers" Template="{StaticResource listTwo}" />
</Grid>
</code>```

This is my trivial viewmodel, and the record class:

```<code>    public class MyRecord
{
    public MyRecord(string firstName, string food, int number, string state)
    {
        Name = firstName;
        Food = food;
        Number = number;
        State = state;
    }

    public string Name { get; set; }
    public string Food { get; set; }
    public int Number { get; set; }
    public string State { get; set; }
}

public class MainViewModel : ViewModelBase
{
    private List<MyRecord> _recordList;

    public MainViewModel()
    {
        _recordList = new List<MyRecord>();
        _recordList = new List<MyRecord>();
        _recordList.Add(new MyRecord("Lee", "pizza", 10, "ID"));
        _recordList.Add(new MyRecord("Gary", "burger", 20, "UT"));

        MyList = new ObservableCollection<MyRecord>(_recordList);
    }

    private ObservableCollection<MyRecord> _myList;
    public ObservableCollection<MyRecord> MyList
    {
        get { return _myList; }
        set
        {
            if (_myList != value)
            {
                _myList = value;
                OnPropertyChanged(() => MyList);
            }
        }
    }

    private bool _isComplex = true;
    public bool IsComplex
    {
        get { return _isComplex; }
        set
        {
            if (_isComplex != value)
            {
                _isComplex = value;
                OnPropertyChanged(() => IsComplex);
            }
        }
    }
}
</code>```

The next-to-last line of the xaml has a hard-coded Template assignment:

```<code>        <ListView Margin="10" Name="lvUsers" Template="{StaticResource listTwo}" />
</code>```

Changing that back and forth in the xaml results in the program displaying one ListView layout or the other without error.

I want to be able to set a property in the ViewModel that will control which layout is used - in this trivial case, I have a checkbox that should control the selected ListView.

I've tried triggers, which seems to be the simplest approach, but haven't found anything that makes the compiler happy.

Any suggestions would be appreciated!

Update:
Ed Plunkett's response showed me that I was making my question too hard. I didn't want to replace the whole ListView, just control what columns were displayed within it. Extracting a bit of his code results in exactly the behavior I wanted originally without going into the code-behind and replacing the entire ListView. The displayed columns in my sample now switch to the correct "view" when I toggle the checkbox. The code-behind is untouched, and the viewmodel remains the same. Thanks Ed! I've accepted his answer because it showed me the subset of code I needed, and I've changed the Title to reflect what the real question was.

This is the complete revised xaml:

```<code><Window x:Class="AAWorkTest.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:ListTest"
    mc:Ignorable="d"
    Title="MainWindow" Height="350" Width="525">
<Window.DataContext>
    <local:MainViewModel />
</Window.DataContext>
<Grid>
    <CheckBox x:Name="checkBox" Content="Complex" HorizontalAlignment="Left"
              Margin="10,10,10,10" VerticalAlignment="Top"
              IsChecked="{Binding IsComplex}"/>
    <ListView ItemsSource="{Binding MyList}" Margin="10,30,10,30" Name="lvUsers">
        <ListView.Style>
            <Style TargetType="ListView">
                <Style.Triggers>
                    <DataTrigger Binding="{Binding IsComplex}" Value="False">
                        <Setter Property="View">
                            <Setter.Value>
                                <GridView>
                                    <GridViewColumn Header="Name" Width="120" DisplayMemberBinding="{Binding Name}" />
                                    <GridViewColumn Header="Food" Width="50" DisplayMemberBinding="{Binding Food}" />
                                </GridView>
                            </Setter.Value>
                        </Setter>
                    </DataTrigger>
                    <DataTrigger Binding="{Binding IsComplex}" Value="True">
                        <Setter Property="View">
                            <Setter.Value>
                                <GridView>
                                    <GridViewColumn Header="Name" Width="120" DisplayMemberBinding="{Binding Name}" />
                                    <GridViewColumn Header="Number" Width="120" DisplayMemberBinding="{Binding Number}" />
                                    <GridViewColumn Header="State" Width="50" DisplayMemberBinding="{Binding State}" />
                                </GridView>
                            </Setter.Value>
                        </Setter>
                    </DataTrigger>
                </Style.Triggers>
            </Style>
        </ListView.Style>
    </ListView>
</Grid>
</code>```

答:
You don't set properties on a control by replacing the template with one that creates a new, nested instance of the control with different properties. In WPF, a ControlTemplate determines how a control is displayed, it doesn't create the control. Instead, you set properties with a style that sets the properties. If it were a good idea to change the ListView's template, this is how you would do that.

Here's how you can do this (I don't recommend naming it UserControl1, of course):

UserControl1.xaml.cs

```<code>public partial class UserControl1 : UserControl
{
    public UserControl1()
    {
        InitializeComponent();
    }

    public IEnumerable ItemsSource
    {
        get { return (IEnumerable)GetValue(ItemsSourceProperty); }
        set { SetValue(ItemsSourceProperty, value); }
    }

    public static readonly DependencyProperty ItemsSourceProperty =
        DependencyProperty.Register(nameof(ItemsSource), typeof(IEnumerable), typeof(UserControl1),
            new PropertyMetadata(null));

    public ViewPurpose ViewPurpose
    {
        get { return (ViewPurpose)GetValue(ViewPurposeProperty); }
        set { SetValue(ViewPurposeProperty, value); }
    }

    public static readonly DependencyProperty ViewPurposeProperty =
        DependencyProperty.Register(nameof(ViewPurpose), typeof(ViewPurpose), typeof(UserControl1),
            new PropertyMetadata(ViewPurpose.None));
}

public enum ViewPurpose
{
    None,
    FoodPreference,
    ContactInfo,
    FredBarneyWilma
}
</code>```

UserControl1.xaml

<UserControl
    x:Class="WpfApp3.UserControl1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:local="clr-namespace:WpfApp3"
    mc:Ignorable="d"
    d:DesignHeight="300" d:DesignWidth="300">
    <Grid>
        <ListView
            ItemsSource="{Binding ItemsSource, RelativeSource={RelativeSource AncestorType=UserControl}}"
            >
            <ListView.Style>

                </Style>
            </ListView.Style>
        </ListView>
    </Grid>
</UserControl>
```

<p>Usage example:</p>

<pre class="lang-xml prettyprint-override"><code><StackPanel Orientation="Vertical">
    <local:UserControl1
        ViewPurpose="FoodPreference"
        ItemsSource="{Binding SomeCollectionOfWhatever}"
        />
    <local:UserControl1
        ViewPurpose="ContactInfo"
        ItemsSource="{Binding DifferentCollectionOfWhatever}"
        />
</StackPanel>
</code>```

The enum is one option for specifying a set of columns. You could also give it a collection of column name, or a single string delimited by some special character ("Name|Food|Gas|Lodging") that would be split, and then do something in the UserControl to create the collection of columns based on that.

But if you have two or three predefined collections of columns, with custom widths and so on, this is quick and simple and does the job. You don't need to get too clever with this one.

Body:
I have a wpf control that can be used in two different windows. The control contains a ListView, which is fed by an ObservableCollection of the same class, regardless of which window is hosting the control.

In one window I want to show a certain set of columns, and in the other window a different set of columns.

I have included a trivial example of what I am trying to accomplish. For the purposes of this example, the xml is contained in a window rather than a UserControl.

Here is the xaml that defines the window and its two ListViews:

<Window x:Class="ListTest.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:ListTest"
    mc:Ignorable="d"
    Title="MainWindow" Height="350" Width="525">
<Window.DataContext>
    <local:MainViewModel />
</Window.DataContext>
<Window.Resources>
    <ControlTemplate x:Key="listOne"  TargetType="{x:Type ListView}">
        <ListView Margin="10,30,10,10" ItemsSource="{Binding MyList}">
            <ListView.View>
                <GridView>
                    <GridViewColumn Header="Name" Width="120" DisplayMemberBinding="{Binding Name}" />
                    <GridViewColumn Header="Food" Width="50" DisplayMemberBinding="{Binding Food}" />
                </GridView>
            </ListView.View>
        </ListView>
    </ControlTemplate>
    <ControlTemplate x:Key="listTwo"  TargetType="{x:Type ListView}">
        <ListView Margin="10,30,10,10" ItemsSource="{Binding MyList}">
            <ListView.View>
                <GridView>
                    <GridViewColumn Header="Name" Width="120" DisplayMemberBinding="{Binding Name}" />
                    <GridViewColumn Header="Number" Width="120" DisplayMemberBinding="{Binding Number}" />
                    <GridViewColumn Header="State" Width="50" DisplayMemberBinding="{Binding State}" />
                </GridView>
            </ListView.View>
        </ListView>
    </ControlTemplate>
</Window.Resources>
<Grid>
    <CheckBox x:Name="checkBox" Content="Complex" HorizontalAlignment="Left"
              Margin="10,10,10,10" VerticalAlignment="Top"
              IsChecked="{Binding IsComplex}"/>
    <ListView Margin="10" Name="lvUsers" Template="{StaticResource listTwo}" />
</Grid>

This is my trivial viewmodel, and the record class:

    public class MyRecord
{
    public MyRecord(string firstName, string food, int number, string state)
    {
        Name = firstName;
        Food = food;
        Number = number;
        State = state;
    }

    public string Name { get; set; }
    public string Food { get; set; }
    public int Number { get; set; }
    public string State { get; set; }
}

public class MainViewModel : ViewModelBase
{
    private List<MyRecord> _recordList;

    public MainViewModel()
    {
        _recordList = new List<MyRecord>();
        _recordList = new List<MyRecord>();
        _recordList.Add(new MyRecord("Lee", "pizza", 10, "ID"));
        _recordList.Add(new MyRecord("Gary", "burger", 20, "UT"));

        MyList = new ObservableCollection<MyRecord>(_recordList);
    }

    private ObservableCollection<MyRecord> _myList;
    public ObservableCollection<MyRecord> MyList
    {
        get { return _myList; }
        set
        {
            if (_myList != value)
            {
                _myList = value;
                OnPropertyChanged(() => MyList);
            }
        }
    }

    private bool _isComplex = true;
    public bool IsComplex
    {
        get { return _isComplex; }
        set
        {
            if (_isComplex != value)
            {
                _isComplex = value;
                OnPropertyChanged(() => IsComplex);
            }
        }
    }
}

The next-to-last line of the xaml has a hard-coded Template assignment:

        <ListView Margin="10" Name="lvUsers" Template="{StaticResource listTwo}" />

Changing that back and forth in the xaml results in the program displaying one ListView layout or the other without error.

I want to be able to set a property in the ViewModel that will control which layout is used - in this trivial case, I have a checkbox that should control the selected ListView.

I've tried triggers, which seems to be the simplest approach, but haven't found anything that makes the compiler happy.

Any suggestions would be appreciated!

Update:
Ed Plunkett's response showed me that I was making my question too hard. I didn't want to replace the whole ListView, just control what columns were displayed within it. Extracting a bit of his code results in exactly the behavior I wanted originally without going into the code-behind and replacing the entire ListView. The displayed columns in my sample now switch to the correct "view" when I toggle the checkbox. The code-behind is untouched, and the viewmodel remains the same. Thanks Ed! I've accepted his answer because it showed me the subset of code I needed, and I've changed the Title to reflect what the real question was.

This is the complete revised xaml:

<Window x:Class="AAWorkTest.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:ListTest"
    mc:Ignorable="d"
    Title="MainWindow" Height="350" Width="525">
<Window.DataContext>
    <local:MainViewModel />
</Window.DataContext>
<Grid>
    <CheckBox x:Name="checkBox" Content="Complex" HorizontalAlignment="Left"
              Margin="10,10,10,10" VerticalAlignment="Top"
              IsChecked="{Binding IsComplex}"/>
    <ListView ItemsSource="{Binding MyList}" Margin="10,30,10,30" Name="lvUsers">
        <ListView.Style>

            </Style>
        </ListView.Style>
    </ListView>
</Grid>

Body:
You don't set properties on a control by replacing the template with one that creates a new, nested instance of the control with different properties. In WPF, a ControlTemplate determines how a control is displayed, it doesn't create the control. Instead, you set properties with a style that sets the properties. If it were a good idea to change the ListView's template, this is how you would do that.

Here's how you can do this (I don't recommend naming it UserControl1, of course):

UserControl1.xaml.cs

public partial class UserControl1 : UserControl
{
    public UserControl1()
    {
        InitializeComponent();
    }

    public IEnumerable ItemsSource
    {
        get { return (IEnumerable)GetValue(ItemsSourceProperty); }
        set { SetValue(ItemsSourceProperty, value); }
    }

    public static readonly DependencyProperty ItemsSourceProperty =
        DependencyProperty.Register(nameof(ItemsSource), typeof(IEnumerable), typeof(UserControl1),
            new PropertyMetadata(null));

    public ViewPurpose ViewPurpose
    {
        get { return (ViewPurpose)GetValue(ViewPurposeProperty); }
        set { SetValue(ViewPurposeProperty, value); }
    }

    public static readonly DependencyProperty ViewPurposeProperty =
        DependencyProperty.Register(nameof(ViewPurpose), typeof(ViewPurpose), typeof(UserControl1),
            new PropertyMetadata(ViewPurpose.None));
}

public enum ViewPurpose
{
    None,
    FoodPreference,
    ContactInfo,
    FredBarneyWilma
}

UserControl1.xaml

<UserControl
    x:Class="WpfApp3.UserControl1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:local="clr-namespace:WpfApp3"
    mc:Ignorable="d"
    d:DesignHeight="300" d:DesignWidth="300">
    <Grid>
        <ListView
            ItemsSource="{Binding ItemsSource, RelativeSource={RelativeSource AncestorType=UserControl}}"
            >
            <ListView.Style>
                <Style TargetType="ListView">
                    <Style.Triggers>
                        <DataTrigger
                            Binding="{Binding ViewPurpose, RelativeSource={RelativeSource AncestorType=UserControl}}"
                            Value="FoodPreference"
                            >
                            <Setter Property="View">
                                <Setter.Value>
                                    <GridView>
                                        <GridViewColumn Header="Name" Width="120" DisplayMemberBinding="{Binding Name}" />
                                        <GridViewColumn Header="Food" Width="50" DisplayMemberBinding="{Binding Food}" />
                                    </GridView>
                                </Setter.Value>
                            </Setter>
                        </DataTrigger>
                        <DataTrigger
                            Binding="{Binding ViewPurpose, RelativeSource={RelativeSource AncestorType=UserControl}}"
                            Value="ContactInfo"
                            >
                            <Setter Property="View">
                                <Setter.Value>
                                    <GridView>
                                        <GridViewColumn Header="Name" Width="120" DisplayMemberBinding="{Binding Name}" />
                                        <GridViewColumn Header="Number" Width="120" DisplayMemberBinding="{Binding Number}" />
                                        <GridViewColumn Header="State" Width="50" DisplayMemberBinding="{Binding State}" />
                                    </GridView>
                                </Setter.Value>
                            </Setter>
                        </DataTrigger>
                    </Style.Triggers>
                </Style>
            </ListView.Style>
        </ListView>
    </Grid>
</UserControl>

Usage example:

<StackPanel Orientation="Vertical">
    <local:UserControl1
        ViewPurpose="FoodPreference"
        ItemsSource="{Binding SomeCollectionOfWhatever}"
        />
    <local:UserControl1
        ViewPurpose="ContactInfo"
        ItemsSource="{Binding DifferentCollectionOfWhatever}"
        />
</StackPanel>

The enum is one option for specifying a set of columns. You could also give it a collection of column name, or a single string delimited by some special character ("Name|Food|Gas|Lodging") that would be split, and then do something in the UserControl to create the collection of columns based on that.

But if you have two or three predefined collections of columns, with custom widths and so on, this is quick and simple and does the job. You don't need to get too clever with this one.


来源: stackexchange

问:
SQL Query to fetch details from multiple tables using Group By and Join
I always feel poor when it comes to SQL. I have two tables, say table revenue which has following structure:

```<code>Revenue Table
Clicks
impressions
ad_id
device_id
money_generated
date
</code>```

Another table which has devices details as follows:

```<code>Devices
device_id
device_name
</code>```

One more table say Ads..

```<code>Ads Table
ad_name
ad_id
----
</code>```

Now what I want is to fetch sum of impressions, clicks, money generated by a particular device for a particular ad between a particular date.

I tried this query of linking revenue & device tables but failed.

```<code>select
   device_name,
   revenue.impressions,
   revenue.clicks,
   revenue.money_generated,
   revenue.ad_id
FROM
   (select
       device_id,
       dim_ad_id,
       SUM(imp_count) as impressions,
       SUM(click_count) as clicks,
       SUM(money_generated) as revenue,
    from
       revenue
    GROUP BY
       ad_id
    GROUP BY
       device_id
    WHERE
       dim_Date>'2017-11-02 10:33:00'
       and dim_date<'2017-11-08 00:56:22') revenue
JOIN deviceTable as devices ON
devices.device_id=revenue.device_id
and devices.device_name IN ('ABC')
</code>```

Please ignore the slight mistake of column names as i have renamed them before pasting it here. Please help me understanding the SQL better

答:
You have two GROUP BY clauses in that derived table which will throw a syntax error.</p>

```<code>select
   device_name,
   revenue.impressions,
   revenue.clicks,
   revenue.money_generated,
   revenue.ad_id
FROM
   (select
       device_id,
       dim_ad_id,
       SUM(imp_count) as impressions,
       SUM(click_count) as clicks,
       SUM(money_generated) as revenue,
    from
       revenue
    GROUP BY
       ad_id, device_id        --fixed syntax here
    WHERE
       dim_Date>'2017-11-02 10:33:00'
       and dim_date<'2017-11-08 00:56:22') revenue
JOIN
    deviceTable as devices ON
    devices.device_id = revenue.device_id
    and devices.device_name IN ('ABC')
LEFT JOIN
    Ads on
    Ads.ad_id = revenue.dim_id --or what ever it should be
</code>```

ContentLicense: CC BY-SA 3.0

Body:
I always feel poor when it comes to SQL. I have two tables, say table revenue which has following structure:

Revenue Table
Clicks
impressions
ad_id
device_id
money_generated
date

Another table which has devices details as follows:

Devices
device_id
device_name

One more table say Ads..

Ads Table
ad_name
ad_id
----

Now what I want is to fetch sum of impressions, clicks, money generated by a particular device for a particular ad between a particular date.

I tried this query of linking revenue & device tables but failed.

select
   device_name,
   revenue.impressions,
   revenue.clicks,
   revenue.money_generated,
   revenue.ad_id
FROM
   (select
       device_id,
       dim_ad_id,
       SUM(imp_count) as impressions,
       SUM(click_count) as clicks,
       SUM(money_generated) as revenue,
    from
       revenue
    GROUP BY
       ad_id
    GROUP BY
       device_id
    WHERE
       dim_Date>'2017-11-02 10:33:00'
       and dim_date<'2017-11-08 00:56:22') revenue
JOIN deviceTable as devices ON
devices.device_id=revenue.device_id
and devices.device_name IN ('ABC')

Please ignore the slight mistake of column names as i have renamed them before pasting it here. Please help me understanding the SQL better

Body:
You have two GROUP BY clauses in that derived table which will throw a syntax error.

select
   device_name,
   revenue.impressions,
   revenue.clicks,
   revenue.money_generated,
   revenue.ad_id
FROM
   (select
       device_id,
       dim_ad_id,
       SUM(imp_count) as impressions,
       SUM(click_count) as clicks,
       SUM(money_generated) as revenue,
    from
       revenue
    GROUP BY
       ad_id, device_id        --fixed syntax here
    WHERE
       dim_Date>'2017-11-02 10:33:00'
       and dim_date<'2017-11-08 00:56:22') revenue
JOIN
    deviceTable as devices ON
    devices.device_id = revenue.device_id
    and devices.device_name IN ('ABC')
LEFT JOIN
    Ads on
    Ads.ad_id = revenue.dim_id --or what ever it should be


问:
Parameterized query issue
I have my own DLL (for the data access layer).  I use escaping characters technique to avoid the user input errors, but recently I decided to enhance my classes by using parameterized queries to prevent all possible errors. Will the modification be easy or difficult?

How to convert queries to use the parameterized queries?

Please show me some sample code to clarify the idea.

答:
This is how i would do it in c#.net and SQL server.</p>

```<code>  string sQuery = @"INSERT INTO [UserJob]
                                      (
                                        [SIJCJOBID],
                                        [SIJCCHDID],
                                        [UserID],
                                        [SageDatabaseID],
                                        [MaxLineValue],
                                        [MaxAuthorisationValue],
                                        [UpdatedDate],
                                        [UpdatedUser]
                                      )
                                      VALUES
                                      (
                                        @SIJCJOBID,
                                        @SIJCCHDID,
                                        @UserID,
                                        @SageDatabaseID,
                                        @MaxLineValue,
                                        @MaxAuthorisationValue,
                                        @UpdatedDate,
                                        @UpdatedUser
                                      )
                                      SELECT SCOPE_IDENTITY() AS 'ID'";

                    using (SqlCommand oSqlCommand = new SqlCommand(sQuery))
                    {
                        oSqlCommand.Parameters.AddWithValue("@SIJCJOBID", this.SIJCJOBID);
                        oSqlCommand.Parameters.AddWithValue("@SIJCCHDID", this.SIJCCHDID);
                        oSqlCommand.Parameters.AddWithValue("@UserID", this.UserID);
                        oSqlCommand.Parameters.AddWithValue("@SageDatabaseID", this.SageDatabaseID);
                        oSqlCommand.Parameters.AddWithValue("@MaxLineValue", this.MaxLineValue);
                        oSqlCommand.Parameters.AddWithValue("@MaxAuthorisationValue", this.MaxAuthorisationValue);
                        oSqlCommand.Parameters.AddWithValue("@UpdatedDate", DateTime.Now);
                        oSqlCommand.Parameters.AddWithValue("@UpdatedUser", StaticStore.CurrentUser != null ? StaticStore.CurrentUser.UserName : "SYSTEM");

                        using (DataTable dt = DataTier.ExecuteQuery(oSqlCommand))
                        {
                            if (dt.Rows.Count == 1)
                            {
                                int.TryParse(dt.Rows[0]["ID"].ToString(), out m_UserJobID);
                            }
                        }
                }
</code>```

Title: Parameterized query issue

Body:
This is how i would do it in c#.net and SQL server.

  string sQuery = @"INSERT INTO [UserJob]
                                      (
                                        [SIJCJOBID],
                                        [SIJCCHDID],
                                        [UserID],
                                        [SageDatabaseID],
                                        [MaxLineValue],
                                        [MaxAuthorisationValue],
                                        [UpdatedDate],
                                        [UpdatedUser]
                                      )
                                      VALUES
                                      (
                                        @SIJCJOBID,
                                        @SIJCCHDID,
                                        @UserID,
                                        @SageDatabaseID,
                                        @MaxLineValue,
                                        @MaxAuthorisationValue,
                                        @UpdatedDate,
                                        @UpdatedUser
                                      )
                                      SELECT SCOPE_IDENTITY() AS 'ID'";

                    using (SqlCommand oSqlCommand = new SqlCommand(sQuery))
                    {
                        oSqlCommand.Parameters.AddWithValue("@SIJCJOBID", this.SIJCJOBID);
                        oSqlCommand.Parameters.AddWithValue("@SIJCCHDID", this.SIJCCHDID);
                        oSqlCommand.Parameters.AddWithValue("@UserID", this.UserID);
                        oSqlCommand.Parameters.AddWithValue("@SageDatabaseID", this.SageDatabaseID);
                        oSqlCommand.Parameters.AddWithValue("@MaxLineValue", this.MaxLineValue);
                        oSqlCommand.Parameters.AddWithValue("@MaxAuthorisationValue", this.MaxAuthorisationValue);
                        oSqlCommand.Parameters.AddWithValue("@UpdatedDate", DateTime.Now);
                        oSqlCommand.Parameters.AddWithValue("@UpdatedUser", StaticStore.CurrentUser != null ? StaticStore.CurrentUser.UserName : "SYSTEM");

                        using (DataTable dt = DataTier.ExecuteQuery(oSqlCommand))
                        {
                            if (dt.Rows.Count == 1)
                            {
                                int.TryParse(dt.Rows[0]["ID"].ToString(), out m_UserJobID);
                            }
                        }
                }

ContentLicense: CC BY-SA 2.5


问:
Julia runtime error when using PyPlot
If I try to plot anything with Julia 0.6.0 (using Plots or PyPlot) on Windows 8.1 I get a runtime error message:

Runtime error:

Program; C:\Users\c\AppData\Local\Julia-0.6.0\bin\julia.exe R6034 An
  application has made an attempt to load the C runtime library
  incorrectly. Please contact the application's support team for more
  information.

and julia exits. Reinstalling julia and the plotting packages did not help. Any ideas how to fix this?

答:
For me the answer was to first make sure I had a Python install that had matplotlib installed. My system, for whatever reason, has multiple installs of multiple versions of Python. Run python.exe and try importing matplotlib.

```<code>import matplotlib
</code>```

If none of them can successfully import matplotlib then install a version that comes with it installed or install it from source (see the matplotlib website for instructions).

When you find one that doesn't return an error make sure Julia knows where it is. The quickest way is directly through Julia (ENV["PYTHON"] = "C:\\PythonDirectory\\python.exe", ENV["PYTHON"] = "" will default to the version of Python installed by PyPlot). Setting it in Julia will not modify the Windows environmental variables and will be the first place PyCall looks.

Alternatively, you can add it to the environment variables, either user or system level environmental variables should do.

PYTHON is the variable name

C:\PythonDirectory\python.exe is the value

Restart your computer so the environmental variables are used.

Open Julia and make sure the Python environmental variable is recognized.

```<code>ENV["PYTHON"] # Should return the directory you set above
</code>```

Try setting it yourself if it's still not correct.

```<code>ENV["PYTHON"] = "C:\\PythonDirectory\\python.exe"
</code>```

Rebuild PyCall in Julia (rebuilding PyPlot should work as well).

```<code>Pkg.build("PyCall")
</code>```

That should do it.

My setup is as follows.

Windows 10 with all updates until the beginning of November 2017

Julia 0.6.0 64bit

Python 2, Anaconda 2, Anaconda 3, and miniconda (via PyPlot standard install) all installed with no environmental variables

PyPlot was working the last time I used it a few weeks ago but I suspect the big fall update from Windows screwed up something.

Title: Julia runtime error when using PyPlot

Body:
For me the answer was to first make sure I had a Python install that had matplotlib installed. My system, for whatever reason, has multiple installs of multiple versions of Python. Run python.exe and try importing matplotlib.

import matplotlib

If none of them can successfully import matplotlib then install a version that comes with it installed or install it from source (see the matplotlib website for instructions).

When you find one that doesn't return an error make sure Julia knows where it is. The quickest way is directly through Julia (ENV["PYTHON"] = "C:\\PythonDirectory\\python.exe", ENV["PYTHON"] = "" will default to the version of Python installed by PyPlot). Setting it in Julia will not modify the Windows environmental variables and will be the first place PyCall looks.

Alternatively, you can add it to the environment variables, either user or system level environmental variables should do.

PYTHON is the variable name

C:\PythonDirectory\python.exe is the value

Restart your computer so the environmental variables are used.

Open Julia and make sure the Python environmental variable is recognized.

ENV["PYTHON"] # Should return the directory you set above

Try setting it yourself if it's still not correct.

ENV["PYTHON"] = "C:\\PythonDirectory\\python.exe"

Rebuild PyCall in Julia (rebuilding PyPlot should work as well).

Pkg.build("PyCall")

That should do it.

My setup is as follows.

Windows 10 with all updates until the beginning of November 2017

Julia 0.6.0 64bit

Python 2, Anaconda 2, Anaconda 3, and miniconda (via PyPlot standard install) all installed with no environmental variables

PyPlot was working the last time I used it a few weeks ago but I suspect the big fall update from Windows screwed up something.


问:
how to implement a linked list properly using java
I am working in a project with linked list (JAVA) but I am not being able to figure out how to insert the person. How could I get the switch to work? Or should I use something else? I also thought about creating a class outside the main method and just call it but it did not work as well. Any help will be much appreciate</p>

```<code>public void listOfPeople() { // Beginning of the method listOfPeople where shows the employees

        //  *** instance the person object ad loading your variables
        Personqueue p1 = new Personqueue(); //saving the employee in a variable???
        p1.setFname("John");
        p1.setLname("Smith");
        p1.setDOA(15);
        p1.setPassportN(306589);
        p1.setNumber(1);
        vectorObj.add(p1); // add the employee in a vector

        Personqueue p2 = new Personqueue(); //saving the employee in a variable???
        p1.setFname("Paul");
        p1.setLname("Clooney");
        p1.setDOA(5);
        p1.setPassportN(30614584);
        p1.setNumber(2);
        vectorObj.add(p2); // add the employee in a vector

    }

    public void ascendingOrder(Vector<Personqueue> vector) { // bubble sort method to order the vector

        int j;
        boolean flag = true; // set flag to true to begin first pass
        Personqueue temp; // holding the variable temporarily

        while (flag) {

            flag = false; // set flag to false awaiting a possible swap
            for (j = 0; j < vector.size() - 1; j++) {

                if (vector.get(j).getNumber() > vector.get(j + 1).getNumber()) {

                    temp = vector.get(j); // swap elements
                    vector.set(j, vector.get(j + 1));
                    vector.set(j + 1, temp);
                    flag = true; // shows a swap occurred

                }

            }

        }

    }

    public void newPerson(int positionPerson) { // beginning of newPerson method

        String Option = null; // declaration of local variables that are used only in this method and don't use too much space
        Personqueue p = new Personqueue(); //instead of setting it to null, here we are calling
                                                  // a constructor which was declared in Personqueue class.

//      switch (positionPerson) {
//      case 1: // insert a person at the start of the queue
//      //  p = new QueueStart();
//          break; // executes in order to end the switch in case one of the options is valid
//      case 2: // insert a person at a chosen point in the queue
//      //  p = new ChoosePosition();
//      case 3: // insert a person at the end of the queue
//          //  p = new EndQueue();
//          break;
//      default:
//          System.out.println("Invalid Option!!!"); // in case the option is not one of the cases above, print this...
//          return; // return to the do/while loop in Principal method
//      }

        BufferedReader br = new BufferedReader(new InputStreamReader(System.in));

        System.out.println("Name:"); // user input
        try {
            Option = br.readLine();
            String name = Option;

            p.setFname(name);

        } catch (IOException e) {
            System.out.println(e);

        }

        System.out.println("Surname:"); // user input
        try {
            Option = br.readLine();
            String lname = Option;

            p.setLname(lname);

        } catch (IOException e) {
            System.out.println(e);

        }

        System.out.println("Date Of Arrival: "); // user input
        try {
            Option = br.readLine();
            int doa = Integer.parseInt(Option); // use parseInt in order to convert Integer to String to be read by BufferedReader.

            p.setDOA(doa);

        } catch (IOException e) {
            System.out.println(e);

        }

        System.out.println("Passport Number:"); // user input
        try {
            Option = br.readLine();
            int pn = Integer.parseInt(Option);

            p.setPassportN(pn);

        } catch (IOException e) {
            System.out.println(e);

        }

        System.out.println("Number:"); // user input
        try {
            Option = br.readLine();
            int no = Integer.parseInt(Option);

            p.setNumber(no);

        } catch (IOException e) {
            System.out.println(e);

        }

        vectorObj.addElement(p); // save all the data in the vector

        System.out.print("Saving Person :" + p.getFname()); // print to the user the name's been saved

        try {
            for (int i = 0; i < 6; i++) {
                System.out.print(".");
                Thread.sleep(300); // suspend the "." execution for a specified period.

            }

        } catch (InterruptedException e) { //  exception p o method thread //  catch for thread above

            e.printStackTrace();
        }

        System.out.println();
        System.out.println("Person: " + p.getFname() + " is saved!!!");

        try {
            Thread.sleep(2000);
        } catch (InterruptedException e) {

            e.printStackTrace();
        }

    } // end of the Method newPerson
</code>```

答:
Check your variables in listOfPeople(...), p2 was not used correctly.

Take a look at Example of LinkedList in Java

Body:
I am working in a project with linked list (JAVA) but I am not being able to figure out how to insert the person. How could I get the switch to work? Or should I use something else? I also thought about creating a class outside the main method and just call it but it did not work as well. Any help will be much appreciate

public void listOfPeople() { // Beginning of the method listOfPeople where shows the employees

        //  *** instance the person object ad loading your variables
        Personqueue p1 = new Personqueue(); //saving the employee in a variable???
        p1.setFname("John");
        p1.setLname("Smith");
        p1.setDOA(15);
        p1.setPassportN(306589);
        p1.setNumber(1);
        vectorObj.add(p1); // add the employee in a vector

        Personqueue p2 = new Personqueue(); //saving the employee in a variable???
        p1.setFname("Paul");
        p1.setLname("Clooney");
        p1.setDOA(5);
        p1.setPassportN(30614584);
        p1.setNumber(2);
        vectorObj.add(p2); // add the employee in a vector

    }

    public void ascendingOrder(Vector<Personqueue> vector) { // bubble sort method to order the vector

        int j;
        boolean flag = true; // set flag to true to begin first pass
        Personqueue temp; // holding the variable temporarily

        while (flag) {

            flag = false; // set flag to false awaiting a possible swap
            for (j = 0; j < vector.size() - 1; j++) {

                if (vector.get(j).getNumber() > vector.get(j + 1).getNumber()) {

                    temp = vector.get(j); // swap elements
                    vector.set(j, vector.get(j + 1));
                    vector.set(j + 1, temp);
                    flag = true; // shows a swap occurred

                }

            }

        }

    }

    public void newPerson(int positionPerson) { // beginning of newPerson method

        String Option = null; // declaration of local variables that are used only in this method and don't use too much space
        Personqueue p = new Personqueue(); //instead of setting it to null, here we are calling
                                                  // a constructor which was declared in Personqueue class.

//      switch (positionPerson) {
//      case 1: // insert a person at the start of the queue
//      //  p = new QueueStart();
//          break; // executes in order to end the switch in case one of the options is valid
//      case 2: // insert a person at a chosen point in the queue
//      //  p = new ChoosePosition();
//      case 3: // insert a person at the end of the queue
//          //  p = new EndQueue();
//          break;
//      default:
//          System.out.println("Invalid Option!!!"); // in case the option is not one of the cases above, print this...
//          return; // return to the do/while loop in Principal method
//      }

        BufferedReader br = new BufferedReader(new InputStreamReader(System.in));

        System.out.println("Name:"); // user input
        try {
            Option = br.readLine();
            String name = Option;

            p.setFname(name);

        } catch (IOException e) {
            System.out.println(e);

        }

        System.out.println("Surname:"); // user input
        try {
            Option = br.readLine();
            String lname = Option;

            p.setLname(lname);

        } catch (IOException e) {
            System.out.println(e);

        }

        System.out.println("Date Of Arrival: "); // user input
        try {
            Option = br.readLine();
            int doa = Integer.parseInt(Option); // use parseInt in order to convert Integer to String to be read by BufferedReader.

            p.setDOA(doa);

        } catch (IOException e) {
            System.out.println(e);

        }

        System.out.println("Passport Number:"); // user input
        try {
            Option = br.readLine();
            int pn = Integer.parseInt(Option);

            p.setPassportN(pn);

        } catch (IOException e) {
            System.out.println(e);

        }

        System.out.println("Number:"); // user input
        try {
            Option = br.readLine();
            int no = Integer.parseInt(Option);

            p.setNumber(no);

        } catch (IOException e) {
            System.out.println(e);

        }

        vectorObj.addElement(p); // save all the data in the vector

        System.out.print("Saving Person :" + p.getFname()); // print to the user the name's been saved

        try {
            for (int i = 0; i < 6; i++) {
                System.out.print(".");
                Thread.sleep(300); // suspend the "." execution for a specified period.

            }

        } catch (InterruptedException e) { //  exception p o method thread //  catch for thread above

            e.printStackTrace();
        }

        System.out.println();
        System.out.println("Person: " + p.getFname() + " is saved!!!");

        try {
            Thread.sleep(2000);
        } catch (InterruptedException e) {

            e.printStackTrace();
        }

    } // end of the Method newPerson


问:
Get clicked chess piece from an SVG chessboard
I am developing a chess GUI in Python 3.6.3 using PyQt5 5.9.1 (GUI framework) and python-chess 0.21.1 (chess library) on Windows 10. I want to get the value of a piece that was clicked on an SVG chessboard (provided by python-chess) so that I can then move that piece to another square.

After the first left mouse click and getting the piece, I want to get the second left mouse click from the user and get the square that the user clicked on. Then my chess GUI must move the piece from originating square to the target square.

So, here's my complete working code so far. Any hints or actual code additions are very welcome.

```<code>import chess
import chess.svg
from PyQt5.QtSvg import QSvgWidget
from PyQt5.QtCore import pyqtSlot, Qt
from PyQt5.QtWidgets import QApplication, QWidget

class MainWindow(QWidget):
    def __init__(self):
        super().__init__()

        self.setWindowTitle("Chess Titan")
        self.setGeometry(300, 300, 800, 800)

        self.widgetSvg = QSvgWidget(parent=self)
        self.widgetSvg.setGeometry(10, 10, 600, 600)

        self.chessboard = chess.Board()

    @pyqtSlot(QWidget)
    def mousePressEvent(self, event):
        if event.buttons() == Qt.LeftButton:
            ## How to get the clicked SVG chess piece?

            # Envoke the paint event.
            self.update()

    @pyqtSlot(QWidget)
    def paintEvent(self, event):
        self.chessboardSvg = chess.svg.board(self.chessboard).encode("UTF-8")
        self.widgetSvg.load(self.chessboardSvg)

if __name__ == "__main__":
    chessTitan = QApplication([])
    window = MainWindow()
    window.show()
    chessTitan.exec()
</code>```

答:
Below is the Python, PyQt5 and python-chess code for a fully functional chess GUI that has legal move detection built in, so chess piece movement behaves according to the rules of chess.

```<code>#! /usr/bin/env python

"""
This module is the execution point of the chess GUI application.
"""

import sys

import chess

from PyQt5.QtCore import pyqtSlot, Qt
from PyQt5.QtSvg import QSvgWidget
from PyQt5.QtWidgets import QApplication, QWidget

class MainWindow(QWidget):
    """
    Create a surface for the chessboard.
    """
    def __init__(self):
        """
        Initialize the chessboard.
        """
        super().__init__()

        self.setWindowTitle("Chess GUI")
        self.setGeometry(300, 300, 800, 800)

        self.widgetSvg = QSvgWidget(parent=self)
        self.widgetSvg.setGeometry(10, 10, 600, 600)

        self.boardSize = min(self.widgetSvg.width(),
                             self.widgetSvg.height())
        self.coordinates = True
        self.margin = 0.05 * self.boardSize if self.coordinates else 0
        self.squareSize = (self.boardSize - 2 * self.margin) / 8.0
        self.pieceToMove = [None, None]

        self.board = chess.Board()
        self.drawBoard()

    @pyqtSlot(QWidget)
    def mousePressEvent(self, event):
        """
        Handle left mouse clicks and enable moving chess pieces by
        clicking on a chess piece and then the target square.

        Moves must be made according to the rules of chess because
        illegal moves are suppressed.
        """
        if event.x() <= self.boardSize and event.y() <= self.boardSize:
            if event.buttons() == Qt.LeftButton:
                if self.margin < event.x() < self.boardSize - self.margin and self.margin < event.y() < self.boardSize - self.margin:
                    file = int((event.x() - self.margin) / self.squareSize)
                    rank = 7 - int((event.y() - self.margin) / self.squareSize)
                    square = chess.square(file, rank)
                    piece = self.board.piece_at(square)
                    coordinates = "{}{}".format(chr(file + 97), str(rank + 1))
                    if self.pieceToMove[0] is not None:
                        move = chess.Move.from_uci("{}{}".format(self.pieceToMove[1], coordinates))
                        if move in self.board.legal_moves:
                            self.board.push(move)
                        piece = None
                        coordinates = None
                    self.pieceToMove = [piece, coordinates]
                    self.drawBoard()

    def drawBoard(self):
        """
        Draw a chessboard with the starting position and then redraw
        it for every new move.
        """
        self.boardSvg = self.board._repr_svg_().encode("UTF-8")
        self.drawBoardSvg = self.widgetSvg.load(self.boardSvg)

        return self.drawBoardSvg

if __name__ == "__main__":
    chessGui = QApplication(sys.argv)
    window = MainWindow()
    window.show()
    sys.exit(chessGui.exec_())
</code>```

ContentLicense: CC BY-SA 4.0

Body:
I am developing a chess GUI in Python 3.6.3 using PyQt5 5.9.1 (GUI framework) and python-chess 0.21.1 (chess library) on Windows 10. I want to get the value of a piece that was clicked on an SVG chessboard (provided by python-chess) so that I can then move that piece to another square.

After the first left mouse click and getting the piece, I want to get the second left mouse click from the user and get the square that the user clicked on. Then my chess GUI must move the piece from originating square to the target square.

So, here's my complete working code so far. Any hints or actual code additions are very welcome.

import chess
import chess.svg
from PyQt5.QtSvg import QSvgWidget
from PyQt5.QtCore import pyqtSlot, Qt
from PyQt5.QtWidgets import QApplication, QWidget

class MainWindow(QWidget):
    def __init__(self):
        super().__init__()

        self.setWindowTitle("Chess Titan")
        self.setGeometry(300, 300, 800, 800)

        self.widgetSvg = QSvgWidget(parent=self)
        self.widgetSvg.setGeometry(10, 10, 600, 600)

        self.chessboard = chess.Board()

    @pyqtSlot(QWidget)
    def mousePressEvent(self, event):
        if event.buttons() == Qt.LeftButton:
            ## How to get the clicked SVG chess piece?

            # Envoke the paint event.
            self.update()

    @pyqtSlot(QWidget)
    def paintEvent(self, event):
        self.chessboardSvg = chess.svg.board(self.chessboard).encode("UTF-8")
        self.widgetSvg.load(self.chessboardSvg)

if __name__ == "__main__":
    chessTitan = QApplication([])
    window = MainWindow()
    window.show()
    chessTitan.exec()

Body:
Below is the Python, PyQt5 and python-chess code for a fully functional chess GUI that has legal move detection built in, so chess piece movement behaves according to the rules of chess.

#! /usr/bin/env python

"""
This module is the execution point of the chess GUI application.
"""

import sys

import chess

from PyQt5.QtCore import pyqtSlot, Qt
from PyQt5.QtSvg import QSvgWidget
from PyQt5.QtWidgets import QApplication, QWidget

class MainWindow(QWidget):
    """
    Create a surface for the chessboard.
    """
    def __init__(self):
        """
        Initialize the chessboard.
        """
        super().__init__()

        self.setWindowTitle("Chess GUI")
        self.setGeometry(300, 300, 800, 800)

        self.widgetSvg = QSvgWidget(parent=self)
        self.widgetSvg.setGeometry(10, 10, 600, 600)

        self.boardSize = min(self.widgetSvg.width(),
                             self.widgetSvg.height())
        self.coordinates = True
        self.margin = 0.05 * self.boardSize if self.coordinates else 0
        self.squareSize = (self.boardSize - 2 * self.margin) / 8.0
        self.pieceToMove = [None, None]

        self.board = chess.Board()
        self.drawBoard()

    @pyqtSlot(QWidget)
    def mousePressEvent(self, event):
        """
        Handle left mouse clicks and enable moving chess pieces by
        clicking on a chess piece and then the target square.

        Moves must be made according to the rules of chess because
        illegal moves are suppressed.
        """
        if event.x() <= self.boardSize and event.y() <= self.boardSize:
            if event.buttons() == Qt.LeftButton:
                if self.margin < event.x() < self.boardSize - self.margin and self.margin < event.y() < self.boardSize - self.margin:
                    file = int((event.x() - self.margin) / self.squareSize)
                    rank = 7 - int((event.y() - self.margin) / self.squareSize)
                    square = chess.square(file, rank)
                    piece = self.board.piece_at(square)
                    coordinates = "{}{}".format(chr(file + 97), str(rank + 1))
                    if self.pieceToMove[0] is not None:
                        move = chess.Move.from_uci("{}{}".format(self.pieceToMove[1], coordinates))
                        if move in self.board.legal_moves:
                            self.board.push(move)
                        piece = None
                        coordinates = None
                    self.pieceToMove = [piece, coordinates]
                    self.drawBoard()

    def drawBoard(self):
        """
        Draw a chessboard with the starting position and then redraw
        it for every new move.
        """
        self.boardSvg = self.board._repr_svg_().encode("UTF-8")
        self.drawBoardSvg = self.widgetSvg.load(self.boardSvg)

        return self.drawBoardSvg

if __name__ == "__main__":
    chessGui = QApplication(sys.argv)
    window = MainWindow()
    window.show()
    sys.exit(chessGui.exec_())


问:
selecting not None value from a dataframe column
I would like to use the fillna function to fill None value of a column with its own first most frequent value that is not None or nan.

Input DF:

```<code>Col_A
a
None
None
c
c
d
d
</code>```

The output Dataframe could be either:

```<code>Col_A
a
c
c
c
c
d
d
</code>```

Any suggestion would be very appreciated.
Many Thanks, Best Regards,
Carlo

答:
Prelude: If your None is actually a string, you can simplify any headaches by getting rid of them first-up. Use replace:

```<code>df = df.replace('None', np.nan)
</code>```

I believe you could use fillna + value_counts:

```<code>df

  Col_A
0     a
1   NaN
2   NaN
3     c
4     c
5     d
6     d

df.fillna(df.Col_A.value_counts(sort=False).index[0])

  Col_A
0     a
1     c
2     c
3     c
4     c
5     d
6     d
</code>```

Or, with Vaishali's suggestion, use idxmax to pick c:

```<code>df.fillna(df.Col_A.value_counts(sort=False).idxmax())

  Col_A
0     a
1     c
2     c
3     c
4     c
5     d
6     d
</code>```

The fill-values could either be c or d, depending on whether you include sort=False or not.

Details

```<code>df.Col_A.value_counts(sort=False)

c    2
a    1
d    2
Name: Col_A, dtype: int64
</code>```

Body:
I would like to use the fillna function to fill None value of a column with its own first most frequent value that is not None or nan.

Input DF:

Col_A
a
None
None
c
c
d
d

The output Dataframe could be either:

Col_A
a
c
c
c
c
d
d

Any suggestion would be very appreciated.
Many Thanks, Best Regards,
Carlo

Body:
Prelude: If your None is actually a string, you can simplify any headaches by getting rid of them first-up. Use replace:

df = df.replace('None', np.nan)

I believe you could use fillna + value_counts:

df

  Col_A
0     a
1   NaN
2   NaN
3     c
4     c
5     d
6     d

df.fillna(df.Col_A.value_counts(sort=False).index[0])

  Col_A
0     a
1     c
2     c
3     c
4     c
5     d
6     d

Or, with Vaishali's suggestion, use idxmax to pick c:

df.fillna(df.Col_A.value_counts(sort=False).idxmax())

  Col_A
0     a
1     c
2     c
3     c
4     c
5     d
6     d

The fill-values could either be c or d, depending on whether you include sort=False or not.

Details

df.Col_A.value_counts(sort=False)

c    2
a    1
d    2
Name: Col_A, dtype: int64


问:
Get database statistics from EntityFramework Core after calling DbContext.SaveChanges
I'd like to write some automated tests to measure how much database IO is occurring during particular processes in our application and ensure they never go above a certain threshold.

Outside of EF, you would:

Open SQL Server Management Studio

set io statistics on

...run your queries

... look at the response returned in the Messages window see the statistics.

So I'm wondering if there is some way to get these same measurements as part of the call to DbContext.SaveChanges. I think you could override the SaveChangesAsync and SaveChanges methods to execute the "set io statistics" statement, but doing it that way would result in an extra database hit and also I wouldn't know how to get the statistics back after SaveChangesAsync was complete.

Any thoughts or ideas?

答:
The statistics messges come back as Info Messages, which you can aggregate.  Eg in EF Core, something like:

```<code>            db.Database.OpenConnection();
            var con = db.Database.GetDbConnection() as SqlConnection;
            db.Database.ExecuteSqlCommand("set statistics io on");
            con.InfoMessage += (s, a) =>
            {
                if (a.Errors[0].Number == 3615)
                {
                    Console.WriteLine(a.Message);
                }
            };
</code>```

A better approach is probably to monitor on the server.  You can use an XEvents session to capture the logout event, which carries summary statistics for each session that ends ( or is reused from the connection pool).  EG

```<code>CREATE EVENT SESSION [Session Stats] ON SERVER
ADD EVENT sqlserver.logout(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.username))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
</code>```

Body:
The statistics messges come back as Info Messages, which you can aggregate.  Eg in EF Core, something like:

            db.Database.OpenConnection();
            var con = db.Database.GetDbConnection() as SqlConnection;
            db.Database.ExecuteSqlCommand("set statistics io on");
            con.InfoMessage += (s, a) =>
            {
                if (a.Errors[0].Number == 3615)
                {
                    Console.WriteLine(a.Message);
                }
            };

A better approach is probably to monitor on the server.  You can use an XEvents session to capture the logout event, which carries summary statistics for each session that ends ( or is reused from the connection pool).  EG

CREATE EVENT SESSION [Session Stats] ON SERVER
ADD EVENT sqlserver.logout(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.username))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO


问:
How to output the numbers 0 to 350 always with three digits?
How can I carry out this intended task in batch file?

The resulting echo of %%h should be always a three digit number.

```<code>@ECHO OFF
FOR /l %%h in (000,001,350) do (
    @ECHO Value is %%h
)
</code>```

答:
There are four solutions which can be all seen in batch code below:

```<code>@echo off
set "Number=0"
:NumberIncrement
set "Value=00%Number%"
echo Value is %Value:~-3%
set /A Number+=1
if not %Number% == 351 goto NumberIncrement

echo ===============================================================================

rem Above solution optimized by counting from 1000 to 1350.
set "Value=1000"
:ValueIncrement
echo Value is %Value:~-3%
set /A Value+=1
if not %Value% == 1351 goto ValueIncrement

echo ===============================================================================

rem Delayed environment variable expansion is needed for loop solutions.
setlocal EnableDelayedExpansion

for /L %%I in (0,1,350) do (
    set "Value=00%%I"
    echo Value is !Value:~-3!
)

echo ===============================================================================

rem Above solution a bit optimized by counting from 1000 to 1350 and
rem using operator & to specify two commands on a single command line.

for /L %%I in (1000,1,1350) do set "Value=%%I" & echo Value is !Value:~-3!

endlocal
</code>```

The environment variable Value is defined with two leading zeros and appended as string the current number (solution 1 and 3). Alternatively the value counting is done from 1000 to 1350 (solution 2 and 4). Only the last 3 characters are output from string value of environment variable Value resulting in output of 000 to 350.

For understanding the used commands and how they work, open a command prompt window, execute there the following commands, and read entirely all help pages displayed for each command very carefully.

echo /?

endlocal /?

for /?

goto /?

if /?

rem /?

set /?

setlocal /?

Body:
How can I carry out this intended task in batch file?

The resulting echo of %%h should be always a three digit number.

@ECHO OFF
FOR /l %%h in (000,001,350) do (
    @ECHO Value is %%h
)

Body:
There are four solutions which can be all seen in batch code below:

@echo off
set "Number=0"
:NumberIncrement
set "Value=00%Number%"
echo Value is %Value:~-3%
set /A Number+=1
if not %Number% == 351 goto NumberIncrement

echo ===============================================================================

rem Above solution optimized by counting from 1000 to 1350.
set "Value=1000"
:ValueIncrement
echo Value is %Value:~-3%
set /A Value+=1
if not %Value% == 1351 goto ValueIncrement

echo ===============================================================================

rem Delayed environment variable expansion is needed for loop solutions.
setlocal EnableDelayedExpansion

for /L %%I in (0,1,350) do (
    set "Value=00%%I"
    echo Value is !Value:~-3!
)

echo ===============================================================================

rem Above solution a bit optimized by counting from 1000 to 1350 and
rem using operator & to specify two commands on a single command line.

for /L %%I in (1000,1,1350) do set "Value=%%I" & echo Value is !Value:~-3!

endlocal

The environment variable Value is defined with two leading zeros and appended as string the current number (solution 1 and 3). Alternatively the value counting is done from 1000 to 1350 (solution 2 and 4). Only the last 3 characters are output from string value of environment variable Value resulting in output of 000 to 350.

For understanding the used commands and how they work, open a command prompt window, execute there the following commands, and read entirely all help pages displayed for each command very carefully.

echo /?

endlocal /?

for /?

goto /?

if /?

rem /?

set /?

setlocal /?


问:
SQL Server XML output with CDATA into xml variable
Following this question, I need to put the select result into a xml variable. How to do this avoiding the error message "The FOR XML and FOR JSON clauses are invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table or common table expression or view and apply FOR XML or FOR JSON on top of it.", please?

Here the test code:

```<code>declare @agent table
(
    AgentID int,
    Fname varchar(5),
    SSN varchar(11)
)

insert into @agent
select 1, 'Vimal', '123-23-4521' union all
select 2, 'Jacob', '321-52-4562' union all
select 3, 'Tom', '252-52-4563'

SELECT
    1 AS Tag,
    NULL AS Parent,
    NULL AS 'Agents!1!',
    NULL AS 'Agent!2!AgentID',
    NULL AS 'Agent!2!Fname!Element',
    NULL AS 'Agent!2!SSN!cdata'
UNION ALL
SELECT
    2 AS Tag,
    1 AS Parent,
    NULL,
    AgentID,
    Fname,
    SSN
FROM @agent
FOR XML EXPLICIT
</code>```

And here an example of what I want to do:

```<code>Declare @xml xml
...
set @xml= (SELECT
    1 AS Tag,
    NULL AS Parent,
    NULL AS 'Agents!1!',
    NULL AS 'Agent!2!AgentID',
    NULL AS 'Agent!2!Fname!Element',
    NULL AS 'Agent!2!SSN!cdata'
UNION ALL
SELECT
    2 AS Tag,
    1 AS Parent,
    NULL,
    AgentID,
    Fname,
    SSN
FROM @agent
FOR XML EXPLICIT)
</code>```

答:
This is tricky...

You can move the UNION ALL part to a CTE like here. The problem is not the FOR XML but rahter the UNION (be carefull, it might be necessary to add an ORDER BY clause):

```<code>DECLARE @xml XML;

WITH UnionAllCte AS
(
    SELECT
        1 AS Tag,
        NULL AS Parent,
        NULL AS 'Agents!1!',
        NULL AS 'Agent!2!AgentID',
        NULL AS 'Agent!2!Fname!Element',
        NULL AS 'Agent!2!SSN!cdata'
    UNION ALL
    SELECT
        2 AS Tag,
        1 AS Parent,
        NULL,
        AgentID,
        Fname,
        SSN
    FROM @agent
)
SELECT @xml=
(
    SELECT * FROM UnionAllCte
    FOR XML EXPLICIT
)

SELECT @xml;
</code>```

And you should be aware, that CDATA as well as FOR XML EXPLICIT are outdated. Rather use FOR XML PATH() and for reading the appropriate methods the XML data type provides.

You might read this and read the following links too!

Body:
Following this question, I need to put the select result into a xml variable. How to do this avoiding the error message "The FOR XML and FOR JSON clauses are invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table or common table expression or view and apply FOR XML or FOR JSON on top of it.", please?

Here the test code:

declare @agent table
(
    AgentID int,
    Fname varchar(5),
    SSN varchar(11)
)

insert into @agent
select 1, 'Vimal', '123-23-4521' union all
select 2, 'Jacob', '321-52-4562' union all
select 3, 'Tom', '252-52-4563'

SELECT
    1 AS Tag,
    NULL AS Parent,
    NULL AS 'Agents!1!',
    NULL AS 'Agent!2!AgentID',
    NULL AS 'Agent!2!Fname!Element',
    NULL AS 'Agent!2!SSN!cdata'
UNION ALL
SELECT
    2 AS Tag,
    1 AS Parent,
    NULL,
    AgentID,
    Fname,
    SSN
FROM @agent
FOR XML EXPLICIT

And here an example of what I want to do:

Declare @xml xml
...
set @xml= (SELECT
    1 AS Tag,
    NULL AS Parent,
    NULL AS 'Agents!1!',
    NULL AS 'Agent!2!AgentID',
    NULL AS 'Agent!2!Fname!Element',
    NULL AS 'Agent!2!SSN!cdata'
UNION ALL
SELECT
    2 AS Tag,
    1 AS Parent,
    NULL,
    AgentID,
    Fname,
    SSN
FROM @agent
FOR XML EXPLICIT)

Body:
This is tricky...

You can move the UNION ALL part to a CTE like here. The problem is not the FOR XML but rahter the UNION (be carefull, it might be necessary to add an ORDER BY clause):

DECLARE @xml XML;

WITH UnionAllCte AS
(
    SELECT
        1 AS Tag,
        NULL AS Parent,
        NULL AS 'Agents!1!',
        NULL AS 'Agent!2!AgentID',
        NULL AS 'Agent!2!Fname!Element',
        NULL AS 'Agent!2!SSN!cdata'
    UNION ALL
    SELECT
        2 AS Tag,
        1 AS Parent,
        NULL,
        AgentID,
        Fname,
        SSN
    FROM @agent
)
SELECT @xml=
(
    SELECT * FROM UnionAllCte
    FOR XML EXPLICIT
)

SELECT @xml;

And you should be aware, that CDATA as well as FOR XML EXPLICIT are outdated. Rather use FOR XML PATH() and for reading the appropriate methods the XML data type provides.

You might read this and read the following links too!


问:
Is there a better conditional setTimeout pattern?
I have the following code:

```<code>if (isFlyoutAnimationDisabled) {
  flyOut();
} else {
  setTimeout(flyOut, 250);
}
</code>```

It seems like there should be a better pattern than calling flyOut in each if  condition.  Seems wasteful to me and pollutes the code.

Is there a better way?

答:
Use the ternary operator, for example:</p>

```<code>isFlyoutAnimationDisabled ? flyOut() : setTimeout(flyOut, 250);
</code>```

Body:
I have the following code:

if (isFlyoutAnimationDisabled) {
  flyOut();
} else {
  setTimeout(flyOut, 250);
}

It seems like there should be a better pattern than calling flyOut in each if  condition.  Seems wasteful to me and pollutes the code.

Is there a better way?

Body:
Use the ternary operator, for example:

isFlyoutAnimationDisabled ? flyOut() : setTimeout(flyOut, 250);


问:
External Translations in .NET
Good morning,

I am interested in developing an application which supports external GUI translation files to be added as desired by the users. I thought about using the localization methods built into .NET, but I think this way I cannot distribute isolated files with translations (can I?) after the application is built.

What is the better way to achieve localization using external files in C#?

Thank you very much.

答:
If you don't want to recompile the files later you can use something like the folowing.

Create multiple xml files with the same keys and different values for different locations. Or make one big file. Save that file's path somewhere in app settings (or embed during build)

Build your application with default language on forms (for e, english)

On startup (or first startup) ask user to specify the language to use.

On each form/window in constructor (after InitializeComponent() called) replace the texts on the form with values in XML.

Example xml:

```<code><doc>
  <loc name="English">
    <form name = "main">
      <okButton>OK</okButton>
      ....
    </form>
    <form name = "about">
      <lblAuthorName>Author's name: </lblAuthorName>
      ....
    </form>
  </loc>
  <loc name="Ukrainian">
    <form name = "main">
      <okButton>Добре</okButton>
      ....
    </form>
  </loc>

</doc>
</code>```

Title: External Translations in .NET

Body:
If you don't want to recompile the files later you can use something like the folowing.

Create multiple xml files with the same keys and different values for different locations. Or make one big file. Save that file's path somewhere in app settings (or embed during build)

Build your application with default language on forms (for e, english)

On startup (or first startup) ask user to specify the language to use.

On each form/window in constructor (after InitializeComponent() called) replace the texts on the form with values in XML.

Example xml:

<doc>
  <loc name="English">
    <form name = "main">
      <okButton>OK</okButton>
      ....
    </form>
    <form name = "about">
      <lblAuthorName>Author's name: </lblAuthorName>
      ....
    </form>
  </loc>
  <loc name="Ukrainian">
    <form name = "main">
      <okButton>Добре</okButton>
      ....
    </form>
  </loc>

</doc>


问:
Magento I want to remove the left nav
I have created my own navigation as a CMS block that I am displaying in the left nav. I am still make certain categories visible in the menu so that they will show up in main navigation. However when I do that the left side nav pops back up. How can I remove this block or hide it?

  <catalog_category_default translate="label">
        Catalog Category (Non-Anchor)
        <reference name="left">
   <block type="catalog/navigation" name="catalog.leftnav" after="currency" template="catalog/navigation/left.phtml"/>
        </reference>
        <reference name="content">```

this is my theme catalog.xml file. I assumed it would be to unset this block?
Magento 1.9.3.3

答:
use unsetChild to remove blocks in layout xml

```<code><action method="unsetChild"><name>catalog.leftnav</name></action>
</code>```

normally you would do this local.xml of your theme

Title: Magento I want to remove the left nav

Body:
I have created my own navigation as a CMS block that I am displaying in the left nav. I am still make certain categories visible in the menu so that they will show up in main navigation. However when I do that the left side nav pops back up. How can I remove this block or hide it?

  <catalog_category_default translate="label">
        Catalog Category (Non-Anchor)
        <reference name="left">
   <block type="catalog/navigation" name="catalog.leftnav" after="currency" template="catalog/navigation/left.phtml"/>
        </reference>
        <reference name="content">

this is my theme catalog.xml file. I assumed it would be to unset this block?
Magento 1.9.3.3

Body:
use unsetChild to remove blocks in layout xml

<action method="unsetChild"><name>catalog.leftnav</name></action>

normally you would do this local.xml of your theme


问:
When elements don't respond to CSS design. Is the issue usually from the frame work.(Bootstrap in this case)
(Code Pen)

I am building a random quote machine, but I am having challenges with the
positioning of some elements.

My font awesome icons are not responding to my coding for their layouts.

My footer text is also not responding to my text alignments or color adjustments.

HTML:

```<code><body class="body">
 <div class="container"> <div class="quote-box"><p class="quote-text">When the Student is ready, the teacher will appear</p>
    <div class="footer">-Zen Proverb
    </div>
  <i class=" fa fa-twitter-square"></i>
   <i class="fa fa-tumblr-square"></i>
   <i class="fa fa-facebook-square"></i>
  <button>New quote</button>
  </div>

   <hr/>
 <div class="footer"> By Ojomiba</div>

</body>
</code>```

CSS:

```<code>.body{background-color:blue;}

p{font-size:25px;
text-align:center;
padding-top:30px;
padding-left:30px;
padding-right:30px;
color:blue;
}

.quote-box{background-color:white;
height:150px;
width:550px;
margin:auto;
text-align:center;
display:table;
border-radius:10px;
font-weight:800;
padding:auto;
}
button{margin-top:90px;
margin-bottom:20px;
margin-left:65%;
height:40px;
border-radius:5px;
font-size:20px;
font-weight:bold;
color:white;
background-color:brown;}

.fa{
  float:left;
  padding:auto;
height:20px;
border-radius:5px;
font-size:20px;
font-weight:bold;
color:brown;
  border:none;
  margin-top:90px;
background-color:white;
}
   .footer{color:blue;
     text-align:center;
     display:block;
     margin left:auto;
     margin-right:auto;}

hr{margin-left:auto;
  margin-right:auto;}
</code>```

答:
Might depend on various factors why the style sheet isn't rendering as you'd like. First check the basic things like:

The precedence of the style sheets. If the bootstrap css file comes after your css file, some bootstrap styles might take precedence over yours. So make sure your css files comes after bootstrap's.

Check to see if there's any JS that touches on those particular elements you're trying to style

Body:
(Code Pen)

I am building a random quote machine, but I am having challenges with the
positioning of some elements.

My font awesome icons are not responding to my coding for their layouts.

My footer text is also not responding to my text alignments or color adjustments.

HTML:

When the Student is ready, the teacher will appear

-Zen Proverb

  <button>New quote</button>

 By Ojomiba

CSS:

.body{background-color:blue;}

p{font-size:25px;
text-align:center;
padding-top:30px;
padding-left:30px;
padding-right:30px;
color:blue;
}

.quote-box{background-color:white;
height:150px;
width:550px;
margin:auto;
text-align:center;
display:table;
border-radius:10px;
font-weight:800;
padding:auto;
}
button{margin-top:90px;
margin-bottom:20px;
margin-left:65%;
height:40px;
border-radius:5px;
font-size:20px;
font-weight:bold;
color:white;
background-color:brown;}

.fa{
  float:left;
  padding:auto;
height:20px;
border-radius:5px;
font-size:20px;
font-weight:bold;
color:brown;
  border:none;
  margin-top:90px;
background-color:white;
}
   .footer{color:blue;
     text-align:center;
     display:block;
     margin left:auto;
     margin-right:auto;}

hr{margin-left:auto;
  margin-right:auto;}


问:
Button with an icon CSS
I created a simple login (via Gmail) button with green color.
Look at this, please

```<code> <!DOCTYPE html>
<html>
<head>
<style>
.button {
    background-color: #4CAF50;
    border: none;
    color: white;
    padding: 15px 32px;
    text-align: center;
    text-decoration: none;
    display: inline-block;
    font-size: 16px;
    margin: 4px 2px;
    cursor: pointer;
}
.button:icon {
    background: url(gmail-icon.png) no-repeat;
    float: left;
    width: 10px;
    height: 40px;
}
</style>
</head>
<body>

<button class="button"><span class="icon"></span>Button</button>

</body>
</code>```

But I can't see my icon on the button.
Please tell me why?

答:
set style rules for the .icon class, see below code:

.button {
    background-color: #4CAF50;
    border: none;
    color: white;
    padding: 15px 32px;
    text-align: center;
    text-decoration: none;
    display: inline-block;
    font-size: 16px;
    margin: 4px 2px;
    cursor: pointer;
}
.button .icon {
    background: url(https://image.flaticon.com/icons/svg/281/281769.svg) no-repeat;
    float: left;
    width: 20px;
    height: 20px;
    margin-right: 10px
}
.button .span {
    float: left;
    width: 20px;
    height: 20px;
}  ```
<pre class="snippet-code-html lang-html prettyprint-override"><code><!DOCTYPE html>
<html>
<head>
</head>
<body>
<button class="button"><span class="icon"></span>Button</button>
</body></code>```

Title: Button with an icon CSS

Body:
I created a simple login (via Gmail) button with green color.
Look at this, please

 <!DOCTYPE html>

<button class="button">Button</button>

But I can't see my icon on the button.
Please tell me why?

Body:
set style rules for the .icon class, see below code:

.button {
    background-color: #4CAF50;
    border: none;
    color: white;
    padding: 15px 32px;
    text-align: center;
    text-decoration: none;
    display: inline-block;
    font-size: 16px;
    margin: 4px 2px;
    cursor: pointer;
}
.button .icon {
    background: url(https://image.flaticon.com/icons/svg/281/281769.svg) no-repeat;
    float: left;
    width: 20px;
    height: 20px;
    margin-right: 10px
}
.button .span {
    float: left;
    width: 20px;
    height: 20px;
}

<!DOCTYPE html>
<html>
<head>
</head>
<body>
<button class="button"><span class="icon"></span>Button</button>
</body>


问:
Not all data is transfered via Socket
I can't send too large data packets over my setup (currently sending to 127.0.0.1), at about 30kB this functionality starts to fail. For testing I have an application that just starts a Receiver and a Sender, starts two threads, one for the sending, one for receiving, and when both have finished, compares if the sending string is the same as the received string.

```<code>void SenderThread(int count)
{
    messageOut = "";
    messageOut.append(count, 'A');

    sender->sendData(messageOut);
}

void ReceivingThread()
{
    receiver->ReceiveData(message);
}

main()
{
    receiver = new utility::Receiver();
    sender = new utility::Sender();

    receiver->startSocket(9000);
    sender->connectToSocket("127.0.0.1", 9000);
    receiver->accept();

    for (int count = 100; count < 1024 * 1024; count += 100)
    {
        std::thread sendThread(SenderThread, count);
        std::thread recvThread(ReceivingThread);

        sendThread.join();
        recvThread.join();

        printf("Sent data of length %d ", messageOut.length());
        if (message == messageOut)
            printf("successfully.\n");
        else
        {
            printf("not successfully.\n");
            printf("Length of original message: %d, Length of received message: %d.\n", messageOut.length(), message.length());
            break;
        }
    }
    delete receiver;
    delete sender;
}
</code>```

I have following code for my sending socket:

```<code>bool utility::Sender::sendData(const std::string & message)
{
    int numBytes = 0;
    int totalSent = 0;

    // Break condition: send() fails, or whole message was transfered

    while (totalSent < message.length() && send(message.substr(totalSent).c_str(), message.length() - totalSent, numBytes))
    {
        totalSent += numBytes;
    }

    return false;
}

bool utility::Sender::send(const char* pBuffer, int32_t lengthOfBuffer, int32_t &numBytes)
{
    numBytes = ::send(connectSocket, pBuffer, lengthOfBuffer, 0);

    if (numBytes == SOCKET_ERROR)
        return false;

    return true;
}
</code>```

The receiving side:

```<code>bool utility::Receiver::ReceiveData(std::string& message)
{
    int32_t numBytes = 0;

    char data[defaultBufferLength];

    // Set to blocking for the first data package

    u_long iMode = 0;
    ioctlsocket(tcpSocket, FIONBIO, &iMode);

    bool success = receive(data, defaultBufferLength, numBytes);
    message = std::string(data, numBytes);

    // Set to non-blocking for the rest of the journey
    iMode = 1;
    ioctlsocket(tcpSocket, FIONBIO, &iMode);

    while (numBytes == defaultBufferLength && receive(data, defaultBufferLength, numBytes))
    {
        message.append(data, numBytes);
    }

    return success;
}

bool utility::Receiver::receive(char* pBuffer, int32_t lengthOfBuffer, int32_t& numBytes)
{
    int32_t flags = 0;

    numBytes = recv(tcpSocket, pBuffer, lengthOfBuffer, flags);
    if (numBytes == -1)
    {
        numBytes = 0;
        if (errno == EAGAIN || errno == EWOULDBLOCK)
            return false;
        else
            close();
    }

    return true;
}
</code>```

The output I am getting is

```<code>Sent data of length 39200 successfully.
Sent data of length 39300 successfully.
Sent data of length 39400 successfully.
Sent data of length 39500 successfully.
Sent data of length 39600 successfully.
Sent data of length 39700 successfully.
Sent data of length 39800 successfully.
Sent data of length 39900 successfully.
Sent data of length 40000 successfully.
Sent data of length 40100 successfully.
Sent data of length 40200 successfully.
Sent data of length 40300 successfully.
Sent data of length 40400 successfully.
Sent data of length 40500 not successfully.
Length of original message: 40500, Length of received message: 29200.
</code>```

The thing which is the most irritating, and probably the cause of this, is the ::send(...). I can give it 2 MB of char*, and it will just send it in one swoop (but the receiver fails miserably). What can I do about that?

答: Adding to ben-voigt answer, you need to create an higher level message system for your socket, so you can send to the server the message size and on your socket receive method create a session or buffer storage that you append the received data till the message size match the total data received, once that requirement is met then you can process the data</p>

Title: Not all data is transfered via Socket

Body:
I can't send too large data packets over my setup (currently sending to 127.0.0.1), at about 30kB this functionality starts to fail. For testing I have an application that just starts a Receiver and a Sender, starts two threads, one for the sending, one for receiving, and when both have finished, compares if the sending string is the same as the received string.

void SenderThread(int count)
{
    messageOut = "";
    messageOut.append(count, 'A');

    sender->sendData(messageOut);
}

void ReceivingThread()
{
    receiver->ReceiveData(message);
}

main()
{
    receiver = new utility::Receiver();
    sender = new utility::Sender();

    receiver->startSocket(9000);
    sender->connectToSocket("127.0.0.1", 9000);
    receiver->accept();

    for (int count = 100; count < 1024 * 1024; count += 100)
    {
        std::thread sendThread(SenderThread, count);
        std::thread recvThread(ReceivingThread);

        sendThread.join();
        recvThread.join();

        printf("Sent data of length %d ", messageOut.length());
        if (message == messageOut)
            printf("successfully.\n");
        else
        {
            printf("not successfully.\n");
            printf("Length of original message: %d, Length of received message: %d.\n", messageOut.length(), message.length());
            break;
        }
    }
    delete receiver;
    delete sender;
}

I have following code for my sending socket:

bool utility::Sender::sendData(const std::string & message)
{
    int numBytes = 0;
    int totalSent = 0;

    // Break condition: send() fails, or whole message was transfered

    while (totalSent < message.length() && send(message.substr(totalSent).c_str(), message.length() - totalSent, numBytes))
    {
        totalSent += numBytes;
    }

    return false;
}

bool utility::Sender::send(const char* pBuffer, int32_t lengthOfBuffer, int32_t &numBytes)
{
    numBytes = ::send(connectSocket, pBuffer, lengthOfBuffer, 0);

    if (numBytes == SOCKET_ERROR)
        return false;

    return true;
}

The receiving side:

bool utility::Receiver::ReceiveData(std::string& message)
{
    int32_t numBytes = 0;

    char data[defaultBufferLength];

    // Set to blocking for the first data package

    u_long iMode = 0;
    ioctlsocket(tcpSocket, FIONBIO, &iMode);

    bool success = receive(data, defaultBufferLength, numBytes);
    message = std::string(data, numBytes);

    // Set to non-blocking for the rest of the journey
    iMode = 1;
    ioctlsocket(tcpSocket, FIONBIO, &iMode);

    while (numBytes == defaultBufferLength && receive(data, defaultBufferLength, numBytes))
    {
        message.append(data, numBytes);
    }

    return success;
}

bool utility::Receiver::receive(char* pBuffer, int32_t lengthOfBuffer, int32_t& numBytes)
{
    int32_t flags = 0;

    numBytes = recv(tcpSocket, pBuffer, lengthOfBuffer, flags);
    if (numBytes == -1)
    {
        numBytes = 0;
        if (errno == EAGAIN || errno == EWOULDBLOCK)
            return false;
        else
            close();
    }

    return true;
}

The output I am getting is

Sent data of length 39200 successfully.
Sent data of length 39300 successfully.
Sent data of length 39400 successfully.
Sent data of length 39500 successfully.
Sent data of length 39600 successfully.
Sent data of length 39700 successfully.
Sent data of length 39800 successfully.
Sent data of length 39900 successfully.
Sent data of length 40000 successfully.
Sent data of length 40100 successfully.
Sent data of length 40200 successfully.
Sent data of length 40300 successfully.
Sent data of length 40400 successfully.
Sent data of length 40500 not successfully.
Length of original message: 40500, Length of received message: 29200.

The thing which is the most irritating, and probably the cause of this, is the ::send(...). I can give it 2 MB of char*, and it will just send it in one swoop (but the receiver fails miserably). What can I do about that?


问:
Apache Kafka's Streams API vs Spark Streaming
I am comparing the throughput of spark streaming and Kafka streams. My results state that Kafka Streams has a higher throughput than Spark streaming. Is this correct? Shouldn't it be the other way around?

Thanks

答: No one streaming platform is universally faster than all others for every use case. Don't get fooled by benchmarketing results that compare apples to oranges (like Kafka Streams reading from a disk based source vs Spark Streaming reading from an in-memory source). You haven't posted your test but it is entirely possible that it represents a use case (and test environment) in which Kafka Streams is indeed faster.</p>


问:
Testing a function that may produce different outputs every time it is called
Given the function:

```<code>static boolean chance(int percentage) {
    return percentage != 0 && random.nextInt(101) <= percentage;
}
</code>```

and a test for that function:

```<code>@Test
public void chance_AlwaysFalse_Percentage0() {
    assertFalse(chance(0));
}
</code>```

The test does not provide with certainty that chance will always return true. I could change the change function to the following:

```<code>static boolean chance(int percentage) {
    return random.nextInt(101) <= percentage;
}
</code>```

and the test would still pass. However, there is a very small chance that random.nextInt(100) will return 0, which would make the function return true, making the test fail.

I could execute this test a billion times as well, but given the nature of random numbers, there is still a minimal chance of failure.

How should I go about testing a function like this? Should it be tested at all? What is a better approach to this problem?

答:
There are some functionalities in java that may serve for repeatable unit tests:

Random numbers: the Random constructor with a seed, for instance new Random(13) will always give the same sequence of random numbers. This already has been exploited to find a random sequence starting with 1, 2, 3, ..., 10. Or the alphabetic letters of someones name.

Time: the new java time functions allow for a faked clock, so "now" is always at a given time. Clock.fixed

And then there are mocking frameworks, that instrument the byte code of arbitrary calls so the return a provided result. Also useful for blending out more complex contexts.

Body:
Given the function:

static boolean chance(int percentage) {
    return percentage != 0 && random.nextInt(101) <= percentage;
}

and a test for that function:

@Test
public void chance_AlwaysFalse_Percentage0() {
    assertFalse(chance(0));
}

The test does not provide with certainty that chance will always return true. I could change the change function to the following:

static boolean chance(int percentage) {
    return random.nextInt(101) <= percentage;
}

and the test would still pass. However, there is a very small chance that random.nextInt(100) will return 0, which would make the function return true, making the test fail.

I could execute this test a billion times as well, but given the nature of random numbers, there is still a minimal chance of failure.

How should I go about testing a function like this? Should it be tested at all? What is a better approach to this problem?


问:
Measuring load per database in Postgres using 'active' processes in pg_stat_activity?
I am trying to measure the load that various databases living on the same Postgres server are incurring, to determine how to best split them up across multiple servers. I devised this query:

```<code>select
 now() as now,
 datname as database,
 usename as user,
 count(*) as processes
from pg_stat_activity
where state = 'active'
 and waiting = 'f'
 and query not like '%from pg_stat_activity%'
group by
 datname,
 usename;
</code>```

But there were surprisingly few active processes!

Digging deeper I ran a simple query that returns 20k rows and took 5 seconds to complete, according to the client I ran it from. When I queried pg_stat_activity during that time, the process was idle! I repeated this experiment several times.

The Postgres documentation says active means

The backend is executing a query.

and idle means

The backend is waiting for a new client command.

Is it really more nuanced than that? Why was the process running my query was not active when I checked in?

If this approach is flawed, what alternatives are there for measuring load at a database granularity than periodically sampling the number of active processes?

答:
your expectations regarding active, idleand idle in transaction are very right. The only explanation I can think of is a huge delay in displaying data client side. So the query indeed finished on server and session is idle and yet you don't see the result with client.

regarding the load measurement - I would not rely on number of active sessions much. Pure luck to hit the fast query in active state. Eg hypothetically you can check pg_stat_activity each second and see one active session, but between measurement one db was queried 10 times and another once - yet none of those numbers will be seen. Because they were active between executions. And this 10+1 active states (although mean that one db is queried 10times more often) do not mean you should consider load at all - because cluster is so much not loaded, that you can't even catch executions. But this unavoidably mean that you can catch many active sessions and it would not mean that server is loaded indeed.

so at least take now()-query_start to your query to catch longer queries. Or even better save execution time for some often queries and measure if it degrades over time. Or better select pid and check resources eaten by that pid.

Btw for longer queries look into pg_stat_statements - looking how they change over time can give you some expectations on how the load changes

Body:
I am trying to measure the load that various databases living on the same Postgres server are incurring, to determine how to best split them up across multiple servers. I devised this query:

select
 now() as now,
 datname as database,
 usename as user,
 count(*) as processes
from pg_stat_activity
where state = 'active'
 and waiting = 'f'
 and query not like '%from pg_stat_activity%'
group by
 datname,
 usename;

But there were surprisingly few active processes!

Digging deeper I ran a simple query that returns 20k rows and took 5 seconds to complete, according to the client I ran it from. When I queried pg_stat_activity during that time, the process was idle! I repeated this experiment several times.

The Postgres documentation says active means

The backend is executing a query.

and idle means

The backend is waiting for a new client command.

Is it really more nuanced than that? Why was the process running my query was not active when I checked in?

If this approach is flawed, what alternatives are there for measuring load at a database granularity than periodically sampling the number of active processes?


问:
How to indicate the database in SparkSQL over Hive in Spark 1.3
I have a simple Scala code that retrieves data from the Hive database and creates an RDD out of the result set.  It works fine with HiveContext. The code is similar to this:

```<code>val hc = new HiveContext(sc)
val mySql = "select PRODUCT_CODE, DATA_UNIT from account"
hc.sql("use myDatabase")
val rdd = hc.sql(mySql).rdd
</code>```

The version of Spark that I'm using is 1.3. The problem is that the default setting for hive.execution.engine is 'mr' that makes Hive to use MapReduce which is slow. Unfortunately I can't force it to use "spark".
I tried to use SQLContext by replacing hc = new SQLContext(sc) to see if performance will improve. With this change the line

```<code>hc.sql("use myDatabase")
</code>```

is throwing the following exception:

```<code>Exception in thread "main" java.lang.RuntimeException: [1.1] failure: ``insert'' expected but identifier use found

use myDatabase
^
</code>```

The Spark 1.3 documentation says that SparkSQL can work with Hive tables. My question is how to indicate that I want to use a certain database instead of the default one.

答:
use database

is supported in later Spark versions

https://docs.databricks.com/spark/latest/spark-sql/language-manual/use-database.html

You need to put the statement in two separate spark.sql calls like this:

```<code>spark.sql("use mydb")
spark.sql("select * from mytab_in_mydb").show
</code>```

Body:
I have a simple Scala code that retrieves data from the Hive database and creates an RDD out of the result set.  It works fine with HiveContext. The code is similar to this:

val hc = new HiveContext(sc)
val mySql = "select PRODUCT_CODE, DATA_UNIT from account"
hc.sql("use myDatabase")
val rdd = hc.sql(mySql).rdd

The version of Spark that I'm using is 1.3. The problem is that the default setting for hive.execution.engine is 'mr' that makes Hive to use MapReduce which is slow. Unfortunately I can't force it to use "spark".
I tried to use SQLContext by replacing hc = new SQLContext(sc) to see if performance will improve. With this change the line

hc.sql("use myDatabase")

is throwing the following exception:

Exception in thread "main" java.lang.RuntimeException: [1.1] failure: ``insert'' expected but identifier use found

use myDatabase
^

The Spark 1.3 documentation says that SparkSQL can work with Hive tables. My question is how to indicate that I want to use a certain database instead of the default one.

Body:
use database

is supported in later Spark versions

https://docs.databricks.com/spark/latest/spark-sql/language-manual/use-database.html

You need to put the statement in two separate spark.sql calls like this:

spark.sql("use mydb")
spark.sql("select * from mytab_in_mydb").show