C# SQLiteConnection.CreateCommand方法代码示例

        protected override void OnNavigatedTo(System.Windows.Navigation.NavigationEventArgs e)
        {
            base.OnNavigatedTo(e);

            if (NavigationContext.QueryString.TryGetValue("BookISBN", out ISBN))
            {
                dbPath = Path.Combine(Path.Combine(ApplicationData.Current.LocalFolder.Path, "jadeface.sqlite"));
                dbConn = new SQLiteConnection(dbPath);
                SQLiteCommand command = dbConn.CreateCommand("select * from booklistitem where isbn = '" + ISBN + "'");
                List<BookListItem> books = command.ExecuteQuery<BookListItem>();
                if (books.Count == 1)
                {
                    book = books.First();
                    //BookDetailGrid.DataContext = book;
                }

            }
            else
            {
                MessageBox.Show("详细信息页面加载出错！");
                NavigationService.Navigate(new Uri("/MainPage.xaml", UriKind.Relative));
            }

            bookService = BookService.getInstance();
            showNoteList();
        }

        protected override void OnNavigatedTo(System.Windows.Navigation.NavigationEventArgs e)
        {
            base.OnNavigatedTo(e);

            string ISBN = "";

            if (NavigationContext.QueryString.TryGetValue("BookISBN", out ISBN))
            {
                currentISBN = ISBN;
                dbPath = Path.Combine(Path.Combine(ApplicationData.Current.LocalFolder.Path, "jadeface.sqlite"));
                dbConn = new SQLiteConnection(dbPath);
                SQLiteCommand command = dbConn.CreateCommand("select * from booklistitem where isbn = '" + ISBN + "'");
                List<BookListItem> books = command.ExecuteQuery<BookListItem>();
                if (books.Count == 1)
                {
                    book = books.First();
                    BookInformationGrid.DataContext = book;

                    StartPage.Text = "";
                    EndPage.Text = "";
                    ReadingRecordPanel.DataContext = record;
                }
                Debug.WriteLine("[DEBUG]Navigate to ReadingRecordPage...");
                bookService = BookService.getInstance();
                RefreshReadingRecord();
            }
            else
            {
                MessageBox.Show("读书记录页面加载出错！");
                NavigationService.Navigate(new Uri("/MainPage.xaml", UriKind.Relative));
            }
        }

        protected override void OnNavigatedTo(NavigationEventArgs e)
        {
            bookService = BookService.getInstance();
            this.datePicker.ValueChanged += new EventHandler<DateTimeValueChangedEventArgs>(picker_ValueChanged);
            List<string> pl = new List<string>() { "高", "中", "低" };
            this.prioritylist.ItemsSource = pl;
            this.prioritylist.SelectedItem = pl[1];

            string username = phoneAppServeice.State["username"].ToString();
            dbPath = Path.Combine(Path.Combine(ApplicationData.Current.LocalFolder.Path, "jadeface.sqlite"));
            dbConn = new SQLiteConnection(dbPath);
            SQLiteCommand command = dbConn.CreateCommand("select * from booklistitem where userid = '" + username + "' and status = 1");
            List<BookListItem> books = command.ExecuteQuery<BookListItem>();

            Debug.WriteLine("[DEBUG]The count of book name list : " + books.Count);

            List<string> bookns = new List<string>();
            foreach (BookListItem book in books)
            {
                bookns.Add(book.Title);
            }
            bl = books;
            this.booknamelist.ItemsSource = bookns;
            base.OnNavigatedTo(e);
        }

 private void InsertSingleData(string st)
 {
     var dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, dbname);
     using (var db = new SQLiteConnection(dbPath))
     {
         var command = db.CreateCommand(st);
         command.ExecuteNonQuery();
     }
 }

        /// <summary>
        /// Получаем адрес сервера синхронизации
        /// </summary>
        public string getSyncServer()
        {
            string changes;
            var dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, dbname);
            using (var db = new SQLiteConnection(dbPath))
            {
                var command = db.CreateCommand("SELECT ServerAddr FROM SyncData;");
                changes = command.ExecuteScalar<string>();
            }

            return changes;
        }

        /// <summary>
        /// Получаем время последней синхроинзации
        /// </summary>
        public DateTime getLastSync()
        {
            DateTime lsync;
            var dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, dbname);
            using (var db = new SQLiteConnection(dbPath))
            {
                var command = db.CreateCommand("SELECT LastSync FROM SyncData;");
                lsync = command.ExecuteScalar<DateTime>();
            }

            return lsync;
        }

 /// <summary>
 /// Изменение сервера синхронизации
 /// </summary>
 public void SetServer(string SrvAddr)
 {
     var dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, dbname);
     using (var db = new SQLiteConnection(dbPath))
     {
         var command = db.CreateCommand("UPDATE SyncData SET ServerAddr ='" + SrvAddr + "';");
         command.ExecuteNonQuery();
     }
 }

        /*public async void CreateSyncFile(List<string> str)
        {
            var folder = ApplicationData.Current.LocalFolder;
            var file = await folder.CreateFileAsync("sync.txt", CreationCollisionOption.ReplaceExisting);
            await FileIO.WriteLinesAsync(file, str);
        }

        public async void ReadSyncFile()
        {
            var storageFolder = ApplicationData.Current.LocalFolder;
            var file = await storageFolder.GetFileAsync("sync.txt");
            var text = await FileIO.ReadLinesAsync(file);
            InsertData(text);
        }*/

        /// <summary>
        /// Изменение времени последней синхронизации
        /// <summary>
        public void SetLastSync()
        {
            var dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, dbname);
            using (var db = new SQLiteConnection(dbPath))
            {
                var command = db.CreateCommand("UPDATE SyncData SET LastSync ='" + DateTime.Now + "';");
                command.ExecuteNonQuery();
            }
        }

        public string SaveMeal(IMealViewModel mealSuggestion)
        {
            string result = string.Empty;
            using (var db = new SQLite.SQLiteConnection(App.DBPath))
            {
                string change = string.Empty;
                try
                {
                    var existingMealSuggestion = (db.Table<MealSuggestion>().Where(
                        c => c.Id == mealSuggestion.Id)).SingleOrDefault();

                    if (existingMealSuggestion != null)
                    {
                        existingMealSuggestion.CategoryId = mealSuggestion.CategoryId;
                        existingMealSuggestion.MealItemIDsWithWeight =
                            (byte[])_dictionaryConverterFloat.Convert(mealSuggestion.MealItemIDsWithWeight, null, null, "");

                        int success = db.Update(existingMealSuggestion);
                    }
                    else
                    {
                        int success = db.Insert(new MealSuggestion()
                        {
                            CategoryId = mealSuggestion.CategoryId,
                            MealItemIDsWithWeight =
                                (byte[])_dictionaryConverterFloat.Convert(mealSuggestion.MealItemIDsWithWeight, null, null, "")
                        });

                        SQLiteCommand cmd = db.CreateCommand("SELECT last_insert_rowid()");
                        int rowId = cmd.ExecuteScalar<int>();
                        cmd.CommandText = "SELECT Id FROM MealSuggestion WHERE rowid = " + rowId.ToString();
                        mealSuggestion.Id = cmd.ExecuteScalar<int>();
                    }
                    result = "Success";
                }
                catch
                {
                    result = "This meal suggestion was not saved.";
                }
            }

            return result;
        }

        public string DeleteMFU(int id)
        {
            string result = "1;1";

            var dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, dbname);
            using (var db = new SQLiteConnection(dbPath))
            {
                // Работа с БД
                var command = db.CreateCommand("Select _Account_Id from MoneyFlowUnitTable WHERE _MFU_Id =" + id);
                var _acc_id = command.ExecuteScalar<int>();
                var command1 = db.CreateCommand("Select _Count from MoneyFlowUnitTable WHERE _MFU_Id =" + id);
                var count = command1.ExecuteScalar<double>();
                var command2 = db.CreateCommand("Select _Category from MoneyFlowUnitTable WHERE _MFU_Id =" + id);
                var _cat_id = command2.ExecuteScalar<int>();
                ChangeAccount(_acc_id, -count, _cat_id);
                db.Delete<MoneyFlowUnitTable>(id);
                var _change = new ChangesTable() { _DBString = "DELETE FROM MoneyFlowUnitTable WHERE _MFU_Id = '" + id + "';", _ChangesDatetime = DateTime.Now };
                db.Insert(_change);
            }
            return result;
        }

 // применяем к счету значение из параметра (меняется только значение суммы на счете, запоминаем экшн)
 public string ChangeAccount(int _acc_id, double _sum, int _cat_id)
 {
     string res = "";
     var dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, dbname);
     using (var db = new SQLiteConnection(dbPath))
     {
         // Работа с БД
         var command = db.CreateCommand("SELECT CurrentMoneyAmount from AccountTable WHERE _account_Id =" + _acc_id);
         var current_sum = command.ExecuteScalar<double>();
         var command1 = db.CreateCommand("Select _Name from AccountTable WHERE _account_Id =" + _acc_id);
         var _name = command1.ExecuteScalar<string>();
         var _acc = new AccountTable() { _account_Id = _acc_id, CurrentMoneyAmount = current_sum + _sum, _Name = _name };
         db.Update(_acc);
         var _act = new ActionTable() {AccountID = _acc_id, Money =  _sum, CategoryID = _cat_id};
         db.Insert(_act);
         // Если до этого был просто быдлокод, то теперь будет быдлокод в квадрате!!!
         var _change_acc = new ChangesTable() { _DBString = "UPDATE AccountTable SET _Name='" + _name + "', CurrentMoneyAmount='" + (current_sum + _sum) + "' WHERE _account_ID='" + _acc_id + "';", _ChangesDatetime = DateTime.Now };
         db.Insert(_change_acc);
         var _change_act = new ChangesTable() { _DBString = "INSERT INTO ActionTable(AccountID, Money, CategoryID) VALUES('" + _acc_id + "', '" + _sum + "', '" + _cat_id + "');", _ChangesDatetime = DateTime.Now };
         db.Insert(_change_act);
     }
     return res;
 }

        public string Save(MealSuggestionCategoryViewModel mealSuggestionCategory)
        {
            string result = string.Empty;
            using (var db = new SQLite.SQLiteConnection(App.DBPath))
            {
                string change = string.Empty;
                try
                {
                    var existingMealSuggestion = (db.Table<MealSuggestionCategory>().Where(
                        c => c.Id == mealSuggestionCategory.Id)).SingleOrDefault();

                    if (existingMealSuggestion != null)
                    {
                        existingMealSuggestion.Name = mealSuggestionCategory.Name;

                        int success = db.Update(existingMealSuggestion);
                    }
                    else
                    {
                        int success = db.Insert(new MealSuggestionCategoryViewModel()
                        {
                            Name = mealSuggestionCategory.Name
                        });

                        SQLiteCommand cmd = db.CreateCommand("SELECT last_insert_rowid()");
                        int rowId = cmd.ExecuteScalar<int>();
                        cmd.CommandText = "SELECT Id FROM MealSuggestionCategory WHERE rowid = " + rowId.ToString();
                        mealSuggestionCategory.Id = cmd.ExecuteScalar<int>();
                    }
                    result = "Success";
                }
                catch
                {
                    result = "This meal suggestion category was not saved.";
                }
            }

            return result;
        }

        public FootballPlayerCell()
        {
            connection = new SQLiteConnection (App.Path);

            MenuItem favouriteAction = new MenuItem { Text = "Favourite" };
            favouriteAction.SetBinding (MenuItem.CommandParameterProperty, new Binding("."));
            favouriteAction.Clicked += async (sender, e) => {
                await Task.Run (() => {
                    Player player;
                    var menuItem = ((MenuItem)sender);
                    player = (Player) menuItem.CommandParameter;

                    bool isFavourite = !(player.IsFavourite);

                    SQLiteCommand command = connection.CreateCommand("UPDATE FootballPlayer SET IsFavourite = ? where Name = ?", isFavourite, player.Name);
                    command.ExecuteNonQuery();

                });
                MessagingCenter.Send<FootballPlayerCell> (this, "Favourite");
            };

            ContextActions.Add (favouriteAction);

            MenuItem deleteAction = new MenuItem { Text = "Delete", IsDestructive = true };
            deleteAction.SetBinding (MenuItem.CommandParameterProperty, new Binding ("."));
            deleteAction.Clicked += async (sender, e) => {
                await Task.Run (() => {
                    Player player;
                    var menuItem = ((MenuItem)sender);
                    player = (Player) menuItem.CommandParameter;

                    SQLiteCommand command = connection.CreateCommand("DELETE from FootballPlayer where Name = ?", player.Name);
                    command.ExecuteNonQuery();

                });
                MessagingCenter.Send<FootballPlayerCell> (this, "Delete");
            };

            ContextActions.Add (deleteAction);

            Image PlayerImage = new Image
            {
                WidthRequest = 50,
                HeightRequest = 50
            };
            PlayerImage.SetBinding (Image.SourceProperty, "PlayerImage");

            Image PlayerFlag = new Image
            {
                WidthRequest = 50,
                HeightRequest = 30
            };
            PlayerFlag.SetBinding (Image.SourceProperty, "PlayerFlag");

            Label Name = new Label
            {
                TextColor = Color.Black
            };
            Name.SetBinding (Label.TextProperty, "Name");

            Label DateOfBirth = new Label
            {
                TextColor = Color.Black
            };
            DateOfBirth.SetBinding (Label.TextProperty, "DateOfBirth");

            Label Age = new Label
            {
                TextColor = Color.Black,
            };
            Age.SetBinding (Label.TextProperty, "Age");

            StackLayout descriptionLayout = new StackLayout
            {
                Orientation = StackOrientation.Horizontal,
                Spacing = 10,
                Children =
                {
                    DateOfBirth,
                    Age
                }
            };

            StackLayout detailsLayout = new StackLayout
            {
                Orientation = StackOrientation.Vertical,
                Spacing = 20,
                Children =
                {
                    Name,
                    descriptionLayout
                }
            };

            var cellLayout = new StackLayout
            {
                Spacing = 30,
                Padding = new Thickness (10, 5, 10, 5),
                Orientation = StackOrientation.Horizontal,
                HorizontalOptions = LayoutOptions.FillAndExpand,
//.........这里部分代码省略.........

        void getDatabase()
        {
            FootballPlayersList = new ObservableCollection<Player> ();
            connection = new SQLiteConnection (App.Path);

            SQLiteCommand command = connection.CreateCommand("SELECT * from FootballPlayer ORDER BY IsFavourite DESC , Name");
            var footballPlayersList = command.ExecuteQuery<FootballPlayer> ();

            foreach (FootballPlayer footballPlayer in footballPlayersList)
            {
                TimeSpan timeSpan = (DateTime.Now - DateTime.Parse(footballPlayer.DateOfBirth));
                double years = timeSpan.Days / 365;

                string playerFlag = null;
                if (footballPlayer.Country == "India")
                {
                    playerFlag = "India.png";
                }
                else if (footballPlayer.Country == "China")
                {
                    playerFlag = "China.png";
                }
                else if (footballPlayer.Country == "Japan")
                {
                    playerFlag = "Japan.png";
                }
                else if (footballPlayer.Country == "Korea")
                {
                    playerFlag = "Korea.png";
                }

                Player player = new Player(footballPlayer.Name, footballPlayer.DateOfBirth, years.ToString(), footballPlayer.Country, playerFlag, footballPlayer.PlayerImage, footballPlayer.IsFavourite);
                FootballPlayersList.Add (player);
            }
        }

        public static int GetDirectoryId(SQLiteConnection db, DriveInformation drive, DirectoryInfo directory)
        {
            var directoryPath = directory.ToDirectoryPath();
            directoryPath = (directoryPath ?? string.Empty).Trim();

            var cmd = db.CreateCommand("Select * from " + typeof(DirectoryInformation).Name + " Where DriveId = ? AND Path = ?", drive.DriveId, directoryPath);
            var retVal = cmd.ExecuteQuery<DirectoryInformation>().FirstOrDefault();

            if (retVal != null) {
                return retVal.DirectoryId;
            }

            int? parentDirectoryInfo = null;

            if (directory.Parent != null) {
                var parentName = directory.Parent.FullName.Substring(3);
                parentDirectoryInfo = GetDirectoryId(db, drive, directory.Parent);
            }

            var directoryName = directory.Name;

            if (directoryName.IndexOf(":") > -1) {
                directoryName = directoryName.Substring(3);
            }

            //create a new record
            var newDirectory = new DirectoryInformation() {
                DriveId = drive.DriveId,
                Name = directoryName,
                ParentDirectoryId = parentDirectoryInfo.HasValue ? parentDirectoryInfo : null,
                Path = directoryPath
            };

            db.Insert(newDirectory);

            return newDirectory.DirectoryId;
        }