How to connect the SfSchedule with SQLite?
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,
- 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.
- 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.
- 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.
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.
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 x86, x64, or ARM for your Active solution platform. You must switch to Release before submitting your app to the store.
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;
}
}
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.
As a result, the stored ScheduleAppointments is retrieved from the SQLite Date base as follows.
