Download Files in Parallel using C#

I wrote this little utility program that allows you to download multiple files from URLs using C#.

 
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;

namespace FileDownloader
{
    class Program
    {
        static void Main(string[] args)
        {
            List allUrls = GetUrls().Select(x=>x.Trim()).ToList();

            Parallel.ForEach(allUrls, new ParallelOptions() { MaxDegreeOfParallelism = 10 }, url =>
            {
                try
                {
                    WebRequest request = WebRequest.Create(url);
                    WebResponse response = request.GetResponse();
                    string originalFileName = response.ResponseUri.AbsolutePath.Substring(response.ResponseUri.AbsolutePath.LastIndexOf("/") + 1);
                    Stream streamWithFileBody = response.GetResponseStream();
                    using (Stream output = File.OpenWrite(@"C:\Ebooks_New\" + originalFileName))
                    {
                        streamWithFileBody.CopyTo(output);
                    }

                    Console.WriteLine("Downloded : " + originalFileName);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Unable to Download : " + ex.ToString());
                    
                }
            });

            Console.WriteLine("Finished : ************************");
            Console.ReadKey();
        }

        public static List GetUrls()
        {
            return new List() // Put list of URLs here
            {
                "http://ligman.me/1IW1oab  ",
    "http://ligman.me/1Uixtlq  ",
    "http://ligman.me/1R9Ubgt  ",
    "http://ligman.me/1H4VXHT  ",
    "http://ligman.me/1f8XUKy  ",
    "http://ligman.me/1HBEUPi  ",
    "http://ligman.me/1NDTZR4  ",
    "http://ligman.me/1Uiy2f9  ",
    "http://ligman.me/1epZ0QU  ",
    "http://ligman.me/1JIhgjA  ",
    "http://ligman.me/1CQX5uG  ",
   }
  }
 }
}

Angular 2 Step by Step Guide: How to consume a REST web service via angular service?

Angular 2 has a modular development architecture where each module has its own set of components, templates and routes. Service is more at an application level and can be shared by multiple components or modules. Angular 2 has a concept of injector whereby you don’t need to create the instance of the service yourself but whenever you need to consume service you can request Angular to provide you with an instance. In this blog we are going to consume an external web service which return list of countries and create an angular service to use that web service to provide list of countries to the dropdown in component.

Think of angular service as nothing special but an ES6 class that exports some methods to be consumed by components. First lets clone/download an Angular 2 seed project from Angular’s github repo ( https://github.com/angular/angular2-seed ) and navigate to angular2-seed folder and run npm install. This will install all necessary modules to run the application.

Open your project in visual studio code which is a free code editor.

In src folder let’s create a services folder and add a file called countrylist.service.ts. In this file add a class called CountryListService . This is a bare bone class that will provide you with a method to get list of countries so lets add a method to it called getCountries().

 
class CountryListService {
getCountries(){
//This method will return list of countries.
}
}

For service to be available to other components we have to export the class and add @Injectable() decorator to class to allow Angular to inject objects in it when required. Think of decorates as an annotations used in C# or java. It provide some metadata to angular to function it properly. To use @Injectable you have to import it from '@angular/core'. At this point you have a class that looks like.

 
import { Injectable } from '@angular/core';
@Injectable()
export class CountryListService {
getCountries(){
//This method will return list of countries.
}
}

Now we are ready to write actual implementation of getCountries(). The REST web service we are going to use is ( https://restcountries.eu/rest/v1/all ). It returns a lot of data but we are only interested in name of countries. We need an http object to call this web service so we need to import Http module from '@angular/http’. Now you can declare an http object in the class and create a new instance when required but Angular provides an alternative where you declare a private variable in constructor of the type you need and it will create an instance when you need it. This is done via dependency injection without you needing to create an object explicitly.

 
import { Injectable } from '@angular/core';
import {Http, Response, Headers, RequestOptions} from '@angular/http';
@Injectable()
export class CountryListService {
constructor(private http:Http) {
     }
getCountries(){
//This method will return list of countries. We can use http variable from constructor to call get method.
}
}

Now we implement getCountries() method. We use http.get method which returns an Observable . Observable provides an async behaviour. Observable comes from RxJs so you have to import it. Once you get a response you have to map it or catch any error. So we implement two functions to deal with either response or error. getCountries() will return Observable of type any[] array. We also need to import map and catch operators from RxJs. In this implementation all we are doing is calling the REST web service and once response comes back and if it is 200 OK we map the response to get the json result out of it and return the result or throw an Observable exception if there is any error calling the web service.

 
getCountries():Observable{
         //You can provide additional header options to get which is not required here.
         //let headers = new Headers({'content-type':'application/json'});
         //let options = new RequestOptions({headers:headers});
        return this.http.get("https://restcountries.eu/rest/v1/all")
                    .map(this.extactData)
                    .catch(this.handleError);
     }

     private extactData(resp:Response){
         let body = resp.json();
         console.log(body);

         return  body ;
     }

     private handleError(error:any){
         console.log(error);
       return Observable.throw(error.statusText);
     }

Now we have fully implemented our service lets make it available to the components. Open app.module.ts and add it to providers array providers: [ CountryListService,... ]. You need to import the service from services folder. This makes Angular aware that whenever any component in AppModule asks for object of type CountryListService where it can create one from. Angular maintains a single instance of this service per module using injector.

 
import {CountryListService} from './services/countrylist.service'
providers: [
    CountryListService,
    GithubService
  ],
  

Now that our service is implemented and ready to be used by components in AppModule lets go to home folder and open home.component.ts . Let import our service and observable first. We use the same principal we used in service to inject service instance via private constructor variable and create a variable of type array to hold list of countries.

 
import {CountryListService} from '../services/countrylist.service'
import {Observable } from 'rxjs/Rx'


countries:any[]=[]; // declared inside the HomeComponent class

 constructor(private countryListService : CountryListService){
  
 }  

Where should you call the service you just written to populate countries array ? It depends on at what point you need the data. If you need the data when your form loads you can implement OnInit from Angular and override ngOnInit method and call the web service there. So lets do that. You need to import OnInit from @angular/core. At this point class signature will look like export class HomeComponent implements OnInit. getCountries() return an Observable so you have to subscribe to it to get data out of it. Here we are using lambda like syntax to subscribe to response or error. Once data is returned we are logging it to console and populating the array with only name field. Hover over subscribe to understand the syntax properly. If there is an error we are just simply logging it to console. You may want to do more with error later.

 
ngOnInit() {
      this.countryListService.getCountries().subscribe(
     data=>{console.log(data); 
      for (var i=data.length;i--;) {
        console.log('returned : ' + data[i].name)
          this.countries[i]=data[i].name;  
        }
      },
     err=> console.log(err)
   )
   }

Now we are ready to display this data in home.component.html. I have added a dropdown for country. We are using *ngFor directive to loop through each country in the array and add it as an option.

 
  <select class="form-control" #country
   name="country">
      <option value="default">Select country...</option>
      <option *ngFor="let cnt of countries">
          {{ cnt }}
      </option>
  </select>

Now to run the application from command prompt run npm start and navigate to browser http://localhost:3000/ . You will be able to see list of countries in a dropdown.

You can use Google chrome’s developer tools using F12 to view the console log. That’s it. There are many moving parts but once you understand how it all fits together it becomes easy to implement service. You can extend on this service by implementing OnDestroy and unsubscribe from the Observable.

Call Google Maps Geocode API in Parallel using C# and TPL

Google Maps Geocode API provides a way to validate addresses by getting latitude and longitude and address type of a given address. In this post I would like to show you how to call this web service in parallel so you can speed up the address validation process. One thing to note is URL to get Geocode details from Google Maps  API is different if you are only using free version in comparison to when you are using Google Maps API for work. By default Google Maps API provide 2500 request per day for free. When you are calling Google Maps API using paid version you need to encrypt your request using your client ID and encryption key provided to you when you buy it.

First thing first. You will need a key to run this application so sign up for Google Maps API and get your key https://developers.google.com/maps/documentation/geocoding/intro

You can download the full source code from GoogleGeocode.GoogleMapsAPI Source

Setup your app.config file
In AppSettings section add

 
  <add key="IsGoogleMapsAPIPaid" value="0"/>
   <add key="URL" value="https://maps.googleapis.com/maps/api/geocode/json?address="/>
   <add key="APIClient" value="yourclientId"/>
   <add key="APIKey" value="yourcryptokey"/>

In your code you will get a list of addresses you want to validate from your database. In this demo I have put some addresses in a list.

 
 List lstAddresses = new List() 
{ 
 "UNIT 7, 7 ERINDALE ROAD BALCATTA WA  6021",                            
 "226 MCINTYRE ROAD SUNSHINE VIC  3020",                           
 "UNIT 1 & 2, 12 PREMIER COURT WARANA QLD  4575",
 "PETERSHAM NSW  2049",
 "UNIT 7, 7 ERINDALE ROAD BALCATTA WA  6021",                            
 "226 MCINTYRE ROAD SUNSHINE VIC  3020",                           
 "UNIT 1 & 2, 12 PREMIER COURT WARANA QLD  4575",
 "WENTWORTHVILLE NSW  2049",
 "UNIT 7, 7 ERINDALE ROAD BALCATTA WA  6021",                            
 "MCINTYRE ROAD SUNSHINE VIC  3020",                           
 "UNIT 1 & 2, 12 PREMIER COURT WARANA QLD  4575",
 "PETERSHAM NSW  2049"
};

There is a limit of 10 API calls per second when you are calling Google Maps APIs so I am passing 10 addresses to Parallel.ForEach loop and calling Google Geocode API. This will parallalize API calls. Also there is a check if all API calls finish within one second wait for 1 second before calling next batch to avoid getting QUERY_OVER_LIMIT error.

 
Parallel.ForEach(selected, new ParallelOptions() { MaxDegreeOfParallelism = 10 }, sel =>
{
 try
 {
  string address = sel.ToString();
  Console.WriteLine("Address = " + address);
  GeoDetail objResult = GeoDetail.GetGeoDetails(key, address);
  lock (lockMe)
  {
   lstResult.Add(objResult);
  }

 }
 catch (Exception ex)
 {
  Console.WriteLine(ex.ToString());
 }

});

GetGeoDetails() method calls Google Maps API using HttpRequest and Get HttpResponse object back which then being converted to JSON object using JSON.Net library.

 
public static GeoDetail GetGeoDetails(string APIKey, string address)
{
 string uri = ConfigurationManager.AppSettings["URL"];
 GeoDetail objResult = new GeoDetail() { Address = address, Latitude = -1, Longitude = -1, AddressType = "", Error = "" };

 try
 {
  string requestURL = "";
  if (ConfigurationManager.AppSettings["IsGoogleMapsAPIPaid"].Trim() == "0")
  {
   requestURL = uri + address + "&key=" + APIKey; // No need to sign URL and there is no APIClient ID to pass.
  }
  else
  {
   requestURL = GoogleSignedUrl.Sign(uri + address + "&client=" + ConfigurationManager.AppSettings["APIClient"].Trim(), APIKey);
  }

  HttpWebRequest request = WebRequest.Create(requestURL) as HttpWebRequest;

  request.Accept = "application/json";

  // Get response  
  using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
  {
   if (response.StatusCode == HttpStatusCode.OK)
   {
    StreamReader readert = new StreamReader(response.GetResponseStream());
    string x = readert.ReadToEnd();
    JObject jObject = JObject.Parse(x);
    //Console.WriteLine(x);
    if (jObject["status"].ToString() == "OK")// successful API call
    {
     if (jObject["results"].Count() > 0)
     {
      string locationType = jObject["results"][0]["geometry"]["location_type"].ToString();

      string lat = jObject["results"][0]["geometry"]["location"]["lat"].ToString().Trim();
      string lng = jObject["results"][0]["geometry"]["location"]["lng"].ToString().Trim();

      Console.WriteLine("Geolocation lat lng :  {0}    {1}  Type  : {2}", lat, lng, locationType);

      double? latValue = null;
      double? lngValue = null;

      if (!string.IsNullOrWhiteSpace(lat))
      {
       latValue = double.Parse(lat);
      }
      if (!string.IsNullOrWhiteSpace(lng))
      {
       lngValue = double.Parse(lng);
      }

      objResult.Latitude = latValue;
      objResult.Longitude = lngValue;
      objResult.AddressType = locationType; //locationType == "ROOFTOP")//exact address match

     }
     else
     {
      Console.WriteLine("No result found");
      objResult.Error = "No result found";
     }
    }
    else
    {
     Console.WriteLine(jObject["status"].ToString());
     objResult.Error = jObject["status"].ToString();
    }


   }
  }

 }
 catch (Exception ex)
 {
  Console.WriteLine(ex.ToString());
  objResult.Error = ex.ToString();
 }

 return objResult;
}

Search all tables in MSSQL for a specific value in all columns

I came across this little snippet which creates a stored procedure that can be used to query all the tables in MSSQL database to search for specific value. It may be useful to someone looking for a similar solution

 
CREATE PROC SearchAllTables
(
 @SearchStr nvarchar(100)
)
AS
BEGIN

    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL

    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL

            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + 'WITH (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM #Results
END

Generic SQL Select Statement Executer in Java

In this post I will show you how to write a Java program that can allow you to run any SQL select statement against SQL Server and write output to csv file. While this is a simple task to use ResultSet and write output to file but imagine you need to keep on modifying output columns based on changes in business requirements or you need to output large number of SQL tables as csv files. In that case task gets little tedious and every time user request a new field to be added or removed you have to modify your class. This is where this trick comes handy.

You can download full source code from here : Generic SQL Select Statement Executer in Java

1. Lets create a new project in eclipse called SQLSelector and add a class file called GenericSelecor.java.

2. Next add sqljdbc4.jar and sqljdbc_auth.dll to your solution. This is required to connect to Microsoft SQL Server.

3. Add sqljdbc4.jar as a reference to your project by right clicking on project and going to properties and select java build path and libraries tab and click on Add Jar

4. Lets add two Properties file. One that is common for all SQL statements like database name,output folder etc and other one specific for current sql statement that will include Select statement, output file name and other specific information. I have Northwind database on my local machine and I want to select all customers from that database.

I have PROD.Properties file which contains database connection specific information.
Relative.Output.Folder=C\:\\SQLOutput\\
Server=localhost;instanceName=SQL2008
ODBC.DataSource=Northwind

I have Generic.Properties file which contains specific select statement and flag to indicate if string outputs should have double quotes
Statement=SELECT CustomerID ,CompanyName ,ContactName ,ContactTitle ,Address ,City ,Region ,PostalCode ,Country ,Phone ,Fax FROM Customers
OutputFileName = Customers.csv
StringOutputInDoubleQuote=Y

5. Now in GenericSelector class read those two properties files as arguments and read all the values in corresponding variables.

  
propertyFile = args[0];
statementFile= args[1];

Properties dpr = new Properties();
Properties spr = new Properties();

//Read all common variables from properties file
try 
{ 
 FileInputStream is = new FileInputStream(propertyFile);
 dpr.load(is);
 is.close();
   
 Server=dpr.getProperty("Server");
 DataSource=dpr.getProperty("ODBC.DataSource");
 
 OutputFolder=dpr.getProperty("Relative.Output.Folder");
 
}
catch(Exception e)
{
 e.printStackTrace();
 System.exit(1); 
}
// Read all specific variables from properties file
try 
{ 
 FileInputStream is = new FileInputStream(statementFile);
 spr.load(is);
 is.close();
 
 selectSatement =spr.getProperty("Statement"); // This is the select statement from external file
 outputFileName =spr.getProperty("OutputFileName");
 stringOutputInDoubleQuote = spr.getProperty("StringOutputInDoubleQuote").toUpperCase();
}
catch(Exception e)
{
 e.printStackTrace();
 System.exit(1);
 
}  

6.Load appropriate class driver and create connection to database. In this case I am using MSSQL server and connecting to Northwind database. Get a ResultSet by executing select statement. Get ResultSetMetaData from result set. This will help in identifying type of columns that are returned and name of those columns which then can be used to get the records from ResultSet without actually hard coding column names in application. ResultSetMetaData provides methods getColumnTypeName() and getColumnName() which are used to retrieve specific column information.

 
// Here we create a connection to database and run the query and then use metadata to generate csv output   
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String sqlCmd = "";
con = DriverManager.getConnection("jdbc:sqlserver://"+Server+";databaseName="+DataSource+";integratedSecurity=true");

sqlCmd = selectSatement;
PreparedStatement stat = con.prepareStatement(sqlCmd);
ResultSet rs = stat.executeQuery(); // Actual Data
ResultSetMetaData rsmd = rs.getMetaData(); // Metadata from current result set.

int columnCount = rsmd.getColumnCount();

String[] columnTypes = new String[columnCount]; 
String[] columnNames = new String[columnCount];

StringBuilder sb = new  StringBuilder();
rows=0;
// The column count starts from 1
for (int i = 1; i < columnCount + 1; i++ ) 
{
 columnTypes[i-1] = rsmd.getColumnTypeName(i); // Get Column Type Names
 String name = rsmd.getColumnName(i);  // Get actual column names
 columnNames[i-1] = name;
 sb.append(name);
 sb.append( i==columnCount ? "\n" : ",");
}                       

7. Now Iterate through each records in ResultSet and for each record find value by using its column name and column type that was retrieved using ResultSetMetaData and write to output file. Here I have checked for type of the column so I can use specific get method. If you don't require any formatting then you can use getObject() method of result set without worrying about underlying record type.

 
String DATETIME_FORMAT_QRY = "dd/MM/yyyy";
java.text.SimpleDateFormat sdf2 = new java.text.SimpleDateFormat(DATETIME_FORMAT_QRY);
   
while(rs.next())
{
 
 rows++;
 // Based on column type call appropriate getXXX() method to get value from database
 // If you don't need specifc formatting based on data type you can use getObject() method instead of specific get method
 for (int i = 1; i < columnCount + 1; i++ ) 
 {
   // If output column type is string type then either output it in double quote or as is based on flag 
   if(columnTypes[i-1]== "nchar" || columnTypes[i-1]== "nvarchar")
    sb.append((rs.getString(columnNames[i-1]) == null ? "" : (stringOutputInDoubleQuote.equals("Y") ? "\""+ rs.getString(columnNames[i-1]).trim() + "\"" : rs.getString(columnNames[i-1]).trim())));
   else if(columnTypes[i-1]== "decimal")    
    sb.append(rs.getDouble(columnNames[i-1]));
   else if(columnTypes[i-1]== "int" || columnTypes[i-1]== "tinyint")   
    sb.append(rs.getInt(columnNames[i-1]));
   else if(columnTypes[i-1]== "datetime")   
    sb.append(rs.getDate(columnNames[i-1])==null ? "" : sdf2.format(rs.getDate(columnNames[i-1])));
   else  
    sb.append("ERROR IN INTERFACE : No check added for "+columnNames[i-1] );
  
  sb.append( i==columnCount ? "\n" : ",");
 }
 

 // Write to file
 if(rows>0)
 {
  if(rows==1) // Delete old file
  {
   File delfile = new File(OutputFolder+"/"+outputFileName);
   delfile.delete();
  }
  FileOutputStream outFile = new FileOutputStream(OutputFolder+"/"+outputFileName,true);
  outFile.write(sb.toString().getBytes());
  outFile.close();
  sb = new StringBuilder(); // Reset string builder for next iteration
  
 }
 
}                        

That is it!!! You have a fully functional java code that can create any table as csv output. All you need to do is change SQL in Generic.Properties file. Happy Coding !!!

Only allow digits in Console Application in C#

If you are writing a console application in C# and you want to restrict user to only enter digits for certain variable there is an option to use ConsoleKeyInfo struct to read each key user input and take action accordingly. This struct provides a way to find which key user has entered in console application and check if it is a number or not using Char.IsNumber() method.
Below is the complete source code that only allow user to enter digits for a field. If user type any other characters it simply ignores them.

 
Console.WriteLine("Enter Numeric Value : ");
ConsoleKeyInfo key;
string inputStr = "";
do
{
 key = Console.ReadKey(true);
 if (key.Key != ConsoleKey.Backspace && key.Key != ConsoleKey.Enter)
 {
  if (char.IsNumber(key.KeyChar))//Check if it is a number
  {
   inputStr += key.KeyChar;
   Console.Write(key.KeyChar);
  }
 }
 else
 {
  if (key.Key == ConsoleKey.Backspace && inputStr.Length > 0)
  {
   inputStr = inputStr.Substring(0, (inputStr.Length - 1));
   Console.Write("\b \b");
  }
 }

} while (key.Key != ConsoleKey.Enter);

Console.WriteLine("\nNumber you entered is {0}", inputStr);

Find Distinct Objects from List of Objects using LINQ

To find distinct values from list of values in C# is a one line task by using LINQ's Distinct() method. This works well with primitive types but if you run the same method on List of custom Objects you will not get distinct objects based on its properties. To achieve this you an option to implement IEqualityComparer interface and use it to find distinct objects based on its properties. In the implementation of  Equals method you can define which properties to check for equality.

Below code provides the complete solution to get distinct objects from list of objects.

 
 using System.Collections.Generic;
    public class Team
    {
        public string Name {get;set;}
        public int Score {get;set;}
    }
        //Create some dummy data with duplicates
        public List<Team> lstTeam = new List<Team>{
        new Team{Name="Brazil", Score=1},
        new Team{Name="Man U", Score=1},
        new Team{Name="Man U", Score=1},
        new Team{Name="Brazil", Score=2},
        new Team{Name="Man U", Score=2},
        new Team{Name="Brazil", Score=2}

        };   

    //This is where we use equality comparer implementation to find unique records
     List<Team> lstDistictTeams = lstTeam.Distinct<Team>(new DistinctComparer()).ToList();

     foreach(Team t in lstDistictTeams) // Output Distinct Objects
     {
         Console.WriteLine("Team {0} has Score {1}",t.Name,t.Score);
     }

    //This class provides a way to compare two objects are equal or not
     public class DistinctComparer : IEqualityComparer<Team>
        {
            public bool Equals(Team x, Team y)
            {
                return (x.Name == y.Name && x.Score == y.Score); // Here you compare properties for equality
            }
            public int GetHashCode(Team obj)
            {
                return (obj.Name.GetHashCode() + obj.score.GetHashCode());
            }
        }