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)); }