Bài giảng Lập trình cho thiết bị di động Windows Phone - Trần Duy Thanh - Data Storage

eb Resources WebClient and HttpRequest objects retrieve XML or JSON data from remote locations on the web, RSS feeds, and web services. WebClient is the simpler of the two to use and is handy for one time access to web services or for downloading remote resources. HttpRequest is slightly more complex but designed for greater control over content type, headers, cookies and credentials. 37 7. Consuming Web Resources WebClient Use the WebClient method DownloadStringAsync() to bring down a string of text or OpenReadAsync() to retrieve a stream of binary data. In both cases, set up a handler for the completion of the operation and call the appropriate asynchronous method. Before we can call either method, we need to set up the objects and methods that will parse and store the data. In the example that follows, we will pull data from the Flickr API. Login : www.flickr.com Get API Key:  38 7. Consuming Web Resources WebClient Login : www.flickr.com Get API Key:  39 7. Consuming Web Resources WebClient MainPage.Xaml 40 7. Consuming Web Resources WebClient private const string BaseUrl = ""; private const string QueryStrings = "?method={0}&api_key={1}&user_id={2}&format=json&nojsoncallback=1"; private const string FlickrMethod = "flickr.people.getPublicPhotos"; private const string YourApiKey = "dcd6a32705ef1615f2a652247bf5df95"; private const string LibraryOfCongressKey = "8623220@N02"; private string FlickrPhotosUrl = BaseUrl + String.Format(QueryStrings, FlickrMethod, YourApiKey, LibraryOfCongressKey); 41 7. Consuming Web Resources WebClient private void UseWebClient() { var uri = new Uri(FlickrPhotosUrl); var client = new WebClient(); client.DownloadStringCompleted += (sender, e) => { var photos = GetFlickrPhotos(e.Result); Dispatcher.BeginInvoke(() => { FlickrListBox.DataContext = photos; }); }; client.DownloadStringAsync(uri); } Call UseWebClient() Method in OnNavigatedTo 42 7. Consuming Web Resources private static List GetFlickrPhotos(string json) { const string baseUrl = "{0}.staticflickr.com/{1}/{2}_{3}_s.jpg"; List FlickrPhotos = null; var serializer = new DataContractJsonSerializer(typeof(RootObject)); using (var stream = new MemoryStream(Encoding.UTF8.GetBytes(json))) { var root = serializer.ReadObject(stream) as RootObject; if (root.photos != null) { FlickrPhotos = (from photo in root.photos.photo select new FlickrPhoto { Title = photo.title, Uri = new Uri(String.Format(baseUrl, photo.farm, photo.server, photo.id, photo.secret)) }).ToList(); } } return FlickrPhotos; } 43 7. Consuming Web Resources public class Photo { public string id { get; set; } public string owner { get; set; } public string secret { get; set; } public string server { get; set; } public int farm { get; set; } public string title { get; set; } public int ispublic { get; set; } public int isfriend { get; set; } public int isfamily { get; set; } } public class Photos { public int page { get; set; } public int pages { get; set; } public int perpage { get; set; } public string total { get; set; } public List photo { get; set; } } 44 7. Consuming Web Resources public class RootObject { public Photos photos { get; set; } public string stat { get; set; } } public class FlickrPhoto { public string Title { get; set; } public Uri Uri { get; set; } } 45 7. Consuming Web Resources HttpWebRequest private void Button_Click(object sender, RoutedEventArgs e) { System.Uri targetUri = new System.Uri(TextBlockTargetUri.Text); HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create 	(targetUri); request.BeginGetResponse(new AsyncCallback(ReadWebRequestCallback), 	request); } 46 7. Consuming Web Resources private void ReadWebRequestCallback(IAsyncResult callbackResult) { HttpWebRequest myRequest = (HttpWebRequest)callbackResult.AsyncState; HttpWebResponse myResponse = (HttpWebResponse)myRequest.EndGetResponse(callbackResult); using (StreamReader httpwebStreamReader = new 	StreamReader(myResponse.GetResponseStream())) { string results = httpwebStreamReader.ReadToEnd(); //TextBlockResults.Text = results; //-- on another thread! Dispatcher.BeginInvoke(() => TextBlockResults.Text = results); } myResponse.Close(); } 47 8. Using a Local Database 8.2 LINQ-to-SQL 8.3 SQLite 48 8.1 LINQ-to-SQL The Windows Phone 7.1 SDK added support for local databases in Windows Phone Store apps by using the LINQ-to-SQL API and the SQL Server Compact Edition (SQLCE) database engine. The primary advantage of a database is that it makes it possible for you to perform complex queries on potentially large data sets without requiring that you load all that data into memory 49 8.1 LINQ-to-SQL LINQ-to-SQL is an object-relational mapping (ORM) API based on the Language-Integrated Query (LINQ) framework. It makes it possible for apps to perform queries and create, read, update, and delete (CRUD) operations on strongly typed managed objects, just as it would on data stored in memory, while taking advantage of the underlying database engine to load only the data that’s required. 50 8.1 LINQ-to-SQL 51 8.1 LINQ-to-SQL 52 8.1 LINQ-to-SQL 53 8.2 SQLite Windows Phone 8 adds support for the popular open-source database engine, SQLite. SQLite offers several key advantages over LINQ-to-SQL and SQLCE, such as the following: SQLite is accessible from native code. It is supported on most modern operating systems, allowing for code reuse across platforms. It provides higher performance for certain types of operations. 54 8.2 SQLite Acquiring SQLite for Windows Phone On the Tools menu, choose Extension And Updates to open the Extensions And Updates dialog box. Use the search box in the upper-right corner of the dialog box to search for “SQLite” 1 55 8.2 SQLite After you’ve installed that, the native sqlite3 dlls are installed into a folder under C:\Program Files (x86)\Microsoft SDKs\Windows Phone\v8.0\ExtensionSDKs\SQLite.WP80\. That’s just for your information – you should never have to manually copy the sqlite3.dll from there in order to use the database. This location: It is important to fix some errors 56 8.2 SQLite Inside “C:\Program Files (x86)\Microsoft SDKs\Windows Phone\v8.0\ExtensionSDKs\SQLite.WP80\3.8.3.1” 57 8.2 SQLite Download SQLiteWinRTPhone: https://sqlwinrt.codeplex.com/SourceControl/latest 2 58 8.2 SQLite Make the same version 3.8.3.1 Inside SQLiteWinRTPhone 3 59 8.2 SQLite 4 Include Existing Project 60 8.2 SQLite 5 Add Reference Project 61 8.2 SQLite Create Database & Table Insert one row Select one row Select Multi rows Update row Delete Row Demo (Take yourself GUI) 62 8.2 SQLite using Windows.Storage; using SQLiteWinRT; using System.Threading; using System.Threading.Tasks; using System.Diagnostics; static Database db; static ManualResetEvent DBLoaded = new ManualResetEvent(false); public static Task GetDatabaseAsync() { return Task.Run(() => { DBLoaded.WaitOne(); return db; }); } MainPage coding 63 8.2 SQLite private async void LoadDatabase() { // Get a reference to the SQLite database db = new Database 	(ApplicationData.Current.LocalFolder, "books.db"); await db.OpenAsync(); string sql = @"CREATE TABLE IF NOT EXISTS Sach (Ma VARCHAR(10), Ten NVARCHAR( 150 ), Trang INTEGER );"; string description = "Create Sach table"; await ExecuteSQLStatement(db, sql, description); DBLoaded.Set(); } Create Database & Table 64 8.2 SQLite private static async Task ExecuteSQLStatement 	(Database db, string sql, string description) { try { await db.ExecuteStatementAsync(sql); Debug.WriteLine(description + " executed OK"); } catch (Exception ex) { 	 } } 65 8.2 SQLite private async void InsertData() { try { // Connection already opened in app.xaml.cs - get reference Database db = await GetDatabaseAsync(); var custstmt = await db.PrepareStatementAsync ("INSERT INTO Sach (Ma, Ten, Trang) VALUES (@Ma, @Ten, @Trang)"); // NOTE that named parameters have a leading "@",":" or "$". custstmt.BindTextParameterWithName("@Ma", txtMaSach.Text); custstmt.BindTextParameterWithName("@Ten", txtTenSach.Text); custstmt.BindIntParameterWithName("@Trang", int.Parse(txtSoTrang.Text)); // Use StepAsync to execute a prepared statement await custstmt.StepAsync(); } catch (System.Runtime.InteropServices.COMException ex) { throw new ApplicationException(ex.Message ); } } Insert one row 66 8.2 SQLite Select one row public async void GetData(string ma) { var db = await GetDatabaseAsync(); var readstmt = await db.PrepareStatementAsync( "SELECT * FROM Sach WHERE Ma = @ma"); readstmt.BindTextParameterWithName("@ma", ma); if (await readstmt.StepAsync() == true) { txtMaSach.Text= readstmt.GetTextAt(0); txtTenSach.Text=readstmt.GetTextAt(1); txtSoTrang.Text=readstmt.GetIntAt(2)+""; } } 67 8.2 SQLite Select Multi rows public async void GetData() { var db = await GetDatabaseAsync(); var readstmt = await db.PrepareStatementAsync 	("SELECT Ma,Ten,Trang FROM Sach"); List dsTen = new List(); while(await readstmt.StepAsync() == true) { string ma = readstmt.GetTextAt(0); string ten = readstmt.GetTextAt(1); int trang = readstmt.GetIntAt(2); dsTen.Add(ma+" - "+ten +" - page = "+trang); } longlistSelector1.ItemsSource = null; longlistSelector1.ItemsSource = dsTen; } 68 8.2 SQLite Update Row public async void UpdateData(string ma) { // See if the customer already exists var custstmt = await db.PrepareStatementAsync 	("UPDATE Sach SET Ten = ?, Trang = ? WHERE Ma=?"); { // NOTE when using anonymous parameters the first has an 	index of 1, not 0. custstmt.BindTextParameterAt(1, txtTenSach.Text); custstmt.BindIntParameterAt(2, int.Parse( txtSoTrang.Text)); custstmt.BindTextParameterAt(3, txtMaSach.Text); await custstmt.StepAsync(); } } 69 8.2 SQLite Delete Row public async void DeleteData(string ma) { string sql = @"DELETE FROM Sach WHERE Ma=@ma"; var custstmt = await db.PrepareStatementAsync(sql); custstmt.BindTextParameterWithName("@ma", ma); await custstmt.StepAsync(); } 70 8.2 SQLite Exercise: Create Database as the same picture below, and then input some data foreach table to test How to Enable foreign key constraints??? END 71