WPF project - db connector

https://www.guru99.com/c-sharp-access-database.html

https://www.youtube.com/watch?v=hsfLtC4Ikvw

WPF tutorial

https://docs.microsoft.com/en-us/visualstudio/get-started/csharp/tutorial-wpf?view=vs-2022

So easy to release:

• No IIS publish
• No admin permission to install the program
• UI can be easily updated through XAML file.

More tutorials:

https://docs.microsoft.com/en-us/dotnet/desktop/wpf/getting-started/wpf-walkthroughs?view=netframeworkdesktop-4.8

I choose the first one.

https://docs.microsoft.com/en-us/previous-versions/visualstudio/visual-studio-2010/bb546972(v=vs.100)

This tutorial shows TreeView and two ListViews.

Code converted to c# by: https://converter.telerik.com/

<ListView Name="listView1" 

        ItemsSource="{Binding Path=SelectedItem.SubFolders, ElementName=treeView1, Mode=OneWay}" 

        Grid.Column="1" 

        Grid.RowSpan="1" />

        <ListView Name="listView2" 

        ItemsSource="{Binding Path=SelectedItem.Files, ElementName=treeView1, Mode=OneWay}" 

        Grid.Column="1" 

        Grid.Row="1" />

visual code + ssh

https://code.visualstudio.com/docs/remote/ssh

You can then open any folder or workspace on the remote machine using File > Open... or File > Open Workspace... just as you would locally!

git bash include my scripts folder

https://superuser.com/questions/1407891/which-directory-should-custom-git-bash-scripts-be-saved-to-in-windows-so-that-th

https://www.maketecheasier.com/make-scripts-executable-everywhere-linux/

Combination of above two links.

I googled:

bash make my script runnable everywhere

git bash include my scripts folder

Now it works ...

dense_rank()

Good url about dense_rank()

https://www.sqlservertutorial.net/sql-server-window-functions/sql-server-dense_rank-function/#:~:text=The%20DENSE_RANK()%20is%20a,they%20have%20the%20same%20values.

-- In addition to dense_rank(), apply row_number for each row

 select *, row_number() over (partition by category_id order by category_id) row_num from (

select category_id, product_id, product_name, list_price, dense_rank() over (partition by category_id order by list_price desc) price_rank from production.products

) t where price_rank < 3;

-- Top 1 record for each category by price

select * from (

select *, row_number() over (partition by category_id order by category_id) row_num from (

select category_id, product_id, product_name, list_price, dense_rank() over (partition by category_id order by list_price desc) price_rank from production.products

) t where price_rank < 3

) t2 where row_num in (1);

DevOPs pipeline

 Very confusing ...

Hi winhost,

Do you support DevOPs pipeline?

I am trying to replicate my table structure and data into one of my database in winhost.

MS document seems saying that I need to run windows shell script on the server.

https://docs.microsoft.com/en-us/azure/devops/pipelines/release/deployment-groups/?view=azure-devops

Use deployment groups in a release pipeline - Azure Pipelines In this article. Azure DevOps Services | Azure DevOps Server 2020 | Azure DevOps Server 2019 | TFS 2018. A deployment group is a logical set of deployment target machines that have agents installed on each one. docs.microsoft.com

Please see attached screenshots.

Any reply will be appreciated.

Thank you,

Matthew

Azure DevOPs repo

Create a new repo

I couldn't make git push work with DevOps. It just showed below stupid screen with wrong branch 'main' not 'master'.

The below could mismatch. That's fine. You just need to go to the DevOps site and select the correct repo.

• Visual studio >> Git menu >> git global setting
• Visual studio >> File menu >> account setting

Go to Project >> Repo

Select correct one that you created from Visual Studio.

Now all files show.

Change repo

1. Delete .git folder
2. Close/reopen visual studio
3. Git>> Create a repository
4. Select Azure DevOps
5. Make the repository name different from the project name, eg, TodoApiMainenance

Add more columns to Todo Web api project

 I want to add 4 more columns to the table.

GET Category

View

index.html

            <form action="javascript:void(0);" method="POST" onsubmit="changeCategory()">

                <select id="select-category">

                    <option>Please select</option>

                </select>

            </form>

Model

TodoItem.cs, TodoItemDTO.cs

public string? Category { get; set; }

Controller

TodoItemsController.cs

private static TodoItemDTO ItemToDTO(TodoItem todoItem) => new TodoItemDTO

        {

            Id = todoItem.Id,

            Name = todoItem.Name,

            IsComplete = todoItem.IsComplete,

            Priority = todoItem.Priority,

            Category = todoItem.Category,

        };

PUT Category

View

site.js

function updateItem() {

    if (!validateCategory()) {

        return;

    }

    

    const itemId = document.getElementById('edit-id').value;

    const item = {

        id: parseInt(itemId, 10),

        isComplete: document.getElementById('edit-isComplete').checked,

        name: document.getElementById('edit-name').value.trim(),

        priority: document.getElementById('edit-priority').value.trim(),

        category: selectCategory.value,

    };

    callUpdateAPI(item);

    closeInput();

    return false;

}

