Kristoffer Lindén
Feb 7, 2022
  3973
(0 votes)

Attach local database using .NET Core and CMS 12

In my CMS 11 solution I used the variable |DataDirectory| to point out the App_Data folder where my database file is placed and that workd just fine. Is Net Core I tried to use the same:

"ConnectionStrings": {
    "EPiServerDB": "Data Source=(LocalDb)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\EPiServerDB_f6c7a112.mdf;Initial Catalog=EPiServerDB_f6c7a112;Connection Timeout=60;Integrated Security=True;MultipleActiveResultSets=True"
  }

In CMS 12 using Net Core, the |DataDictionary| does not work anymore and need to be replaced in some way. I found this that I thought was my solution:

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
    string baseDir = env.ContentRootPath;
    AppDomain.CurrentDomain.SetData("DataDirectory", System.IO.Path.Combine(baseDir, "App_Data"));
}

What happend was that when calling ConfigureCmsDefaults() which is done in Program.cs, Episerver tries to connect to the database but the connectionstring is not modified so that fails. A strange behavior was that the code then never reached the Configure(...) method and I could never understand why until I set the real physical path to the database in the connectionstring.

"ConnectionStrings": {
    "EPiServerDB": "Data Source=(LocalDb)\\MSSQLLocalDB;AttachDbFilename=D:\\Projekt\\Web\\App_Data\\EPiServerDB_f6c7a112.mdf;Initial Catalog=EPiServerDB_f6c7a112;Connection Timeout=60;Integrated Security=True;MultipleActiveResultSets=True"
  }

Then everything worked just fine, but is doesn't look to good and finally I found a solution using PostConfigure.

Connectionstring looks like this:

"ConnectionStrings": {
  "EPiServerDB": "Data Source=(LocalDb)\\MSSQLLocalDB;AttachDbFilename=App_Data\\EPiServerDB_f6c7a112.mdf;Initial Catalog=EPiServerDB_f6c7a112;Connection Timeout=60;Integrated Security=True;MultipleActiveResultSets=True",
}

And ConfigureServices like this:

public void ConfigureServices(IServiceCollection services)
{
        services.PostConfigure<DataAccessOptions>(o =>
        {
            o.SetConnectionString(_configuration.GetConnectionString("EPiServerDB").Replace("App_Data", Path.GetFullPath("App_Data")));
        });
        services.PostConfigure<ApplicationOptions>(o =>
        {
            o.ConnectionStringOptions.ConnectionString = _configuration.GetConnectionString("EPiServerDB").Replace("App_Data", Path.GetFullPath("App_Data"));
        });
}

In this way the connectionstring is modified before Episerver is initiated and the database will be attached correctly.

Feb 07, 2022

Comments

Vincent
Vincent Feb 8, 2022 12:24 AM

Hi Kristoffer

Thanks for sharing.

It's a bit strange you need to use PostConfigure for this. I don't find any issue by using Configure to replace the token. In addition, if your solution is CMS only, I found out there is DefaultConnectStringName property you can use instead of hard-coded EPiServerDB. The following snippet is my solution that I'm planning to post in my blog :)

My connectionstring in appsettings.Development.json

  "ConnectionStrings": {
    "EPiServerDB": "Server=(LocalDb)\\MSSQLLocalDB;AttachDbFilename=%CONTENTROOTPATH%\\APP_DATA\\foundation-mvc-net5.Cms.mdf;Initial Catalog=foundation-mvc-net5.Cms;Connection Timeout=60;Integrated Security=True;MultipleActiveResultSets=True"
  },

My startup ConfigureService class changes

 public void ConfigureServices(IServiceCollection services)
        {
            services.Configure<DataAccessOptions>(o =>
            {
                o.ConnectionStrings.Add(new ConnectionStringOptions
                {
                    ConnectionString = _configuration.GetConnectionString(o.DefaultConnectionStringName).Replace(ContentRootPathToken, _webHostingEnvironment.ContentRootPath),
                    Name = o.DefaultConnectionStringName
                });
            });



            services.AddCmsAspNetIdentity<SiteUser>();
...........
}

Kristoffer Lindén
Kristoffer Lindén Feb 8, 2022 08:49 AM

Hi Vincent,

Thanks for the update. I have actually not tried the Configure, the solution I found used PostConfigure so that is the only option I have tried. I will try with Configure and let you know.

Johan Petersson
Johan Petersson Feb 8, 2022 09:10 AM

You can also remove MultipleActiveResultSets=True from the connection string if you're running latest version of CMS Core. You should see a warning in the logs if MARS is enabled but not needed.

Vincent
Vincent Feb 8, 2022 10:39 PM

Hi Johan

Interesting suggestion. Do you mean remove MAR setting for LocalDb scenario or any type of dbs? Any side effect with this?

Thanks

Vincent
Vincent Feb 9, 2022 02:10 AM

Hi Johan

In fact, the MARS setting seems required in this article Initial configuration | Optimizely Developer Community

Johan Petersson
Johan Petersson Feb 9, 2022 08:32 AM

The documentation needs to be updated. We only support SQL Server, so not any type of database. MARS causes issues on SQL Server on Linux. There's no issues having it enabled on Windows.

Please login to comment.
Latest blogs
Creating Custom Feed Options for Optimizely Forms Selection Elements

Optimizely Forms is a versatile tool for building forms used in registrations, job applications, surveys, and much more. These forms can include a...

Nahid | Aug 12, 2024

How to hide Reports from the global menu in Optimizely CMS

Reports section is available by default to all users who are members of the WebEditors group as the authorization policy of that MenuItem is set to...

Bartosz Sekula | Aug 8, 2024 | Syndicated blog

Azure Function App for PDF Creation With Syncfusion .Net PDF Framework

We have a couple of use cases that require internal and external users to print content in PDF format. We've offloaded this functionality from the...

KennyG | Aug 8, 2024

Changes to PictureRenderer.Optimizely

In version 3.2 of PictureRenderer.Optimizely it is possible to add any attribute to the rendered img element. This makes it possible to add e.g....

Erik Henningson | Aug 2, 2024 | Syndicated blog

Rolling Restart Revolution!

I'm not sure if anyone has written about this yet or not. If so, I'm sorry. But we discovered something this morning where the heavens opened up an...

KennyG | Aug 1, 2024

Optimizely SaaS CMS Content Modeling

Content modeling for websites can be considered as much an art as a science. While we may look at content modeling as a process of defining and...

Patrick Lam | Aug 1, 2024