Category / Section
How to load data from SQLite offline database into SfCalendar for Xamarin.Android
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
