)
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 operation in the Schedule control with WebServices DataSource?

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

The Schedule control supports the CRUD operations (Create, Read, Update, Delete) by using the Web Services methods.

The following steps help you perform the CRUD operations like adding, editing and deleting actions on the Schedule control by using the web services in the ASP.NET (EJWEB) application.

Step 1: Create the ASP.NET application and include the EJWEB Schedule control in it by referring the following KB link,

http://www.syncfusion.com/kb/3948/how-to-add-the-ejweb-schedule-control-in-the-asp-net-application

Step 2: Create a 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
);

 

Note:

You can create the table through Visual Studio as a local database or by using SQL Server Management Studio

Step 3: Add the connection String configuration in the Web.Config page. Refer to the following code example.

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 the database connection. Here, the “local database” connection string is mentioned

Step 4: Create a web service with the CRUD operation functionality using the following steps:

  1. Create a web service by selecting the new item in the solution.

Creation of the webservice

Figure 1: Creation of the webservice

  1. The WebService1.asmx.cs/ WebService1.asmx.vb file opens with the details as shown in the screenshot.

WebService1.asmx.cs/ WebService1.asmx.vb file

Figure 2: WebService1.asmx.cs/ WebService1.asmx.vb file

  1. Now, add the CRUD operation code to this page.

