public void Drop()
 {
     using (var connection = new SQLiteConnection(_dbPath))
     {
         connection.DropTable<Account>();
     }
 }

 //기본 카테고리 생성
 public void BasicSetting()
 {
     try
     {
         //DB와 연결
         SQLiteConnection conn = new SQLiteConnection(dbPath, true);
         //기본 카테고리들 생성
         string[] basicIncomeCategory = { "Salary", "Interest", "Installment Saving", "Allowance" };
         string[] basicExpenseCategory = { "Electronics", "Food", "Internet", "Transport", "Housing" };
         //돌아가면서 Income category insert
         foreach (string tempCategory in basicIncomeCategory)
         {
             IncomeCategoryForm basicCategory = new IncomeCategoryForm
             {
                 categoryName = tempCategory
             };
             conn.Insert(basicCategory);
         }
         //돌아가면서 expense category insert
         foreach (string tempCategory in basicExpenseCategory)
         {
             ExpenseCategoryForm expenseCategory = new ExpenseCategoryForm
             {
                 categoryName = tempCategory
             };
             conn.Insert(expenseCategory);
         }
     }
     catch (Exception ex)
     {
         ex.Message.ToString();
     }
 }

 private async Task<Location> GetLastLocation()
 {
     using (var db = new SQLiteConnection(Database.DatabasePath))
     {
         return db.Table<Location>().OrderByDescending(x => x.Timestamp).FirstOrDefault();
     }
 }

		public static async Task<Exception> CheckAuth(string id, string pass,SQLiteConnection connection)
		{
			pass = base64Encode (pass);
			var httpClient = new HttpClient ();
			Exception  error;
			httpClient.Timeout = TimeSpan.FromSeconds (20);
			string contents;
			Task<string> contentsTask = httpClient.GetStringAsync ("http://www.schoolapi.somee.com/dangnhap/"+id+"/"+pass);

			try
			{
				contents =  await contentsTask;

			}
			catch(Exception e) {
				error =new Exception("Xảy Ra Lỗi Trong Quá Trình Kết Nối Server");
				return error;
			}
			if (contents.Contains ("false")) {
				error=new Exception("Mã Sinh Viên Hoặc Mật Khẩu Không Đúng");
				return error;

			}
			User usr = new User ();
			usr.Password = pass;
			usr.Id = id;
			Task<string> contentNameTask = httpClient.GetStringAsync ("http://www.schoolapi.somee.com/user/" + id);
			contents=await contentNameTask;
			XDocument doc = XDocument.Parse (contents);
			usr.Hoten= doc.Root.Elements().ElementAt(0).Elements().ElementAt(1).Value.ToString();
			int i = AddUser (connection, usr);
			return null;
		}

 public void Initialize()
 {
     using (var db = new SQLiteConnection(DbPath))
     {
         db.CreateTable<TracklistItem>();
     }
 }

 public void InsertPost(Post post)
 {
     using(Connection = new SQLiteConnection(this.DbPath))
     {
         this.Connection.Insert(post);
     }
 }

			public static List<DiemThi> getAll(SQLiteConnection connection)
			{
				list = new List<DiemThi>();
				DataProvider dtb = new DataProvider (connection);
				list = dtb.GetAllDT ();
				return list;
			}

        public bool AddCategoryOffline(CategoryOfflineViewModel newCategoryOffline, string _synced)
        {
            bool result = false;
            try
            {
                using (var db = new SQLite.SQLiteConnection(_dbPath))
                {
                    CategoryOffline objCategoryOffline = new CategoryOffline();

                    objCategoryOffline.categoryId = Convert.ToString(newCategoryOffline.categoryId);
                    objCategoryOffline.organizationId = Convert.ToString(newCategoryOffline.organizationId);
                    objCategoryOffline.categoryCode = Convert.ToString(newCategoryOffline.categoryCode);
                    objCategoryOffline.categoryDescription = newCategoryOffline.categoryDescription;
                    objCategoryOffline.parentCategoryId = newCategoryOffline.parentCategoryId;
                    objCategoryOffline.imageName = newCategoryOffline.imageName;
                    objCategoryOffline.active = newCategoryOffline.active;

                    objCategoryOffline.synced = _synced;  // i.e. Need to synced when online and Update the synced status = "True"

                    db.RunInTransaction(() =>
                    {
                        db.Insert(objCategoryOffline);
                    });
                }

                result = true;
            }//try
            catch (Exception ex)
            {

            }//catch
            return result;
        }

		/// <returns>
		/// Output of test query
		/// </returns>
		public static string DoSomeDataAccess ()
		{
			var output = "";
			output += "\nCreating database, if it doesn't already exist";
			string dbPath = Path.Combine (
				Environment.GetFolderPath (Environment.SpecialFolder.Personal), "ormdemo.db3");

			var db = new SQLiteConnection (dbPath);
			db.CreateTable<Stock> ();

			if (db.Table<Stock> ().Count() == 0) {
				// only insert the data if it doesn't already exist
				var newStock = new Stock ();
				newStock.Symbol = "AAPL";
				db.Insert (newStock); 

				newStock = new Stock ();
				newStock.Symbol = "GOOG";
				db.Insert (newStock); 

				newStock = new Stock ();
				newStock.Symbol = "MSFT";
				db.Insert (newStock);
			}

			output += "\nReading data using Orm";
			var table = db.Table<Stock> ();
			foreach (var s in table) {
				output += "\n" + s.Id + " " + s.Symbol;
			}

			return output;
		}

        public static void ProcessPath(string databasePath, string filePath)
        {
            //TODO ensure directory exists for the database
            using (var db = new SQLiteConnection(databasePath)) {

                DatabaseLookups.CreateTables(db);

                var hdCollection = DriveUtilities.ProcessDriveList(db);

                var start = DateTime.Now;

                List<string> arrHeaders = DriveUtilities.GetFileAttributeList(db);

                var directory = new DirectoryInfo(filePath);

                var driveLetter = directory.FullName.Substring(0, 1);
                //TODO line it up with the size or the serial number since we will have removable drives.
                var drive = hdCollection.FirstOrDefault(letter => letter.DriveLetter.Equals(driveLetter, StringComparison.OrdinalIgnoreCase));

                if (directory.Exists) {
                    ProcessFolder(db, drive, arrHeaders, directory);
                }

                //just in case something blew up and it is not committed.
                if (db.IsInTransaction) {
                    db.Commit();
                }

                db.Close();
            }
        }

 public void Update(Account currentAccount)
 {
     using (var connection = new SQLiteConnection(_dbPath))
     {
         connection.Update(currentAccount);
     }
 }

 public void Initialize()
 {
     using (var connection = new SQLiteConnection(_dbPath))
     {
         connection.CreateTable<Account>();
     }
 }

 public List<Account> GetAccounts()
 {
     using (var connection = new SQLiteConnection(_dbPath))
     {
         return connection.Table<Account>().ToList();
     }
 }

        /// <summary>
        /// Creates the database if it doesn't already exist.
        /// </summary>
        internal static void CreateDatabase()
        {
            try
            {
                if (!File.Exists(IndexLocation))
                {
                    string absolutePath = HostingEnvironment.MapPath(VirtualCachePath);

                    if (absolutePath != null)
                    {
                        DirectoryInfo directoryInfo = new DirectoryInfo(absolutePath);

                        if (!directoryInfo.Exists)
                        {
                            // Create the directory.
                            Directory.CreateDirectory(absolutePath);
                        }
                    }

                    using (SQLiteConnection connection = new SQLiteConnection(IndexLocation))
                    {
                        connection.CreateTable<CachedImage>();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

		public static void SaveLTRemind(SQLiteConnection connection,LTRemindItem item)
		{
			DataProvider dtb = new DataProvider (connection);
			if (dtb.GetLTRemind (item.MaMH, item.NamHoc, item.HocKy) == null) {
				dtb.AddRemindLT (item);
			}
		}

 public static void Create()
 {
     Console.WriteLine ("Creo Database...");
     string dbPath = Path.Combine (Environment.GetFolderPath (Environment.SpecialFolder.Personal), "cust.db");
     var db = new SQLiteConnection (dbPath);
     db.CreateTable<schede> ();
 }

		public static void SaveLHRemind(SQLiteConnection connection,LHRemindItem item)
		{
			DataProvider dtb = new DataProvider (connection);
			if (dtb.GetLHRemind (item.IDLH,item.Date) == null) {
				dtb.AddRemindLH (item);
			}
		}

        public MainModel()
        {
            // This forces the instantiation
            mDictionarySearcher = ServiceContainer.Resolve<SearchModel> ();
            mSuggestionModel = ServiceContainer.Resolve<SuggestionModel> ();
            mPlaySoundModel = ServiceContainer.Resolve<PlaySoundModel> ();

            mDictionarySearcher.SearchCompleted += OnSearchCompleted;

            var documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            mDatabasePath = Path.Combine(documentsPath, "..", "Library", "db_sqlite-net.db");

            bool isFirstLoad = false;

            using (var conn = new SQLite.SQLiteConnection(mDatabasePath))
            {
                // https://github.com/praeclarum/sqlite-net/wiki
                // In general, it will execute an automatic migration
                conn.CreateTable<WordModel>();
                conn.CreateTable<WaveModel>();

                InitialRefresh(conn);
                LoadNextWord(conn);
                RefreshWordsList(conn);

                isFirstLoad = conn.Table<WordModel>().Count() == 0;
            }

            if (isFirstLoad)
            {
                AddWord("Thanks");
                AddWord("For");
                AddWord("Downloading");
            }
        }

        public void Connect()
        {
            sqlConnection = new SQLiteConnection(DB_PATH);

            if (!ApplicationData.Current.LocalSettings.Values.ContainsKey(DB_VERSION_KEY))
            {
                ApplicationData.Current.LocalSettings.Values.Add(DB_VERSION_KEY, 0);
            }

            int currentDatabaseVersion = DebugHelper.CastAndAssert<int>(ApplicationData.Current.LocalSettings.Values[DB_VERSION_KEY]);

            if (currentDatabaseVersion < 1)
            {
                sqlConnection.CreateTable<ArtistTable>();
                sqlConnection.CreateTable<AlbumTable>();
                sqlConnection.CreateTable<SongTable>();
                sqlConnection.CreateTable<PlayQueueEntryTable>();
                sqlConnection.CreateTable<PlaylistTable>();
                sqlConnection.CreateTable<PlaylistEntryTable>();
                sqlConnection.CreateTable<HistoryTable>();
                sqlConnection.CreateTable<MixTable>();
                sqlConnection.CreateTable<MixEntryTable>();
            }

            if (currentDatabaseVersion < DB_VERSION)
            {
                ApplicationData.Current.LocalSettings.Values[DB_VERSION_KEY] = DB_VERSION;
            }
        }

		public static bool IsLogined(SQLiteConnection connection)
		{
			DataProvider dtb = new DataProvider (connection);
			if (dtb.GetMainUser () != null)
				return true;
			return false;
		}