Insert / update / delete appointment in an SQL database

[Frederik Gysel]

Hi,

Can you please show me an example of how to insert / update / delete appointment data in an SQL server database?

I found some information about how to bind the appointment table to the schedule controle.

But how to insert an appointment in a database and update/delete existing appointments.

Is it possible to do this via the INSERT, UPDATE, and DELETE statements generated by the Data Source configuration wizard?

update 10/06/2018:

I am now trying to insert/update/delete appointment data via the CRUD functions: https://help.syncfusion.com/aspnet/schedule/data-binding#web-method-binding-with-crud-operation

I have followed the steps from https://help.syncfusion.com/aspnet/getting-started#manual-integration-of-syncfusion-aspnet-controls-into-the-newexisting-application to create my project.

Problem I am having now is that (after adding the schedule control) the events are not triggering the code behind.

With events I mean Schedule1_ServerAppointmentCreated, Schedule1_ServerAppointmentSaved, Schedule1_ServerAppointmentClick,...

When I set a breakpoint on above events, nothing happens...

This is the link to the project: project (v16.1.0.24)  Code is in schedule.aspx

What am I doing wrong?

Thank you

Frederik

[Nevitha Ravi]

Hi Fredrerik, 

Thank you for contacting Syncfusion Support. 

We recommend dataManger for performing CRUD operations instead of server side events. We have prepared a sample for your reference which can be downloaded from the below link. 

http://www.syncfusion.com/downloads/support/forum/138032/ze/asp_web_CRUD1226648129  

    <ej:Schedule ID="Schedule1" ClientIDMode="Static" Height="525px" Width="100%" CurrentDate="10/10/2015" runat="server"  > 

         <AppointmentSettings ApplyTimeOffset="false" Id="Id" Subject="Subject" AllDay="AllDay" StartTime="StartTime" EndTime="EndTime" Description="Description" Recurrence="Recurrence"  /> 

         <DataManager CrossDomain="true" URL="Default.aspx/GetData" CrudURL="Default.aspx/CrudResult" InsertURL="Default.aspx/InsertData" UpdateURL="Default.aspx/UpdateData" RemoveURL="Default.aspx/RemoveData" Adaptor="UrlAdaptor" /> 

    </ej:Schedule> 