WebService1.asmx.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Services;
namespace ScheduleCRUDWithWebServices
{
    /// <summary>
    /// Summary description for WebService1.
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // Allows this Web Service to be called from script by using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class WebService1 : System.Web.Services.WebService
    {
        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }
        //Gets/reads the appointment details from the database.
        [WebMethod]
        public DataTable BindAppointments()
        {
            string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            SqlConnection conn = new SqlConnection(constr);
            SqlDataAdapter adapter1 = new SqlDataAdapter();
            DataSet drugs = new DataSet();
            conn.Open();
            SqlCommand command = new SqlCommand("Select EndTime,Id,StartTime,Subject,AllDay,Description From Appointments", conn);
            //Sets up the command.
            adapter1.SelectCommand = command;
            adapter1.Fill(drugs);
            return drugs.Tables[0];
        }
        //Inserts the appointment details into the database.
        [WebMethod]
        public void Insert(int id, string subject, string description, string startTime, string endTime, bool allDay)
        {
            string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("INSERT INTO Appointments (Id, Subject, Description, StartTime, EndTime, AllDay) VALUES (@Id, @Subject, @Description, @StartTime, @EndTime, @AllDay)"))
                {
                    cmd.Parameters.AddWithValue("@Id", id);
                    cmd.Parameters.AddWithValue("@Subject", subject);
                    cmd.Parameters.AddWithValue("@Description", description);
                    cmd.Parameters.AddWithValue("@StartTime", Convert.ToDateTime(startTime));
                    cmd.Parameters.AddWithValue("@EndTime", Convert.ToDateTime(endTime));
                    cmd.Parameters.AddWithValue("@AllDay", allDay);
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
        //Updates the appointment details into the database.
        [WebMethod]
        public void Update(int id, string subject, string description, string startTime, string endTime, bool allDay)
        {
            string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("UPDATE Appointments SET Id = @Id, Subject = @Subject, Description=@Description, StartTime=@StartTime, EndTime=@EndTime, AllDay=@AllDay WHERE Id = @Id"))
                {
                    cmd.Parameters.AddWithValue("@Id", id);
                    cmd.Parameters.AddWithValue("@Subject", subject);
                    cmd.Parameters.AddWithValue("@Description", description);
                    cmd.Parameters.AddWithValue("@StartTime", Convert.ToDateTime(startTime));
                    cmd.Parameters.AddWithValue("@EndTime", Convert.ToDateTime(endTime));
                    cmd.Parameters.AddWithValue("@AllDay", allDay);
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
        //Deletes the appointment details from the database.
        [WebMethod]
        public void Delete(int id)
        {
            string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("DELETE FROM Appointments WHERE Id = @Id"))
                {
                    cmd.Parameters.AddWithValue("@Id", id);
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
    }
}

WebService1.aspx.vb

Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports System.Data.SqlClient
'Allows this Web Service to be called from script by using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class WebService1
    Inherits System.Web.Services.WebService
    <WebMethod()> _
    Public Function HelloWorld() As String
        Return "Hello World"
    End Function
    'Gets/reads the appointment details from the database.
    <WebMethod()> _
    Public Function BindAppointments() As DataTable
        Dim constr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
        Dim conn As New SqlConnection(constr)
        Dim adapter1 As New SqlDataAdapter
        Dim drugs As New DataSet
        conn.Open()
        Dim command As New SqlCommand("Select EndTime,Id,StartTime,Subject,AllDay,Description From Appointments", conn)
        'Sets up the command.
        adapter1.SelectCommand = command
        adapter1.Fill(drugs)
        Return drugs.Tables(0)
    End Function
    'Inserts the appointment details from the database.
    <WebMethod()> _
    Public Sub Insert(id As Integer, subject As String, description As String, startTime As String, endTime As String, allDay As Boolean)
        Dim constr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
        Using con As New SqlConnection(constr)
            Using cmd As New SqlCommand("INSERT INTO Appointments (Id, Subject, Description, StartTime, EndTime, AllDay) VALUES (@Id, @Subject, @Description, @StartTime, @EndTime, @AllDay)")
                cmd.Parameters.AddWithValue("@Id", id)
                cmd.Parameters.AddWithValue("@Subject", subject)
                cmd.Parameters.AddWithValue("@Description", description)
                cmd.Parameters.AddWithValue("@StartTime", Convert.ToDateTime(startTime))
                cmd.Parameters.AddWithValue("@EndTime", Convert.ToDateTime(endTime))
                cmd.Parameters.AddWithValue("@AllDay", allDay)
                cmd.Connection = con
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    End Sub
    'Updates the appointment details in the database.
    <WebMethod()> _
    Public Sub Update(id As Integer, subject As String, description As String, startTime As String, endTime As String, allDay As Boolean)
        Dim constr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
        Using con As New SqlConnection(constr)
            Using cmd As New SqlCommand("UPDATE Appointments SET Id = @Id, Subject = @Subject, Description=@Description, StartTime=@StartTime, EndTime=@EndTime, AllDay=@AllDay WHERE Id = @Id")
                cmd.Parameters.AddWithValue("@Id", id)
                cmd.Parameters.AddWithValue("@Subject", subject)
                cmd.Parameters.AddWithValue("@Description", description)
                cmd.Parameters.AddWithValue("@StartTime", Convert.ToDateTime(startTime))
                cmd.Parameters.AddWithValue("@EndTime", Convert.ToDateTime(endTime))
                cmd.Parameters.AddWithValue("@AllDay", allDay)
                cmd.Connection = con
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    End Sub
    'Deletes the appointment details from the database.
    <WebMethod()> _
    Public Sub Delete(id As Integer)
        Dim constr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
        Using con As New SqlConnection(constr)
            Using cmd As New SqlCommand("DELETE FROM Appointments WHERE Id = @Id")
                cmd.Parameters.AddWithValue("@Id", id)
                cmd.Connection = con
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    End Sub
End Class

Step 5: Build the sample and add the WebReference to your application using the following steps,

  1. Right-click Reference and select the Add ServiceReference option as shown in the following screenshot.

Select the Add ServiceReference option

Figure 3: Select the Add ServiceReference option

  1. Click Discover to get the created WebServices details.

WebService details

Figure 4: WebService details

  1. Click the Advanced button and the Service Reference Settings window opens. Select Add Web Reference in that window.

Service Reference Settings window

Figure 5: Service Reference Settings window

  1. The Add Web Reference dialog opens as shown in the following screenshot.

Add Web Reference

Figure 6: Add Web Reference

  1. Select the Web Services in this solution option from the three options, and the available WebServices details are displayed.

WebServices details

Figure 7: WebServices details

  1. Select the WebService (Ex: WebService1) link and it displays the details of the created WebServices with the web reference name. For example, localhost is displayed here.

Details of the created WebServices

Figure 8: Details of the created WebServices

  1. Click the Add Reference button and the reference is successfully added to your application.

References added

Figure 9: Reference added

Step 6: Make use of the server-side events of the Schedule control and also map the field names to the Schedule control’s appointmentSettings to perform the CRUD operation.

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" /> <%--Maps the table fields to the schedule--%>
    </ej:Schedule>

Step 7: Define the server-side methods with appropriate functionalities in the code behind the page. Also, call the web service methods within each of the following server-side methods appropriately.

Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace ScheduleCRUDWithWebServices
{
    public partial class _Default : Page
    {
        localhost.WebService1 service = new localhost.WebService1();   // Creates the object to the service to call the method.
        private string descriptionValue = null;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindAppointments();
            }
        }
        private void BindAppointments()
        {
            DataTable appointmentsData = default(DataTable);
            appointmentsData = service.BindAppointments();
            // Calls the BindAppointments webservice method to read the data.
            Schedule1.AppointmentSettings.DataSource = appointmentsData;
            //Passes the appointment value.
        }
        private Dictionary<string, object> m_Arguments;
        public Dictionary<string, object> Arguments
        {
            get { return m_Arguments; }
            set { m_Arguments = value; }
        }
        protected void Schedule1_ServerAppointmentSaved(object sender, Syncfusion.JavaScript.Web.ScheduleEventArgs e)
        {
            Arguments = e.Arguments["appointment"] as Dictionary<string, object>;
            dynamic list = Arguments as Dictionary<string, object>;
            descriptionValue = list.ContainsKey("Description") == false ? "" : list["Description"].ToString();
            service.Insert(list["Id"], list["Subject"].ToString(), descriptionValue.ToString(), Convert.ToDateTime(list["StartTime"]).ToUniversalTime().ToString(), Convert.ToDateTime(list["EndTime"]).ToUniversalTime().ToString(), Convert.ToBoolean(list["AllDay"])); //Calls the insert Web Service method.
            BindAppointments();
        }
        protected void Schedule1_ServerAppointmentEdited(object sender, Syncfusion.JavaScript.Web.ScheduleEventArgs e)
        {
            Arguments = e.Arguments["appointment"] as Dictionary<string, object>;
            dynamic list = Arguments as Dictionary<string, object>;
            descriptionValue = list.ContainsKey("Description") == false ? "" : list["Description"].ToString();
            service.Update(list["Id"], list["Subject"].ToString(), descriptionValue.ToString(), Convert.ToDateTime(list["StartTime"]).ToUniversalTime().ToString(), Convert.ToDateTime(list["EndTime"]).ToUniversalTime().ToString(), Convert.ToBoolean(list["AllDay"])); //Calls the update Web Service method.
            BindAppointments();
        }
        protected void Schedule1_ServerAppointmentDeleted(object sender, Syncfusion.JavaScript.Web.ScheduleEventArgs e)
        {
            var Arguments1 = (System.Collections.ArrayList)e.Arguments["appointment"];
            var list = (Dictionary<String, Object>)Arguments1[0];
            service.Delete(Convert.ToInt32(list["Id"])); //Calls the delete Web Service method.
            BindAppointments();
        }
        protected void Schedule1_ServerResizeStop(object sender, Syncfusion.JavaScript.Web.ScheduleEventArgs e)
        {
            Arguments = e.Arguments["appointment"] as Dictionary<string, object>;
            dynamic list = Arguments as Dictionary<string, object>;
            descriptionValue = list.ContainsKey("Description") == false ? "" : list["Description"].ToString();
            service.Update(list["Id"], list["Subject"].ToString(), descriptionValue.ToString(), Convert.ToDateTime(list["StartTime"]).ToUniversalTime().ToString(), Convert.ToDateTime(list["EndTime"]).ToUniversalTime().ToString(), Convert.ToBoolean(list["AllDay"])); //Calls the update Web Service method. 
            BindAppointments();
        }
        protected void Schedule1_ServerDragStop(object sender, Syncfusion.JavaScript.Web.ScheduleEventArgs e)
        {
            Arguments = e.Arguments["appointment"] as Dictionary<string, object>;
            dynamic list = Arguments as Dictionary<string, object>;
            descriptionValue = list.ContainsKey("Description") == false ? "" : list["Description"].ToString();
            service.Update(list["Id"], list["Subject"].ToString(), descriptionValue.ToString(), Convert.ToDateTime(list["StartTime"]).ToUniversalTime().ToString(), Convert.ToDateTime(list["EndTime"]).ToUniversalTime().ToString(), Convert.ToBoolean(list["AllDay"]));  //Calls the update Web Service method.
            BindAppointments();
        }
    }
}

