问:
wrong counting with $filter
My db cofiguration looks like:

```<code>{
    "_id" : ObjectId("5ece47aa6510a611b47aac5a"),
    "boats" : [
        {
            "_id" : ObjectId("5ece47aa6510a611b47aac6e"),
            "model" : "Dufour",
            "year" : 2019,
            "about" : [
                {
                    "_id" : ObjectId("5ece47aa6510a611b47aac71"),
                    "Capacity" : 14,
                    "characteristics" : [
                        {
                            "_id" : ObjectId("5ece47aa6510a611b47aac73"),
                            "fuel" : "petrol",
                             "fuelCap" : 200
                        },
                        {
                            "_id" : ObjectId("5ece47aa6510a611b47aac73"),
                            "fuel" : "petrol",
                            "fuelCap" : 120
                        },
                    ]
                },
                {
                    "_id" : ObjectId("5ece47aa6510a611b47aac71"),
                    "Capacity" : 8,
                    "characteristics" : [
                        {
                            "_id" : ObjectId("5ece47aa6510a611b47aac73"),
                            "fuel" : "benzin",
                             "fuelCap" : 180
                        },
                        {
                            "_id" : ObjectId("5ece47aa6510a611b47aac73"),
                            "fuel" : "petrol",
                            "fuelCap" : 100
                        },
                    ]
                },
              {...},
              {...},
           ]
    }
</code>```

Now i am trying to count the number of boats which have "fuel" : "petrol", so i use the code bellow:

```<code>router.get('/boat', async(req, res)=>{
    try{
        const fuelData = await Boat.aggregate([
            {
            $project: {
                fuelData: {
                     $filter: {
                        input: "$boats",
                        as: "boats",
                       cond: {
                            $filter:{
                                input:"$$boats.about",
                                as:"about",
                                cond:{
                                    $filter:{
                                        input:"$$about.characteristics",
                                        as:"characteristics",
                                        cond:{
                                            $eq:["$$activity1.activity.type", "STILL"]
                                        }
                                    }

                                }
                            }
                        }

                    }
                }
            }
            },
            {
               $project: {
                  boatsCount: {$size : "$fuelData" }
               }
            }
        ])
        res.status(201).send(fuelData)
    }catch(e){
        res.send(e)
    }

})
</code>```

The problem is that return wrong number of boatCount. And it seems like it returns the number of the boats which are inside the db. Any help how to count correctly the boats which have "fuel" : "petrol"?
Is there anything wrong in my code?

答:
https://mongoplayground.net/p/D0FEhTMJEJ1

Hope this is what you need.
The sample data you have provided is missing a ] & }.

So I added 1 additional boat with 2 about.

```<code>db.collection.aggregate([
  {
    $match: {
      "boats.about.characteristics.fuel": "petrol"
    }
  },
  {
    $unwind: "$boats"
  },
  {
    $unwind: "$boats.about"
  },
  {
    $match: {
      "boats.about.characteristics.fuel": "petrol"
    }
  },
  {
    $group: {
      _id: null,
      count: {
        $sum: 1
      }
    }
  }
])
</code>```

Title: wrong counting with $filter

Body:
My db cofiguration looks like:

{
    "_id" : ObjectId("5ece47aa6510a611b47aac5a"),
    "boats" : [
        {
            "_id" : ObjectId("5ece47aa6510a611b47aac6e"),
            "model" : "Dufour",
            "year" : 2019,
            "about" : [
                {
                    "_id" : ObjectId("5ece47aa6510a611b47aac71"),
                    "Capacity" : 14,
                    "characteristics" : [
                        {
                            "_id" : ObjectId("5ece47aa6510a611b47aac73"),
                            "fuel" : "petrol",
                             "fuelCap" : 200
                        },
                        {
                            "_id" : ObjectId("5ece47aa6510a611b47aac73"),
                            "fuel" : "petrol",
                            "fuelCap" : 120
                        },
                    ]
                },
                {
                    "_id" : ObjectId("5ece47aa6510a611b47aac71"),
                    "Capacity" : 8,
                    "characteristics" : [
                        {
                            "_id" : ObjectId("5ece47aa6510a611b47aac73"),
                            "fuel" : "benzin",
                             "fuelCap" : 180
                        },
                        {
                            "_id" : ObjectId("5ece47aa6510a611b47aac73"),
                            "fuel" : "petrol",
                            "fuelCap" : 100
                        },
                    ]
                },
              {...},
              {...},
           ]
    }

Now i am trying to count the number of boats which have "fuel" : "petrol", so i use the code bellow:

router.get('/boat', async(req, res)=>{
    try{
        const fuelData = await Boat.aggregate([
            {
            $project: {
                fuelData: {
                     $filter: {
                        input: "$boats",
                        as: "boats",
                       cond: {
                            $filter:{
                                input:"$$boats.about",
                                as:"about",
                                cond:{
                                    $filter:{
                                        input:"$$about.characteristics",
                                        as:"characteristics",
                                        cond:{
                                            $eq:["$$activity1.activity.type", "STILL"]
                                        }
                                    }

                                }
                            }
                        }

                    }
                }
            }
            },
            {
               $project: {
                  boatsCount: {$size : "$fuelData" }
               }
            }
        ])
        res.status(201).send(fuelData)
    }catch(e){
        res.send(e)
    }

})

The problem is that return wrong number of boatCount. And it seems like it returns the number of the boats which are inside the db. Any help how to count correctly the boats which have "fuel" : "petrol"?
Is there anything wrong in my code?

Body:
https://mongoplayground.net/p/D0FEhTMJEJ1

Hope this is what you need.
The sample data you have provided is missing a ] & }.

So I added 1 additional boat with 2 about.

db.collection.aggregate([
  {
    $match: {
      "boats.about.characteristics.fuel": "petrol"
    }
  },
  {
    $unwind: "$boats"
  },
  {
    $unwind: "$boats.about"
  },
  {
    $match: {
      "boats.about.characteristics.fuel": "petrol"
    }
  },
  {
    $group: {
      _id: null,
      count: {
        $sum: 1
      }
    }
  }
])


来源: stackexchange

问:
Convert Complex Entity to DTO With ModelMapper
i'm working in a rest API using Spring boot.
when i wanted to return my entity from an End Point i realized that the Properties are different from what i need on my response so i tried to use Model Mapper to return a DTO.

My entity is like this:

```<code>public class RuleEntity {

private String ruleId;
private String bankDecision;
private String aggregatorFunctionType;
private String limitOperatorType;
private double limitRule;
private Integer windowMinutes;
private Integer layer;
private String expressionRule;
private String status;
private List<GroupingKeyName> groupingKeyNames;
private List<RuleFilter> ruleFilters;
</code>```

}

And the DTO that i need Must Be Like this:

```<code>public class RuleDTO {

private String ruleId;
private String bankDecision;
private String aggregatorFunctionType;
private String limitOperatorType;
private double limitRule;
private Integer windowMinutes;
private Integer layer;
private String expressionRule;
private String status;
private List<String> groupingKeyNames;
private List<String> ruleFilters;
</code>```

}

The only change is that the last two lists are of String instead of The Object

The Objects groupingKeyNames and ruleFilters have a Name and an ID, and i only need the name on the list of DTO so it is a List of Strings

I tried using Model Mapper like this:

```<code>ModelMapper modelMapper = new ModelMapper();
    RuleSetModel ruleSetModel =  modelMapper.map(ruleSetEntity, RuleSetModel.class);
</code>```

it works, with all the properties but in the Lists it is returning something like:

```<code>groupingKeyNames=[GroupingKeyName(groupingKeyId=1, name=cardHash)], ruleFilters=[RuleFilter(ruleFilterId=1, name=status)]
</code>```

What could i do so i get a result like this:

```<code>groupingKeyNames=[cardHash], ruleFilters=[status]
</code>```

Thanks in advance!

答:
Create a method into your RuleEntity to do it

```<code>    public RuleDTO dto() {
    // config to skip
    PropertyMap<RuleEntity, RuleDTO> propertyMap = new PropertyMap<RuleEntity, RuleDTO>() {
        @Override
        protected void configure() {
            skip(destination.getGroupingKeyNames());
            skip(destination.getRuleFilters());
        }
    };

    RuleDTO ruleDTO = new RuleDTO();
    ModelMapper modelMapper = new ModelMapper();
    modelMapper.getConfiguration().setPropertyCondition(Conditions.isNotNull());
    modelMapper.addMappings(propertyMap);
    modelMapper.map(this,ruleDTO);

    if (!this.groupingKeyNames.isEmpty()) {
        ruleDTO.getGroupingKeyNames().clear();
        List<String> tmpGroupingKeyNames = new ArrayList<>();
        this.getGroupingKeyNames().forEach(itemDTO -> {
            tmpGroupingKeyNames.add(itemDTO.name);
        });
        ruleDTO.getGroupingKeyNames().addAll(tmpGroupingKeyNames);
    }

    if (!this.ruleFilters.isEmpty()) {
        ruleDTO.getRuleFilters().clear();
        List<String> tmpRuleFilters = new ArrayList<>();
        this.getRuleFilters().forEach(itemDTO -> {
            tmpRuleFilters.add(itemDTO.name);
        });
        ruleDTO.getRuleFilters().addAll(tmpRuleFilters);
    }
    return ruleDTO;
}
</code>```

ContentLicense: CC BY-SA 4.0

Body:
i'm working in a rest API using Spring boot.
when i wanted to return my entity from an End Point i realized that the Properties are different from what i need on my response so i tried to use Model Mapper to return a DTO.

My entity is like this:

public class RuleEntity {

private String ruleId;
private String bankDecision;
private String aggregatorFunctionType;
private String limitOperatorType;
private double limitRule;
private Integer windowMinutes;
private Integer layer;
private String expressionRule;
private String status;
private List<GroupingKeyName> groupingKeyNames;
private List<RuleFilter> ruleFilters;

}

And the DTO that i need Must Be Like this:

public class RuleDTO {

private String ruleId;
private String bankDecision;
private String aggregatorFunctionType;
private String limitOperatorType;
private double limitRule;
private Integer windowMinutes;
private Integer layer;
private String expressionRule;
private String status;
private List<String> groupingKeyNames;
private List<String> ruleFilters;

}

The only change is that the last two lists are of String instead of The Object

The Objects groupingKeyNames and ruleFilters have a Name and an ID, and i only need the name on the list of DTO so it is a List of Strings

I tried using Model Mapper like this:

ModelMapper modelMapper = new ModelMapper();
    RuleSetModel ruleSetModel =  modelMapper.map(ruleSetEntity, RuleSetModel.class);

it works, with all the properties but in the Lists it is returning something like:

groupingKeyNames=[GroupingKeyName(groupingKeyId=1, name=cardHash)], ruleFilters=[RuleFilter(ruleFilterId=1, name=status)]

What could i do so i get a result like this:

groupingKeyNames=[cardHash], ruleFilters=[status]

Thanks in advance!

