adding custom database tables

MTM
MTM
Vote:
 

Hi, If I add customs tables to the database using code first approach, will Optimizely automatically detect this and do a migration for me or do I need to do that on my own?

#303879
Jun 21, 2023 9:14
Vote:
 

Hi,

Optimizely won't automatically detect this, 'Code First Migrations' will.

If you have it all setup and configured correctly when the solution runs the table/s will be created using entity framework.

https://docs.developers.optimizely.com/digital-experience-platform/docs/custom-database-tables

Paul

#303881
Edited, Jun 21, 2023 10:05
Vote:
 

For CMS 11, I usually make sure I have this configuration as true in the web.config file: 

 <episerver.framework createDatabaseSchema="true" updateDatabaseSchema="true">

Then, I create a Db Context class and specify the connectionstring = "EpiserverDB"

    public class NewTableNameContext : DbContext
    {
        public NewTableNameContext()
            : base("name=EPiServerDB")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {

        }

        public virtual DbSet<NewTableRecord> NewTableRecords { get; set; }
    }

Then I need to create the Entity for the table: 

    [Table("NewTableRecord")]
    public class NewTableRecord
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        [Required]
        [StringLength(50)]
        public string Title{ get; set; }
    }

Finally, I create a repository where all the CRUD operations happen:

    public class NewTableNameRepository : INewTableNameRepository  //You can create an interface
    {
        public IEnumerable<NewTableRecord> GetAll()
        {
            using (var context = new NewTableNameContext())
            {
                return context.Set<NewTableRecord>().OrderBy(x => x.Title).ToList();
            }
        }

        public NewTableRecord GetById(int id)
        {
            using (var context = new NewTableNameContext())
            {
                return context.Set<NewTableRecord>().Find(id);
            }
        }        

        public void Insert(NewTableRecord entity)
        {
            using (var context = new NewTableNameContext())
            {
                context.Set<NewTableRecord>().Add(entity);
                context.SaveChanges();
            }
        }

        public void InsertAll(IEnumerable<NewTableRecord> entities)
        {
            using (var context = new NewTableNameContext())
            {
                context.Set<NewTableRecord>().AddRange(entities);
                context.SaveChanges();
            }
        }

        public void Update(NewTableRecordentity)
        {
            using (var context = new NewTableNameContext())
            {
                context.Set<NewTableRecord>().Attach(entity);
                context.Entry(entity).State = EntityState.Modified;
                context.SaveChanges();
            }
        }
       
        public void Delete(NewTableRecordentity)
        {
            using (var context = new NewTableNameContext())
            {
                if (context.Entry(entity).State == EntityState.Detached)
                   context.Set<NewTableRecord>().Attach(entity);
                   context.Set<NewTableRecord>().Remove(entity);
                   context.SaveChanges();
            }
        }
    }

I'm using EntityFramework to work with the Database and you can use DI to inject the services. 

Hope this helps :)

#315084
Jan 04, 2024 16:53
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.