After a few requests, I decided to go back and do another data provider for BlogEngine.NET.  This time around I’m using SQLite.  SQLite is a file based format very similar to VistaDB Express and it has no restrictions .  It is another fine option for blog data and very easy to work with.

Once again, this is a complete provider setup.  It will store all your blog posts, pages, profiles, users, roles, and settings.  It could be partially implemented by making changes to the default providers in your web.config if you desire.

The process for getting setup is very easy.  The instructions are assuming you have BlogEngine.NET setup already.  If you don’t please check out the initial setup screen cast to walk you through the process.  If you already have an existing blog, be sure to backup before following these steps.

1. Download the SQLite database provider for BlogEngine and unzip it.

2. Download the SQLite ADO.NET Provider binaries.

3. In your SQLite ADO.NET download, find the System.Data.SQLite.DLL and add it to your blog’s bin folder.

4. From the BlogEngine download, copy the BlogEngine.s3db file to your blog’s App_Data folder.

5. Next, replace your blog’s web.config file with the new web.config file from the BlogEngine download.

6. Browse out to your blog and you should see the BlogEngine with SQLite welcome post. 

7. Your User security has been reset, so login as Admin (with the password, admin), add new users, and change your password.

That is it.  You should be now blogging with data stored in SQLite.

If you are reading this blog regularly, you are now likely tired of the database themes going on this week.  First, it was SQL Server and BlogEngine.  Then VistaDB Express and BlogEngine.NET complete with download.  Today, I’m moving on to MySql.

Let me start this post by admitting right up front that I have almost zero experience with it.  I didn’t even get it installed until last week and that was just to make sure the BlogEngine.NET could work with it.  So instead of completely embarrassing myself by doing a screencast with something I know nothing about, I figured I’d just write up a guide on how to do get started with BlogEngine and MySql and embarrass myself with text.

Requirements

I’m expecting you have BlogEngine.NET installed with the default settings and providers.  If you need help getting to this point, you should check out my initial installation screencast.

Second, I’m expecting you have a MySql database setup and some administration tool to work with.  If you need help getting to this point, I wish you the very best. :)  Actually, I was truly miserable until Ruslan Tur recommended Toad for an admin tool.  This made my experience much, much better.

Creating a Database

You’ll need to create a database or have a database to put the BlogEngine table in.  I have created a script to get the initial tables and data created for you.  Just execute it against the database you want to use for BlogEngine.NET.  Download the script here.

Add the MySQL dll to your bin folder

You’ll need to download the MySQL Connector/NET 5.1, find the MySql.Data.dll, and added to your bin folder.

Update your web.config

I’m including a sample web.config for you to download, but I’ll go through the changes here. (Download Sample web.config.)

First, we need to let BlogEngine know about this new MySQL dll we just added.  This is done by adding the assembly to the list of assemblies and then adding in the system.data section to let BlogEngine know where it will find the MySQL DBProviderFactory.

   1: <system.data>

   2:     <DbProviderFactories>

   3:         <clear />

   4:         <add name="MySQL Data Provider" 

   5:              invariant="MySql.Data.MySqlClient" 

   6:              description=".Net Framework Data Provider for MySQL"  

   7:              type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=5.1.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />

   8:     </DbProviderFactories>

   9: </system.data>

  10: <system.web>

  11:     <compilation debug="false">

  12:         <assemblies>

  13:             <add assembly="MySql.Data, Version=5.1.6.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D"/>

  14:             <add assembly="System.Management, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>

  15:             <add assembly="System.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>

  16:             <add assembly="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>

  17:             <add assembly="System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>

  18:             <add assembly="System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>

  19:             <add assembly="System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>

  20:             <add assembly="System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>

  21:         </assemblies>

  22:     </compilation>

  23:     < ....snip..../>

  24: </system.web>