Body:
Create a method into your RuleEntity to do it

    public RuleDTO dto() {
    // config to skip
    PropertyMap<RuleEntity, RuleDTO> propertyMap = new PropertyMap<RuleEntity, RuleDTO>() {
        @Override
        protected void configure() {
            skip(destination.getGroupingKeyNames());
            skip(destination.getRuleFilters());
        }
    };

    RuleDTO ruleDTO = new RuleDTO();
    ModelMapper modelMapper = new ModelMapper();
    modelMapper.getConfiguration().setPropertyCondition(Conditions.isNotNull());
    modelMapper.addMappings(propertyMap);
    modelMapper.map(this,ruleDTO);

    if (!this.groupingKeyNames.isEmpty()) {
        ruleDTO.getGroupingKeyNames().clear();
        List<String> tmpGroupingKeyNames = new ArrayList<>();
        this.getGroupingKeyNames().forEach(itemDTO -> {
            tmpGroupingKeyNames.add(itemDTO.name);
        });
        ruleDTO.getGroupingKeyNames().addAll(tmpGroupingKeyNames);
    }

    if (!this.ruleFilters.isEmpty()) {
        ruleDTO.getRuleFilters().clear();
        List<String> tmpRuleFilters = new ArrayList<>();
        this.getRuleFilters().forEach(itemDTO -> {
            tmpRuleFilters.add(itemDTO.name);
        });
        ruleDTO.getRuleFilters().addAll(tmpRuleFilters);
    }
    return ruleDTO;
}


问:
Angular2 subscribing to changes to @Input in Child Component
I have a parent and child component. Parent component has index and passes this through to the child component as an @Input. This index value constantly changes in the parent component, and inside my child component, I want to run a function everytime the Parent component changes index. This has a slide component that constantly changes it. How can I achieve this? I've tried with the below code (this.index.subscribe(() =>), but I'm getting that it's not a function everytime I try and initialise the subscription.

EDIT: There is no related code in the Child template that could impact this, so it's not provided. ngOnChange doesn't seem to work as the change is happening in the directive as opposed to the parent's template.

Child:

```<code>import {Component, OnInit, ViewChild, Input} from '@angular/core';
import {Observable} from 'rxjs/Observable';

@Component({
    selector: "child",
    templateUrl: "components/child/child.html",
})

export class ChildComponent implements OnInit {
    @Input() index: string;
    currentIndex: any;

    constructor() {}

    ngOnInit() {}

    ngOnChanges(){}

    ngAfterViewInit() {
        console.log("index " + this.index);
        this.currentIndex = this.index.subscribe(() => {
             console.log("index " + this.index);
        })
    }

    ngOnDestroy() {
        this.currentIndex.unsubscribe();
    }

}
</code>```

Parent:

```<code>import {Component, ElementRef, OnInit, ViewChild} from '@angular/core';
import {Page} from "ui/page";
import {ChildComponent} from '/components/child/child.component'

@Component({
    selector: "parent",
    template: "<child [index]="index"></child>",
    directives: [ChildComponent]
})

export class ParentComponent implements OnInit {

    index: string = "0,1";

    constructor(private page: Page) {
    }

}
</code>```

答:
Another solution for "transforming" the changing value into an observable should be

@Input() status: Status;
private status$: Subject<Status>;

constructor() {
    this.status$ = new Subject<Status>();
}

ngOnChanges(changes: {[propertyName: string]: SimpleChange}) {

    this.status.next(this.status);
}
```

ContentLicense: CC BY-SA 3.0

Body:
I have a parent and child component. Parent component has index and passes this through to the child component as an @Input. This index value constantly changes in the parent component, and inside my child component, I want to run a function everytime the Parent component changes index. This has a slide component that constantly changes it. How can I achieve this? I've tried with the below code (this.index.subscribe(() =>), but I'm getting that it's not a function everytime I try and initialise the subscription.

EDIT: There is no related code in the Child template that could impact this, so it's not provided. ngOnChange doesn't seem to work as the change is happening in the directive as opposed to the parent's template.

Child:

import {Component, OnInit, ViewChild, Input} from '@angular/core';
import {Observable} from 'rxjs/Observable';

@Component({
    selector: "child",
    templateUrl: "components/child/child.html",
})

export class ChildComponent implements OnInit {
    @Input() index: string;
    currentIndex: any;

    constructor() {}

    ngOnInit() {}

    ngOnChanges(){}

    ngAfterViewInit() {
        console.log("index " + this.index);
        this.currentIndex = this.index.subscribe(() => {
             console.log("index " + this.index);
        })
    }

    ngOnDestroy() {
        this.currentIndex.unsubscribe();
    }

}

Parent:

import {Component, ElementRef, OnInit, ViewChild} from '@angular/core';
import {Page} from "ui/page";
import {ChildComponent} from '/components/child/child.component'

@Component({
    selector: "parent",
    template: "<child [index]="index"></child>",
    directives: [ChildComponent]
})

export class ParentComponent implements OnInit {

    index: string = "0,1";

    constructor(private page: Page) {
    }

}


问:
Export 2 gridview to 2 worksheets in single Excel with formatting
I want to export 2 gridviews to 2 worksheets in single excel file.
and also don't want to use any other dll or utility or Interop assemblies.</p>

答:
I've just done that a few days a ago.

```<code>Imports System.Data.SqlClient

Public Class Export

Inherits System.Web.UI.Page

Dim myGridViews(2) As Object 'number of gridviews -1

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim myGrid As New GridView
    Dim myN As Integer = 0
    Dim myID As String
    Dim strq As String
    Dim cmd As New SqlCommand
    Dim dt As DataTable

    'load the first gridview
    myGrid = OneWorkBook
    myID = myGrid.ID
    strq = "SELECT * FROM " + myID
    cmd = New SqlCommand(strq)
    dt = Getdata(cmd)
    myGrid.DataSource = dt
    myGrid.DataBind()
    myGridViews(myN) = myGrid 'add this gridview to myGridViews, myN =0
    myN += 1

    'load the second gridview
    myGrid = TwoWorkBook
    myID = myGrid.ID
    strq = "SELECT * FROM " + myID
    cmd = New SqlCommand(strq)
    dt = Getdata(cmd)
    myGrid.DataSource = dt
    myGrid.DataBind()
    myGridViews(myN) = myGrid 'add this gridview to myGridViews, myN =1
    myN += 1

    'load the third gridview
    myGrid = ThreeWorkBook
    myID = myGrid.ID
    strq = "SELECT * FROM " + myID
    cmd = New SqlCommand(strq)
    dt = Getdata(cmd)
    myGrid.DataSource = dt
    myGrid.DataBind()
    myGridViews(myN) = myGrid 'add this gridview to myGridViews, myN =2
    myN += 1

End Sub

Protected Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click
    CreateWorkBook(myGridViews, "ExportToExcel", 80)