Default.aspx.vb

Imports System.Data.SqlClient
Public Class _Default
    Inherits Page
    Private descriptionValue As String = Nothing
    Dim service As New localhost.WebService1  'Creates the object to the service to call the method.
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        If Not Me.IsPostBack Then
            Me.BindAppointments()
        End If
    End Sub
    Private Sub BindAppointments()
        Dim loc As New localhost.WebService1
        Dim appointmentsData As DataTable
        appointmentsData = loc.BindAppointments()   'Calls the BindAppointments webservice method to read the data.
        Schedule1.AppointmentSettings.DataSource = appointmentsData   'Passes the appointment value.
    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
    Protected Sub Schedule1_ServerAppointmentSaved(sender As Object, e As Syncfusion.JavaScript.Web.ScheduleEventArgs)
        Arguments = TryCast(e.Arguments("appointment"), Dictionary(Of String, Object))
        Dim list = TryCast(Arguments, Dictionary(Of String, Object))
        descriptionValue = If(list.ContainsKey("Description") = False, "", list("Description").ToString())
        Dim service As New localhost.WebService1
        service.Insert(list("Id").ToString(), list("Subject").ToString(), descriptionValue.ToString(), Convert.ToDateTime(list("StartTime")).ToUniversalTime().ToString(), Convert.ToDateTime(list("EndTime")).ToUniversalTime().ToString(), Convert.ToBoolean(list("AllDay"))) 'Calls the insert Web Service method.
        Me.BindAppointments()
    End Sub
    Protected Sub Schedule1_ServerAppointmentEdited(sender As Object, e As Syncfusion.JavaScript.Web.ScheduleEventArgs)
        Arguments = TryCast(e.Arguments("appointment"), Dictionary(Of String, Object))
        Dim list = TryCast(Arguments, Dictionary(Of String, Object))
        descriptionValue = If(list.ContainsKey("Description") = False, "", list("Description").ToString())
        service.Update(list("Id").ToString(), list("Subject").ToString(), descriptionValue.ToString(), Convert.ToDateTime(list("StartTime")).ToUniversalTime().ToString(), Convert.ToDateTime(list("EndTime")).ToUniversalTime().ToString(), Convert.ToBoolean(list("AllDay"))) 'Calls the update Web Service method.
        Me.BindAppointments()
    End Sub
    Protected Sub Schedule1_ServerAppointmentDeleted(sender As Object, e As Syncfusion.JavaScript.Web.ScheduleEventArgs)
        Arguments = TryCast(e.Arguments("appointment")(0), Dictionary(Of String, Object))
        Dim list = TryCast(Arguments, Dictionary(Of String, Object))
        service.Delete(list("Id").ToString())  'Calls the delete Web Service method.
        Me.BindAppointments()
    End Sub
    Protected Sub Schedule1_ServerResizeStop(sender As Object, e As Syncfusion.JavaScript.Web.ScheduleEventArgs)
        Arguments = TryCast(e.Arguments("appointment"), Dictionary(Of String, Object))
        Dim list = TryCast(Arguments, Dictionary(Of String, Object))
        descriptionValue = If(list.ContainsKey("Description") = False, "", list("Description").ToString())
        service.Update(list("Id").ToString(), list("Subject").ToString(), descriptionValue.ToString(), Convert.ToDateTime(list("StartTime")).ToUniversalTime().ToString(), Convert.ToDateTime(list("EndTime")).ToUniversalTime().ToString(), Convert.ToBoolean(list("AllDay"))) 'Calls the update Web Service method.
        Me.BindAppointments()
    End Sub
    Protected Sub Schedule1_ServerDragStop(sender As Object, e As Syncfusion.JavaScript.Web.ScheduleEventArgs)
        Arguments = TryCast(e.Arguments("appointment"), Dictionary(Of String, Object))
        Dim list = TryCast(Arguments, Dictionary(Of String, Object))
        descriptionValue = If(list.ContainsKey("Description") = False, "", list("Description").ToString())
        service.Update(list("Id").ToString(), list("Subject").ToString(), descriptionValue.ToString(), Convert.ToDateTime(list("StartTime")).ToUniversalTime().ToString(), Convert.ToDateTime(list("EndTime")).ToUniversalTime().ToString(), Convert.ToBoolean(list("AllDay"))) 'Calls the update Web Service method.
        Me.BindAppointments()
    End Sub
End Class
 

Step 8: Run the sample and now you can perform CRUD operations on the schedule appointments that are reflected appropriately in the WebServices too.

Sample Links:

C#:

http://www.syncfusion.com/downloads/support/forum/119417/ze/ScheduleCRUDWithWebServices-728709208

VB:

http://www.syncfusion.com/downloads/support/forum/119417/ze/ScheduleSampleWebServices-617457551

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
Comments
Frederik Gysel
Jun 16, 2018

Dear,

This example generates error "The given key was not present in the dictionary." while trying to edit an agenda item on the schedule.


Rgds

Frederik


Reply
Frederik Gysel
Jun 16, 2018

string ss = list["Id"].ToString();

Above line trows the error.

Required parameters are in [1] of the dictionary, not in [0].




Reply
Frederik Gysel
Jun 16, 2018

How to get the data of [1] in the dictionary?

Reply

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