C# SQLiteCommand.ExecuteNonQueryAsync方法代码示例

        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 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 [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();
            }
        }

        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();
            }
        }

        /// <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;
        }