Network Programming in .NET

Database mail allows you send email from SQL server, which is handy to report the status of jobs. It’s easy to set up, if you expand  Management, select Configure Database Mail, to set your SMTP credentials. Then press send Test Email to check it works.

If it doesn’t work, then, here was a issue I ran in to; if you run

EXEC msdb.dbo.sysmail_help_queue_sp

And both queues are marked as INACTIVE, then you may be missing .NET 3.5 from your server,

You can add .NET 3.5 via server managment, Add Roles and Features Wizard.

You may need to restart your SQL server service after installation.

Colombia is one of the largest automotive markets in South America, after Brazil and Argentina.

At the end of 2017 there were 13 million vehicles in Colombia, according to data from the Ministry of Transportation and the automotive association Andemos. Of those, 912,142 were registered for transportation (freight and passenger) and 7.4 million were motorcycles (57%). Not including motorcycles, 83% of Colombian vehicles are for private use, 16% are in public service, and one percent is for official use (7). According to research conducted by Business Monitor International in 2017, Colombia’s car ownership is still far from reaching saturation point. [Source: Export.gov]

We have launched an API that can return vehicle details from a number plate registered in Colombia at http://co.placaapi.com 

Car registration plates in Colombia use the /CheckColombia  endpoint and return the following information:

• Make & Model
• Age
• VIN
• Insurance details
• Owner

Sample Registration Number:

HBK482

Sample Json:

{

 “Description”: “FORD ECOSPORT [2] SE TP 2000CC 4X2”,

 “CarMake”: “FORD”,

 “CarModel”: “ECOSPORT [2] SE TP 2000CC 4X2”,

 “EngineSize”: “1999”,

 “NumberOfDoors”: “0”,

 “FuelType”: “COMBUSTIBLE SIN ESPECIFICAR”,

 “Vin”: “9BFZB55F6F8517816”,

 “VehicleType”: “CAMPEROS Y CAMIONETAS”,

 “MotorCode”: “AOJAF8517816”,

 “RegistrationYear”: “2015”,

 “ImageUrl”: “http://co.placaapi.com/image.aspx/@Rk9SRCBFQ09TUE9SVCBbMl0gU0UgVFAgMjAwMENDIDRYMg==”,

 “Insurance”: {

   “InsuranceCompany”: “SURAMERICANA”,

   “PolicyNumber”: “40006946171”,

   “FasecoldaCode”: “03006130”,

   “ValidityEndDate”: “03/06/2018 00:00:00”,

   “ValidityStartDate”: “03/06/2017 00:00:00”,

   “DocumentId”: “79643987”,

   “DocumentType”: “CC”,

   “Owner”: “JARAMILLO RUBIO  WILLIAM”

 }

}

 

Ecuador is a country with almost 2 million vehicles registered via the AMT, and we now offer an API that allows users query the make, model and age of a vehicle registered in Ecuador, via it’s number plate, at the website ec.placaapi.com

Car registration plates in Ecuador use the /CheckEcuador  endpoint and return the following information:

• Make & Model
• Age
• Representative image

Sample Registration Number:

PBU2742

Sample Json:

{

 “Description”: “JEEP RENEGADE”,

 “CarMake”: {

   “CurrentTextValue”: “JEEP”

 },

 “CarModel”: {

   “CurrentTextValue”: “RENEGADE”

 },

 “MakeDescription”: {

   “CurrentTextValue”: “JEEP”

 },

 “ModelDescription”: {

   “CurrentTextValue”: “RENEGADE”

 },

 “Year”: “1979”,

 “Type”: “LIVIANO”,

 “Subtype”: “AUTOMOVIL 4 TIEMPOS”,

 “ImageUrl”: “http://ec.placaapi.com/image.aspx/@SkVFUCBSRU5FR0FERQ==”

}

This brings our south american coverage to the following countries:

Bolivia has a relatively low motor vehicles per capita ratio of only 70 vehicles per 1,000, – meaning the total road population would be only 762,000 – however, for businesses selling into Bolivia, having an API that can quickly and accurately determine the make, model, colour, age, and policy number is a great time saver for users. – We’ve just launched our bolivian website today on http://bo.placaapi.com

Car registration plates in Bolivia  use the /CheckBolivia endpoint and return the following information:

• Make & Model
• Age
• Representative image
• Policy Number
• Colour
• and more…

Sample Registration Number:

3054SXI

Sample Json:

{

 “Description”: “FORD FIESTA”,

 “RegistrationYear”: “2014”,

 “CarMake”: {

   “CurrentTextValue”: “FORD”

 },

 “CarModel”: {

   “CurrentTextValue”: “FIESTA”

 },

 “MakeDescription”: {

   “CurrentTextValue”: “FORD”

 },

 “ModelDescription”: {

   “CurrentTextValue”: “FIESTA”

 },

 “Policy”: “140447810”,

 “Class”: “AUTOMOVIL”,

 “Country”: “MEXICO”,

 “Service”: “PARTICULAR”,

 “Drive”: “4 X 2 (SIMPLE)”,

 “CC”: “1600”,

 “Colour”: “ROJO METALICO”,

 “Body”: “–“,

 “Load”: “0”,

 “Doors”: “5”,

 “Location”: “LA PAZ”,

 “VehicleType”: “REEMPLACADO”,

 “ImageUrl”: “http://bo.placaapi.com/image.aspx/@Rk9SRCBGSUVTVEE=”

}

 

Does your business sell car parts, car services, car insurance, or used cars in Peru? this API will save your users time, and give you more accurate information about the car that the user owns, or intends to purchase.

You can open a free account at http://pe.placaapi.com/ – and get 10 free credits to search for number plates registered in Peru. The data returned will include the make, model, VIN, age, and current owner of the vehicle.

Car registration plates in Peru use the /CheckPeru  endpoint and return the following information:

• Make & Model
• Age
• Representative image
• Owner
• VIN number

Sample Registration Number:

B6U175

Sample Json:

{

 “Description”: “HYUNDAI TUCSON”,

 “RegistrationYear”: “2012”,

 “CarMake”: {

   “CurrentTextValue”: “HYUNDAI”

 },

 “CarModel”: {

   “CurrentTextValue”: “TUCSON”

 },

 “MakeDescription”: {

   “CurrentTextValue”: “HYUNDAI”

 },

 “ModelDescription”: {

   “CurrentTextValue”: “TUCSON”

 },

 “DeliveryPoint”: “ASOCIACIÓN AUTOMOTRIZ DEL PERÚ – LIMA”,

 “Date”: “10/05/2012 16:18:48”,

 “VIN”: “KMHJT81BBCU481538”,

 “Make”: “HYUNDAI”,

 “Model”: “TUCSON”,

 “Owner”: “TOKASHIKI TOKASHIKI JENNY / CORNEJO CORRALES CARLOS ALFREDO”,

 “Use”: “Vehiculos Particulares ( Categoria M )”,

 “ImageUrl”: “http://pe.placaapi.com/image.aspx/@SFlVTkRBSSBUVUNTT04=”

}

Yes, it’s possible to connect to Microsoft SQL Server from Node JS, even on a Mac OSX environment.

First, install msssql via NPM as follows

npm install mssql

Then, you can write some node code as follows;

const sql = require(‘mssql’)

const config = {
user: ‘xxxxx’,
password: ‘xxxxx’,
server: ‘yoursqlserver.com’,
database: ‘master’,
options: {
encrypt: true
}
}
sql.connect(config, err => {
console.log(err);
new sql.Request().query(‘select @@version’, (err, result) => {
console.dir(result)
});
});

sql.on(‘error’, err => {
console.log(err);
});

Which, in my case gives the output;

{ recordsets: [ [ [Object] ] ],

  recordset:

   [ { ”:

        ‘Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) – 13.0.1742.0 (X64) \n\tJul  5 2017 23:41:17 \n\tCopyright (c) Microsoft Corporation\n\tEnterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )\n’ } ],

  output: {},

  rowsAffected: [ 1 ] }

There are many myths surrounding SEO, some completely unfounded, some educated guesswork – someone might say to wrap your key terms in bold tags, does that work?, who knows. It’s an educated guess. Google keeps it’s ranking algorithm top secret, so weighting factors are hard to know.

However, when you get an email from Google saying that you’ve made a mistake with an SEO feature, that *HAS* to mean that Google is paying attention to it, and has given it enough weight to send out emails about it.

So, the feature is hreflang tags, they tell google if there is a country/language specific version of the website that might be more applicable to a user from that country, for example

<link href=”http://ie.carregistrationapi.com” hreflang=”en-IE” rel=”alternate” type=”text/html”/>

<link href=”http://www.vehicleregistrationapi.com” hreflang=”en-US” rel=”alternate” type=”text/html”/>

