Customizing Xamarin Editor

Another post about editor. In my previous post. I’ve shared about how you can add a placeholder to Editor using Behavior. In this post we’ll go little bit further. We still have an editor with placeholder, but when user start typing on it, the placeholder will turn in to it’s title. So it will make our editor little bit more interactive to user. Let’s start with creating our custom editor.

Custom Editor

I don’t make any update about this class. This class is still exactly same like the same class in my older post. It has 2 additional properties, Placeholder and Placeholder Color. But Placeholder Color will have more rules in this editor, because we will use same color for placeholder and title. Here’s the unchanged code of the Custom Editor class.

public class CustomEditor : Editor
{
    public static BindableProperty PlaceholderProperty =
        BindableProperty.Create(nameof(Placeholder), typeof(string), typeof(CustomEditor), string.Empty, BindingMode.TwoWay);

    public string Placeholder
    {
        get { return (string)GetValue(PlaceholderProperty); }
        set { SetValue(PlaceholderProperty, value); }
    }

    public static BindableProperty PlaceholderColorProperty =
        BindableProperty.Create(nameof(PlaceholderColor), typeof(string), typeof(CustomEditor), "#cccccc", BindingMode.TwoWay);

    public string PlaceholderColor
    {
        get { return (string)GetValue(PlaceholderColorProperty); }
        set { SetValue(PlaceholderColorProperty, value); }
    }
}

Custom Content View

Now let’s handle the user interaction. We won’t be using behavior anymore in this post, because you can’t generate title just by using it. Instead, we’ll be making a custom content view. This is gonna look similar like when I created Entry with Title, but it will be little bit trickier, because unlike entry, editor doesn’t have placeholder by default. Let’s take a look at the xaml file.

TitledEditorCode

As you can see, we create similar xaml file like the entry. We have a label above custom editor to become the title. We also move all user interaction from behavior into this file. We handle The BindingContextChangedFocused, Unfocused and TextChanged event. With those four event handler, we’ll manage when the placeholder or the title is displayed. Let’s see the code behind it.

public partial class TitledEditor : ContentView
{
    public TitledEditor()
    {
        InitializeComponent();

        EditorContent.BindingContext = this;
        LabelTitle.BindingContext = this;
    }

    public static BindableProperty PlaceholderProperty =
        BindableProperty.Create(nameof(Placeholder), typeof(string), typeof(TitledEditor), string.Empty, BindingMode.TwoWay);

    public string Placeholder
    {
        get { return (string)GetValue(PlaceholderProperty); }
        set { SetValue(PlaceholderProperty, value); }
    }

    public static BindableProperty PlaceholderColorProperty =
        BindableProperty.Create(nameof(PlaceholderColor), typeof(string), typeof(TitledEditor), "#cccccc", BindingMode.TwoWay);

    public string PlaceholderColor
    {
        get { return (string)GetValue(PlaceholderColorProperty); }
        set { SetValue(PlaceholderColorProperty, value); }
    }

    public static BindableProperty TextProperty =
        BindableProperty.Create(nameof(Text), typeof(string), typeof(TitledEditor), string.Empty, BindingMode.TwoWay);

    public string Text
    {
        get { return (string)GetValue(TextProperty); }
        set { SetValue(TextProperty, value); }
    }

    public static BindableProperty FontSizeProperty =
        BindableProperty.Create(nameof(FontSize), typeof(int), typeof(TitledEditor), 16, BindingMode.TwoWay);

    public int FontSize
    {
        get => (int)GetValue(FontSizeProperty);
        set => SetValue(FontSizeProperty, value);
    }

    public static BindableProperty TextColorProperty =
        BindableProperty.Create(nameof(TextColor), typeof(Color), typeof(TitledEditor), Color.Default, BindingMode.TwoWay);

    public Color TextColor
    {
        get { return (Color)GetValue(TextColorProperty); }
        set { SetValue(TextColorProperty, value); }
    }

    void Handle_ContainerFocused(object sender, FocusEventArgs e)
    {
        EditorContent.Focus();
    }

