Powershell script to read from Excel file

This Powershell script will help you reading data from excel sheet.

To read the Excel sheet, you should have Excel installed on your machine.

Create the object to read the excel file:

$objExcel = New-Object -ComObject Excel.Application

Create the object for the workbook:

$WorkBook = $objExcel.Workbooks.Open($strPath)

Assess worksheet:

$worksheet = $workbook.sheets.item("Worksheet1")

Traverse into rows and columns:

$intRowMax = ($worksheet.UsedRange.Rows).count
$intColMax = ($worksheet.UsedRange.Columns).count
for ($intRow = 1 ; $intRow -le $intRowMax ; $intRow++) {
      for($col =1 ; $col -le $intColumns ; $col++) {
                                                    }}

Powershell Script

$strPath = ""
$outpath = ""

$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $false

$WorkBook = $objExcel.Workbooks.Open($strPath)
$worksheet = $workbook.sheets.item("Sheet1")
$intRowMax =  ($worksheet.UsedRange.Rows).count
$intColMax =  ($worksheet.UsedRange.Columns).count

Write "--------------- ------------------- -------------------- -----------------------------" | 
      Out-FIle $outpath -Append

Write-Host "Processing: " $intRowMax " rows" "and columns " $intColMax    

for ($intRow = 1 ; $intRow -le $intRowMax ; $intRow++) {
  
     for($col =1 ; $col -le $intColMax ; $col++) 
     {
            $cellValue = $worksheet.cells.item($intRow, $col).value2
             "Cell ( " + $intRow + "," + $col + ") : =" + $cellValue |
                Out-File $outpath -Append
     }
 }

$objexcel.quit()

Link to download the powershell script

Download

Advertisements

List of useful .Net libraries, NuGet packages and Tools for Developers

List of useful .net Libraries, NuGet packages, and Tools

  • LinqPad  Very useful instantly tool to test LINQ, SQL queries
  • ReoGridFast and powerful .NET spreadsheet component, support data format, freeze, outline, formula calculation, chart, script execution and etc. Compatible with Excel 2007 (&later) format and working on .NET 3.5 (or client profile), WPF and Android (view only) platform.
  • PDF Sharp & MigraDoc: .NET library for modeling and rendering pdf documents. Sample code http://www.pdfsharp.net/wiki/MixMigraDocAndPdfSharp-sample.ashx
  • LinqToDb Provider:  LINQ to DB is the fastest LINQ database access library offering a simple, light, fast, and type-safe layer between your POCO objects and your database. Learning. Code Samples: https://github.com/linq2db/examples

It supports these databases:

DB2 (LUW, z/OS)

Firebird

Informix

Microsoft Access

Microsoft Sql Azure

Microsoft Sql Server

Microsoft SqlCe

MySql

Oracle

PostgreSQL

SQLite

SAP HANA

Sybase ASE

  • ExtensionMethods.Net – Site with a collection of extension methods
  • Z.ExtensionMethods – Over a 1000 useful extension methods
  • HTML Utility Pack: HAP is an HTML parser written in C# to read/write DOM and supports plain XPATH or XSLT.
  • C# Bulk Operations  Extend and Overcome SqlBulkCopy Limitations with Must-Have Features.
  • NLog – Advanced .NET and Silverlight logging
  • FluentEmail – A fluent wrapper for System.Net.Mail with Razor templating support.
  • PushSharp – A server-side library for sending Push Notifications to iOS, OSX, Android, Chrome, Windows Phone, Windows 8, Blackberry, and Amazon devices.
  • RabbitMQ.NET – Implementation of an AMQP client library for C#, and a binding exposing AMQP services via WCF
  • Kafka Client – .NET implementation of the Apache Kafka Protocol that provides basic functionality through Producer/Consumer classes.

PowerShell Sample Scripts

I am listing some of the commonly used PowerShell scripts which might make your life little easier.

Files and Folders

  • GetFiles: Get file count in a folder