Controller: 

       public static List<ScheduleAppointment> GetData() 

        { 

            // Mention the correct path of the database to connect. Also ensure, whether the provided database already exists. 

            SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\ScheduleData.mdf;Integrated Security=True"); 

            using (connection) 

            { 

 

                DataTable myDataSet = new DataTable(); 

                SqlConnection myAccessConn = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\ScheduleData.mdf;Integrated Security=True"); 

                SqlCommand myAccessCommand = new SqlCommand("SELECT * FROM ScheduleAppointments", myAccessConn); 

                SqlDataAdapter myDataAdapter = new SqlDataAdapter(myAccessCommand); 

                myAccessConn.Open(); 

                myDataAdapter.Fill(myDataSet); 

                List<DataRow> datasource = myDataSet.AsEnumerable().ToList(); 

 

                List<ScheduleAppointment> studentList = new List<ScheduleAppointment>(); 

                for (int i = 0; i < datasource.Count; i++) 

                { 

                    ScheduleAppointment student = new ScheduleAppointment(); 

                    student.Id = Convert.ToInt32(datasource[i]["Id"]); 

                    student.Subject = datasource[i]["Subject"].ToString(); 

                    student.StartTime = Convert.ToDateTime(datasource[i]["StartTime"]); 

                    student.EndTime = Convert.ToDateTime(datasource[i]["EndTime"]); 

                    student.AllDay = Convert.ToBoolean(datasource[i]["AllDay"]); 

                    student.Recurrence = Convert.ToBoolean(datasource[i]["Recurrence"]); 

                    student.RecurrenceRule = datasource[i]["RecurrenceRule"].ToString(); 

                    studentList.Add(student); 

                } 

                myAccessConn.Close(); 

                return studentList.ToList(); 

            } 

        } 

 

        [WebMethod] 

        [ScriptMethod(ResponseFormat = ResponseFormat.Json)] 

        public static List<ScheduleAppointment> CrudResult(List<ScheduleAppointment> added, List<ScheduleAppointment> changed, List<ScheduleAppointment> deleted) 

        { 

            SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\ScheduleData.mdf;Integrated Security=True"); 

            SqlDataAdapter adapter1 = new SqlDataAdapter(); 

            string sql = null; 

            DataClasses1DataContext db = new DataClasses1DataContext(); 

            //int intMax = db.ScheduleAppointments.ToList().Count > 0 ? db.ScheduleAppointments.ToList().Max(p => p.Id) : 0; 

            if (added != null && added.Count > 0) // it will be called when an appointment is added through detailed window 

            { 

                var value = added[0]; 

                connection.Open(); 

                sql = "insert into ScheduleAppointments (EndTime,Recurrence,StartTime,Subject,AllDay,RecurrenceRule) values(" + value.EndTime + "','" + value.Recurrence + "','" + value.StartTime + "','" + value.Subject + "','" + value.AllDay + "','" + value.RecurrenceRule + "')"; 

                SqlCommand cmd = new SqlCommand(sql, connection); 

                SqlParameter unitsParam = cmd.Parameters.AddWithValue("@RecurrenceRule", value.RecurrenceRule); 

                if (value.RecurrenceRule == null) 

                { 

                    unitsParam.Value = DBNull.Value; 

                } 

                cmd.ExecuteNonQuery(); 

                connection.Close(); 

            } 

            if (changed != null && changed.Count > 0) // it will be called when an appointment is edited through detailed window 

            { 

                var filterData = db.ScheduleAppointments.Where(c => c.Id == Convert.ToInt32(changed[0].Id)); 

                if (filterData.Count() > 0) 

                { 

                    var value = changed[0]; 

                    connection.Open(); 

                    sql = "update ScheduleAppointments set EndTime='" + value.EndTime + "',Recurrence='" + value.Recurrence + "',StartTime='" + value.StartTime + "',Subject='" + value.Subject + "',AllDay='" + value.AllDay + "',RecurrenceRule='" + value.RecurrenceRule + "'where Id='" + value.Id + "'"; 

                    adapter1.UpdateCommand = new SqlCommand(sql, connection); 

                    adapter1.UpdateCommand.ExecuteNonQuery(); 

                    connection.Close(); 

 

                } 

            } 

 

            if (deleted != null && deleted.Count > 0) 

            { 

                connection.Open(); 

                foreach (ScheduleAppointment dele in deleted) 

                { 

                    var app = db.ScheduleAppointments.ToList().Where(c => c.Id == Convert.ToInt32(dele.Id)).FirstOrDefault(); 

                    if (app != null) 

                    { 

                        SqlCommand delete = new SqlCommand("delete from ScheduleAppointments where Id=" + app.Id + "", connection); 

                        delete.ExecuteNonQuery(); 

                    } 

                } 

                connection.Close(); 

            } 

            return GetData(); 

        } 

 

        [WebMethod] 

        [ScriptMethod(ResponseFormat = ResponseFormat.Json)] 

        public static List<ScheduleAppointment> InsertData(ScheduleAppointment value) // this function will be called when an appointment is added through quick window 

        { 

            DataClasses1DataContext db = new DataClasses1DataContext();             

            SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\ScheduleData.mdf;Integrated Security=True"); 

            connection.Open(); 

            var command = "insert into ScheduleAppointments (Subject, StartTime, EndTime, AllDay, Recurrence, RecurrenceRule) values (@Subject, @StartTime, @EndTime, @AllDay, @Recurrence, @RecurrenceRule)"; 

            SqlCommand cmd = new SqlCommand(command,connection); 

            cmd.Parameters.AddWithValue("@Subject", value.Subject); 

            cmd.Parameters.AddWithValue("@StartTime", value.StartTime); 

            cmd.Parameters.AddWithValue("@EndTime", value.EndTime); 

            cmd.Parameters.AddWithValue("@AllDay", value.AllDay); 

            cmd.Parameters.AddWithValue("@Recurrence", value.Recurrence); 

            SqlParameter unitsParam  = cmd.Parameters.AddWithValue("@RecurrenceRule", value.RecurrenceRule); 

            if (value.RecurrenceRule == null) 

            { 

                unitsParam.Value = DBNull.Value; 

            } 

            cmd.ExecuteNonQuery(); 

            connection.Close(); 

            return GetData(); 

        } 

        [WebMethod] 

        [ScriptMethod(ResponseFormat = ResponseFormat.Json)] 

        public static List<ScheduleAppointment> RemoveData(string key) // this function will be called when an appointment is deleted 

        { 

            SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\ScheduleData.mdf;Integrated Security=True"); 

            connection.Open(); 

            int appId = Convert.ToInt32(key); 

            SqlCommand delete = new SqlCommand("delete from ScheduleAppointments where Id=" + appId + "", connection); 

            delete.ExecuteNonQuery(); 

            return GetData(); 

        } 

 

        [WebMethod] 

        [ScriptMethod(ResponseFormat = ResponseFormat.Json)] 

        public static List<ScheduleAppointment> UpdateData(ScheduleAppointment value) // this function will be called when an appointment is updated 

        { 

            DataClasses1DataContext db = new DataClasses1DataContext(); 

            SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\ScheduleData.mdf;Integrated Security=True"); 

            string sql = null; 

            SqlDataAdapter adapter1 = new SqlDataAdapter(); 

            var filterData = db.ScheduleAppointments.Where(c => c.Id == Convert.ToInt32(value.Id)); 

            if (filterData.Count() > 0) 

            { 

                connection.Open(); 

                sql = "update ScheduleAppointments set EndTime='" + value.EndTime + "',Recurrence='" + value.Recurrence + "',StartTime='" + value.StartTime + "',Subject='" + value.Subject + "',AllDay='" + value.AllDay + "',RecurrenceRule='" + value.RecurrenceRule + "'where Id='" + value.Id + "'"; 

                SqlCommand cmd = new SqlCommand(sql, connection); 

                SqlParameter unitsParam = cmd.Parameters.AddWithValue("@RecurrenceRule", value.RecurrenceRule); 

                if (value.RecurrenceRule == null) 

                { 

                    unitsParam.Value = DBNull.Value; 

                } 

                adapter1.UpdateCommand = new SqlCommand(sql, connection); 

                adapter1.UpdateCommand.ExecuteNonQuery(); 

                connection.Close(); 

            } 

            return GetData(); 

        } 