    void Handle_BindingContextChanged(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Text))
        {
            Text = Placeholder;
            LabelTitle.IsVisible = false;
            TextColor = Color.FromHex(PlaceholderColor);
        }
        else
        {
            LabelTitle.IsVisible = true;
            TextColor = Color.Default;
        }
    }

    void Handle_Focused(object sender, FocusEventArgs e)
    {
        LabelTitle.IsVisible = true;

        if (Placeholder == Text)
        {
            Text = string.Empty;
        }
    }

    void Handle_Unfocused(object sender, FocusEventArgs e)
    {
        if (string.IsNullOrEmpty(Text))
        {
            Text = Placeholder;
            LabelTitle.IsVisible = false;
            TextColor = Color.FromHex(PlaceholderColor);
        }
    }

    void Handle_TextChanged(object sender, TextChangedEventArgs e)
    {
        if (string.IsNullOrEmpty(Text))
        {
            LabelTitle.IsVisible = true;
            TextColor = Color.FromHex(PlaceholderColor);
        }
        else
        {
            if (Text != Placeholder)
            {
                LabelTitle.IsVisible = true;
                EditorContent.TextColor = Color.Default;
            }
            else
            {
                EditorContent.TextColor = Color.FromHex(PlaceholderColor);
            }
        }
    }
}

Let me walk you through it. First, when the binding context changed, it means when in initialized, it will check whether the editor have text on it or not. If it doesn’t have one, the placeholder will become the text with the color of placeholder color. We won’t have any title on it. But if it has text on it, the text color will be default and we will show the title with color of placeholder color. Basically, we do the similar checking to the other three events. The placeholder and title just keep switching place depend on the text of the editor and both of them have same color.

Titled Editor in action

 

It’s all it takes to make Editor with placeholder and title. And if you try to use the editor in one of you page, it will look like this.

Sample Code is available in my Github repo

Advertisements

Adjusting font Size of Xamarin’s Picker (Part 2)

I already made 2 post about picker, but in this post I want to be more specific about picker in iOS especially about it’s text size. I already told how to do it in Android, now it’s iOS’s turn.  First thing first, now let’s review the custom picker class.

Custom Picker Class

This class actually is almost the same with class in my older post. The only thing I did is changing the name of FontSize property into TextSize. This is because I got warning saying that Font Size is hidden property that Picker already had. I didn’t get this warning when first time I created this class, so I made little adjustment here.

public class CustomPicker : Picker
{
    public static readonly BindableProperty TextSizeProperty =
        BindableProperty.Create(nameof(TextSize), typeof(float), typeof(CustomPicker), 24, BindingMode.TwoWay);

    public float TextSize
    {
        set { SetValue(TextSizeProperty, value); }
        get { return (Int32)GetValue(TextSizeProperty); }
    }

    public static BindableProperty PlaceholderColorProperty =
        BindableProperty.Create(nameof(PlaceholderColor), typeof(string), typeof(CustomPicker), DefaultColor, BindingMode.TwoWay);

    public string PlaceholderColor
    {
        get { return (string)GetValue(PlaceholderColorProperty); }
        set { SetValue(PlaceholderColorProperty, value); }
    }

    public static string DefaultColor => "#CCCCCC";
}

Picker iOS Renderer

Now let’s move on to the picker renderer in iOS project. This class also similar with a class with same name in my old post, when I discussed about placeholder color. The only difference is I added some lines of code to adjust it’s text size. And it’s actually a little bit tricky to do that.

To change the size of picker’s text in iOS, we need to create something called FontDescriptor. Ok, I’m not expert on swift programming, and maybe there’s better way to create FontDescriptor, but I did it by taking the descriptor of UILabel’s font. After I got the description, I made new Font descriptor from it with adjusted size property. Then I create new UIFont with that new description.

Lastly, I put my new font to NSAttributedString so I can update the AttributedPlaceholder and AttributedText property of the iOS picker. I did it so the placeholder and the text had same set of attributes.

