/// <summary>
/// Gets sorted list of all Business Locations in the dataset by filter
/// </summary>
/// <param name="SearchQuery">Query to filter on</param>
/// <param name="PageNumber">Current page number</param>
/// <param name="PageSize">Number of items per page</param>
/// <param name="OrderBy">Column name to sequence the list by</param>
/// <param name="OrderByAscDesc">Sort direction</param>
/// <returns>object PagedList</returns>
public static PagedList<BusinessLocation> GetBusinessLocations(string SearchQuery, int PageNumber, int PageSize, string OrderBy, bool OrderByAscDesc)
{
//Create client to talk to OpenDat API Endpoint
var client = new SodaClient(_APIEndPointHost, _AppToken);
//get a reference to the resource itself the result (a Resouce object) is a generic type
//the type parameter represents the underlying rows of the resource
var dataset = client.GetResource <PagedList<BusinessLocation>>(_APIEndPoint4x4);
//Build the select list of columns for the SoQL call
string[] columns = new[] { "legal_name", "doing_business_as_name", "date_issued", "city", "state", "zip_code", "latitude", "longitude" };
//Column alias must not collide with input column name, i.e. don't alias 'city' as 'city'
string[] aliases = new[] { "LegalName", "DBA", "IssuedOn" };
//using SoQL and a fluent query building syntax
var soql = new SoqlQuery().Select(columns)
.As(aliases)
.Order((OrderByAscDesc) ? SoqlOrderDirection.ASC: SoqlOrderDirection.DESC, new[] { OrderBy });
if(!string.IsNullOrEmpty(SearchQuery))
{
soql = new SoqlQuery().FullTextSearch(SearchQuery);
}
var results = dataset.Query<BusinessLocation>(soql);
//page'em cause there might be quite a few
PagedList<BusinessLocation> pagedResults = new PagedList<BusinessLocation>(results.ToList(), PageNumber, PageSize);
return pagedResults;
}
public void Default_Ctor_Orders_By_DefaultOrder_In_DefaultOrderDirection()
{
string defaultOrderClause = String.Format("{0}={1} {2}", SoqlQuery.OrderKey, String.Join(SoqlQuery.Delimiter, SoqlQuery.DefaultOrder), SoqlQuery.DefaultOrderDirection);
string soql = new SoqlQuery().ToString();
StringAssert.Contains(defaultOrderClause, soql);
}
public void Select_Clause_Only_Gets_Valid_Columns(params string[] columns)
{
string expected = String.Format("{0}={1}", SoqlQuery.SelectKey, String.Join(SoqlQuery.Delimiter, columns.Where(c => !String.IsNullOrEmpty(c))));
string soql = new SoqlQuery().Select(columns).ToString();
StringAssert.Contains(expected, soql);
}
public void Default_Ctor_Selects_Default()
{
string defaultSelectClause = String.Format("{0}={1}", SoqlQuery.SelectKey, String.Join(SoqlQuery.Delimiter, SoqlQuery.DefaultSelect));
string soql = new SoqlQuery().ToString();
StringAssert.Contains(defaultSelectClause, soql);
}
public void Empty_FullTextSearch_Ignores_Search_Clause()
{
string startOfSearchClause = String.Format("{0}=", SoqlQuery.SearchKey);
string emptySearch = new SoqlQuery().FullTextSearch("").ToString();
string nullSearch = new SoqlQuery().FullTextSearch(null).ToString();
StringAssert.DoesNotContain(startOfSearchClause, emptySearch);
StringAssert.DoesNotContain(startOfSearchClause, nullSearch);
}
public void Query_Ctor_Sets_RawQuery()
{
var query = "SELECT something WHERE this > that ORDER BY another";
var expected = String.Format("{0}={1}", SoqlQuery.QueryKey, query);
var soql = new SoqlQuery(query).ToString();
Assert.AreEqual(expected, soql);
}
public void Having_Clause_Gets_Formatted_Input()
{
string format = "something > {0}";
string expected = String.Format("{0}={1}", SoqlQuery.HavingKey, String.Format(format, "nothing"));
string soql = new SoqlQuery().Having(format, "nothing").ToString();
StringAssert.Contains(expected, soql);
}
public void Having_Clause_Gets_Valid_Predicate()
{
string predicate = "something > nothing";
string expected = String.Format("{0}={1}", SoqlQuery.HavingKey, predicate);
string soql = new SoqlQuery().Having(predicate).ToString();
StringAssert.Contains(expected, soql);
}
public void Empty_Where_Ignores_Where_Clause()
{
string startOfWhereClause = String.Format("{0}=", SoqlQuery.WhereKey);
string emptyWhere = new SoqlQuery().Where("").ToString();
string nullWhere = new SoqlQuery().Where(null).ToString();
StringAssert.DoesNotContain(startOfWhereClause, emptyWhere);
StringAssert.DoesNotContain(startOfWhereClause, nullWhere);
}
public void Empty_Having_Ignores_Having_Clause()
{
string startOfHavingClause = String.Format("{0}=", SoqlQuery.HavingKey);
string emptyHaving = new SoqlQuery().Having("").ToString();
string nullHaving = new SoqlQuery().Having(null).ToString();
StringAssert.DoesNotContain(startOfHavingClause, emptyHaving);
StringAssert.DoesNotContain(startOfHavingClause, nullHaving);
}
public void Select_Clause_Gets_Aliases_As_Lowercase()
{
string[] columns = new[] { "column1", "column2", "column3" };
string[] aliases = new[] { "Column_A", "COLUMN_B", "CoLuMn_C" };
string soql = new SoqlQuery().Select(columns).As(aliases).ToString();
for (int i = 0; i < columns.Length; i++)
{
StringAssert.Contains(String.Format("{0} AS {1}", columns[i], aliases[i].ToLower()), soql);
}
}
public void Select_Clause_Gets_Valid_Aliases_When_Some_Columns_Are_Aliased()
{
string[] columns = new[] { "column1", "column2", "column3" };
string[] aliases = new[] { "column_a", "column_b", };
string soql = new SoqlQuery().Select(columns).As(aliases).ToString();
for (int i = 0; i < aliases.Length; i++)
{
StringAssert.Contains(String.Format("{0} AS {1}", columns[i], aliases[i]), soql);
}
}
public void Empty_Order_Orders_By_DefaultOrder_In_DefaultOrderDirection()
{
string defaultOrderClause = String.Format("{0}={1} {2}", SoqlQuery.OrderKey, String.Join(SoqlQuery.Delimiter, SoqlQuery.DefaultOrder), SoqlQuery.DefaultOrderDirection);
string emptyGroup = new SoqlQuery().Order("").ToString();
string manyEmptyGroup = new SoqlQuery().Order("", "", "").ToString();
string nullGroup = new SoqlQuery().Order(null).ToString();
StringAssert.Contains(defaultOrderClause, emptyGroup);
StringAssert.Contains(defaultOrderClause, manyEmptyGroup);
StringAssert.Contains(defaultOrderClause, nullGroup);
}
public void Empty_Select_Selects_Default()
{
string defaultSelectClause = String.Format("{0}={1}", SoqlQuery.SelectKey, String.Join(SoqlQuery.Delimiter, SoqlQuery.DefaultSelect));
string emptySelect = new SoqlQuery().Select("").ToString();
string manyEmptySelect = new SoqlQuery().Select("", "", "").ToString();
string nullSelect = new SoqlQuery().Select(null).ToString();
StringAssert.Contains(defaultSelectClause, emptySelect);
StringAssert.Contains(defaultSelectClause, manyEmptySelect);
StringAssert.Contains(defaultSelectClause, nullSelect);
}
public void Empty_Select_Selects_Default()
{
string defaultSelectClause = String.Format("{0}={1}", SoqlQuery.SelectKey, String.Join(SoqlQuery.Delimiter, SoqlQuery.DefaultSelect));
string emptySelect = new SoqlQuery().Select("").ToString();
string manyEmptySelect = new SoqlQuery().Select("", "", "").ToString();
string nullSelect = new SoqlQuery().Select(null).ToString();
StringAssert.Contains(defaultSelectClause, emptySelect);
StringAssert.Contains(defaultSelectClause, manyEmptySelect);
StringAssert.Contains(defaultSelectClause, nullSelect);
}
public void Empty_Group_Ignores_Group_Clause()
{
string startOfGroupClause = String.Format("{0}=", SoqlQuery.GroupKey);
string emptyGroup = new SoqlQuery().Group("").ToString();
string manyEmptyGroup = new SoqlQuery().Group("", "", "").ToString();
string nullGroup = new SoqlQuery().Group(null).ToString();
StringAssert.DoesNotContain(startOfGroupClause, emptyGroup);
StringAssert.DoesNotContain(startOfGroupClause, manyEmptyGroup);
StringAssert.DoesNotContain(startOfGroupClause, nullGroup);
}
public void get(Location locate, int radius, int limit)
{
//initialize a new client
//make sure you register for your own app token (http://dev.socrata.com/register)
var client = new SodaClient("data.cityoftacoma.org", "faxxyxOUEBkwIxlgvMgFaEViQ");
//read metadata of a dataset using the resource identifier (Socrata 4x4)
//var metadata = client.GetMetadata("iww5-t4fx");
//Console.WriteLine("{0} has {1} views.", metadata.Name, metadata.ViewsCount);
//get a reference to the resource itself
//the result (a Resouce object) is a generic type
//the type parameter represents the underlying rows of the resource
var dataset = client.GetResource <Dictionary <string, object> >("iww5-t4fx");
//collections of an arbitrary type can be returned
//using SoQL and a fluent query building syntax
string sql = "within_circle(location," +
locate.latitude.ToString() + "," +
locate.longitude.ToString() + "," + radius.ToString() + ")";
var soql = new SoqlQuery().Where(sql).Limit(limit);
var results = dataset.Query(soql);
foreach (var row in results)
{
var tmp = new Fire();
if (0 != row.Count)
{
//foreach (var vv in row)
//Console.WriteLine(vv);
try
{
tmp.intersection_address = row["location_address"].ToString();
tmp.fire_generalcause = row["fire_generalcause"].ToString();
tmp.firetype = row["firetype"].ToString();
tmp.time1 = row["firstunitturnout"].ToString();
tmp.time2 = row["incidentclosed"].ToString();
tmp.date = row["incidentdate"].ToString().Substring(0, 10);
tmp.estimatedtotalfireloss = row["estimatedtotalfireloss"].ToString();
tmp.loc.city = row["city"].ToString();
tmp.loc.state = row["state"].ToString();
tmp.loc.zipcode = row["zipcode"].ToString();
tmp.loc.latitude = float.Parse(row["latitude"].ToString());
tmp.loc.longitude = float.Parse(row["longitude"].ToString());
}catch (Exception e)
{ Console.WriteLine("Error occurred in FireIncident entrance"); }
}
list.Add(tmp);
}
}
public void get(Location locate, int radius, int limit)
{
//https://data.cityoftacoma.org/resource/kjk6-j7c9.json
var client = new SodaClient("data.cityoftacoma.org", "faxxyxOUEBkwIxlgvMgFaEViQ");
var dataset = client.GetResource <Dictionary <string, object> >("kjk6-j7c9");
string sql = "within_circle(collision_location," +
locate.latitude.ToString() + "," +
locate.longitude.ToString() + "," + radius.ToString() + ")";
var soql = new SoqlQuery().Where(sql).Limit(limit);
var results = dataset.Query(soql);
foreach (var row in results)
{
if (0 != row.Count)
{
try
{
Collision collision = new Collision
{
};
try { collision.most_severe_injury_type = row["most_severe_injury_type"].ToString(); }
catch (Exception e) { }
try { collision.lighting_conditions = row["lighting_conditions"].ToString(); }
catch (Exception e) { }
try { collision.jurisdiction = row["jurisdiction"].ToString(); }
catch (Exception e) { }
try { collision.weather = row["weather"].ToString(); }
catch (Exception e) { }
try { collision.date = row["date"].ToString(); }catch (Exception e) {}
string collision_location = row["collision_location"].ToString();
string coordinates = collision_location.Substring(collision_location.IndexOf("coordinates") + 13).Trim('[', ']', '}', '{', '\n', '\r', ' ', '\t');
//Console.WriteLine(float.Parse(coordinates.Split(',')[0]));
collision.coordinate.latitude = float.Parse(coordinates.Split(',')[1]);
collision.coordinate.longitude = float.Parse(coordinates.Split(',')[0]);
collision.place = collision.coordinate.reverseGeocoding();
list.Add(collision);
}
catch (Exception e) {
//Console.WriteLine("Error traffic data entrance");
//https://data.cityofchicago.org/resource/6zsd-86xi.json?
//$where=date between '2018-02-27T12:00:00' and '2018-03-04T14:00:00'
}
}
}
}
public void Select_Clause_With_Aliases_Generates_Valid_SoQL()
{
string[] columns = new[] { "column1", "column2" };
string[] aliases = new[] { "column_a", "column_b" };
string expected = String.Format(@"{0} AS {1},{2} AS {3}",
columns[0],
aliases[0],
columns[1],
aliases[1]);
string soql = new SoqlQuery().Select(columns).As(aliases).ToString();
StringAssert.IsMatch(expected, soql);
}
public void Last_Select_Overwrites_All_Previous()
{
string[] first = { "first", "second", "last" };
string[] second = { "first", "second" };
string[] last = { "last" };
string format = String.Format("{0}={{0}}", SoqlQuery.SelectKey);
string soql = new SoqlQuery().Select(first)
.Select(second)
.Select(last)
.ToString();
StringAssert.DoesNotContain(String.Format(format, String.Join(SoqlQuery.Delimiter, first)), soql);
StringAssert.DoesNotContain(String.Format(format, String.Join(SoqlQuery.Delimiter, second)), soql);
StringAssert.Contains(String.Format(format, String.Join(SoqlQuery.Delimiter, last)), soql);
}
public Map(SoqlQuery <K> soqlQuery)
{
// make sure that Map<T, K> is Map<ID, SObject>
if (!typeof(SObject).IsAssignableFrom(typeof(K)) ||
!typeof(ID).IsAssignableFrom(typeof(T)))
{
throw new NotSupportedException("Only Map<ID, SObject> can be initialized via SOQL query data.");
}
foreach (object row in soqlQuery.QueryResult.Value)
{
var sobj = (SObject)row;
object key = sobj.Id;
this[(T)key] = (K)row;
}
}
public void Last_Select_Overwrites_All_Previous()
{
string[] first = { "first", "second", "last" };
string[] second = { "first", "second" };
string[] last = { "last" };
string format = String.Format("{0}={{0}}", SoqlQuery.SelectKey);
string soql = new SoqlQuery().Select(first)
.Select(second)
.Select(last)
.ToString();
StringAssert.DoesNotContain(String.Format(format, String.Join(SoqlQuery.Delimiter, first)), soql);
StringAssert.DoesNotContain(String.Format(format, String.Join(SoqlQuery.Delimiter, second)), soql);
StringAssert.Contains(String.Format(format, String.Join(SoqlQuery.Delimiter, last)), soql);
}
public void Last_FullTextSearch_Overwrites_All_Previous()
{
string first = "first text";
string second = "second text";
string last = "last text";
string format = String.Format("{0}={{0}}", SoqlQuery.SearchKey);
string soql = new SoqlQuery().FullTextSearch(first)
.FullTextSearch(second)
.FullTextSearch(last)
.ToString();
StringAssert.DoesNotContain(String.Format(format, first), soql);
StringAssert.DoesNotContain(String.Format(format, second), soql);
StringAssert.Contains(String.Format(format, last), soql);
}
public void Last_Offset_Overwrites_All_Previous()
{
int first = 1;
int second = 2;
int last = 3;
string format = String.Format("{0}={{0}}", SoqlQuery.OffsetKey);
string soql = new SoqlQuery().Offset(first)
.Offset(second)
.Offset(last)
.ToString();
StringAssert.DoesNotContain(String.Format(format, first), soql);
StringAssert.DoesNotContain(String.Format(format, second), soql);
StringAssert.Contains(String.Format(format, last), soql);
}
/// <summary>
/// Assisted by code from https://dev.socrata.com/foundry/data.cityofnewyork.us/fhrw-4uyv
/// queried DB - https://data.cityofnewyork.us
/// API KEY - PVGjhHLj8Svy7Ryz0uJgW9IBh
/// loadDB connects to the database, sends the query and then returns the data
/// </summary>
/// <returns>Returns the dataset of the query to main</returns>
public IEnumerable <Dictionary <string, object> > LoadDB(DateTime date)
{
SODA.SodaClient client = new SodaClient("https://data.cityofnewyork.us", "PVGjhHLj8Svy7Ryz0uJgW9IBh");
/// <remarks>
/// The documentation on the web is outdated.
/// The .NET library has been updated to no longer allow generic typing.
/// You must use either Dictionary(String,Object) - use <> but not allowed in XML comments
/// OR a user-defined json serializable class - their documentation does not explain how to do this
/// well enough, however so we are sticking with the Generic Collection specified
/// </remarks>>
SODA.Resource <Dictionary <string, object> > dataset = client.GetResource <Dictionary <string, object> >("fhrw-4uyv");
SoqlQuery soql = this.GetQueryDate(date);
IEnumerable <Dictionary <string, object> > results = dataset.Query <Dictionary <string, object> >(soql);
return(results);
}
public void MapCanBeInitializedWithSoqlQueryResults()
{
Assert.DoesNotThrow(() =>
{
var lazyData = new LazyOfListOfSample(GetSampleObjects);
var query = new SoqlQuery <SampleSObject>(lazyData, string.Empty);
var map = new Map <ID, SampleSObject>(query);
var item123 = map.get("123");
Assert.NotNull(item123);
Assert.AreEqual("OneTwoThree", item123.Name);
var item321 = map.get("321");
Assert.NotNull(item321);
Assert.AreEqual("ThreeTwoOne", item321.Name);
});
}
public void KeySetMethodIsImplemented()
{
Assert.DoesNotThrow(() =>
{
var lazyData = new LazyOfListOfSample(GetSampleObjects);
var query = new SoqlQuery <SampleSObject>(lazyData, string.Empty);
var map = new Map <ID, SampleSObject>(query);
var keySet = map.keySet();
Assert.NotNull(keySet);
Assert.AreEqual(2, keySet.size());
Assert.IsTrue(keySet.contains("123"));
Assert.IsTrue(keySet.contains("321"));
Assert.IsFalse(keySet.contains("111"));
});
}
public void Query_With_UndefinedLimit_UsesMaximum()
{
var query = new SoqlQuery();
var initialValue = query.LimitValue;
try
{
mockClient.Query <object>(query, StringMocks.ResourceId);
}
catch (InvalidOperationException)
{
//pass
}
Assert.Greater(query.LimitValue, initialValue);
Assert.AreEqual(SoqlQuery.MaximumLimit, query.LimitValue);
}
public void Empty_Aliases_Are_Ignored()
{
string[] columns = new[] { "column1", "column2" };
string[] startOfAliases = columns.Select(c => String.Format("{0} AS ", c)).ToArray();
string[] nullAliases = new[] { (string)null, (string)null };
string[] emptyAliases = new[] { "", "" };
string nullSoql = new SoqlQuery().Select(columns).As(nullAliases).ToString();
string emptySoql = new SoqlQuery().Select(columns).As(emptyAliases).ToString();
foreach (string startOfAlias in startOfAliases)
{
StringAssert.DoesNotContain(startOfAlias, nullSoql);
StringAssert.DoesNotContain(startOfAlias, emptySoql);
}
}
public void Query_Ctor_Takes_Precidence()
{
var query = "SELECT something WHERE this > that ORDER BY another";
var expected = String.Format("{0}={1}", SoqlQuery.QueryKey, query);
var soql = new SoqlQuery(query);
var select = soql.Select("column1", "column2").ToString();
var where = soql.Where("that > this").ToString();
var order = soql.Order("yetanother").ToString();
Assert.AreEqual(expected, select);
Assert.AreEqual(expected, where);
Assert.AreEqual(expected, order);
}
public void Last_Having_Overwrites_All_Previous()
{
string first = "first > 0";
string second = "second > first";
string last = "last > anything";
string format = String.Format("{0}={{0}}", SoqlQuery.HavingKey);
string expected = String.Format(format, last);
string soql = new SoqlQuery().Having(first)
.Having(second)
.Having(last)
.ToString();
StringAssert.DoesNotContain(String.Format(format, first), soql);
StringAssert.DoesNotContain(String.Format(format, second), soql);
StringAssert.Contains(String.Format(format, last), soql);
}
static void Main(string[] args)
{
// Get set up data and get data.
var client = new SodaClient("https://data.sfgov.org", "tOvbugsbjxkiUpNOfwfNmz4Sk");
string[] columns = { "dayoftheweekstr",
"starttime",
"endtime",
"location",
"locationdesc",
"applicant",
"longitude",
"latitude" };
var soql = new SoqlQuery().Select(columns);
var dataset = client.GetResource <FoodTruck>("jjew-r69b");
var results = dataset.GetRows(limit: 5000);
List <FoodTruck> openTrucks = (from FoodTruck item in results
where item.IsOpen(DateTime.Now)
select item).ToList();
openTrucks.Sort();
// Queue is for paging results.
Queue <FoodTruck> queueTrucks = new Queue <FoodTruck>(openTrucks);
Console.WriteLine(openTrucks.Count + " trucks open at " + DateTime.Now.ToString());
Console.WriteLine("NAME - LOCATION");
while (queueTrucks.Count > 0)
{
for (int i = 0; i < 10; i++)
{
if (queueTrucks.Count == 0)
{
break;
}
queueTrucks.Dequeue().Display();
}
Console.ReadKey();
}
Console.WriteLine("End of list.");
Console.ReadKey();
}
public void Select_Clause_Gets_Valid_Aliases_And_Ignores_Extra_Aliases()
{
string[] columns = new[] { "column1", "column2" };
string[] aliases = new[] { "column_a", "column_b", "column_c", "column_d" };
string soql = new SoqlQuery().Select(columns).As(aliases).ToString();
for (int i = 0; i < columns.Length; i++)
{
StringAssert.Contains(String.Format("{0} AS {1}", columns[i], aliases[i]), soql);
}
string[] extraAliases = aliases.Skip(columns.Length).ToArray();
for (int j = 0; j < extraAliases.Length; j++)
{
StringAssert.DoesNotContain(String.Format("AS {0}", extraAliases[j]), soql);
}
}
public void get(Location locate, int radius, int limit)
{
var client = new SodaClient("data.cityoftacoma.org", "faxxyxOUEBkwIxlgvMgFaEViQ");
var dataset = client.GetResource <Dictionary <string, object> >("vzsr-722t");
string sql = "within_circle(intersection," +
locate.latitude.ToString() + "," +
locate.longitude.ToString() + "," + radius.ToString() + ")";
var soql = new SoqlQuery().Where(sql).Limit(limit);
var results = dataset.Query(soql);
foreach (var row in results)
{
var tmp = new Crime();
if (0 != row.Count)
{
//foreach (var vv in row)
//Console.WriteLine(vv);
try
{
tmp.intersection_address = row["intersection_address"].ToString();
tmp.date = row["occurred_on"].ToString().Substring(0, 10);
tmp.time = row["approximate_time"].ToString();
tmp.crime = row["crime"].ToString();
tmp.loc.city = row["intersection_city"].ToString();
tmp.loc.state = row["intersection_state"].ToString();
tmp.loc.zipcode = locate.zipcode;
//longthy
string intersection = row["intersection"].ToString();
String coordinates = intersection.Substring(intersection.IndexOf("coordinates") + 13).Trim('[', ']', '}', '{', '\n', '\r', ' ', '\t');
//Console.WriteLine(coordinates.Split(',')[1]);
tmp.loc.latitude = float.Parse(coordinates.Split(',')[1]);
tmp.loc.longitude = float.Parse(coordinates.Split(',')[0]);
}catch (Exception e) { Console.WriteLine("Error occurred in Crime entrance"); }
}
list.Add(tmp);
}
}
public void All_Query_Methods_Return_The_Original_Instance()
{
var original = new SoqlQuery();
var select = original.Select("something");
var where = original.Where("something");
var order = original.Order(SoqlOrderDirection.DESC, "something");
var group = original.Group("something");
var limit = original.Limit(10);
var offset = original.Offset(10);
var search = original.FullTextSearch("something");
Assert.AreSame(original, select);
Assert.AreSame(original, where);
Assert.AreSame(original, order);
Assert.AreSame(original, group);
Assert.AreSame(original, limit);
Assert.AreSame(original, offset);
Assert.AreSame(original, search);
}
public static PagedList <BusinessLocation> GetBusinessLocations(string SearchQuery, int PageNumber, int PageSize, string OrderBy, bool OrderByAscDesc)
{
var client = new SodaClient(_APIEndPointHost, _AppToken);
var dataset = client.GetResource <PagedList <BusinessLocation> >(_APIEndPoint4x4);
var columns = new[] { "company_name", "sub_subindustry", "phone", " location_1" };
var aliases = new[] { "CompanyName", "SubIndustry", "fone", "Location" };
var soql = new SoqlQuery().Select(columns)
.As(aliases)
.Order((OrderByAscDesc) ? SoqlOrderDirection.ASC : SoqlOrderDirection.DESC, new[] { OrderBy });
if (!string.IsNullOrWhiteSpace(SearchQuery))
{
soql = new SoqlQuery().FullTextSearch(SearchQuery);
}
var results = dataset.Query <BusinessLocation>(soql);
PagedList <BusinessLocation> pagedResults = new PagedList <BusinessLocation>(results.ToList(), PageNumber, PageSize);
return(pagedResults);
}
public void Search_Clause_Gets_FormattedInput()
{
string format = "search term is {0}";
string expected = String.Format("{0}={1}", SoqlQuery.SearchKey, String.Format(format, "test"));
string soql = new SoqlQuery().FullTextSearch(format, "test").ToString();
StringAssert.Contains(expected, soql);
}
public void Search_Clause_Gets_FullTextSearch(string searchText)
{
string expected = String.Format("{0}={1}", SoqlQuery.SearchKey, searchText);
string soql = new SoqlQuery().FullTextSearch(searchText).ToString();
StringAssert.Contains(expected, soql);
}
public void Empty_FullTextSearch_Ignores_Search_Clause()
{
string startOfSearchClause = String.Format("{0}=", SoqlQuery.SearchKey);
string emptySearch = new SoqlQuery().FullTextSearch("").ToString();
string nullSearch = new SoqlQuery().FullTextSearch(null).ToString();
StringAssert.DoesNotContain(startOfSearchClause, emptySearch);
StringAssert.DoesNotContain(startOfSearchClause, nullSearch);
}
public void Where_Clause_Gets_Valid_Predicate()
{
string predicate = "something > nothing";
string expected = String.Format("{0}={1}", SoqlQuery.WhereKey, predicate);
string soql = new SoqlQuery().Where(predicate).ToString();
StringAssert.Contains(expected, soql);
}
public void Empty_Aliases_Are_Ignored()
{
string[] columns = new[] { "column1", "column2" };
string[] startOfAliases = columns.Select(c => String.Format("{0} AS ", c)).ToArray();
string[] nullAliases = new[] { (string)null, (string)null };
string[] emptyAliases = new[] { "", "" };
string nullSoql = new SoqlQuery().Select(columns).As(nullAliases).ToString();
string emptySoql = new SoqlQuery().Select(columns).As(emptyAliases).ToString();
foreach (string startOfAlias in startOfAliases)
{
StringAssert.DoesNotContain(startOfAlias, nullSoql);
StringAssert.DoesNotContain(startOfAlias, emptySoql);
}
}
public void ForQuery_With_Valid_Arguments_Creates_Query_Uri()
{
SoqlQuery soqlQuery = new SoqlQuery();
var uri = SodaUri.ForQuery(StringMocks.Host, StringMocks.ResourceId, soqlQuery);
StringAssert.AreEqualIgnoringCase(String.Format("/resource/{0}", StringMocks.ResourceId), uri.LocalPath);
StringAssert.AreEqualIgnoringCase(String.Format("?{0}", Uri.EscapeUriString(soqlQuery.ToString())), uri.Query);
}
public void Empty_Group_Ignores_Group_Clause()
{
string startOfGroupClause = String.Format("{0}=", SoqlQuery.GroupKey);
string emptyGroup = new SoqlQuery().Group("").ToString();
string manyEmptyGroup = new SoqlQuery().Group("", "", "").ToString();
string nullGroup = new SoqlQuery().Group(null).ToString();
StringAssert.DoesNotContain(startOfGroupClause, emptyGroup);
StringAssert.DoesNotContain(startOfGroupClause, manyEmptyGroup);
StringAssert.DoesNotContain(startOfGroupClause, nullGroup);
}
public void Empty_Order_Orders_By_DefaultOrder_In_DefaultOrderDirection()
{
string defaultOrderClause = String.Format("{0}={1} {2}", SoqlQuery.OrderKey, String.Join(SoqlQuery.Delimiter, SoqlQuery.DefaultOrder), SoqlQuery.DefaultOrderDirection);
string emptyGroup = new SoqlQuery().Order("").ToString();
string manyEmptyGroup = new SoqlQuery().Order("", "", "").ToString();
string nullGroup = new SoqlQuery().Order(null).ToString();
StringAssert.Contains(defaultOrderClause, emptyGroup);
StringAssert.Contains(defaultOrderClause, manyEmptyGroup);
StringAssert.Contains(defaultOrderClause, nullGroup);
}
public void Order_Clause_Gets_Order_Direction_And_Valid_Columns(SoqlOrderDirection direction, params string[] columns)
{
string expected = String.Format("{0}={1} {2}", SoqlQuery.OrderKey, String.Join(SoqlQuery.Delimiter, columns.Where(c => !String.IsNullOrEmpty(c))), direction);
string soql = new SoqlQuery().Order(direction, columns).ToString();
StringAssert.Contains(expected, soql);
}
public void Last_Where_Overwrites_All_Previous()
{
string first = "first > 0";
string second = "second > first";
string last = "last > anything";
string format = String.Format("{0}={{0}}", SoqlQuery.WhereKey);
string expected = String.Format(format, last);
string soql = new SoqlQuery().Where(first)
.Where(second)
.Where(last)
.ToString();
StringAssert.DoesNotContain(String.Format(format, first), soql);
StringAssert.DoesNotContain(String.Format(format, second), soql);
StringAssert.Contains(String.Format(format, last), soql);
}
public void Where_Clause_Gets_Formatted_Input()
{
string format = "something > {0}";
string expected = String.Format("{0}={1}", SoqlQuery.WhereKey, String.Format(format, "nothing"));
string soql = new SoqlQuery().Where(format, "nothing").ToString();
StringAssert.Contains(expected, soql);
}
public void Select_Clause_Gets_Valid_Aliases_And_Ignores_Extra_Aliases()
{
string[] columns = new[] { "column1", "column2" };
string[] aliases = new[] { "column_a", "column_b", "column_c", "column_d" };
string soql = new SoqlQuery().Select(columns).As(aliases).ToString();
for (int i = 0; i < columns.Length; i++)
{
StringAssert.Contains(String.Format("{0} AS {1}", columns[i], aliases[i]), soql);
}
string[] extraAliases = aliases.Skip(columns.Length).ToArray();
for (int j = 0; j < extraAliases.Length; j++)
{
StringAssert.DoesNotContain(String.Format("AS {0}", extraAliases[j]), soql);
}
}
public void Last_FullTextSearch_Overwrites_All_Previous()
{
string first = "first text";
string second = "second text";
string last = "last text";
string format = String.Format("{0}={{0}}", SoqlQuery.SearchKey);
string soql = new SoqlQuery().FullTextSearch(first)
.FullTextSearch(second)
.FullTextSearch(last)
.ToString();
StringAssert.DoesNotContain(String.Format(format, first), soql);
StringAssert.DoesNotContain(String.Format(format, second), soql);
StringAssert.Contains(String.Format(format, last), soql);
}
public void Limit_Less_Than_One_Throws_ArgumentOutOfRangeException(int limit)
{
var soql = new SoqlQuery().Limit(limit);
}
public void All_Query_Methods_Return_The_Original_Instance()
{
var original = new SoqlQuery();
var select = original.Select("something");
var where = original.Where("something");
var order = original.Order(SoqlOrderDirection.DESC, "something");
var group = original.Group("something");
var limit = original.Limit(10);
var offset = original.Offset(10);
var search = original.FullTextSearch("something");
Assert.AreSame(original, select);
Assert.AreSame(original, where);
Assert.AreSame(original, order);
Assert.AreSame(original, group);
Assert.AreSame(original, limit);
Assert.AreSame(original, offset);
Assert.AreSame(original, search);
}
public void Limit_Clause_Has_A_Ceiling_At_MaximumLimit(int limit)
{
string expected = String.Format("{0}={1}", SoqlQuery.LimitKey, Math.Min(limit, SoqlQuery.MaximumLimit));
string soql = new SoqlQuery().Limit(limit).ToString();
StringAssert.Contains(expected, soql);
}
/// <summary>
/// Create a Uri for querying the specified resource on the specified Socrata host, using the specified SoqlQuery object.
/// </summary>
/// <param name="socrataHost">The Socrata host to target.</param>
/// <param name="resourceId">The identifier (4x4) for a resource on the Socrata host to target.</param>
/// <param name="soqlQuery">A SoqlQuery object to use for querying.</param>
/// <returns>A query Uri for the specified resource on the specified Socrata host.</returns>
public static Uri ForQuery(string socrataHost, string resourceId, SoqlQuery soqlQuery)
{
if (String.IsNullOrEmpty(socrataHost))
throw new ArgumentException("socrataHost", "Must provide a Socrata host to target.");
if (FourByFour.IsNotValid(resourceId))
throw new ArgumentOutOfRangeException("resourceId", "The provided resourceId is not a valid Socrata (4x4) resource identifier.");
if (soqlQuery == null)
throw new ArgumentNullException("soqlQuery", "Must provide a valid SoqlQuery object");
string url = metadataUrl(socrataHost, resourceId).Replace("views", "resource");
string queryUrl = Uri.EscapeUriString(String.Format("{0}?{1}", url, soqlQuery.ToString()));
return new Uri(queryUrl);
}
public void Offset_Less_Than_Zero_Throws_ArgumentOutOfRangeException(int offset)
{
var soql = new SoqlQuery().Offset(offset);
}
public void Select_Clause_Gets_Valid_Aliases_When_All_Columns_Are_Aliased()
{
string[] columns = new[] { "column1", "column2", "column3" };
string[] aliases = new[] { "column_a", "column_b", "column_c" };
string soql = new SoqlQuery().Select(columns).As(aliases).ToString();
for (int i = 0; i < columns.Length; i++)
{
StringAssert.Contains(String.Format("{0} AS {1}", columns[i], aliases[i]), soql);
}
}
public void Last_Offset_Overwrites_All_Previous()
{
int first = 1;
int second = 2;
int last = 3;
string format = String.Format("{0}={{0}}", SoqlQuery.OffsetKey);
string soql = new SoqlQuery().Offset(first)
.Offset(second)
.Offset(last)
.ToString();
StringAssert.DoesNotContain(String.Format(format, first), soql);
StringAssert.DoesNotContain(String.Format(format, second), soql);
StringAssert.Contains(String.Format(format, last), soql);
}
public void Select_Clause_With_Aliases_Generates_Valid_SoQL()
{
string[] columns = new[] { "column1", "column2" };
string[] aliases = new[] { "column_a", "column_b" };
string expected = String.Format(@"{0} AS {1},\s?{2} AS {3}[^,]",
columns[0],
aliases[0],
columns[1],
aliases[1]);
string soql = new SoqlQuery().Select(columns).As(aliases).ToString();
StringAssert.IsMatch(expected, soql);
}
public void Select_Clause_Gets_Aliases_As_Lowercase()
{
string[] columns = new[] { "column1", "column2", "column3" };
string[] aliases = new[] { "Column_A", "COLUMN_B", "CoLuMn_C" };
string soql = new SoqlQuery().Select(columns).As(aliases).ToString();
for (int i = 0; i < columns.Length; i++)
{
StringAssert.Contains(String.Format("{0} AS {1}", columns[i], aliases[i].ToLower()), soql);
}
}
public void Select_Clause_Selects_Unaliased_Columns_When_Some_Columns_Are_Aliased()
{
string[] columns = new[] { "column1", "column2", "column3", "column4", "column5" };
string[] aliases = new[] { "column_a", "column_b" };
string soql = new SoqlQuery().Select(columns).As(aliases).ToString();
StringAssert.Contains(String.Join(SoqlQuery.Delimiter, columns.Skip(aliases.Length)), soql);
}
public void Empty_Where_Ignores_Where_Clause()
{
string startOfWhereClause = String.Format("{0}=", SoqlQuery.WhereKey);
string emptyWhere = new SoqlQuery().Where("").ToString();
string nullWhere = new SoqlQuery().Where(null).ToString();
StringAssert.DoesNotContain(startOfWhereClause, emptyWhere);
StringAssert.DoesNotContain(startOfWhereClause, nullWhere);
}
