Student Info In Xamarin.Forms Application Using SQLite For Android And UWP

Student Information is used for day-to-day activities in the educational environment. Reading this article, you can learn how to perform local database operation in Xamarin Forms application using SQLite for Android and Universal Windows Platform with XAML and Visual C# in cross-platform application development.

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private.

 

The following important tools are required for developing UWP,

1. Windows 10 (Recommended)
2. Visual Studio 2017 Community (https://www.visualstudio.com/downloads/ )

Now, we can discuss step by step App development.

Step 1

Open Visual studio 2017 -> Start -> New Project-> Select Cross-Platform (under Visual C#->Cross Platform App-> MobileApp (Xamarin.Forms) -> Give the Suitable Name for your App (XamSqlitestud) ->OK.

 

Step 2

Select the Cross-Platform template as a Blank APP ->Set Platform as Android and UWP and code sharing strategy as .NET standard, Afterwards, Visual Studio creates 3 projects (Portable, Droid, UWP) and displays Getting Started.XamarinPage.

 

Step 3

Add the SQLite Reference, Right Click the Solution(XamWeather)-> Select Manage NuGet Packages for Solution.

Browse and Install the Reference,

 

Step 4

Add the Student Class for data binding,

 

Add the following Namespace and code in Student.cs,

using SQLite;
public class Student {
    [PrimaryKey, AutoIncrement]
    public int stdid {
        get;
        set;
    }
    [NotNull]
    public string stdname {
        get;
        set;
    }
    public string stdcourse {
        get;
        set;
    }
    public int stdage {
        get;
        set;
    }
}

Step 5

Add the StudDB Class for SQLite operations,

 

Add the following Namespace and code in StudDB.cs,

using System.Threading.Tasks;
using SQLite;
public class StudDB {
    readonly SQLiteAsyncConnection database;
    public StudDB(string dbpath) {
        database = new SQLiteAsyncConnection(dbpath);
        database.CreateTableAsync < Student > ().Wait();
    }
    public Task < List < Student >> GetStudentsAsync() {
        return database.Table < Student > ().ToListAsync();
    }
    public Task < Student > GetStudentAsync(int id) {
        return database.Table < Student > ().Where(i => i.stdid == id).FirstOrDefaultAsync();
    }
    public Task < int > SaveStudentAsync(Student student) {
        if (student.stdid != 0) {
            return database.UpdateAsync(student);
        } else {
            return database.InsertAsync(student);
        }
    }
    public Task < int > DeleteStudentAsync(Student student) {
        return database.DeleteAsync(student);
    }
    public Task < int > EditStudent(Student student) {
        return database.UpdateAsync(student);
    }
}

 

Step 6

Add the IStdLocHelper interface for SQLite operations between projects,

 

Add the following Namespace and code in IStdLocHelper.cs,

public interface IStdLocHelper {
    string GetLocalFilePath(string filename);
}

Step 7

Add the LocalFileHelper class in XamSqlitestud.UWP for implementing IStdLocHelper interface to perform SQLite operations in UWP project,

 

Add the following Namespace and code in LocalFileHelper.cs,

using Windows.Storage;
using XamSqlitestud.UWP;
using System.IO;
using Xamarin.Forms;
[assembly: Dependency(typeof(LocalFileHelper))]
namespace XamSqlitestud.UWP {
    public class LocalFileHelper: IStdLocHelper {
        public string GetLocalFilePath(string filename) {
            string docFolder = ApplicationData.Current.LocalFolder.Path;
            string libFolder = Path.Combine(docFolder, "Databases");
            if (!Directory.Exists(libFolder)) {
                Directory.CreateDirectory(libFolder);
            }
            return Path.Combine(libFolder, filename);
        }
    }
}

Step 8

Add the LocalFileHelper class in XamSqlitestud.Droid for implementing IStdLocHelper interface to perform SQLite operations in UWP project,

 

Add the following Namespace and code in LocalFileHelper.cs,

using System.IO;
using Xamarin.Forms;
using XamSqlitestud.Droid;
[assembly: Dependency(typeof(LocalFileHelper))]
namespace XamSqlitestud.Droid {
    public class LocalFileHelper: IStdLocHelper {
        public string GetLocalFilePath(string filename) {
            string docFolder = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
            string libFolder = Path.Combine(docFolder, "..", "Library", "Databases");
            if (!Directory.Exists(libFolder)) {
                Directory.CreateDirectory(libFolder);
            }
            return Path.Combine(libFolder, filename);
        }
    }
}

Step 9

Add the following code in App.xaml.cs for dependency service,

static StudDB database;
public static StudDB Database {
    get {
        if (database == null) {
            database = new StudDB(DependencyService.Get < IStdLocHelper > ().GetLocalFilePath("Student1.db3"));
        }
        return database;
    }
}

Step 10

For User View, in MainPage.Xaml Page , add the Listview with data template for student list,

<ContentPage.Content>
    <ListView x:Name="StudListview" ItemSelected="Student_Itemselected">
        <ListView.ItemTemplate>
            <DataTemplate>
                <TextCell Text="{Binding stdname}" Detail="{Binding stdcourse}" /> </DataTemplate>
        </ListView.ItemTemplate>
    </ListView>
</ContentPage.Content>

Step 11

Add new Contentpage- AddingStudent for adding new student,

 

Add the following controls code for new student,

<ContentPage.Content>
    <StackLayout Padding="10" HorizontalOptions="FillAndExpand" VerticalOptions="FillAndExpand">
        <Grid>
            <Label Text="Name" Grid.Row="0" Grid.Column="0" HorizontalOptions="Start" WidthRequest="100" VerticalOptions="Center" />
            <Entry Text="{Binding stdname}" Grid.Row="0" Grid.Column="1" HorizontalOptions="Start" WidthRequest="100" VerticalOptions="Center" />
            <Label Text="Course" Grid.Row="1" Grid.Column="0" HorizontalOptions="Start" WidthRequest="100" VerticalOptions="Center" />
            <Entry Text="{Binding stdcourse}" Grid.Row="1" Grid.Column="1" HorizontalOptions="Start" WidthRequest="100" VerticalOptions="Center" />
            <Label Text="Age" Grid.Row="2" Grid.Column="0" HorizontalOptions="Start" WidthRequest="100" VerticalOptions="Center" />
            <Entry Text="{Binding stdage}" Grid.Row="2" Grid.Column="1" HorizontalOptions="Start" WidthRequest="100" VerticalOptions="Center" /> </Grid>
        <Button Text="Save" HorizontalOptions="FillAndExpand" BackgroundColor="Blue" TextColor="White" Clicked="Save_Clicked" />
        <Button Text="Cancel" HorizontalOptions="FillAndExpand" BackgroundColor="Blue" TextColor="White" Clicked="Cancel_Clicked" /> </StackLayout>
</ContentPage.Content>

Step 12

Add new Contentpage - EditingStudent for Editing (update or delete)a student,

 

Add the following controls code for Editing (update or delete)a student ,

<StackLayout Padding="10" HorizontalOptions="FillAndExpand" VerticalOptions="FillAndExpand">
    <Grid>
        <Label Text="Name" Grid.Row="0" Grid.Column="0" HorizontalOptions="Start" WidthRequest="100" VerticalOptions="Center" />
        <Entry Text="{Binding stdname}" Grid.Row="0" Grid.Column="1" HorizontalOptions="Start" WidthRequest="100" VerticalOptions="Center" />
        <Label Text="Course" Grid.Row="1" Grid.Column="0" HorizontalOptions="Start" WidthRequest="100" VerticalOptions="Center" />
        <Entry Text="{Binding stdcourse}" Grid.Row="1" Grid.Column="1" HorizontalOptions="Start" WidthRequest="100" VerticalOptions="Center" />
        <Label Text="Age" Grid.Row="2" Grid.Column="0" HorizontalOptions="Start" WidthRequest="100" VerticalOptions="Center" />
        <Entry Text="{Binding stdage}" Grid.Row="2" Grid.Column="1" HorizontalOptions="Start" WidthRequest="100" VerticalOptions="Center" /> </Grid>
    <Button Text="Save" HorizontalOptions="FillAndExpand" BackgroundColor="Blue" TextColor="White" Clicked="Save_Clicked" />
    <Button Text="Delete" HorizontalOptions="FillAndExpand" BackgroundColor="Blue" TextColor="White" Clicked="OnDeleteClicked" />
</StackLayout>

Step 13

Add the following code in MainPage.Xaml.cs for adding new student using Toolbar Item + Click,

public MainPage() {
    InitializeComponent();
    this.Title = "Student List";
    var toolbarItem = new ToolbarItem {
        Text = "+"
    };
    toolbarItem.Clicked += async (sender, e) => {
        await Navigation.PushAsync(new AddStudent() {
            BindingContext = new Student()
        });
    };
    ToolbarItems.Add(toolbarItem);
}
protected async override void OnAppearing() {
    base.OnAppearing();
    StudListview.ItemsSource = await App.Database.GetStudentsAsync();
}
async void Student_Itemselected(object sender, SelectedItemChangedEventArgs e) {
    if (e.SelectedItem != null) {
        await Navigation.PushAsync(new EditStudent() {
            BindingContext = e.SelectedItem as Student
        });
    }
}
}

Step 14

Add the following code in Addstudent.Xaml.cs for Save and Cancel Buttons,

async void Save_Clicked(object sender, System.EventArgs e) {
    var personItem = (Student) BindingContext;
    await App.Database.SaveStudentAsync(personItem);
    await Navigation.PopAsync();
}
async void Cancel_Clicked(object sender, System.EventArgs e) {
    await Navigation.PopAsync();
}

Step 15

Add the following code in EditStudent.Xaml.cs for Save and Delete Buttons,

async void Save_Clicked(object sender, System.EventArgs e) {
    var personItem = (Student) BindingContext;
    await App.Database.SaveStudentAsync(personItem);
    await Navigation.PopAsync();
}
public async void OnDeleteClicked(object sender, System.EventArgs e) {
    bool accepted = await DisplayAlert("Confirm", "Are you Sure ?", "Yes", "No");
    if (accepted) {
        var personItem = (Student) BindingContext;
        await App.Database.DeleteStudentAsync(personItem);
        await Navigation.PopAsync();
    }
    await Navigation.PushAsync(new AddStudent());
}
}

Step 16

Deploy your App to Android Emulator and Local Machine (UWP) and the output of the XamSqlitestud App is,

 

 

After Press the + Toolbar button for Adding a new student in UWP and Android,

 

 

After adding new student detail in UWP and Android,

 

 

Select specific student for Edit in UWP and Android,

 

 

Editing the student Course in UWP and Android,

 

 

After Editing a student Course in UWP and Android,

 

To perform delete a student in UWP and Android,

 

 

After Deleting a student in UWP and Android,

 

 

Summary

Now, you have successfully tested student info using SQLite in Xamarin Forms application for Cross-Platform Application Development using Visual C# and Xamarin.