public class CustomPickerRenderer: PickerRenderer
{
    protected override void OnElementChanged(ElementChangedEventArgs e)
    {
        base.OnElementChanged(e);
        if (e.NewElement != null)
        {
            var customPicker = e.NewElement as CustomPicker;

            // get Bindable properties
            UIColor placeholderColor = GetUIColor(customPicker.PlaceholderColor);
            float textSize = (float)customPicker.TextSize;

            // create font decsriptor
            var label = new UILabel();
            var fontDescriptor = label.Font.FontDescriptor;

            // adjusting font size
            var newDescriptor = fontDescriptor.CreateWithAttributes(new UIFontAttributes()
            {
                Size = textSize
            });
            UIFont font = UIFont.FromDescriptor(newDescriptor, 0);

            // set attributes
            var placeholderAttributes = new NSAttributedString(customPicker.Title, new UIStringAttributes()
            { ForegroundColor = placeholderColor, Font = font });

            Control.AttributedPlaceholder = placeholderAttributes;

            var textAttributes = new NSAttributedString(customPicker.Title, new UIStringAttributes()
            { ForegroundColor = placeholderColor, Font = font });

            Control.AttributedText = textAttributes;<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>
        }
    }

    private UIColor GetUIColor(string color)
    {
        return UIColor.FromRGB(GetRed(color), GetGreen(color), GetBlue(color));
    }

    private float GetRed(string color)
    {
        Color c = Color.FromHex(color);
        return (float)c.R;
    }

    private float GetGreen(string color)
    {
        Color c = Color.FromHex(color);
        return (float)c.G;
    }

    private float GetBlue(string color)
    {
        Color c = Color.FromHex(color);
        return (float)c.B;
    }
}

 

Sample Code is available in my Github repo

Github Announcement

Some of you guys have asked me to put sample code on Git. So, today is the day, I’ve created a Github account and and put some of the sample code in a repository there. You can access my repo in this link. Until I created this post, I already added 2 sample code, but I will add more code soon in the future. Here’s the sneak peak of the sample code.

 

Create your own Animated Bottom Picker

OK guys, I’m still playing around in animation in Xamarin, so in this post I want to try out two other animation functions, which are TranslateTo and FadeTo. In order to do that, I created my own picker that comes up from the screen’s bottom , just like picker you would get if you used Swift or React Native. The picker itself would not really looks similar like the picker in that platforms but it would kinda works like them. My goal in this post will be just exploring different kind of animation in Xamarin. In my last post, I already used RotateTo for accordion list view which was awesome and I’m exited to dig deeper about this stuff. So, let’s begin.

Absolute Layout

First thing first, we need to create a layout that enable us to put our “picker” outside the screen when the app started because the picker will be showing up from the bottom of the screen. So, I used Absolute Layout to put some elements off the screen. If you are not familiar with Absolute Layout, basically anything you put inside this layout can have 2 important properties. First, LayoutBonds which takes 4 parameters, X position, Y position, Width and Height. Second, LayoutFlags which is the setting of the LayoutBound, whether you want to use pixel or screen proportion for those 4 parameters. You can read the complete explanation in the documentation, in this post I will only discuss what I did with my absolute layout. So, let’s take a look at the code first.

BottomPickerCode

So, I put everything inside absolute layout, but I only used absolute layout’s properties in two elements. First, in a BoxView, which I used to the background of the picker. I set layout flag to All, means the layout bound will be the screen’s proportion. With the layout bound 0,0,1,1; I put the background in upper left corner (0,0) and I set it as big as the screen (1,1). I’ll do the same for the Frame, which will be the picker and contain a list view. I just set different proportion to make it bellow the screen and has half of the screen size. For user interaction, I only used familiar function like entry’s Focused, list view’s ItemSelected and also GestureRecognizer for the box view. Now, let’s move to code behind and add some animations into our screen.

The Animation

I created two methods, Show_Picker and Hide_Picker to execute the animation that will be accessed from our event handlers. Here’s the code.

public partial class PickerButtomPage : ContentPage
{
    public ObservableCollection CountryList;

    public PickerButtomPage()
    {
        InitializeComponent();
        CountryList = new ObservableCollection()
        {
            new Country("Indonesia"),
            new Country("India"),
            new Country("United States"),
            new Country("Brazil"),
            new Country("United Kingdom")
        };
        PickerContry.ItemsSource = CountryList;
    }

    void Handle_ItemTapped(object sender, ItemTappedEventArgs e)
    {
        ((ListView)sender).SelectedItem = null;
    }

    void Handle_ItemSelected(object sender, SelectedItemChangedEventArgs e)
    {
        if (e.SelectedItem == null)
            return;

        Country country = e.SelectedItem as Country;
        EntryCountry.Text = country.Name;

        Hide_Picker();
    }

