public void InserindoListaFabricantes(List<Fabricante> fabricantes) { using (var dbConn = new SQLiteConnection(DB_path)) { dbConn.DropTable<Fabricante>(); dbConn.Dispose(); dbConn.Close(); } foreach (Fabricante f in fabricantes) this.Inserindo(f); }
public App() { // The root page of your application MainPage = new ContentPage { Content = new StackLayout { VerticalOptions = LayoutOptions.Center, Children = { new Label { XAlign = TextAlignment.Center, Text = "Welcome to Xamarin Forms!" } } } }; // path to db var path = Path.Combine (Environment.GetFolderPath (Environment.SpecialFolder.MyDocuments), "mydb"); // open connection and attempt to apply encryption using PRAGMA statement var conn = new SQLiteConnection (path); conn.Execute ("PRAGMA key = 'passme'"); int v = conn.ExecuteScalar<int> ("SELECT count(*) FROM sqlite_master"); conn.Close (); // open another connection, this time use wrong password. It will still open, but should fail the // query (see docs on key PRAGMA https://www.zetetic.net/sqlcipher/sqlcipher-api/) var conn2 = new SQLiteConnection (path); conn2.Execute ("PRAGMA key = 'wrongpassword'"); int v2 = conn2.ExecuteScalar<int> ("SELECT count(*) FROM sqlite_master"); conn2.Close (); }
public static void addCompletedCircle(Circle circle) { completeCircles.Add(circle); string dbPath = GetDBPath(); SQLiteConnection db; db = new SQLiteConnection(dbPath); db.Insert(circle); var result = db.Query<Line>("SELECT * FROM Lines"); foreach (Line l in result) { Console.WriteLine("Line DB: bx {0}, by {1}, ex {2}, ey {3}", l.begin.X, l.begin.Y, l.end.X, l.end.Y); } var result2 = db.Query<Circle>("SELECT * FROM Circles"); foreach (Circle c in result2) { Console.WriteLine("Circle DB: cx {0}, cy {1}, p2x {2}, p2yy {3}", c.center.X, c.center.Y, c.point2.X, c.point2.Y); } db.Close(); db = null; foreach (Line l in completeLines) { Console.WriteLine("Line CL: bx {0}, by {1}, ex {2}, ey {3}", l.begin.X, l.begin.Y, l.end.X, l.end.Y); } foreach (Circle c in completeCircles) { Console.WriteLine("Circle CC: cx {0}, cy {1}, p2x {2}, p2yy {3}", c.center.X, c.center.Y, c.point2.X, c.point2.Y); } }
// This method checks to see if the database exists, and if it doesn't, it creates // it and inserts some data protected void CheckAndCreateDatabase(string dbName) { // create a connection object. if the database doesn't exist, it will create // a blank database using(SQLiteConnection db = new SQLiteConnection (GetDBPath (dbName))) { // create the tables db.CreateTable<Person> (); // skip inserting data if it already exists if(db.Table<Person>().Count() > 0) return; // declare vars List<Person> people = new List<Person> (); Person person; // create a list of people that we're going to insert person = new Person () { FirstName = "Peter", LastName = "Gabriel" }; people.Add (person); person = new Person () { FirstName = "Thom", LastName = "Yorke" }; people.Add (person); person = new Person () { FirstName = "J", LastName = "Spaceman" }; people.Add (person); person = new Person () { FirstName = "Benjamin", LastName = "Gibbard" }; people.Add (person); // insert our people db.InsertAll (people); // close the connection db.Close (); } }
async void Login_Loaded() { try { await ApplicationData.Current.LocalFolder.CreateFileAsync("Shopping.db3"); var path = Path.Combine(ApplicationData.Current.LocalFolder.Path, "Shopping.db3"); using (var db = new SQLite.SQLiteConnection(path)) { // Create the tables if they don't exist db.CreateTable<ShopLists>(); db.CreateTable<ShopAdmins>(); db.Commit(); db.Dispose(); db.Close(); } ServiceReference1.SoapServiceSoapClient sc=new SoapServiceSoapClient(); sc.AllCouponsCompleted += (a, b) => MessageBox.Show(b.Result); sc.AllCouponsAsync("hacking"); } catch (Exception ex) { FaultHandling(ex.StackTrace); } }
public static List<Drink> getDrinks() { var conn = new SQLiteConnection (System.IO.Path.Combine (documentsFolder(), "database.db")); var results = conn.Query<Drink> ("SELECT * FROM Drink"); conn.Close (); return results; }
public override void ViewWillAppear(bool animated) { base.ViewWillAppear (animated); // This is not double loading becuase it is a new _loader each time and the _newSL is a get set _loader = new List<SongToSave> (); _dbWorker = new DBWorker (); _dbWorker.StartDBWorker (); dbPath = _dbWorker.GetPathToDb (); var conn = new SQLiteConnection (dbPath); // this seems redundant. foreach (var item in conn.Table<SongToSave>()) { var tempSong = new SongToSave () { BookTitle = item.BookTitle, BookAuthor = item.BookAuthor, PlayPosition = item.PlayPosition }; _loader.Add (tempSong); } conn.Close (); // why am I using two list? _newSL = _loader; TableView.ReloadData (); }
public async Task <ObservableCollection <AccountsModel> > GetAllAccountsDB() { ObservableCollection <AccountsModel> _accounts_DB = new ObservableCollection <AccountsModel>(); try { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { var AccountsDB = db.Table <AccountsModel>().Where(a => a.Name.Contains("a")); foreach (AccountsModel accountModel in AccountsDB) { _accounts_DB.Add(accountModel); } db.Commit(); db.Dispose(); db.Close(); //var line = new MessageDialog("Records Inserted"); //await line.ShowAsync(); } } catch (SQLiteException) { } return(_accounts_DB); }
private void Button_Click_4(object sender, RoutedEventArgs e) { // ApplicationBar.IsVisible = true; try { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { list1.Items.Clear(); // var sd = from x in db.Table<person>() select x; //.Distinct<kural>() if (list1.Items == null) { list1.Items.Add("Recent List is Empty!!!Start Search"); } var d = from x in db.Table <person>() select x; foreach (var sd in d) { list1.Items.Add(sd.word); } db.Close(); } } catch { } //search(textbox2.Text.ToString()); }
private async Task AccountStore(ObservableCollection <AccountsModel> account) { try { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { foreach (AccountsModel accountM in Accounts) { var accountInDB = db.Find <AccountsModel>(accountM.Accountid); if (accountInDB != null) { db.Delete(accountM); } db.Insert(accountM); } db.Commit(); db.Dispose(); db.Close(); } } catch (SQLiteException) { } }
private async void deleteselected(object sender, RoutedEventArgs e) { try { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { db.Delete <person>(list1.SelectedItem.ToString()); var d = from x in db.Table <person>() select x; list1.Items.Clear(); foreach (var sd in d) { list1.Items.Add(sd.name.ToString()); //list1.Items.Add(sd.address.ToString()); //list1.Items.Add(sd.phone.ToString()); } db.Dispose(); db.Close(); } var line = new MessageDialog("Selected Item Deleted"); await line.ShowAsync(); } catch { } }
public void criando_populando(string s) { dbConn = new SQLiteConnection(DB_path); SQLiteCommand cmd = new SQLiteCommand(dbConn); cmd.CommandText = s; cmd.ExecuteNonQuery(); dbConn.Close(); }
public void wylaczBaze() { if (conn == null) { return; } conn.Close(); conn = null; }
public static void clearLines() { completeLines.Clear(); string dbPath = GetDBPath(); SQLiteConnection db; db = new SQLiteConnection(dbPath); db.DeleteAll<Line>(); db.Close(); db = null; }
public List<Fabricante> LendoFabricantesLocal() { using(dbConn = new SQLiteConnection(DB_path)) { //dbConn.CreateTable<Fabricante>(); List<Fabricante> fabricantes = dbConn.Table<Fabricante>().ToList(); dbConn.Close(); return fabricantes; } }
public static SyncParams LoadSavedSyncParams() { SyncParams param = null; SQLiteConnection db = new SQLiteConnection(SyncController.DatabasePath); db.CreateTable<SyncParams>(); if (db.Table<SyncParams>().Count() != 0) param = db.Table<SyncParams>().First(); else param = new SyncParams(); db.Close(); return param; }
// method to check for db exsistence, and create and insert if not found protected void CheckAndCreateDatabase(string pathToDatabase) { using (var db = new SQLiteConnection (pathToDatabase)) { db.CreateTable<SongToSave> (); //this doesn't seem to make sense why it is here because I am creating it either way I think ^ if (db.Table<SongToSave> ().Count () > 0) { //Console.WriteLine ("The DB is already at:" + pathToDatabase); // debugging return; } db.Close (); } }
// public static bool saveChanges() // { // // Save to db // string dbPath = GetDBPath(); // var db = new SQLiteConnection(dbPath); // db.DeleteAll<BNRItem>(); // db.BeginTransaction(); // db.InsertAll(allItems); // db.Commit(); // db.Close(); // return true; // Archive method of saving // // returns success or failure // For archiving method of saving // string path = itemArchivePath(); // For archiving method of saving // NSMutableArray newArray = new NSMutableArray(); // For archiving method of saving // foreach (BNRItem item in allItems) { // For archiving method of saving // newArray.Add(item); // For archiving method of saving // } // For archiving method of saving // return NSKeyedArchiver.ArchiveRootObjectToFile(newArray, path); // For archiving method of saving // } public static void addAssetType(string assetType) { string dbPath = GetDBPath(); SQLiteConnection db; if (File.Exists(dbPath)) { db = new SQLiteConnection(dbPath); db.BeginTransaction(); var at = new BNRAssetType(); at.assetType = assetType; allAssetTypes.Add(at); db.Insert(at); db.Commit(); db.Close(); } }
// This method checks to see if the database exists, and if it doesn't, it creates // it and inserts some data public static void CheckAndCreateDatabase () { string path = Path.Combine (Environment.GetFolderPath (Environment.SpecialFolder.Personal), "CourseSchedule.sqlite"); // create a connection object. if the database doesn't exist, it will create // a blank database using (SQLiteConnection db = new SQLiteConnection (path)) { // create the tables db.CreateTable<CourseSchedule> (); // close the connection db.Close (); } }
public static CourseSchedule FindCourseSchedule (decimal classId) { CheckAndCreateDatabase (); string path = Path.Combine (Environment.GetFolderPath (Environment.SpecialFolder.Personal), "CourseSchedule.sqlite"); using (SQLiteConnection db = new SQLiteConnection (path)) { // create the tables var classSchedule = db.Find<CourseSchedule> (c => c.ClassId == classId); // close the connection db.Close (); return classSchedule; } }
public void search(string add) { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "encrypted.db3"); word.Text = add; using (SQLiteConnection db = new SQLiteConnection(dbpath)) { var query = from x in db.Table <embed>() where x.word.ToLower() == add select x; foreach (var x in query) { tiled = x.meaning; } fff.Text = tiled; //fff.Blocks.Clear(); //Run myRun1 = new Run(); //myRun1.Text = x.meaning; //Paragraph myParagraph = new Paragraph(); //myParagraph.Inlines.Add(myRun1); //fff.Blocks.Add(myParagraph); cou = cou + 1; tiles(tiled, cou); var rpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var rdb = new SQLite.SQLiteConnection(rpath)) { // Create the tables if they don't exist var d = from x in rdb.Table <person>() where x.word.ToLower() == add select x; if (d.Count() == 0) { rdb.Insert(new person() { word = add, }); } rdb.Commit(); // rdb.Dispose(); rdb.Close(); } db.Close(); } }
public override Task<IEnumerable<Scrobble>> GetCachedAsync() { using (var db = new SQLiteConnection(DatabasePath, SQLiteOpenFlags.ReadOnly)) { var tableInfo = db.GetTableInfo(typeof (Scrobble).Name); if (!tableInfo.Any()) { return Task.FromResult(Enumerable.Empty<Scrobble>()); } var cached = db.Query<Scrobble>("SELECT * FROM Scrobble"); db.Close(); return Task.FromResult(cached.AsEnumerable()); } }
public void Inserindo(Fabricante f) { try { dbConn = new SQLiteConnection(DB_path); dbConn.CreateTable<Fabricante>(); dbConn.Insert(f); //dbConn.Dispose(); dbConn.Close(); } catch(Exception e) { System.Diagnostics.Debug.WriteLine("##Erro Inserindo## "+e); } }
public void LoadData() { SQLiteConnection sqlite_conn = new SQLiteConnection(dbPath, SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite, true); sqlite_conn.CreateTable<Person>(); //sqlite_conn.Insert(new Person() { FirstName = "Keming", LastName = "Chen" }); List<Person> personList = sqlite_conn.Query<Person>("SELECT * FROM Person"); for (int i = 0; i < personList.Count; i++) { _myListBox.Items.Add(personList[i].FirstName + " " + personList[i].LastName); } sqlite_conn.Close(); }
/// <summary> /// Create Table for accout method /// </summary> /// private async Task <bool> createAccountTable() { try { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { // Create the tables if they don't exist db.CreateTable <AccountsModel>(); db.Commit(); db.Dispose(); db.Close(); } //var line = new MessageDialog("Table Created"); //await line.ShowAsync(); } catch (SQLiteException exLite) { throw new Exception(exLite.Message); } catch (Exception ex) { throw new Exception(ex.Message); } try { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { AccountStore(Accounts); // Create the tables if they don't exist db.Commit(); db.Dispose(); db.Close(); //var line = new MessageDialog("Records Inserted"); //await line.ShowAsync(); } } catch (SQLiteException) { } return(true); }
private async void drop(object sender, RoutedEventArgs e) { try { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { db.DropTable <person>(); db.Dispose(); db.Close(); } var line = new MessageDialog("Table Dropped"); await line.ShowAsync(); } catch { } }
private void AddContact() { // Create temp contact variable to help add info to the database. Classes.Contact tempContact = new Classes.Contact(); // Open the Database var db = new SQLite.SQLiteConnection(TempUser.GetDBPath()); // If Name and Number or Name and Email then set fields in tempContact and add to database if (((Name.Text != "") && (Number.Text != "")) || ((Name.Text != "") && (Email.Text != ""))) { // If name is not empty, then set name variable of temp if (Name.Text != "") { tempContact.SetName(Name.Text); } // If number is not empty, then set phone number variable of temp if (Number.Text != "") { tempContact.SetPhone(Number.Text); } // If email is not empty, then set email variable of temp if (Email.Text != "") { tempContact.SetEmail(Email.Text); } // Reset Fields to Blank Name.Text = ""; Number.Text = ""; Email.Text = ""; // Insert into Database db.Insert(tempContact); } // Close Database db.Close(); MainActivity.UpdateListview(); }
private void createtable() { try { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { // Create the tables if they don't exist db.CreateTable <person>(); db.Commit(); db.Dispose(); db.Close(); } } catch { } }
private void Cache(IEnumerable<Scrobble> scrobbles) { using (var db = new SQLiteConnection(DatabasePath, SQLiteOpenFlags.ReadWrite)) { var tableInfo = db.GetTableInfo(typeof (Scrobble).Name); if (!tableInfo.Any()) { db.CreateTable<Scrobble>(); } db.BeginTransaction(); foreach (var scrobble in scrobbles) { db.Insert(scrobble); } db.Commit(); db.Close(); } }
private async void insert(object sender, RoutedEventArgs e) { try { if (txt1.Text != "" && txt2.Text != "" && txt3.Text != "") { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { // Create the tables if they don't exist db.Insert(new person() { name = txt1.Text.ToString(), address = txt2.Text.ToString(), phone = Convert.ToDouble(txt3.Text.ToString()), } ); db.Commit(); db.Dispose(); db.Close(); var line = new MessageDialog("Records Inserted"); await line.ShowAsync(); } } else { throw new NullReferenceException("Enter The Data In Textboxes"); //var line = new MessageDialog("Enter The Data In Textboxes"); //await line.ShowAsync(); } } catch (SQLiteException) { } catch (NullReferenceException ex) { list1.Items.Add(ex.Message); } }
public static void addCompletedLine(Line line) { completeLines.Add(line); string dbPath = GetDBPath(); SQLiteConnection db; db = new SQLiteConnection(dbPath); db.Insert(line); var result = db.Query<Line>("SELECT * FROM Lines"); foreach (Line l in result) { Console.WriteLine("Line DB: bx {0}, by {1}, ex {2}, ey {3}", l.begin.X, l.begin.Y, l.end.X, l.end.Y); } db.Close(); db = null; foreach (Line l in completeLines) { Console.WriteLine("Line CL: bx {0}, by {1}, ex {2}, ey {3}", l.begin.X, l.begin.Y, l.end.X, l.end.Y); } }
// Top Songs Cache // public static DateTime topSongsCacheDate { // get { // return (DateTime)NSUserDefaults.StandardUserDefaults.ValueForKey(new NSString("topSongsCacheDate")); // } // set { // topSongsCacheDate = value; // NSUserDefaults.StandardUserDefaults.SetValueForKey(value, new NSString("topSongsCacheDate")); // } // } public static void FetchRSSFeed(Block completionBlock) // UITableViewController tvc) { channel = new RSSChannel(); channel.type = "posts"; items.Clear(); string dbPath = GetDBPath(); SQLiteConnection db; if (!File.Exists(dbPath)) { db = new SQLiteConnection(dbPath); db.CreateTable<RSSItem>(); db.Close(); db = null; } db = new SQLiteConnection(dbPath); items = db.Query<RSSItem>("SELECT * FROM RSSItems WHERE type='post' ORDER BY ID DESC"); db.Close(); db = null; FetchRSSAsync(completionBlock); }
private async void createtable(object sender, RoutedEventArgs e) { try { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { // Create the tables if they don't exist db.CreateTable <person>(); db.Commit(); db.Dispose(); db.Close(); } var line = new MessageDialog("Table Created"); await line.ShowAsync(); } catch { } }
protected override void OnCreate(Bundle bundle) { base.OnCreate(bundle); // Set our view from the "main" layout resource SetContentView(Resource.Layout.Main); // Get our button from the layout resource, // and attach an event to it using (var conn = new SQLite.SQLiteConnection(Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal).ToString(), "database.sqlite"))) { if (conn.ExecuteScalar <int>("SELECT count(*) FROM sqlite_master WHERE type = 'table' AND name = 'Login'") == 0) { conn.CreateTable <Login>(); conn.Commit(); } else { if (conn.ExecuteScalar <int>("Select Count(*) From Login") != 0) { var NxtAct = new Intent(this, typeof(StartActivity)); StartActivity(NxtAct); } } conn.Close(); } Button button = FindViewById <Button> (Resource.Id.Login); button.Click += delegate { var NxtAct = new Intent(this, typeof(LoginActivity)); StartActivity(NxtAct); }; Button button1 = FindViewById <Button>(Resource.Id.Register); button1.Click += delegate { var NxtAct = new Intent(this, typeof(RegisterActivity)); StartActivity(NxtAct); }; }
protected void saveProfileImage(string image) { ProfileImage profileImage = new ProfileImage(); profileImage.Base64 = image; using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(App.FilePath)) { conn.DropTable <ProfileImage>(); conn.CreateTable <ProfileImage>(); int rowsAdded = conn.Insert(profileImage); if (rowsAdded > 0) { Debug.WriteLine("Inserted Itinerary"); } else { Debug.WriteLine("Failed to Insert Itinerary"); } conn.Close(); } }
public static void loadItemsFromArchive() { // Archive method of saving // string path = lineArchivePath(); // var unarchiver = (NSMutableArray)NSKeyedUnarchiver.UnarchiveFile(path); // if (unarchiver != null) { // for (int i = 0; i < unarchiver.Count; i++) { // completeLines.Add(unarchiver.GetItem<Line>(i)); // } // } string dbPath = GetDBPath(); SQLiteConnection db; if (!File.Exists(dbPath)) { db = new SQLiteConnection(dbPath); db.CreateTable<Line>(); var columnInfo = db.GetTableInfo("Lines"); foreach (SQLiteConnection.ColumnInfo ci in columnInfo) { //Console.WriteLine("Collumn Info: {0}", ci.Name); } db.Close(); db = null; } db = new SQLiteConnection(dbPath); completeLines = db.Query<Line>("SELECT * FROM Lines"); // foreach (Line line in completeLines) { // //line.setColor(); // //Console.WriteLine("Line bx {0}, by {1}, ex {2}, ey {3}", line.begin.X, line.begin.Y, line.end.X, line.end.Y); // } db.Close(); db = null; }
public App(string filePath) { Syncfusion.Licensing.SyncfusionLicenseProvider.RegisterLicense("MjMyMTM2QDMxMzgyZTMxMmUzMGpzYzNpT1FVcnp4ZDJwdWhQUDhNdnI0MlNxMTlLTEEyZkRIOStpYkpMTlE9"); InitializeComponent(); MainPage = new MainPage(); FilePath = filePath; using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(App.FilePath)) { conn.CreateTable<User>(); var users = conn.Table<User>().ToList(); var doctors = conn.Table<Doctor>(); //Each prescription reminder requires a unique ID which we will track and save to settings. bool hasPrescriptionReminderKey = Preferences.ContainsKey("PrescriptionReminderNotifID"); int PrescriptionReminderID = 1000000; //If the setting does not exist, create it and set it with a starting value. if (!hasPrescriptionReminderKey) { Preferences.Set("PrescriptionReminderNotifID", PrescriptionReminderID); } else { PrescriptionReminderID = Preferences.Get("PrescriptionReminderNotifID", 1000000); //If the ID number gets within 10000 of overflowing, we will reset it. if ((PrescriptionReminderID + 10000) >= int.MaxValue) { PrescriptionReminderID = 1000000; } } if (users?.Any() == true) { //Have to resubmit notifications after device restart because they are not saved. var appts = conn.Table<Appointment>().ToList(); var prescriptions = conn.Table<Prescription>().ToList(); //We need to set up reminders for any prescriptions currently being taken. prescriptions = prescriptions.Where(item => (DateTime.Parse(item.startDate).Date <= DateTime.Now.Date) && (DateTime.Parse(item.endDate).Date >= DateTime.Now.Date)).ToList(); if (prescriptions?.Any() == true) { foreach (var prescript in prescriptions) { //Refresh the reminder notifications for each prescription if it is not entirely past the time that it must be taken. if (DateTime.Parse(prescript.endDate) >= DateTime.Now) { PrescriptionNotifClass.PrescriptionNotifHandler(prescript); } } } if (appts?.Any() == true) { foreach (var x in appts) { if (x.reminderTime > DateTime.Now) { var doctor = conn.GetWithChildren<Doctor>(x.dId); CrossLocalNotifications.Current.Show("Appointment Reminder", "You have an appointment with Dr. " + doctor.dName + " at " + x.aptDate.ToShortTimeString(), x.Id, x.reminderTime); } } } } conn.Close(); } }
//카테고리 수정 public void UpdateIncomeCategoryHandling(string oldCategoryName, string newCategoryName, string tableFormName) { try { using (SQLiteConnection conn = new SQLiteConnection(dbPath, true)) { //수입항목 카테고리 수정 if (tableFormName.Equals("IncomeCategoryForm")) { //기존에 존재하는 카테고리의 ID를 알기위해서 해당 목록을 불러온다. IncomeCategoryForm exist = conn.Query<IncomeCategoryForm>("SELECT * FROM IncomeCategoryTable WHERE categoryName ='" + oldCategoryName + "';").FirstOrDefault(); if (exist != null) { //기존에 있던 정보를 수정한다. exist.categoryName = newCategoryName; //업데이트 conn.Update(exist); } // 현재 oldcategoryname으로 되어있는 항목들의 category를 전부 바꿔준다. List<MoneyDBForm> existMoney = conn.Query<MoneyDBForm>("SELECT * FROM MoneyTable WHERE category = '" + oldCategoryName + "';").ToList<MoneyDBForm>(); if(existMoney != null) { foreach (var item in existMoney) { item.category = newCategoryName; conn.Update(item); } } } //지출항목 카테고리 수정 else if (tableFormName.Equals("ExpenseCategoryForm")) { //ID알기 ExpenseCategoryForm exist = conn.Query<ExpenseCategoryForm>("SELECT * FROM ExpenseCategoryTable WHERE categoryName = '" + oldCategoryName + "';").FirstOrDefault(); if (exist != null) { //기존에 있던 정보를 수정한다. exist.categoryName = newCategoryName; //업데이트 conn.Update(exist); } // 현재 oldcategoryname으로 되어있는 항목들의 category를 전부 바꿔준다. List<MoneyDBForm> existMoney = conn.Query<MoneyDBForm>("SELECT * FROM MoneyTable WHERE category = '" + oldCategoryName + "';").ToList<MoneyDBForm>(); if (existMoney != null) { foreach (var item in existMoney) { item.category = newCategoryName; conn.Update(item); } } } //연결종료 conn.Close(); } } catch (Exception ex) { ex.Message.ToString(); } }
//테이블 전체 삭제 및 재생성 public bool AllTableDrop() { bool result = false; try { //DB Connection SQLiteConnection conn = new SQLiteConnection(dbPath, true); //테이블 삭제 conn.DropTable<DBForm.MoneyDBForm>(); conn.DropTable<DBForm.ExpenseCategoryForm>(); conn.DropTable<DBForm.IncomeCategoryForm>(); //연결종료 conn.Close(); //새로 테이블을 만든다. CreateDatabaseAsync(); //카테고리 새로 세팅 BasicSetting(); //어플리케이션종료하기위해 결과 값을 리턴한다. result = true; } catch (Exception ex) { ex.Message.ToString(); result = false; } return result; }
//수입 카테고리 조회 쿼리문 처리 public List<IncomeCategoryForm> IncomeCategoryQueryHandling(string outQuery) { try { //연결 SQLiteConnection conn = new SQLiteConnection(dbPath, true); //쿼리문을 날려서 해당 테이블을 조회해서 List로 출력 List<IncomeCategoryForm> incomeQueryResultList = conn.Query<IncomeCategoryForm>(outQuery); //연결종료 conn.Close(); //정상적으로 되면 결과 리턴 return incomeQueryResultList; } catch (Exception ex) { ex.Message.ToString(); } //불러오는데 실패하면 NULL을 리턴 return null; }
public void SaveSyncParam() { SQLiteConnection db = new SQLiteConnection(SyncController.DatabasePath); db.CreateTable<SyncParams>(); this.id = 1; if (db.Table<SyncParams>().Count() != 0) db.Update(this); else db.Insert(this); db.Close(); }
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(); } }
//Delete all contactlist or delete Contacts table public void DeleteAllContact() { using (var dbConn = new SQLiteConnection(App.DB_PATH)) { //dbConn.RunInTransaction(() => // { dbConn.DropTable<GhiChu>(); dbConn.CreateTable<GhiChu>(); dbConn.Dispose(); dbConn.Close(); //}); } }
public void Dispose() { connection.Close(); }
// This method checks to see if the database exists, and if it doesn't, it creates // it and inserts some data protected void CheckAndCreateDatabase(string dbName) { // create a connection object. if the database doesn't exist, it will create // a blank database using(SQLiteConnection db = new SQLiteConnection (GetDBPath (dbName))) { // create the tables db.CreateTable<GroupPersonData> (); // close the connection db.Close (); } }
private string GetInvoiceSumm(DateTime printDate1, DateTime printDate2) { string text = ""; int count = 0; double ttlAmtl=0; double ttlCN=0; double ttl = 0; double rate = 0; double roundVal = 0; double GranInv = 0; double GranCN = 0; double GranCSInv = 0; double GranCSCN = 0; string cnno = ""; bool isSamedate = printDate1==printDate2; string pathToDatabase = ((GlobalvarsApp)Application.Context).DATABASE_PATH; SQLiteConnection db = new SQLiteConnection(pathToDatabase); PrintSummHeader (printDate1, printDate2, ref text); var list = db.Table<Invoice>().ToList(); var cnlist = db.Table<CNNote>().ToList(); var grps = from p in list where p.invdate >= printDate1 && p.invdate <= printDate2 group p by p.invdate into g select new { key = g.Key, result = g.ToList() }; foreach (var grp in grps) { count = 1; GranInv = 0; GranCN = 0; GranCSInv = 0; GranCSCN = 0; //"------------------------------------------\"; //12. 123456789012345678901234567890 12345567 //CN without Inv var cnNoInvs = cnlist.Where (x => x.invdate == grp.key && x.invno == ""); if (!isSamedate) { text = text + grp.key.ToString ("dd-MM-yyyy") + "\n"; text = text + "------------------------------------------\n"; } foreach (var itm in grp.result) { ttlAmtl=itm.amount+itm.taxamt; ttlCN=0; ttl = 0; cnno = ""; //var cninv = cnlist.Where(x => x.invno == itm.invno && x.invdate == itm.invdate).FirstOrDefault(); var cninv = cnlist.Where(x => x.invno == itm.invno ).FirstOrDefault(); if (cninv!=null) { cnno = cninv.cnno; ttlCN=cninv.amount + cninv.taxamt; } if (itm.trxtype == "CASH") { ttlAmtl = Utility.AdjustToNear(ttlAmtl, ref roundVal); ttlCN = Utility.AdjustToNear(ttlCN, ref roundVal); GranCSInv = GranCSInv + ttlAmtl; GranCSCN = GranCSCN + ttlCN; } else { GranInv = GranInv + ttlAmtl; GranCN = GranCN+ttlCN; } if (itm.description.Length > 30) { //12. 123456789012345678901234567890 12345567 text = text + count.ToString().PadRight(2, ' ') + ". " + itm.description.Substring(0, 30) + " " + itm.created.ToString("hh:mmtt") + "\n"; text = text + "".PadRight(4, ' ') + itm.description.Substring(30).Trim()+ "\n"; } else { text = text + count.ToString().PadRight(2, ' ') + ". " + itm.description.PadRight(30,' ') + " " + itm.created.ToString("hh:mmtt") + "\n"; } text = text + "INV AMT -"+itm.invno.PadRight(21,' ')+ttlAmtl.ToString("n2").PadLeft(12,' ')+"\n"; text = text + "CN AMT -" + cnno.PadRight(21, ' ') + ttlCN.ToString("n2").PadLeft(12, ' ') + "\n"; ttl = ttlAmtl - ttlCN; if (ttlCN > 0) rate = Math.Round((ttlCN / ttlAmtl)*100, 2); text = text + "TOTAL COLLECT AMOUNT -".PadRight(30,' ') + ttl.ToString("n2").PadLeft(12, ' ') + "\n"; text = text + "RETURN RATE % -".PadRight(30, ' ') + rate.ToString("n2").PadLeft(12, ' ') + "\n\n"; count += 1; } foreach (var itm in cnNoInvs) { ttlAmtl=0; ttlCN=itm.amount+itm.taxamt; ttl = 0; cnno = ""; if (itm.trxtype == "CASH") { ttlCN = Utility.AdjustToNear(ttlCN, ref roundVal); GranCSCN = GranCSCN + ttlCN; } else { GranCN = GranCN+ttlCN; } if (itm.description.Length > 30) { //12. 123456789012345678901234567890 12345567 text = text + count.ToString().PadRight(2, ' ') + ". " + itm.description.Substring(0, 30) + " " + itm.created.ToString("hh:mmtt") + "\n"; text = text + "".PadRight(4, ' ') + itm.description.Substring(30).Trim()+ "\n"; } else { text = text + count.ToString().PadRight(2, ' ') + ". " + itm.description.PadRight(30,' ') + " " + itm.created.ToString("hh:mmtt") + "\n"; } text = text + "INV AMT -"+itm.invno.PadRight(21,' ')+ttlAmtl.ToString("n2").PadLeft(12,' ')+"\n"; text = text + "CN AMT -" + itm.cnno.PadRight(21, ' ') + ttlCN.ToString("n2").PadLeft(12, ' ') + "\n"; ttl = ttlAmtl - ttlCN; rate = 0; text = text + "TOTAL COLLECT AMOUNT -".PadRight(30,' ') + ttl.ToString("n2").PadLeft(12, ' ') + "\n"; text = text + "RETURN RATE % -".PadRight(30, ' ') + rate.ToString("n2").PadLeft(12, ' ') + "\n\n"; count += 1; } text = text + "\n"; text = text + "SALES SUMMARY\n\n"; text = text + "TOTAL INVOICE ".PadRight(30, ' ') + GranInv.ToString("n2").PadLeft(12, ' ') + "\n"; text = text + "TOTAL CN INVOICE".PadRight(30, ' ') + GranCN.ToString("n2").PadLeft(12, ' ') + "\n"; text = text + "TOTAL INVOICE COLLECT ".PadRight(30, ' ') + (GranInv - GranCN).ToString("n2").PadLeft(12, ' ') + "\n\n"; text = text + "TOTAL CASH ".PadRight(30, ' ') + GranCSInv.ToString("n2").PadLeft(12, ' ') + "\n"; text = text + "TOTAL CN CASH".PadRight(30, ' ') + GranCSCN.ToString("n2").PadLeft(12, ' ') + "\n"; text = text + "TOTAL CASH COLLECT ".PadRight(30, ' ') + (GranCSInv - GranCSCN).ToString("n2").PadLeft(12, ' ') + "\n\n"; } text += "------------------------------------------\n"; text += "CASH COLLECTION :\n\n\n"; text += "(-)DIESEL EXP :\n\n\n"; text += "(-)OTHER EXP :\n\n\n"; text += "(=)NET COLLECTION :\n\n\n"; text += "(-)PAYMENT :\n\n\n"; text += "(=)SHORT :\n\n\n"; text += "PREPARED BY:\n\n\n\n\n"; text += "VERIFY BY :\n\n\n\n\n"; text += "------------------------------------------\n\n\n\n"; db.Close (); return text; }