private static IDataFunctions GetDataFunction(string databaseName) { try { var _file = SharedFunctions.GetDatabaseFile(); var _ret = new DataFunctionsSQLite() { DatabaseFile = _file, Name = databaseName }; return(_ret); } catch (Exception ex) { throw new Exception(ex.Message, ex); } }
private static IDataFunctions GetDataFunction(string databaseName) { try { var _file = SharedFunctions.GetDatabaseFile(); if (!File.Exists(_file)) { SQLiteConnection.CreateFile(_file); } var _ret = new DataFunctionsSQLite() { DatabaseFile = _file, Name = databaseName }; return(_ret); } catch (Exception ex) { throw new Exception(ex.Message, ex); } }
private async Task Init() { //setting the database // setting the database path var _dataBasePath = PortablePath.Combine(m_Root.Path, "Databases"); //var _dataBasePath = "Databases"; if (await m_Root.CheckExistsAsync(_dataBasePath) == ExistenceCheckResult.NotFound) { var _temp = await m_Root.CreateFolderAsync(_dataBasePath, CreationCollisionOption.OpenIfExists); } // setting the db file //var _file = Path.Combine(_dataBasePath, "database.db3"); var _file = "database.db3"; if (await m_Root.CheckExistsAsync(_file) == ExistenceCheckResult.NotFound) { var _retfile = await m_Root.CreateFileAsync(_file, CreationCollisionOption.ReplaceExisting); } // create the database connection function var _dbFuncName = "MyDataFunctionName"; _file = PortablePath.Combine(m_Root.Path, _file); var _function = new DataFunctionsSQLite(_file) { Name = _dbFuncName }; DatabaseObjectShared.DataFunctions.Clear(); DatabaseObjectShared.DataFunctions.Add(_dbFuncName, _function); DatabaseObjectShared.DefaultDataFunctionName = _dbFuncName; //creating table User VerifyObject <User>(_function, _dataBasePath); VerifyObject <Purchase>(_function, _dataBasePath); VerifyObject <PurchaseItem>(_function, _dataBasePath); // creating the screen that will do the examples // insert a record var _add = new Button { Text = "Add new User Record" }; _add.Clicked += async(sender, args) => { // adding a new record on User table // getting total number of rows on table var _count = _function.ExecuteScalar <long>("select count(0) from User") + 1; // create a new instance of User class var _user = new User { Id = (int)_count, Name = string.Format("User #{0}", _count), Password = "******", UserType = _count == 1 ? UserType.Administrator : UserType.Simple }; // save data on db await _user.SaveChangesAsync(); }; // open up a list of User`s var _list = new Button { Text = "Open User List" }; _list.Clicked += async(sender, args) => { await App.Current.MainPage.Navigation.PushModalAsync(new UserList()); }; // delete a row var _delete = new Button { Text = "Delete last row on table" }; _delete.Clicked += async(sender, args) => { // list all rows and get last User row var _users = await User.QueryAsync(); //--> null predicates return all rows from table var _user = _users.LastOrDefault(); if (_user != null) { await _user.DeleteAsync(); } }; // delete all admin Users var _deleteOnlyAdm = new Button { Text = "Delete all Admin Users" }; _deleteOnlyAdm.Clicked += async(sender, args) => { await User.DeleteAllAsync(u => u.UserType == UserType.Administrator); }; // delete all simple Users var _deleteOnlySimple = new Button { Text = "Delete all Simple Users" }; _deleteOnlySimple.Clicked += async(sender, args) => { await User.DeleteAllAsync(u => u.UserType == UserType.Simple); }; // delete all rows var _deleteAll = new Button { Text = "Delete all rows on table" }; _deleteAll.Clicked += async(sender, args) => { // for all rows parameters must be null await User.DeleteAllAsync(); }; // search for Nelson var _searchFor = new Button { Text = "Search for Nelson..." }; _searchFor.Clicked += async(sender, args) => { // for all rows parameters must be null foreach (var _item in await User.QueryAsync(u => u.Name.Contains("nelson"))) { Debug.WriteLine("User Name: {0}", _item.Name); } }; var _jsonData = "{ Id : 9999, Name : \"Nelson Santos\", Password : \"123\", UserType : 1 }"; // UserType -> 0 = Simple / 1 = Administrator var _loadFromJson = new Button { Text = "Load from Json data\r\r" + _jsonData }; _loadFromJson.Clicked += async(sender, args) => { var _user = await User.FromJsonAsync(_jsonData); _user.SaveChanges(); }; for (var _id = 1; _id < 3; _id++) { var _order = new Purchase { PurchaseId = _id, PurchaseDate = DateTime.Parse("2016-05-25") }; var _saved = await _order.SaveChangesAsync(); var _amount = 1; for (int _item = 1; _item < (_id == 1 ? 4 : 3); _item++) { var _orderitem = new PurchaseItem { PurchaseId = _id, ItemId = _item, Amount = _amount, Price = 1.0 }; await _orderitem.SaveChangesAsync(); _amount++; } } var _orders = await Purchase.QueryAsync(); // get all rows on table foreach (var _order in _orders) { Debug.WriteLine(string.Format("Purchase: {0} - PurchaseDate: {1} - Total: {2}", _order.PurchaseId, _order.PurchaseDate, _order.TotalPurchase)); foreach (var _item in _order.OrderItems) { Debug.WriteLine("Item: {0} - Amount: {1} - Price: {2} - Total: {3}", _item.ItemId, _item.Amount, _item.Price, _item.TotalItem); } } // adding the button on screen m_MainStack.Children.Add(_add); m_MainStack.Children.Add(_list); m_MainStack.Children.Add(_delete); m_MainStack.Children.Add(_deleteOnlyAdm); m_MainStack.Children.Add(_deleteOnlySimple); m_MainStack.Children.Add(_deleteAll); m_MainStack.Children.Add(_searchFor); m_MainStack.Children.Add(_loadFromJson); }