Xamarin : SQLite Local Database



Xamarin : SQLite - Local Database



1. Install "SQLite.Net-PCL" package using nuget package manager. Install in all projects in solution.


2. Create service signature using interface.

using SQLite.Net;

namespace App_LocalDataBase
{
    public interface ISQLite
    {
        SQLiteConnection GetConnection();
    }
}

3. Create service manager to implement Interface method.

For Android
using System;
using Xamarin.Forms;
using LocalDataBase.Droid;
using System.IO;

[assembly: Dependency(typeof(SqliteService))]
namespace App_LocalDataBase.Droid
{
    public class SqliteService : ISQLite
    {
        public SqliteService() { }

        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;
        } 
    }
}

4. Create class in Droid project  implement

using LocalDataBase.iOS;
using System;
using System.IO;
using Xamarin.Forms;

[assembly: Dependency(typeof(SqliteService))]
namespace App_LocalDataBase.iOS
{
    public class SqliteService : ISQLite
    {
        public SqliteService()
        {
        }

        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;
        }
 }
}

5. Write database operation methods in PCL project. Create class name - DataAccess

using SQLite.Net;
using Xamarin.Forms;

namespace App_LocalDataBase
{
    public class DataAccess
    {
        SQLiteConnection dbConn;
        public DataAccess()
        {
            dbConn = DependencyService.Get<ISQLite>().GetConnection();
            // create the table(s)
            dbConn.CreateTable<Employee>();
        }
        public List<Employee> GetAllEmployees()
        {
            return dbConn.Query<Employee>("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);
        }
    }
}

6. Create  table model in PCL project.

using SQLite.Net.Attributes;
using System;

namespace App_LocalDataBase
{
    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; }
    }
}

7. Create the static object property of ‘DataAccess‘ in ‘App‘ class
using Xamarin.Forms;
using Xamarin.Forms.Xaml;

[assembly: XamlCompilation(XamlCompilationOptions.Compile)]
namespace App_LocalDataBase
{
    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
        }
    }
}
 
8. Now its turn to create CRUD operation

  1. Manage Employees (ManageEmployee.xaml)
  2. Add Employee (AddEmployee.xaml)
  3. Show Employee Details ShowEmplyee.xaml
  4. Edit Employee (EditEmployee.xaml)
9. Manage Employee page
Design 
<ContentPage.Padding>
    <OnPlatform x:TypeArguments="Thickness" iOS="0, 20, 0, 0" />
  </ContentPage.Padding>
  <ContentPage.Content>
    <ListView x:Name="lstData" HasUnevenRows="false" Header="Header Value" Footer="Footer"  ItemSelected="OnSelection" >
      <ListView.HeaderTemplate>
        <DataTemplate>
          <StackLayout Orientation="Horizontal" BackgroundColor="Blue" Padding="5,5,5,5">
            <Label Text="Name" FontSize="Medium" FontAttributes="Bold" TextColor="White"/>
            <Label Text="Designation" FontSize="Medium" FontAttributes="Bold" TextColor="White"/>
            <Label Text="Department" FontSize="Medium" FontAttributes="Bold" TextColor="White"/>
          </StackLayout>
        </DataTemplate>
      </ListView.HeaderTemplate>
      <ListView.ItemTemplate>
        <DataTemplate>
          <ViewCell>
            <StackLayout Orientation="Horizontal" Padding="5,5,5,5">
              <Label Text="{Binding EmpName}" FontSize="Medium" />
              <Label Text="{Binding Designation}" FontSize="Medium" />
              <Label Text="{Binding Department}" FontSize="Medium" />
            </StackLayout>
          </ViewCell>
        </DataTemplate>
      </ListView.ItemTemplate>
      <ListView.FooterTemplate>
        <DataTemplate>
          <StackLayout Orientation="Horizontal" Padding="5,5,5,5">
            <Button Text="Add New Employee" Clicked="OnNewClicked" />
          </StackLayout>
        </DataTemplate>
      </ListView.FooterTemplate>
    </ListView>
  </ContentPage.Content>
 
Code

public ManageEmployee()
        {
            InitializeComponent();
            var vList = App.DAUtil.GetAllEmployees();
            lstData.ItemsSource = vList;
        }
 
        void OnSelection(object sender, SelectedItemChangedEventArgs e)
        {
            if (e.SelectedItem == null)
            {
                return;
                //ItemSelected is called on deselection, 
                //which results in SelectedItem being set to null
            }
            var vSelUser = (Employee)e.SelectedItem;
            Navigation.PushAsync(new ShowEmplyee(vSelUser));
        }
        public void OnNewClicked(object sender, EventArgs args)
        {
            Navigation.PushAsync(new AddEmployee());
        }

 10. Add Employee Page
Design  
<ContentView.Content>
    <TableView Intent="Settings" BackgroundColor="White">
      <TableRoot>
        <TableSection Title="Add New Employee">
          <EntryCell x:Name="txtEmpName" Label="Employee Name" Keyboard="Text" />
          <EntryCell x:Name="txtDesign" Label="Designation" Keyboard="Text" />
          <EntryCell x:Name="txtDepartment" Label="Department" Keyboard="Text" />
          <EntryCell x:Name="txtQualification" Label="Qualification" Keyboard="Text" />
          <ViewCell>
            <ContentView Padding="0,0">
              <ContentView.Padding>
                <OnPlatform x:TypeArguments="Thickness" iOS="10,0" WinPhone="0,15,0,0" />
              </ContentView.Padding>
              <ContentView.Content>
                <Button BackgroundColor="#fd6d6d" Text="Save" TextColor="White" Clicked="OnSaveClicked" />
              </ContentView.Content>
            </ContentView>
          </ViewCell>
        </TableSection>
      </TableRoot>
    </TableView>
  </ContentView.Content>
 

