public GeoPointDto Get(string key) { GeoPointDto result = new GeoPointDto(); string queryString = "select top(1) Location, createDate, SerialNumber from geoPoint where SerialNumber like (?) order by createDate desc"; IFieldsQueryCursor query2 = cache.Query(new SqlFieldsQuery(queryString, key)); var query3 = cache.QueryFields(new SqlFieldsQuery(queryString, key)); var resQ3 = query3.GetAll(); foreach (var item in resQ3) { var tempRow = item[0].ToString().Remove(0, 6).Replace("(", "").Replace(")", "").Split(" "); Coordinate c = Cartesian.CartesianToLatLong(double.Parse(tempRow[0]), double.Parse(tempRow[1]), double.Parse(tempRow[2])); var point = new Dto.Point() { elevation = 0, Latitude = c.Latitude.DecimalDegree, Longitude = c.Longitude.DecimalDegree }; result.Point = point; result.CreateDate = DateTime.Parse(item[1].ToString()); result.SerialNumber = item[2].ToString(); } return(result); }
public static void Main() { using (IIgniteClient ignite = Ignition.StartClient(Utils.GetThinClientConfiguration())) { Console.WriteLine(); Console.WriteLine(">>> Cache query DDL example started."); // Create dummy cache to act as an entry point for SQL queries (new SQL API which do not require this // will appear in future versions, JDBC and ODBC drivers do not require it already). var cacheCfg = new CacheClientConfiguration("dummy_cache") { SqlSchema = "PUBLIC", CacheMode = CacheMode.Replicated }; ICacheClient <object, object> cache = ignite.GetOrCreateCache <object, object>(cacheCfg); // Create reference City table based on REPLICATED template. cache.Query(new SqlFieldsQuery( "CREATE TABLE city (id LONG PRIMARY KEY, name VARCHAR) WITH \"template=replicated\"")).GetAll(); // Create table based on PARTITIONED template with one backup. cache.Query(new SqlFieldsQuery( "CREATE TABLE person (id LONG, name VARCHAR, city_id LONG, PRIMARY KEY (id, city_id)) " + "WITH \"backups=1, affinity_key=city_id\"")).GetAll(); // Create an index. cache.Query(new SqlFieldsQuery("CREATE INDEX on Person (city_id)")).GetAll(); Console.WriteLine("\n>>> Created database objects."); const string addCity = "INSERT INTO city (id, name) VALUES (?, ?)"; cache.Query(new SqlFieldsQuery(addCity, 1L, "Forest Hill")); cache.Query(new SqlFieldsQuery(addCity, 2L, "Denver")); cache.Query(new SqlFieldsQuery(addCity, 3L, "St. Petersburg")); const string addPerson = "INSERT INTO person (id, name, city_id) values (?, ?, ?)"; cache.Query(new SqlFieldsQuery(addPerson, 1L, "John Doe", 3L)); cache.Query(new SqlFieldsQuery(addPerson, 2L, "Jane Roe", 2L)); cache.Query(new SqlFieldsQuery(addPerson, 3L, "Mary Major", 1L)); cache.Query(new SqlFieldsQuery(addPerson, 4L, "Richard Miles", 2L)); Console.WriteLine("\n>>> Populated data."); IFieldsQueryCursor res = cache.Query(new SqlFieldsQuery( "SELECT p.name, c.name FROM Person p INNER JOIN City c on c.id = p.city_id")); Console.WriteLine("\n>>> Query results:"); foreach (var row in res) { Console.WriteLine("{0}, {1}", row[0], row[1]); } cache.Query(new SqlFieldsQuery("drop table Person")).GetAll(); cache.Query(new SqlFieldsQuery("drop table City")).GetAll(); Console.WriteLine("\n>>> Dropped database objects."); } Console.WriteLine(); Console.WriteLine(">>> Example finished, press any key to exit ..."); Console.ReadKey(); }
static void Main() { using (var ignite = Ignition.Start()) { Console.WriteLine(">> cache query SQL DDL example"); //create a cache to act as an entry point SQL queries var cachecfg = new CacheConfiguration(CacheName) { SqlSchema = "PUBLIC", CacheMode = CacheMode.Replicated }; ICache <object, object> cache = ignite.GetOrCreateCache <object, object>(cachecfg); // Create reference City table based on REPLICATED template. cache.Query(new SqlFieldsQuery( "CREATE TABLE city (id LONG PRIMARY KEY, name VARCHAR) WITH \"template=replicated\"")).GetAll(); // Create table based on PARTITIONED template with one backup. cache.Query(new SqlFieldsQuery( "CREATE TABLE person (id LONG, name VARCHAR, city_id LONG, PRIMARY KEY (id, city_id)) " + "WITH \"backups=1, affinity_key=city_id\"")).GetAll(); // Create an index. cache.Query(new SqlFieldsQuery("CREATE INDEX on Person (city_id)")).GetAll(); Console.WriteLine("\n>>> Created database objects."); const string addCity = "INSERT INTO city (id, name) VALUES (?, ?)"; cache.Query(new SqlFieldsQuery(addCity, 1, "qewe")); cache.Query(new SqlFieldsQuery(addCity, 2, "zxcf")); cache.Query(new SqlFieldsQuery(addCity, 3, "lkj")); const string addPerson = "INSERT INTO person (id, name, city_id) values (?, ?, ?)"; cache.Query(new SqlFieldsQuery(addPerson, 1, "John ", 3)); cache.Query(new SqlFieldsQuery(addPerson, 2, "Micky", 2)); cache.Query(new SqlFieldsQuery(addPerson, 3, "sdnj", 1)); cache.Query(new SqlFieldsQuery(addPerson, 4, "Richard", 2)); Console.WriteLine("\n>>> Populated data."); IFieldsQueryCursor res = cache.Query(new SqlFieldsQuery( "SELECT p.name, c.name FROM Person p INNER JOIN City c on c.id = p.city_id")); Console.WriteLine("\n>>> Query results:"); foreach (var row in res) { Console.WriteLine("{0}, {1}", row[0], row[1]); } cache.Query(new SqlFieldsQuery("drop table Person")).GetAll(); cache.Query(new SqlFieldsQuery("drop table City")).GetAll(); Console.WriteLine("\n>>> Dropped database objects."); } Console.WriteLine(); Console.WriteLine(">>> Example finished, press any key to exit ..."); Console.ReadKey(); }
public static void Main() { using (var ignite = Ignition.StartFromApplicationConfiguration()) { Console.WriteLine(); Console.WriteLine(">>> Cache query DDL example started."); var cacheCfg = new CacheConfiguration(DummyCacheName) { SqlSchema = "PUBLIC", CacheMode = CacheMode.Replicated }; ICache <object, object> cache = ignite.GetOrCreateCache <object, object>(cacheCfg); cache.Query(new SqlFieldsQuery( "CREATE TABLE city (id LONG PRIMARY KEY, name VARCHAR) WITH \"template=replicated\"")).GetAll(); cache.Query(new SqlFieldsQuery( "CREATE TABLE person (id LONG, name VARCHAR, city_id LONG, PRIMARY KEY (id, city_id)) " + "WITH \"backups=1, affinity_key=city_id\"")).GetAll(); cache.Query(new SqlFieldsQuery("CREATE INDEX on Person (city_id)")).GetAll(); Console.WriteLine("\n>>> Created database objects."); const string addCity = "INSERT INTO city (id, name) VALUES (?, ?)"; cache.Query(new SqlFieldsQuery(addCity, 1L, "Bangalore")); cache.Query(new SqlFieldsQuery(addCity, 2L, "Delhi")); cache.Query(new SqlFieldsQuery(addCity, 3L, "Pune")); const string addPerson = "INSERT INTO person (id, name, city_id) values (?, ?, ?)"; cache.Query(new SqlFieldsQuery(addPerson, 1L, "Nitesh", 3L)); cache.Query(new SqlFieldsQuery(addPerson, 2L, "Bala", 2L)); cache.Query(new SqlFieldsQuery(addPerson, 3L, "Ram", 1L)); cache.Query(new SqlFieldsQuery(addPerson, 4L, "Suraj", 2L)); Console.WriteLine("\n>>> Populated data."); IFieldsQueryCursor res = cache.Query(new SqlFieldsQuery( "SELECT p.name, c.name FROM Person p INNER JOIN City c on c.id = p.city_id")); Console.WriteLine("\n>>> Query results:"); foreach (var row in res) { Console.WriteLine("{0}, {1}", row[0], row[1]); } cache.Query(new SqlFieldsQuery("drop table Person")).GetAll(); cache.Query(new SqlFieldsQuery("drop table City")).GetAll(); Console.WriteLine("\n>>> Dropped database objects."); } Console.WriteLine(); Console.WriteLine(">>> Example finished, press any key to exit ..."); Console.ReadKey(); }