[PowerShell] How to Retrieve Data with ADO.NET

I am primary a C# developer since 2005. Before that I was a VB5/VB6/VB.NET developer. Recent, I changed my role from development side to a consulting side. I found out that I have to do some development by using powershell script. It is because I will pass the program to client and we should try to build with script or any “no compile needed language”. And so I start writing PowerShell script. Because of this, I will write some blog about PowerShell  that I learned from my job. And this is the first one in this series.

My first PowerShell task is, retrieve data from MSSQL.

From my C# knowledge, I would like to retrieve data by using ADO.NET. So I start looking for how I could use C# library in PowerShell Script. And lucky that there is a way.

Below is my first script in PowerShell.

Function GetDataFromMSSQL {
    Write-Host "====================================="
    $sqlConn = New-Object System.Data.SqlClient.SqlConnection
    $sqlConn.ConnectionString = "Server=localhost;Integrated Security=true;Initial Catalog=AdventureWorks2019;"
    
    try {
        $sqlConn.Open()
        Write-Host "Data Source is connected."
        #$sqlcmd = $sqlConn.CreateCommand()
        $sqlcmd = New-Object System.Data.SqlClient.SqlCommand
        $sqlcmd.Connection = $sqlConn
        #$selectQuery = "SELECT TOP (1000) [SpecialOfferID],[Description],[DiscountPct],[MinQty],[MaxQty] FROM [AdventureWorks2019].[Sales].[SpecialOffer]"
        $sqlcmd.CommandText = $selectQuery

        $sqladp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd
        $data = New-Object System.Data.DataSet
        $sqladp.Fill($data) | Out-Null

        $rowCount = $data.Tables[0].Rows.Count
        Write-Host "$rowCount rows returned."
        $data.Tables
        $true
    } catch {
        Write-Host "Data Source cannot be connected."
        Write-Host $Error[0].Exception.Message
        $false
    } finally {
        $sqlConn.Close()
    }
}
GetDataFromMSSQL

 

No internet access on Host after enabled Hyper-V (or any VM software, i.e., VirtualBox)

Recent, I installed Oracle VM VirtualBox and enabled Hyper-V feature on my Win10 Professional (1709). Since then I cannot browse any intranet or internet web pages in IE and Edge. But there is no problem at all if I disable the newly added virtual network adapter.

I tried to google and search how to fix it. But I found no clue at all.

After few days, suddenly a light bulb goes on in my head.

I think it might be the adapter priorities so all the network traffic is sending out through the virtual network adapter. I then find out the REAL solution to fix it. Now all of the newly added virtual network adapters are enabled and I have no problem at all on browsing intranet and internet web pages.

Here are the steps to fix.

    1. Open Windows PowerShell
    2. Run “Get-NetAdapter” to list all the adapters info
    3. Now you can see the IfIndex value for the virtual network adapter (it is 18 in my PC, it will be other value in your PC). Remember this value.
    4. Then we run “Get-NetIPInterface” to list all the IP interface info
    5. The “real” network adapter for me to go intranet and internet is IfIndex=52, and its InterfaceMetric is 35.
    6. The virtual network adapter (IfIndex=18) is having InterfaceMetric=15.
    7. In windows, the lower number means the higher priorities. So that means all the network traffic will route out from the virtual network adapter and so it seems I lost all the internet access. Now what we should do is, lower the priorities of the virtual network adapter. Because my wifi adapter is having InterfaceMetric=45. So I think putting 55 for the virtual network adapter is a good choice. Let’s try to make the change now.
    8. Let’s open “Windows Settings”
    9. Click into “Network & Internet”
    10. Click into “Change Adapter Options”
    11. Right click the virtual network adapter and select “Properties” button
    12. select “TCP/IPv4” and click “Properties” button
    13. Click “Advanced…” button
    14. Clear the “Automatic metric” and enter “55” to the textbox of Interface Metric (entering other proper value in your case)
    15. Click “OK” button and until all property windows are closed.
    16. Repeat the same steps from (12) to (15) for “TCP/IPv6”
    17. You may run “Get-NetIPInterface” in PowerShell Window to make sure the values are applied.

DONE!
These are all the steps to fix this issue. I hope these steps could also help you.

 

 

[Solved] “Win10 Mail and Calendar Crash”

I am running a new PC and installed win10 1703. I tried to open Mail app and found out that it crashed and closed after opening it for 2-3 seconds. I tried to search and found many blogs that showing me to uninstall and reinstall it back from App Store. I also found some posts showing me to grant FULL permission to “Users” on AppData in my user profile. But none of them can really solve the problem and Mail app is still crashing.