GetFiles(“c:\temp”, “*.*”).Count

  • get-ChildItem : Enumerating files in a folder
      $dirpath="c:\temp"
      foreach ($file in get-ChildItem -Path $dirpath\* -Include *.*) 
       {           
         $file.FullName
       }
  • Filtering files in a folder. Below script is showing only SQL files.
$dirpath="c:\temp"
foreach ($file in get-ChildItem -Path $dirpath\* -filter *.sql) 
     {           
       $file.FullName
     }
  • Display files with Include or Exclude conditions

it will display files having the name “ABC” and exclude all files having the name “XXX”

$dirpath="c:\temp"
  foreach ($file in get-ChildItem -Path $dirpath* -Include "*ABC*.*" -Exclude "XXX*.Sql") 
     {    
		$file.FullName
	}
  • Get-Content: Get the content of text file in an array and display it.
  $Content = Get-Content -Path C:\temp\DeliveryLog.txt
  <#Display all text of a file#>
  $Content
  <#Display text of first line#>
  $topRow = $Content[0]
  $topRow

SQL Server

  • Invoke-Sqlcmd:

    Execute SQL queries with Windows Authentication

	$sqlcmd ="Select * from table1"
	$Database ="databasename"
	$DbServer ="localhost"
	Invoke-Sqlcmd -Query $sqlcmd -ServerInstance $DbServer -Database $Database
  • Invoke-Sqlcmd:

Execute SQL queries with SQL Authentication

  • 	$file="c:\temp\dbscript.sql"
    	$Database ="databasename"
    	$DbServer ="localhost"
    	Invoke-Sqlcmd -InputFile $file -ServerInstance $DbServer -Database $Database -UserName "user1" -Password "password1"
  • Invoke-Sqlcmd:

    Execute Sql file

	$file="c:\temp\dbscript.sql"
	$Database ="databasename"
	$DbServer ="localhost"
	Invoke-Sqlcmd -InputFile $file -ServerInstance $DbServer -Database $Database 
  • SQL Query output to Grid View

    Out-GridView displays output to data-grid.

	$sqlcmd ="Select * from table1"
	$Database ="databasename"
	$DbServer ="localhost"
	Invoke-Sqlcmd -Query $sqlcmd -ServerInstance $DbServer -Database $Database | Out-GridView

Windows Service

  • Get-Service: Export the list of windows services on a windows machine
Get-Service | Export-CSV c:\temp\service.csv
  • Start-Service: Start windows service
Start-Service -name "ServiceName" <#name of service #>
Start-Service -displayname "ServiceName" <#display name of service #>
  • Stop-Service: Stop windows service
Stop-Service -name "ServiceName" <#name of service #>
Stop-Service -displayname "ServiceName" <#display name of service #>
  • Restart-Service: Restart windows service
Restart-Service -name "ServiceName" <#name of service #>
Restart-Service -displayname "ServiceName" <#display name of service #>

Send Email via Asp.net Web application hosted on godaddy

This article is providing the fix for the issue while sending emails from the web applications hosted on Godaddy server.

Recently I faced an issue while sending emails from a website which is hosted on GoDaddy server. I used Zoho SMTP server to send the emails because I am using that email server. I used below code to send an email which worked fine on my local machine:

 MailMessage mail = new MailMessage();
 SmtpClient smtpclient = new SmtpClient("smtp.zoho.eu", 587);
 smtpclient.Credentials = new System.Net.NetworkCredential("neeraj@abc.com", "abcXXX");
 smtpclient.EnableSsl = true;
 smtpclient.UseDefaultCredentials = false;
 mail.From = new MailAddress("neeraj@abc.com");
 mail.To.Add("neeraj@abc.com");
 mail.Subject = "Test Email";
 mail.IsBodyHtml = true;
 mail.Body = HttpUtility.HtmlDecode("test");
 smtpclient.Send(mail);

When I uploaded this code to GoDaddy server and try to send the email, I got an error like:

System.Net.Mail.SmtpException: Failure sending mail. ---> System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: An attempt was made to access a socket in a way forbidden by its access permissions 31.186.226.34:587
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)

After searching on GoDaddy forum, I found that GoDaddy forbids other SMTP hosts except for GoDaddy. Therefore, I had to change the code to use GoDaddy SMTP hosting server.

Code Changes

  • Smtp Client’s Host changed to “relay-hosting.secureserver.net”.
  • Removed port as 587
  • Removed EnableSSL

This is the final code which worked fine on GoDaddy server.

MailMessage mail = new MailMessage();
 SmtpClient smtpclient = new SmtpClient();
 smtpclient.Host = "relay-hosting.secureserver.net";
 smtpclient.UseDefaultCredentials = false;
 smtpclient.Credentials = new System.Net.NetworkCredential("neeraj@abc.com", "abcXXX");
 mail.From = new MailAddress("neeraj@abc.com");
 mail.To.Add("neeraj@abc.com");
 mail.Subject = $"Test Email";
 mail.IsBodyHtml = true;
 mail.Body = HttpUtility.HtmlDecode("test");
 smtpclient.Send(mail);

Comparison: Sql Server in Azure VM and Azure Sql Database

Azure SQL Database is native to the Azure and offered as Platform as a service (PaaS). The objective of this offering is to reduce the overall costs to the minimum for provisioning and managing many databases. This offering comes with built-in high availability, disaster recovery, and upgrades for the database. Databases run on latest version of SQL Server Enterprise Edition. Many databases can be managed by single IT resource which reduces the overall cost of administrations.

SQL Server running on Azure VMs is categorized as Infrastructure as a service (IaaS). This setup is best for migrating existing applications to Azure or extending existing on-premises applications to the cloud in deployments.

You can use preinstalled SQL Server on VMs or install your own licensed version of SQL Server. This setup is good when you want to run existing applications that require fast migration to the cloud with minimal changes. You have the full administrative rights over a dedicated SQL Server instance and a cloud-based VM.

Comparison Table

Azure Website Deployment mechanisms

A website or code to Azure App Service is deployed normally via following methods:

FTP: This is manual steps to deploy a website wherein all deliverable files are copied to destination path on the web server via FTP client tools like FileZila. This is a basic way of deployment where you have to know how to deploy files to the correct directories in App Service, no version control supported for rollback when failures occur.

Web Deploy: It is a mechanism to deploy code to App service directly from Visual Studio. This tool supports diff-only deployment, database creation, transforms of connection strings, etc. Web Deploy differs from Kudu in that application binaries are built before they are deployed to Azure. Similar to FTP, no additional services are provided by App Service.

Kudu: it is a deployment engine which facilitates deployment of code to App service directly from Dropbox, One Drive, git, mercurial repositories. Kudu also provides added services when the code is committed, including version control, package restore, MSBuild, and web hooks for continuous deployment (eg. git hooks) and other automation tasks.

Kudu deployment engine supports 3 different types of deployment sources:

  • Content synchronization from OneDrive and Dropbox.
  • Repository-based continuous deployment from external services like GitHub, Bitbucket, and Visual Studio Team Services.
  • Repository-based deployment with sync from local Git.  The Git repository is “local” to your Web App instance.  You can publish directly to the Web App from your local computer simply by cloning the Git repository.  Once you commit and push the changes directly to the repository that is local to the Web App.  On any commits, the Web App creates a new deployment and using the latest build of code.

Continuous Deployment using Git

Continuous Deployment workflow from Git repository

KUDU engine source code is available as open source. You can read more about KUDU on its wiki page. KUDU wiki.

 

 

 

 

Different Hosting Models in Azure

Azure_hosting models

Orange boxes here showing what you have to manage.

Yellow boxes here showing what cloud vendors manage.

On-premises: customer has to provision servers, storage, monitoring, installation and maintenance of services and applications.

