DBSchedulerProviderBase and Entities

[Digger]

Hope someone has the patients to spell out for me the actual changes needed in the DBSchedulerProviderBase(inherited from SchedulerProviderBase) class to work with my Entities Provider.  I cant seem to find any examples of what needs to change in this base class to get my Entities associated with my custom RadLabDBSchedulerProvider class.

Here is what i have so far in this Base Class - I have added the obvious DBFactory and ConnectionString, :

public abstract class DbSchedulerProviderBase : SchedulerProviderBase
    {
        protected DbProviderFactory DbFactory { get { return DbProviderFactories.GetFactory("System.Data.EntityClient");} set { }}
        protected bool PersistChanges { get { }; set { }; }
        protected string ConnectionString { get { return ConfigurationManager.ConnectionStrings["RadLabDBEntities"].ConnectionString;} set { }; }
        public override void Initialize(string name, NameValueCollection config) { };
        protected virtual DbConnection OpenConnection() { };
        protected virtual DbParameter CreateParameter(string name, object value) { };
    }

So what needs to go in PersitChanges, Initialize(), DbConnection, etc??...   I have my inherited class for the provider all ready, just i don't understand all these function in the base class, and what needs be changed.  Can you point me to examples?  Ok with giving me links to other resources, but if you could spell out an examples (cant seem to find any examples of this in your codebase or docs).  

Here is my web.config info:

<configSections>
   <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
   <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
 <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 --></configSections>
 <connectionStrings>
   <add name="TelerikConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Telerik.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
   <add name="TelerikVSXConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TelerikVSX.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
   <add name="Telerik" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Telerik;Integrated Security=True" providerName="System.Data.SqlClient" />
   <add name="RadLabDBEntities" connectionString="metadata=res://BarLab/BarLabModel.csdl|res://BarLab/BarLabModel.ssdl|res://BarLab/BarLabModel.msl;provider=System.Data.SqlClient;provider connection string='data source=IRIONVO\SQLIRIONVO;initial catalog=RadLabDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework'" providerName="System.Data.EntityClient" />
 </connectionStrings>

<entityFramework>
   <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
   <providers>
     <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
   </providers>
 </entityFramework>

