public void CreateEmptyAthleteRecord()
{
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = new Mono.Data.Sqlite.SqliteConnection("Data Source=" + this.getDbFileName());
conn.Open();
var command = conn.CreateCommand();
// note that AthleteID is not auto-incremented
command.CommandText = @"
INSERT INTO [Athlete] (AthleteID,EMail,Name,IsPro,Gender,Country,MetroID,TimeModified,TimeCreated,SnookerAbout)
VALUES
(0,'','',0,0,'',0,@TimeModified,@TimeCreated,'')
";
DateTime now = DateTimeHelper.GetUtcNow();
command.Parameters.Add(new Mono.Data.Sqlite.SqliteParameter("@TimeModified", now));
command.Parameters.Add(new Mono.Data.Sqlite.SqliteParameter("@TimeCreated", now));
command.ExecuteNonQuery();
}
catch (Exception exc)
{
throw new Exception("Failed to create Athlete record. Message: " + TraceHelper.ExceptionToString(exc));
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
private void upgradeDataTablesIfNecessary()
{
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = new Mono.Data.Sqlite.SqliteConnection("Data Source=" + this.getDbFileName());
conn.Open();
if (!this.checkIfColumnExists(conn, "Score", "ExtraData"))
{
this.createColumn(conn, "Score", "ExtraData", "ntext");
}
}
catch (Exception exc)
{
throw new Exception("Failed to update data table(s)", exc);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void dbSetCommand(String dbPathFile, String commands)
{
bool exists = System.IO.File.Exists(dbPathFile);
if (exists)
{
try
{
Mono.Data.Sqlite.SqliteConnection connection =
new Mono.Data.Sqlite.SqliteConnection("Data Source=" + dbPathFile);
connection.Open();
Mono.Data.Sqlite.SqliteCommand dbcmd = connection.CreateCommand();
dbcmd.CommandText = commands;
dbcmd.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
showMessage("Erro ao executar SqliteCommand: " + ex.Message);
}
}
else
{
showMessage("O db " + dbPathFile + ", não foi encontrado.");
}
}
public static bool VacuumDb(string file)
{
bool ret = true;
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=True;Page Size=32768", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", file);
#endif
cn.Open();
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "vacuum;";
cmd.ExecuteNonQuery();
}
cn.Close();
}
}
}
catch (Exception ex)
{
Debug.WriteLine("VacuumDb: " + ex.ToString());
ret = false;
}
return(ret);
}
public override bool TableExists(string tableName)
{
object result;
string sql = string.Format("select count(name) from sqlite_master where name = '{0}'", tableName);
using (var cn = new SQLiteConnectionAlias(this.ConnectionString))
{
using (var cmd = new SQLiteCommandAlias(sql, cn))
{
cn.Open();
try
{
result = cmd.ExecuteScalar();
}
catch (SQLiteExceptionAlias)
{
throw;
}
finally
{
cn.Close();
}
}
}
return((System.Int64)result > 0);
}
public static bool CreateEmptyDB(string file)
{
bool ret = true;
try
{
string dir = Path.GetDirectoryName(file);
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False", file);
#endif
cn.Open();
{
using (DbTransaction tr = cn.BeginTransaction())
{
try
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = Properties.Resources.IpCacheCreateDb;
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch (Exception exx)
{
Console.WriteLine("CreateEmptyDB: " + exx.ToString());
Debug.WriteLine("CreateEmptyDB: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("CreateEmptyDB: " + ex.ToString());
#endif
Debug.WriteLine("CreateEmptyDB: " + ex.ToString());
ret = false;
}
return(ret);
}
public static void Main(string[] args)
{
Mono.Data.Sqlite.SqliteConnection v_con = null;
Mono.Data.Sqlite.SqliteDataAdapter v_adapter = null;
System.Data.DataTable v_table;
Console.WriteLine("Exemplo SQLite usando DataAdapter");
Console.WriteLine();
try
{
// 1) instanciando Connection
v_con = new Mono.Data.Sqlite.SqliteConnection(
"Data Source=../../../databases/lugares.db;Version=3;Synchronous=Full;Journal Mode=Off;"
);
// 2) abrindo Connection
v_con.Open();
// 3) instanciando DataAdapter
v_adapter = new Mono.Data.Sqlite.SqliteDataAdapter("select * from estados", v_con);
// 4) instanciando DataTable
v_table = new System.Data.DataTable("RESULTADO");
// 5) alimentando DataTable
v_adapter.Fill(v_table);
// 6) usando DataTable (imprimindo na tela)
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", c.ColumnName);
Console.WriteLine();
foreach (System.Data.DataRow r in v_table.Rows)
{
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", r[c].ToString());
Console.WriteLine();
}
}
catch (Mono.Data.Sqlite.SqliteException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
// 7) fechando e liberando Connection
if (v_con != null)
{
v_con.Close();
v_con = null;
}
}
Console.ReadKey();
}
public static void Main(string[] args)
{
Mono.Data.Sqlite.SqliteConnection v_con = null;
Mono.Data.Sqlite.SqliteCommand v_cmd = null;
Console.WriteLine("Exemplo SQLite usando Command");
Console.WriteLine();
try
{
// 1) instanciando Connection
v_con = new Mono.Data.Sqlite.SqliteConnection(
"Data Source=../../../databases/lugares.db;Version=3;Synchronous=Full;Journal Mode=Off;"
);
// 2) abrindo Connection
v_con.Open();
// 3) instanciando Command
v_cmd = new Mono.Data.Sqlite.SqliteCommand(
//"insert into estados values (60, 'WI', 'William Ivanski')",
"delete from estados where codigo = 60",
v_con
);
// 4) executando Command
v_cmd.ExecuteNonQuery();
Console.WriteLine("Ok.");
}
catch (Mono.Data.Sqlite.SqliteException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
// 5) liberando Command
if (v_cmd != null)
{
v_cmd.Cancel();
v_cmd.Dispose();
v_cmd = null;
}
// 6) fechando e liberando Connection
if (v_con != null)
{
v_con.Close();
v_con = null;
}
}
Console.ReadKey();
}
public static bool PreAllocateDB(string file, int addSizeInMBytes)
{
bool ret = true;
try
{
Debug.WriteLine("PreAllocateDB: " + file + ", +" + addSizeInMBytes + "MB");
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768", file);
#endif
cn.Open();
{
using (DbTransaction tr = cn.BeginTransaction())
{
try
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = string.Format("create table large (a); insert into large values (zeroblob({0})); drop table large;", addSizeInMBytes * 1024 * 1024);
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch (Exception exx)
{
#if MONO
Console.WriteLine("PreAllocateDB: " + exx.ToString());
#endif
Debug.WriteLine("PreAllocateDB: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("PreAllocateDB: " + ex.ToString());
#endif
Debug.WriteLine("PreAllocateDB: " + ex.ToString());
ret = false;
}
return(ret);
}
public IpInfo GetDataFromCache(string ip)
{
IpInfo ret = null;
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";", _db);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},Default Timeout=33", db);
#endif
cn.Open();
{
using (DbCommand com = cn.CreateCommand())
{
com.CommandText = "SELECT * FROM Cache WHERE Ip = '" + ip + "'";
using (DbDataReader rd = com.ExecuteReader())
{
if (rd.Read())
{
IpInfo val = new IpInfo();
{
val.Ip = ip;
val.CountryName = rd["CountryName"] as string;
val.RegionName = rd["RegionName"] as string;
val.City = rd["City"] as string;
val.Latitude = (double)rd["Latitude"];
val.Longitude = (double)rd["Longitude"];
val.CacheTime = (DateTime)rd["Time"];
}
ret = val;
}
rd.Close();
}
}
}
cn.Close();
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("GetDataFromCache: " + ex.ToString());
#endif
Debug.WriteLine("GetDataFromCache: " + ex.ToString());
ret = null;
}
return(ret);
}
public static MySqlConnection GetConnection()
{
MySqlConnection db = null;
try {
string conn_string = WebConfigurationManager.ConnectionStrings["CommunityAddinRepoConnectionString"].ConnectionString;
db = new MySqlConnection (conn_string);
db.Open ();
return db;
} catch (Exception ex) {
if (db != null)
db.Close ();
throw new Exception ("Database connection failed", ex);
}
}
private bool disposedValue = false; // To detect redundant calls
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
Database.Close();
//This necessary because SQLite doesn't seem to totally close the
//Database until the finalizer is called.
GC.Collect();
GC.WaitForPendingFinalizers();
}
disposedValue = true;
}
}
public static MySqlConnection GetConnection()
{
MySqlConnection db = null;
try {
string conn_string = WebConfigurationManager.ConnectionStrings["CommunityAddinRepoConnectionString"].ConnectionString;
db = new MySqlConnection(conn_string);
db.Open();
return(db);
} catch (Exception ex) {
if (db != null)
{
db.Close();
}
throw new Exception("Database connection failed", ex);
}
}
//dbPathFile --> path file do banco de dados
//tablename --> nome da tabela
//resultCol --> coluna da tabela que sera retornada Ex."Nome"
//refCol --> coluna de referencia para comparação Ex."Senha"
//refData --> dado que sera comparado com a coluna de referencia Ex."123"
public String dbGetCommand(String dbPathFile, String tableName, String resultCol, String refCol, String refData)
{
int i = 0;
String resultModel = "";
String strQuery = "SELECT * FROM " + tableName;
ArrayList readArray = new ArrayList();
try
{
Mono.Data.Sqlite.SqliteConnection connection =
new Mono.Data.Sqlite.SqliteConnection("Data Source=" + dbPathFile);
connection.Open();
Mono.Data.Sqlite.SqliteCommand dbcmd = connection.CreateCommand();
dbcmd.CommandText = strQuery;
//SqliteDataReader rdr = cmd.ExecuteReader()
Mono.Data.Sqlite.SqliteDataReader rdr = dbcmd.ExecuteReader();
while (rdr.Read())
{
i++;
//if(rdr[refCol].ToString() == refData){
// resultModel = rdr[resultCol].ToString();
//}
if (rdr[refCol].ToString() == refData)
{
resultModel = rdr[resultCol].ToString();
}
}
connection.Close();
}
catch (Exception ex)
{
showMessage("Erro ao executar SqliteDataReader: " + ex.Message);
}
return(resultModel);
}
private void btnTestSqlite_Click(object sender, EventArgs e)
{
try
{
string dbPath = Path.GetDirectoryName(Application.ExecutablePath);
var db = new Mono.Data.Sqlite.SqliteConnection("URI=file:" + Path.Combine(dbPath, "test.db"));
db.Open();
db.Close();
MessageBox.Show("ok");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public bool Converti(String Da, String A)
{
var SA = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Da + ";";
var SS = "Version=3;Data Source=" + A + ";";
using (System.Data.OleDb.OleDbConnection connA = new System.Data.OleDb.OleDbConnection(SA))
using (var connS =
#if __MonoCS__
new Mono.Data.Sqlite.SqliteConnection(SS)
#else
new System.Data.SQLite.SQLiteConnection(SS)
#endif
)
{
connA.Close();
connS.Close();
}
return(true);
}
public MemoryMappedFileRepository(ChunkDataRepositoryCollection Repo, string Path, string Name)
: base(Repo)
{
if (Repo == null)
{
throw new ArgumentNullException("Repo");
}
if (Path == null)
{
throw new ArgumentNullException("Path");
}
if (Name == null)
{
throw new ArgumentNullException("Name");
}
publicChunks = new SortedSet <ChunkDescriptor> (new ChunkDescriptor.Comparer_Hash());
path = Path;
name = Name;
string FPath = Path + System.IO.Path.DirectorySeparatorChar + Name;
bool Exists = System.IO.File.Exists(FPath);
if (!Exists)
{
System.IO.File.Create(FPath);
}
Base = new Mono.Data.Sqlite.SqliteConnection("Data Source=" + FPath);
if (!Exists)
{
try{
id = Guid.NewGuid();
Base.Open();
using (Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand(
"CREATE TABLE CHUNKS(" +
"ID BLOB(64) NOT NULL, " +
"HAS_DEPENDENDANTS BIT NOT NULL, " +
"Path TEXT(400) NOT NULL)", Base))
Comm.ExecuteNonQuery();
using (Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand(
"CREATE TABLE ATTRIBUTES(" +
"NAME TEXT(128) PRIMARY KEY NOT NULL, " +
"VALUE BLOB(1024) NOT NULL)", Base))
Comm.ExecuteNonQuery();
using (Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand(
"CREATE TABLE DEPENDENCIES(" +
"ID BLOB(64) NOT NULL, " +
"DependantIDs BLOB(896) NOT NULL)", Base)) //default sqlite3 page size = 1024 = 64 for ID + 40 for everything sqlite needs + 920 which is a bit bigger than 14 Dependencies(896).
Comm.ExecuteNonQuery();
using (Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand(
"INSERT INTO ATTRIBUTES(" +
"NAME, " +
"VALUE) VALUES('ID', @p0)", Base)){
Comm.Parameters.AddWithValue("p0", ID.ToByteArray());
Comm.ExecuteNonQuery();
}
}
catch (Exception ex) {
if (Base != null)
{
if (Base.State == System.Data.ConnectionState.Open)
{
Base.Close();
}
Base.Dispose();
}
if (System.IO.File.Exists(FPath))
{
System.IO.File.Delete(FPath);
}
throw ex;
}
}
else
{
Base.Open();
}
}
PureImage PureImageCache.GetImageFromCache(int type, GPoint pos, int zoom)
{
PureImage ret = null;
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
if(!string.IsNullOrEmpty(attachSqlQuery))
{
using(DbCommand com = cn.CreateCommand())
{
com.CommandText = attachSqlQuery;
int x = com.ExecuteNonQuery();
//Debug.WriteLine("Attach: " + x);
}
}
using(DbCommand com = cn.CreateCommand())
{
com.CommandText = string.Format(finnalSqlSelect, pos.X, pos.Y, zoom, type);
using(DbDataReader rd = com.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
if(rd.Read())
{
long length = rd.GetBytes(0, 0, null, 0, 0);
byte[] tile = new byte[length];
rd.GetBytes(0, 0, tile, 0, tile.Length);
{
if(GMapProvider.TileImageProxy != null)
{
ret = GMapProvider.TileImageProxy.FromArray(tile);
}
}
tile = null;
}
rd.Close();
}
}
if(!string.IsNullOrEmpty(detachSqlQuery))
{
using(DbCommand com = cn.CreateCommand())
{
com.CommandText = detachSqlQuery;
int x = com.ExecuteNonQuery();
//Debug.WriteLine("Detach: " + x);
}
}
}
cn.Close();
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("GetImageFromCache: " + ex.ToString());
#endif
Debug.WriteLine("GetImageFromCache: " + ex.ToString());
ret = null;
}
return ret;
}
bool PureImageCache.PutImageToCache(byte[] tile, int type, GPoint pos, int zoom)
{
bool ret = true;
if(Created)
{
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
using(DbTransaction tr = cn.BeginTransaction())
{
try
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = singleSqlInsert;
cmd.Parameters.Add(new SQLiteParameter("@p1", pos.X));
cmd.Parameters.Add(new SQLiteParameter("@p2", pos.Y));
cmd.Parameters.Add(new SQLiteParameter("@p3", zoom));
cmd.Parameters.Add(new SQLiteParameter("@p4", type));
cmd.Parameters.Add(new SQLiteParameter("@p5", DateTime.Now));
cmd.ExecuteNonQuery();
}
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = singleSqlInsertLast;
cmd.Parameters.Add(new SQLiteParameter("@p1", tile));
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
tr.Rollback();
ret = false;
}
}
}
cn.Close();
}
if(Interlocked.Increment(ref preAllocationPing) % 22 == 0)
{
CheckPreAllocation();
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
ret = false;
}
}
return ret;
}
public static bool VacuumDb(string file)
{
bool ret = true;
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=True;Page Size=32768", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", file);
#endif
cn.Open();
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "vacuum;";
cmd.ExecuteNonQuery();
}
cn.Close();
}
}
}
catch(Exception ex)
{
Debug.WriteLine("VacuumDb: " + ex.ToString());
ret = false;
}
return ret;
}
private static bool AlterDBAddTimeColumn(string file)
{
bool ret = true;
try
{
if(File.Exists(file))
{
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768;Pooling=True", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768,Pooling=True", file);
#endif
cn.Open();
{
using(DbTransaction tr = cn.BeginTransaction())
{
bool? NoCacheTimeColumn = null;
try
{
using(DbCommand cmd = new SQLiteCommand("SELECT CacheTime FROM Tiles", cn))
{
cmd.Transaction = tr;
using(DbDataReader rd = cmd.ExecuteReader())
{
rd.Close();
}
NoCacheTimeColumn = false;
}
}
catch(Exception ex)
{
if(ex.Message.Contains("no such column: CacheTime"))
{
NoCacheTimeColumn = true;
}
else
{
throw ex;
}
}
try
{
if(NoCacheTimeColumn.HasValue && NoCacheTimeColumn.Value)
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "ALTER TABLE Tiles ADD CacheTime DATETIME";
cmd.ExecuteNonQuery();
}
tr.Commit();
NoCacheTimeColumn = false;
}
}
catch(Exception exx)
{
#if MONO
Console.WriteLine("AlterDBAddTimeColumn: " + exx.ToString());
#endif
Debug.WriteLine("AlterDBAddTimeColumn: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
else
{
ret = false;
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("AlterDBAddTimeColumn: " + ex.ToString());
#endif
Debug.WriteLine("AlterDBAddTimeColumn: " + ex.ToString());
ret = false;
}
return ret;
}
public MemoryMappedFileRepository(ChunkDataRepositoryCollection Repo, string Path, string Name)
: base(Repo)
{
if (Repo == null)
throw new ArgumentNullException ("Repo");
if (Path == null)
throw new ArgumentNullException ("Path");
if (Name == null)
throw new ArgumentNullException ("Name");
publicChunks = new SortedSet<ChunkDescriptor> (new ChunkDescriptor.Comparer_Hash());
path = Path;
name = Name;
string FPath = Path + System.IO.Path.DirectorySeparatorChar + Name;
bool Exists = System.IO.File.Exists (FPath);
if (!Exists)
System.IO.File.Create (FPath);
Base = new Mono.Data.Sqlite.SqliteConnection ("Data Source=" + FPath);
if (!Exists) {
try{
id = Guid.NewGuid();
Base.Open();
using(Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand (
"CREATE TABLE CHUNKS(" +
"ID BLOB(64) NOT NULL, " +
"HAS_DEPENDENDANTS BIT NOT NULL, " +
"Path TEXT(400) NOT NULL)",Base))
Comm.ExecuteNonQuery();
using(Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand (
"CREATE TABLE ATTRIBUTES(" +
"NAME TEXT(128) PRIMARY KEY NOT NULL, " +
"VALUE BLOB(1024) NOT NULL)",Base))
Comm.ExecuteNonQuery();
using(Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand (
"CREATE TABLE DEPENDENCIES(" +
"ID BLOB(64) NOT NULL, " +
"DependantIDs BLOB(896) NOT NULL)",Base)) //default sqlite3 page size = 1024 = 64 for ID + 40 for everything sqlite needs + 920 which is a bit bigger than 14 Dependencies(896).
Comm.ExecuteNonQuery();
using(Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand (
"INSERT INTO ATTRIBUTES(" +
"NAME, " +
"VALUE) VALUES('ID', @p0)",Base)){
Comm.Parameters.AddWithValue("p0", ID.ToByteArray());
Comm.ExecuteNonQuery();
}
}
catch(Exception ex){
if(Base != null) {
if(Base.State == System.Data.ConnectionState.Open) {
Base.Close();
}
Base.Dispose();
}
if(System.IO.File.Exists (FPath))
System.IO.File.Delete(FPath);
throw ex;
}
}
else
Base.Open();
}
private static bool AlterDBAddTimeColumn(string file)
{
bool ret = true;
try
{
if (File.Exists(file))
{
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768;Pooling=True", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768,Pooling=True", file);
#endif
cn.Open();
{
using (DbTransaction tr = cn.BeginTransaction())
{
bool?NoCacheTimeColumn = null;
try
{
using (DbCommand cmd = new SQLiteCommand("SELECT CacheTime FROM Tiles", cn))
{
cmd.Transaction = tr;
using (DbDataReader rd = cmd.ExecuteReader())
{
rd.Close();
}
NoCacheTimeColumn = false;
}
}
catch (Exception ex)
{
if (ex.Message.Contains("no such column: CacheTime"))
{
NoCacheTimeColumn = true;
}
else
{
throw ex;
}
}
try
{
if (NoCacheTimeColumn.HasValue && NoCacheTimeColumn.Value)
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "ALTER TABLE Tiles ADD CacheTime DATETIME";
cmd.ExecuteNonQuery();
}
tr.Commit();
NoCacheTimeColumn = false;
}
}
catch (Exception exx)
{
#if MONO
Console.WriteLine("AlterDBAddTimeColumn: " + exx.ToString());
#endif
Debug.WriteLine("AlterDBAddTimeColumn: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
else
{
ret = false;
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("AlterDBAddTimeColumn: " + ex.ToString());
#endif
Debug.WriteLine("AlterDBAddTimeColumn: " + ex.ToString());
ret = false;
}
return(ret);
}
public void Dispose()
{
db.Close();
}
public static void Main(string[] args)
{
Mono.Data.Sqlite.SqliteConnection v_con = null;
Mono.Data.Sqlite.SqliteCommand v_cmd = null;
Mono.Data.Sqlite.SqliteDataReader v_reader = null;
System.Data.DataTable v_table;
System.Data.DataRow v_row;
Console.WriteLine("Exemplo SQLite usando DataReader");
Console.WriteLine();
try
{
// 1) instanciando Connection
v_con = new Mono.Data.Sqlite.SqliteConnection(
"Data Source=../../../databases/lugares.db;Version=3;Synchronous=Full;Journal Mode=Off;"
);
// 2) abrindo Connection
v_con.Open();
// 3) instanciando Command
v_cmd = new Mono.Data.Sqlite.SqliteCommand("select * from estados", v_con);
// 4) executando DataReader
v_reader = v_cmd.ExecuteReader();
// 5) criando DataTable
v_table = new System.Data.DataTable("RESULTADO");
for (int i = 0; i < v_reader.FieldCount; i++)
v_table.Columns.Add(v_reader.GetName(i), typeof(string));
// 6) alimentando DataTable
while (v_reader.Read())
{
v_row = v_table.NewRow();
for (int i = 0; i < v_reader.FieldCount; i++)
v_row[i] = v_reader[i].ToString();
v_table.Rows.Add(v_row);
}
// 7) usando DataTable (imprimindo na tela)
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", c.ColumnName);
Console.WriteLine();
foreach (System.Data.DataRow r in v_table.Rows)
{
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", r[c].ToString());
Console.WriteLine();
}
}
catch (Mono.Data.Sqlite.SqliteException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
// 8) liberando Command
if (v_cmd != null)
{
v_cmd.Cancel();
v_cmd.Dispose();
v_cmd = null;
}
// 9) liberando DataReader
if (v_reader != null)
{
v_reader.Close();
v_reader = null;
}
// 10) fechando e liberando Connection
if (v_con != null)
{
v_con.Close();
v_con = null;
}
}
Console.ReadKey();
}
public bool PutDataToCache(string ip, IpInfo data)
{
bool ret = true;
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";", _db);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Default Timeout=33", db);
#endif
cn.Open();
{
{
using (DbTransaction tr = cn.BeginTransaction())
{
try
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "INSERT INTO Cache(Ip, CountryName, RegionName, City, Latitude, Longitude, Time) VALUES(@p1, @p2, @p3, @p4, @p5, @p6, @p7)";
cmd.Parameters.Add(new SQLiteParameter("@p1", ip));
cmd.Parameters.Add(new SQLiteParameter("@p2", data.CountryName));
cmd.Parameters.Add(new SQLiteParameter("@p3", data.RegionName));
cmd.Parameters.Add(new SQLiteParameter("@p4", data.City));
cmd.Parameters.Add(new SQLiteParameter("@p5", data.Latitude));
cmd.Parameters.Add(new SQLiteParameter("@p6", data.Longitude));
cmd.Parameters.Add(new SQLiteParameter("@p7", data.CacheTime));
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch (Exception ex)
{
Console.WriteLine("PutDataToCache: " + ex.ToString());
Debug.WriteLine("PutDataToCache: " + ex.ToString());
tr.Rollback();
ret = false;
}
}
}
}
cn.Close();
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("PutDataToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutDataToCache: " + ex.ToString());
ret = false;
}
return(ret);
}
/// <summary>
/// gets routes from gpsd log file
/// </summary>
/// <param name="gpsdLogFile"></param>
/// <param name="start">start time(UTC) of route, null to read from very start</param>
/// <param name="end">end time(UTC) of route, null to read to the very end</param>
/// <param name="maxPositionDilutionOfPrecision">max value of PositionDilutionOfPrecision, null to get all</param>
/// <returns></returns>
public static IEnumerable<List<GpsLog>> GetRoutesFromMobileLog(string gpsdLogFile, DateTime? start, DateTime? end, double? maxPositionDilutionOfPrecision)
{
#if SQLite
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=True;", gpsdLogFile);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True", gpsdLogFile);
#endif
cn.Open();
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM GPS ";
if(start.HasValue)
{
cmd.CommandText += "WHERE TimeUTC >= @t1 ";
SQLiteParameter lookupValue = new SQLiteParameter("@t1", start);
cmd.Parameters.Add(lookupValue);
}
if(end.HasValue)
{
if(!start.HasValue)
{
cmd.CommandText += "WHERE ";
}
else
{
cmd.CommandText += "AND ";
}
cmd.CommandText += "TimeUTC <= @t2 ";
SQLiteParameter lookupValue = new SQLiteParameter("@t2", end);
cmd.Parameters.Add(lookupValue);
}
if(maxPositionDilutionOfPrecision.HasValue)
{
if(!start.HasValue && !end.HasValue)
{
cmd.CommandText += "WHERE ";
}
else
{
cmd.CommandText += "AND ";
}
cmd.CommandText += "(PositionDilutionOfPrecision <= @p3)";
SQLiteParameter lookupValue = new SQLiteParameter("@p3", maxPositionDilutionOfPrecision);
cmd.Parameters.Add(lookupValue);
}
using(DbDataReader rd = cmd.ExecuteReader())
{
List<GpsLog> points = new List<GpsLog>();
long lastSessionCounter = -1;
while(rd.Read())
{
GpsLog log = new GpsLog();
{
log.TimeUTC = (DateTime)rd["TimeUTC"];
log.SessionCounter = (long)rd["SessionCounter"];
log.Delta = rd["Delta"] as double?;
log.Speed = rd["Speed"] as double?;
log.SeaLevelAltitude = rd["SeaLevelAltitude"] as double?;
log.EllipsoidAltitude = rd["EllipsoidAltitude"] as double?;
log.SatellitesInView = rd["SatellitesInView"] as System.Byte?;
log.SatelliteCount = rd["SatelliteCount"] as System.Byte?;
log.Position = new PointLatLng((double)rd["Lat"], (double)rd["Lng"]);
log.PositionDilutionOfPrecision = rd["PositionDilutionOfPrecision"] as double?;
log.HorizontalDilutionOfPrecision = rd["HorizontalDilutionOfPrecision"] as double?;
log.VerticalDilutionOfPrecision = rd["VerticalDilutionOfPrecision"] as double?;
log.FixQuality = (FixQuality)((byte)rd["FixQuality"]);
log.FixType = (FixType)((byte)rd["FixType"]);
log.FixSelection = (FixSelection)((byte)rd["FixSelection"]);
}
if(log.SessionCounter - lastSessionCounter != 1 && points.Count > 0)
{
List<GpsLog> ret = new List<GpsLog>(points);
points.Clear();
{
yield return ret;
}
}
points.Add(log);
lastSessionCounter = log.SessionCounter;
}
if(points.Count > 0)
{
List<GpsLog> ret = new List<GpsLog>(points);
points.Clear();
{
yield return ret;
}
}
points.Clear();
points = null;
rd.Close();
}
}
}
cn.Close();
}
#else
return null;
#endif
}
bool PureImageCache.PutImageToCache(byte[] tile, int type, GPoint pos, int zoom)
{
bool ret = true;
if (Created)
{
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
using (DbTransaction tr = cn.BeginTransaction())
{
try
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = singleSqlInsert;
cmd.Parameters.Add(new SQLiteParameter("@p1", pos.X));
cmd.Parameters.Add(new SQLiteParameter("@p2", pos.Y));
cmd.Parameters.Add(new SQLiteParameter("@p3", zoom));
cmd.Parameters.Add(new SQLiteParameter("@p4", type));
cmd.Parameters.Add(new SQLiteParameter("@p5", DateTime.Now));
cmd.ExecuteNonQuery();
}
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = singleSqlInsertLast;
cmd.Parameters.Add(new SQLiteParameter("@p1", tile));
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
tr.Rollback();
ret = false;
}
}
}
cn.Close();
}
if (Interlocked.Increment(ref preAllocationPing) % 22 == 0)
{
CheckPreAllocation();
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
ret = false;
}
}
return(ret);
}
private void createDataTables()
{
// note: sqlite tables always have a hidden "rowid" field
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = new Mono.Data.Sqlite.SqliteConnection("Data Source=" + this.getDbFileName());
conn.Open();
var command = conn.CreateCommand(); // note that AthleteID is not auto-incremented
command.CommandText = @"
CREATE TABLE [Athlete] (
[AthleteID] int PRIMARY KEY NOT NULL,
[EMail] ntext,
[Name] ntext,
[IsPro] bit,
[DOB] date NULL,
[Gender] int,
[Country] ntext,
[MetroID] int,
[Picture] ntext,
[FacebookId] ntext,
[TimeCreated] datetime,
[TimeModified] datetime,
[SnookerAbout] ntext
)";
command.ExecuteNonQuery();
command.CommandText = @"
CREATE TABLE [Result] (
[ResultID] integer PRIMARY KEY NOT NULL,
[AthleteID] int NOT NULL,
[ResultTypeID] int,
[Time] float NULL,
[Distance] float NULL,
[Count] int NULL,
[Count2] int NULL,
[Date] date NULL,
[Notes] ntext,
[TimeModified] datetime,
[Guid] nvarchar(50),
[IsDeleted] bit NOT NULL,
[VenueID] int NULL,
[OpponentAthleteID] int NULL,
[OpponentConfirmation] int,
[Type1] int NULL,
[Details1] ntext
)";
command.ExecuteNonQuery();
command.CommandText = @"
CREATE TABLE [Score] (
[ScoreID] integer PRIMARY KEY NOT NULL,
[AthleteAID] int NOT NULL,
[AthleteBID] int NOT NULL,
[Date] date NOT NULL,
[IsUnfinished] int NOT NULL,
[TimeModified] date NOT NULL,
[Guid] nvarchar(50),
[IsDeleted] bit NOT NULL,
[SportID] int NOT NULL,
[VenueID] int NULL,
[Type1] int NULL,
[OpponentConfirmation] int,
[PointsA] int NOT NULL,
[PointsB] int NOT NULL,
[InnerPoints1A] int NOT NULL,
[InnerPoints1B] int NOT NULL,
[InnerPoints2A] int NOT NULL,
[InnerPoints2B] int NOT NULL,
[InnerPoints3A] int NOT NULL,
[InnerPoints3B] int NOT NULL,
[InnerPoints4A] int NOT NULL,
[InnerPoints4B] int NOT NULL,
[InnerPoints5A] int NOT NULL,
[InnerPoints5B] int NOT NULL,
[InnerPoints6A] int NOT NULL,
[InnerPoints6B] int NOT NULL,
[InnerPoints7A] int NOT NULL,
[InnerPoints7B] int NOT NULL,
[InnerPoints8A] int NOT NULL,
[InnerPoints8B] int NOT NULL,
[InnerPoints9A] int NOT NULL,
[InnerPoints9B] int NOT NULL,
[InnerPoints10A] int NOT NULL,
[InnerPoints10B] int NOT NULL,
[ExtraData] ntext
)";
command.ExecuteNonQuery();
command.CommandText = @"
CREATE TABLE [Singular] (
[ID] int,
[DateDbCreated] datetime,
[MyAthleteID] int,
[AccessToken] ntext,
[UserWantsToBeGuest] int
)";
command.ExecuteNonQuery();
command.CommandText = @"
INSERT INTO [Singular] (ID,DateDbCreated,MyAthleteID,AccessToken,UserWantsToBeGuest) VALUES (0,@Date,0,'',0)
";
command.Parameters.Add(new Mono.Data.Sqlite.SqliteParameter("@Date", DateTime.Now.ToUniversalTime()));
command.ExecuteNonQuery();
}
catch (Exception exc)
{
throw new Exception("Failed to create tables. Message: " + TraceHelper.ExceptionToString(exc));
}
finally
{
if (conn != null)
{
conn.Close();
}
}
CreateEmptyAthleteRecord();
}
public IpInfo GetDataFromCache(string ip)
{
IpInfo ret = null;
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";", db);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},Default Timeout=33", db);
#endif
cn.Open();
{
using(DbCommand com = cn.CreateCommand())
{
com.CommandText = "SELECT * FROM Cache WHERE Ip = '" + ip + "'";
using(DbDataReader rd = com.ExecuteReader())
{
if(rd.Read())
{
IpInfo val = new IpInfo();
{
val.Ip = ip;
val.CountryName = rd["CountryName"] as string;
val.RegionName = rd["RegionName"] as string;
val.City = rd["City"] as string;
val.Latitude = (double)rd["Latitude"];
val.Longitude = (double)rd["Longitude"];
val.CacheTime = (DateTime)rd["Time"];
}
ret = val;
}
rd.Close();
}
}
}
cn.Close();
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("GetDataFromCache: " + ex.ToString());
#endif
Debug.WriteLine("GetDataFromCache: " + ex.ToString());
ret = null;
}
return ret;
}
public bool PutDataToCache(string ip, IpInfo data)
{
bool ret = true;
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";", db);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Default Timeout=33", db);
#endif
cn.Open();
{
{
using(DbTransaction tr = cn.BeginTransaction())
{
try
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "INSERT INTO Cache(Ip, CountryName, RegionName, City, Latitude, Longitude, Time) VALUES(@p1, @p2, @p3, @p4, @p5, @p6, @p7)";
cmd.Parameters.Add(new SQLiteParameter("@p1", ip));
cmd.Parameters.Add(new SQLiteParameter("@p2", data.CountryName));
cmd.Parameters.Add(new SQLiteParameter("@p3", data.RegionName));
cmd.Parameters.Add(new SQLiteParameter("@p4", data.City));
cmd.Parameters.Add(new SQLiteParameter("@p5", data.Latitude));
cmd.Parameters.Add(new SQLiteParameter("@p6", data.Longitude));
cmd.Parameters.Add(new SQLiteParameter("@p7", data.CacheTime));
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch(Exception ex)
{
Console.WriteLine("PutDataToCache: " + ex.ToString());
Debug.WriteLine("PutDataToCache: " + ex.ToString());
tr.Rollback();
ret = false;
}
}
}
}
cn.Close();
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("PutDataToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutDataToCache: " + ex.ToString());
ret = false;
}
return ret;
}
public static bool CreateEmptyDB(string file)
{
bool ret = true;
try
{
string dir = Path.GetDirectoryName(file);
if(!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768", file);
#endif
cn.Open();
{
using(DbTransaction tr = cn.BeginTransaction())
{
try
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
#if !PocketPC
cmd.CommandText = Properties.Resources.CreateTileDb;
#else
cmd.CommandText = GMap.NET.WindowsMobile.Properties.Resources.CreateTileDb;
#endif
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch(Exception exx)
{
#if MONO
Console.WriteLine("CreateEmptyDB: " + exx.ToString());
#endif
Debug.WriteLine("CreateEmptyDB: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("CreateEmptyDB: " + ex.ToString());
#endif
Debug.WriteLine("CreateEmptyDB: " + ex.ToString());
ret = false;
}
return ret;
}
public static bool PreAllocateDB(string file, int addSizeInMBytes)
{
bool ret = true;
try
{
Debug.WriteLine("PreAllocateDB: " + file + ", +" + addSizeInMBytes + "MB");
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768", file);
#endif
cn.Open();
{
using(DbTransaction tr = cn.BeginTransaction())
{
try
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = string.Format("create table large (a); insert into large values (zeroblob({0})); drop table large;", addSizeInMBytes * 1024 * 1024);
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch(Exception exx)
{
#if MONO
Console.WriteLine("PreAllocateDB: " + exx.ToString());
#endif
Debug.WriteLine("PreAllocateDB: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("PreAllocateDB: " + ex.ToString());
#endif
Debug.WriteLine("PreAllocateDB: " + ex.ToString());
ret = false;
}
return ret;
}
public void Close()
{
__connection.Close();
}
PureImage PureImageCache.GetImageFromCache(int type, GPoint pos, int zoom)
{
PureImage ret = null;
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
if (!string.IsNullOrEmpty(attachSqlQuery))
{
using (DbCommand com = cn.CreateCommand())
{
com.CommandText = attachSqlQuery;
int x = com.ExecuteNonQuery();
//Debug.WriteLine("Attach: " + x);
}
}
using (DbCommand com = cn.CreateCommand())
{
com.CommandText = string.Format(finnalSqlSelect, pos.X, pos.Y, zoom, type);
using (DbDataReader rd = com.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
if (rd.Read())
{
long length = rd.GetBytes(0, 0, null, 0, 0);
byte[] tile = new byte[length];
rd.GetBytes(0, 0, tile, 0, tile.Length);
{
if (GMapProvider.TileImageProxy != null)
{
ret = GMapProvider.TileImageProxy.FromArray(tile);
}
}
tile = null;
}
rd.Close();
}
}
if (!string.IsNullOrEmpty(detachSqlQuery))
{
using (DbCommand com = cn.CreateCommand())
{
com.CommandText = detachSqlQuery;
int x = com.ExecuteNonQuery();
//Debug.WriteLine("Detach: " + x);
}
}
}
cn.Close();
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("GetImageFromCache: " + ex.ToString());
#endif
Debug.WriteLine("GetImageFromCache: " + ex.ToString());
ret = null;
}
return(ret);
}
public bool BatchUpDate()
{
bool ret = true;
if (Created)
{
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
using (DbTransaction tr = cn.BeginTransaction())
{
try
{
foreach (var item in BatchPutImageData)
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = singleSqlInsert;
cmd.Parameters.Add(new SQLiteParameter("@p1", item.pos.X));
cmd.Parameters.Add(new SQLiteParameter("@p2", item.pos.Y));
cmd.Parameters.Add(new SQLiteParameter("@p3", item.zoom));
cmd.Parameters.Add(new SQLiteParameter("@p4", item.type));
cmd.Parameters.Add(new SQLiteParameter("@p5", BatchPutImage.curTime));
cmd.ExecuteNonQuery();
}
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = singleSqlInsertLast;
cmd.Parameters.Add(new SQLiteParameter("@p1", item.tile));
cmd.ExecuteNonQuery();
}
}
tr.Commit();
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
tr.Rollback();
ret = false;
}
}
}
cn.Close();
}
if (Interlocked.Increment(ref preAllocationPing) % 22 == 0)
{
CheckPreAllocation();
}
reSetBatchQueue();
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
ret = false;
}
}
return(ret);
}
/// <summary>
/// gets routes from gpsd log file
/// </summary>
/// <param name="gpsdLogFile"></param>
/// <param name="start">start time(UTC) of route, null to read from very start</param>
/// <param name="end">end time(UTC) of route, null to read to the very end</param>
/// <param name="maxPositionDilutionOfPrecision">max value of PositionDilutionOfPrecision, null to get all</param>
/// <returns></returns>
public static IEnumerable <List <GpsLog> > GetRoutesFromMobileLog(string gpsdLogFile, DateTime?start, DateTime?end, double?maxPositionDilutionOfPrecision)
{
#if SQLite
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=True;", gpsdLogFile);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True", gpsdLogFile);
#endif
cn.Open();
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM GPS ";
if (start.HasValue)
{
cmd.CommandText += "WHERE TimeUTC >= @t1 ";
SQLiteParameter lookupValue = new SQLiteParameter("@t1", start);
cmd.Parameters.Add(lookupValue);
}
if (end.HasValue)
{
if (!start.HasValue)
{
cmd.CommandText += "WHERE ";
}
else
{
cmd.CommandText += "AND ";
}
cmd.CommandText += "TimeUTC <= @t2 ";
SQLiteParameter lookupValue = new SQLiteParameter("@t2", end);
cmd.Parameters.Add(lookupValue);
}
if (maxPositionDilutionOfPrecision.HasValue)
{
if (!start.HasValue && !end.HasValue)
{
cmd.CommandText += "WHERE ";
}
else
{
cmd.CommandText += "AND ";
}
cmd.CommandText += "(PositionDilutionOfPrecision <= @p3)";
SQLiteParameter lookupValue = new SQLiteParameter("@p3", maxPositionDilutionOfPrecision);
cmd.Parameters.Add(lookupValue);
}
using (DbDataReader rd = cmd.ExecuteReader())
{
List <GpsLog> points = new List <GpsLog>();
long lastSessionCounter = -1;
while (rd.Read())
{
GpsLog log = new GpsLog();
{
log.TimeUTC = (DateTime)rd["TimeUTC"];
log.SessionCounter = (long)rd["SessionCounter"];
log.Delta = rd["Delta"] as double?;
log.Speed = rd["Speed"] as double?;
log.SeaLevelAltitude = rd["SeaLevelAltitude"] as double?;
log.EllipsoidAltitude = rd["EllipsoidAltitude"] as double?;
log.SatellitesInView = rd["SatellitesInView"] as System.Byte?;
log.SatelliteCount = rd["SatelliteCount"] as System.Byte?;
log.Position = new PointLatLng((double)rd["Lat"], (double)rd["Lng"]);
log.PositionDilutionOfPrecision = rd["PositionDilutionOfPrecision"] as double?;
log.HorizontalDilutionOfPrecision = rd["HorizontalDilutionOfPrecision"] as double?;
log.VerticalDilutionOfPrecision = rd["VerticalDilutionOfPrecision"] as double?;
log.FixQuality = (FixQuality)((byte)rd["FixQuality"]);
log.FixType = (FixType)((byte)rd["FixType"]);
log.FixSelection = (FixSelection)((byte)rd["FixSelection"]);
}
if (log.SessionCounter - lastSessionCounter != 1 && points.Count > 0)
{
List <GpsLog> ret = new List <GpsLog>(points);
points.Clear();
{
yield return(ret);
}
}
points.Add(log);
lastSessionCounter = log.SessionCounter;
}
if (points.Count > 0)
{
List <GpsLog> ret = new List <GpsLog>(points);
points.Clear();
{
yield return(ret);
}
}
points.Clear();
points = null;
rd.Close();
}
}
}
cn.Close();
}
#else
return(null);
#endif
}
public static void Main(string[] args)
{
var options = new Options();
CommandLine.ICommandLineParser cmdParser =
new CommandLine.CommandLineParser(new CommandLine.CommandLineParserSettings(System.Console.Error));
if (cmdParser.ParseArguments(args, options)) {
string connectionString = string.Format("URI=file:{0}", options.Database);
#if (NET)
var connection = new System.Data.SQLite.SQLiteConnection(connectionString);
#else
var connection = new Mono.Data.Sqlite.SqliteConnection(connectionString);
#endif
connection.Open();
var command = connection.CreateCommand();
command.CommandText =
"CREATE TABLE IF NOT EXISTS at (id INTEGER PRIMARY KEY NOT NULL,name VARCHAR,surname VARCHAR,year INTEGER,gender CHAR,time VARCHAR)";
command.ExecuteNonQuery();
var repo = new AthleteRepository(command);
switch (options.Action) {
case Action.Module: {
// 10mm d=> 28pt
// 15mm => 42pt
//float marginLeft, float marginRight, float marginTop, float marginBottom
var document = new iTextSharp.text.Document(iTextSharp.text.PageSize.A4, 10, 10, 36, 36);
iTextSharp.text.pdf.PdfWriter.GetInstance(document,
new System.IO.FileStream("./module.pdf", System.IO.FileMode.Create));
document.Open();
var builder = new ModuleBuilder(document, options.YearEdition, 10);
for (int page = 0; page < 10; page++) {
builder.AddPage();
}
document.Close();
break;
}
case Action.Insert: {
System.Console.WriteLine("Drop all results?[y/N]?");
string yes = System.Console.ReadLine();
if (yes == "y") {
FileHelpers.FileHelperEngine<Athlete> engine = new FileHelpers.FileHelperEngine<Athlete>();
Athlete[] athletes = engine.ReadFile(options.Input);
repo.DeleteAll();
foreach (var a in athletes) {
System.Console.WriteLine(a.Name);
repo.Insert(a);
}
}
break;
}
case Action.CreateList:
case Action.CreateResult: {
string catFileName = GetCatFileName(options);
string reportFileName = GetReportFileName(options);
var document = new iTextSharp.text.Document(iTextSharp.text.PageSize.A4, 10, 10, 36, 36);
iTextSharp.text.pdf.PdfWriter.GetInstance(document,
new System.IO.FileStream(reportFileName, System.IO.FileMode.Create));
document.Open();
IBuilder builder = null;
if (options.Action == Action.CreateList) {
builder = new ListBuilder(document);
} else {
builder = new PdfBuilder(document);
}
Category[] cats = GetCategories(catFileName);
foreach (Category cat in cats) {
if (log.IsDebugEnabled) log.Debug("parse" + cat.Id);
builder.BeginReport(cat.Title, options.YearEdition);
var athletes = repo.Query(string.Format (cat.Sql, options.YearEdition));
foreach (Athlete athlete in athletes) {
builder.Add(athlete);
}
builder.EndReport();
}
document.Close();
break;
}
case Action.Interactive: {
Category[] cats = GetCategories(GetCatFileName(options));
var parser = new TimeParser();
foreach (Category cat in cats) {
System.Console.WriteLine("========{0}=========", cat.Id);
var athletes = repo.Query(string.Format (cat.Sql, options.YearEdition));
foreach (Athlete athlete in athletes) {
System.Console.Write("{0:00} {1}\t{2}({3}):", athlete.Id, athlete.Surname, athlete.Name, athlete.Gender);
string time = string.Empty;
string fmt = string.Empty;
do {
time = System.Console.ReadLine();
fmt = parser.Parse(time);
if (!string.IsNullOrEmpty(fmt) ) {
System.Console.WriteLine(fmt);
repo.UpdateTime(athlete.Id, fmt);
} else {
if (time != "s") {
System.Console.WriteLine("invalid..");
}
}
} while (string.IsNullOrEmpty(fmt) && time != "s");
}
}
break;
}
}
connection.Close();
}
}
