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