Database Blog Provider

BlogEngine.NET includes a Database Blog Provider (DbBlogProvider) in case you prefer to store data in a database rather than the default XML provider. The DbBlogProvider is capable of storing and retrieving data in any database which supports standard SQL. This includes, but is not limited to, MS SQL Server, MySql, SQLite and Vista DB.

Instructions for setting up BlogEngine.NET for each of these databases is included in the setup folder. In the setup folders, you should find a readme.txt file with complete instructions for upgrading and initial setup. A brief walkthrough of making the switch to a MS SQL Server database follows.

Preparing your SQL Server Database

Setup is still a little bit rough, but easy enough to do if you are familiar with Microsoft SQL Server. The first thing you need to do is to create the data structure. You can put the BlogEngine.NET tables in a new database or create them in an existing database.

The script to create the tables and initial values is included in the setup folder of the web project. You will need to run this script against the database you want to create the tables in. Once the script has completed, you should have a number of new database tables with names beginning with be_ (e.g. be_Posts, be_Settings, etc). The script also inserts some initial data into some of the tables.

Now that you you have the tables setup and the initial values in place, you should think about security while you are here. (How will you connect to the database?) One method is to create a SQLUser and give it access. The database user will only need basic access. Adding the database user to the db_datareader and db_datawriter roles will be sufficient.

Setting up BlogEngine.NET for SQL Server

There are two basic changes that need to be made in BlogEngine.NET.

First, you need to open the web.config file in the root folder. Find the BlogEngine section:

<BlogEngine>
  <blogProvider defaultProvider="XmlBlogProvider">
    <providers>
      <add name="XmlBlogProvider"
           type="BlogEngine.Core.Providers.XmlBlogProvider, BlogEngine.Core"/>
      <add name="DbBlogProvider"
           type="BlogEngine.Core.Providers.DbBlogProvider, BlogEngine.Core"
           connectionStringName="BlogEngine" />
    </providers>
  </blogProvider>
</BlogEngine>
The only change here is to tell BlogEngine.NET that the "defaultProvider" is DbBlogProvider. Make it look like this:

<BlogEngine>
  <blogProvider defaultProvider="DbBlogProvider">
    <providers>
      <add name="XmlBlogProvider"
           type="BlogEngine.Core.Providers.XmlBlogProvider, BlogEngine.Core"/>
      <add name="DbBlogProvider"
           type="BlogEngine.Core.Providers.DbBlogProvider, BlogEngine.Core"
           connectionStringName="BlogEngine" />
    </providers>
  </blogProvider>
</BlogEngine>
You will also need to add your connection string to the web.config. The connection string name you define should match the name specified above. In this example, the connection string name is BlogEngine.

<connectionStrings>
  <add name="BlogEngine"
   connectionString="Data Source=MySQLServer;User ID=user;Password=password;Initial Catalog=BlogEngine;"
   providerName="System.Data.SqlClient"/>
</connectionStrings>

Running it

That is all you need to do. Pull up your blog in the browser and you should have at least one blog post titled "Welcome to BlogEngine.NET". This post should have been inserted into the database when you ran the setup script earlier.

If you have problems with it, please post on the discussion board and someone should be able to answer your question.

Last edited Mar 11, 2009 at 6:53 AM by BenAmada, version 2

Comments

shodson May 31 at 11:40 PM 
Do we need to set the fileStoreProvider attribute to "DbBlogProvider" also or leave it as "XmlBlogProvider"?

m_asolkar Aug 14, 2013 at 3:54 PM 
Hi, Just wanted to Know how to use DSN to connect the BlogEngine SQL Server DB?

donrolling Feb 18, 2013 at 8:34 PM 
Here are two helpful points to address firepol's question:
1. In order to change the membership and role providers to also use the database, do the following:
Web.config:
<membership defaultProvider="SqlMembershipProvider">
<roleManager defaultProvider="SqlRoleProvider" enabled="true" cacheRolesInCookie="false">
2. In order to use the membership stuff, you'll have to run a command on the database that will create the procs and tables that MS Membership requires. This can be done by following the instructions here: http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx, or just by running aspnet_regsql.exe in C:\WINDOWS\Microsoft.NET\Framework\[Correct Framework Folder]

firepol Jun 7, 2012 at 4:57 PM 
in this tutorial you forgot to explain how to change the membership (& role etc.) defaultProvider. Please update it so that the new user can proceed and do that as well. Else a new user will end up to have the users saved in the xml file, and the rest of the blog in the database...

robertlewis2001 Sep 27, 2011 at 8:53 PM 
Thank you so much. Worked perfectly.

msewell57 Oct 25, 2010 at 10:49 PM 
When I switched to SQL Server, I no longer get any comment moderation options in the admin pages. What gives?

vivekthangaswamy Sep 28, 2010 at 3:18 PM 
"Your login attempt was not successful. Please try again" getting this err :(

jaydee777 Dec 1, 2009 at 1:11 AM 
I am getting the following error: Parser Error Message: Could not load type 'BlogEngine.Core.Providers.MSSQLBlogProvider' from assembly 'BlogEngine.Core'.

tehremo Jul 4, 2009 at 8:15 PM 
Worked like a charm, thanks for the script!