static void Main(string[] args)
{
string host = ConfigurationManager.AppSettings["socrata.host"];
string datasetId = ConfigurationManager.AppSettings["socrata.sample.publishDataset"];
string username = ConfigurationManager.AppSettings["socrata.username"];
string password = ConfigurationManager.AppSettings["socrata.password"];
string appToken = ConfigurationManager.AppSettings["socrata.appToken"];
var basicAuthClient = new Soda2Client(username, password, appToken);
var dataset = basicAuthClient.getDatasetInfo <Row>(host, datasetId);
//demo only - this simple CSV parsing doesn't handle escaped commas!
FileStream movieCsv = File.OpenRead("resources/movies.csv");
StreamReader reader = new StreamReader(movieCsv);
var header = reader.ReadLine().Split(',');
LinkedList <Row> rows = new LinkedList <Row>();
while (!reader.EndOfStream)
{
var line = reader.ReadLine().Split(',');
Row row = new Row();
for (int i = 0; i < line.Length; i++)
{
row.Add(header[i], line[i]);
}
rows.AddLast(row);
}
dataset.truncate();
dataset.upsert(rows.ToArray());
}
static void Main(string[] args)
{
var noAuthClient = new Soda2Client(null, null, null);
var host = ConfigurationManager.AppSettings["socrata.host"];
var datasetId = ConfigurationManager.AppSettings["socrata.sample.dataset"];
var dataset = noAuthClient.getDatasetInfo <Row>(host, datasetId);
Column[] columns = dataset.columns;
var responseA = dataset.query("select * where title = 'The Killer'");
Console.WriteLine(String.Format("The Killer was directed by {0}", responseA[0]["director"]));
var responseB = dataset.query(
new QueryBuilder()
.select("count(year)", "year")
.where ("year > 1950")
.groupBy("year")
.having("count_year > 0")
.orderBy("count_year desc")
);
Console.WriteLine(
String.Format("This dataset shows {1} movies from {0}",
responseB[1]["year"],
responseB[1]["count_year"])
);
}
public static List<ServiceRequestModel> ServiceRequestData(string host, string datasetId)
{
var noAuthClient = new Soda2Client(null, null, null);
//var host = ConfigurationManager.AppSettings["socrata.host"];
//var datasetId = ConfigurationManager.AppSettings["socrata.servicerequest.dataset"];
var dataset = noAuthClient.getDatasetInfo<Row>(host, datasetId);
Column[] columns = dataset.columns;
var dataList = dataset.query("select *");
var models = new List<ServiceRequestModel>();
foreach (var data in dataList)
{
var model = new ServiceRequestModel();
foreach (var column in columns)
{
model.Address += (column.fieldName.ToUpper().Contains("ADDRESS1"))
? (data[column.fieldName].ToString() + " ")
: string.Empty;
// model.Address += (column.fieldName.ToUpper().Contains("ADDRESS2")) ? (data[column.fieldName].ToString()+ "22 ") : string.Empty;
if (column.fieldName.ToUpper().Contains("ADDRESS2"))
{
if (data.Keys.Contains(column.fieldName))
{
model.Address += (data[column.fieldName].ToString() + "22 ");
}
//Response.Write("Key Name:" + column.fieldName + " \t LENGTH:" + column.fieldName.Length + ":" + data[column.fieldName] + "<br/>");
}
if (column.fieldName.ToUpper().Contains("SERVNO"))
{
model.ServNo = data[column.fieldName].ToString();
}
if (column.fieldName.ToUpper().Contains("REQUESTDATE"))
{
var requestDateTime = DateTime.MinValue;
model.RequestDate = Convert.ToDateTime(data[column.fieldName]);
}
if (column.fieldName.ToUpper().Contains("PROBCODE"))
{
model.ProbCode = data[column.fieldName].ToString();
}
if (column.fieldName.ToUpper().Contains("PROBDESC"))
{
model.ProbDesc = data[column.fieldName].ToString();
}
if (column.fieldName.ToUpper().Contains("GPSX"))
{
float latitude = 0;
model.GPSX = (float.TryParse(data[column.fieldName].ToString(), out latitude)) ? latitude : 0;
//model.GPSX = (data[column.fieldName].ToString());
}
if (column.fieldName.ToUpper().Contains("GPSY"))
{
float longitude = 0;
model.GPSY = (float.TryParse(data[column.fieldName].ToString(), out longitude)) ? longitude : 0;
//model.GPSY = (data[column.fieldName].ToString());
}
}
//Response.Write("ServiceNumber:" + model.ServNo + "<BR/>");
//Response.Write("RequestDate:" + model.RequestDate + "<BR/>");
//Response.Write("ProblemCode:" + model.ProbCode + "<BR/>");
//Response.Write("ProblemDescription:" + model.ProbDesc + "<BR/>");
//Response.Write("Address:" + model.Address + "<BR/>");
//Response.Write("GPSX:" + model.GPSX + "<BR/>");
//Response.Write("GPSY:" + model.GPSY + "<BR/>");
models.Add(model);
}
return models;
}
public static List<RoadContructionModel> RoadContructionData(string host, string datasetId)
{
var noAuthClient = new Soda2Client(null, null, null);
//var host = ConfigurationManager.AppSettings["socrata.host"];
//var datasetId = ConfigurationManager.AppSettings["socrata.sample.dataset"];]
//var service = new ConfigService();
//var host = service.Config.SodaHost;
//var datasetId = service.Config.SodaDataSet;
var dataset = noAuthClient.getDatasetInfo<Row>(host, datasetId);
Column[] columns = dataset.columns;
var dataList = dataset.query("select * where county_nam = 'WINNEBAGO'");
var models = new List<RoadContructionModel>();
foreach (var data in dataList)
{
var location = string.Empty;
var model = new RoadContructionModel();
foreach (var column in columns)
{
//Response.Write("Column name:" + data[column.name] + ":" + column.fieldValue + "\t Type:" + column.dataTypeName + "<br/>");
//location+=(column.fieldName.ToUpper().Contains("LOCATION"))? (data[column.fieldName] +" "):string.Empty;
//Response.Write("Column:" + column.name + "\t Data:" + data[column.fieldName] +"<BR/>");
model.Location += (column.fieldName.ToUpper().Contains("LOCATION"))
? (data[column.fieldName] + " ")
: string.Empty;
model.Improvement += (column.fieldName.ToUpper().Contains("IMPRVMNT"))
? (data[column.fieldName] + " ")
: string.Empty;
model.Route += (column.fieldName.ToUpper().Contains("ROUTE"))
? (data[column.fieldName] + " ")
: string.Empty;
if (column.fieldName.ToUpper().Contains("INVENTORY"))
{
model.Inventory = data[column.fieldName].ToString();
}
if (column.fieldName.ToUpper().Contains("RECORD"))
{
model.Record = data[column.fieldName].ToString();
}
if (column.fieldName.ToUpper().Contains("POINT_X"))
{
float latitude = 0;
model.Latitude = (float.TryParse(data[column.fieldName].ToString(), out latitude))
? latitude
: 0;
}
if (column.fieldName.ToUpper().Contains("POINT_Y"))
{
float longitude = 0;
model.Longitude = (float.TryParse(data[column.fieldName].ToString(), out longitude))
? longitude
: 0;
}
if (column.fieldName.ToUpper().Contains("COUNTY_NAM"))
{
model.CountyName = data[column.fieldName].ToString();
}
model.Description = "<h2>Location</h2>" + model.Location + "<br/>";
model.Description += "<h2>Improvement</h2>" + model.Improvement + "<br/>";
model.Description += "<h2>Route</h2>" + model.Route + "<br/>";
}
//Response.Write("Record:" + model.Record + "<BR/>");
//Response.Write("Inventory:" + model.Inventory + "<BR/>");
//Response.Write("Location:" + model.Location + "<BR/>");
//Response.Write("Improvment:" + model.Improvement + "<BR/>");
//Response.Write("Latitue:" + model.Latitude.ToString() + "<BR/>");
//Response.Write("Longitude:" + model.Longitude.ToString() + "<BR/>");
//Response.Write("Route:" + model.Route + "<BR/>");
models.Add(model);
}
return models;
}
static void Main(string[] args)
{
Console.WriteLine("Are you SURE you created a working copy and updated the dataset ID in your app.config?");
var key = Console.ReadKey();
if (key.Key != ConsoleKey.Y)
{
Console.WriteLine("Exiting...");
Environment.Exit(1);
}
using (var db = new StoresEntities())
{
string host = ConfigurationManager.AppSettings["SocrataHost"];
string datasetId = ConfigurationManager.AppSettings["SocrataDatasetID"];
string username = ConfigurationManager.AppSettings["SocrataUsername"];
string password = ConfigurationManager.AppSettings["SocrataPassword"];
string appToken = ConfigurationManager.AppSettings["SocrataAppToken"];
var basicAuthClient = new Soda2Client(username, password, appToken);
var dataset = basicAuthClient.getDatasetInfo <Row>(host, datasetId);
// truncate the new working copy we created - it's easier just to dump all new results and not horribly time consuming
Console.WriteLine("Truncating");
dataset.truncate();
LinkedList <Row> rows = new LinkedList <Row>();
// get the most recent LastSeen date, so we know what our new batch is
var mostRecent = db.Stores.Select(s => s.LastSeen).Max(s => (DateTime?)s);
// get all the stores in the most recent batch
var mostRecentStores = db.Stores.Where(s => s.LastSeen == mostRecent);
Row row;
foreach (var store in mostRecentStores)
{
var streetCombined = "";
if (store.Street1 != null && store.Street1.Trim() != "")
{
streetCombined = streetCombined + store.Street1.Trim();
}
if (store.Street2 != null && store.Street2.Trim() != "")
{
streetCombined = streetCombined + ", " + store.Street2.Trim();
}
if (store.Street3 != null && store.Street3.Trim() != "")
{
streetCombined = streetCombined + ", " + store.Street3.Trim();
}
row = new Row();
row.Add("Store ID", store.StarbucksStoreID);
row.Add("Name", store.Name);
row.Add("Brand", store.BrandName);
row.Add("Store Number", store.StoreNumber);
row.Add("Phone Number", store.PhoneNumber);
row.Add("Ownership Type", store.OwnershipType);
row.Add("Street Combined", streetCombined);
row.Add("Street 1", store.Street1);
row.Add("Street 2", store.Street2);
row.Add("Street 3", store.Street3);
row.Add("City", store.City);
row.Add("Country Subdivision", store.CountrySubdivisionCode);
row.Add("Country", store.CountryCode);
row.Add("Postal Code", store.PostalCode);
row.Add("Latitude", store.Latitude);
row.Add("Longitude", store.Longitude);
row.Add("Timezone", store.TZID);
row.Add("Current Timezone Offset", store.TZOffset);
row.Add("Olson Timezone", store.TZOlsonID);
rows.AddLast(row);
Console.WriteLine("Added store " + store.StarbucksStoreID);
if (rows.Count > 2000)
{
Console.WriteLine("Upserting batch");
dataset.upsert(rows.ToArray());
rows.Clear();
}
}
if (rows.Count > 0)
{
Console.WriteLine("Upserting last");
dataset.upsert(rows.ToArray());
}
Console.WriteLine("Complete");
Console.ReadKey();
}
}
protected void Page_Load(object sender, EventArgs e)
{
var noAuthClient = new Soda2Client(null, null, null);
var host = ConfigurationManager.AppSettings["socrata.host"];
var datasetId = ConfigurationManager.AppSettings["socrata.sample.dataset"];
var dataset = noAuthClient.getDatasetInfo<Row>(host, datasetId);
Column[] columns = dataset.columns;
var dataList = dataset.query("select * where county_nam = 'WINNEBAGO'");
var models = new List<RoadContructionModel>();
foreach (var data in dataList)
{
var location = string.Empty;
var model = new RoadContructionModel();
foreach (var column in columns)
{
//Response.Write("Column name:" + data[column.name] + ":" + column.fieldValue + "\t Type:" + column.dataTypeName + "<br/>");
//location+=(column.fieldName.ToUpper().Contains("LOCATION"))? (data[column.fieldName] +" "):string.Empty;
//Response.Write("Column:" + column.name + "\t Data:" + data[column.fieldName] +"<BR/>");
model.Location += (column.fieldName.ToUpper().Contains("LOCATION")) ? (data[column.fieldName] + " ") : string.Empty;
model.Improvement += (column.fieldName.ToUpper().Contains("IMPRVMNT")) ? (data[column.fieldName] + " ") : string.Empty;
model.Route += (column.fieldName.ToUpper().Contains("ROUTE")) ? (data[column.fieldName] + " ") : string.Empty;
if (column.fieldName.ToUpper().Contains("INVENTORY"))
{
model.Inventory = data[column.fieldName].ToString();
}
if(column.fieldName.ToUpper().Contains("RECORD"))
{
model.Record = data[column.fieldName].ToString();
}
if (column.fieldName.ToUpper().Contains("POINT_X"))
{
float latitude = 0;
model.Latitude = (float.TryParse(data[column.fieldName].ToString(), out latitude)) ? latitude : 0;
}
if (column.fieldName.ToUpper().Contains("POINT_Y"))
{
float longitude = 0;
model.Longitude = (float.TryParse(data[column.fieldName].ToString(), out longitude)) ? longitude : 0;
}
if (column.fieldName.ToUpper().Contains("COUNTY_NAM"))
{
model.CountyName = data[column.fieldName].ToString();
}
}
Response.Write("Record:" + model.Record + "<BR/>");
Response.Write("Inventory:" + model.Inventory + "<BR/>");
Response.Write("Location:" + model.Location + "<BR/>");
Response.Write("Improvment:" + model.Improvement + "<BR/>");
Response.Write("Latitue:" + model.Latitude.ToString() + "<BR/>");
Response.Write("Longitude:" + model.Longitude.ToString() + "<BR/>");
Response.Write("Route:" + model.Route + "<BR/>");
models.Add(model);
}
Response.Write("Total Count:" + models.Capacity);
//var responseA = dataset.query("select * where title = 'The Killer'");
}
public void QueryResultWithObjectClassTest()
{
var rows = Soda2Client.deserialize <Object[]>(crimeJson);
testResultDeserialization(rows);
}
public void QueryResultConstructorWithRowClassTest()
{
var rows = Soda2Client.deserialize <Row[]>(crimeJson);
testResultDeserialization(rows);
}
