Preview only show first 10 pages with watermark. For full document please download

Sqlite With Xamarin Forms Step By Step Guide

Descripción: Sql Lite y XAMARIN

   EMBED

Share

Transcript

12,255,491 members members 58,031 online online articles Q&A forums Sign in lounge Search for articles, questions, tips SQLite SQ Lite with Xamarin Forms Forms Step by Step guide S Ravi‐Kumar‐2012, 1 May 2016 CPOL Rate this: 4.11 2 votes In many many mobile applications it’s a requirement to requirement to store data locally and for that purpose we use databases with the applications. appli cations. The type of database [...] In many many mobile applications it’s a requirement to store data store data locally and for that purpose we use databases with the applications. appli cations. The type of database to use mainly depends depends upon the requirement of the application, but in most of MIS Management Information Systems based application relational databases are used for this purpose. The most commonly used relational database with Xamarin Forms is SQLite SQLite.. It is the best suited relational database for mobile applications as it has very small footprint. This article will be an step by step guide on how to use a SQLite database with a Xamarin Forms application.We will be using the database to persi persist st employee data. The structure of the database will be like following Diagram: Step‐1: create a new project in Xamarin/Visual Step‐1: Xamarin/Visual Studio see this article for steps . Name it as ”. There There is no no out of box tool available in Xamarin Forms, So we will be using a third party plugin for this purpose and the most popular plugin is ‘SQLite.Net‐ PCL’ created by oysteinkrog. Step‐2: In order to use ‘SQLite.Net‐PCL’ plugin, add the nuget package of the plugin to each project of the solution. In Visual Studio this can be done by right click on the solution and selecting ‘Manage NuGet Packages for Solution‘ option. Which will give following screen: In case of Xamarin Studio, you will have to individually add the NuGet package by right clicking on the ‘Packages’  folder and selecting ‘Add Packages’  option on each project. Even tough the ‘SQLite.Net‐PCL‘ package will provide us the functionality of manipulating the SQLite database, it can’t automatically initialize the database connection object as the location of the database file varies on different platforms. So in order to solve this issue we will use dependency service to load the database file in connection object. Step‐3: Create a blank interface with one method signature like following code Hide Copy Code using  SQLite.Net; namespace  SQLiteEx { public interface  ISQLite { SQLiteConnection GetConnection(); } } Step‐4: Create Class file named ‘SQLiteService‘ implementing ‘ISQLite‘ interface and Write the implementation of this ‘GetConnection‘ in every platform specific code like following given codes: For new database: When we are creating a database in the application itself for the first time then following code will be used. Android: Hide Shrink using  System; using  Xamarin.Forms; using  SQLiteEx.Droid; Copy Code using  System.IO; [assembly: Dependency(typeof(SqliteService))] namespace  SQLiteEx.Droid { public class  SqliteService : ISQLite { public  SqliteService() { } #region ISQLite implementation public  SQLite.Net.SQLiteConnection GetConnection() { var  sqliteFilename ="SQLiteEx.db3";  // string  documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); Documents folder var  path = Path.Combine(documentsPath, sqliteFilename); Console.WriteLine(path); if  (!File.Exists(path)) File.Create(path); var  plat = new  SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid(); var  conn = new  SQLite.Net.SQLiteConnection(plat, path);  // Return the database connection return  conn; } #endregion } } iOS: Hide Shrink using  using  using  using  Copy Code SQLiteEx.iOS; System; System.IO; Xamarin.Forms; [assembly: Dependency(typeof(SqliteService))] namespace  SQLiteEx.iOS { public class  SqliteService : ISQLite { public  SqliteService() { } #region ISQLite implementation public  SQLite.Net.SQLiteConnection GetConnection() { var  sqliteFilename ="SQLiteEx.db3";  // string  documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); Documents folder string  libraryPath = Path.Combine(documentsPath,"..", "Library"); // Library folder var  path = Path.Combine(libraryPath, sqliteFilename);  // This is where we copy in the prepopulated database Console.WriteLine(path); if  (!File.Exists(path)) { File.Create(path); } var  plat = new  SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS(); var  conn = new  SQLite.Net.SQLiteConnection(plat, path);  // Return the database connection return  conn; } #endregion } } For Pre‐Created Database: In some application scenarios there may be a requirement that the database is pre‐populated with some data and we are going to use the same in the application. In such scenarios first copy the database file at following mentioned locations in platform specific project and then use following code in ‘SQLiteService’ Class Android: Copy the database file in ‘Resources\Raw’  folder of Android platform project. Hide Shrink using  using  using  using  Copy Code System; Xamarin.Forms; SQLiteEx.Droid; System.IO; [assembly: Dependency(typeof(SqliteService))] namespace  SQLiteEx.Droid { public class  SqliteService : ISQLite { public  SqliteService () {} #region ISQLite implementation public  SQLite.Net.SQLiteConnection GetConnection () { var  sqliteFilename ="SQLiteEx.db3"; string  documentsPath = System.Environment.GetFolderPath (System.Environment.SpecialFolder.Personal); // Documents folder var  path = Path.Combine(documentsPath, sqliteFilename);  // This is where we copy in the prepopulated database Console.WriteLine (path); if  (!File.Exists(path)) { var  s = Forms.Context.Resources.OpenRawResource(Resource.Raw.APGameDb); // RESOURCE NAME ###  // create a write stream FileStream writeStream = new  FileStream(path, FileMode.OpenOrCreate, FileAccess.Write);  // write to the stream ReadWriteStream(s, writeStream); } var  plat = new  SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid(); var  conn = new  SQLite.Net.SQLiteConnection(plat, path);  // Return the database connection return  conn; } #endregion /// <summary> ///  helper method to get the database out of /raw/ and into the user filesystem ///  </summary> void  ReadWriteStream(Stream readStream, Stream writeStream) { int  Length = 256; Byte[] buffer = new Byte[Length]; int  bytesRead = readStream.Read(buffer,0, Length);  // write the required bytes while  (bytesRead >0) { writeStream.Write(buffer, 0, bytesRead); bytesRead = readStream.Read(buffer, 0, Length); } readStream.Close(); writeStream.Close(); } } } iOS: Copy the database file in ‘Resources’  folder of Android platform project. Hide Shrink using  using  using  using  Copy Code SQLiteEx.iOS; System; System.IO; Xamarin.Forms; [assembly: Dependency(typeof(SqliteService))] namespace  SQLiteEx.iOS { public class  SqliteService : ISQLite { public  SqliteService() { } #region ISQLite implementation public  SQLite.Net.SQLiteConnection GetConnection() { var  sqliteFilename ="SQLiteEx.db3";  // string  documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); Documents folder string  libraryPath = Path.Combine(documentsPath,"..", "Library"); // Library folder var  path = Path.Combine(libraryPath, sqliteFilename);  // This is where we copy in the prepopulated database Console.WriteLine(path); if  (!File.Exists(path)) { File.Copy(sqliteFilename, path); } var  plat = new  SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS(); var  conn = new  SQLite.Net.SQLiteConnection(plat, path);  // Return the database connection return  conn; } #endregion } } Step‐5: Create a class in PCL project named as ‘DataAccess‘. This class will contain all the data access codes for the application. The code of the class is as follows: Hide Shrink Copy Code using  SQLite.Net; using  Xamarin.Forms; namespace  SQLiteEx { public class  DataAccess { SQLiteConnection dbConn; public  DataAccess() { dbConn = DependencyService.Get<ISQLite>().GetConnection();  // create the table(s) dbConn.CreateTable<Employee>(); } public  List GetAllEmployees() { return  dbConn.Query( "Select * From [Employee]"); } public int  SaveEmployee(Employee aEmployee) { return  dbConn.Insert(aEmployee); } public int  DeleteEmployee(Employee aEmployee) { return  dbConn.Delete(aEmployee); } public int  EditEmployee(Employee aEmployee) { return  dbConn.Update(aEmployee); } } } As it can be seen from the code above that the application is using dependency service to fetch the database and create the connection object in the constructor of the class which will be used in order to manipulate the data. Secondly the code to create tables are there in constructor also, this we will have to give even if we are using a pre‐created database as the method automatically checks for the table in database and create it if not present. Step‐6: Create the Plain Old CLR Object POCO classes for the table s present in the database. For Example ‘Employee‘ class for the above given tables in ‘DataAccess‘ class. Employee: Hide Copy Code using  SQLite.Net.Attributes; using  System; namespace  SQLiteEx { public class  Employee { [PrimaryKey, AutoIncrement] public long  EmpId { get; set; } [NotNull] public string  EmpName { get; set; } public string  Designation { get; set; } public string  Department { get; set; } public string  Qualification { get; set; } } } Step‐7: Create the static object property of ‘DataAccess‘ in  ‘App‘ class like following code. This will enable us to use manipulate the data from any screen of the application. Hide Shrink Copy Code using  Xamarin.Forms; using  Xamarin.Forms.Xaml; [assembly: XamlCompilation(XamlCompilationOptions.Compile)] namespace  SQLiteEx { public class  App : Application { static  DataAccess dbUtils; public  App() {  // The root page of your application MainPage = new  NavigationPage(new  ManageEmployee()); } public static  DataAccess DAUtil { get { if  (dbUtils == null) { dbUtils = new  DataAccess(); } return  dbUtils; } } protected override void  OnStart() {  // Handle when your app starts } protected override void  OnSleep() {  // Handle when your app sleeps } protected override void  OnResume() {  // Handle when your app resumes } } } This completes the basic structure of application using a SQLite database to persist data of the application . Now let’s create the screens of our sample application to see the CRUD operation code.The Application will contain following screens: 1. Manage Employees 2. Add Employee 3. Show Employee Details 4. Edit Employee Manage Employees: This screen is the home screen of the application, it will show the current list of Employees and give an option to add new employee, view the details of the Employee and manage departments. The XAML code of this page will be like : Hide Shrink Copy Code