Skip to content

How to do a Select SQL GROUP BY for number/score ranges?

Hi folks!! Long time no post!  This is a very useful piece of script for collating the set of ranges from a list of numeric data that is groups them on a set of scores/range values, something that may be coherent for ranged reports.

score      |   scorelevel
   0.2      |       C 
   0.5     |        C 
   1.5     |        B 
  1.6       |       B 
  2.8       |       A 
  3.6       |       A+
  1.7       |       B 
  2.5       |       A 
  2.8       |       A 
  0.6       |       C 

select t.range as [score range], count(*) as [number of occurences]
from (
select case
 when score between 0 and 1 then ‘0-1’
when score between 1 and 2 then ‘1-2’
when score between 2 and 3 then ‘2-3’
else ‘3+’ end as range
from scores) t
group by t.range




How to write a Polling Service to check by regular intervals in C#

A very simple basic template for writing a How to write a Polling Service to check by regular intervals in C# which I customise slightly further for usage from researching the StackOverflow blog, this is useful for my case checking the database, rather than embedding this process into an existing main Windows service, we can utilise this as a seperate service for smaller tasks to check the SQL database records or particular records, very useful for having a fairly light intensive process.

Here is the generic templated code: –

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace PollingService
public partial class PollingService : ServiceBase
private CancellationTokenSource cts = new CancellationTokenSource();
private Task mainTask = null;

public PollingService()

protected override void OnStart(string[] args)
mainTask = new Task(Poll, cts.Token, TaskCreationOptions.LongRunning);

protected override void OnStop()

private void Poll()
CancellationToken cancellation = cts.Token;
TimeSpan interval = TimeSpan.Zero;
while (!cancellation.WaitHandle.WaitOne(interval))
// Poll here
// Ocassionally check the cancellation state.
if (cancellation.IsCancellationRequested)
interval = TimeSpan.FromMinutes((Convert.ToDouble(ConfigurationSettings.AppSettings[“WaitAfterSuccessInterval”])));
catch (Exception)
// Log the exception
//string fromTimeString = result.ToString(“hh’:’mm”);
//interval = Convert.ToDouble(ConfigurationSettings.AppSettings[“WaitAfterSuccessInterval”]);
//interval = TimeSpan.FromMinutes((ConfigurationSettings.AppSettings[“WaitAfterSuccessInterval”]));
interval = TimeSpan.FromMinutes((Convert.ToDouble(ConfigurationSettings.AppSettings[“WaitAfterSuccessInterval”])));
//TimeSpan result = TimeSpan.FromMinutes(interval);

Method has no supported translation to SQL

I have encountered many LINQ to SQL errors recently where I have predominately been linking objects from the dbml file to the backend C# code with much experimentation. If you are translating the C# code for LINQ to SQL, you may stumble upon this error: –

Method ‘Project.Model.DrivingLicence GetDataRow(Project.Data.Objects.CheckDrivingLicence_view)’ has no supported translation to SQL.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NotSupportedException: Method ‘Project.Model.DrivingLicence GetDataRow(Project.Data.Objects.CheckDrivingLicence_view)’ has no supported translation to SQL.

If so, then the issue is that the method you wrote, in this situation ,  the method name GetDataRow cannot be translated to the relevent SQL code even though it is configured correctly on the DBML file as I have found out.

You will often need to write an alternative method than the GetDataRow as specified: –

public static Model.GetDrivingLicence GetDataRow(Project.Data.Objects.DrivingLicences_view entity)
return new Model.DrivingLicence()
LicenceNumber = entity.LicenceNumber,
LicenceTypeId = entity.LicenceTypeID.Value,
LicenceStatusId = entity.LicenceStatusID,
InsuranceCategoryId = entity.InsuranceCategoryID.Value,
InsuranceCategory= entity.InsuranceCategory,
Active = entity.Active.Value,
Description = entity.Description,
CarType = entity.CarType,

CarModel = entity.CarModel,

CarManufacturer = entity.CarManufacturer


You will often need to write a method that reads off the database using LINQ to SQL than picking up as entity which may have an issue with the naming of the columns.

Using SQL Management Studio Execution Plan

Its been a long while since my last programming post, but I have been doing a lot of SQL work on the backend over the last few months.

I had been told on Monday by a colleague I had rarely spoken to about using the SQL Profiler a lot more as it involves timing individual SQL queries/executions in the SQKL database, it is soemthing I always oversee. However trying to use this relatively unknown tool that I keep forgetting on a Monday morning didn’t work, it was perhaps I did a SQL database upgrade to 2014 but the dlls were missing.

Instead he told me to use the Execution plan, what is this? It is a detailed overview of the components of an SQL statement you wrote which analyses the cost % ratio of the total execution query, which I find equally as good as the SQL Profiler. See the screenshot below.

