We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

How to connect the SfSchedule with SQLite?

Platform: WinRT |
Control: SfSchedule
Tags: sfschedule

SfSchedule is connected with the database in order to store the appointments. To achieve this, you need to install the SQLite’s in your project. This article explains the storing of SfSchedule appointments in SQLite’s database.

Adding SQLite in project

To store Schedule appointments in SQL Server Compact, SQLite’s characteristics are used.  It’s an embedded database that runs in-process with your app instead of running as a separate process like a server-based database. It stores data in a single disk file, supports transactions, and doesn’t require any type of configuration to get started.

The following are three ways to install SQLite,

  1.  Please follow the link as follows,

Link: https://visualstudiogallery.msdn.microsoft.com/1d04f82f-2fe9-4727-a2f9-a2db127ddc9a

When the download process is completed, install the sqlite-winrt81-3080301.vsix.This launches the VSIX Installer as shown in the following screenshot. Click Install.

http://robtiffany.com/wp-content/uploads/2012/10/4.png

 

  1. It is installed within Visual Studio by selecting Tools | Extensions and Updates.  Expand Online and then search for SQLite.  The same SQLite for Windows Runtime shows up the ability to download and install from within the tool as shown in the following screenshot.  You can be asked to restart Visual Studio after doing this.

Extensions and Updates

  1. SQLite is installed within Visual Studio by selecting Project| Manage NuGet Packages or right-click on References and select Manage NuGet Packages. In the Manage NuGet Packages dialog, expand Online and then search for SQLite.  Your search results include wrappers, but you need to steer in the direction of libraries that supports x86, x64, and ARM without depending on ADO.NET.  Select the LINQ-friendly sqlite-net library created by Frank Krueger, click Install and then close the dialog to add it to your solution. Once the installation is done, SQLiteAsync.cs and SQLite.cs are created in your project.

Temp - Manage NuGet Packages

After the SQLite for Window Runtime is installed, launch Visual Studio 2013 on your Windows 8.1computer and create new Windows Store app.  In the Solution Explorer, right-click on References, and select Add Reference.  In the Reference Manager, expand Windows and select Extensions on the left side of the dialog.  Check both Microsoft Visual C++ Runtime Package and SQLite for Windows Runtime and click OK as shown as follows.

Reference Manager - Sqlite_WinRT

In order to create an app using SQLite, you cannot use any CPU compilation.  Therefore, from the Debug combo box, select Configuration Manager.  For both Debug and Release Active solution configurations, you can select either x86x64, or ARM for your Active solution platform. You must switch to Release before submitting your app to the store.

Configuration Manager

 

To Connect SfSchedule with SQLite Database

Create WinRT application and add SfSchedule control as follows.

XAML

   <Schedule:SfSchedule x:Name="schedule1"  CurrentDateBackground="#63A028"  HeaderBackground="#FF25A0DA" Margin="0,0,0,51"   >
            <Schedule:SfSchedule.AppointmentMapping>
                <Schedule:ScheduleAppointmentMapping StartTimeMapping="StartTime"  EndTimeMapping="EndTime" SubjectMapping="Subject" />
            </Schedule:SfSchedule.AppointmentMapping>
        </Schedule:SfSchedule>

 

The Custom class’s (ScheduleAppointment) properties created in application are mapped to ScheduleAppointmentMapping class of SfSchedule control.

C#

  public class ScheduleAppointment
    {
        [SQLite.PrimaryKey]
        public DateTime StartTime { get; set; }
        public DateTime EndTime { get; set; }
        public string Subject { get; set; }
    }

 

Collection of this Custom class are assigned to Schedule’s ItemsSource property, by listening to the VisibleDates property of the schedule.

C#

    public ObservableCollection<DateTime> CurrentVisibleDate
        {
            get { return _currentVisibleDate; }
            set 
            {
                _currentVisibleDate = value;
                var collection = viewModel.GetAppointments(_currentVisibleDate.First(), _currentVisibleDate.Last());                
                collection.CollectionChanged += MainPage_CollectionChanged;
                this.schedule1.ItemsSource = collection;
            }
        }

 

 

Note:

In the above code example, appointments are filtered based on the current visible date and assigned to the schedule, in order to avoid handling of large collection of data to improve performance.

 

VisibleDates property of the Schedule control is binded with the local property, to listen and handle appointment based on the visible date change in schedule.

Refer the following code example.

C#

          Binding visibleDatesBind = new Binding();
            visibleDatesBind.Source = this;
            visibleDatesBind.Path = new PropertyPath("CurrentVisibleDate");
            visibleDatesBind.Mode = BindingMode.TwoWay;
            BindingOperations.SetBinding(schedule1, SfSchedule.VisibleDatesProperty, visibleDatesBind);

 

In order to store the schedule appointments in SQLite database, you can create table when it does not exist, to store the appointments of SfSchedule in App.Xaml.cs. Use SQLiteConnection class to connect ScheduleAppointments with SQLite database.

Refer the following code example to create the table in SQLite Database.

C#

