C#学习十之windows应用和windows phone应用中的SQLite操作

Windows应用和windows phone应用可以用博客：http://www.cnblogs.com/clownice/p/4518639.html中WPF的方法使用SQLite数据库

但是，还有另一种方法是使用SQLite：

首先，在VS中的工具栏中有一项拓展和更新，进入后联机搜索拓展包

根据开发的应用选用不同的拓展包，安装即可。

然后，添加引用来引用这个拓展的应用，之后，需要调整运行架构为X64或X86否则会报错（在配置管理器中调整）。

之后，右击项目名称进入管理Gu-net包，在里面加载两个类SQLite.cs和SQLiteAsync.cs

到这里，配置就完成了，然后是这两个类中使用数据库的一些操作：

 class DatabaseHelperClass
    {
        SQLiteConnection dbConn;

        //create Table
        public async Task<bool> OnCreate(string DB_PATH)
        {
            try
            {
                if (!CheckFileExists(DB_PATH).Result)
                {
                    using (dbConn = new SQLiteConnection(DB_PATH))
                    {
                        dbConn.CreateTable<Information>();
                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        private async Task<bool> CheckFileExists(string fileName)
        {
            try
            {
                var store = await Windows.Storage.ApplicationData.Current.LocalFolder.GetFileAsync(fileName);
                return true;
            }
            catch
            {
                return false;
            }
        }

        //Retrieve the specific information from the database
        public Information readRecord(int RecordId)
        {
            using (var dbConn = new SQLiteConnection(App.DB_PATH))
            {
                var existingconact = dbConn.Query<Information>("select * from Information where Id =" + RecordId).FirstOrDefault();
                return existingconact;
            }
        }

        // Retrieve the all contact list from the database. 
        public ObservableCollection<Information> ReadRecord()
        {
            using (var dbConn = new SQLiteConnection(App.DB_PATH))
            {
                List<Information> myCollection = dbConn.Table<Information>().ToList<Information>();
                ObservableCollection<Information> RecordList = new ObservableCollection<Information>(myCollection);
                return RecordList;
            }
        }

        //Update existing conatct 
        public void UpdateRecord(Information record)
        {
            using (var dbConn = new SQLiteConnection(App.DB_PATH))
            {
                var existingconact = dbConn.Query<Information>("select * from Information where Id =" + record.Id).FirstOrDefault();
                if (existingconact != null)
                {
                    existingconact.Website = record.Website;
                    existingconact.userName = record.userName;
                    existingconact.password = record.password;
                    existingconact.CreationDate = record.CreationDate;
                    dbConn.RunInTransaction(() =>
                    {
                        dbConn.Update(existingconact);
                    });
                }
            }
        }

        // Insert the new contact in the Contacts table. 
        public void Insert(Information newrecord)
        {
            using (var dbConn = new SQLiteConnection(App.DB_PATH))
            {
                dbConn.RunInTransaction(() =>
                {
                    dbConn.Insert(newrecord);
                });
            }
        }

        //Delete specific contact 
        public void DeleteRecord(int Id)
        {
            using (var dbConn = new SQLiteConnection(App.DB_PATH))
            {
                var existingconact = dbConn.Query<Information>("select * from Information where Id =" + Id).FirstOrDefault();
                if (existingconact != null)
                {
                    dbConn.RunInTransaction(() =>
                    {
                        dbConn.Delete(existingconact);
                    });
                }
            }
        }
        //Delete all contactlist or delete Contacts table 
        public void DeleteAllRecord()
        {
            using (var dbConn = new SQLiteConnection(App.DB_PATH))
            {
                //dbConn.RunInTransaction(() => 
                //   { 
                dbConn.DropTable<Information>();
                dbConn.CreateTable<Information>();
                dbConn.Dispose();
                dbConn.Close();
                //}); 
            }
        }