Category / Section
How to perform the CRUD operations like insert, update, and delete with the SQL Database?
7 mins read
The newly created appointments in the Schedule control can be saved to the SQL database. It is also possible to update the appointment details in the SQL database while editing or deleting the appointments in the Schedule control by making use of the server-side events.
Refer to the following steps to perform CRUD operations like Add, Edit, and Delete on the Schedule appointments in the SQL database.
Step 1: Create the ASP.NET application and include the EJWEB Schedule control in it by referring to the link.
Step 2: Create the database table with the necessary fields as follows.
[SQL Table Script]
CREATE TABLE [dbo].[ScheduleAppointments] ( [Id] INT NOT NULL, [Subject] NVARCHAR (50) NULL, [Description] NVARCHAR (50) NULL, [StartTime] DATETIME NULL, [EndTime] DATETIME NULL, [AllDay] BIT NULL, [Recurrence] BIT NULL, [RecurrenceRule] NVARCHAR(100) NULL );
Note:
You can create the table in Visual Studio Itself as a local database or use SQL Server Management Studio
Step 3: Connect the database to the Schedule control by using the SqlDataSource ASP.NET control as shown.
[Default.aspx]
<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server"> <ej:Schedule ID="Schedule1" runat="server" DataSourceID="SqlDataSource1"> </ej:Schedule> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [ScheduleAppointments]"></asp:SqlDataSource> </asp:Content>
Step 4: Add the connectionString configuration to the Web.Config page as highlighted in the following code.
[Web.Config]
<configuration> ------------------------------------------- ------------------------------------------- <connectionStrings> <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-ScheduleCRUDCS-20150607072453.mdf;Initial Catalog=aspnet-ScheduleCRUDCS-20150607072453;Integrated Security=True" providerName="System.Data.SqlClient" /> <add name="ConnectionString" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\ScheduleData.mdf;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings> ------------------------------------------- ------------------------------------------- </configuration>
Note:
Change the ConnectionString value based on your database connection. Here, it is mentioned as the “local database” connection string
Step 5: Make use of the following server-side events of the Schedule control to perform the CRUD operation. Here, each event is assigned a server-side method name to be invoked at the time of a particular action.
[Default.aspx]
<ej:Schedule ID="Schedule1" runat="server" DataSourceID="SqlDataSource1" OnServerAppointmentSaved="Schedule1_ServerAppointmentSaved" OnServerAppointmentEdited="Schedule1_ServerAppointmentEdited" OnServerAppointmentDeleted="Schedule1_ServerAppointmentDeleted" OnServerDragStop="Schedule1_ServerDragStop" OnServerResizeStop="Schedule1_ServerResizeStop"> <AppointmentSettings Id="Id" Subject="Subject" AllDay="AllDay" StartTime="StartTime" EndTime="EndTime" Description="Description" Recurrence="Recurrence" RecurrenceRule="RecurrenceRule" /> <%--Here the table fields are mapped to the Schedule--%> </ej:Schedule>
Step 6: Define the server-side methods with appropriate code blocks as shown in the code-behind page.
[Default.aspx.vb]
Imports System.Data.SqlClient
Imports System.Data
Imports Syncfusion.JavaScript.Web
Partial Class _Default
Inherits Page
Dim connectionString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim conn As New SqlConnection(connectionString)
Dim drugs As New DataSet()
Dim adapter1 As New SqlDataAdapter
Dim sql As String
Private descriptionValue As String = Nothing
Private recurrenceValue As String = Nothing
'Binds the appointments to the Schedule.
Private Sub BindAppointments()
conn.Open()
Dim command As New SqlCommand("Select EndTime,Id,Recurrence,StartTime,Subject,AllDay,Description From ScheduleAppointments", conn)
'Sets up the command.
adapter1.SelectCommand = command
adapter1.Fill(drugs)
Schedule1.AppointmentSettings.DataSource = drugs
Schedule1.DataBind()
End Sub
Private m_Arguments As Dictionary(Of String, Object)
Public Property Arguments() As Dictionary(Of String, Object)
Get
Return m_Arguments
End Get
Set(value As Dictionary(Of String, Object))
m_Arguments = value
End Set
End Property
'Triggers and performs the insertion of appointments details into the database.
Protected Sub Schedule1_ServerAppointmentSaved(sender As Object, e As ScheduleEventArgs)
Arguments = TryCast(e.Arguments("appointment"), Dictionary(Of String, Object))
Dim list = TryCast(Arguments, Dictionary(Of String, Object))
sql = "Select Id from ScheduleAppointments Where Id='" + list("Id").ToString() + "'"
adapter1.SelectCommand = New SqlCommand(sql, conn)
adapter1.Fill(drugs)
descriptionValue = If(list.ContainsKey("Description") = False, Nothing, list("Description").ToString())
recurrenceValue = If(list("Recurrence").ToString() = "False", Nothing, list("RecurrenceRule").ToString())
If drugs.Tables(0).Rows.Count = 0 Then
conn.Open()
sql = "insert into ScheduleAppointments (Id,EndTime,Recurrence,StartTime,Subject,AllDay,Description,RecurrenceRule) values(" & list("Id").ToString() & ",'" & Convert.ToDateTime(list("EndTime")).ToUniversalTime().ToString() & "','" & list("Recurrence").ToString() & "','" & Convert.ToDateTime(list("StartTime")).ToUniversalTime().ToString() & "','" & list("Subject") & "','" & list("AllDay").ToString() & "','" & descriptionValue & "','" & recurrenceValue & "')"
adapter1.InsertCommand = New SqlCommand(sql, conn)
adapter1.InsertCommand.ExecuteNonQuery()
conn.Close()
End If
BindAppointments()
End Sub
'Triggers and performs the updating of appointment details into the database.
Protected Sub Schedule1_ServerAppointmentEdited(sender As Object, e As ScheduleEventArgs)
Arguments = TryCast(e.Arguments("appointment"), Dictionary(Of String, Object))
Dim list = TryCast(Arguments, Dictionary(Of String, Object))
conn.Open()
descriptionValue = If(list.ContainsKey("Description") = False, Nothing, list("Description").ToString())
recurrenceValue = If(list("Recurrence").ToString() = "False", Nothing, list("RecurrenceRule").ToString())
sql = "update ScheduleAppointments set EndTime='" & Convert.ToDateTime(list("EndTime")).ToUniversalTime().ToString() & "',Recurrence='" & list("Recurrence").ToString() & "',StartTime='" & Convert.ToDateTime(list("StartTime")).ToUniversalTime().ToString() & "',Subject='" & list("Subject") & "',AllDay='" & list("AllDay").ToString() & "',Description='" & descriptionValue & "',RecurrenceRule='" & recurrenceValue & "'where Id='" & list("Id").ToString() & "'"
adapter1.UpdateCommand = New SqlCommand(sql, conn)
adapter1.UpdateCommand.ExecuteNonQuery()
conn.Close()
BindAppointments()
End Sub
'Triggers while deleting the appointment details, and does the same in the database.
Protected Sub Schedule1_ServerAppointmentDeleted(sender As Object, e As ScheduleEventArgs)
Arguments = TryCast(e.Arguments("appointment")(0), Dictionary(Of String, Object))
Dim list = TryCast(Arguments, Dictionary(Of String, Object))
conn.Open()
sql = "Delete ScheduleAppointments Where Id='" & list("Id").ToString() & "'"
adapter1.DeleteCommand = New SqlCommand(sql, conn)
adapter1.DeleteCommand.ExecuteNonQuery()
conn.Close()
BindAppointments()
End Sub
'Triggers while performing the drag and drop of appointments, and updates the details into the database.
Protected Sub Schedule1_ServerDragStop(sender As Object, e As ScheduleEventArgs)
Arguments = TryCast(e.Arguments("appointment"), Dictionary(Of String, Object))
Dim list = TryCast(Arguments, Dictionary(Of String, Object))
conn.Open()
descriptionValue = If(list.ContainsKey("Description") = False, Nothing, list("Description").ToString())
recurrenceValue = If(list("Recurrence").ToString() = "False", Nothing, list("RecurrenceRule").ToString())
sql = "update ScheduleAppointments set EndTime='" & Convert.ToDateTime(list("EndTime")).ToUniversalTime().ToString() & "',Recurrence='" & list("Recurrence").ToString() & "',StartTime='" & Convert.ToDateTime(list("StartTime")).ToUniversalTime().ToString() & "',Subject='" & list("Subject") & "',AllDay='" & list("AllDay").ToString() & "',Description='" & descriptionValue & "',RecurrenceRule='" & recurrenceValue & "'where Id='" & list("Id").ToString() & "'"
adapter1.UpdateCommand = New SqlCommand(sql, conn)
adapter1.UpdateCommand.ExecuteNonQuery()
conn.Close()
BindAppointments()
End Sub
'Triggers and performs the updating of appointments details into the database after resizing the appointments.
Protected Sub Schedule1_ServerResizeStop(sender As Object, e As ScheduleEventArgs)
Arguments = TryCast(e.Arguments("appointment"), Dictionary(Of String, Object))
Dim list = TryCast(Arguments, Dictionary(Of String, Object))
conn.Open()
descriptionValue = If(list.ContainsKey("Description") = False, Nothing, list("Description").ToString())
recurrenceValue = If(list("Recurrence").ToString() = "False", Nothing, list("RecurrenceRule").ToString())
sql = "update ScheduleAppointments set EndTime='" & Convert.ToDateTime(list("EndTime")).ToUniversalTime().ToString() & "',Recurrence='" & list("Recurrence").ToString() & "',StartTime='" & Convert.ToDateTime(list("StartTime")).ToUniversalTime().ToString() & "',Subject='" & list("Subject") & "',AllDay='" & list("AllDay").ToString() & "',Description='" & descriptionValue & "',RecurrenceRule='" & recurrenceValue & "'where Id='" & list("Id").ToString() & "'"
adapter1.UpdateCommand = New SqlCommand(sql, conn)
adapter1.UpdateCommand.ExecuteNonQuery()
conn.Close()
BindAppointments()
End Sub
End Class
[Default.aspx.cs]
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Script.Serialization;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace ScheduleCRUDCS
{
public partial class _Default : Page
{
static string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(connectionString);
DataSet drugs = new DataSet();
SqlDataAdapter adapter1 = new SqlDataAdapter();
string sql = null;
string desriptionValue = "";
string recurrenceValue = "";
protected void Page_Load(object sender, EventArgs e)
{
}
//Binds the appointments to the Schedule.
private void BindAppointments()
{
conn.Open();
SqlCommand command = new SqlCommand("Select EndTime,Id,Recurrence,StartTime,Subject,AllDay,Description,RecurrenceRule From ScheduleAppointments", conn);
//Sets up the command.
adapter1.SelectCommand = command;
adapter1.Fill(drugs);
Schedule1.AppointmentSettings.DataSource = drugs;
Schedule1.DataBind();
}
private Dictionary<string, object> m_Arguments;
public Dictionary<string, object> Arguments
{
get { return m_Arguments; }
set { m_Arguments = value; }
}
//Triggers and performs the insertion of appointments details into the database.
protected void Schedule1_ServerAppointmentSaved(object sender, Syncfusion.JavaScript.Web.ScheduleEventArgs e)
{
Arguments = e.Arguments["appointment"] as Dictionary<string, object>;
var list = Arguments as Dictionary<string, object>;
sql = "Select Id from ScheduleAppointments Where Id='" + list["Id"].ToString() + "'";
adapter1.SelectCommand = new SqlCommand(sql, conn);
adapter1.Fill(drugs);
desriptionValue = list.ContainsKey("Description") == false ? null : list["Description"].ToString();
recurrenceValue = list["Recurrence"].ToString() == "True" ? list["RecurrenceRule"].ToString() : null;
if (drugs.Tables[0].Rows.Count == 0)
{
conn.Open();
sql = "insert into ScheduleAppointments (Id,EndTime,Recurrence,StartTime,Subject,AllDay,Description,RecurrenceRule) values(" + list["Id"].ToString() + ",'" + Convert.ToDateTime(list["EndTime"]).ToUniversalTime().ToString() + "','" + list["Recurrence"].ToString() + "','" + Convert.ToDateTime(list["StartTime"]).ToUniversalTime().ToString() + "','" + list["Subject"] + "','" + list["AllDay"].ToString() + "','" + desriptionValue + "','" + recurrenceValue + "')";
adapter1.InsertCommand = new SqlCommand(sql, conn);
adapter1.InsertCommand.ExecuteNonQuery();
conn.Close();
}
BindAppointments();
}
// Triggers and performs the updating of appointment details into the database.
protected void Schedule1_ServerAppointmentEdited(object sender, Syncfusion.JavaScript.Web.ScheduleEventArgs e)
{
Arguments = e.Arguments["appointment"] as Dictionary<string, object>;
var list = Arguments as Dictionary<string, object>;
conn.Open();
desriptionValue = list.ContainsKey("Description") == false ? null : list["Description"].ToString();
recurrenceValue = list["Recurrence"].ToString() == "True" ? list["RecurrenceRule"].ToString() : null;
sql = "update ScheduleAppointments set EndTime='" + Convert.ToDateTime(list["EndTime"]).ToUniversalTime().ToString() + "',Recurrence='" + list["Recurrence"].ToString() + "',StartTime='" + Convert.ToDateTime(list["StartTime"]).ToUniversalTime().ToString() + "',Subject='" + list["Subject"] + "',AllDay='" + list["AllDay"].ToString() + "',Description='" + desriptionValue + "',RecurrenceRule='" + recurrenceValue + "'where Id='" + list["Id"].ToString() + "'";
adapter1.UpdateCommand = new SqlCommand(sql, conn);
adapter1.UpdateCommand.ExecuteNonQuery();
conn.Close();
BindAppointments();
}
//Triggers while deleting the appointment details, and does the same in the database.
protected void Schedule1_ServerAppointmentDeleted(object sender, Syncfusion.JavaScript.Web.ScheduleEventArgs e)
{
var Arguments1 = (System.Collections.ArrayList)e.Arguments["appointment"];
var delList = (Dictionary<String, Object>)Arguments1[0];
conn.Open();
sql = "Delete ScheduleAppointments Where Id='" + delList["Id"].ToString() + "'";
adapter1.DeleteCommand = new SqlCommand(sql, conn);
adapter1.DeleteCommand.ExecuteNonQuery();
conn.Close();
BindAppointments();
}
//Triggers while performing the drag and drop of appointments, and updates the details into the database.
protected void Schedule1_ServerDragStop(object sender, Syncfusion.JavaScript.Web.ScheduleEventArgs e)
{
Arguments = e.Arguments["appointment"] as Dictionary<string, object>;
var list = Arguments as Dictionary<string, object>;
conn.Open();
desriptionValue = list.ContainsKey("Description") == false ? null : list["Description"].ToString();
recurrenceValue = list["Recurrence"].ToString() == "True" ? list["RecurrenceRule"].ToString() : null;
sql = "update ScheduleAppointments set EndTime='" + Convert.ToDateTime(list["EndTime"]).ToUniversalTime().ToString() + "',Recurrence='" + list["Recurrence"].ToString() + "',StartTime='" + Convert.ToDateTime(list["StartTime"]).ToUniversalTime().ToString() + "',Subject='" + list["Subject"] + "',AllDay='" + list["AllDay"].ToString() + "',Description='" + desriptionValue + "',RecurrenceRule='" + recurrenceValue + "'where Id='" + list["Id"].ToString() + "'";
adapter1.UpdateCommand = new SqlCommand(sql, conn);
adapter1.UpdateCommand.ExecuteNonQuery();
conn.Close();
BindAppointments();
}
//Triggers and performs the updating of appointments details into the database after resizing the appointments.
protected void Schedule1_ServerResizeStop(object sender, Syncfusion.JavaScript.Web.ScheduleEventArgs e)
{
Arguments = e.Arguments["appointment"] as Dictionary<string, object>;
var list = Arguments as Dictionary<string, object>;
conn.Open();
desriptionValue = list.ContainsKey("Description") == false ? null : list["Description"].ToString();
recurrenceValue = list["Recurrence"].ToString() == "True" ? list["RecurrenceRule"].ToString() : null;
sql = "update ScheduleAppointments set EndTime='" + Convert.ToDateTime(list["EndTime"]).ToUniversalTime().ToString() + "',Recurrence='" + list["Recurrence"].ToString() + "',StartTime='" + Convert.ToDateTime(list["StartTime"]).ToUniversalTime().ToString() + "',Subject='" + list["Subject"] + "',AllDay='" + list["AllDay"].ToString() + "',Description='" + desriptionValue + "',RecurrenceRule='" + recurrenceValue + "'where Id='" + list["Id"].ToString() + "'";
adapter1.UpdateCommand = new SqlCommand(sql, conn);
adapter1.UpdateCommand.ExecuteNonQuery();
conn.Close();
BindAppointments();
}
}
}
Step 6: Run the sample. Now, you can perform insert, edit, and delete operations on the schedule appointments that simultaneously reflects in the SQL database.
Sample Links:
C#:
https://www.syncfusion.com/downloads/support/directtrac/134692/ze/ScheduleCRUDCS-1040553939
VB:
https://www.syncfusion.com/downloads/support/directtrac/134692/ze/ScheduleCRUDVB2094783690
