Windows8SQLite数据库操作[Sync]

Windows8SQLite数据库操作[Sync]

(2013-01-22 13:32:03)

转载▼

标签： 杂谈

        private async void CreateAndOperateSQLiteDataBase()

        {

            //数据文件保存的位置

            var dataBasePath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "MSISQLiteDataBase.sqlite");

            //var dataBasePath = Path.Combine(KnownFolders.MusicLibrary.Path, "MSISQLiteDataBase.sqlite");

            //创建或打开数据库

            var dataBase = new SQLite.SQLiteConnection(dataBasePath);

            //var dataBase = new SQLite.SQLiteAsyncConnection(dataBasePath);

            

            //创建表

            dataBase.CreateTable();

            //await dataBase.CreateTableAsync();

            //单条插入语句

            dataBase.Insert(new Person() { FirstName = "Jack", LastName = "Slater" });

            //Person person = new Person(){ FirstName = "Jack", LastName = "Slater" };

            //await dataBase.InsertAsync(person);

            //插入操作.首先声明一个集合,多条插入集合

            ObservableCollection personCollection = new ObservableCollection();

            personCollection.Add(new Person() { FirstName = "Lebron", LastName = "James" });

            personCollection.Add(new Person() { FirstName = "Yao", LastName = "Ming" });

            personCollection.Add(new Person() { FirstName = "Ray", LastName = "Allen" });

            int rows = dataBase.InsertAll(personCollection);

            //int rows = await dataBase.InsertAllAsync(personCollection);

            //更新语句

            //方法1

            SQLiteCommand cmd = dataBase.CreateCommand("update person set FirstName='Jake'where FirstName='Lebron'");

            cmd.ExecuteNonQuery();

            //方法2

            dataBase.Update(new Person() { ID = 2, FirstName = "Yao", LastName = "LiCheng" });

            

            try

            {

                //单行删除操作

                int pKey = 3;

                dataBase.Delete(pKey);

            }

            catch (Exception ex)

            {

                Debug.WriteLine(ex.Message);

            }

            //多行删除

            //dataBase.DeleteAll();

            //查询所有数据绑定到UI

            List

            //List list = await dataBase.QueryAsync("select *  from  Person");

            this.listView.ItemsSource = list;

            //dataBase.Dispose();

            //dataBase.Close();

        }

    class Person

    {

       [SQLite.AutoIncrement, SQLite.PrimaryKey]

        public int ID { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

    }