public async void AddPremiumPlayerAsync(string ip, Guid guid) { await Connection.OpenAsync(); // Check if the player already has an entry SQLiteCommand command = new SQLiteCommand("SELECT COUNT(*) FROM premium WHERE GUID = $guid"); command.Parameters.AddWithValue("$guid", guid.ToString("N")); long count = (long)await ExecuteScalarAsync(command); if (count == 1) { command = new SQLiteCommand("UPDATE premium SET ip = $ip, timestamp = datetime('now') WHERE GUID = $guid"); command.Parameters.AddWithValue("$guid", guid.ToString("N")); command.Parameters.AddWithValue("$ip", ip); command.Connection = Connection; await command.ExecuteNonQueryAsync(); } else { command = new SQLiteCommand("INSERT INTO premium(IP, GUID) VALUES ($ip, $guid)"); command.Parameters.AddWithValue("$guid", guid.ToString("N")); command.Parameters.AddWithValue("$ip", ip); command.Connection = Connection; await command.ExecuteNonQueryAsync(); } Connection.Close(); }
public async Task CreateTables(SQLiteConnection connection) { using ( var command = new SQLiteCommand( "CREATE TABLE `Playlists` (Name VARCHAR(255), Guid VARCHAR(36) NOT NULL, PRIMARY KEY (Guid))", connection)) await command.ExecuteNonQueryAsync(); using ( var command = new SQLiteCommand( "CREATE TABLE `PlaylistTracks` (PlaylistId VARCHAR(36), TrackId VARCHAR(36))", connection)) await command.ExecuteNonQueryAsync(); using ( var command = new SQLiteCommand( "INSERT INTO `Playlists` (Name, Guid) VALUES (@name, @guid)", connection)) { command.Parameters.AddWithValue("@name", "Default Playlist"); command.Parameters.AddGuid("@guid", Guid.NewGuid()); await command.ExecuteNonQueryAsync(); } }
public Task CreateTables(SQLiteConnection connection) { using ( var command = new SQLiteCommand("CREATE TABLE `Images` (Type INT, Guid VARCHAR(36) NOT NULL, Data VARCHAR(400), PRIMARY KEY (Guid))", connection)) return command.ExecuteNonQueryAsync(); }
public async Task ExecuteQueryAsync(string queryString) { _con.Open(); _sqlCmd = _con.CreateCommand(); _sqlCmd.CommandText = queryString; await _sqlCmd.ExecuteNonQueryAsync(); //sql_con.Dispose(); _con.Close(); }
public Task CreateTables(SQLiteConnection connection) { using ( var command = new SQLiteCommand( "CREATE TABLE `Artists` (Name VARCHAR(255), MusicBrainzId VARCHAR(36), Url VARCHAR(255), Guid VARCHAR(36) NOT NULL, SmallImage VARCHAR(36), MediumImage VARCHAR(36), LargeImage VARCHAR(36), PRIMARY KEY (Guid))", connection)) return command.ExecuteNonQueryAsync(); }
public Task CreateTables(SQLiteConnection connection) { using ( var command = new SQLiteCommand( "CREATE TABLE `Albums` (Name VARCHAR(255), Guid VARCHAR(36) NOT NULL, Artists VARCHAR(8192),PRIMARY KEY (Guid))", connection)) return command.ExecuteNonQueryAsync(); }
public Task CreateTables(SQLiteConnection connection) { using ( var command = new SQLiteCommand( "CREATE TABLE `Tracks` (Title VARCHAR(255) NOT NULL, ArtistGuid VARCHAR(36), AlbumGuid VARCHAR(36), Guid VARCHAR(36) NOT NULL, LastTimePlayed DATETIME, MusicBrainzId VARCHAR(36), Duration VARCHAR(25), Cover VARCHAR(36), XmlData VARCHAR(1024), PRIMARY KEY (Guid))", connection)) return command.ExecuteNonQueryAsync(); }
public async void SetComplete(Todo todo) { using (var conn = new SQLiteConnection("Data Source=TodoList.s3db")) { using (var cmd = new SQLiteCommand("UPDATE TODOs SET Done = 1 WHERE Id = " + todo.Id, conn)) { await conn.OpenAsync(); await cmd.ExecuteNonQueryAsync(); } } }
public async Task<int> DeleteDataAsync(InventurItem item) { //using (var db = new InventurContext()) //{ // db.InventurItems.Attach(item); // db.InventurItems.Remove(item); // return await db.SaveChangesAsync(); //} var command = new SQLiteCommand($"delete from {TABNAME} where ID={item.ID}", _dbTool.ConnectDb()); return await command.ExecuteNonQueryAsync(); }
public Task AddAlbum(Album album) { Collection.Add(album.Guid, album); using ( var command = new SQLiteCommand( "INSERT INTO `Albums` (Name, Guid, Artists) VALUES (@name, @guid, @artists)", _connection)) { command.Parameters.AddWithValue("@name", album.Name); command.Parameters.AddGuid("@guid", album.Guid); command.Parameters.AddWithValue("@artists", string.Join(",", album.Artists.Select(x => x.Guid.ToString("D")))); return command.ExecuteNonQueryAsync(); } }
public async void ConvertToSqlite(string pathToExcelFile) { SetPathToParentDirectoryOfDatabaseFile(); if (File.Exists(PathToDatabaseArchiveFile) && !File.Exists(_pathToDatabaseFile)) ZipFile.ExtractToDirectory(PathToDatabaseArchiveFile, _pathToDatabase); using ( var dbSqLiteConnection = new SQLiteConnection((WebConfigurationManager.ConnectionStrings["SQLite"].ConnectionString))) { //load data from xlsx(excel) file var ds = await SetDataSet(pathToExcelFile); await dbSqLiteConnection.OpenAsync(); //Set data from rows for (var i = 0; i < ds.Tables[0].Rows.Count; i++) { var rowsStringBuilder = new StringBuilder(); //load data from row to string for (var j = 0; j < ds.Tables[0].Rows[i].ItemArray.Length; j++) { var row = string.IsNullOrEmpty(ds.Tables[0].Rows[i][j].ToString()) ? "NULL" : ds.Tables[0].Rows[i][j].ToString(); if (j < ds.Tables[0].Rows[i].ItemArray.Length - 1) rowsStringBuilder.Append(row + ","); else rowsStringBuilder.Append(row); } //Insert data into table var sqlQuery = "Insert into " + TableName + "(" + ColumnNames + ") Values(" + rowsStringBuilder + ");"; using (var cmd = new SQLiteCommand(sqlQuery, dbSqLiteConnection)) await cmd.ExecuteNonQueryAsync(); } dbSqLiteConnection.Shutdown(); dbSqLiteConnection.Close(); } if (File.Exists(PathToDatabaseArchiveFile)) File.Delete(PathToDatabaseArchiveFile); ZipFile.CreateFromDirectory(_pathToDatabase, PathToDatabaseArchiveFile); }
public async void AddTodo(Todo todo) { using (var conn = new SQLiteConnection("Data Source=TodoList.s3db")) { var sb = new StringBuilder(); sb.Append("INSERT INTO TODOs (Task, DueDate, CreateDate, Done) VALUES ('"); sb.Append(todo.Task); sb.Append("', '"); sb.Append(todo.DueDate.Value.ToString("yyyy-MM-dd HH:mm")); sb.Append("', '"); sb.Append(todo.CreateDate.ToString("yyyy-MM-dd HH:mm")); sb.Append("', "); sb.Append(todo.Done ? 1 : 0); sb.Append(");"); Debug.WriteLine("Executing: " + sb.ToString()); using (var cmd = new SQLiteCommand(sb.ToString(), conn)) { await conn.OpenAsync(); await cmd.ExecuteNonQueryAsync(); } } }
public Task AddTrack(PlayableBase track) { track.Guid = Guid.NewGuid(); Collection.Add(track.Guid, track); Tracks.Add(track); _imageProvider.AddImage(track.Cover); using ( var command = new SQLiteCommand( "INSERT INTO `Tracks` (Title, ArtistGuid, AlbumGuid, Guid, LastTimePlayed, MusicBrainzId, Duration, Cover, XmlData) VALUES (@title, @artistGuid, @albumGuid, @guid, @lastTimePlayed, @musicBrainzId, @duration, @cover, @xmlData)", _connection)) { command.Parameters.AddWithValue("@title", track.Title); command.Parameters.AddGuid("@artistGuid", track.Artist.Guid); command.Parameters.AddGuid("@albumGuid", track.Album?.Guid); command.Parameters.AddGuid("@guid", track.Guid); command.Parameters.AddWithValue("@lastTimePlayed", track.LastTimePlayed.ToString("yyyy-MM-dd HH:mm:ss")); command.Parameters.AddWithValue("@musicBrainzId", track.MusicBrainzId); command.Parameters.AddWithValue("@duration", XmlConvert.ToString(track.Duration)); command.Parameters.AddGuid("@cover", track.Cover?.Guid); using (var stringWriter = new StringWriter()) { _serializer.Serialize(stringWriter, track); command.Parameters.AddWithValue("@xmlData", stringWriter.ToString()); } return command.ExecuteNonQueryAsync(); } }
public async Task<bool> SetExportedAsync() { //using (var db = new InventurContext()) //{ // await db.InventurItems.Where(x => x.Exported == false).ForEachAsync<InventurItem>(y => y.Exported = true); // var changes = await db.SaveChangesAsync(); // if (changes > 0) return true; // return false; //} var command = new SQLiteCommand($"update {TABNAME} set Exported=1 where Exported=0", _dbTool.ConnectDb()); var changes = await command.ExecuteNonQueryAsync(); if (changes > 0) return true; return false; }
public Task UpdateLastTimePlayed(PlayableBase track, DateTime newLastTimePlayed) { track.LastTimePlayed = newLastTimePlayed; using (var command = new SQLiteCommand("UPDATE `Tracks` SET LastTimePlayed=@newLastTimePlayed WHERE Guid=@guid", _connection)) { command.Parameters.AddWithValue("@newLastTimePlayed", newLastTimePlayed.ToString("yyyy-MM-dd HH:mm:ss")); command.Parameters.AddGuid("@guid", track.Guid); return command.ExecuteNonQueryAsync(); } }
public Task UpdateAlbumArtists(Album album) { using (var command = new SQLiteCommand("UPDATE `Albums` SET Artists=@artists WHERE Guid=@guid", _connection)) { command.Parameters.AddWithValue("@artists", string.Join(",", album.Artists.Select(x => x.Guid.ToString("D")))); command.Parameters.AddGuid("@guid", album.Guid); return command.ExecuteNonQueryAsync(); } }
/// <summary> /// Edit an existing entry /// </summary> /// <param name="post"></param> /// <returns>a post object</returns> public async Task<Post> Update(Post post) { if (post == null) { return null; } bool result = false; try { using (var conn = new SQLiteConnection(Connectionstring)) { conn.Open(); string query = $"UPDATE {TableName} SET Title = '{post.Title}', Content = '{post.Content}' WHERE Id = {post.ID}"; using (SQLiteCommand cmd = new SQLiteCommand(query, conn)) { await cmd.ExecuteNonQueryAsync(); result = true; } conn.Close(); } } //useless catch, I need to handle it properly, either log or display a meaningfull message to the UI catch (SQLiteException ex) { Console.WriteLine(ex.ToString()); } return result ? post : null; }
public Task RemoveTrack(PlayableBase track) { Collection.Remove(track.Guid); Tracks.Remove(track); using (var command = new SQLiteCommand("DELETE FROM `Tracks` WHERE Guid=@guid", _connection)) { command.Parameters.AddGuid("@guid", track.Guid); return command.ExecuteNonQueryAsync(); } }
public override void saveToDB() { if (imageCacheNew.Count == 0) return; Dictionary<String, Image> tempData; try { tempData = new Dictionary<string, Image>(imageCacheNew); imageCacheNew.Clear(); this.connect(); SQLiteCommand command = new SQLiteCommand(connection); // only store new images foreach (var imageData in tempData) { command.CommandText = String.Format("REPLACE INTO imageData (id, data) VALUES ('{0}', @0);", imageData.Key); SQLiteParameter parameter = new SQLiteParameter("@0", System.Data.DbType.Binary); parameter.Value = imageToByteArray(imageData.Value); command.Parameters.Add(parameter); command.ExecuteNonQueryAsync(); } command.Dispose(); this.close(); tempData.Clear(); } catch (Exception e) { this.writeLog(LogType.Warning, "Fehler beim speichern der Bilder auf der Datenbank", e); } }
public Task AddPlaylist(UserPlaylist playlist) { Playlists.Add(playlist); PlaylistAdded?.Invoke(this, playlist); using ( var command = new SQLiteCommand( "INSERT INTO `Playlists` (Name, Guid) VALUES (@name, @guid)", _connection)) { command.Parameters.AddWithValue("@name", playlist.Name); command.Parameters.AddGuid("@guid", playlist.Id); return command.ExecuteNonQueryAsync(); } }
public async Task RemovePlaylist(UserPlaylist playlist) { Playlists.Remove(playlist); PlaylistRemoved?.Invoke(this, playlist); using ( var command = new SQLiteCommand( "DELETE FROM `Playlists` WHERE Guid=@guid", _connection)) { command.Parameters.AddGuid("@guid", playlist.Id); await command.ExecuteNonQueryAsync(); } using ( var command = new SQLiteCommand( "DELETE FROM `PlaylistTracks` WHERE PlaylistId=@guid", _connection)) { command.Parameters.AddGuid("@guid", playlist.Id); await command.ExecuteNonQueryAsync(); } }
protected void ExecuteDatabaseQuery(String sql, Boolean async = false) { if (IsUnix()) { try { using (SqliteConnection db = new SqliteConnection(ConnectionString)) { db.Open(); using (SqliteCommand cmd = new SqliteCommand(sql, db)) { if (async) { cmd.ExecuteNonQueryAsync(); } else { cmd.ExecuteNonQuery(); } } } } catch (Exception) { // ignored } } else { /*try {*/ using (SQLiteConnection db = new SQLiteConnection(ConnectionString)) { db.Open(); using (SQLiteCommand cmd = new SQLiteCommand(sql, db)) { if (async) { cmd.ExecuteNonQueryAsync(); } else { cmd.ExecuteNonQuery(); } } } /* } catch (Exception) { // ignored }*/ } }
async static void BuildDatabase(string fileName, IEnumerable<Entry> entries) { try { ResourceExtractor.ExtractResourceToFile("awagame.sqlite.dll", "SQLite.Interop.dll"); } catch { Console.WriteLine("[ERROR] Can not write SQLite library."); } try { File.Delete(fileName); } catch (IOException) { } finally { SQLiteConnection.CreateFile(fileName); } SQLiteConnection database = new SQLiteConnection("Data Source=:memory:;Version=3;"); //use a memory database for speed purposes SQLiteConnection disk = new SQLiteConnection("Data Source=" + fileName + ";Version=3;"); database.Open(); using (var sqlCommand = new SQLiteCommand(@"CREATE TABLE IF NOT EXISTS roms( gamename TEXT, romname TEXT, size TEXT, crc TEXT, md5 TEXT, sha1 TEXT PRIMARY KEY, romID TEXT, datName TEXT, datSource TEXT, datDate TEXT )", database)) { sqlCommand.ExecuteNonQuery(); } foreach (Entry gameEntry in entries) { using (var sqlCommand = new SQLiteCommand(@"INSERT OR IGNORE INTO roms VALUES( @gamename, @romname, @size, @crc, @md5, @sha1, @romID, @datName, @datSource, @datDate)", database)) { sqlCommand.Parameters.AddWithValue("@gamename", gameEntry.GameName); sqlCommand.Parameters.AddWithValue("@romname", gameEntry.RomFileName); sqlCommand.Parameters.AddWithValue("@size", gameEntry.RomSize); sqlCommand.Parameters.AddWithValue("@crc", gameEntry.HashCRC32); sqlCommand.Parameters.AddWithValue("@md5", gameEntry.HashMD5); sqlCommand.Parameters.AddWithValue("@sha1", gameEntry.HashSHA1); sqlCommand.Parameters.AddWithValue("@romID", gameEntry.OpenVGDB_RomID); sqlCommand.Parameters.AddWithValue("@datName", gameEntry.DatName); sqlCommand.Parameters.AddWithValue("@datSource", gameEntry.DatSource); sqlCommand.Parameters.AddWithValue("@datDate", gameEntry.DatDate); await sqlCommand.ExecuteNonQueryAsync(); if (Program.Verbose) { await Console.Out.WriteLineAsync(String.Format("[INFO] Added ROM record {0} with game record {1} (SHA {2})", gameEntry.RomFileName, gameEntry.GameName, gameEntry.HashSHA1)); } } } disk.Open(); Console.WriteLine("[INFO] Saving Database to " + fileName); try { database.BackupDatabase(disk, "main", "main", -1, null, 0); } catch { Console.WriteLine("[ERROR] Could not save SQLite file (File in use?)."); } Console.WriteLine("Saved to " + fileName); disk.Close(); database.Close(); }
public Task AddArtist(Artist artist) { ArtistDictionary.Add(artist.Guid, artist); _imageProvider.AddImage(artist.SmallImage); _imageProvider.AddImage(artist.MediumImage); _imageProvider.AddImage(artist.LargeImage); using ( var command = new SQLiteCommand( "INSERT INTO `Artists` (Name, MusicBrainzId, Url, Guid, SmallImage, MediumImage, LargeImage) VALUES (@name, @musicBrainzId, @url, @guid, @smallImage, @mediumImage, @largeImage)", _connection)) { command.Parameters.AddWithValue("@name", artist.Name); command.Parameters.AddWithValue("@musicBrainzId", artist.MusicBrainzId); command.Parameters.AddWithValue("@url", artist.Url); command.Parameters.AddGuid("@guid", artist.Guid); command.Parameters.AddGuid("@smallImage", artist.SmallImage?.Guid); command.Parameters.AddGuid("@mediumImage", artist.MediumImage?.Guid); command.Parameters.AddGuid("@largeImage", artist.LargeImage?.Guid); return command.ExecuteNonQueryAsync(); } }
private Task AddImageRow(ImageProvider image, int id, string data) { Collection.Add(image.Guid, image); using ( var command = new SQLiteCommand( "INSERT INTO `Images` (Type, Guid, Data) VALUES (@type, @guid, @data)", _connection)) { command.Parameters.AddWithValue("@type", id); command.Parameters.AddGuid("@guid", image.Guid); command.Parameters.AddWithValue("@data", data); return command.ExecuteNonQueryAsync(); } }
private async void Playlist_TrackRemoved(object sender, PlayableBase e) { using ( var command = new SQLiteCommand( "DELETE FROM `PlaylistTracks` WHERE PlaylistId=@playlistId AND TrackId=@trackId", _connection) ) { command.Parameters.AddGuid("@playlistId", ((UserPlaylist) sender).Id); command.Parameters.AddGuid("@trackId", e.Guid); await command.ExecuteNonQueryAsync(); } }
private async void Playlist_TrackAdded(object sender, PlayableBase e) { using ( var command = new SQLiteCommand( "INSERT INTO `PlaylistTracks` (PlaylistId, TrackId) VALUES (@playlistId, @trackId)", _connection) ) { command.Parameters.AddGuid("@playlistId", ((UserPlaylist) sender).Id); command.Parameters.AddGuid("@trackId", e.Guid); await command.ExecuteNonQueryAsync(); } }
public async Task<int> SaveDataAsync(InventurItem item, bool isNew = false) { //using (var db = new InventurContext()) //{ // item.ChangedAt = DateTime.Now; // if (isNew) // { // item.CreatedAt = DateTime.Now; // db.InventurItems.Add(item); // } // else // { // db.InventurItems.Attach(item); // var entry = db.Entry(item); // entry.Property(x => x.EANCode).IsModified = true; // entry.Property(x => x.ChangedAt).IsModified = true; // entry.Property(x => x.Amount).IsModified = true; // } // return await db.SaveChangesAsync(); //} SQLiteCommand command; item.ChangedAt = DateTime.Now; if (isNew) { item.CreatedAt = DateTime.Now; command = new SQLiteCommand($"insert into {TABNAME} (CreatedAt, ChangedAt, EANCode, Amout, Exported) values ({item.CreatedAt}, {item.ChangedAt}, {item.EANCode}, {item.Amount}, 0)", _dbTool.ConnectDb()); return await command.ExecuteNonQueryAsync(); } else { command = new SQLiteCommand($"update {TABNAME} set EANCode={item.EANCode}, ChangedAt={item.ChangedAt}, Amount={item.Amount} where ID={item.ID}", _dbTool.ConnectDb()); return await command.ExecuteNonQueryAsync(); } }
public static async Task Init() { string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "qts.db"); string connectionString = new SQLiteConnectionStringBuilder { DataSource = path }.ToString(); bool isExist = File.Exists(path); if (isExist) { Connection = new SQLiteConnection(connectionString); Connection.Open(); } else { SQLiteConnection.CreateFile(path); Connection = new SQLiteConnection(connectionString); Connection.Open(); var cmd = new SQLiteCommand(SQL_SCRIPT, Connection); await cmd.ExecuteNonQueryAsync(); } }