Controller

Model

Add Category

View

site.js

function addItem() {

    const addCategory = document.getElementById('add-category');

    const addCategoryCheckbox = document.getElementById('add-category-checkbox');

    let categoryString = undefined;

    if (addCategoryCheckbox.checked) {

        categoryString = addCategory.value;

    } else {

        if (!validateCategory()) {

            return;

        }

        categoryString = selectCategory.value;

    }    

    // saving ...

    const addNameTextbox = document.getElementById('add-name');

    toggleInputValue("#add-name-button");

    

    const item = {

        isComplete: false,

        name: addNameTextbox.value.trim(),

        priority: 9,

        category: categoryString

    };

Controller

TodoItemsController.cs

        [HttpPost]

        public async Task<ActionResult<TodoItem>> PostTodoItem(TodoItemDTO todoItemDTO)

        {

            var todoItem = new TodoItem

            {

                IsComplete = todoItemDTO.IsComplete,

                Name = todoItemDTO.Name,

                Priority = todoItemDTO.Priority,

                Category = todoItemDTO.Category,

            };

            _context.TodoItems.Add(todoItem);

            await _context.SaveChangesAsync();

            //return CreatedAtAction("GetTodoItem", new { id = todoItem.Id }, todoItem);

            return CreatedAtAction(nameof(GetTodoItem), new { id = todoItem.Id }, ItemToDTO(todoItem));

        }

Model

Create shell script reading csv file and inserting SQL Server

Create a shell script

https://www.mssqltips.com/sqlservertip/5623/linux-commands-to-assist-with-importing-a-csv-file-into-sql-server/

Above link's bcp command options didn't work.

Correct bcp command options could be found in:

https://docs.microsoft.com/en-us/azure/azure-sql/load-from-csv-with-bcp?view=azuresql

https://datacadamia.com/db/sql_server/bcp#csv

Also, make sure you didn't open the csv file.

https://stackoverflow.com/questions/39465354/bcp-error-unable-to-open-bcp-host-data-file

After changing 'Year' as int type, import had error.

To delete 1st line of csv file you have tail -n +2 or sed option.

https://stackoverflow.com/questions/604864/print-a-file-skipping-the-first-x-lines-in-bash

Import all working well. bcp -e option could be used for log. But it seems making an empty log although there is no error.

Run script from Informatica

Add a new column Priority to API - PUT

 Now updating priority into database

We first need to make Edit button work, then make Arrow button work.

View

Add Priority value input box

index.html

<input type="text" id="edit-priority">

site.js

updateItem() {

...

const item = {

        id: parseInt(itemId, 10),

        isComplete: document.getElementById('edit-isComplete').checked,

        name: document.getElementById('edit-name').value.trim(),

        priority: document.getElementById('edit-priority').value.trim(),

    };

Model

no change

Controller

no change

All working well.

Now let's update Arrow button.

View

function increasePriority(id) {

    const item = todos.find(item => item.id === id);

    item.priority++;

    callUpdateAPI(item);

    console.log('item to increase:', item);

}

Model

no change

Controller

no change

All good, arrow button works.

Now let's sort by priority desc.

We just need to sort array data.

Below site shows a simple sort function.

https://stackoverflow.com/questions/1129216/sort-array-of-objects-by-string-property-value

function compare( a, b ) {
  if ( a.last_nom < b.last_nom ){
    return -1;
  }
  if ( a.last_nom > b.last_nom ){
    return 1;
  }
  return 0;
}

objs.sort( compare );

We can modify it for priority and desc order.

View

function _displayItems(data) {

    data.sort(compare);

Not it shows sorted.

Add a new column Priority to API - GET

 I want to add 'Priority' column.

- change database

Priority int column in TodoItems table, default value 0

getItems

- change view

site.js

function getItems() {

    displayLoading();

    fetch(uri)

        .then(response => response.json())

        .then((data) => {

            hideLoading();

            _displayItems(data);

            applyCompletedCss();

        })

        .catch(error => console.error('Unable to get items.', error));

}

function _displayItems() {

...

let tdPri = tr.insertCell(2);

        let textNodePri = document.createTextNode(item.priority);

        tdPri.appendChild(textNodePri);

- change model

public class TodoItem {

...

public int Priority { get; set; }

public class TodoItemDTO {

...

public int Priority { get; set; }

- change controller

TodoItemsController.cs >> no change

When everything is good, GET works and it shows.

Errors

When priority is null you have below two errors.

GET https://localhost:7147/api/todoitems 500

site.js:15 Unable to get items. SyntaxError: Unexpected token S in JSON at position 0

If database is bigint and vs code is int, you have below error.

InvalidCastException: Unable to cast object of type 'System.Int64' to type 'System.Int32'.