Execution Plan

As you can see in the screenshot diagram, the last query key lookup clustered index is suggested! Yes it makes suggestions to adding the query, in this situation it is to add a clustered index on several of the fields for a particular table to potentially improve the speed of the lookup of this complex query! Thanks!!!


Scale mobile screen to size without zoom

Heres a useful bit of code taken from David Walsh ever so useful website that is a metag to to prevent any web pages on the mobile from zooming in and out from within its defined scale.


<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=0"/> <!--320-->

That’s as easy as it gets. According to Mozilla, mobile browsers are generally set to a size of 320 or 480. The maximum scale keeps the scale settings when the user switches from portrait to landscape view

My Super Generic Webservice DataTable to serialisable XML string method call Part 2

Further upon the super generic webservice method I wrote, on the web service side end, you can obtain the Stream fairly easily and convert to a datatable, here is how: –

public string getXMLStream(Stream data)
    // Convert Stream Data to StreamReader
    StreamReader reader = new StreamReader(data);

    // Read StreamReader data as String
    string xmlString = reader.ReadToEnd();
    // Convert to DataTable from XML String
    DataTable dataTable = ConvertXMLToDataTable(xmlString);

    string returnValue = xmlString;
The rather useful converter is also shown here.

public DataTable ConvertXMLTODataTable(string xmlData)
    StringReader reader = new StringReader(xmlData);
    DataSet dataSet = new DataSet();
    schemaName = dataSet.DataSetName;
    return dataSet.Tables[0];

My Super Generic Webservice DataTable to serialisable XML string method call

OK I couldn’t hold back as to how super this C# method I wrote yesterday and would like you guys to know about.

Essentially what it does is that it reads in an SQL database tables, converts ii into a DataTable using SQL Data Adapter then the very clever bit is that it convert it to a MemorySteam that is XML serialisable over the HTTP to WCF Data Services for it to be received on the other end as a Stream and serialised back to DataTable again! Its fantastic, and its so generic, you can use this time and time again. Hope its useful for you!

public string Add(string webServiceCall, string sourceTable, string destinationTable)
 // Restful service URL
 string url = readServiceUri().ToString() + webServiceCall;
// declare ascii encoding
 ASCIIEncoding encoding = new ASCIIEncoding();
 string strResult = string.Empty;
 string xmlResult = "";
DataSet dataSet = new DataSet();
 DataTable dataTable = new DataTable();
 dataTable.TableName = destinationTable;
 using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString))
 SqlCommand cmd = new SqlCommand("SELECT * FROM " + sourceTable, conn);
cmd.CommandType = CommandType.Text;
 SqlDataAdapter adapter = new SqlDataAdapter();
 adapter.SelectCommand = cmd;
 catch (Exception ex)
 throw new Exception("ERROR " + ex);
// Important logical conversion to XML string
 using (MemoryStream memoryStream = new MemoryStream())
 using (TextWriter streamWriter = new StreamWriter(memoryStream))
 XmlSerializer xmlSerializer = new XmlSerializer(typeof(DataSet));
 xmlSerializer.Serialize(streamWriter, dataSet);
 xmlResult = Encoding.UTF8.GetString(memoryStream.ToArray());
string postData = xmlResult.ToString();
 // Convert xmlstring to byte using ascii encoding
 byte[] data = encoding.GetBytes(postData);
 // Declare httpwebrequet wrt url defined above
 HttpWebRequest webRequest = (HttpWebRequest)WebRequest.Create(url);
 // Set method as post
 webRequest.Method = "POST";
 // Set content type
 webRequest.ContentType = "application/x-www-form-urlencoded";
 // Set content length
 webRequest.ContentLength = data.Length;
 // Set timeout
 webRequest.Timeout = 60000;
 // Get stream data out of webrequest object
 Stream newStream = webRequest.GetRequestStream();
 newStream.Write(data, 0, data.Length);
 // Declare & read response from service
 HttpWebResponse webResponse = (HttpWebResponse)webRequest.GetResponse();
 if (webResponse.StatusCode == HttpStatusCode.OK)
 // set utf8 encoding
 Encoding enc = System.Text.Encoding.GetEncoding("utf-8");
 // read response stream from response object
 StreamReader loResponseStream =
 new StreamReader(webResponse.GetResponseStream(), enc);
 // read string from stream data
 strResult = loResponseStream.ReadToEnd();
 // close the stream object
 // close the response object
 // below steps remove unwanted data from response string
 strResult = strResult.Replace("</string>", "");
if (strResult.Contains("OK"))
 return "OK";
 return "FAIL";
 return "FAIL";
 catch (Exception ex)
 throw new Exception("ERROR " + ex);