Articles in this section
Category / Section

How to load the data from SQLite offline database into SfSchedule?

1 min read

SfSchedule allows you to bind the data from local database by using SQLite. To achieve this, you need to follow the below steps.

Step 1: Add the database in to the cross-platform projects Resource folder.

Step 2: Create connection with the database using interface in cross platform projects and create interface in PCL project.

C#

public interface ISQLite
    {
        SQLiteConnection GetConnection();
    }

 

Step 3: Create a class which inherits from the ISQLite interface in cross-platform project and get connection with the offline database using below code.

In Android cross-platform project

C#

  public class SQLite_Android : ISQLite
    {
        public SQLite_Android()
        {
        }
 
 
        public SQLite.SQLiteConnection GetConnection()
        {
            var sqliteFilename = "SampleSQLites.db3";
            string documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal); // Documents folder
            var path = Path.Combine(documentsPath, sqliteFilename);
 
            // This is where we copy in the prepopulated database
            Console.WriteLine(path);
            if (!File.Exists(path))
            {
                var s = Forms.Context.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;
        }
 
        /// <summary>
        /// helper method to get the database out of /raw/ and into the user filesystem
        /// </summary>
        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();
        }
    }

 

In iOS cross-platform project

C#

public class SQLite_iOS : ISQLite
      {
            public SQLite_iOS ()
            {
            }
 
            public SQLite.SQLiteConnection GetConnection ()
            {
                  var sqliteFilename = "SampleSQLites.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.Copy (sqliteFilename, path);
                  }
 
                  var conn = new SQLite.SQLiteConnection(path);
 
                  // Return the database connection 
                  return conn;
            }
      }

 

In UWP cross-platform project

C#

class SQLite_UWP : ISQLite
    {
        public SQLite_UWP()
        {
 
        }
 
 
        public SQLiteConnection GetConnection()
        {
            var databasename = "SampleSQLites.db3";
            var path = Path.Combine(ApplicationData.Current.LocalFolder.Path, databasename);
            var conn = new SQLite.SQLiteConnection(path);
            // Return the database connection 
            return conn;
        }
    }

 

Step 4: Create data model for SQLite.

C#

 
  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 DateTime type when adding items to the local collection from SQLite data model, since SQLite does not support DateTime type.

 

Step 5: Create SQLite data base table, and populate the data into the table.

C#

SQLiteConnection database;
              
        public ViewModel()
        {
            //Create connection
            database = DependencyService.Get<ISQLite>().GetConnection();
 
            //Create table
            database.CreateTable<AppointmentDB>();
 
            var currentDate = DateTime.Now.Date;
            database.Query<AppointmentDB>("DELETE From AppointmentDB");
 
            //Insert data in to table 
            database.Query<AppointmentDB>("INSERT INTO AppointmentDB (Subject,StartTime,EndTime,AllDay,Color)values ('Yoga Therapy','" + currentDate.AddHours(1).ToString() + "', '" + currentDate.AddHours(2).ToString() + "','false','#ff0000')");
            database.Query<AppointmentDB>("INSERT INTO AppointmentDB (Subject,StartTime,EndTime,AllDay,Color)values ('Client Meeting','" + currentDate.ToString() + "', '" + currentDate.AddHours(1).ToString() + "','true','#0000ff')");
            database.Query<AppointmentDB>("INSERT INTO AppointmentDB (Subject,StartTime,EndTime,AllDay,Color)values ('Client Meeting','" + currentDate.AddHours(5).ToString() + "', '" + currentDate.AddHours(7).ToString() + "','false','#ffa500')");
            AddAppointments();
        }
 

 

Step 7: Create another class other than SQLite data modal class for appointment mapping, since SQLite data modal class does not support certain data types.

C#

    public class Meeting
    {
        public string EventName { get; set; }
        public DateTime From { get; set; }
        public DateTime To { get; set; }
        public bool AllDay { get; set; }
        public Color Color { get; set; }
    }

 

Step 8: Store the SQLite data into the collection (Here, Meetings).

C#

        public ObservableCollection<Meeting> Meetings { get; set; }
 
        /// <summary>
        /// Creates meetings and stores in a collection.  
        /// </summary>
        private void AddAppointments()
        {
            var table = (from i in database.Table<AppointmentDB>() select i);
            Meetings = new ObservableCollection<Meeting>();
            foreach (var order in table)
            {
                var EventName = order.Subject;
                var From = DateTime.Parse(order.StartTime);
                var To = DateTime.Parse(order.EndTime);
                var AllDay = Convert.ToBoolean(order.AllDay);
 
                Meetings.Add(new Meeting()
                {
                    EventName = order.Subject,
                    From = DateTime.Parse(order.StartTime),
                    To = DateTime.Parse(order.EndTime),
                    Color = Color.FromHex(order.Color),
                    AllDay = Convert.ToBoolean(order.AllDay),
                });
            }
     }
 

 

 

Step 9: Bind it to SfSchedule using SfSchedule.DataSource property.

XAML

  <syncfusion:SfSchedule x:Name="schedule"  
                               DataSource = "{Binding Meetings}"
                               ScheduleView = "WeekView">
            <syncfusion:SfSchedule.AppointmentMapping>
                <syncfusion:ScheduleAppointmentMapping 
                    IsAllDayMapping="AllDay"
                    ColorMapping="Color"
                    EndTimeMapping="To"
                    StartTimeMapping="From"
                    SubjectMapping="EventName"/>
            </syncfusion:SfSchedule.AppointmentMapping>
            <syncfusion:SfSchedule.BindingContext>
                <local:ViewModel/>
            </syncfusion:SfSchedule.BindingContext>
        </syncfusion:SfSchedule>

 

 

Step 10: Refer the following link to know how to create SQLite connection,
http://developer.xamarin.com/guides/xamarin-forms/working-with/databases/

Note: To run this sample in UWP, install sqlite.net.pcl, version v1.0.10 (Only this version of SQLite supports UWP platform, later versions don’t support UWP).

Please refer the below UG link for more details about creating custom appointment mapping.

 

https://help.syncfusion.com/xamarin/sfschedule/getting-started#adding-custom-appointments

 

 

Sample Link: SQLiteSample

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please  to leave a comment
Access denied
Access denied