Then, you will need to add your connection string to your connectionString section.

   1: <connectionStrings>

   2:         <clear/>

   3:         <add name="LocalSqlServer" connectionString="dummy"/>

   4:         <!-- Mono complains if LocalSqlServer isn't specified -->

   5:         <add name="BlogEngine" 

   6:              connectionString="Data Source=MySQLServer;User ID=user;Password=password;persist security info=False;initial catalog=BlogEngine;" 

   7:              providerName="System.Data.SqlClient"/>

   8:         <add name="MySQLDB" 

   9:              connectionString="Server=localhost;Database=blogengine;Uid=beUser;Pwd=beUser;" 

  10:              providerName="MySql.Data.MySqlClient"/>

  11:     </connectionStrings>

Lastly, we need to link up the BlogProvider, the MembershipProvider, the RoleProvider, and the ProfileProvider.  The BlogProvider section is near the top of your web.config in a BlogEngine section.  The other 2 can be found in in succession down much further in the file.

   1: <BlogEngine>

   2:     <blogProvider defaultProvider="DbBlogProvider">

   3:         <providers>

   4:             <add name="XmlBlogProvider" 

   5:                  type="BlogEngine.Core.Providers.XmlBlogProvider, BlogEngine.Core"/>

   6:             <add name="DbBlogProvider" 

   7:                  type="BlogEngine.Core.Providers.DbBlogProvider, BlogEngine.Core" 

   8:                  connectionStringName="MySQLDB" 

   9:                  parmPrefix="?"/>

  10:         </providers>

  11:     </blogProvider>

  12: </BlogEngine>

   1: <profile enabled="true" defaultProvider="DbProfileProvider" inherits="BlogEngine.Core.Web.AuthorProfile" automaticSaveEnabled="false">

   2:     <providers>

   3:         <add name="XmlProfileProvider" 

   4:              type="BlogEngine.Core.Providers.XmlProfileProvider, BlogEngine.Core"/>

   5:         <add name="DbProfileProvider" 

   6:              type="BlogEngine.Core.Providers.DbProfileProvider, BlogEngine.Core" 

   7:              connectionStringName="MySQLDB" 

   8:              parmPrefix="?" />

   9:     </providers>

  10: </profile>

  11: <membership defaultProvider="DbMembershipProvider">

  12:     <providers>

  13:         <clear/>

  14:         <add name="XmlMembershipProvider" 

  15:              type="BlogEngine.Core.Providers.XmlMembershipProvider, BlogEngine.Core" 

  16:              description="XML membership provider" 

  17:              passwordFormat="Hashed"/>

  18:         <add name="SqlMembershipProvider" 

  19:              type="System.Web.Security.SqlMembershipProvider" 

  20:              connectionStringName="BlogEngine" 

  21:              applicationName="BlogEngine"/>

  22:         <add name="DbMembershipProvider" 

  23:              type="BlogEngine.Core.Providers.DbMembershipProvider, BlogEngine.Core" 

  24:              passwordFormat="Hashed" 

  25:              connectionStringName="MySQLDB" 

  26:              parmPrefix="?" />

  27:     </providers>

  28: </membership>

  29: <roleManager defaultProvider="DbRoleProvider" enabled="true" cacheRolesInCookie="true" cookieName=".BLOGENGINEROLES">

  30:     <providers>

  31:         <clear/>

  32:         <add name="XmlRoleProvider" 

  33:              type="BlogEngine.Core.Providers.XmlRoleProvider, BlogEngine.Core" 

  34:              description="XML role provider"/>

  35:         <add name="SqlRoleProvider" 

  36:              type="System.Web.Security.SqlRoleProvider" 

  37:              connectionStringName="BlogEngine" 

  38:              applicationName="BlogEngine"/>

  39:         <add name="DbRoleProvider" 

  40:              type="BlogEngine.Core.Providers.DbRoleProvider, BlogEngine.Core" 

  41:              connectionStringName="MySQLDB" 

  42:              parmPrefix="?" />

  43:     </providers>

  44: </roleManager>

Configuration

Once you have these changes made, you can browse out to your web site and you should be greeted by a new welcome message.  Just as with the SQL Server setup, you’ll be missing your widgets.  So you can login with user “admin” and password “admin”.  Add your Administration widget and then refresh your page to see the full menu.

At this point, you should add users and change the admin password.

It really is fairly easy to get set up especially since you can copy and paste the new sections out of my sample web.config.  I also wanted to start that I save a bit of time getting the install script done by starting with the script I found as part of this post by Mr. Wize.  I added the new tables and initial data, but the initial work was done by him.

Now that the latest version of BlogEngine.NET is out, it is easier than ever to get your blog running with VistaDB Express.  I’ve been using VistaDB Express with BlogEngine.NET for a little while now and just love it.  I’ve become a fan of the lightweight, file based database for a single user blog.  I think it is a perfect way to store your blog data.  Enough of my gushing already, let’s get on the real info.

Getting BlogEngine.NET 1.4 to work with VistaDB is super easy, but will require you to upload a few more files.  I’ve put together a new screencast that will walk you through the steps, show you exactly what to do and what changes are being made.

It is incredibly simple however.  You simply download the BlogEngine 1.4 VistaDB Express Pack, unzip it, upload 3 files, and enjoy the goodness that is VistaDB Express and BlogEngine.NET 1.4.  In the screencast, I also take a little time to walk you through the main changes made to the web.config to make this work.

If you are starting a new BlogEngine.NET blog, I strongly recommend it.  If you already have a BlogEngine.NET blog and want to convert over to it, I’ll get to that soon.  I promise. :)

I found a small bug in my initial release of the VistaDB providers DLL that I released on Friday.  I have made the correction and updated the download.  If you are already using the VistaDB provider, it is only the BlogEngine.VistaDB.dll file that needs to be updated in your site.

Please download the latest version to get the update.

It is a day later than I had hoped, but I now have my blog running with a VistaDB back end.  I'm thrilled. I've never been a huge fan of putting my personal blog data in SQL Server and I feel free.

I had never intended to move my data to VistaDB Express when I started work for this presentation a few weeks ago, but after working with it for a while and making the providers, I realized I really wanted to use it for my data storage.  VistaDB has a lot going for it as it is managed code, runs in medium trust, runs on mono and is very lightweight.  I'm really most pleased with the xcopy deployment and the nice graphical management tool.  I have heard from others that speed might be an issue, but it seems fine with the small tests I've run.  Inside BlogEngine with the caching we do, I don't see it as an issue.

As I was making some small changes to the code to make it more secure and getting it ready to make available, I decided to go for it.  I fired up my rough migration tool, made a few small changes, and I had a complete copy of my blog posts, pages, comments, settings, and other stuff in a VistaDB database.  I had to manually add my user name in, but since I'm single user it was no big deal.  My small blog database is less than 1 MB and even easier to copy around than a few directories full of XML files.

If you are interested in playing around with it, or following in my footsteps to using VistaDB Express for your blog data, I'm making it all available now.  Complete with instructions.  If you wish to use it, you are required to meet the license agreement of VistaDB Express which states that it is for non-commercial use.  (If you are a commercial operation, you can purchase VistaDB and then use it with BlogEngine without restriction.)

• Download this file: BlogEngine131VistaDB33.zip
• Unzip the file and place the 2 dll files in your blog's bin folder.  (Note: one is the VistaDB Express dll and one is the BlogEngine VistaDB providers.)
• Place the BlogEngine.* files in your blog's App_Data folder.  These 2 files are your initial VistaDB database.
• Make changes to your web.config to let BlogEngine know to use VistaDB.  I've included samples of the relevant areas below.
• As per the VistaDB Express license, you should place a link back to VistaDB somewhere on your blog.  (I placed mine in my footer.)

Down under system.web

That is it.  If you are interested in checking out the VistaDB database or want to encrypt the database with a password, you can download VistaDB Express.  (User passwords are encrypted inside the database in the User table, but you can encrypt the whole file if you'd like.)  The initial database has the default settings and the initial welcome post you come to expect.