Infrastructure as Service (IaaS): the customer can manage some of it, like the actual Hyper-V, the virtualization engine that your virtual machines are running on; the storage, the networking, the customer can also manage the OS. The customer can do whatever you want to with IaaS which makes it really nice for workloads that you have to customize a lot.

Platform as Service (PaaS): Cloud vendors are managing most of the stuff like
the operating system, the runtime, the middleware, the actual virtual machine itself or the host, and so in case of that, the customer just basically have to manage the application and data.


Software as a Service: In this case basically everything’s managed by the vendors. The customer doesn’t have to do anything; they don’t even have to bring applications way out, vendors already have it provisioned.
If we think of something – like a Software as a Service we have out there like Office365 or
something like that, even third party like sales force or something – those are SaaS
type vendors where you just go create an account, use the system; you don’t have to provision the application, it does it all for the customer.

 

Suspend or resume windows controls to be redraw

Recently, I was facing issue with third party Image Viewer to apply multiple operations before redrawing of images.
Third-party control was not allowing to suspend and resume painting of images inherently. After searching for various solutions, I found a solution that can be achieved using windows message WM_SETREDRAW from an application to Windows to allow changes in that control to be redrawn or to prevent changes in that control from being redrawn.

public class UIDrawController
{
[DllImport("user32.dll")]
public static extern int SendMessage(IntPtr hWnd, Int32 wMsg, bool wParam, Int32 lParam);

private const int WM_SETREDRAW = 11;

public static void SuspendDrawing(Control ctrl)
{
SendMessage(ctrl.Handle, WM_SETREDRAW, false, 0);
}

public static void ResumeDrawing(Control ctrl)
{
SendMessage(ctrl.Handle, WM_SETREDRAW, true, 0);
ctrl.Refresh();
}
}

Example Usage:


public ImageViewer : UserControl
{

public void LoadDocument(string filename)
{

UIDrawController.SuspendDrawing(this);

OpenImage(filename);
FitToWidth();
Rotate90();
ApplyImageFilters();

UIDrawController.ResumeDrawing(this);
}
}

Change Tracking example -Sql Server

If there is a requirement to get incremental or changed data from database frequently without putting a heavy load on database objects, then Change Tracking mechanism of Sql Server can be out of the box solution for this requirement. Normally, developers have to do custom implementation to achieve change tracking behavior. It can be implementation by considering triggers, timestamp columns, or maintaining new tables.

Following is step by step instructions to enable and use of change tracking feature in SQL Server.

Step 1: Check if database compatibility level is set to 90 or greater. If It is lower than 90 then change tracking will not work.

SELECT compatibility_level
FROM sys.databases WHERE name = '';

Step 2: Enable Isolation level on a database to Snapshot. It will ensure change tracking information is consistent.

ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON

Step 3: Set Change tracking on a database.

ALTER DATABASE SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS,AUTO_CLEANUP = ON)

CHANGE_RETENTION: It specifies the time period for which change tracking information is kept
AUTO_CLEANUP: It enables or disables the cleanup task that removes old change tracking information.

Step 4: Enable change tracking on a table.

ALTER TABLE
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF)

TRACK_COLUMNS_UPDATED: Setting value to “ON” will make SQL Server Engine storing extra information about columns which are enabled for change tracking. ‘OFF’ is default value to avoid extra overhead on SQL Server to maintain extra columns information.

Step 5: Example to get changed data.

It is example of SQL procedure which will only send changed data from table. Application can pass @lastVersion = 0 first time and going forward application can keep the last version in the cache and pass on last stored version.


CREATE PROCEDURE [dbo].[GetIncrementalChanges]
@lastVersion BIGINT = 0 OUTPUT
AS
BEGIN
DECLARE @curVersion BIGINT = CHANGE_TRACKING_CURRENT_VERSION()
IF @lastVersion = 0
BEGIN
SELECT
a.*
FROM a
END
ELSE
BEGIN
SELECT
a.*
FROM a
INNER JOIN CHANGETABLE(CHANGES , @lastVersion) ct ON A.Id= ct.Id
END

