```sh $progressPreference = 'silentlyContinue' Write-Information "Downloading WinGet and its dependencies..." Invoke-WebRequest -Uri https://aka.ms/getwinget -OutFile Microsoft.DesktopAppInstaller_8wekyb3d8bbwe.msixbundle Invoke-WebRequest -Uri https://aka.ms/Microsoft.VCLibs.x64.14.00.Desktop.appx -OutFile Microsoft.VCLibs.x64.14.00.Desktop.appx Invoke-WebRequest -Uri https://github.com/microsoft/microsoft-ui-xaml/releases/download/v2.7.3/Microsoft.UI.Xaml.2.7.x64.appx -OutFile Microsoft.UI.Xaml.2.7.x64.appx Add-AppxPackage Microsoft.VCLibs.x64.14.00.Desktop.appx Add-AppxPackage Microsoft.UI.Xaml.2.7.x64.appx Add-AppxPackage Microsoft.DesktopAppInstaller_8wekyb3d8bbwe.msixbundle ``` Below is code snippet to install Net 7.0 SDK using winget ```sh winget install Microsoft.DotNet.SDK.7 ``` Below is code snippet to install Net 7.0 runtime using winget ```sh winget install dotnet-runtime-7 ```powershell
Rajesh Kolla's blog
Azure Cloud Solution Architect, Full-Stack Development in .Net Eco system, Senior Manager at Capgemini
Sunday, November 19, 2023
How to install winget utility using powershell
Windows Package Manager (winget) is a command-line tool that enables users to discoer, install, upgrade, remove and configure application on windows 10 and higher version
We can install winget utility using below powershell script in windows
Sunday, October 8, 2023
Get Folders with Exclude folders recursively
In general , we can use get-childitems to get folders recursively and if we want to exculde any list of folders then we can use Exclude switch but this switch wont work for recursively , it only work for
current depth so we can use below recursive function to get list of folders recursively by excluding list of folders recursively.
```sh function Get-Folders { param ( [string]$Path, [string[]]$Excludes ) $list = New-Object System.Collections.Generic.List[string] $Directories=(gci $path -Directory -Exclude $Excludes ).FullName foreach ( $Directory in $Directories) { $Directory Get-Folders $Directory -Excludes $Excludes } } [string[]]$Excludes=@("bin","obj",".vscode","dialogs","Controllers","AzureOpenAIBot") Get-Folders "C:\Users\codeRep\labs\aztd-OpenAI" -Excludes $Excludes ```
Monday, September 18, 2023
git stash usages
---- Sure, here are some examples of how you gcan use `git stash`: 1. **Save changes to a new stash**: - Suppose you're working on a new feature in a dedicated branch, but you need to switch to another branch to fix a bug. - You've made changes that you don't want to commit yet, so you can stash them. Here's how you can do it: ```bash git stash save "WIP on new feature" ``` This command will save your changes to a new stash with the message "WIP on new feature". You can use any message that helps you remember what changes are in the stash. 2. **List all stashes**: - If you want to see all of the stashes that you've currently saved, you can use this command: ```bash git stash list ``` This command will show you a list of all stashes that you currently have. 3. **Apply changes from the most recent stash**: - Once you're ready to continue working on your new feature, you can apply the changes from your most recent stash using this command: ```bash git stash pop ``` This command will apply the changes from the most recent stash and then remove it from your list of stashes. 4. **Apply changes from a specific stash**: - If you have multiple stashes and you want to apply changes from a specific one, you can do so with this command: ```bash git stash apply stash@{10} ``` This command will apply the changes from the stash named `stash@{10}`. 5. **Discard the most recent stash**: - If you decide that you no longer need the changes in your most recent stash, you can discard it with this command: ```bash git stash drop ``` This command will discard the most recent stash. 6. **Remove all stashes**: - If you want to remove all of your stashes at once, you can use this command: ```bash git stash clear ``` This command will remove all of your stashes. Note Remember, any changes that are stashed are kept locally and are not transferred when you push your commits to a remote repository.
Sunday, September 17, 2023
Ways of Identifying duplicate rows in SQL
---- To find duplicate rows using partition and rank, you can use the following steps: - First, you need to use the PARTITION BY clause to divide the rows in your table into groups based on one or more columns that you want to compare for duplicates. For example, if you want to find duplicate rows based on the name and address columns, you can use PARTITION BY name, address. - Next, you need to use the ORDER BY clause to specify the order of rows within each partition. You can use any column that you want to sort by, such as the ID column. For example, you can use ORDER BY ID. - Then, you need to use the RANK () function to assign a rank to each row within each partition. The rank starts from 1 and increases by 1 for each row. If two or more rows have the same values in the partition columns, they will have the same rank. For example, you can use RANK () OVER (PARTITION BY name, address ORDER BY ID) AS rank. - Finally, you need to select the rows that have a rank greater than 1. These are the duplicate rows that have the same values in the partition columns as another row. You can use a subquery or a common table expression (CTE) to filter these rows. For example, you can use: ```sql -- Using a subquery SELECT * FROM ( SELECT *, RANK () OVER (PARTITION BY name, address ORDER BY ID) AS rank FROM table_name ) t WHERE rank > 1; -- Using a CTE WITH cte AS ( SELECT *, RANK () OVER (PARTITION BY name, address ORDER BY ID) AS rank FROM table_name ) SELECT * FROM cte WHERE rank > 1; ``` There are some other ways to find duplicate rows in SQL, depending on the database system and the requirements. Here are some examples: - You can use the EXISTS or NOT EXISTS operator to check if a row exists in a subquery that matches the values of another row. For example, you can use this query to find duplicate rows based on the name and address columns: ```sql SELECT * FROM table_name t1 WHERE EXISTS ( SELECT 1 FROM table_name t2 WHERE t1.name = t2.name AND t1.address = t2.address AND t1.id <> t2.id ); ``` - You can use the JOIN clause to join a table with itself and compare the values of the columns. For example, you can use this query to find duplicate rows based on the name and address columns: ```sql SELECT t1.* FROM table_name t1 JOIN table_name t2 ON t1.name = t2.name AND t1.address = t2.address AND t1.id <> t2.id; ``` - You can use the DISTINCT or GROUP BY clause to select only the unique values of the columns and then use the EXCEPT or MINUS operator to subtract them from the original table. For example, you can use this query to find duplicate rows based on the name and address columns: ```sql SELECT name, address FROM table_name EXCEPT SELECT DISTINCT name, address FROM table_name; ``` To identify duplicate rows using group by in SQL, you can use the following steps: - First, you need to select the columns that you want to check for duplicates using the SELECT statement. For example, if you want to find duplicate rows based on the name and address columns, you can use SELECT name, address. - Next, you need to use the GROUP BY clause to group the rows by the selected columns. For example, you can use GROUP BY name, address. - Then, you need to use the COUNT function in the HAVING clause to filter the groups that have more than one row. These are the groups that contain duplicates. For example, you can use HAVING COUNT (*) > 1. - Finally, you need to execute the query and see the results. For example, the complete query would look like this: ```sql SELECT name, address FROM table_name GROUP BY name, address HAVING COUNT (*) > 1; ``` This query will return all the rows that have duplicate values in the name and address columns.
Remove port in windows
---- To remove a port in Windows, you need to find and kill the process that is using that port. You can do that by following these steps: - Open a command prompt as an administrator. - Type the command `netstat -ano | findstr :8000` and press Enter. This will show you the process ID (PID) of the process that is using port 8000. - Type the command `taskkill /F /PID` and press Enter, where ` ` is the number you found in the previous step. This will force the process to terminate and free the port. - You can verify that the port is no longer in use by typing the command `netstat -ano | findstr :8000` again and seeing no results. ```sh netstat -ano | findstr :8000 taskkill /F /PID 8992 # example PID 8992 which was return by above statement ```
Wednesday, September 13, 2023
Extract SQL query from Open Database connection in EXCEL
---- 1. This script opens an Excel file, loops through each connection in the workbook, checks if the connection is an OLEDB connection, 2. then prints the command text (SQL query) of each OLEDB connection. 3. Rplace "C:\path\to\your\Spreadshee.xlsx" with the path to your Excel file1. ```sh # Create an Excel COM object $excel = New-Object -ComObject Excel.Application Extracting SQL queries from Excel’s open data connection # Open the workbook $workbook = $excel.Workbooks.Open("C:\path\to\your\Spreadshee.xlsx") # Loop through each connection in the workbook foreach ($connection in $workbook.connections) { # Check if the connection is an OLEDB connection if ($connection.Type -eq 2) { # Get the OLEDB connection $oledbConnection = $connection.OLEDBConnection # Print the command text (SQL query) Write-Output $oledbConnection.CommandText } } # Close the workbook and quit Excel $workbook.Close() $excel.Quit() # Release the COM objects [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null [System.GC]::Collect() [System.GC]::WaitForPendingFinalizers() ``` Please note that this script requires Excel to be installed on your machine, as it uses Excel’s COM object model. Also, running scripts that use COM objects might have issues with non-interactive sessions (like a scheduled task or a remote session).
Extract Data from Excel using powershell
---- 1. This script opens an Excel file and reads data from a sheet named Sheet. 2. Replace "SELECT * FROM [Sheet1$]" with the query that suits your needs. 3. Also, please ensure that the Microsoft.ACE.OLEDB.12.0 provider is installed on your machine, as it’s required to read data from Excel1. ```sh #Read data from Excel $cnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES';Data Source=c:temptest.xlsx;Mode=Share Deny Write;Jet OLEDB:Engine Type=37;" $cn = New-Object System.Data.OleDb.OleDbConnection $cnStr $cn.Open() $cmd = $cn.CreateCommand() $cmd.CommandText = "SELECT * FROM [Sheet1$]" $rdr = $cmd.ExecuteReader() $dt = new-object System.Data.DataTable $dt.Load($rdr) $dt | Out-GridView ``` Please note that this script reads data from Excel using SQL,but it doesn’t extract SQL queries stored in Excel. If you have SQL queries stored in cells of an Excel sheet and you want to extract them, you would need to adjust the script to select those specific cells.
Subscribe to:
Posts (Atom)