Tells google that I want Irish visitors to go to ie.carregistrationapi.com and US visitors to go to VehicleRegistrationAPI.com, so Google can adapt the results accordingly. Since both websites are in english, it might be otherwise hard for Google to determine which of the two is more applicable  – of course I’ve set the International targeting in Webmaster tools to prefer Irish and US respectively … but this is another re-enforcing step.

Now, the interesting thing is, that if you don’t set up reciprocal hreflang tags, so if you don’t hreflang-link back from Ireland to US, but you do in the opposite direction, and you’ve registered your website with Google webmaster tools, then you get a warning, like this:

Search Console

Errors in hreflang tags on http://www.placaapi.com/

You’re almost there

We found the hreflang tags on your site, but a few of them have technical problems. Take a look at our hreflang report to see which of your hreflang tags have issues. Until you fix these tag errors, your website will not benefit from additional language or region targeting.

 

The fix is simple, you just set up reciprocal hreflang-links, but what’s interesting, is that Google is treating this with a similar level of importance to 404 errors, or mobile friendliness, which are obvious ranking factors.

Mexico, a country with 43 million motor vehicles, is the latest south american country to be added to our growing coverage of vehicle number plate lookup APIs. This API allows you to input a car number plate in Mexico, and get the vehicle information in response.

Car registration plates in Mexico use the /CheckMexico endpoint and return the following information:

• Make & Model
• Age
• Representative image
• Engine
• NCI
• VIN number

Sample Registration Number:
JGS9180
Sample Json:

{
“Description”: “NISSAN SENTRA”,
“RegistrationYear”: “2007”,
“CarMake”: {
“CurrentTextValue”: “NISSAN”
},
“CarModel”: {
“CurrentTextValue”: “SENTRA”
},
“MakeDescription”: {
“CurrentTextValue”: “NISSAN”
},
“ModelDescription”: {
“CurrentTextValue”: “SENTRA”
},
“Class”: “AUTOMOVIL”,
“Type”: “SEDAN”,
“VIN”: “3N1AB61D17L630743”,
“NCI”: “0A3J8GO2”,
“Doors”: “4”,
“Country”: “MEXICO”,
“Version”: “CUSTOM 20 TM”,
“EngineSize”: “2.0L”,
“Cylinders”: “L4”,
“Axles”: “”,
“AssemblyPlant”: “AGUASCALIENTES, MEXICO”,
“Institution”: “”,
“RegistrationDate”: “03/07/2007”,
“IssueDate”: “20/01/2012”,
“LastUpdate”: “12/01/2017”,
“CertifiateOfRegistration”: “”,
“Observations”: “”,
“ImageUrl”: “http://mx.placaapi.com/image.aspx/@TklTU0FOIFNFTlRSQQ==&#8221;
}

Get a free account at http://mx.placaapi.com

With a driving population of over 90 million, brazil is the largest country in south america, and via www.placaapi.com you can now determine make, model, VIN, location and other technical information from a vehicle registered in Brazil via its number plate.

Car registration plates in Brazil use the /CheckBrazil endpoint and return the following information:

• Make & Model
• Age
• Representative image
• Colour
• Fuel
• Location
• VIN number

Sample Registration Number:

KNX9595

Sample Json:

{
  "Description": "GM - CHEVROLET VECTRA GT 2.0 MPFI 8V FLEXPOWER MEC.",
  "RegistrationYear": "2011",
  "CarMake": {
    "CurrentTextValue": "CHEVROLET"
  },
  "CarModel": {
    "CurrentTextValue": "Vectra GT 2.0 MPFI 8V FlexPower Mec."
  },
  "MakeDescription": {
    "CurrentTextValue": "CHEVROLET"
  },
"ModelDescription": {
"CurrentTextValue": "Vectra GT 2.0 MPFI 8V FlexPower Mec."
},
"ImageUrl": "http://www.placaapi.com/image.aspx/@Q0hFVlJPTEVUIFZlY3RyYSBHVCAyLjAgTVBGSSA4ViBGbGV4UG93ZXIgTWVjLg==",
"Location": "SAO LUIS, MA",
"Vin": "9BGAJ48C0BB209328",
"Fuel": "ALCOOL / GASOLINA",
"Colour": "AZUL",
"Power": "140",
"EngineCC": "2000",
"Type": "PASSAGEIRO",
"Seats": "5",
"Axles": "{}",
"GrossWeight": "180",
"MaxTraction": "230"
}

If you have domains registered with Namecheap, and you’d like a programmatic way to update DNS setttings – i.e. you want to change the IP address of a domain automatically in the event of a server outage, here’s how their API works.

First, you have to active the API via the control panel as follows;