SET @lastVersion = @curVersion

END

Disable Change Tracking

1. Before disabling change tracking on a database, all tables should have change tracking disabled.

Testing Sql statements

You can find working example in attached SQL file or code below:

changetracking


SET NOCOUNT ON
go
PRINT 'Creating test database'
Go
CREATE DATABASE testDb
GO
USE testDb
go
PRINT 'Get compatibility level of db'
GO

SELECT compatibility_level
FROM sys.databases WHERE name = 'v';

GO
PRINT 'Setting db isolation level'
ALTER DATABASE testDb SET ALLOW_SNAPSHOT_ISOLATION ON;

GO
PRINT 'Creating table testchange'
GO
CREATE TABLE dbo.TestChange
(
Id INT NOT NULL ,
NAME VARCHAR(20)
NOT NULL CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED ( [Id] ASC )
);

GO
PRINT 'Inserting initial values'
GO

INSERT INTO dbo.TestChange
( Id, NAME )
VALUES ( 1, -- Id - int
'ABC' -- NAME - varchar(2)
),
( 2, 'XXX' );
GO

PRINT 'See current change tracking version before Change tracking enabled';

SELECT [change tracking version after Enabling] = CHANGE_TRACKING_CURRENT_VERSION();
GO
PRINT 'Enable Change Tracking on database';

ALTER DATABASE testDb SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS,AUTO_CLEANUP = ON)

GO
PRINT 'Enable Change Tracking on testchange table';
GO
ALTER TABLE dbo.TestChange
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

SELECT [change tracking version after Enabling] = CHANGE_TRACKING_CURRENT_VERSION();

GO
CREATE PROCEDURE [dbo].[GetIncrementalChanges]
@lastVersion BIGINT = 0 OUTPUT
AS
BEGIN
DECLARE @curVersion BIGINT = CHANGE_TRACKING_CURRENT_VERSION()
IF @lastVersion = 0
BEGIN
SELECT
a.*
FROM TestChange a
END
ELSE
BEGIN
SELECT
a.*
FROM TestChange a
INNER JOIN CHANGETABLE(CHANGES dbo.TestChange, @lastVersion) ct ON A.Id= ct.Id
END

SET @lastVersion = @curVersion

END
GO

DECLARE @lastVersion1 BIGINT =0

EXECUTE dbo.GetIncrementalChanges @lastVersion = @lastVersion1 OUTPUT -- bigint

PRINT 'Get Last Version'
SELECT [Last Version] = @lastVersion1

PRINT 'insert new rows in table'

INSERT INTO dbo.TestChange
( Id, NAME )
VALUES ( 3, -- Id - int
'YYYY' -- NAME - varchar(2)
),
( 4, -- Id - int
'ZZZ' -- NAME - varchar(2)
)

EXECUTE dbo.GetIncrementalChanges @lastVersion = @lastVersion1 OUTPUT -- bigint

PRINT 'Get latest Version'
SELECT @lastVersion1

INSERT INTO dbo.TestChange
( Id, NAME )
VALUES ( 5, -- Id - int
'KKKK' -- NAME - varchar(2)
),
( 6, -- Id - int
'LLLL' -- NAME - varchar(2)
)

EXECUTE dbo.GetIncrementalChanges @lastVersion = @lastVersion1 OUTPUT -- bigint

PRINT 'Get latest Version'
SELECT @lastVersion1

GO
PRINT 'Disable Change Tracking on table'
ALTER TABLE dbo.TestChange
DISABLE CHANGE_TRACKING
GO
PRINT 'Current change tracking version after disabling';
SELECT [change tracking version after disabling] = CHANGE_TRACKING_CURRENT_VERSION()
GO
PRINT 'Disable Change Tracking on Database'

ALTER DATABASE testDb SET CHANGE_TRACKING = OFF

GO

PRINT 'test complete, dropping database'
USE master
Go
DROP DATABASE testDb