Regarding Server side events not triggering issue, we have checked the shared sample in which you have missed to refer scriptManager in the schedule.aspx file. Please add the below highlighted code to overcome the reported problem. 

    <form id="form1" runat="server"> 

         <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager> 

       <div> 

        <ej:Schedule ID="Schedule1" runat="server" OnServerAppointmentCreated="Schedule1_ServerAppointmentCreated" OnServerAppointmentSaved="Schedule1_ServerAppointmentSaved" OnServerAppointmentClick="Schedule1_ServerAppointmentClick"> 

        </ej:Schedule> 

     </div> 

    </form> 

Please try out the above sample and let us know if you need further assistance. 

Regards, 

Nevitha.  

 

[Frederik Gysel]

Hi Nevitha,

Thank you for quick reply.

The missing scriptmanager was indeed the cause of not firing the events.

Yesterday I placed the scriptmanager after the opening div, just before the schedule control and it did not work.

Right position of scriptmanager is apparently very important.

Maybe another thing for other people implementing the schedule control in existing applications:

Watch out with  <xhtmlConformance mode="Legacy"/> tag in web.config.

Server events of the schedule control will not fire with mode set to Legacy. It should be set to 'transitional' or 'strict', then event triggers will work but also watch out with other pages containing a scriptmanager. Functionality of these pages should be evaluated afterwards.

(http://burnignorance.com/asp-net-developer-tips/asp-net-project-migration-issue-with-asp-net-ajax-solution-xhtmlconformance-in-web-config/)

One more thing: why is datamanager recommended for CRUD? Using the events looks far more easier, no?

Thank you

Frederik

[Nevitha Ravi]

Hi Frederik, 

Thanks for your update. 

As we have handled server-side events customization within our source itself, it is effective way to use dataManager to perform CRUD operations. 

Regards, 

Nevitha.