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(); } } }
int PureImageCache.DeleteOlderThan(DateTime date, int?type) { int affectedRows = 0; try { using (SQLiteConnection cn = new SQLiteConnection()) { cn.ConnectionString = ConnectionString; cn.Open(); { using (DbCommand com = cn.CreateCommand()) { com.CommandText = string.Format("DELETE FROM Tiles WHERE CacheTime is not NULL and CacheTime < datetime('{0}')", date.ToString("s")); if (type.HasValue) { com.CommandText += " and Type = " + type; } affectedRows = com.ExecuteNonQuery(); } } } } catch (Exception ex) { #if MONO Console.WriteLine("DeleteOlderThan: " + ex); #endif Debug.WriteLine("DeleteOlderThan: " + ex); } return(affectedRows); }
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 EntityStore(string databaseFullPath, DestructorType destructorType = DestructorType.None) { _databaseFullPath = databaseFullPath; _destructorType = destructorType; _connectionString = String.Format("Data Source={0}; Version=3; Read Only=False; Pooling=True; Max Pool Size=10", _databaseFullPath); if (!File.Exists(_databaseFullPath)) { SQLiteConnection.CreateFile(_databaseFullPath); Log.Info("Successfully created the EntityStore database file at {0}", databaseFullPath); } else { Log.Info("Successfully confirmed that the EntityStore database exists at {0}", databaseFullPath); } using (var connection = new SQLiteConnection(_connectionString)) { connection.Open(); using (var cmd = connection.CreateCommand()) { Log.Debug("About to create or check for the Entities table in the EntityStore database."); cmd.CommandText = "CREATE TABLE IF NOT EXISTS Entities (entityType TEXT, entityKey TEXT, entityBlob TEXT, entityTag TEXT, lastModified DATETIME, PRIMARY KEY (entityType, entityKey))"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); Log.Info("Successfully created or checked that the Entities table exists in the EntityStore database."); } } }
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 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 EntityStore(string databaseFullPath, DestructorType destructorType = DestructorType.None) { _databaseFullPath = databaseFullPath; _destructorType = destructorType; _connectionString = String.Format("Data Source={0}; Version=3; Read Only=False; Pooling=True; Max Pool Size=10", _databaseFullPath); if (!File.Exists(_databaseFullPath)) { SQLiteConnection.CreateFile(_databaseFullPath); Log.Info("Successfully created the EntityStore database file at {0}", databaseFullPath); } else { Log.Info("Successfully confirmed that the EntityStore database exists at {0}", databaseFullPath); } using (var connection = new SQLiteConnection(_connectionString)) { connection.Open(); using (var cmd = connection.CreateCommand()) { Log.Debug("About to create or check for the Entities table in the EntityStore database."); cmd.CommandText = "CREATE TABLE IF NOT EXISTS Entities (entityType TEXT, entityKey TEXT, entityBlob TEXT, entityTag TEXT, lastModified DATETIME, PRIMARY KEY (entityType, entityKey))"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); Log.Info("Successfully created or checked that the Entities table exists in the EntityStore database."); } } }
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 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 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 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 T Create <T>(T model) where T : class // { // using (var c = new Mono.Data.Sqlite.SqliteConnection(Database.ConnectionString)) { // c.Insert<T>(model); // } // return model; // } public void Create(Curso model) { using (var c = new Mono.Data.Sqlite.SqliteConnection(Database.ConnectionString)) { c.Open(); var m = model.ToDynamic(); m.SetorId = model.Setor.Id; (m as IDictionary<string, object>).Remove("Setor"); c.Insert((object)m, "Curso"); } }
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 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 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 byte[] GetEntity(string entityType, string entityKey, out string entityTag, out DateTime?lastModified) { using (Log.Scope(LogLevel.Debug, "GetEntity")) { using (var connection = new SQLiteConnection(_connectionString)) { connection.Open(); using (var cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT entityTag, lastModified, entityBlob FROM Entities WHERE entityType=? AND entityKey=?"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey }); var reader = cmd.ExecuteReader(CommandBehavior.SingleRow); if (!reader.Read()) { Log.Debug("Could not find Entity with EntityType '{0}' and EntityKey '{1}'", entityType, entityKey); entityTag = null; lastModified = null; return(null); } else { entityTag = null; if (!reader.IsDBNull(0)) { entityTag = reader.GetString(0); } lastModified = null; if (!reader.IsDBNull(1)) { lastModified = reader.GetDateTime(1); } Log.Debug("Found Entity with EntityType '{0}' and EntityKey '{1}'. It had EntityTag '{2}' and was LastModified '{3}'", entityType, entityKey, entityTag, lastModified); if (!reader.IsDBNull(2)) { return(ReadBytes(reader, 2)); } else { return(null); } } } } } }
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 BaseProxy() { lock (_lock) { if (initialized) { return; } if (!File.Exists(dbFilePath)) { if (!IsRunningOnMono()) { System.Data.SQLite.SQLiteConnection.CreateFile(dbFilePath); if (!Directory.Exists("x86")) { Directory.CreateDirectory("x86"); SaveToDisk("NetStash.x86.SQLite.Interop.dll", "x86\\SQLite.Interop.dll"); } if (!Directory.Exists("x64")) { Directory.CreateDirectory("x64"); SaveToDisk("NetStash.x64.SQLite.Interop.dll", "x64\\SQLite.Interop.dll"); } using (System.Data.SQLite.SQLiteConnection cnn = (System.Data.SQLite.SQLiteConnection)GetConnection()) { cnn.Open(); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand("CREATE TABLE \"Log\" ([IdLog] integer, [Message] nvarchar, PRIMARY KEY(IdLog));", cnn); cmd.ExecuteNonQuery(); } } else { Mono.Data.Sqlite.SqliteConnection.CreateFile(dbFilePath); using (Mono.Data.Sqlite.SqliteConnection cnn = (Mono.Data.Sqlite.SqliteConnection)GetConnection()) { cnn.Open(); Mono.Data.Sqlite.SqliteCommand cmd = new Mono.Data.Sqlite.SqliteCommand("CREATE TABLE \"Log\" ([IdLog] integer, [Message] nvarchar, PRIMARY KEY(IdLog));", cnn); cmd.ExecuteNonQuery(); } } } initialized = 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); } }
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 IDictionary <string, byte[]> GetAllEntities(string entityType) { using (Log.Scope(LogLevel.Debug, "GetAllEntities")) { using (var connection = new SQLiteConnection(_connectionString)) { connection.Open(); using (var cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT entityKey, entityBlob FROM Entities WHERE entityType=?"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType }); var reader = cmd.ExecuteReader(CommandBehavior.SingleResult); var dict = new Dictionary <string, byte[]>(8); while (reader.Read()) { string entityKey = null; if (!reader.IsDBNull(0)) { entityKey = reader.GetString(0); } Log.Debug("Found Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey); if (reader.IsDBNull(1)) { continue; } var bytes = ReadBytes(reader, 1); dict.Add(entityKey, bytes); } return(dict); } } } }
/// <summary> /// Initializes a new instance of the <see cref="InMemorySQLiteDatabase"/> class. /// </summary> public InMemorySQLiteDatabase() { var connectionStringBuilder = new SQLiteConnectionStringBuilder { DataSource = ":memory:", Version = 3, DefaultTimeout = 5, #if MONO JournalMode = SQLiteJournalModeEnum.Off, #else JournalMode = SQLiteJournalModeEnum.Memory, #endif UseUTF16Encoding = true }; ConnectionString = connectionStringBuilder.ToString(); connectionManager = new SQLiteConnectionManager(connectionStringBuilder.ConnectionString); sharedConnection = new SQLiteConnection(connectionStringBuilder.ConnectionString); sharedConnection.Open(); sqlRunner = new AdHocSqlRunner(() => sharedConnection.CreateCommand(), null, () => true); }
public DatabaseFactory(IScriptDiscovery scriptDiscovery) { dbFilePath = Path.Combine(Directory.GetCurrentDirectory(), "box9database.sqlite"); database = () => { if (bool.Parse(ConfigurationManager.AppSettings["MonoSqlite"])) { var conn = new Mono.Data.Sqlite.SqliteConnection(string.Format("Data Source={0};", dbFilePath)); conn.Open(); return(conn); } else { var conn = new SQLiteConnection(string.Format("Data Source={0};", dbFilePath)); conn.Open(); return(conn); } }; this.scriptDiscovery = scriptDiscovery; Initialize(); }
/// <summary> /// Initializes a new instance of the <see cref="TemporarySQLiteDatabase"/> class. /// </summary> /// <param name="name">The name.</param> public TemporarySQLiteDatabase(string name) { dataSourcePath = Path.Combine(Environment.CurrentDirectory, name); var connectionStringBuilder = new SQLiteConnectionStringBuilder { DataSource = name, Version = 3, DefaultTimeout = 5, #if MONO JournalMode = SQLiteJournalModeEnum.Off, #else JournalMode = SQLiteJournalModeEnum.Memory, #endif UseUTF16Encoding = true }; sqLiteConnection = new SQLiteConnection(connectionStringBuilder.ConnectionString); sqLiteConnection.Open(); sharedConnection = new SharedConnection(sqLiteConnection); sqlRunner = new AdHocSqlRunner(() => sqLiteConnection.CreateCommand(), null, () => true); }
/// <summary> /// Initializes a new instance of the <see cref="InMemorySQLiteDatabase"/> class. /// </summary> public InMemorySQLiteDatabase() { var connectionStringBuilder = new SQLiteConnectionStringBuilder { DataSource = ":memory:", Version = 3, DefaultTimeout = 5, #if MONO JournalMode = SQLiteJournalModeEnum.Off, #else JournalMode = SQLiteJournalModeEnum.Memory, #endif UseUTF16Encoding = true }; ConnectionString = connectionStringBuilder.ToString(); connectionManager = new SQLiteConnectionManager(connectionStringBuilder.ConnectionString); sharedConnection = new SQLiteConnection(connectionStringBuilder.ConnectionString); sharedConnection.Open(); sqlRunner = new AdHocSqlRunner(() => sharedConnection.CreateCommand(), null, () => true); }
public byte[] GetEntity(string entityType, string entityKey, out string entityTag, out DateTime? lastModified) { using (Log.Scope(LogLevel.Debug, "GetEntity")) { using (var connection = new SQLiteConnection(_connectionString)) { connection.Open(); using (var cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT entityTag, lastModified, entityBlob FROM Entities WHERE entityType=? AND entityKey=?"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey }); var reader = cmd.ExecuteReader(CommandBehavior.SingleRow); if (!reader.Read()) { Log.Debug("Could not find Entity with EntityType '{0}' and EntityKey '{1}'", entityType, entityKey); entityTag = null; lastModified = null; return null; } else { entityTag = null; if (!reader.IsDBNull(0)) entityTag = reader.GetString(0); lastModified = null; if (!reader.IsDBNull(1)) lastModified = reader.GetDateTime(1); Log.Debug("Found Entity with EntityType '{0}' and EntityKey '{1}'. It had EntityTag '{2}' and was LastModified '{3}'", entityType, entityKey, entityTag, lastModified); if (!reader.IsDBNull(2)) return ReadBytes(reader, 2); else return null; } } } } }
/// <summary> /// Initializes a new instance of the <see cref="TemporarySQLiteDatabase"/> class. /// </summary> /// <param name="name">The name.</param> public TemporarySQLiteDatabase(string name) { dataSourcePath = Path.Combine(Environment.CurrentDirectory, name); var connectionStringBuilder = new SQLiteConnectionStringBuilder { DataSource = name, Version = 3, DefaultTimeout = 5, #if MONO JournalMode = SQLiteJournalModeEnum.Off, #else JournalMode = SQLiteJournalModeEnum.Memory, #endif UseUTF16Encoding = true }; sqLiteConnection = new SQLiteConnection(connectionStringBuilder.ConnectionString); sqLiteConnection.Open(); sharedConnection = new SharedConnection(sqLiteConnection); sqlRunner = new AdHocSqlRunner(() => sqLiteConnection.CreateCommand(), null, () => true); }
public static void Setup(bool useLocalFiles) { DataDirectory = new DirectoryInfo(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "Gablarski")); if (!DataDirectory.Exists) { DataDirectory.Create(); } if (useLocalFiles) { KeyFile = new FileInfo("gablarski.key"); DbFile = new FileInfo("gablarski.db"); } else { KeyFile = new FileInfo(Path.Combine(DataDirectory.FullName, "gablarski.key")); DbFile = new FileInfo(Path.Combine(DataDirectory.FullName, "gablarski.db")); } #if !XAMARIN var builder = new SQLiteConnectionStringBuilder(); builder.DataSource = DbFile.FullName; db = new SQLiteConnection(builder.ToString()); #else KeyFile = new FileInfo(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), KeyFile.Name)); DbFile = new FileInfo(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), DbFile.Name)); if (!DbFile.Exists) { SQLiteConnection.CreateFile(DbFile.FullName); } db = new SQLiteConnection("Data Source=" + DbFile.FullName); #endif db.Open(); CreateDbs(); }
public bool DeleteEntity(string entityType, string entityKey) { using (var connection = new SQLiteConnection(_connectionString)) { connection.Open(); using (var cmd = connection.CreateCommand()) { cmd.CommandText = "DELETE FROM Entities WHERE entityType=? AND entityKey=?"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey }); var affectedCount = cmd.ExecuteNonQuery(); Log.Debug("Deleted Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey); return(affectedCount == 1); } } }
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 ExportMapDataToDB(string sourceFile, string destFile) { bool ret = true; try { if(!File.Exists(destFile)) { ret = CreateEmptyDB(destFile); } if(ret) { using(SQLiteConnection cn1 = new SQLiteConnection()) { #if !MONO cn1.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", sourceFile); #else cn1.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", sourceFile); #endif cn1.Open(); if(cn1.State == System.Data.ConnectionState.Open) { using(SQLiteConnection cn2 = new SQLiteConnection()) { #if !MONO cn2.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", destFile); #else cn2.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", destFile); #endif cn2.Open(); if(cn2.State == System.Data.ConnectionState.Open) { using(SQLiteCommand cmd = new SQLiteCommand(string.Format("ATTACH DATABASE \"{0}\" AS Source", sourceFile), cn2)) { cmd.ExecuteNonQuery(); } using(SQLiteTransaction tr = cn2.BeginTransaction()) { try { List<long> add = new List<long>(); using(SQLiteCommand cmd = new SQLiteCommand("SELECT id, X, Y, Zoom, Type FROM Tiles;", cn1)) { using(SQLiteDataReader rd = cmd.ExecuteReader()) { while(rd.Read()) { long id = rd.GetInt64(0); using(SQLiteCommand cmd2 = new SQLiteCommand(string.Format("SELECT id FROM Tiles WHERE X={0} AND Y={1} AND Zoom={2} AND Type={3};", rd.GetInt32(1), rd.GetInt32(2), rd.GetInt32(3), rd.GetInt32(4)), cn2)) { using(SQLiteDataReader rd2 = cmd2.ExecuteReader()) { if(!rd2.Read()) { add.Add(id); } } } } } } foreach(long id in add) { using(SQLiteCommand cmd = new SQLiteCommand(string.Format("INSERT INTO Tiles(X, Y, Zoom, Type, CacheTime) SELECT X, Y, Zoom, Type, CacheTime FROM Source.Tiles WHERE id={0}; INSERT INTO TilesData(id, Tile) Values((SELECT last_insert_rowid()), (SELECT Tile FROM Source.TilesData WHERE id={0}));", id), cn2)) { cmd.Transaction = tr; cmd.ExecuteNonQuery(); } } add.Clear(); tr.Commit(); } catch(Exception exx) { Debug.WriteLine("ExportMapDataToDB: " + exx.ToString()); tr.Rollback(); ret = false; } } using(SQLiteCommand cmd = new SQLiteCommand("DETACH DATABASE Source;", cn2)) { cmd.ExecuteNonQuery(); } } } } } } } catch(Exception ex) { Debug.WriteLine("ExportMapDataToDB: " + 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(); }
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 static bool ExportMapDataToDB(string sourceFile, string destFile) { bool ret = true; try { if (!File.Exists(destFile)) { ret = CreateEmptyDB(destFile); } if (ret) { using (SQLiteConnection cn1 = new SQLiteConnection()) { #if !MONO cn1.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", sourceFile); #else cn1.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", sourceFile); #endif cn1.Open(); if (cn1.State == System.Data.ConnectionState.Open) { using (SQLiteConnection cn2 = new SQLiteConnection()) { #if !MONO cn2.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", destFile); #else cn2.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", destFile); #endif cn2.Open(); if (cn2.State == System.Data.ConnectionState.Open) { using (SQLiteCommand cmd = new SQLiteCommand(string.Format("ATTACH DATABASE \"{0}\" AS Source", sourceFile), cn2)) { cmd.ExecuteNonQuery(); } using (SQLiteTransaction tr = cn2.BeginTransaction()) { try { List <long> add = new List <long>(); using (SQLiteCommand cmd = new SQLiteCommand("SELECT id, X, Y, Zoom, Type FROM Tiles;", cn1)) { using (SQLiteDataReader rd = cmd.ExecuteReader()) { while (rd.Read()) { long id = rd.GetInt64(0); using (SQLiteCommand cmd2 = new SQLiteCommand(string.Format("SELECT id FROM Tiles WHERE X={0} AND Y={1} AND Zoom={2} AND Type={3};", rd.GetInt32(1), rd.GetInt32(2), rd.GetInt32(3), rd.GetInt32(4)), cn2)) { using (SQLiteDataReader rd2 = cmd2.ExecuteReader()) { if (!rd2.Read()) { add.Add(id); } } } } } } foreach (long id in add) { using (SQLiteCommand cmd = new SQLiteCommand(string.Format("INSERT INTO Tiles(X, Y, Zoom, Type, CacheTime) SELECT X, Y, Zoom, Type, CacheTime FROM Source.Tiles WHERE id={0}; INSERT INTO TilesData(id, Tile) Values((SELECT last_insert_rowid()), (SELECT Tile FROM Source.TilesData WHERE id={0}));", id), cn2)) { cmd.Transaction = tr; cmd.ExecuteNonQuery(); } } add.Clear(); tr.Commit(); } catch (Exception exx) { Debug.WriteLine("ExportMapDataToDB: " + exx.ToString()); tr.Rollback(); ret = false; } } using (SQLiteCommand cmd = new SQLiteCommand("DETACH DATABASE Source;", cn2)) { cmd.ExecuteNonQuery(); } } } } } } } catch (Exception ex) { Debug.WriteLine("ExportMapDataToDB: " + ex.ToString()); ret = false; } return(ret); }
public void Open(string absoluteFilePath) { try { ConnectionStringBuilder connstr = new ConnectionStringBuilder(); connstr.DataSource = absoluteFilePath; conn = new Connection(); conn.ConnectionString = connstr.ToString(); conn.Open(); cmdCommand = new Command( "SELECT [Command] FROM [Command] WHERE [Name]=:name AND [Class]=:class", conn); cmdCommand.Parameters.Add(":name", DbType.Binary); cmdCommand.Parameters.Add(":class", DbType.Binary); textCommand = new Command( "SELECT [Content] FROM [Text] WHERE [Name]=:name AND [Language]=:language AND [Class]=:class", conn); textCommand.Parameters.Add(":name", DbType.Binary); textCommand.Parameters.Add(":language", DbType.Binary); textCommand.Parameters.Add(":class", DbType.Binary); troubleCodeCommand = new Command( "SELECT [Content], [Description] FROM [TroubleCode] WHERE [Code]=:code AND [Language]=:language AND [Class]=:class", conn); troubleCodeCommand.Parameters.Add(":code", DbType.Binary); troubleCodeCommand.Parameters.Add(":language", DbType.Binary); troubleCodeCommand.Parameters.Add(":class", DbType.Binary); liveDataCommand = new Command( "SELECT [ShortName], [Content], [Unit], [DefaultValue], [CommandName], [CommandClass], [Description], [Index] FROM [LiveData] WHERE [Language]=:language AND [Class]=:class", conn); liveDataCommand.Parameters.Add(":language", DbType.Binary); liveDataCommand.Parameters.Add(":class", DbType.Binary); } catch (Exception ex) { Close(); throw new DatabaseException( String.Format("Cannot open vehicle database! file path = \"{0}\", error message: {1}", absoluteFilePath, ex.Message)); } }
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(); } }
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 PutEntity(string entityType, string entityKey, byte[] entityBlob, string entityTag = null, DateTime? lastModified = null, bool alwaysOverwrite = true) { using (Log.Scope(LogLevel.Debug, "PutEntity")) { bool worked = false; SQLiteConnection connection; using (connection = new SQLiteConnection(_connectionString)) { connection.Open(); SQLiteCommand cmd; using (cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT COUNT(entityType) FROM Entities WHERE entityType=? AND entityKey=?"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType}); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey }); var someType = cmd.ExecuteScalar(); var foundCount = (long)someType; if (foundCount > 0) { if (alwaysOverwrite) { using (cmd = connection.CreateCommand()) { cmd.CommandText = "UPDATE Entities SET entityBlob=?, entityTag=?, lastModified=? WHERE entityType=? AND entityKey=?"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.Object, ParameterName = "entityBlob", Value = entityBlob }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityTag", Value = entityTag }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.DateTime, ParameterName = "lastModified", Value = lastModified }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey }); if (cmd.ExecuteNonQuery() > 0) { worked = true; Log.Debug("Put (update) Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey); } } } else { using (cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT COUNT(entityType) FROM Entities WHERE entityType=? AND entityKey=? AND entityTag=? AND lastModified=?"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityTag", Value = entityTag }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.DateTime, ParameterName = "lastModified", Value = lastModified }); if ((int)cmd.ExecuteScalar() > 0) { using (cmd = connection.CreateCommand()) { cmd.CommandText = "UPDATE Entities SET entityBlob=?, entityTag=?, lastModified=? WHERE entityType=? AND entityKey=?"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.Object, ParameterName = "entityBlob", Value = entityBlob }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityTag", Value = entityTag }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.DateTime, ParameterName = "lastModified", Value = lastModified }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey }); if (cmd.ExecuteNonQuery() > 0) { worked = true; Log.Debug("Put (update) Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey); } } } } } } else { using (cmd = connection.CreateCommand()) { cmd.CommandText = "INSERT INTO Entities (entityType, entityKey, entityBlob, entityTag, lastModified) VALUES (?, ?, ?, ?, ?)"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.Object, ParameterName = "entityBlob", Value = entityBlob }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityTag", Value = entityTag }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.DateTime, ParameterName = "lastModified", Value = lastModified }); if (cmd.ExecuteNonQuery() > 0) { worked = true; Log.Debug("Put (insert) Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey); } } } } } return worked; } }
public IDictionary<string, byte[]> GetAllEntities(string entityType) { using (Log.Scope(LogLevel.Debug, "GetAllEntities")) { using (var connection = new SQLiteConnection(_connectionString)) { connection.Open(); using (var cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT entityKey, entityBlob FROM Entities WHERE entityType=?"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType }); var reader = cmd.ExecuteReader(CommandBehavior.SingleResult); var dict = new Dictionary<string, byte[]>(8); while(reader.Read()) { string entityKey = null; if (!reader.IsDBNull(0)) entityKey = reader.GetString(0); Log.Debug("Found Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey); if (reader.IsDBNull(1)) continue; var bytes = ReadBytes(reader, 1); dict.Add(entityKey, bytes); } return dict; } } } }
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(); }
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; }
int PureImageCache.DeleteOlderThan(DateTime date, int? type) { int affectedRows = 0; try { using(SQLiteConnection cn = new SQLiteConnection()) { cn.ConnectionString = ConnectionString; cn.Open(); { using(DbCommand com = cn.CreateCommand()) { com.CommandText = string.Format("DELETE FROM Tiles WHERE CacheTime is not NULL and CacheTime < datetime('{0}')", date.ToString("s")); if(type.HasValue) { com.CommandText += " and Type = " + type; } affectedRows = com.ExecuteNonQuery(); } } } } catch(Exception ex) { #if MONO Console.WriteLine("DeleteOlderThan: " + ex); #endif Debug.WriteLine("DeleteOlderThan: " + ex); } return affectedRows; }
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 bool DeleteEntity(string entityType, string entityKey) { using (var connection = new SQLiteConnection(_connectionString)) { connection.Open(); using (var cmd = connection.CreateCommand()) { cmd.CommandText = "DELETE FROM Entities WHERE entityType=? AND entityKey=?"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey }); var affectedCount = cmd.ExecuteNonQuery(); Log.Debug("Deleted Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey); return affectedCount == 1; } } }
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); }
/// <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 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 Run() { _running = true; try { if (!LoadConfig (_config)) { throw new ConfigFileInitializedException (); } ushort bindUdp = (ushort)_config.GetValue<int> (ConfigFields.NetBindUdp); ushort bindTcp = (ushort)_config.GetValue<int> (ConfigFields.NetBindTcp); int gwBindTcp = _config.GetValue<int> (ConfigFields.GwBindTcp); _url = string.Format ("http://127.0.0.1:{0}/", gwBindTcp); using (IDatagramEventSocket dgramSock = UdpSocket.CreateIPv4 ()) using (TcpListener listener = new TcpListener ()) { dgramSock.Bind (new IPEndPoint (IPAddress.Any, bindUdp)); listener.Bind (new IPEndPoint (IPAddress.Any, bindTcp)); listener.ListenStart (); CreateDatabaseConnectionDelegate create_session_db = delegate () { IDbConnection connection = new Mono.Data.Sqlite.SqliteConnection (); connection.ConnectionString = "Data Source=http-session.sqlite,DateTimeFormat=Ticks,Pooling=False"; connection.Open (); return connection; }; using (Interrupters ints = new Interrupters ()) using (Node node = new Node (ints, dgramSock, listener, "database.sqlite", bindUdp, bindTcp)) using (WebApp app = new WebApp (node, ints)) using (SessionMiddleware mid1 = new SessionMiddleware (create_session_db, app)) using (HttpServer.CreateEmbedHttpServer (mid1, null, true, true, _config.GetValue<bool> (ConfigFields.GwBindAny), gwBindTcp, 16)) { InitNodeList initNodeList = new InitNodeList (node.PortOpenChecker); _app = app; _node = node; _startupWaitHandle.Set (); if (Started != null) { try { Started (this, EventArgs.Empty); } catch {} } initNodeList.Load (); app.ExitWaitHandle.WaitOne (); initNodeList.Save (); app.CreateStatisticsXML ().Save ("statistics-" + DateTime.Now.ToString ("yyyyMMddHHmmss") + ".xml"); _waitHandle.Set (); } } } finally { _running = false; _startupWaitHandle.Set (); } }
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; }
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; }
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 void Open() { connection.Open(); //CreateStandardTables(); }
/// <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(); } }