protected static T ExecuteQuery <T>(Database db, SqlDataReaderHandler handler, int commandTimeoutSeconds, string parameterizedSql, params object[] parameters)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader rdr = null;
try
{
//log.Debug("ExecuteQuery: " + sql);
conn = GetConnection(db);
conn.Open();
cmd = new SqlCommand(parameterizedSql, conn);
cmd.CommandTimeout = commandTimeoutSeconds;
for (int i = 0; i < parameters.Length; i++)
{
//log.Debug("Param " + i + " = " + param[i]);
cmd.Parameters.Add(new SqlParameter("@" + i, parameters[i]));
}
rdr = cmd.ExecuteReader();
return((T)handler(rdr));
}
catch (Exception ex)
{
log.Error("Error executing sql: " + parameterizedSql, ex);
throw new ApplicationException("Error executing sql: " + parameterizedSql, ex);
}
finally
{
Close(conn, cmd, rdr);
}
}
public IList <CanadianLocatorResult> GetDealers(double latitude, double longitude, int resultsPerPage, int pageNumber, object[] param)
{
// NOTE: Results were multiplied by 1.609344 to convert from MI to KM
var sql = new StringBuilder();
sql.AppendLine(" SELECT * FROM ");
sql.AppendLine(" ( ");
sql.AppendLine(" SELECT TOP " + resultsPerPage + " * FROM ");
sql.AppendLine(" ( ");
sql.AppendLine(" SELECT TOP " + (resultsPerPage * pageNumber) + " ");
sql.AppendLine(" min(id) AS id, company, address, city, province, postal_code, phone, website, distanceKilometers ");
sql.AppendLine(" FROM ");
sql.AppendLine(" ( ");
sql.AppendLine(" SELECT ");
sql.AppendLine(" D.id, D.product, D.company, D.address, D.city, D.province, D.postal_code, D.phone, D.website, ");
sql.AppendLine(" (((SQRT((SQUARE((@1) - DZ.longitude) + ");
sql.AppendLine(" SQUARE((@0) - DZ.latitude)))) * 60) * 1.15078 * 1.609344) ");
sql.AppendLine(" AS distanceKilometers ");
sql.AppendLine(" FROM ZipPostalCodes DZ (NOLOCK) ");
sql.AppendLine(" INNER JOIN " + dealerLocatorDb + "CanadaDealersInfo D (NOLOCK) ");
sql.AppendLine(" ON DZ.PostalCode = D.postal_code ");
for (var i = 2; i < param.Length; i++)
{
if (i == 2)
{
sql.Append(" WHERE (product=@" + i);
}
else
{
sql.Append(" OR product=@" + i);
}
if ((i + 1) == param.Length)
{
sql.AppendLine(" ) ");
}
}
sql.AppendLine(" ) AS inner1 ");
sql.AppendLine(" GROUP BY company, address, city, province, postal_code, phone, website, distanceKilometers ");
sql.AppendLine(" ORDER BY distanceKilometers, id ");
sql.AppendLine(" ) AS inner2 ");
sql.AppendLine(" ORDER BY distanceKilometers desc, id desc ");
sql.AppendLine(" ) AS inner3 ");
sql.AppendLine(" ORDER BY distanceKilometers, id ");
SqlDataReaderHandler handler = delegate(SqlDataReader rdr)
{
IList <CanadianLocatorResult> list = new List <CanadianLocatorResult>();
while (rdr.Read())
{
list.Add(BuildDealerItem(rdr));
}
return(list);
};
return(ExecuteQuery <IList <CanadianLocatorResult> >(LocatorDatabase.Common, handler, sql.ToString(), param));
}
/// <summary>
/// Counts the records in the specified table and database
/// </summary>
/// <param name="db"></param>
/// <param name="tableName"></param>
/// <returns></returns>
protected static int CountTable(Database db, String tableName)
{
StringBuilder sql = new StringBuilder();
int count = 0;
sql.AppendLine(" select count(*) as total ");
sql.AppendLine(" from " + tableName);
SqlDataReaderHandler handler = delegate(SqlDataReader rdr)
{
if (rdr.HasRows)
{
if (rdr.Read())
{
count = rdr.GetInt32(rdr.GetOrdinal("total"));
}
}
return(count);
};
return(ExecuteQuery <int>(db, handler, sql));
}
public void PopulateLatLongFromZip(CanadaLocatorSearchOptions searchOptions)
{
var sql = new StringBuilder();
sql.AppendLine("select Latitude, Longitude ");
sql.AppendLine("FROM ZipPostalCodes DZ (NOLOCK) ");
sql.AppendLine("where PostalCode = @0 ");
SqlDataReaderHandler handler = delegate(SqlDataReader rdr)
{
Boolean isData = false;
if (rdr.HasRows)
{
if (rdr.Read())
{
searchOptions.Latitude = (rdr.IsDBNull(rdr.GetOrdinal("Latitude")) ? 0.0 : double.Parse(rdr[rdr.GetOrdinal("Latitude")].ToString()));
searchOptions.Longitude = (rdr.IsDBNull(rdr.GetOrdinal("Longitude")) ? 0.0 : double.Parse(rdr[rdr.GetOrdinal("Longitude")].ToString()));
}
}
return(isData);
};
ExecuteQuery <Boolean>(LocatorDatabase.Common, handler, sql.ToString(), searchOptions.PostalCode);
}
protected static T ExecuteQuery <T>(Database db, SqlDataReaderHandler handler, string parameterizedSql, params object[] parameters)
{
// 30 seconds is default CommandTimeout value
return(ExecuteQuery <T>(db, handler, 30, parameterizedSql, parameters));
}
protected static T ExecuteQuery <T>(Database db, SqlDataReaderHandler handler, int commandTimeoutSeconds, StringBuilder parameterizedSql, params object[] parameters)
{
return(ExecuteQuery <T>(db, handler, commandTimeoutSeconds, parameterizedSql.ToString(), parameters));
}