    void Handle_Tapped(object sender, EventArgs e)
    {
        Hide_Picker();
    }

    void Handle_Focused(object sender, FocusEventArgs e)
    {
        Show_Picker();
    }

    void Show_Picker()
    {
        Background.FadeTo(0.4, 500, Easing.SinIn);
        PickerFrame.TranslateTo(0, -300, 500);
    }
void Hide_Picker()
    {
        Background.FadeTo(0, 500, Easing.SinOut);
        PickerFrame.TranslateTo(0, 300, 500);
        EntryHide.Focus();
    }
}

public class Country
{
    public Country(string name){ Name = name; }
    public string Name { get; set; }
}

So in Show_Picker, I faded in the background (box view) into opacity 0.4 in 0.5 second. Meanwhile I also moved the Frame Picker 300 pixels up, also in 0.5 second. I did the other way around for the Hide_Picker and then added one line to remove focus from the Entry.

Basically, this how my picker works. I trigger the picker to show up when the country entry is focused . Then when user choose one country or hide the picker by clicking the background, I set the focus to another entry I hide in the screen to prevent the user to type manually to the entry. And if it works as expected, it will look like this.

Adding Animation to Accordion ListView

So, I just started learning about animation in Xamarin. I read the documentation and want to try implementing it. Because I’m new at this stuff, I wanna start with something simple. I modified one of my old post about accordion list view by adding little animation to it. If you’ve read that post, you will know that when I expand the list view, I changed the down arrow icon to up arrow icon. And when I collapse the list view, I do the otherwise. The icon changing process just happen as it is, without any effect or animation on it. I want to change that. I want to add a rotate animation when the icon changing happen. So, let’s do that.

Image Gesture Recognizer

First thing we need to is switching the trigger of expanding list view event. Back then I used List View Item Tapped event, but now, because I want to rotate the arrow icon, I need to put the trigger in Image view where the arrow icon is. So, I add Image Gesture Recognizer to the Image view. It’s look like this.

animated accordion

With this gesture recognizer, I can get the current Image view being tapped by user so I can handle it properly in code behind. In code behind, all I need to is just rotate the image and call function in view model to expand or collapse the list view. I rotate the image 180 degree and for 0,5 second.

public partial class AccordionCountriesPage : ContentPage
{
    AccordionCountriesViewModel viewModel;
    public AccordionCountriesPage()
    {
        InitializeComponent();
        BindingContext = viewModel = new AccordionCountriesViewModel();
    }

    void Handle_ItemTapped(object sender, ItemTappedEventArgs e)
    {
        //Country mCountry = (Country)e.Item;
        //viewModel.ShowCities(mCountry);
        ListView listView = sender as ListView;
        listView.SelectedItem = null;
    }

    async void Handle_Tapped(object sender, EventArgs e)
    {
        Image image = sender as Image;
        await image.RotateTo(180, 500);
        Grid grid = image.Parent as Grid;
        Label label = grid.Children[0] as Label;
        viewModel.ShowCities(label.Text);
    }
}

A little bit downside from this method is I don’t get the whole Country class like I did when I was using List View Item Tapped. But I’m still able to know which country user tapped by accessing the label which contain the country’s name. By doing this, it means I also need little modification to the view model as well. This is how the view model look like now. I only updated the ShowCities method.

public class AccordionCountriesViewModel : BaseViewModel
{
    private CustomObservableCollection countries;
    public CustomObservableCollection Countries
    {
        get => countries;
        set => SetProperty(ref countries, value);
    }

    public AccordionCountriesViewModel()
    {
        ObservableCollection USACities = new ObservableCollection()
        {
            new City(){ CityName = "Washington DC" },
            new City(){ CityName = "New York" },
            new City(){ CityName = "Los Angeles" }
        };

        ObservableCollection ChinaCities = new ObservableCollection()
        {
            new City(){ CityName = "Beijing" },
            new City(){ CityName = "Shanghai" },
            new City(){ CityName = "Shenzhen" }
        };

        ObservableCollection RussiaCities = new ObservableCollection()
        {
            new City(){ CityName = "Moscow" },
            new City(){ CityName = "St. Peterburg" },
            new City(){ CityName = "Kazan" }
        };

        countries = new CustomObservableCollection()
        {
            new Country(){ CountryName = "USA", IsChildrenVisible = false, Cities = USACities },
            new Country(){ CountryName = "China", IsChildrenVisible = false, Cities = ChinaCities },
            new Country(){ CountryName = "Russia", IsChildrenVisible = false, Cities = RussiaCities },
        };
    }

