ExecuteNonQueryAsync() public method

public ExecuteNonQueryAsync ( ) : Task
return Task
Example #1
0
        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();
        }
Example #2
0
        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();
            }
        }
Example #3
0
 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();
 }
Example #4
0
 public async Task ExecuteQueryAsync(string queryString)
 {
     _con.Open();
     _sqlCmd = _con.CreateCommand();
     _sqlCmd.CommandText = queryString;
     await _sqlCmd.ExecuteNonQueryAsync();
     //sql_con.Dispose();
     _con.Close();
 }
Example #5
0
 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();
 }
Example #6
0
 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();
 }
Example #7
0
 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();
 }
Example #8
0
 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();
         }
     }
 }
Example #9
0
        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();
        }
Example #10
0
        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);
 }
Example #12
0
        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();
                }
            }
        }
Example #13
0
        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();
            }
        }
Example #14
0
        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;
        }
Example #15
0
        public Task UpdateLastTimePlayed(PlayableBase track, DateTime newLastTimePlayed)
        {
            track.LastTimePlayed = newLastTimePlayed;
            using (var command = new SQLiteCommand("UPDATE `Tracks` SET [email protected] WHERE [email protected]", _connection))
            {
                command.Parameters.AddWithValue("@newLastTimePlayed", newLastTimePlayed.ToString("yyyy-MM-dd HH:mm:ss"));
                command.Parameters.AddGuid("@guid", track.Guid);

                return command.ExecuteNonQueryAsync();
            }
        }
Example #16
0
        public Task UpdateAlbumArtists(Album album)
        {
            using (var command = new SQLiteCommand("UPDATE `Albums` SET [email protected] WHERE [email protected]", _connection))
            {
                command.Parameters.AddWithValue("@artists",
                    string.Join(",", album.Artists.Select(x => x.Guid.ToString("D"))));
                command.Parameters.AddGuid("@guid", album.Guid);

                return command.ExecuteNonQueryAsync();
            }
        }
Example #17
0
        /// <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;
        }
Example #18
0
        public Task RemoveTrack(PlayableBase track)
        {
            Collection.Remove(track.Guid);
            Tracks.Remove(track);

            using (var command = new SQLiteCommand("DELETE FROM `Tracks` WHERE [email protected]", _connection))
            {
                command.Parameters.AddGuid("@guid", track.Guid);
                return command.ExecuteNonQueryAsync();
            }
        }
Example #19
0
        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);
            }
        }
Example #20
0
        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();
            }
        }
Example #21
0
        public async Task RemovePlaylist(UserPlaylist playlist)
        {
            Playlists.Remove(playlist);
            PlaylistRemoved?.Invoke(this, playlist);

            using (
                var command =
                    new SQLiteCommand(
                        "DELETE FROM `Playlists` WHERE [email protected]", _connection))
            {
                command.Parameters.AddGuid("@guid", playlist.Id);

                await command.ExecuteNonQueryAsync();
            }

            using (
                var command =
                    new SQLiteCommand(
                        "DELETE FROM `PlaylistTracks` WHERE [email protected]", _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
         }*/
     }
 }
Example #23
0
        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();

        }
Example #24
0
        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();
            }
        }
Example #25
0
        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();
            }
        }
Example #26
0
        private async void Playlist_TrackRemoved(object sender, PlayableBase e)
        {
            using (
                var command =
                    new SQLiteCommand(
                        "DELETE FROM `PlaylistTracks` WHERE [email protected] AND [email protected]", _connection)
                )
            {
                command.Parameters.AddGuid("@playlistId", ((UserPlaylist) sender).Id);
                command.Parameters.AddGuid("@trackId", e.Guid);

                await command.ExecuteNonQueryAsync();
            }
        }
Example #27
0
        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();
            }
        }
Example #28
0
        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();
            }

        }
Example #29
0
File: DALUtil.cs Project: botvs/QTS
 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();
     }
 }