And if needed, here is my RadLabDbSchedulerProvider.cs code  - This is what i would then assign to radscheduler1.Provider = RadLabDbSchedulerProvider:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using Telerik.Web.UI;


    public class RadLabDbSchedulerProvider : DbSchedulerProviderBase
    {

        public ArrayList resourceKeys = new ArrayList();

        public Dictionary<int, Resource> _persons { get; set; }

        public Dictionary<int, Resource> _hardware { get; set; }

        public override IEnumerable<ResourceType> GetResourceTypes(RadScheduler owner)
        {
            ResourceType[] resourceTypes = new ResourceType[2];
            resourceKeys.Clear();

            resourceTypes[0] = new ResourceType("Person", true);
            resourceKeys.Add("Person");

            resourceTypes[1] = new ResourceType("BAR_HW", true);
            resourceKeys.Add("BAR_HW");
            return resourceTypes;
        }

        public override IEnumerable<Resource> GetResourcesByType(RadScheduler owner, string resourceType)
        {
            switch (resourceType)
            {
                case "Person":
                    return Persons.Values;

                case "BAR_HW":
                    return BAR_HWs.Values;

                default:
                    throw new InvalidOperationException("Unknown resource type: " + resourceType);
            }
        }

        private IDictionary<int, Resource> Persons
        {
            get
            {
                if (_persons == null)
                {
                    _persons = new Dictionary<int, Resource>();
                    foreach (Resource person in LoadPersons())
                    {
                        _persons.Add((int)person.Key, person);
                    }
                }
                return _persons;
            }
        }

        private IDictionary<int, Resource> BAR_HWs
        {
            get
            {
                _hardware = new Dictionary<int, Resource>();
                foreach (Resource hardware in LoadHardware())
                {
                    _hardware.Add((int)hardware.Key, hardware);
                }
                return _hardware;
            }
        }

        private IEnumerable<Resource> LoadPersons()
        {
            List<Resource> resources = new List<Resource>();
            using (DbConnection conn = OpenConnection())
            {
                DbCommand cmd = DbFactory.CreateCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT [ResourceId], [Name], [Type], [Available], [ParentId], [Level], [Status], [Shared], [LastModifedTime], [Url], [LastModifedUser], [Building], [PersonId], [QuicklookId], [Cube], [Phone], [Email] FROM [Person]";
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Resource res = new Resource();
                        res.Type = "Person";
                        res.Key = reader["ResourceId"];
                        res.Text = Convert.ToString(reader["Name"]);
                        res.Attributes["ParentId"] = Convert.ToString(reader["ParentId"]);
                        res.Attributes["Type"] = Convert.ToString(reader["Type"]);
                        res.Attributes["BomId"] = string.Empty;
                        res.Attributes["Approved"] = string.Empty;
                        resources.Add(res);
                    }
                }
            }
            return resources;
        }

        private IEnumerable<Resource> LoadHardware()
        {
            List<Resource> resources = new List<Resource>();
            using (DbConnection conn = OpenConnection())
            {
                DbCommand cmd = DbFactory.CreateCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT [ResourceId], [Name], [Type], [Available], [ParentId], [Level], [Status], [Shared], [LastModifedTime], [Url], [LastModifedUser], [Building], [HWId], [Lab], [Tile], [BomId], [Owner], [RegisteredTime], [LastContactTime], [Station], [Row], [IP], [Cabinet], [RailSlot], [SizeU], [AssetTag], [SrvsTicket] FROM [BAR_HW]";
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Resource res = new Resource();
                        res.Type = "BAR_HW";
                        res.Key = reader["ResourceId"];
                        res.Text = Convert.ToString(reader["Name"]);
                        res.Attributes["ParentId"] = Convert.ToString(reader["ParentId"]);
                        res.Attributes["Type"] = Convert.ToString(reader["Type"]);
                        res.Attributes["BomId"] = Convert.ToString(reader["BomId"]);
                        res.Attributes["Approved"] = string.Empty;

                        resources.Add(res);
                    }
                }
            }
            return resources;
        }

        private void LoadResources(Appointment apt)
        {
            using (DbConnection conn = OpenConnection())
            {
                DbCommand cmd = DbFactory.CreateCommand();
                cmd.Connection = conn;

                cmd.Parameters.Add(CreateParameter("@ScheduleId", apt.ID));
                cmd.CommandText = "SELECT [ResourceId], [ParentId], [BomId], [Type], [Approved] FROM [ScheduleResource] WHERE [ScheduleId] = @ScheduleId";
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        switch (Convert.ToString(reader["Type"]))
                        {
                            case "Person":
                                Resource presource = Persons[Convert.ToInt32(reader["ResourceId"])];
                                presource.Attributes["ParentId"] = Convert.ToString(reader["ParentId"]);
                                presource.Attributes["Type"] = Convert.ToString(reader["Type"]);
                                presource.Attributes["BomId"] = Convert.ToString(reader["BomId"]);
                                presource.Attributes["Approved"] = Convert.ToString(reader["Approved"]);
                                apt.Resources.Add(presource);
                                break;

                            case "BAR_HW":
                                Resource resource = BAR_HWs[Convert.ToInt32(reader["ResourceId"])];
                                resource.Attributes["ParentId"] = Convert.ToString(reader["ParentId"]);
                                resource.Attributes["Type"] = Convert.ToString(reader["Type"]);
                                resource.Attributes["BomId"] = Convert.ToString(reader["BomId"]);
                                resource.Attributes["Approved"] = Convert.ToString(reader["Approved"]);
                                apt.Resources.Add(resource);
                                break;

                            default:
                                throw new InvalidOperationException("Unknown resource type: " + Convert.ToString(reader["Type"]));
                        }
                    }
                }
            }
        }

        public override IEnumerable<Appointment> GetAppointments(RadScheduler owner)
        {
            List<Appointment> appointments = new List<Appointment>();
            using (DbConnection conn = OpenConnection())
            {
                DbCommand cmd = DbFactory.CreateCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT [ScheduleId], [Subject], [Start], [End], [RecurrenceRule], [RecurrenceParentID], [Email], [LastModified], [Description], [ProjectId], [TicketId], [QuicklookId], [Reminder], [AppointmentColor] FROM [Schedules]";
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Appointment apt = new Appointment();
                        apt.Owner = owner;
                        apt.ID = reader["ScheduleId"];
                        apt.Subject = Convert.ToString(reader["Subject"]);
                        apt.Start = DateTime.SpecifyKind(Convert.ToDateTime(reader["Start"]), DateTimeKind.Utc);
                        apt.End = DateTime.SpecifyKind(Convert.ToDateTime(reader["End"]), DateTimeKind.Utc);
                        apt.RecurrenceRule = Convert.ToString(reader["RecurrenceRule"]);
                        apt.RecurrenceParentID = reader["RecurrenceParentId"] == DBNull.Value ? null : reader["RecurrenceParentId"];
                        apt.Description = Convert.ToString(reader["Description"]);

                        apt.Attributes["Email"] = reader["Email"].ToString();
                        apt.Attributes["ProjectId"] = reader["ProjectId"].ToString();
                        apt.Attributes["TicketId"] = reader["TicketId"].ToString();
                        apt.Attributes["QuicklookId"] = reader["QuicklookId"].ToString();
                        apt.Attributes["AppointmentColor"] = reader["AppointmentColor"].ToString();
                        apt.Attributes["LastModified"] = DateTime.SpecifyKind(Convert.ToDateTime(reader["LastModified"]), DateTimeKind.Utc).ToShortDateString();

                        if (apt.RecurrenceParentID != null)
                        {
                            apt.RecurrenceState = RecurrenceState.Exception;
                        }
                        else if (apt.RecurrenceRule != string.Empty)
                        {
                            apt.RecurrenceState = RecurrenceState.Master;
                        }
                        LoadResources(apt);
                        appointments.Add(apt);
                    }
                }
            }
            return appointments;
        }

        private void FillSchedResources(Appointment appointment, DbCommand cmd, object ScheduleId, string resType)
        {
            foreach (Resource resource in appointment.Resources.GetResourcesByType(resType))
            {
                cmd.Parameters.Clear();
                cmd.Parameters.Add(CreateParameter("@SchedleId", ScheduleId));
                cmd.Parameters.Add(CreateParameter("@ResourceId", resource.Key));
                cmd.Parameters.Add(CreateParameter("@ParentId", resource.Attributes["ParentId"]));
                cmd.Parameters.Add(CreateParameter("@Type", resource.Attributes["Type"]));
                cmd.Parameters.Add(CreateParameter("@BomId", resource.Attributes["BomId"]));
                cmd.Parameters.Add(CreateParameter("@Approved", resource.Attributes["Approved"]));

                cmd.CommandText = "INSERT INTO [ScheduleResource] ([ScheduleId], [ResourceId], [ParentId], [Type], [BomId], [Approved]) VALUES (@ScheduleId, @ResourceId, @ParentId, @Type, @BomId, @Approved)";
                cmd.ExecuteNonQuery();
            }
        }

        private void ClearSchedResources(object ScheduleId, DbCommand cmd)
        {
            cmd.Parameters.Clear();
            cmd.Parameters.Add(CreateParameter("@ScheduleId", ScheduleId));
            cmd.CommandText = "DELETE FROM [ScheduleResource] WHERE [ScheduleId] = @ScheduleId";
            cmd.ExecuteNonQuery();
        }

        private void PopulateAppointmentParameters(DbCommand cmd, Appointment apt)
        {
            cmd.Parameters.Add(CreateParameter("@Subject", apt.Subject));
            cmd.Parameters.Add(CreateParameter("@Start", apt.Start));
            cmd.Parameters.Add(CreateParameter("@End", apt.End));
            cmd.Parameters.Add(CreateParameter("@Description", apt.Description));
            cmd.Parameters.Add(CreateParameter("@Reminder", apt.Reminders));


            cmd.Parameters.Add(CreateParameter("@Email", apt.Attributes["Email"]));
            cmd.Parameters.Add(CreateParameter("@LastModified", apt.Attributes["LastModified"]));
            cmd.Parameters.Add(CreateParameter("@ProjectId", apt.Attributes["ProjectId"]));
            cmd.Parameters.Add(CreateParameter("@TicketId", apt.Attributes["TicketId"]));
            cmd.Parameters.Add(CreateParameter("@QuicklookId", apt.Attributes["QuicklookId"]));
            cmd.Parameters.Add(CreateParameter("@AppointmentColor", apt.Attributes["AppointmentColor"]));
            string rrule = null;
            if (apt.RecurrenceRule != string.Empty)
            {
                rrule = apt.RecurrenceRule;
            }
            cmd.Parameters.Add(CreateParameter("@RecurrenceRule", rrule));

            object parentId = null;
            if (apt.RecurrenceParentID != null)
            {
                parentId = apt.RecurrenceParentID;
            }
            cmd.Parameters.Add(CreateParameter("@RecurrenceParentId", parentId));


        }

        public override void Insert(RadScheduler owner, Appointment appointmentToInsert)
        {
            if (!PersistChanges)
            {
                return;
            }
            using (DbConnection conn = OpenConnection())
            using (DbTransaction tran = conn.BeginTransaction())
            {
                DbCommand cmd = DbFactory.CreateCommand();
                cmd.Connection = conn;
                cmd.Transaction = tran;
                PopulateAppointmentParameters(cmd, appointmentToInsert);
                cmd.CommandText =
                    @" INSERT INTO [Schedules]
                                   ([Subject], [Start], [End],
                                    [RecurrenceRule], [RecurrenceParentID],
                                    [Email], [LastModified], [Description], [ProjectId], [TicketId], [QuicklookId], [Reminder], [AppointmentColor])
                          VALUES (@Subject, @Start, @End,
                                  @RecurrenceRule, @RecurrenceParentID,
                                  @Email, @LastModified, @Description, @ProjectId, @TicketId, @QuicklookId, @Reminder, @AppointmentColor)";
                if (DbFactory is SqlClientFactory)
                {
                    cmd.CommandText += Environment.NewLine + "SELECT SCOPE_IDENTITY()";
                }
                else
                {
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SELECT @@IDENTITY";
                }
                int identity = Convert.ToInt32(cmd.ExecuteScalar());

                foreach (string resType in resourceKeys)
                {
                    FillSchedResources(appointmentToInsert, cmd, identity, resType);
                }

                tran.Commit();
            }
        }

        public override void Update(RadScheduler owner, Appointment appointmentToUpdate)
        {
            if (!PersistChanges)
            {
                return;
            }
            using (DbConnection conn = OpenConnection())
            {
                using (DbTransaction tran = conn.BeginTransaction())
                {
                    DbCommand cmd = DbFactory.CreateCommand();
                    cmd.Connection = conn;
                    cmd.Transaction = tran;
                    PopulateAppointmentParameters(cmd, appointmentToUpdate);
                    cmd.Parameters.Add(CreateParameter("@ScheduleId", appointmentToUpdate.ID));
                    cmd.CommandText =
                                  @"UPDATE [Schedules] SET [Subject] = @Subject, [Start] = @Start, [End] = @End,
                                        [RecurrenceRule] = @RecurrenceRule, [RecurrenceParentID]= @RecurrenceParentID,
                                        [Email] = @Email, [LastModified] = @LastModified, [Description] = @Description,
                                        [ProjectId] = @ProjectId, [TicketId] = @TicketId, [QuicklookId] = @QuicklookId,
                                        [Reminder] = @Reminder, [AppointmentColor] = @AppointmentColor WHERE [ScheduleId] = @ScheduleId";
                    cmd.ExecuteNonQuery();
                    ClearSchedResources(appointmentToUpdate.ID, cmd);
                    foreach (string resType in resourceKeys)
                    {
                        FillSchedResources(appointmentToUpdate, cmd, appointmentToUpdate.ID, resType);
                    }
                    tran.Commit();
                }
            }
        }

        public override void Delete(RadScheduler owner, Appointment appointmentToDelete)
        {
            if (!PersistChanges)
            {
                return;
            }
            using (DbConnection conn = OpenConnection())
            {
                DbCommand cmd = DbFactory.CreateCommand();
                cmd.Connection = conn;
                using (DbTransaction tran = conn.BeginTransaction())
                {
                    cmd.Transaction = tran;
                    ClearSchedResources(appointmentToDelete.ID, cmd);
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add(CreateParameter("@ScheduleId", appointmentToDelete.ID));
                    cmd.CommandText = "DELETE FROM [Schedules] WHERE [ScheduleId] = @ScheduleId";
                    cmd.ExecuteNonQuery();
                    tran.Commit();
                }
            }
        }
    }

[Boyan Dimitrov]

Hello,

I would like to clarify that you do not need to use the DBSchedulerProviderBase in your code in order to use the entity framework in your custom provider. You can simply create a custom class lets say EntityProvider that inherits the SchedulerProviderBase. In this class implementation you just need to override the CRUD operations.

public class CustomEntityProvider: SchedulerProviderBase

    {

        #region Appointments

         

      public override IEnumerable<Appointment> GetAppointments(ISchedulerInfo shedulerInfo)

    {

 

        List<Appointment> appointments = new List<Appointment>();

         using (var db = new SchedulerEntities())

            {

              //here goes the logic for retrieving the appointments

            }

            return appointments ;

     }
//here goes the logic for the rest opreations


....

}

You can pass this custom provider as shown:

public WebServiceController Controller
   {
       get
       {
           return new WebServiceController(new CustomEntityProvider());
       }
   }

Regards,
Boyan Dimitrov
Telerik