Sitecore read from Custom Database

It is very easy to read from a custom database in your Sitecore code.

STEP 1: ADD THE CONNECTIONSTRING TO CONNECTIONSTRINGS.CONFIG

This is an example of a .config file that transforms the connection strings for existing Sitecore databases (like the core, master, web etc.) and includes a connection string to a custom database.

<connectionStrings xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
  <add name="core" connectionString="Data Source=xxx;Initial Catalog=xxx_Core;User ID=xxx;Password=xxx" xdt:Locator="Match(name)" xdt:Transform="SetAttributes(connectionString)" />
  <add name="master" connectionString="Data Source=xxx;Initial Catalog=xxx_Master;User ID=xxx;Password=xxx" xdt:Locator="Match(name)" xdt:Transform="SetAttributes(connectionString)" />
  <add name="web" connectionString="Data Source=xxx;Initial Catalog=xxx_Web;User ID=xxx;Password=xxx" xdt:Locator="Match(name)" xdt:Transform="SetAttributes(connectionString)" />
  ...
  ...
  <add name="CustomDatabase" connectionString="Data Source=xxx;Initial Catalog=xxx_CustomDatabase;User ID=xxx;Password=xxx" providerName="System.Data.SqlClient" xdt:Locator="Match(name)" xdt:Transform="InsertIfMissing"/>
  ...
  ...
</connectionStrings>

STEP 2: GET THE CONNECTIONSTRING FROM Sitecore.Configuration.Settings:

Reading the connection string is easy:

private static readonly string _connectionString =
    Sitecore.Configuration.Settings.GetConnectionString("CustomDatabase");

STEP 3: DO YOUR MAGIC:

This is an example of a class that uses Dapper to read data from a custom database:

using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace MyCode
{
  internal static class MyRepository
  {
    private static readonly string _connectionString = Sitecore.Configuration.Settings.GetConnectionString("CustomDatabase");

    internal static IEnumerable<CustomData> Get(int customKey)
    {
      using (var conn = new SqlConnection(_connectionString))
      {
        var trail = conn.Query<CustomData>(Constants.StoredProcedures.Get, new { CustomKey = customKey }, commandType: CommandType.StoredProcedure);
        return trail;
      }
    }
  }
}

MORE TO READ:

About briancaos

Developer at Pentia A/S since 2003. Have developed Web Applications using Sitecore Since Sitecore 4.1.
This entry was posted in c#, General .NET, Sitecore, Sitecore 6, Sitecore 7, Sitecore 8, Sitecore 9 and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.