    public void ShowCities(string countryName)
    {
        Country country = Countries.SingleOrDefault(c => c.CountryName == countryName);
        country.IsChildrenVisible = !country.IsChildrenVisible;
        Countries.ReportItemChange(country);
    }
}

Let’s rotate it!

Nah, now we can run the project again and see how our arrow rotating.

 

Credit :

  • Arrow Icons from FlatIcon, Down Arrow icon by Freepik, Right Arrow icon by GraphicsBay, Up Arrow icon by Hadrien

Binding DateTime and TimeSpan to one property

As we know together, it take different properties to record what user input into DatePicker and TimePicker. DatePicker requires DateTime property, and yet TimePicker needs TimeSpan. And let say we have some kind of application form to submit bunch of data, and one property require the user to input complete set of time, from year to hour but we only have one DateTime property to bind into. Of course we can just add TimeSpan to DateTime and we gonna have complete set of DateTime from our DatePicker and TimePicker. But, what if we got bunch of DateTime property to input, we gonna adding TimeSpan to DateTime multiple times, which is ineffective. So, I create a simple custom control to simplify this problem. Let’s take a look.

XAML File

First, we need to create new content view with XAML on it. In XAML file we gonna need three 2 visible items, the DatePicker and TimePicker, and one invisible item, a DatePicker. The function of two first properties is obvious, to enable user input. But the last invisible DateTime will serve special function. I will explain later after we reach the code behind section, for now let see the code for XAML file

TitledDateTimePickerCode2

As you can see, I have one invisible DatePicker that bind to property called SelectedDateTime, we will use this property to bind whatever Date and Time that user input. One thing you need to take note is how I handle property changed in both visible DatePicker and TimePicker. Whenever user change the date or time, it will also change the our main property, SelectedDateTime.

Another thing you may notice is, I’m using custom Custom Date and Time Picker. It’s actually just custom picker with adjustable Font Size. If you curious, you can take a look at my previous post about adjusting font size of Xamarin Form’s Picker. But of course you also can use the original DatePicker and TimePicker from Xamarin Forms. It will works just fine, you just can’t bind the font size.

Code Behind

Now let’s move to the code behind that XAML file. This file actually is just full of properties you wanna bind to this custom control. You can add more properties that fits your needs, like FontColor maybe, or any other properties. But I wanna focus to how I handle property changes in this file. Like I said before, I use DateSelected and PropertyChanged event handler in DatePicker and TimePicker respectively to update SelectedDateTime. But this method has one weakness. If the SelectedDateTime already has value when its bind, the visible DatePicker and TimePicker won’t show it’s value. Before we go any further, let’s take a look at the code.

public partial class TitledDateTimePicker : ContentView
    {
        public TitledDateTimePicker()
        {
            InitializeComponent();
            LabelTitle.BindingContext = this;
            DatePickerContent.BindingContext = this;
            TimePickerContent.BindingContext = this;
        }

        public static BindableProperty SelectedDateTimeProperty =
            BindableProperty.Create(nameof(SelectedDateTime), typeof(DateTime?), typeof(TitledDateTimePicker), DateTime.Now, BindingMode.TwoWay);

        public DateTime? SelectedDateTime
        {
            get => (DateTime?)GetValue(SelectedDateTimeProperty);
            set => SetValue(SelectedDateTimeProperty, value);
        }

        public static BindableProperty TitleProperty =
            BindableProperty.Create(nameof(Title), typeof(string), typeof(TitledDateTimePicker), null, BindingMode.TwoWay);

        public string Title
        {
            get => (string)GetValue(TitleProperty);
            set => SetValue(TitleProperty, value);
        }

        public static BindableProperty DatetimeProperty =
            BindableProperty.Create(nameof(Datetime), typeof(DateTime), typeof(TitledDateTimePicker), DateTime.Today, BindingMode.TwoWay);

        public DateTime Datetime
        {
            get => (DateTime)GetValue(DatetimeProperty);
            set => SetValue(DatetimeProperty, value);
        }

        public static BindableProperty TimespanProperty =
            BindableProperty.Create(nameof(Timespan), typeof(TimeSpan), typeof(TitledDateTimePicker), DateTime.Today.TimeOfDay, BindingMode.TwoWay);

        public TimeSpan Timespan
        {
            get => (TimeSpan)GetValue(TimespanProperty);
            set => SetValue(TimespanProperty, value);
        }

        public static BindableProperty FontSizeProperty =
            BindableProperty.Create(nameof(FontSize), typeof(int), typeof(TitledDateTimePicker), 12, BindingMode.TwoWay);

        public int FontSize
        {
            get => (int)GetValue(FontSizeProperty);
            set => SetValue(FontSizeProperty, value);
        }

        public static BindableProperty IsEditableProperty =
            BindableProperty.Create(nameof(IsEditable), typeof(bool), typeof(TitledEntry), true, BindingMode.TwoWay);

        public bool IsEditable
        {
            get => (bool)GetValue(IsEditableProperty);
            set => SetValue(IsEditableProperty, value);
        }

        void Handle_DateSelected(object sender, DateChangedEventArgs e)
        {
            UpdateSelectedDatime();
        }

        void Handle_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            if (e.PropertyName == "Time")
            {
                UpdateSelectedDatime();
            }
        }

        void Handle_BindingContextChanged(object sender, EventArgs e)
        {
            if (SelectedDateTime.HasValue)
                SetDateTime();
        }

        void UpdateSelectedDatime()
        {
            SelectedDateTime = Datetime.Add(Timespan);
        }

        void SetDateTime()
        {
            Datetime = SelectedDateTime.Value;
            Timespan = SelectedDateTime.Value.TimeOfDay;
        }
    }

As you can see at code above, whenever DateSelected and PropertyChanged fired, means user input date or time, it will update SelectedDateTime. In other hand, BindingContextChanged of my invisible DatePicker do the completely opposites. It will update the DateTime and TimeSpan of the visible DatePicker and TimePicker respectively.

So, when user navigate to a page which use this custom control, and he want to update a DateTime field that has beed set before hand, he will find fields of DatePicker and TimePicker that already set to proper values, because when he navigate to that page, the BindingContextChanged will be triggered. If we don’t do this, the DatePicker will set to default value, which is January 1st, 1990. This why we need that invisible DatePicker, because we need to put that BindingContextChanged event handler somewhere in the code.

How to use it?

Nah, we already have packed up everything we need about Date and Time Selecting in one simple custom control. So, all we need to do is calling it from any pages that need it. This is a simple sample how to do it.

TitledDateTimePickerCode1

 

Sample Code is available in my Github repo

 

SQLite and ORM in Xamarin Forms

It’s about a week ago, I was looking for a complete tutorial about how to implement ORM in SQLite in Xamarin Forms. I did find out that there’s already a library called SQLiteNetExtensions that handle this kind of stuff. It’s also has complete documentation with it. Unfortunately, the documentation is not up to date. I was having difficulty to use that library at first, so I look up for other tutorials on the net. But I couldn’t find one that suit my need, a complete and up to date tutorial. This is actually pretty simple thing, but if you don’t know how to do it properly, it will cost you hours of your life before you can make it works. So, this is how I’ve done it.

Note : Nuget packages you need are SQLite.Net-PCL and SQLiteNetExtenstions

Database Strictures

First thing first. Because we’re talking about ORM, let’s have a simple data structure that represent all kind relationships in ORM; OneToMany, ManyToOne, ManyToMany and OneToOne.

public class Department
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }

    public string DepartmentName { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List Employees { get; set; }
}

public class Employee
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }

    public string IDCardNumber { get; set; }
    public string EmployeeName { get; set; }

    [ForeignKey(typeof(Department))]
    public int DepartmentID { get; set; }

    [ManyToOne(CascadeOperations = CascadeOperation.All)]
    public Department Deparment { get; set; }

    [ForeignKey(typeof(EmployeeFamily))]
    public int FamilyID { get; set; }

    [OneToOne(CascadeOperations = CascadeOperation.All)]
    public EmployeeFamily Family { get; set; }

    [ManyToMany(typeof(EmployeeBenefit))]
    public List Benefits { get; set; }
}

public class EmployeeFamily
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }

    public string SpouseName { get; set; }
    public string FirstChildName { get; set; }
    public string SecondChildName { get; set; }

    [ForeignKey(typeof(Employee))]
    public int EmployeeID { get; set; }

    [OneToOne(CascadeOperations = CascadeOperation.All)]
    public Employee Employee { get; set; }
}

public class EmployeeBenefit
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }

    public string BenefitName { get; set; }
    public double BenefitValue { get; set; }

    [ManyToMany(typeof(Employee))]
    public List Employees { get; set; }
}

One thing to take a note is make sure you’re using SQLite and SqLiteExtensions.Attributes instead of SQL.Net.Attributes or it’s not gonna works.

Dependency Services

To implement sqlite to Android and iOS, of course we gonna need dependency service classes. But first let create the interface in our PCL project.

public interface ISQLite
{
    SQLite.SQLiteConnection GetSQLiteConnection();
}

Then we move on to Android project. This is how the dependency service class looks like.

public class SQLiteAndroid : ISQLite
{
    public SQLite.SQLiteConnection GetSQLiteConnection()
    {
        var fileName = "testing.db3";
        var documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
        var path = Path.Combine(documentsPath, fileName);

        var platform = new SQLitePlatformAndroid();
        var connection = new SQLite.SQLiteConnection(path, true);

        return connection;
    }
}

Lastly, we also implement the dependency class to iOS project.

public class SQLiteiOS : ISQLite
{
    public SQLite.Net.SQLiteConnection GetConnection()
    {
        var sqliteFilename = "testing.db3";
        string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
        string libraryPath = Path.Combine(documentsPath, "..", "Library");
        var path = Path.Combine(libraryPath, sqliteFilename);
        var platform = new SQLitePlatformIOS();
        var conn = new SQLite.Net.SQLiteConnection(platform, path);
        return conn;
    }
}

Data Context

Next, let’s create a class that will handle all things SQLite related. So when we need to do some operations in SQLite, all we need to do just calling the method in this class. Below this is the sample code of the class, but I don’t include all operations possible, just some important operations for example.

public class EmployeeDataContext
{
    private static EmployeeDataContext instance;

    public static EmployeeDataContext Instance
    {
        get
        {
            if (instance == null)
            {
                instance = new EmployeeDataContext();
            }
            return instance;
        }
    }

    private SQLite.SQLiteConnection connection;

    private EmployeeDataContext()
    {
        connection = DependencyService.Get().GetSQLiteConnection();
        CreateTableIfNotExist();
    }

    private void CreateTableIfNotExist()
    {

        bool isTableNotExist = false;

        try
        {
            var test = connection.Table().FirstOrDefault();
        }
        catch
        {
            isTableNotExist = true;
        }
if (isTableNotExist)
        {
            connection.CreateTable();
        }

    }

    public List GetAllDepartment()
    {
        return ReadOperations.GetAllWithChildren(connection, recursive: true).ToList();
    }

    public void RefreshDepartment(IList listDepartment)
    {
       WriteOperations.DeleteAll(connection, GetAllDepartment(), true);
       WriteOperations.InsertOrReplaceAllWithChildren(connection, listDepartment, true);
    }

    public void DeleteAllDepartment()
    {
        WriteOperations.DeleteAll(connection, GetAllDepartment(), true);
    }

    public void UpdateDepartment(Department department)
    {
        WriteOperations.UpdateWithChildren(connection, department);
    }

    public void UpdateEmployee(Employee employee)
    {
        WriteOperations.UpdateWithChildren(connection, employee);
    }
}

How to use it

Now, everything is ready, let’s see how to use it. You call the methods from view model or directly from code behind.


List listDepartment = new List();
Department department = new Department();
Employee employee = new Employee();

EmployeeDataContext.Instance.GetAllDepartment();
EmployeeDataContext.Instance.RefreshDepartment(listDepartment);
EmployeeDataContext.Instance.UpdateDepartment(department);
EmployeeDataContext.Instance.UpdateEmployee(employee);