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()); }
public void BasicAuthTest() { Dataset <Row> dataset = basicAuthClient.getDatasetInfo <Row>(host, datasetId); Assert.IsNotNull(dataset.columns); Assert.IsNotNull(dataset.name); }
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 ConfirmDatasetPrivateTest() { Dataset <Row> shouldBePrivate = noAuthClient.getDatasetInfo <Row>(host, datasetId); }