private static ONSPDRegionSubregion GetRegionSubRegionByPostcodeSql(string Postcode, SqlConnection sqlConnection)
{
var onspdRegionSubregion = new ONSPDRegionSubregion();
using (var command = sqlConnection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dfc_GetRegionSubRegionByPostcode";
command.Parameters.Add(new SqlParameter("@Postcode", SqlDbType.NVarChar, 50));
command.Parameters["@Postcode"].Value = Postcode;
//Open connection.
sqlConnection.Open();
using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
onspdRegionSubregion = ExtractONSPDRegionSubregionFromDbReader(dataReader);
}
// Close the SqlDataReader.
dataReader.Close();
}
}
sqlConnection.Close();
return(onspdRegionSubregion);
}
public static ONSPDRegionSubregion GetRegionSubRegionByPostcode(string Postcode, string connectionString, out string errorMessageGetRegionSubRegion)
{
var onspdRegionSubregion = new ONSPDRegionSubregion();
errorMessageGetRegionSubRegion = string.Empty;
using (var sqlConnection = new SqlConnection(connectionString))
{
try
{
onspdRegionSubregion = GetRegionSubRegionByPostcodeSql(Postcode, sqlConnection);
if (string.IsNullOrEmpty(onspdRegionSubregion.Region))
{
onspdRegionSubregion = GetRegionSubRegionByPostcodeSql(Postcode.Replace(" ", ""), sqlConnection);
}
}
catch (Exception ex)
{
errorMessageGetRegionSubRegion = string.Format("Error Message: {0}" + Environment.NewLine + "Stack Trace: {1}", ex.Message, ex.StackTrace);
sqlConnection.Close();
}
}
onspdRegionSubregion.Postcode = Postcode;
return(onspdRegionSubregion);
}
public static ONSPDRegionSubregion ExtractONSPDRegionSubregionFromDbReader(SqlDataReader reader)
{
ONSPDRegionSubregion onspdRegionSubregion = new ONSPDRegionSubregion();
onspdRegionSubregion.Region = (string)CheckForDbNull(reader["Region"], string.Empty);
onspdRegionSubregion.SubRegion = (string)CheckForDbNull(reader["SubRegion"], string.Empty);
return(onspdRegionSubregion);
}