Until today, I open event viewer and found a lot of errors on ESENT with event ID 455.

svchost (8300) Unistore: Error -1023 (0xfffffc01) occurred while opening logfile C:\Users\kenlin\AppData\Local\Comms\UnistoreDB\USS.jtx.

I found out that UnistoreDB is a folder for Mail and Calendar. so I tried to browse into this folder and found out that the folder “Comms” and its sub-folder “UnistoreDB” are missing. I then create it by my own. Bingo! Mail and Calendar is working now! Problem Solved.

Hope this blog could also help you to resolve the Mail app crash.

 

Workaround on adding files or shortcuts to Quick Access in Windows 10

This afternoon, another MVP asked a question, how do we add a file or shortcut to Quick Access in Windows 10. He would like to do the same action as he could add a file or shortcut to Favorites in Windows 8 or before.

In Windows 10, “Favorites” is replaced by “Quick Access”. The “Quick Access” is letting you to browse into any folder in shortest route. If you want to add any folders to “Quick Access”, you could right click any folder and add it to “Quick Access” by selecting “Pin to Quick Access”.

After you pinned it to “Quick Access”, you could then remove it by right click and select “Unpin from Quick Access”.

But if you right click to any files or shortcuts, you will found out that there is no such option to add them into Quick Access. But if the files or shortcuts are often being executed, you could find it from the lower section of the Quick Access, “Recent Files”. But what about if they are not being executed often and you still want to add it to “Quick Access”? One of the work around way is, adding the Favorites to “Quick Access”. With this workaround, you could add any files or shortcuts to the Favorites and it will then appear to the Quick Access. On the other hand, it will be removed from “Quick Access” if you remove it from Favorites. The Favorites path in windows Explorer is “C:\Users\(username)\Links”.

Here is the regedit nodes that you have to add in order to enable “Favorites” add to “Quick Access”.


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\HomeFolderDesktop\NameSpace\DelegateFolders\{d34a6ca6-62c2-4c34-8a7c-14709c1ad938}]
@=”Common Places FS Folder”


[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Explorer\HomeFolderDesktop\NameSpace\DelegateFolders\{d34a6ca6-62c2-4c34-8a7c-14709c1ad938}]
@=”Common Places FS Folder”

And here is the regedit nodes that you have to remove in order to disable “Favorites” add to “Quick Access”.

[-HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\HomeFolderDesktop\NameSpace\DelegateFolders\{d34a6ca6-62c2-4c34-8a7c-14709c1ad938}]


[-HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Explorer\HomeFolderDesktop\NameSpace\DelegateFolders\{d34a6ca6-62c2-4c34-8a7c-14709c1ad938}]



Please note, You will need Administrator right to pass the UAC when you add/remove the regedit nodes.

 

AdminPack / RSAT in Windows 10 for Windows Server 2016

I have blogged about AdminPack/RAST in the past,
1) Where is AdminPak in Windows 2008 and R2 ?
2) AdminPak / RSAT for Windows Server 2012 and R2

I just found out that one of the RAST package is no longer available, it is the RAST in Win8.1. If you are looking for the RSAT for win2012R2, you might need to get the RSAT package for Win10. And here is the link,

RSAT for Win10

IMPORTANT: Remove all older versions of Administration Tools Pack or Remote Server Administration Tools—including older prerelease versions, and releases of the tools for different languages or locales—from the computer before you install Remote Server Administration Tools for Windows 10. Only one copy at a time of Remote Server Administration Tools can be installed on a computer. If you have upgraded to Windows 10 from an older release of Windows, you will need to install Remote Server Administration Tools for Windows 10 on the computer; no earlier releases of Remote Server Administration Tools are still installed on a computer that you have upgraded to Windows 10.

Remote Server Administration Tools for Windows 10 includes support for remote management of computers that are running the Server Core installation option or the Minimal Server Graphical Interface configuration of Windows Server Technical Preview. However, Remote Server Administration Tools for Windows 10 cannot be installed on any releases or installation options of the Windows Server operating system.

Earlier releases of Remote Server Administration Tools (such as those for Windows 8.1) are not available–nor do they run–on Windows 10.

Server Manager is included with Remote Server Administration Tools for Windows 10; GUI-based tools that are part of this release of Remote Server Administration Tools can be opened by using commands on the Tools menu of the Server Manager console. To use Server Manager to access and manage remote servers that are running Windows Server 2008, Windows Server 2008 R2, Windows Server 2012, Windows Server 2012 R2, or Windows Server 2016, you must install several updates on the older operating systems.

*P.S., RSAT (Remote Server Administration Tools) is the replacement toolkit to the AdminPak before Windows Server 2008