Access Custom SQL Database in Sitecore context

Question

I'm using Sitecore 9.1.1.

I have created a local custom database that have some tables with some records on SQL server. I need to programmatically insert and retrieve some records from this database on some triggered action. However, I'm not sure where I should define the connectionstring for this database. I tried adding the connection string to the App_Config/connectionstrings.config file as below.

In my code I tried accessing my new database as follows:

var customdb = Sitecore.Configuration.Factory.GetDatabase("CustomDB");

However this gave me the below error

{"Could not find configuration node: databases/database[@id='CustomDB']"}

I'm not sure if I should define my database and its connectionstring somewhere else or should I try connecting to my custom database using EF?

Answer 1

You should not use the Sitecore API to connect to non-Sitecore databases. They are not designed to do that.

Use EF or any other way you prefer - anything that you would use in a project that has no Sitecore.

The connectionstring can stay in the connectionstrings.config where you have it now.

Answer 2

It is very easy to do with Entity Framework:

1) Add CustomDb in connectionstrings.config (as you did)

2) Install Entity Framework nuget package in your project:

3) Create new DbContext by providing connections string name and map your database tables to models:

    public class DatabaseContext : DbContext
    {
        public DatabaseContext() : base(ConfigurationManager.ConnectionStrings["CustomDb"].ConnectionString)
        {

        }

        public DbSet<Room> Rooms { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Room>().ToTable("Room");
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            base.OnModelCreating(modelBuilder);
        }
    }

4) Initialize your DbContext in ServicesConfigurator:

    public class DbServicesConfigurator : IServicesConfigurator
    {
        public void Configure(IServiceCollection serviceCollection)
        {
            serviceCollection.AddTransient<DatabaseContext>();
        }
    }

<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
    <sitecore>
        <services>
            <configurator type="Sitecore.Foundation.Dashboard.DbServicesConfigurator , Sitecore.Foundation.Dashboard"/>
        </services>
    </sitecore>
</configuration>

That`s all! Now you can work with your custom database. Example:

    public class TestService : ITestService 
    {
        private readonly DatabaseContext _context;

        public RoomService(DatabaseContext context)
        {
            _context = context;
        }

        public List<Room> GetAll()
        {
            return _context.Rooms.ToList();
        }
}

Answer 3

If you are using custom database, treat this as a normal ASP.Net MVC/core application and write the code accordingly.

You can add your connection string inside the web.config and use normal C# code to connect the database.

You can use SqlConnection class to connect database.

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection?view=netframework-4.8

Answer 4

It is recommended to handle custom DB in different ways instead of using Sitecore APIs. However, need may arise to use dbs (other than default Sitecore Dbs) e.g. for having different publishing targets etc. In that case, after adding the DB in connection string, you also need to add database to your configurations as below -

<?xml version="1.0" encoding="utf-8" ?> <configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">    <sitecore>  <databases>
    <!-- Custom web db-->
        <database id="<custom db name>" patch:after="database[@id='web']" singleInstance="true" type="Sitecore.Data.DefaultDatabase, Sitecore.Kernel">
             <param desc="name">$(id)</param>
             <icon>Images/database_web.png</icon>
             <securityEnabled>true</securityEnabled>
             <dataProviders hint="list:AddDataProvider">
                 <dataProvider ref="dataProviders/main" param1="$(id)">

Answer 5

If your custom database is all custom and created by yourself, then you should use other SQL connectors like EntityFramework, NHibernate etc, as per the answers above.

However if the database is copied over as an existing Sitecore database for some reason (for example, you want to store UGC as items to take advantage of Sitecore's workflow engine), then you might need to patch these config nodes, in additional to the connection string:

• eventQueueProvider
• PropertyStoreProvider
• databases (mandatory)