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); }
protected void CreateStore(Boolean UpdateStore) { if (StoreExists) { if (!UpdateStore) { throw new StoreAlreadyExistsException(); } } else { #if (!WINDOWS_PHONE) SQLiteConnection.CreateFile(FileName); #endif } var connection = GetConnection(true); try { foreach (var entity in this.Entities) { CreateTable(connection, entity); } } finally { DoneWithConnection(connection, true); } }
public static bool UpdateObject <T> (this DbConnection gdb, T obj) { MySqlConnection db = (MySqlConnection)gdb; using (MySqlCommand cmd = db.CreateCommand()) { DbMap map = GetMap(obj.GetType()); StringBuilder sql = new StringBuilder("UPDATE `"); sql.Append(map.Table).Append("` SET "); foreach (var f in map) { if (f.Key == map.IdentityField) { continue; } string fp = "@_" + f.Key; sql.Append(f.Key).Append('=').Append(fp).Append(','); object val = f.Value.GetValue(obj, null); /* if (val is Enum) * val = Convert.ChangeType (val, typeof(int));*/ cmd.Parameters.AddWithValue(fp, val); } sql.Remove(sql.Length - 1, 1); sql.Append(" WHERE "); AppendObjectFilter(map, cmd, obj, sql); cmd.CommandText = sql.ToString(); return(cmd.ExecuteNonQuery() > 0); } }
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 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(); } } }
void createColumn(Mono.Data.Sqlite.SqliteConnection conn, string tableName, string columnName, string type) { var command = conn.CreateCommand(); command.CommandText = "ALTER TABLE " + tableName + " ADD COLUMN " + columnName + " " + type; command.ExecuteNonQuery(); }
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."); } } }
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 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 bool _isDisposing; // To detect redundant calls /// <summary> /// Initializes a new instance of the <see cref="LiteDbContext" /> class. /// </summary> /// <param name="databaseFilePath">The database file path.</param> /// <param name="enabledLog">if set to <c>true</c> [enabled log].</param> protected LiteDbContext(string databaseFilePath, bool enabledLog = true) { EnabledLog = enabledLog; _contextType = GetType(); LoadEntitySets(); databaseFilePath = Path.GetFullPath(databaseFilePath); var databaseExists = File.Exists(databaseFilePath); #if NET461 Connection = new Mono.Data.Sqlite.SqliteConnection($"URI=file:{databaseFilePath}"); #else var builder = new SqliteConnectionStringBuilder { DataSource = databaseFilePath, }; Connection = new SqliteConnection(builder.ToString()); #endif Connection.Open(); if (databaseExists == false) { "DB file does not exist. Creating.".Debug(nameof(LiteDbContext)); CreateDatabase(); $"DB file created: '{databaseFilePath}'".Debug(nameof(LiteDbContext)); } UniqueId = Guid.NewGuid(); }
public void Init() { connection = new SQLiteConnection { ConnectionString = "Data Source=" + "save.db" }; Open(); }
public static DbDataReader ExecuteSelect(this DbConnection gdb, string sql, params object[] args) { MySqlConnection db = (MySqlConnection)gdb; MySqlCommand cmd = db.CreateCommand(); GenerateSqlCommand(cmd, sql, args); return(cmd.ExecuteReader()); }
/// <summary> /// Creates the database. /// </summary> public void Create() { var filePath = new FileInfo(dataSourcePath); if (!filePath.Exists) { SQLiteConnection.CreateFile(dataSourcePath); } }
public void CreateDatabaseFile(string dbFile) { this.DBFile = dbFile; var connection = new Mono.Data.Sqlite.SqliteConnection("Data Source=" + dbFile); BooruApp.BooruApplication.Database.CreateDatabase(connection); BooruApp.BooruApplication.Settings.Set("last_used_db", dbFile); }
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 InsertObject <T> (this DbConnection gdb, T obj) { MySqlConnection db = (MySqlConnection)gdb; using (MySqlCommand cmd = db.CreateCommand()) { DbMap map = GetMap(obj.GetType()); StringBuilder sql = new StringBuilder("INSERT INTO `"); sql.Append(map.Table).Append("` ("); foreach (string f in map.Keys) { if (f != map.IdentityField) { sql.Append(f).Append(','); } } sql[sql.Length - 1] = ')'; sql.Append(" VALUES ("); foreach (var f in map) { if (f.Key == map.IdentityField) { continue; } string fp = "@_" + f.Key; sql.Append(fp).Append(','); cmd.Parameters.AddWithValue(fp, f.Value.GetValue(obj, null)); } sql[sql.Length - 1] = ')'; if (map.IdentityField != null) { sql.Append("; SELECT @@IDENTITY"); } cmd.CommandText = sql.ToString(); if (map.IdentityField == null) { cmd.ExecuteNonQuery(); } else { using (DbDataReader dr = cmd.ExecuteReader()) { if (dr.Read()) { PropertyInfo prop = map[map.IdentityField]; object val = Convert.ChangeType(dr[0], prop.PropertyType); prop.SetValue(obj, val, null); } else { throw new Exception("Insertion failed"); } } } } }
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; }
private void CreateDbIfNotExists() { string path = Path.Combine(this.DBDirectory, this.DBName); if (!File.Exists(path)) { Directory.CreateDirectory(this.DBDirectory); SQLiteConnectionAlias.CreateFile(Path.Combine(this.DBDirectory, this.DBName)); } }
// 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"); } }
/// <summary> /// Creates the database. /// </summary> public void Create() { #if !NETCORE var filePath = new FileInfo(dataSourcePath); if (!filePath.Exists) { SQLiteConnection.CreateFile(dataSourcePath); } #endif }
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 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 newLinkDialog() { this.Build (); this.SetPosition (Gtk.WindowPosition.Center); dbConn = new SQLiteConnection ("Data Source="+approot+"LAdd.db, Version=3"); //TODO make set password / change password dialog / window ... dbConn fillCbFlagWithAllFlagTypes (); fillInputsFromClipboard (); /* retry getting title if value enter in entryLink widget*/ entryLink.Activated += entryLink_activated; entryLink.GrabFocus (); Gtk.Clipboard.Get (Gdk.Selection.Clipboard).OwnerChange += onClipboardOwnerChange; }
public static void DeleteObject <T> (this DbConnection gdb, T obj) { MySqlConnection db = (MySqlConnection)gdb; using (MySqlCommand cmd = db.CreateCommand()) { DbMap map = GetMap(obj.GetType()); StringBuilder sql = new StringBuilder("DELETE FROM `"); sql.Append(map.Table).Append("` WHERE "); AppendObjectFilter(map, cmd, obj, sql); Console.WriteLine(sql); cmd.CommandText = sql.ToString(); cmd.ExecuteNonQuery(); } }
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 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; } }
private bool checkIfColumnExists(Mono.Data.Sqlite.SqliteConnection conn, string tableName, string columnName) { var command = conn.CreateCommand(); command.CommandText = string.Format("PRAGMA table_info({0})", tableName); var reader = command.ExecuteReader(); int nameIndex = reader.GetOrdinal("Name"); while (reader.Read()) { if (reader.GetString(nameIndex).Equals(columnName)) { return(true); } } return(false); }
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); } }
/// <summary> /// Deletes the database. /// </summary> public void Dispose() { var filePath = new FileInfo(dataSourcePath); if (!filePath.Exists) { return; } sharedConnection.Dispose(); sqLiteConnection.Dispose(); SQLiteConnection.ClearAllPools(); // SQLite requires all created sql connection/command objects to be disposed // in order to delete the database file GC.Collect(2, GCCollectionMode.Forced); System.Threading.Thread.Sleep(100); File.Delete(dataSourcePath); }
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); }
/// <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 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; } } } } }
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 SQLiteConnection(string connectionString) { __connection = new Mono.Data.Sqlite.SqliteConnection (connectionString); }
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; }
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 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 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)); } }
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 SQLiteConnection() { __connection = new Mono.Data.Sqlite.SqliteConnection (); }
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; } } }
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 static IDbConnection GetConnection(string provider, string cstring) { IDbConnection cn = null; switch (provider.ToLower()) { case "sql": // can't connect unless information provided; // when user wants to set the connection programmatically this they should do this if (cstring.Length > 0) cn = new SqlConnection(cstring); break; case "odbc": cn = new OdbcConnection(cstring); break; case "oledb": cn = new OleDbConnection(cstring); break; case "filedirectory": cn = new fyiReporting.Data.FileDirConnection(cstring); break; case "xml": cn = new fyiReporting.Data.XmlConnection(cstring); break; case "webservice": cn = new fyiReporting.Data.WebServiceConnection(cstring); break; case "weblog": cn = new fyiReporting.Data.LogConnection(cstring); break; case "text": cn = new fyiReporting.Data.TxtConnection(cstring); break; case "itunes": cn = new fyiReporting.Data.iTunesConnection(cstring); break; #if EnableBundleLinux // See properties -> Build -> Conditional compilation symbols case "sqlite": cn = new Mono.Data.Sqlite.SqliteConnection(cstring); break; case "postgresql": cn = new Npgsql.NpgsqlConnection(cstring); break; #endif default: if (SqlEntries == null){ // if never initialized; we should init RdlEngineConfigInit(); } System.Console.WriteLine("Attempt to find provider"); SqlConfigEntry sce = SqlEntries[provider] as SqlConfigEntry; if (sce == null || sce.CodeModule == null) { if (sce != null && sce.ErrorMsg != null){ // error during initialization?? throw new Exception(sce.ErrorMsg); } break; } System.Console.WriteLine("Provider Create Instance"); object[] args = new object[] { cstring }; Assembly asm = sce.CodeModule; object o = asm.CreateInstance(sce.ClassName, false, BindingFlags.CreateInstance, null, args, null, null); if (o == null) throw new Exception(string.Format(Strings.RdlEngineConfig_Error_UnableCreateInstance, sce.ClassName, provider)); cn = o as IDbConnection; break; } return cn; }
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 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 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 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 SQLiteConnection(SQLiteConnection conn) { __connection = new Mono.Data.Sqlite.SqliteConnection (conn.__connection); }
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; } }