protected override void OnLaunched(LaunchActivatedEventArgs args)
        {
            Frame rootFrame = Window.Current.Content as Frame;
            // Get a reference to the SQLite database
            this.DBPath = Path.Combine(
                Windows.Storage.ApplicationData.Current.LocalFolder.Path, "schedule.sqlite");
            // Initialize the database if necessary
            using (var db = new SQLite.SQLiteConnection(this.DBPath))
            {
                // Create the tables if they don't exist
                db.CreateTable<ScheduleAppointment>();
            }
            // Place the frame in the current Window
            Window.Current.Content = rootFrame;
            // Ensure the current window is active
            Window.Current.Activate();
            using (var db = new SQLite.SQLiteConnection(this.DBPath))
            {
                // Create the tables if they don't exist
                db.CreateTable<ScheduleAppointment>();
            }
            ResetData();
  
            // Do not repeat app initialization when the Window already has content,
            // just ensure that the window is active
            if (rootFrame == null)
            {
                // Create a Frame to act as the navigation context and navigate to the first page
                rootFrame = new Frame();
 
                if (args.PreviousExecutionState == ApplicationExecutionState.Terminated)
                {
                    //TODO: Load state from previously suspended application
                }
 
                // Place the frame in the current Window
                Window.Current.Content = rootFrame;
            }
 
            if (rootFrame.Content == null)
            {
                // When the navigation stack isn't restored navigate to the first page,
                // configuring the new page by passing required information as a navigation
                // parameter
                if (!rootFrame.Navigate(typeof(MainPage), args.Arguments))
                {
                    throw new Exception("Failed to create initial page");
                }
            }
        }
 

You can insert the appointments after the table is created. Similarly you can update, and delete the appointments of Schedule that is stored in database.

Refer the following code example to insert the ScheduleAppointments in SQLite database.

C#

private void ResetData()
        {
            using (var db = new SQLite.SQLiteConnection(this.DBPath))
            {
                // Empty the tables
                db.DeleteAll<ScheduleAppointment>();
 
                // Adding ScheduleAppointment
                db.Insert(new ScheduleAppointment()
                {
                    StartTime = DateTime.Now.Date.AddMonths(-3),
                    EndTime = DateTime.Now.Date.AddMonths(-3).AddHours(3),
                    Subject = "Medical Check up"
                });
                db.Insert(new ScheduleAppointment()
                {
                    StartTime = DateTime.Now.Date.AddMonths(-2),
                    EndTime = DateTime.Now.Date.AddMonths(-2).AddHours(3),
                    Subject = "marriage"
                });
                db.Insert(new ScheduleAppointment()
                {
                    StartTime = DateTime.Now.Date.AddMonths(-1),
                    EndTime = DateTime.Now.Date.AddMonths(-1).AddHours(3),
                    Subject = "shopping"
                });
                db.Insert(new ScheduleAppointment()
                {
                    StartTime = DateTime.Now.AddHours(1),
                    EndTime =  DateTime.Now.AddHours(2),
                    Subject = "Client Meeting"
                });
                db.Insert(new ScheduleAppointment()
                {
                    StartTime = DateTime.Now.AddHours(2),
                    EndTime = DateTime.Now.AddHours(2),
                    Subject = "Presentation"
                });
                db.Insert(new ScheduleAppointment()
                {
                    StartTime = DateTime.Now.Date.AddMonths(1),
                    EndTime = DateTime.Now.Date.AddMonths(1),
                    Subject = "Restart Server"
                });
                db.Insert(new ScheduleAppointment()
                {
                    StartTime = DateTime.Now.Date.AddMonths(2),
                    EndTime = DateTime.Now.Date.AddMonths(2).AddHours(3),
                    Subject = "Project meeting"
                });
                db.Insert(new ScheduleAppointment()
                {
                    StartTime = DateTime.Now.Date.AddMonths(3),
                    EndTime = DateTime.Now.Date.AddMonths(3).AddHours(3),
                    Subject = "Documentation"
                });
                db.Insert(new ScheduleAppointment()
                {
                    StartTime = DateTime.Now.Date.AddMonths(4),
                    EndTime = DateTime.Now.Date.AddMonths(4).AddHours(3),
                    Subject = "Product Release"
                });
            }
        }

 

The inserted appointments can be retrieved by using queries.

Refer the following code example to retrieve the ScheduleAppointments property.

C#

    private NewSQLite_Schedule_WinRT.App app = (Application.Current as App);
        public ObservableCollection<ScheduleAppointment> GetAppointments(DateTime StartTime, DateTime EndTime )
        {
            Appointments = new ObservableCollection<ScheduleAppointment>();
            using (var db = new SQLite.SQLiteConnection(app.DBPath))
            {
                var query = db.Table<ScheduleAppointment>();
                               foreach (var _appnt in query.Where(item=> item.StartTime >= StartTime && item.StartTime <=EndTime))
                {
                    var appoint = new ScheduleAppointment()
                    {
                        StartTime = _appnt.StartTime,
                        EndTime = _appnt.EndTime,
                        Subject = _appnt.Subject
                    };
                    Appointments.Add(appoint);
                }
            }
            return Appointments;
        }
 

 

Sample Link

Please find the following sample to illustrate data base connection of SfSchedule.

NewSQLite_Schedule_WinRT.zip

As a result, the stored ScheduleAppointments is retrieved from the SQLite Date base as follows.

Schedule Appointments

ADD COMMENT
You must log in to leave a comment

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Up arrow icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile