)
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. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

How to perform the CRUD operations like insert, update, and delete with the SQL Database?

Platform: ASP.NET Web Forms |
Control: Schedule |
Published Date: June 16, 2015 |
Last Revised Date: May 30, 2019

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#:

http://www.syncfusion.com/downloads/support/directtrac/134692/ze/ScheduleCRUDCS-1040553939

VB:

http://www.syncfusion.com/downloads/support/directtrac/134692/ze/ScheduleCRUDVB2094783690

2X faster development

The ultimate ASP.NET Web Forms UI toolkit to boost your development speed.
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
Live Chat Icon