Category / Section
How to load data from SQLite offline database into SfCalendar for Xamarin.Android
3 mins read
SfCalendar allows you to load data from local database using SQLite. Using the following steps, you can load data from local database:
Step 1: Add database into the Resource folder of project.
Step 2: Create connection with the offline database using the following code.
public SQLiteConnection GetConnection()
{
var sqliteFilename = "SampleSQLites.db3";
string documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal); // Documents folder
var path = System.IO.Path.Combine(documentsPath, sqliteFilename);
// This is where we copy in the prepopulated database
Console.WriteLine(path);
if (!File.Exists(path))
{
var s = this.Resources.OpenRawResource(Resource.Raw.SampleSQLites); // RESOURCE NAME ###
// Create a write stream
FileStream writeStream = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write);
// Write to the stream
ReadWriteStream(s, writeStream);
}
var conn = new SQLite.SQLiteConnection(path);
// Return the database connection
return conn;
}
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();
}
Step 3: Create data model for SQLite.
public class AppointmentDB
{
public string Subject { get; set; }
public string StartTime { get; set; }
public string EndTime { get; set; }
public string AllDay { get; set; }
public string Color { get; set; }
}
Note:
Convert String type into Calendar type when adding items to the local collection from SQLite data model, since SQLite does not support Calendar type.
Step 4: Create SQLite data base table and populate data into the table.
private void CreateDatabase()
{
dataBase = this.GetConnection();
dataBase.CreateTable<AppointmentDB>();
var currentDate = (Calendar)Calendar.Instance.Clone();
dataBase.Query<AppointmentDB>("DELETE From AppointmentDB");
var startTime = (Calendar)currentDate.Clone();
startTime.Set(CalendarField.Hour, 1);
var endTime = (Calendar)currentDate.Clone();
endTime.Set(CalendarField.Hour, 2);
var startTime1 = (Calendar)currentDate.Clone();
var endTime1 = (Calendar)currentDate.Clone();
endTime1.Set(CalendarField.Hour, 1);
var startTime2 = (Calendar)currentDate.Clone();
startTime2.Set(CalendarField.Hour, 5);
var endTime2 = (Calendar)currentDate.Clone();
endTime2.Set(CalendarField.Hour, 6);
// Insert data in to table
dataBase.Query<AppointmentDB>("INSERT INTO AppointmentDB (Subject,StartTime,EndTime,AllDay,Color)values ('Yoga Therapy','" + startTime.Time.ToString() + "', '" + endTime.Time.ToString() + "','false','#ff0000')");
dataBase.Query<AppointmentDB>("INSERT INTO AppointmentDB (Subject,StartTime,EndTime,AllDay,Color)values ('Client Meeting','" + startTime1.Time.ToString() + "', '" + endTime1.Time.ToString() + "','true','#0000ff')");
dataBase.Query<AppointmentDB>("INSERT INTO AppointmentDB (Subject,StartTime,EndTime,AllDay,Color)values ('Client Meeting','" + startTime2.Time.ToString() + "', '" + endTime2.Time.ToString() + "','false','#ffa500')");
this.AddAppointments();
}
Step 5: Store the SQLite data into the collection (here, appointments).
private void AddAppointments()
{
var table = (from i in dataBase.Table<AppointmentDB>() select i);
appointments = new CalendarEventCollection();
foreach (var order in table)
{
var EventName = order.Subject;
var From = this.ConvertToCalendar(order.StartTime);
var To = this.ConvertToCalendar(order.EndTime);
var AllDay = Convert.ToBoolean(order.AllDay);
appointments.Add(new CalendarInlineEvent()
{
Subject = order.Subject,
StartTime =this.ConvertToCalendar(order.StartTime),
EndTime = this.ConvertToCalendar(order.EndTime),
Color = Color.ParseColor(order.Color),
IsAllDay = Convert.ToBoolean(order.AllDay),
});
}
}
Step 6: Set the appointments collection into the Calendar.DataSource property.
protected override void OnCreate(Bundle savedInstanceState)
{
base.OnCreate(savedInstanceState);
this.CreateDatabase();
calendar = new SfCalendar(this);
calendar.ShowEventsInline = true;
calendar.DataSource = appointments;
SetContentView(calendar);
}
Step 7: Refer to Using SQLite.NET ORM to know more about creating SQLite connection.
Sample link: CalendarSQLiteAndroid