Code 
public AddEmployee()
        {
            InitializeComponent();
        }
 
        public void OnSaveClicked(object sender, EventArgs args)
        {
            var vEmployee = new Employee()
            {
                EmpName = txtEmpName.Text,
                Department = txtDepartment.Text,
                Designation = txtDesign.Text,
                Qualification = txtQualification.Text
            };
            App.DAUtil.SaveEmployee(vEmployee);
            Navigation.PushAsync(new ManageEmployee());
        }

11. View Employee Page
Design 

<ContentView.Content>
    <Grid>
      <Grid.RowDefinitions>
        <RowDefinition Height="*"/>
        <RowDefinition Height="*"/>
        <RowDefinition Height="*"/>
        <RowDefinition Height="*"/>
        <RowDefinition Height="*"/>
        <RowDefinition Height="*"/>
        <RowDefinition Height="*"/>
      </Grid.RowDefinitions>
 
      <Grid.ColumnDefinitions>
        <ColumnDefinition Width="10"/>
        <ColumnDefinition Width="*"/>
        <ColumnDefinition Width="*"/>
        <ColumnDefinition Width="10"/>
      </Grid.ColumnDefinitions>
 
      <Label Grid.Row ="0" Grid.Column="0" Grid.ColumnSpan="2" Text="Employee Details" />
      <Label Grid.Row ="1" Grid.Column="1" Text="Name" />
      <Label Grid.Row="1" Grid.Column="2" Text ="{Binding EmpName}" />
      <Label Grid.Row ="2" Grid.Column="1" Text="Designation" />
      <Label Grid.Row="2" Grid.Column="2" Text ="{Binding Designation}"/>
      <Label Grid.Row ="3" Grid.Column="1" Text="Department" />
      <Label Grid.Row="3" Grid.Column="2" Text ="{Binding Department}"/>
      <Label Grid.Row ="4" Grid.Column="1" Text="Qualification" />
      <Label Grid.Row="4" Grid.Column="2" Text ="{Binding Qualification}" />
      <Button Grid.Row ="5" Grid.Column="1" Text="Edit Details" Clicked="OnEditClicked" />
      <Button Grid.Row="5" Grid.Column="2" Text="Delete" Clicked="OnDeleteClicked" />
    </Grid>
  </ContentView.Content>
 
 Code 
Employee mSelEmployee;
        public ShowEmplyee(Employee aSelectedEmp)
        {
            InitializeComponent();
            mSelEmployee = aSelectedEmp;
            BindingContext = mSelEmployee;
        }
 
        public void OnEditClicked(object sender, EventArgs args)
        {
           // Navigation.PushAsync(new EditEmployee(mSelEmployee));
        }
        public async void OnDeleteClicked(object sender, EventArgs args)
        {
            bool accepted = await DisplayAlert("Confirm", "Are you Sure ?", "Yes", "No");
            if (accepted)
            {
                App.DAUtil.DeleteEmployee(mSelEmployee);
            }
            await Navigation.PushAsync(new ManageEmployee());
        }



12. Edit Employee Page

Design 
 <ContentView.Content>
    <TableView Intent="Settings" BackgroundColor="White">
      <TableRoot>
        <TableSection Title="Edit Employee">
          <EntryCell x:Name="txtEmpName" Label="Employee Name" Text ="{Binding EmpName}" Keyboard="Text" />
          <EntryCell x:Name="txtDesign" Label="Designation" Text ="{Binding Designation}" Keyboard="Text" />
          <EntryCell x:Name="txtDepartment" Label="Department" Text ="{Binding Department}" Keyboard="Text" />
          <EntryCell x:Name="txtQualification" Label="Qualification" Text ="{Binding Qualification}" Keyboard="Text" />
          <ViewCell>
            <ContentView Padding="0,0">
              <ContentView.Padding>
                <OnPlatform x:TypeArguments="Thickness" iOS="10,0" WinPhone="0,15,0,0" />
              </ContentView.Padding>
              <ContentView.Content>
                <Button BackgroundColor="#fd6d6d" Text="Save" TextColor="White" Clicked="OnSaveClicked" />
              </ContentView.Content>
            </ContentView>
          </ViewCell>
        </TableSection>
      </TableRoot>
    </TableView>
  </ContentView.Content>


Code 

Employee mSelEmployee;
       public EditEmployee(Employee aSelectedEmp)
       {
           InitializeComponent();
           mSelEmployee = aSelectedEmp;
           BindingContext = mSelEmployee;
       }
 
       public void OnSaveClicked(object sender, EventArgs args)
       {
           mSelEmployee.EmpName = txtEmpName.Text;
           mSelEmployee.Department = txtDepartment.Text;
           mSelEmployee.Designation = txtDesign.Text;
           mSelEmployee.Qualification = txtQualification.Text;
           App.DAUtil.EditEmployee(mSelEmployee);
           Navigation.PushAsync(new ManageEmployee());
       }




at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)