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 FootballPlayer() { database = DependencyService.Get<MySqlConn> ().getConnection (); var count = database.ExecuteScalar<int> ("SELECT Count(*) FROM FootballPlayer"); if (count == 0) { database.CreateTable<FootballPlayer> (); } }
private static bool isTableExisting(string strTblName){ try{ var connection = new SQLiteConnection(path); var count = connection.ExecuteScalar<int>("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='" + strTblName + "'"); if(count > 0){ return true; }else{ return false; } }catch(SQLiteException){ return false; } }
public MapServiceSQLite() { using (SQLiteConnection connection = new SQLiteConnection(SQLiteConfiguration.ConnectionString)) { connection.CreateTable<MapModel>(); if (connection.ExecuteScalar<int>("SELECT COUNT(*) FROM Maps") == 0) connection.RunInTransaction(() => { connection.Insert(new MapModel(Guid.NewGuid(), "Default Map", @"ms-appx:///MetroExplorer.Components.Maps/DesignAssets/MapBackground.bmp")); }); connection.CreateTable<MapLocationModel>(); connection.CreateTable<MapLocationFolderModel>(); } }
public async void Initialize() { using (var db = new SQLite.SQLiteConnection(_dbPath)) { db.CreateTable <Customer>(); //Note: This is a simplistic initialization scenario if (db.ExecuteScalar <int>("select count(1) from Customer") == 0) { db.RunInTransaction(() => { db.Insert(new Customer() { FirstName = "Phil", LastName = "Japikse" }); db.Insert(new Customer() { FirstName = "Jon", LastName = "Galloway" }); db.Insert(new Customer() { FirstName = "Jesse", LastName = "Liberty" }); }); } else { await Load(); } } }
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); }; }
public static decimal GetBalance() { decimal balance = 0; //SQLiteCommand cmd; using (var conn = new SQLite.SQLiteConnection(my_db_path)) { //conn.Table<Transaction>().Sum<Transaction>(Fun) try { balance = conn.ExecuteScalar <decimal>("SELECT SUM(Amount) FROM 'Transaction'"); return(balance); } catch { return(balance); } } }
public static decimal GetTotal(string type, string category) { decimal total = 0; //type = "Expense"; //SQLiteCommand cmd; using (var conn = new SQLite.SQLiteConnection(my_db_path)) { //conn.Table<Transaction>().Sum<Transaction>(Fun) try { total = conn.ExecuteScalar <decimal>("SELECT SUM(Amount) FROM 'Transaction' WHERE Type = ? AND Category = ?", type, category); return(total); } catch { return(total); } } }
private void clickFoodDialogList(object sender, DialogClickEventArgs args) { listChoice = choices [args.Which]; Console.WriteLine ("Selected: {0}", args.Which); // Open a confirmation alert AlertDialog.Builder confirmFoodDialogBuilder = new AlertDialog.Builder(this); confirmFoodDialogBuilder.SetTitle ("Confirm selection"); confirmFoodDialogBuilder.SetMessage ("You are adding the following choice: " + listChoice + ". Do you wish to proceed?"); // Insert the selection into the database on confirmation confirmFoodDialogBuilder.SetPositiveButton ("Confirm", delegate { dismissAddFoodDialog (); LogEntry newLog = new LogEntry { LoggedAt = DateTime.Now, Level = LogEntry.MapToLevel(args.Which) }; using (var db = new SQLiteConnection(dbPath)) { db.Insert (newLog); var count = db.ExecuteScalar<int> ("Select COUNT(*) from LogEntry"); Console.WriteLine("There are now {0} Log Entries", count); } }); // Close all alerts if the user cancels at this point confirmFoodDialogBuilder.SetNegativeButton ("Cancel", delegate { dismissAddFoodDialog (); }); confirmFoodDialog = confirmFoodDialogBuilder.Create (); confirmFoodDialog.Show (); }
private static void do_feeds(SQLiteConnection db, List<feed_row> rows) { foreach (feed_row q in rows) { string dbfile_name_for_this_feed = string.Format("feed_{0}", q.feedid); Console.WriteLine("Updating {0}: {1}", dbfile_name_for_this_feed, q.url); SyndicationFeed f = null; try { XmlReader xr = new XmlTextReader(q.url); f = SyndicationFeed.Load(xr); } catch (Exception e) { Console.WriteLine("{0}", e); // TODO failed trying to retrieve or parse this feed. // TODO log the failure? // TODO delete the feed row? // TODO launch nethack? } if (f != null) { db.Execute("ATTACH ? AS cur;", dbfile_name_for_this_feed); bool found = false; var query = "pragma cur.table_info(\"items\")"; List<SQLiteConnection.ColumnInfo> cols = db.Query<SQLiteConnection.ColumnInfo> (query); foreach (var c in cols) { found = (string.Compare ("permalink", c.Name, StringComparison.OrdinalIgnoreCase) == 0); if (found) break; } if (! found) { db.Execute("BEGIN TRANSACTION;"); db.ExecuteScalar<string>(@"select zumero_alter_table_add_column('cur', 'items', 'permalink TEXT');"); db.Execute("COMMIT TRANSACTION;"); // after altering a zumero table, the dbfile connection must be reopened db.Execute("DETACH cur;", dbfile_name_for_this_feed); db.Execute("ATTACH ? AS cur;", dbfile_name_for_this_feed); } db.Execute("BEGIN TRANSACTION;"); // set last_update to the time we retrieved the feed XML db.Execute( @"INSERT OR REPLACE INTO main.last_update (feedid, when_unix_time) VALUES (?, strftime('%s','now') );", q.feedid); foreach (SyndicationItem it in f.Items) { Console.WriteLine(" {0}", it.Title.Text); TextSyndicationContent t = (TextSyndicationContent) it.Summary; if (null == t) t = (TextSyndicationContent)it.Content; string id = it.Id; string url = null; foreach(SyndicationLink link in it.Links) { url = link.Uri.ToString(); break; } if (null == id) id = url; if (null == url) url = id; if (null == id) { Console.WriteLine(" no id"); } else { db.Execute("INSERT OR IGNORE INTO cur.items (id, title, summary, pubdate_unix_time, permalink) VALUES (?,?,?,?,?)", id, it.Title.Text, t.Text, (it.PublishDate.UtcDateTime - new DateTime(1970,1,1)).TotalSeconds, url ); } } db.Execute("COMMIT TRANSACTION;"); db.Execute("DETACH cur;"); } } }
protected override void OnCreate(Bundle bundle) { base.OnCreate(bundle); // Create your application here SetContentView(Resource.Layout.Update); var buttonUpdate = FindViewById<Button>(Resource.Id.buttonUpdate); var textViewCurrentVersion = FindViewById<TextView>(Resource.Id.textViewCurrentVersion); var textViewLatestVersion = FindViewById<TextView>(Resource.Id.textViewLatestVersion); var progressBarUpdate = FindViewById<ProgressBar>(Resource.Id.progressBarUpdate); var buttonAppUpdate = FindViewById<Button>(Resource.Id.buttonAppUpdate); var textViewCurrentAppVersion = FindViewById<TextView>(Resource.Id.textViewCurrentAppVersion); var textViewLatestAppVersion = FindViewById<TextView>(Resource.Id.textViewLatestAppVersion); progressBarUpdate.Visibility = ViewStates.Invisible; var preference = GetSharedPreferences("settings", FileCreationMode.WorldWriteable); var editor = preference.Edit(); if (!preference.Contains("version")) { editor.PutString("version", "20150504"); editor.Commit(); } textViewCurrentVersion.Text = preference.GetString("version", "20150504"); editor.PutString("appVersion", PackageManager.GetPackageInfo(PackageName, 0).VersionName); editor.Commit(); textViewCurrentAppVersion.Text = preference.GetString("appVersion", "0"); buttonUpdate.Click += async (sender, e) => { try { buttonUpdate.Enabled = false; buttonUpdate.Text = "正在更新"; var requestVersion = WebRequest.Create("http://kidfruit.github.io/WisdriContacts/LatestDataVersion.txt?" + Guid.NewGuid().ToString()); requestVersion.Method = "GET"; using (var response = await requestVersion.GetResponseAsync()) { using (var responseStream = response.GetResponseStream()) { using (var sr = new StreamReader(responseStream, Encoding.UTF8)) { var receive = sr.ReadToEnd(); textViewLatestVersion.Text = receive; } } } if (!string.IsNullOrEmpty(textViewLatestVersion.Text.Trim()) && textViewCurrentVersion.Text != textViewLatestVersion.Text) { progressBarUpdate.Visibility = ViewStates.Visible; var request = WebRequest.Create("http://kidfruit.github.io/WisdriContacts/data.txt?" + Guid.NewGuid().ToString()); request.Method = "GET"; using (var response = await request.GetResponseAsync()) { using (var responseStream = response.GetResponseStream()) { using (var sr = new StreamReader(responseStream, Encoding.GetEncoding("UTF-8"))) { int count = 0; long length = responseStream.Length; byte[] receiveStream = new byte[length]; progressBarUpdate.Visibility = ViewStates.Visible; progressBarUpdate.Indeterminate = false; progressBarUpdate.Max = (int)length; progressBarUpdate.Progress = 0; while (true) { int readLength = (int)(length - count > 1000 ? 1000 : length - count); int num = await responseStream.ReadAsync(receiveStream, count, readLength); if (num == 0) break; count += num; progressBarUpdate.Progress = count; } var receive = Encoding.UTF8.GetString(receiveStream, 0, (int)length); //var receive = await sr.ReadToEndAsync(); var byteValue = Convert.FromBase64String(receive); string decodeReceive = Encoding.UTF8.GetString(byteValue, 0, byteValue.Length); var personList = JsonConvert.DeserializeObject<List<PERSON>>(decodeReceive); var dbFile = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "contacts.db"); using (SQLiteConnection conn = new SQLiteConnection(dbFile)) { try { var sql = "delete from PERSON"; int oldDataCount = conn.Execute(sql); new AlertDialog.Builder(this).SetMessage(string.Format("旧数据有{0}条", oldDataCount)).Show(); sql = "DROP TABLE [PERSON];"; conn.Execute(sql); sql = @"CREATE TABLE [PERSON] ( [ID] nvarchar(2147483647) NOT NULL , [NAME] nvarchar(2147483647) NULL , [DEPARTMENT] nvarchar(2147483647) NULL , [MOBILE_PHONE] nvarchar(2147483647) NULL , [VIRTUAL_PHONE] nvarchar(2147483647) NULL , [POSITION] nvarchar(2147483647) NULL , [REGION] nvarchar(2147483647) NULL , [OFFICE_PHONE] nvarchar(2147483647) NULL , [INNER_PHONE] nvarchar(2147483647) NULL , [PY] nvarchar(2147483647) NULL , [CAR] nvarchar(2147483647) NULL , CONSTRAINT [sqlite_autoindex_PERSON_1] PRIMARY KEY ([ID]) );"; conn.Execute(sql); conn.BeginTransaction(); conn.InsertAll(personList); textViewCurrentVersion.Text = textViewLatestVersion.Text; editor.PutString("version", textViewCurrentVersion.Text); editor.Commit(); sql = "select count(ID) from PERSON"; int newDataCount = conn.ExecuteScalar<int>(sql); new AlertDialog.Builder(this).SetMessage(string.Format("新数据有{0}条", newDataCount)).Show(); conn.Commit(); new AlertDialog.Builder(this).SetMessage("更新完毕").SetPositiveButton("确定", delegate { }).Show(); } catch (Exception ex) { conn.Rollback(); new AlertDialog.Builder(this).SetMessage(ex.Message).Show(); } } } } } } else { new AlertDialog.Builder(this).SetMessage("无需更新").Show(); } } catch (System.Exception ex) { new AlertDialog.Builder(this).SetMessage(ex.Message).Show(); } finally { progressBarUpdate.Visibility = ViewStates.Invisible; buttonUpdate.Enabled = true; buttonUpdate.Text = "更新数据"; } }; buttonAppUpdate.Click += async (sender, e) => { try { buttonAppUpdate.Enabled = false; var requestAppVersion = WebRequest.Create("http://kidfruit.github.io/WisdriContacts/LatestAndroidVersion.txt?" + Guid.NewGuid().ToString()); requestAppVersion.Method = "GET"; using (var response = await requestAppVersion.GetResponseAsync()) { using (var responseStream = response.GetResponseStream()) { using (var sr = new StreamReader(responseStream, Encoding.UTF8)) { var receive = sr.ReadToEnd(); textViewLatestAppVersion.Text = receive; } } } buttonAppUpdate.Enabled = true; if (!string.IsNullOrEmpty(textViewLatestAppVersion.Text.Trim()) && textViewCurrentAppVersion.Text != textViewLatestAppVersion.Text) { var uri = Android.Net.Uri.Parse("http://pan.baidu.com/s/1ntypvj7"); var intent = new Intent(Intent.ActionView, uri); StartActivity(intent); new AlertDialog.Builder(this).SetMessage("下载更新包").Show(); } else { new AlertDialog.Builder(this).SetMessage("无需更新").Show(); } } catch (System.Exception ex) { new AlertDialog.Builder(this).SetMessage(ex.Message).Show(); } finally { progressBarUpdate.Visibility = ViewStates.Invisible; } }; }
public static string ScalarQuery () { var output = ""; output += "\nScalar query example: "; string dbPath = Path.Combine ( Environment.GetFolderPath (Environment.SpecialFolder.Personal), "ormdemo.db3"); var db = new SQLiteConnection (dbPath); var rowcount = db.ExecuteScalar<int> ("SELECT COUNT(*) FROM [Items] WHERE Symbol <> ?", "MSFT"); output += "\nNumber of rows : " + rowcount; return output; }
//------------------------------------------------------------------------// //used for debugging purposes public int get_total_records() { try { var db = new SQLiteConnection(m_db_path); // this counts all records in the database, it can be slow depending on the size of the database var count = db.ExecuteScalar<int>("SELECT Count(*) FROM ShopItem"); return count; } catch (SQLiteException ex) { Console.WriteLine("get total records has failed, ex.msg :{0}",ex.Message); return -1; } }
public static void CreateSampleData() { int termId = -999; string termName = "Term 1 (for evaluation purposes)"; bool termIsFound = false; using (SQLite.SQLiteConnection connection = new SQLite.SQLiteConnection(App.DBPath)) { connection.CreateTable <Term>(); List <Term> Terms = connection.Table <Term>().ToList(); for (var i = 0; i < Terms.Count; i++) { if (Terms[i].Name == termName) { termIsFound = true; } } //Only create the sample data if the term does not already exist. if (termIsFound != true) { Term term = new Term { Name = termName, StartDate = new DateTime(2019, 11, 1), EndDate = new DateTime(2020, 4, 30) }; int numberInserted = connection.Insert(term); //If the Term insertion was successful, assign the Term Id. if (numberInserted > 0) { termId = connection.ExecuteScalar <int>("SELECT last_insert_rowid()"); } } } //If the Term was inserted, create a course for the Term. if (termId >= 0) { Course course = new Course { TermId = termId, Name = "Mobile Application Development Using C# – C971", StartDate = new DateTime(2019, 11, 1), EndDate = new DateTime(2019, 12, 15), Status = "Plan to take", InstructorName = "Timothy Horton", InstructorPhone = "334-300-4786", InstructorEmail = "*****@*****.**", ObjectiveName = "Xamarin Principles Exam", ObjectiveStart = new DateTime(2019, 12, 1), ObjectiveEnd = new DateTime(2019, 12, 3), PerformanceName = "Sample Mobile Appliation", PerformanceStart = new DateTime(2019, 12, 4), PerformanceEnd = new DateTime(2019, 12, 15), startNotify = true, endNotify = true, startObjNotify = true, endObjNotify = true, startPerfNotify = true, endPerfNotify = true }; using (SQLite.SQLiteConnection connection = new SQLite.SQLiteConnection(App.DBPath)) { connection.CreateTable <Course>(); connection.Insert(course); } } }
private static string getCurrentSig(SQLiteConnection db) { string json = db.ExecuteScalar<string>("SELECT v FROM t$v WHERE k=10"); return DependencyService.Get<ISHA1Service>().HashString(json); }
private static void do_feeds(SQLiteConnection db, List<feed_row> rows) { foreach (feed_row q in rows) { string dbfile_name_for_this_feed = string.Format("feed_{0}", q.feedid); Console.WriteLine("Creating {0}: {1}", dbfile_name_for_this_feed, q.url); SyndicationFeed f = null; try { XmlReader xr = new XmlTextReader(q.url); f = SyndicationFeed.Load(xr); } catch (Exception e) { Console.WriteLine("{0}", e); // TODO failed trying to retrieve or parse this feed. // TODO log the failure? // TODO delete the feed row? // TODO launch nethack? } if (f != null) { db.Execute("ATTACH ? AS cur;", dbfile_name_for_this_feed); db.Execute("BEGIN TRANSACTION;"); db.Execute( @"CREATE VIRTUAL TABLE cur.items USING zumero ( id TEXT PRIMARY KEY NOT NULL, title TEXT NOT NULL, summary TEXT NOT NULL, pubdate_unix_time INTEGER NOT NULL, permalink TEXT );" ); // each feed is allowed to be pulled by anyone, but only the admin user // can make changes db.ExecuteScalar<string>( @"SELECT zumero_define_acl_table('cur');" ); db.Execute( @"INSERT INTO cur.z_acl (scheme,who,tbl,op,result) VALUES ( '', zumero_named_constant('acl_who_anyone'), '', '*', zumero_named_constant('acl_result_deny') );" ); db.Execute( @"INSERT INTO cur.z_acl (scheme,who,tbl,op,result) VALUES ( zumero_internal_auth_scheme('zumero_users_admin'), zumero_named_constant('acl_who_any_authenticated_user'), '', '*', zumero_named_constant('acl_result_allow') );" ); db.Execute( @"INSERT INTO cur.z_acl (scheme,who,tbl,op,result) VALUES ( '', zumero_named_constant('acl_who_anyone'), '', zumero_named_constant('acl_op_pull'), zumero_named_constant('acl_result_allow') );" ); // set the feed title db.Execute("INSERT INTO main.about (feedid, title) VALUES (?,?)", q.feedid, f.Title.Text ); db.Execute("COMMIT TRANSACTION;"); db.Execute("DETACH cur;"); } } }
double FetchStatsFromDate (SQLiteConnection connection, DateTime dt) { return connection.ExecuteScalar<double> (SumQuery, dt.ToUniversalTime ()); }
public static string getRandomTip(){ try { var db = new SQLiteConnection(path); string tip = db.ExecuteScalar<string>("SELECT TipPhrase FROM Tip WHERE ID = " + Convert.ToString(new System.Random().Next(12) + 1)); if(string.IsNullOrEmpty(tip)){ tip = "0 returned"; } return tip; } catch (SQLiteException ex) { return ex.Message ; } }
private int findNumberRecords(){ try{ var db = new SQLiteConnection(path); // this counts all records in the database, it can be slow depending on the size of the database var count = db.ExecuteScalar<int>("SELECT Count(*) FROM Phrases"); // for a non-parameterless query // var count = db.ExecuteScalar<int>("SELECT Count(*) FROM Person WHERE FirstName="Amy"); return count; }catch (SQLiteException){ return -1; } }
public static int getCoins(){ try { var db = new SQLiteConnection(path); return db.ExecuteScalar<int>("SELECT CoinsCount FROM Coins WHERE ID = 1"); } catch (SQLiteException) { return 0; } }
public void Initialize() { using (var db = new SQLite.SQLiteConnection(_dbPath)) { db.CreateTable <Room>(); db.CreateTable <Client>(); db.CreateTable <Record>(); db.CreateTable <SettingModel>(); int countRoom = db.ExecuteScalar <int>("select count(1) from Room"); int countClient = db.ExecuteScalar <int>("select count(1) from Client"); int countRecord = db.ExecuteScalar <int>("select count(1) from Record"); int countSetting = db.ExecuteScalar <int>("select count(1) from SettingModel"); //Note: This is a simplistic initialization scenario if (countRoom == 0) { db.RunInTransaction(() => { db.Insert(new Room() { oirecord = -1, roomNo = countRoom + 1, ipaddress = "192.168.1.1", computerDescription = "new computer", remarks = "no remaks", active = true, isAvailable = true, modDate = new DateTime() }); db.Insert(new Room() { oirecord = -1, roomNo = countRoom + 2, ipaddress = "192.168.1.2", computerDescription = "new computer", remarks = "no remaks", active = true, isAvailable = true, modDate = new DateTime() }); db.Insert(new SettingModel() { key = "halfhour", value = "20", active = true, modDate = DateTime.Now }); db.Insert(new SettingModel() { key = "firsthour", value = "30", active = true, modDate = DateTime.Now }); db.Insert(new SettingModel() { key = "secondhour", value = "20", active = true, modDate = DateTime.Now }); /* * db.Insert(new Client() * { * description = "i m muzammil peer", * imageurl = "", * name = "muzammil peer", * gender = true, * password = "", * active = true, * lastLogin = new DateTime(), * modDate = new DateTime() * });*/ // Create the tables if they don't exist /* db.Insert(new Record() * { * active = true, * checkIn = new DateTime(), * checkOut = new DateTime(), * isCardReader = "none", * isWebCam = "none", * modDate = new DateTime(), * oiclient = 1, * recievedAmount = 0.0, * totalAmount = 10.0, * });*/ }); } else { LoadRoom(); LoadClient(); LoadRecord(); LoadRecordWithInt(); LoadSettingModel(); } } }
private static SQLiteConnection open_and_load_zumero(string s) { // open the local SQLite db SQLiteConnection db = new SQLiteConnection(s); // tell SQLite to allow load_extension() db.EnableLoadExtension(1); // load the Zumero extension // this is the equivalent of ".load zumero.dylib" in the sqlite3 shell db.ExecuteScalar<string>("SELECT load_extension('zumero.dylib');"); return db; }
double FetchStatsBetweenDates (SQLiteConnection connection, DateTime startTime, DateTime endTime) { return connection.ExecuteScalar<double> (SumBetweenQuery, startTime.ToUniversalTime (), endTime.ToUniversalTime ()); }
private static bool isTableExisting(string strTblName){ try{ var docsFolder = System.Environment.GetFolderPath (System.Environment.SpecialFolder.MyDocuments); var pathToDatabase = System.IO.Path.Combine (docsFolder, "db_sqlnet.db"); var connection = new SQLiteConnection(pathToDatabase); var count = connection.ExecuteScalar<int>("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='" + strTblName + "'"); if(count > 0){ return true; }else{ return false; } }catch(SQLiteException){ return false; } }