1. Login to your Namecheap account.
2. Go to the Profile > Tools menu.
3. Scroll down to the Business & Dev Tools section.
4. Click MANAGE, next to Namecheap API Access.
5. Toggle ON/OFF, read the Terms of Service
6. Enter your account password.

Then add IP addresses that you are going to use to access the API. i.e. your local IP, and server IP.

When you call the command namecheap.domains.dns.setHosts on the namecheap API, it overwrites all existing DNS records, so you need to first read all the DNS records on a domain, then set them all back again, plus your change. So first step is to read all DNS records.

So, create a class to hold a DNS record

class HostRecord
{
public string Name { get; set; }
public string Type { get; set; }
public string Value { get; set; }

public override string ToString()
{
return string.Format(“[{0}] {1} : {2}”, Name, Type, Value);
}
}

The ToString() is just for debug purposes

Another helper method we need is some code to get our current IP, which is required by the API

private static string GetIP()
{
var wc = new WebClient();
var strIP = wc.DownloadString(“http://icanhazip.com&#8221;).Trim();
return strIP;
}

To get DNS hosts for a Domain registered on Namecheap;

private static List<HostRecord> GetHostsFromDomain(string SLD, string TLD)
{
var strUrl = “https://api.namecheap.com/xml.response?&#8221;;
strUrl += “ApiUser=….”;
strUrl += “&ApiKey=…..”;
strUrl += “&UserName=…..”;
strUrl += “&Command=namecheap.domains.dns.getHosts”;
strUrl += “&ClientIp=” + GetIP();
strUrl += “&SLD=” + SLD;
strUrl += “&TLD=” + TLD;
var wc = new WebClient();
var strXml = wc.DownloadString(strUrl);
XmlDocument xdoc = new XmlDocument();
xdoc.LoadXml(strXml);
XmlNamespaceManager ns = new XmlNamespaceManager(xdoc.NameTable);
ns.AddNamespace(“xxx”, “http://api.namecheap.com/xml.response&#8221;);
var hosts = xdoc.SelectNodes(“//xxx:host”, ns);
var lHosts = new List<HostRecord>();
Console.WriteLine(“Previous DNS Records”);
foreach (XmlNode host in hosts)
{
var record = new HostRecord()
{
Name = host.Attributes[“Name”].Value,
Type = host.Attributes[“Type”].Value,
Value = host.Attributes[“Address”].Value
};
lHosts.Add(record);
Console.WriteLine(record);
}
return lHosts;
}

If your domain is “boo.com”, then SLD is “boo” and TLD is “com”.

Once you have the list of Hostrecords, you can add or modify this collection, and then update the hosts as follows;

public static string SetHostRecords(string SLD, string TLD, List<HostRecord> records)
{
var strUrl2 = “https://api.namecheap.com/xml.response&#8221;;
string strPostData = “ApiUser=….”;
strPostData += “&ApiKey=….”;
strPostData += “&UserName=….”;
strPostData += “&Command=namecheap.domains.dns.setHosts”;
strPostData += “&ClientIp=” + GetIP();
strPostData += “&SLD=” + SLD;
strPostData += “&TLD=” + TLD;
for (int i = 0; i< records.Count; i++)
{
strPostData += “&HostName” + (i+1) + “=” + records[i].Name;
strPostData += “&RecordType” + (i+1) + “=” + records[i].Type;
strPostData += “&Address” + (i+1) + “=” + records[i].Value;
strPostData += “&TTL” + (i+1) + “=60”;
}
var wc2 = new WebClient();
wc2.Headers[“Content-Type”] = “application/x-www-form-urlencoded”;
var strXml2 = wc2.UploadString(strUrl2,strPostData);
return strXml2;
}

If you want to create a console app for this, where you can pass in command line arguments, then here’s some sample code for this;

var strSLD = args[0];
var strTLD = args[1];
var strName = args[2];
var strAddress = args[3];
var hosts = GetHostsFromDomain(strSLD, strTLD);
if (hosts.Any(h => h.Name == strName))
{
Console.WriteLine(“Updating Record “+ strName);
hosts.First(h => h.Name == strName).Value = strAddress;
}
else
{
Console.WriteLine(“Adding Record ” + strName);
hosts.Add(new HostRecord() { Type = “A”, Name = strName, Value = strAddress });
}
var strResponse = SetHostRecords(strSLD, strTLD, hosts);
Console.WriteLine(strResponse);

This only updates A records, you could modify the code to also update MX records, if you need to change email providers.