End Sub
Public Shared Sub CreateWorkBook(ByVal cList As Object, ByVal wbName As String, ByVal CellWidth As Integer)
    Dim attachment As String = "attachment; filename=""" & wbName & ".xml"""

    HttpContext.Current.Response.ClearContent()

    HttpContext.Current.Response.AddHeader("content-disposition", attachment)
    HttpContext.Current.Response.ContentType = "application/ms-excel"

    Dim sw As System.IO.StringWriter = New System.IO.StringWriter()
    sw.WriteLine("<?xml version=""1.0""?>")

    sw.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
    sw.WriteLine("<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""")

    sw.WriteLine("xmlns:o=""urn:schemas-microsoft-com:office:office""")
    sw.WriteLine("xmlns:x=""urn:schemas-microsoft-com:office:excel""")

    sw.WriteLine("xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""")
    sw.WriteLine("xmlns:html=""http://www.w3.org/TR/REC-html40"">")

    sw.WriteLine("<DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">")
    sw.WriteLine("<LastAuthor>Try Not Catch</LastAuthor>")

    sw.WriteLine("<Created>2010-05-15T19:14:19Z</Created>")
    sw.WriteLine("<Version>11.9999</Version>")

    sw.WriteLine("</DocumentProperties>")
    sw.WriteLine("<ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">")

    sw.WriteLine("<WindowHeight>9210</WindowHeight>")
    sw.WriteLine("<WindowWidth>19035</WindowWidth>")

    sw.WriteLine("<WindowTopX>0</WindowTopX>")
    sw.WriteLine("<WindowTopY>90</WindowTopY>")

    sw.WriteLine("<ProtectStructure>False</ProtectStructure>")
    sw.WriteLine("<ProtectWindows>False</ProtectWindows>")

    sw.WriteLine("</ExcelWorkbook>")
    sw.WriteLine("<Styles>")

    sw.WriteLine("<Style ss:ID=""Default"" ss:Name=""Normal"">")
    sw.WriteLine("<Alignment ss:Vertical=""Bottom""/>")

    sw.WriteLine("<Borders/>")
    sw.WriteLine("<Font/>")

    sw.WriteLine("<Interior/>")
    sw.WriteLine("<NumberFormat/>")

    sw.WriteLine("<Protection/>")
    sw.WriteLine("</Style>")

    sw.WriteLine("<Style ss:ID=""s22"">")
    sw.WriteLine("<Alignment ss:Horizontal=""Center"" ss:Vertical=""Center"" ss:WrapText=""1""/>")

    sw.WriteLine("<Borders>")
    sw.WriteLine("<Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("</Borders>")

    sw.WriteLine("<Font ss:Bold=""1""/>")
    sw.WriteLine("</Style>")

    sw.WriteLine("<Style ss:ID=""s23"">")
    sw.WriteLine("<Alignment ss:Vertical=""Bottom"" ss:WrapText=""1""/>")

    sw.WriteLine("<Borders>")
    sw.WriteLine("<Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("</Borders>")

    sw.WriteLine("</Style>")
    sw.WriteLine("<Style ss:ID=""s24"">")

    sw.WriteLine("<Alignment ss:Vertical=""Bottom"" ss:WrapText=""1""/>")
    sw.WriteLine("<Borders>")

    sw.WriteLine("<Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("<Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("<Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("<Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("</Borders>")
    sw.WriteLine("<Font ss:Color=""#FFFFFF""/>")

    sw.WriteLine("<Interior ss:Color=""#FF6A6A"" ss:Pattern=""Solid""/>") 'set header colour here
    sw.WriteLine("</Style>")

    sw.WriteLine("</Styles>")
    For Each gView As GridView In cList
        CreateWorkSheet(gView.ID.ToString, sw, gView, CellWidth)
    Next
    sw.WriteLine("</Workbook>")
    HttpContext.Current.Response.Write(sw.ToString())
    HttpContext.Current.Response.End()

End Sub
Private Shared Sub CreateWorkSheet(ByVal wsName As String, ByVal sw As System.IO.StringWriter, ByVal gv As GridView, ByVal cellwidth As Integer)
    If IsNothing(gv.HeaderRow) = False Then
        sw.WriteLine("<Worksheet ss:Name=""" & wsName & """>")
        Dim cCount As Integer = gv.HeaderRow.Cells.Count
        Dim rCount As Long = gv.Rows.Count + 1
        sw.WriteLine("<Table ss:ExpandedColumnCount=""" & cCount & """ ss:ExpandedRowCount=""" & rCount & """ x:FullColumns=""1""")
        sw.WriteLine("x:FullRows=""1"">")
        For i As Integer = (cCount - cCount) To (cCount - 1)
            sw.WriteLine("<Column ss:AutoFitWidth=""1"" ss:Width=""" & cellwidth & """/>")
        Next

        GridRowIterate(gv, sw)
        sw.WriteLine("</Table>")
        sw.WriteLine("<WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">")

        sw.WriteLine("<Selected/>")
        sw.WriteLine("<DoNotDisplayGridlines/>")

        sw.WriteLine("<ProtectObjects>False</ProtectObjects>")
        sw.WriteLine("<ProtectScenarios>False</ProtectScenarios>")

        sw.WriteLine("</WorksheetOptions>")
        sw.WriteLine("</Worksheet>")
    End If
End Sub
Private Shared Sub GridRowIterate(ByVal gv As GridView, ByVal sw As System.IO.StringWriter)
    sw.WriteLine("<Row>")

    For Each tc As TableCell In gv.HeaderRow.Cells
        Dim tcText As String = tc.Text

        Dim tcWidth As String = gv.Width.Value
        Dim dType As String = "String"

        If IsNumeric(tcText) = True Then

            dType = "Number"

        End If
        sw.WriteLine("<Cell ss:StyleID=""s24""><Data ss:Type=""String"">" & tcText & "</Data></Cell>")

    Next
    sw.WriteLine("</Row>")

    For Each gr As GridViewRow In gv.Rows
        sw.WriteLine("<Row>")

        For Each gc As TableCell In gr.Cells
            Dim gcText As String = gc.Text
            Dim dType As String = "String"

            If IsNumeric(gcText) = True Then

                dType = "Number"
                gcText = CDbl(gcText)

            End If
            sw.WriteLine("<Cell ss:StyleID=""s23""><Data ss:Type=""" & dType & """>" & gcText & "</Data></Cell>")

        Next
        sw.WriteLine("</Row>")
    Next

End Sub
Private Function Getdata(ByVal cmd As SqlCommand) As DataTable
    Dim dt As New DataTable()
    Dim strcon As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
    Dim con As New SqlConnection(strcon)
    Dim sda As New SqlDataAdapter
    cmd.CommandType = CommandType.Text
    cmd.Connection = con
    Try
        con.Open()
        sda.SelectCommand = cmd
        sda.Fill(dt)
        Return dt
    Catch ex As Exception
        Throw (ex)
    Finally
        con.Close()
        sda.Dispose()
        con.Dispose()
    End Try

End Function
</code>```

End Class

ContentLicense: CC BY-SA 2.5

Body:
I've just done that a few days a ago.

Imports System.Data.SqlClient

Public Class Export

Inherits System.Web.UI.Page

Dim myGridViews(2) As Object 'number of gridviews -1

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim myGrid As New GridView
    Dim myN As Integer = 0
    Dim myID As String
    Dim strq As String
    Dim cmd As New SqlCommand
    Dim dt As DataTable

    'load the first gridview
    myGrid = OneWorkBook
    myID = myGrid.ID
    strq = "SELECT * FROM " + myID
    cmd = New SqlCommand(strq)
    dt = Getdata(cmd)
    myGrid.DataSource = dt
    myGrid.DataBind()
    myGridViews(myN) = myGrid 'add this gridview to myGridViews, myN =0
    myN += 1

    'load the second gridview
    myGrid = TwoWorkBook
    myID = myGrid.ID
    strq = "SELECT * FROM " + myID
    cmd = New SqlCommand(strq)
    dt = Getdata(cmd)
    myGrid.DataSource = dt
    myGrid.DataBind()
    myGridViews(myN) = myGrid 'add this gridview to myGridViews, myN =1
    myN += 1

    'load the third gridview
    myGrid = ThreeWorkBook
    myID = myGrid.ID
    strq = "SELECT * FROM " + myID
    cmd = New SqlCommand(strq)
    dt = Getdata(cmd)
    myGrid.DataSource = dt
    myGrid.DataBind()
    myGridViews(myN) = myGrid 'add this gridview to myGridViews, myN =2
    myN += 1

End Sub

Protected Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click
    CreateWorkBook(myGridViews, "ExportToExcel", 80)
End Sub
Public Shared Sub CreateWorkBook(ByVal cList As Object, ByVal wbName As String, ByVal CellWidth As Integer)
    Dim attachment As String = "attachment; filename=""" & wbName & ".xml"""

    HttpContext.Current.Response.ClearContent()

    HttpContext.Current.Response.AddHeader("content-disposition", attachment)
    HttpContext.Current.Response.ContentType = "application/ms-excel"

    Dim sw As System.IO.StringWriter = New System.IO.StringWriter()
    sw.WriteLine("<?xml version=""1.0""?>")

    sw.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
    sw.WriteLine("<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""")

    sw.WriteLine("xmlns:o=""urn:schemas-microsoft-com:office:office""")
    sw.WriteLine("xmlns:x=""urn:schemas-microsoft-com:office:excel""")

    sw.WriteLine("xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""")
    sw.WriteLine("xmlns:html=""http://www.w3.org/TR/REC-html40"">")

    sw.WriteLine("<DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">")
    sw.WriteLine("<LastAuthor>Try Not Catch</LastAuthor>")

    sw.WriteLine("<Created>2010-05-15T19:14:19Z</Created>")
    sw.WriteLine("<Version>11.9999</Version>")

    sw.WriteLine("</DocumentProperties>")
    sw.WriteLine("<ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">")

    sw.WriteLine("<WindowHeight>9210</WindowHeight>")
    sw.WriteLine("<WindowWidth>19035</WindowWidth>")

    sw.WriteLine("<WindowTopX>0</WindowTopX>")
    sw.WriteLine("<WindowTopY>90</WindowTopY>")

    sw.WriteLine("<ProtectStructure>False</ProtectStructure>")
    sw.WriteLine("<ProtectWindows>False</ProtectWindows>")

    sw.WriteLine("</ExcelWorkbook>")
    sw.WriteLine("<Styles>")

    sw.WriteLine("<Style ss:ID=""Default"" ss:Name=""Normal"">")
    sw.WriteLine("<Alignment ss:Vertical=""Bottom""/>")

    sw.WriteLine("<Borders/>")
    sw.WriteLine("<Font/>")

    sw.WriteLine("<Interior/>")
    sw.WriteLine("<NumberFormat/>")

    sw.WriteLine("<Protection/>")
    sw.WriteLine("</Style>")

    sw.WriteLine("<Style ss:ID=""s22"">")
    sw.WriteLine("<Alignment ss:Horizontal=""Center"" ss:Vertical=""Center"" ss:WrapText=""1""/>")

    sw.WriteLine("<Borders>")
    sw.WriteLine("<Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("</Borders>")

    sw.WriteLine("<Font ss:Bold=""1""/>")
    sw.WriteLine("</Style>")

    sw.WriteLine("<Style ss:ID=""s23"">")
    sw.WriteLine("<Alignment ss:Vertical=""Bottom"" ss:WrapText=""1""/>")

    sw.WriteLine("<Borders>")
    sw.WriteLine("<Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("</Borders>")

    sw.WriteLine("</Style>")
    sw.WriteLine("<Style ss:ID=""s24"">")

    sw.WriteLine("<Alignment ss:Vertical=""Bottom"" ss:WrapText=""1""/>")
    sw.WriteLine("<Borders>")

    sw.WriteLine("<Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("<Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("<Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("<Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("</Borders>")
    sw.WriteLine("<Font ss:Color=""#FFFFFF""/>")

    sw.WriteLine("<Interior ss:Color=""#FF6A6A"" ss:Pattern=""Solid""/>") 'set header colour here
    sw.WriteLine("</Style>")

    sw.WriteLine("</Styles>")
    For Each gView As GridView In cList
        CreateWorkSheet(gView.ID.ToString, sw, gView, CellWidth)
    Next
    sw.WriteLine("</Workbook>")
    HttpContext.Current.Response.Write(sw.ToString())
    HttpContext.Current.Response.End()

End Sub
Private Shared Sub CreateWorkSheet(ByVal wsName As String, ByVal sw As System.IO.StringWriter, ByVal gv As GridView, ByVal cellwidth As Integer)
    If IsNothing(gv.HeaderRow) = False Then
        sw.WriteLine("<Worksheet ss:Name=""" & wsName & """>")
        Dim cCount As Integer = gv.HeaderRow.Cells.Count
        Dim rCount As Long = gv.Rows.Count + 1
        sw.WriteLine("<Table ss:ExpandedColumnCount=""" & cCount & """ ss:ExpandedRowCount=""" & rCount & """ x:FullColumns=""1""")
        sw.WriteLine("x:FullRows=""1"">")
        For i As Integer = (cCount - cCount) To (cCount - 1)
            sw.WriteLine("<Column ss:AutoFitWidth=""1"" ss:Width=""" & cellwidth & """/>")
        Next

        GridRowIterate(gv, sw)
        sw.WriteLine("</Table>")
        sw.WriteLine("<WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">")

        sw.WriteLine("<Selected/>")
        sw.WriteLine("<DoNotDisplayGridlines/>")

        sw.WriteLine("<ProtectObjects>False</ProtectObjects>")
        sw.WriteLine("<ProtectScenarios>False</ProtectScenarios>")

        sw.WriteLine("</WorksheetOptions>")
        sw.WriteLine("</Worksheet>")
    End If
End Sub
Private Shared Sub GridRowIterate(ByVal gv As GridView, ByVal sw As System.IO.StringWriter)
    sw.WriteLine("<Row>")

    For Each tc As TableCell In gv.HeaderRow.Cells
        Dim tcText As String = tc.Text

        Dim tcWidth As String = gv.Width.Value
        Dim dType As String = "String"

        If IsNumeric(tcText) = True Then

            dType = "Number"

        End If
        sw.WriteLine("<Cell ss:StyleID=""s24""><Data ss:Type=""String"">" & tcText & "</Data></Cell>")

    Next
    sw.WriteLine("</Row>")

    For Each gr As GridViewRow In gv.Rows
        sw.WriteLine("<Row>")

        For Each gc As TableCell In gr.Cells
            Dim gcText As String = gc.Text
            Dim dType As String = "String"

            If IsNumeric(gcText) = True Then

                dType = "Number"
                gcText = CDbl(gcText)

            End If
            sw.WriteLine("<Cell ss:StyleID=""s23""><Data ss:Type=""" & dType & """>" & gcText & "</Data></Cell>")

        Next
        sw.WriteLine("</Row>")
    Next

End Sub
Private Function Getdata(ByVal cmd As SqlCommand) As DataTable
    Dim dt As New DataTable()
    Dim strcon As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
    Dim con As New SqlConnection(strcon)
    Dim sda As New SqlDataAdapter
    cmd.CommandType = CommandType.Text
    cmd.Connection = con
    Try
        con.Open()
        sda.SelectCommand = cmd
        sda.Fill(dt)
        Return dt
    Catch ex As Exception
        Throw (ex)
    Finally
        con.Close()
        sda.Dispose()
        con.Dispose()
    End Try

End Function

End Class


问:
Import data into SQL Server using BCP utility (export the log file with the error records and continue inserting with the normal records)
I have a data set and want to import it into my database with the condition:

In case there is a record that cannot be imported, it can be extracted into a log

Although existing records can not be imported but still allow import of records that can be imported (other records) and continue to process

Currently I use the BCP utility to import data into the table from the csv file with:

```<code>bcp table_name IN C:\Users\09204086121\Desktop\data.csv  -T -c -o C:\Users\09204086121\Desktop\logOut.log -e C:\Users\09204086121\Desktop\errOut.log
</code>```

It just satisfies my condition 1 above.
I need that when the record has error (duplicate primary key,...), write to log (1) and continue to insert into the table the other normal records (2).

I came up with the idea that combining trigger with bcp, after creating a trigger and adding the parameter -h "FIRE_TRIGGERS" to the bcp statement, the insert will ignore records that have the same key but it won't write to the log.

This is my trigger.

```<code>ALTER TRIGGER [PKGORDERCOMMON].[T_ImportData] ON [PKGORDERCOMMON].[IF_R_BUNRUI1]
INSTEAD OF INSERT
AS
BEGIN
    --Insert non duplicate records
    INSERT INTO [IF_R_BUNRUI1]
    (
        SYSTEM_KB,
        BUNRUI1_CD,
        BUNRUI1_KANJI_NA,
        BUNRUI1_KANA_NA,
        CREATE_TS
    )
    SELECT SYSTEM_KB,
        BUNRUI1_CD,
        BUNRUI1_KANJI_NA,
        BUNRUI1_KANA_NA,
        CREATE_TS
    FROM inserted i
    WHERE NOT EXISTS
    (
        SELECT *
        FROM [IF_R_BUNRUI1] c
        WHERE c.BUNRUI1_CD = i.BUNRUI1_CD
        AND c.SYSTEM_KB = i.SYSTEM_KB
    );
END;
</code>```

Is there anyone who can help me.

答:
BCP is not meant for what you are asking it to do (separate good and bad records). For instance, bcp -e option has a limit to how many records it will show. Im not sure if this limit is tied to the "max errors" option, but regardless there is a limit.

Your best option is to load all the records and address bad data in t-sql.

Load all records in such a way to ignore conversion errors. Either:

load entire line from file into a single, large varchar column. Parse out columns and qc data as needed.
or

load all columns from source file into generic varchar columns with large enough size to accomodate your source data.

Either way, when done, use t-sql to inspect your data and split among good/bad records.

Body:
I have a data set and want to import it into my database with the condition:

In case there is a record that cannot be imported, it can be extracted into a log

Although existing records can not be imported but still allow import of records that can be imported (other records) and continue to process

Currently I use the BCP utility to import data into the table from the csv file with:

bcp table_name IN C:\Users\09204086121\Desktop\data.csv  -T -c -o C:\Users\09204086121\Desktop\logOut.log -e C:\Users\09204086121\Desktop\errOut.log

It just satisfies my condition 1 above.
I need that when the record has error (duplicate primary key,...), write to log (1) and continue to insert into the table the other normal records (2).

I came up with the idea that combining trigger with bcp, after creating a trigger and adding the parameter -h "FIRE_TRIGGERS" to the bcp statement, the insert will ignore records that have the same key but it won't write to the log.

This is my trigger.

ALTER TRIGGER [PKGORDERCOMMON].[T_ImportData] ON [PKGORDERCOMMON].[IF_R_BUNRUI1]
INSTEAD OF INSERT
AS
BEGIN
    --Insert non duplicate records
    INSERT INTO [IF_R_BUNRUI1]
    (
        SYSTEM_KB,
        BUNRUI1_CD,
        BUNRUI1_KANJI_NA,
        BUNRUI1_KANA_NA,
        CREATE_TS
    )
    SELECT SYSTEM_KB,
        BUNRUI1_CD,
        BUNRUI1_KANJI_NA,
        BUNRUI1_KANA_NA,
        CREATE_TS
    FROM inserted i
    WHERE NOT EXISTS
    (
        SELECT *
        FROM [IF_R_BUNRUI1] c
        WHERE c.BUNRUI1_CD = i.BUNRUI1_CD
        AND c.SYSTEM_KB = i.SYSTEM_KB
    );
END;

Is there anyone who can help me.


问:
Toad for Oracle - changing formatting on DBMS Output window
On the DBMS Output window for Toad v11, how can you change the DBMS Output font and color? I've looked all over in the options but I'm unable to find where it's possible it change this. It's way too small by default.</p>

答: For Toad 13, the DMBS Output settings are at View > Toad Options > Oracle > General.</p>


问:
Angular and Http Calls How to make an http call to keep trying until a success response is received?
We are migrating data from a system into another. These data are composed of two excel files.

The first one contains the basic/general information about a family, and the other is information about each member of each family.

The script is composed of two nested loops. The first loop will take a row from the excel, add it into the database then the second loop, will search for related members (using a unique ID for each family) and add them as well.

I wrapped the HTTP calls, inside promise. When a promise is resolved, we will move to the next step, if not, an array will be added to a log file saying that this row had an issue while uploading and we will check it later.

Sometimes, when handling HTTP calls from an observable, some of the calls will be ahead of others that should not be. Let us say, that family X is being added, and there is 5 HTTP calls are running, if one of them, had a late response and the initial loop is started with uploading info of family Y, the code will break, because of the structure of the new system we are migrating to.

Long story short, while uploading data, we receive an error 500: Internal Server Error without knowing the reason. But we expected that this is going to happen, so we did the following at our api.services.ts service:

```<code>postData(data) {
    const url = 'apiUrlForDataUpload';
    return this.http.post(url, data, this.httpOptions).pipe(retry(3), timeout(15000));
}
</code>```

I am specifying that in case the server returned an error, to repeat the call at least 3 times, and in case there was a network interruption, to wait for 15 seconds before skipping into next part.

The problem is that whenever the server returns an error, the loop will be broke and the call would not retry the process for 1 time at least not 3.

How to make an HTTP call to keep trying until a success response is received?

答:
```<code>postData(data) {
  const url = `apiUrlForDataUpload`
  return this.http.post(url, data, this.httpOptions).pipe(retryWhen(err => err.pipe(delay(timer(2000)))));
)
</code>```

Body:
We are migrating data from a system into another. These data are composed of two excel files.

The first one contains the basic/general information about a family, and the other is information about each member of each family.

The script is composed of two nested loops. The first loop will take a row from the excel, add it into the database then the second loop, will search for related members (using a unique ID for each family) and add them as well.

I wrapped the HTTP calls, inside promise. When a promise is resolved, we will move to the next step, if not, an array will be added to a log file saying that this row had an issue while uploading and we will check it later.

Sometimes, when handling HTTP calls from an observable, some of the calls will be ahead of others that should not be. Let us say, that family X is being added, and there is 5 HTTP calls are running, if one of them, had a late response and the initial loop is started with uploading info of family Y, the code will break, because of the structure of the new system we are migrating to.

Long story short, while uploading data, we receive an error 500: Internal Server Error without knowing the reason. But we expected that this is going to happen, so we did the following at our api.services.ts service:

postData(data) {
    const url = 'apiUrlForDataUpload';
    return this.http.post(url, data, this.httpOptions).pipe(retry(3), timeout(15000));
}

I am specifying that in case the server returned an error, to repeat the call at least 3 times, and in case there was a network interruption, to wait for 15 seconds before skipping into next part.

The problem is that whenever the server returns an error, the loop will be broke and the call would not retry the process for 1 time at least not 3.

How to make an HTTP call to keep trying until a success response is received?


问:
Output numbers with comma separation in snowflake result set
I'm working with SQL queries in Snowflake. I'm able to work in the browser (chrome), or on SQLWorkbenchJ. I'm comfortable with both, but prefer the browser.

I'm working with moderately large numbers, up into the hundreds of billions (10^8), so it would be really helpful if I could make the program print my numbers with comma separation for the thousands (e.g. 12,345,678,901.00 vs. 12345678901.00).

I've looked into the documentation on format models here
https://docs.snowflake.com/en/sql-reference/sql-format-models.html
but I can't see an option to output in the style I'm looking for.

Even besides that, I would really prefer to implement this as a configuration to the interface itself rather than code I apply to my queries.

答:
The numeric format models appears to cover your need via the 9 (nine), , (comma), and . (period) symbols. Quoting some relevant portions from the documentation:

9: Position for a digit; leading/trailing zeros are replaced with blank spaces.

0: Position for a digit; leading/trailing zeros are explicitly printed.

. (period): Decimal fraction separator; always printed as a period.

, (comma): Digit group separator; printed as a comma or blank space.

[...]

The digit group separator , (comma) or G results in the corresponding group separator character being printed if the number is big enough so the digits are on the both sides of group separator. An example of a format model useful for printing currency sums would be 999,999.00.

Here are some adapted examples demonstrating the required format:

SELECT to_varchar(123.21, '999,999,999,999.00');
SELECT to_varchar(12345.00, '999,999,999,999.00');
SELECT to_varchar(12345678, '999,999,999,999.00');
SELECT to_varchar(12345678901, '999,999,999,999.00');
SELECT to_varchar(12345678901.59, '999,999,999,999.00');
```


问:
Query statement works in SQL Server but it's not working in C#
I have this query.

```<code>SELECT DISTINCT
    nome as Nome, Cognome As Cognome, DataConsegna as Data,
    'RichiesteAttrezzatura' [Tipo Richiesta]
FROM
    RichiesteAttrezzatura

UNION

SELECT DISTINCT
    nome AS Nome, Cognome as Cognome, DataConsegna as Data,
    'RichiesteMateriali' [Tipo Richiesta]
FROM
    RichiesteMateriali

UNION

SELECT DISTINCT
    nome AS Nome, Cognome as Cognome, giorno AS Data,
    'RichiesteVestiario' [Tipo Richiesta]
FROM
    RichiesteVestiario
</code>```

As in the title the problem is that it works in SQL Server when I execute it, but when I try in C# it just returns an empty table.

From what I understood looking on the net and in this forum the problem comes with the single quotes.

This is my function

```<code>adonet db;

DataProvider()
{
    db = new adonet();
    db.CreaConnessione();
}

public DataTable getRichiesteForDGV(int id)
{
    DataTable dt = new DataTable();

    string richiesteVestiario = "RichiesteVestiario";
    string richiesteMateriali = "RichiesteMateriali";
    string richiesteAttrezzatura = "RichiesteAttrezzatura";

    string sql = "SELECT DISTINCT nome as Nome, Cognome As Cognome, DataConsegna as Data, '" + richiesteAttrezzatura + "' [Tipo Richiesta] FROM RichiesteAttrezzatura " +
                "UNION SELECT DISTINCT nome AS Nome, Cognome as Cognome, DataConsegna as Data, '" + richiesteMateriali + "' [Tipo Richiesta] FROM RichiesteMateriali " +
                "UNION SELECT DISTINCT nome AS Nome, Cognome as Cognome, giorno AS Data, '" + richiesteVestiario + "' [Tipo Richiesta] FROM RichiesteVestiario";

    db.EseguiQueryWithParams(sql, new SqlParameter("@id", id));

    return dt;
}

public DataTable EseguiQueryWithParams(string sql, params SqlParameter[] parameters)
    {
        ApriConnessione();
        string nometab = "dump";
        cmd.CommandText = sql;

        cmd.CommandType = System.Data.CommandType.Text;
        cmd.Parameters.AddRange(parameters);
        try
        {
            adp = new SqlDataAdapter(cmd);
            if (dset.Tables[nometab] != null)
                dset.Tables[nometab].Clear();

            adp.Fill(dset, nometab);
            DataTable d = dset.Tables[nometab];
            //d.Columns;
            return d;
        }
        catch (Exception e)
        {
            throw e;
            //throw new System.Exception("Errore nella lettura della tabella");

        }
        finally
        {
            adp.Dispose();
            cmd.Dispose();
            ChiudiConnessione();
        }

    }
</code>```

Any help is appreciated. I've updated the post, since I understood that the problem comes with my C#

答:
First up, I'm gonna spend a minute suggesting some changes to EseguiQueryWithParams() that are too large to fit as a comment.

For starters, the way it handles the connection is not good practice. Read the link until you understand it. Instead, you want a common method or property that tells you the connection string, and nothing else. The other changes to the method should all be understandable in the code:

```<code>private DataTable EseguiQuery(string sql, params SqlParameter[] parameters)
{
    // The method should only supply the string.
    // DO NOT TRY TO REUSE THE SAME CONNECTION OBJECT!
    string connectionString = Connessione();
    var dset = new DataSet(); //creating a new dataset for this query also means there's no need to check and clear it further down

    //Using blocks guarantee the items are disposed, even if an exception is thrown
    using (var connection = new SqlConnection(connectionString))
    using (var cmd = new SqlCommand(sql, connection))
    using (var adp = new SqlDataAdapater(cmd))
    {
        cmd.CommandText = sql; //cmd.CommandType already has "Text" as the default value
        if (parameters is object && parameters.Length > 0)
        {   //conditional test lets us skip the parameters argument when calling the method
            cmd.Parameters.AddRange(parameters);
        }
        adp.Fill(dset); // Fill() opens and closes the connection automatically
        return dset.Tables[0];
    }
} // No try/catch anywhere, because the old try/catch didn't do anything
</code>```

See how much less code that it is (17 lines vs 31, after cleaning up comments), but it accomplishes all the same things. Even opening and closing the connection is covered, even it's less obvious where. Additionally, it allows you to skip passing parameters, like this:

```<code>var result = db.EseguiQuery(sql)
</code>```

One other important change is making this method private. Do the same for other Execute/Esegui methods. The getRichiesteForDGV(), as well as other methods which talk to the DB, should then also be (public) members of the type represented by the db variable. In this way, you obtain good separation of concerns between your data access and your user interface. There's no way in your application to access the database without use a public member of the that type.

Finally, I removed the WithParams part of the name, because I believe it's poor practice to provide an overload that doesn't expect them. Parameters are so important, you want the message loud and clear to developers they're expected to use them. And since the table name isn't really important and you can called stored procedures with the default Text CommandType by prefacing them the exec, this can completely replace the old ExecuteQuery() method.

With all that out of the way, we can finally address the actual question. EseguiQueryWithParams() returns a DataTable, but this return value is ignored. It's never connected with the dt variable declared and returned in getRichiesteForDGV(). Fix it, along with my suggested changes, like this:

```<code>public DataTable getRichiesteForDGV(int id)
{
    string richiesteVestiario = "RichiesteVestiario";
    string richiesteMateriali = "RichiesteMateriali";
    string richiesteAttrezzatura = "RichiesteAttrezzatura";

    string sql = $"SELECT DISTINCT nome as Nome, Cognome As Cognome, DataConsegna as Data, '{richiesteAttrezzatura}' [Tipo Richiesta] FROM RichiesteAttrezzatura " +
                "UNION SELECT DISTINCT nome AS Nome, Cognome as Cognome, DataConsegna as Data, '{richiesteMateriali}' [Tipo Richiesta] FROM RichiesteMateriali " +
                "UNION SELECT DISTINCT nome AS Nome, Cognome as Cognome, giorno AS Data, '{richiesteVestiario}' [Tipo Richiesta] FROM RichiesteVestiario";

    // this should be a member of the `db` class now, so "db." is implied
    return EseguiQuery(sql);
}
</code>```

Body:
I have this query.

SELECT DISTINCT
    nome as Nome, Cognome As Cognome, DataConsegna as Data,
    'RichiesteAttrezzatura' [Tipo Richiesta]
FROM
    RichiesteAttrezzatura

UNION

SELECT DISTINCT
    nome AS Nome, Cognome as Cognome, DataConsegna as Data,
    'RichiesteMateriali' [Tipo Richiesta]
FROM
    RichiesteMateriali

UNION

SELECT DISTINCT
    nome AS Nome, Cognome as Cognome, giorno AS Data,
    'RichiesteVestiario' [Tipo Richiesta]
FROM
    RichiesteVestiario

As in the title the problem is that it works in SQL Server when I execute it, but when I try in C# it just returns an empty table.

From what I understood looking on the net and in this forum the problem comes with the single quotes.

This is my function

adonet db;

DataProvider()
{
    db = new adonet();
    db.CreaConnessione();
}

public DataTable getRichiesteForDGV(int id)
{
    DataTable dt = new DataTable();

    string richiesteVestiario = "RichiesteVestiario";
    string richiesteMateriali = "RichiesteMateriali";
    string richiesteAttrezzatura = "RichiesteAttrezzatura";

    string sql = "SELECT DISTINCT nome as Nome, Cognome As Cognome, DataConsegna as Data, '" + richiesteAttrezzatura + "' [Tipo Richiesta] FROM RichiesteAttrezzatura " +
                "UNION SELECT DISTINCT nome AS Nome, Cognome as Cognome, DataConsegna as Data, '" + richiesteMateriali + "' [Tipo Richiesta] FROM RichiesteMateriali " +
                "UNION SELECT DISTINCT nome AS Nome, Cognome as Cognome, giorno AS Data, '" + richiesteVestiario + "' [Tipo Richiesta] FROM RichiesteVestiario";

    db.EseguiQueryWithParams(sql, new SqlParameter("@id", id));

    return dt;
}

public DataTable EseguiQueryWithParams(string sql, params SqlParameter[] parameters)
    {
        ApriConnessione();
        string nometab = "dump";
        cmd.CommandText = sql;

        cmd.CommandType = System.Data.CommandType.Text;
        cmd.Parameters.AddRange(parameters);
        try
        {
            adp = new SqlDataAdapter(cmd);
            if (dset.Tables[nometab] != null)
                dset.Tables[nometab].Clear();

            adp.Fill(dset, nometab);
            DataTable d = dset.Tables[nometab];
            //d.Columns;
            return d;
        }
        catch (Exception e)
        {
            throw e;
            //throw new System.Exception("Errore nella lettura della tabella");

        }
        finally
        {
            adp.Dispose();
            cmd.Dispose();
            ChiudiConnessione();
        }

    }

Any help is appreciated. I've updated the post, since I understood that the problem comes with my C#

Body:
First up, I'm gonna spend a minute suggesting some changes to EseguiQueryWithParams() that are too large to fit as a comment.

For starters, the way it handles the connection is not good practice. Read the link until you understand it. Instead, you want a common method or property that tells you the connection string, and nothing else. The other changes to the method should all be understandable in the code:

private DataTable EseguiQuery(string sql, params SqlParameter[] parameters)
{
    // The method should only supply the string.
    // DO NOT TRY TO REUSE THE SAME CONNECTION OBJECT!
    string connectionString = Connessione();
    var dset = new DataSet(); //creating a new dataset for this query also means there's no need to check and clear it further down

    //Using blocks guarantee the items are disposed, even if an exception is thrown
    using (var connection = new SqlConnection(connectionString))
    using (var cmd = new SqlCommand(sql, connection))
    using (var adp = new SqlDataAdapater(cmd))
    {
        cmd.CommandText = sql; //cmd.CommandType already has "Text" as the default value
        if (parameters is object && parameters.Length > 0)
        {   //conditional test lets us skip the parameters argument when calling the method
            cmd.Parameters.AddRange(parameters);
        }
        adp.Fill(dset); // Fill() opens and closes the connection automatically
        return dset.Tables[0];
    }
} // No try/catch anywhere, because the old try/catch didn't do anything

See how much less code that it is (17 lines vs 31, after cleaning up comments), but it accomplishes all the same things. Even opening and closing the connection is covered, even it's less obvious where. Additionally, it allows you to skip passing parameters, like this:

var result = db.EseguiQuery(sql)

One other important change is making this method private. Do the same for other Execute/Esegui methods. The getRichiesteForDGV(), as well as other methods which talk to the DB, should then also be (public) members of the type represented by the db variable. In this way, you obtain good separation of concerns between your data access and your user interface. There's no way in your application to access the database without use a public member of the that type.

Finally, I removed the WithParams part of the name, because I believe it's poor practice to provide an overload that doesn't expect them. Parameters are so important, you want the message loud and clear to developers they're expected to use them. And since the table name isn't really important and you can called stored procedures with the default Text CommandType by prefacing them the exec, this can completely replace the old ExecuteQuery() method.

With all that out of the way, we can finally address the actual question. EseguiQueryWithParams() returns a DataTable, but this return value is ignored. It's never connected with the dt variable declared and returned in getRichiesteForDGV(). Fix it, along with my suggested changes, like this:

public DataTable getRichiesteForDGV(int id)
{
    string richiesteVestiario = "RichiesteVestiario";
    string richiesteMateriali = "RichiesteMateriali";
    string richiesteAttrezzatura = "RichiesteAttrezzatura";

    string sql = $"SELECT DISTINCT nome as Nome, Cognome As Cognome, DataConsegna as Data, '{richiesteAttrezzatura}' [Tipo Richiesta] FROM RichiesteAttrezzatura " +
                "UNION SELECT DISTINCT nome AS Nome, Cognome as Cognome, DataConsegna as Data, '{richiesteMateriali}' [Tipo Richiesta] FROM RichiesteMateriali " +
                "UNION SELECT DISTINCT nome AS Nome, Cognome as Cognome, giorno AS Data, '{richiesteVestiario}' [Tipo Richiesta] FROM RichiesteVestiario";

    // this should be a member of the `db` class now, so "db." is implied
    return EseguiQuery(sql);
}


问:
How to loop over an Array with JavaScript?
I have a string that has data separated by a pipe character (|).

Example

```<code>var somestring = "data1|data2|data3";
var separated = somestring.split("|");
</code>```

I know how to use the split() to separate each data.

However, I don't know how many pipes there will be in the resulting Array.

In jQuery or JavaScript, how do I loop over the array returned?

答:
str.split() returns an array of values, so in your example, since 'separated' is an array,  you could:</p>

```<code>for (var i=0, len=separated.length; i < len; i++) {
   // do something with separated[i]
}
</code>```

Body:
I have a string that has data separated by a pipe character (|).

Example

var somestring = "data1|data2|data3";
var separated = somestring.split("|");

I know how to use the split() to separate each data.

However, I don't know how many pipes there will be in the resulting Array.

In jQuery or JavaScript, how do I loop over the array returned?

Body:
str.split() returns an array of values, so in your example, since 'separated' is an array,  you could:

for (var i=0, len=separated.length; i < len; i++) {
   // do something with separated[i]
}


问:
Implementing Domain Event Handler pattern in C# with Simple Injector
I am trying to implement the Domain Event pattern in C# using Simple Injector.

I have simplified my code to be in one file that can be ran as a console app and have excluded the Simple Injector code to keep things clear for the purpose of this question.

The problem I am coming up against is that each event could have multiple event handlers and multiple events could be raised but I want to restrict my Dispatcher to only handle events that implement the IEvent interface so I put that restraint on my Dispatch method.

This caused problems as to how to get the instance from Simple Injector as each time the Dispatch method is called TEvent is of type IEvent (as I would expect) but I need to get the type of the event passed in so I can get the relevant handlers from Simple Injector.

Hopefully my code will explain this a little bit better:

```<code>interface IEvent
{
}

interface IEventHandler<T> where T : IEvent
{
    void Handle(T @event);
}

class StandardEvent : IEvent
{
}

class AnotherEvent : IEvent
{
}

class StandardEventHandler : IEventHandler<StandardEvent>
{
    public void Handle(StandardEvent @event)
    {
        Console.WriteLine("StandardEvent handled");
    }
}

class AnotherEventHandler : IEventHandler<AnotherEvent>
{
    public void Handle(AnotherEvent @event)
    {
        Console.WriteLine("AnotherEvent handled");
    }
}
</code>```

Here's my dispatcher:

```<code>static class Dispatcher
{
    // I need to get the type of @event here so I can get the registered instance from the
    // IoC container (SimpleInjector), however TEvent is of type IEvent (as expected).
    // What I need to do here is Get the registered instance from Simple Injector for each
    // Event Type i.e. Container.GetAllInstances<IEventHandler<StandardEvent>>()
    // and Container.GetAllInstances<IEventHandler<AnotherEvent>>()
    public static void Dispatch<TEvent>(TEvent @event) where TEvent : IEvent
    {
    }
}

class PlainOldObject
{
    public ICollection<IEvent> Events = new List<IEvent>
    {
        new StandardEvent(),
        new AnotherEvent()
    };
}

class StandAlone
{
    static void Main(string[] args)
    {
        var poco = new PlainOldObject();
        foreach (var @event in poco.Events)
        {
            Dispatcher.Dispatch(@event);
        }
    }
}
</code>```

I have commented in the Dispatch method what my issue is. Does anyone have any idea on how I should resolve this?

Regards,
Gary

答:
Here is a faster version using cached delegates. No dynamic and no reflection after the first call. Uses IServiceProvider from the default Microsoft DI but that can be easily changed. Expression trees can be used too, but they take more memory:

```<code>public class EventDispatcherService : IEventDispatcher
{
    private static readonly ConcurrentDictionary<Type, IEnumerable<Func<object, Task>>> HandlersCache
        = new ConcurrentDictionary<Type, IEnumerable<Func<object, Task>>>();

    private static readonly Type HandlerType = typeof(IEventHandler<>);

    private static readonly MethodInfo MakeDelegateMethod = typeof(EventDispatcherService)
        .GetMethod(nameof(MakeDelegate), BindingFlags.Static | BindingFlags.NonPublic);

    private static readonly Type OpenGenericFuncType = typeof(Func<,>);

    private static readonly Type TaskType = typeof(Task);

    private readonly IServiceProvider serviceProvider;

    public EventDispatcherService(IServiceProvider serviceProvider)
        => this.serviceProvider = serviceProvider;

    public async Task Dispatch(IDomainEvent domainEvent)
    {
        var eventHandlers = HandlersCache.GetOrAdd(domainEvent.GetType(), eventType =>
        {
            var eventHandlerType = HandlerType.MakeGenericType(eventType);

            var makeDelegate = MakeDelegateMethod.MakeGenericMethod(eventType);

            var funcType = OpenGenericFuncType.MakeGenericType(eventType, TaskType);

            return this.serviceProvider
                .GetServices(eventHandlerType)
                .Select(handler => handler
                    .GetType()
                    .GetMethod("Handle")
                    .CreateDelegate(funcType, handler))
                .Select(handlerDelegateConcrete => (Func<object, Task>)makeDelegate
                    .Invoke(null, new object[] { handlerDelegateConcrete }))
                .ToList();
        });

        foreach (var eventHandler in eventHandlers)
        {
            await eventHandler(domainEvent);
        }
    }

    private static Func<object, Task> MakeDelegate<T>(Func<T, Task> action)
        => value => action((T)value);
}
</code>```

And this is the event handler interface:

```<code>public interface IEventHandler<in TEvent>
    where TEvent : IDomainEvent
{
    Task Handle(TEvent domainEvent);
}
</code>```

Body:
I am trying to implement the Domain Event pattern in C# using Simple Injector.

I have simplified my code to be in one file that can be ran as a console app and have excluded the Simple Injector code to keep things clear for the purpose of this question.

The problem I am coming up against is that each event could have multiple event handlers and multiple events could be raised but I want to restrict my Dispatcher to only handle events that implement the IEvent interface so I put that restraint on my Dispatch method.

This caused problems as to how to get the instance from Simple Injector as each time the Dispatch method is called TEvent is of type IEvent (as I would expect) but I need to get the type of the event passed in so I can get the relevant handlers from Simple Injector.

Hopefully my code will explain this a little bit better:

interface IEvent
{
}

interface IEventHandler<T> where T : IEvent
{
    void Handle(T @event);
}

class StandardEvent : IEvent
{
}

class AnotherEvent : IEvent
{
}

class StandardEventHandler : IEventHandler<StandardEvent>
{
    public void Handle(StandardEvent @event)
    {
        Console.WriteLine("StandardEvent handled");
    }
}

class AnotherEventHandler : IEventHandler<AnotherEvent>
{
    public void Handle(AnotherEvent @event)
    {
        Console.WriteLine("AnotherEvent handled");
    }
}

Here's my dispatcher:

static class Dispatcher
{
    // I need to get the type of @event here so I can get the registered instance from the
    // IoC container (SimpleInjector), however TEvent is of type IEvent (as expected).
    // What I need to do here is Get the registered instance from Simple Injector for each
    // Event Type i.e. Container.GetAllInstances<IEventHandler<StandardEvent>>()
    // and Container.GetAllInstances<IEventHandler<AnotherEvent>>()
    public static void Dispatch<TEvent>(TEvent @event) where TEvent : IEvent
    {
    }
}

class PlainOldObject
{
    public ICollection<IEvent> Events = new List<IEvent>
    {
        new StandardEvent(),
        new AnotherEvent()
    };
}

class StandAlone
{
    static void Main(string[] args)
    {
        var poco = new PlainOldObject();
        foreach (var @event in poco.Events)
        {
            Dispatcher.Dispatch(@event);
        }
    }
}

I have commented in the Dispatch method what my issue is. Does anyone have any idea on how I should resolve this?

Regards,
Gary

Body:
Here is a faster version using cached delegates. No dynamic and no reflection after the first call. Uses IServiceProvider from the default Microsoft DI but that can be easily changed. Expression trees can be used too, but they take more memory:

public class EventDispatcherService : IEventDispatcher
{
    private static readonly ConcurrentDictionary<Type, IEnumerable<Func<object, Task>>> HandlersCache
        = new ConcurrentDictionary<Type, IEnumerable<Func<object, Task>>>();

    private static readonly Type HandlerType = typeof(IEventHandler<>);

    private static readonly MethodInfo MakeDelegateMethod = typeof(EventDispatcherService)
        .GetMethod(nameof(MakeDelegate), BindingFlags.Static | BindingFlags.NonPublic);

    private static readonly Type OpenGenericFuncType = typeof(Func<,>);

    private static readonly Type TaskType = typeof(Task);

    private readonly IServiceProvider serviceProvider;

    public EventDispatcherService(IServiceProvider serviceProvider)
        => this.serviceProvider = serviceProvider;

    public async Task Dispatch(IDomainEvent domainEvent)
    {
        var eventHandlers = HandlersCache.GetOrAdd(domainEvent.GetType(), eventType =>
        {
            var eventHandlerType = HandlerType.MakeGenericType(eventType);

            var makeDelegate = MakeDelegateMethod.MakeGenericMethod(eventType);

            var funcType = OpenGenericFuncType.MakeGenericType(eventType, TaskType);

            return this.serviceProvider
                .GetServices(eventHandlerType)
                .Select(handler => handler
                    .GetType()
                    .GetMethod("Handle")
                    .CreateDelegate(funcType, handler))
                .Select(handlerDelegateConcrete => (Func<object, Task>)makeDelegate
                    .Invoke(null, new object[] { handlerDelegateConcrete }))
                .ToList();
        });

        foreach (var eventHandler in eventHandlers)
        {
            await eventHandler(domainEvent);
        }
    }

    private static Func<object, Task> MakeDelegate<T>(Func<T, Task> action)
        => value => action((T)value);
}

And this is the event handler interface:

public interface IEventHandler<in TEvent>
    where TEvent : IDomainEvent
{
    Task Handle(TEvent domainEvent);
}


问:
Sharer.js and angular 8 doesn't work - social sharing
I am working on an angular 8 project and i had to implement the social sharing. I am trying to use sharer.js (https://ellisonleao.github.io/sharer.js/) but it seems that something is not going well.

I had imported sharer through CDN and placed in the index.html.

In the component, on the ngAfterInit() i had tried this:

```<code>ngAfterViewInit() {
    setTimeout(() => {
      ;(window as any).Sharer.init()
   })
}
</code>```

and also

```<code>ngAfterViewInit() {
    this.ngZone.run(() => {
      setTimeout(() => {
        ;(window as any).Sharer.init()
      })
    })
  }
</code>```

and the html is:

```<code><span data-sharer="twitter" data-title="testingggg" data-url="https://ellisonleao.github.io/sharer.js/">TEST</span>
</code>```

After clicking on the HTML element nothing happens. Any suggestion?

Reading the documentation it seems that you only need to initialize the module but it doesn't work.
the same thing was done in an angular 9 project and it works. the steps are the same

答:
This approach works in Angular 9 and I would expect it to also work in Angular 8.

1.Install sharer

```<code>npm install sharer.js
</code>```

2.Add to /angular.json

```<code>"scripts": [
    "node_modules/sharer.js/sharer.min.js"
]
</code>```

3.Add sharer to your .ts

```<code>import { Component, OnInit } from '@angular/core';

 // Declare external javascript library symbol.
declare var Sharer: any;

@Component({
    selector: 'sharer',
    templateUrl: './sharer.component.html',
    styleUrls: ['./sharer.component.scss'],
})
export class SharerComponent implements OnInit {

    ngOnInit() {
        // Initialize sharer javascript library.
        Sharer.init();
    }
}
</code>```

4.Add sharer to your .html e.g.

```<code><button class="button" data-sharer="twitter" data-title="Checkout Sharer.js!" data-hashtags="awesome, sharer.js" data-url="https://ellisonleao.github.io/sharer.js/">Share on Twitter</button>
</code>```

Body:
I am working on an angular 8 project and i had to implement the social sharing. I am trying to use sharer.js (https://ellisonleao.github.io/sharer.js/) but it seems that something is not going well.

I had imported sharer through CDN and placed in the index.html.

In the component, on the ngAfterInit() i had tried this:

ngAfterViewInit() {
    setTimeout(() => {
      ;(window as any).Sharer.init()
   })
}

and also

ngAfterViewInit() {
    this.ngZone.run(() => {
      setTimeout(() => {
        ;(window as any).Sharer.init()
      })
    })
  }

and the html is:

TEST

After clicking on the HTML element nothing happens. Any suggestion?

Reading the documentation it seems that you only need to initialize the module but it doesn't work.
the same thing was done in an angular 9 project and it works. the steps are the same

Body:
This approach works in Angular 9 and I would expect it to also work in Angular 8.

1.Install sharer

npm install sharer.js

2.Add to /angular.json

"scripts": [
    "node_modules/sharer.js/sharer.min.js"
]

3.Add sharer to your .ts

import { Component, OnInit } from '@angular/core';

 // Declare external javascript library symbol.
declare var Sharer: any;

@Component({
    selector: 'sharer',
    templateUrl: './sharer.component.html',
    styleUrls: ['./sharer.component.scss'],
})
export class SharerComponent implements OnInit {

    ngOnInit() {
        // Initialize sharer javascript library.
        Sharer.init();
    }
}

4.Add sharer to your .html e.g.

<button class="button" data-sharer="twitter" data-title="Checkout Sharer.js!" data-hashtags="awesome, sharer.js" data-url="https://ellisonleao.github.io/sharer.js/">Share on Twitter</button>


问:
Laravel Email Verification link not working?
I have set up an email verification to my Laravel APP, however when I register as a user and go to mailtrap.io and when I click on "Verify Email Address" button I get 403 This action is unauthorized, HOWEVER if I click on resend verification mail and then click on the button it works fine.

Here are my web routes:

```<code>Auth::routes(['verify' => true]);
Route::get('/home', 'HomeController@index')->name('home');
Route::get('/', 'HomeController@index')->name('home');

Route::resource('challenge', 'ChallengesController');
Route::post('/challenge/join/{id}', 'ChallengesController@joinChallenge')->name('challenge.join');
Route::delete('/challenge/finish/{id}', 'ChallengesController@finishChallenge')->name('challenge.finish');
</code>```

In my User model, I have implemented MustVerifyEmail

```<code><?php

namespace App;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;

class User extends Authenticatable implements MustVerifyEmail
{
    use Notifiable;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'user_id', 'name', 'username', 'email', 'password',
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password', 'remember_token',
    ];

    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
    ];

    protected $primaryKey = 'user_id';
    protected $keyType = 'string';

}
</code>```

UPDATE
Here is my HomeController:

```<code><?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\User;

class HomeController extends Controller
{
    /**
     * Create a new controller instance.
     *
     * @return void
     */
    public function __construct()
    {
        $this->middleware(['verified', 'auth']);
    }

    /**
     * Show the application dashboard.
     *
     * @return \Illuminate\Contracts\Support\Renderable
     */
    public function index()
    {
        $user_id = auth()->id();
        $user = User::find($user_id);

        return view('home')
            ->with('challenges', $user->challenges)
            ->with('userChallenges', $user->userChallenges);
    }
}
</code>```

RegisterController:

```<code><?php

namespace App\Http\Controllers\Auth;

use App\Http\Controllers\Controller;
use App\Providers\RouteServiceProvider;
use App\User;
use Illuminate\Foundation\Auth\RegistersUsers;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Facades\Validator;

class RegisterController extends Controller
{
    /*
    |--------------------------------------------------------------------------
    | Register Controller
    |--------------------------------------------------------------------------
    |
    | This controller handles the registration of new users as well as their
    | validation and creation. By default this controller uses a trait to
    | provide this functionality without requiring any additional code.
    |
    */

    use RegistersUsers;

    /**
     * Where to redirect users after registration.
     *
     * @var string
     */
    protected $redirectTo = RouteServiceProvider::HOME;

    /**
     * Create a new controller instance.
     *
     * @return void
     */
    public function __construct()
    {
        $this->middleware('guest');
    }

    /**
     * Get a validator for an incoming registration request.
     *
     * @param  array  $data
     * @return \Illuminate\Contracts\Validation\Validator
     */
    protected function validator(array $data)
    {
        return Validator::make($data, [
            'username' => ['required', 'string', 'max:45', 'unique:users'],
            'name' => ['required', 'string', 'max:255'],
            'email' => ['required', 'string', 'email', 'max:255', 'unique:users'],
            'password' => ['required', 'string', 'min:8', 'confirmed'],
        ]);
    }

    /**
     * Create a new user instance after a valid registration.
     *
     * @param  array  $data
     * @return \App\User
     */
    protected function create(array $data)
    {
        return User::create([
            'user_id' => uniqid(),
            'username' => $data['username'],
            'name' => $data['name'],
            'email' => $data['email'],
            'password' => Hash::make($data['password']),
        ]);
    }
}
</code>```

答:
Make sure you don't have your authentication paths inside the auth middleware</p>

```<code>Auth::routes(['verify' => true]);
Route::group(['middleware' => 'auth'], function () {

});
</code>```

Body:
I have set up an email verification to my Laravel APP, however when I register as a user and go to mailtrap.io and when I click on "Verify Email Address" button I get 403 This action is unauthorized, HOWEVER if I click on resend verification mail and then click on the button it works fine.

Here are my web routes:

Auth::routes(['verify' => true]);
Route::get('/home', 'HomeController@index')->name('home');
Route::get('/', 'HomeController@index')->name('home');

Route::resource('challenge', 'ChallengesController');
Route::post('/challenge/join/{id}', 'ChallengesController@joinChallenge')->name('challenge.join');
Route::delete('/challenge/finish/{id}', 'ChallengesController@finishChallenge')->name('challenge.finish');

In my User model, I have implemented MustVerifyEmail

<?php

namespace App;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;

class User extends Authenticatable implements MustVerifyEmail
{
    use Notifiable;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'user_id', 'name', 'username', 'email', 'password',
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password', 'remember_token',
    ];

    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
    ];

    protected $primaryKey = 'user_id';
    protected $keyType = 'string';

}

UPDATE
Here is my HomeController:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\User;

class HomeController extends Controller
{
    /**
     * Create a new controller instance.
     *
     * @return void
     */
    public function __construct()
    {
        $this->middleware(['verified', 'auth']);
    }

    /**
     * Show the application dashboard.
     *
     * @return \Illuminate\Contracts\Support\Renderable
     */
    public function index()
    {
        $user_id = auth()->id();
        $user = User::find($user_id);

        return view('home')
            ->with('challenges', $user->challenges)
            ->with('userChallenges', $user->userChallenges);
    }
}

RegisterController:

<?php

namespace App\Http\Controllers\Auth;

use App\Http\Controllers\Controller;
use App\Providers\RouteServiceProvider;
use App\User;
use Illuminate\Foundation\Auth\RegistersUsers;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Facades\Validator;

class RegisterController extends Controller
{
    /*
    |--------------------------------------------------------------------------
    | Register Controller
    |--------------------------------------------------------------------------
    |
    | This controller handles the registration of new users as well as their
    | validation and creation. By default this controller uses a trait to
    | provide this functionality without requiring any additional code.
    |
    */

    use RegistersUsers;

    /**
     * Where to redirect users after registration.
     *
     * @var string
     */
    protected $redirectTo = RouteServiceProvider::HOME;

    /**
     * Create a new controller instance.
     *
     * @return void
     */
    public function __construct()
    {
        $this->middleware('guest');
    }

    /**
     * Get a validator for an incoming registration request.
     *
     * @param  array  $data
     * @return \Illuminate\Contracts\Validation\Validator
     */
    protected function validator(array $data)
    {
        return Validator::make($data, [
            'username' => ['required', 'string', 'max:45', 'unique:users'],
            'name' => ['required', 'string', 'max:255'],
            'email' => ['required', 'string', 'email', 'max:255', 'unique:users'],
            'password' => ['required', 'string', 'min:8', 'confirmed'],
        ]);
    }

    /**
     * Create a new user instance after a valid registration.
     *
     * @param  array  $data
     * @return \App\User
     */
    protected function create(array $data)
    {
        return User::create([
            'user_id' => uniqid(),
            'username' => $data['username'],
            'name' => $data['name'],
            'email' => $data['email'],
            'password' => Hash::make($data['password']),
        ]);
    }
}

Body:
Make sure you don't have your authentication paths inside the auth middleware

Auth::routes(['verify' => true]);
Route::group(['middleware' => 'auth'], function () {

});


问:
SQL Server Management Studio -- where is Tools/Import and Export settings menu?
In Visual Studio, there is an Import and Export settings wizard which you can use to customize Visual Studio and save settings to a .settings file.

Do we have something like it in SQL Server Management Studio?

I'm constantly switching between several VM's and configuring each SSMS is a pain in the neck. I want to be able to save my settings to a file, then import it in all my VM's.

Thank you.

答:
Looks like MS has (essentially) acknowledged that, if this did exist, it's gone and there are no firm plans to bring it back...

From Microsoft Connect

Hi Doug, Thank you for your suggestion
to import/export settings for the R2
release. We will not be considering
this for R2. We have this request
already on file and we'll look at it
for a future release beyond R2 to
address this request.

Unfortunate.


问:
Unable to use "request" library with headers
Using Chrome dev tools I can see all the requests made. I want to replicate one of them using a script. The request is replicable as replaying it from dev tools works. I'm using this code:

let request = require("request");
request.put("<hidden>", {
    "headers": {
        ":authority": "<hidden>",
        ":method": "PUT",
        ":path": "/api/<hidden>",
        ":scheme": "https",
        "accept": "*/*",
        "accept-encoding": "gzip, deflate, br",
        "accept-language": "en-US",
        "authorization": "<hidden>",
        "content-length": "0",
        // and more
    }
}, (err, response, body) => {
    console.log(err);
    console.log(response.statusCode);
    console.log(body);
});
```

<p>The console says:</p>

```TypeError [ERR_INVALID_HTTP_TOKEN]: Header name must be a valid HTTP token [":authority"]
    at ClientRequest.setHeader (_http_outgoing.js:487:3)
    at new ClientRequest (_http_client.js:221:14)
    at Object.request (https.js:309:10)
    at Object.request (C:\Users\Admin\AppData\Roaming\npm\node_modules\puppeteer-core\node_modules\agent-base\patch-core.js:25:22)
    at Request.start (C:\Users\Admin\AppData\Roaming\npm\node_modules\request\request.js:751:32)
    at Request.end (C:\Users\Admin\AppData\Roaming\npm\node_modules\request\request.js:1505:10)
    at end (C:\Users\Admin\AppData\Roaming\npm\node_modules\request\request.js:564:14)
    at Immediate._onImmediate (C:\Users\Admin\AppData\Roaming\npm\node_modules\request\request.js:578:7)
    at processImmediate (internal/timers.js:439:21) {
  code: 'ERR_INVALID_HTTP_TOKEN'
}
```

I tried not using .put(), or specifying method and body properties. Same results.

答:
Headers that show with a leading colon are "pseudo-headers".  You don't actually send them yourself with the request() library as they are an illegal http 1.1 header format.

You can read about them here: Purpose of pseudo colon-header fields.

You should be able to remove all the pseudo-headers and just make sure that the information in them is properly specified elsewhere in your request (url, method, etc...) which they would normally already be.

Body:
Using Chrome dev tools I can see all the requests made. I want to replicate one of them using a script. The request is replicable as replaying it from dev tools works. I'm using this code:

let request = require("request");
request.put("<hidden>", {
    "headers": {
        ":authority": "<hidden>",
        ":method": "PUT",
        ":path": "/api/<hidden>",
        ":scheme": "https",
        "accept": "*/*",
        "accept-encoding": "gzip, deflate, br",
        "accept-language": "en-US",
        "authorization": "<hidden>",
        "content-length": "0",
        // and more
    }
}, (err, response, body) => {
    console.log(err);
    console.log(response.statusCode);
    console.log(body);
});

The console says:

TypeError [ERR_INVALID_HTTP_TOKEN]: Header name must be a valid HTTP token [":authority"]
    at ClientRequest.setHeader (_http_outgoing.js:487:3)
    at new ClientRequest (_http_client.js:221:14)
    at Object.request (https.js:309:10)
    at Object.request (C:\Users\Admin\AppData\Roaming\npm\node_modules\puppeteer-core\node_modules\agent-base\patch-core.js:25:22)
    at Request.start (C:\Users\Admin\AppData\Roaming\npm\node_modules\request\request.js:751:32)
    at Request.end (C:\Users\Admin\AppData\Roaming\npm\node_modules\request\request.js:1505:10)
    at end (C:\Users\Admin\AppData\Roaming\npm\node_modules\request\request.js:564:14)
    at Immediate._onImmediate (C:\Users\Admin\AppData\Roaming\npm\node_modules\request\request.js:578:7)
    at processImmediate (internal/timers.js:439:21) {
  code: 'ERR_INVALID_HTTP_TOKEN'
}

I tried not using .put(), or specifying method and body properties. Same results.


问:
How do I split date ranges into an array with a specific array from user input?
I have an array which i would like to get a range of data from when user inputs a range which falls into the values available.

```<code>$dat= array("2020-02-01","2020-02-05","2020-02-10","20-02-12","2020-02-15");
</code>```

when user input search range of $start="2020-01-01" and $end="2020-02-07"

i want the $dat array to produce into $currentdat == array("2020-02-01","2020-02-05");alone

Body:
I have an array which i would like to get a range of data from when user inputs a range which falls into the values available.

$dat= array("2020-02-01","2020-02-05","2020-02-10","20-02-12","2020-02-15");

when user input search range of $start="2020-01-01" and $end="2020-02-07"

i want the $dat array to produce into $currentdat == array("2020-02-01","2020-02-05");alone

Body:
<?php
$dat= array("2020-02-01","2020-02-05","2020-02-10","20-02-12","2020-02-15");

//User input
$start ="2020-01-01";
$end="2020-02-07";

//Filter out dates between start and end date
$result = array_filter($dat, function($data_item) use($start,$end) {
    return $data_item >= $start && $data_item <= $end;
});

$result would output

Array
(
    [0] => 2020-02-01
    [1] => 2020-02-05
)


问:
Error in classes - missing 1 required positional argument
I am actually learning python3 and not able to figure it why it is so

```<code>class Load():
    def __init__(self):
        print("Starting Now")
        self.player = []
    def player_Stats(self,filename):
        with open(filename) as my_names:
            names = my_names.readlines()
            for one in names:
                one.replace("\n","")
                self.player.append(one.split[":"][0])
                print(player)

print(Load.player_Stats("players.txt"))
</code>```

It is giving me an error

Traceback (most recent call last):
  File "test.py", line 56, in <module>
    print(Load.player_Stats("players.txt"))
TypeError: player_Stats() missing 1 required positional argument: 'filename'
```

and I am not getting it why.

答:
You have to define an object or make the function static.

Option 1: Define Object:

```<code>class Load():
    def __init__(self):
        print("Starting Now")
        self.player = []
    def player_Stats(self,filename):
        with open(filename) as my_names:
            names = my_names.readlines()
            for one in names:
                one.replace("\n","")
                self.player.append(one.split[":"][0])
                print(player)

load = Load()
print(load.player_Stats("players.txt"))
</code>```

Option 2: Static method:

```<code>class Load():
    def __init__(self):
        print("Starting Now")
        self.player = []
    @staticmethod
    def player_Stats(filename):
        with open(filename) as my_names:
            names = my_names.readlines()
            for one in names:
                one.replace("\n","")
                self.player.append(one.split[":"][0])
                print(player)

print(Load.player_Stats("players.txt"))
</code>```

Body:
I am actually learning python3 and not able to figure it why it is so

class Load():
    def __init__(self):
        print("Starting Now")
        self.player = []
    def player_Stats(self,filename):
        with open(filename) as my_names:
            names = my_names.readlines()
            for one in names:
                one.replace("\n","")
                self.player.append(one.split[":"][0])
                print(player)

print(Load.player_Stats("players.txt"))

It is giving me an error

Traceback (most recent call last):
  File "test.py", line 56, in <module>
    print(Load.player_Stats("players.txt"))
TypeError: player_Stats() missing 1 required positional argument: 'filename'

and I am not getting it why.

Body:
You have to define an object or make the function static.

Option 1: Define Object:

class Load():
    def __init__(self):
        print("Starting Now")
        self.player = []
    def player_Stats(self,filename):
        with open(filename) as my_names:
            names = my_names.readlines()
            for one in names:
                one.replace("\n","")
                self.player.append(one.split[":"][0])
                print(player)

load = Load()
print(load.player_Stats("players.txt"))

Option 2: Static method:

class Load():
    def __init__(self):
        print("Starting Now")
        self.player = []
    @staticmethod
    def player_Stats(filename):
        with open(filename) as my_names:
            names = my_names.readlines()
            for one in names:
                one.replace("\n","")
                self.player.append(one.split[":"][0])
                print(player)

print(Load.player_Stats("players.txt"))


问:
Page not found (404) , error in django logout function
When i click on 'logout' link in home page(html page) ,i get this error:

```<code>Page not found (404)
Request Method: GET
Request URL:    http://127.0.0.1:8000/login1/logout
Using the URLconf defined in webapp.urls, Django tried these URL patterns, in this order:

admin/
Home/ [name='Home']
login/ [name='login']
Signup/ [name='Signup']
login1/ [name='login1']
logout/ [name='logout']
The current path, login1/logout, didn't match any of these.
</code>```

i don't understand why it can't find 'logout' function in views.py file.

This is Home.html

```<code><body>
<center>
<h1  style="color:white">Welcome to my web page</h1>
<a href= 'logout' target="_blank">LogOut</a>
</center>
</body>
</code>```

This is urls.py:

```<code>from django.contrib import admin
from django.urls import path
from application import views
urlpatterns = [
     path('admin/', admin.site.urls),
     path('Home/', views.Home , name = "Home"),
     path('login/', views.login,name = 'login'),
     path('Signup/', views.Signup , name = 'Signup'),
     path('login1/',views.login1,name='login1'),
     path('logout/',views.logout,name='logout'),
 ]
</code>```

This is views.py:

```<code>from django.contrib import auth
from django.contrib.auth.models import User
from django.shortcuts import render, redirect

def login(request):
    return render(request, 'login.html')

def login1(request):
    if request.method == "POST":
        uname = request.POST['username']
        pwd = request.POST['pass']
        user = auth.authenticate(username=uname, password=pwd)
        if user is not None:
            auth.login(request, user)
            return render(request, 'Home.html')
        else:
            return render(request, 'login.html', {'error': 'invalid credential details'})
    else:
        return render(request, 'Signup.html')

def logout(request):
    auth.logout(request)  # logout is predefined
    return redirect('/login/')
</code>```

答:
Consider using url in html

Here's an example

Logout
```

Body:
When i click on 'logout' link in home page(html page) ,i get this error:

Page not found (404)
Request Method: GET
Request URL:    http://127.0.0.1:8000/login1/logout
Using the URLconf defined in webapp.urls, Django tried these URL patterns, in this order:

admin/
Home/ [name='Home']
login/ [name='login']
Signup/ [name='Signup']
login1/ [name='login1']
logout/ [name='logout']
The current path, login1/logout, didn't match any of these.

i don't understand why it can't find 'logout' function in views.py file.

This is Home.html

Welcome to my web page

LogOut

This is urls.py:

from django.contrib import admin
from django.urls import path
from application import views
urlpatterns = [
     path('admin/', admin.site.urls),
     path('Home/', views.Home , name = "Home"),
     path('login/', views.login,name = 'login'),
     path('Signup/', views.Signup , name = 'Signup'),
     path('login1/',views.login1,name='login1'),
     path('logout/',views.logout,name='logout'),
 ]

This is views.py:

from django.contrib import auth
from django.contrib.auth.models import User
from django.shortcuts import render, redirect

def login(request):
    return render(request, 'login.html')

def login1(request):
    if request.method == "POST":
        uname = request.POST['username']
        pwd = request.POST['pass']
        user = auth.authenticate(username=uname, password=pwd)
        if user is not None:
            auth.login(request, user)
            return render(request, 'Home.html')
        else:
            return render(request, 'login.html', {'error': 'invalid credential details'})
    else:
        return render(request, 'Signup.html')

def logout(request):
    auth.logout(request)  # logout is predefined
    return redirect('/login/')

Body:
Consider using url in html

Here's an example

<a href="{% url 'logout' %}">Logout</a>


答:
Try this instead:

lass Load():
    def __init__(self):
        print("Starting Now")
        self.player = []
    def player_Stats(self,filename):
        with open(filename) as my_names:
            names = my_names.readlines()
            for one in names:
                one.replace("\n","")
                self.player.append(one.split[":"][0])
                print(player)

load = Load()

print(load.player_Stats("players.txt"))
```

The problem was that when you have a class, you must create an instance of it. Then you can call it's methods.

Title: Error in classes - missing 1 required positional argument

Body:
Try this instead:

lass Load():
    def __init__(self):
        print("Starting Now")
        self.player = []
    def player_Stats(self,filename):
        with open(filename) as my_names:
            names = my_names.readlines()
            for one in names:
                one.replace("\n","")
                self.player.append(one.split[":"][0])
                print(player)

load = Load()

print(load.player_Stats("players.txt"))

The problem was that when you have a class, you must create an instance of it. Then you can call it's methods.