Vista DB to SQL_CE

Topics: ASP.NET 2.0, Business Logic Layer
Nov 24, 2010 at 1:36 PM

I am not clear on the following:

Will it be possible to upgrade to BE 2.0 from a BE 1.6 using Vista DB Express installation  to BE 2.0 and convert the Vista DB to SQL_CE.

I would like to discontinue Vista DB in case something fails in the future with no support offered and also take advantage of SQL_CE interfacing with Visual Web Developer 2010 Express.

Thank you and hoping.

Jerry Dean





Nov 24, 2010 at 2:58 PM

You should be able to export your 1.6 Vista DB data to BlogML and then import it into 2.0. There may be minor data loss in transition, as BlogML does not support some features, but it worse trying first - my work for you just fine. And if it not, report the issue and we'll try to fix it for final 2.0 release.

Nov 24, 2010 at 5:32 PM

In addition to BlogML, another method is the BlogEngine.NET Provider Migration tool.  It's a little bit out of date, and could probably be updated, but I'm guessing probably works even right now.

Nov 24, 2010 at 5:37 PM

Thank you, I believe I have tried this in the past and it works for blog posts but not the user database which is what I need to convert.

Nov 24, 2010 at 7:31 PM

You're probably right that that tool doesn't deal with the users or roles.  It's probably possible a similar migration tool could be created that works the same way to migrate the users and roles.

Nov 25, 2010 at 7:38 PM

Ben, do you know if the Migration Tool support SQL_CE as the destination for the data?

Thank you.

Nov 25, 2010 at 7:52 PM

Untested, but it looks 100% like it should work.  I'm looking at the migration tool post, and as long as you have the old provider and new provider added in the <providers> section, with the correct connection strings, it should work.

The migration tool uses the providers already built into BE to copy the data.  Because the DbBlogProvider in BE handles SQL CE, the migration tool will just use this DbBlogProvider to access the SQL CE database.

Nov 25, 2010 at 8:02 PM

The issue I am having is that I have setup BE 2.0 with SQL_CE in VWD and it is working, I am at a loss as how I can use SQL_CE in my existing BE 1.6 installation with Vista DB, the way I see it I have to have the receiving DB setup in 1.6 and SQE_CE won't work in 1.6.

I cannot see a way to get the blog data from 1.6 and Vista DB into BE 2.0 with SQL_CE.  Any ideas?

Nov 25, 2010 at 8:18 PM

Yes, I see the catch 22.  I think the easiest path will be to get VistaDB working in BE 2.0 so you can use the migration tool.

All that is needed is to add some new columns into some VistaDB tables, and to create 2 new tables.

You can use the SQL Server upgrade script to know what you need to add to the VistaDB DB.  For reference, what's new is:

be_PostComment table, 2 new columns
 - IsSpam, bit (default 0)
 - IsDeleted, bit (default 0)

be_Posts table, 1 new column1
 - IsDeleted, bit (default 0)

be_Pages table, 1 new column1
 - IsDeleted, bit (default 0)

NEW table: be_Rights
 - RightName, nvarchar(100), Primary Key
NEW table: be_RightRoles
 - RightName, nvarchar(100), Primary Key
 - Role, nvarchar(100), Primary Key
You could manually create those new columns and tables using the VistaDB management tool.  I'm actually not sure what that even looks like, as I've not used VistaDB before.  But looking at the last VistaDB upgrade script (for v1.5 to 1.6), and how the syntax for that works, my guess is that the following script would work in VistaDB to add the additional columns and tables to it to bring it up to BE 2.0.

ALTER TABLE dbo.be_PostComment ADD IsSpam bit NOT NULL DEFAULT '0';
ALTER TABLE dbo.be_PostComment ADD IsDeleted bit NOT NULL DEFAULT '0';

ALTER TABLE dbo.be_Posts ADD IsDeleted bit NOT NULL DEFAULT '0';
ALTER TABLE dbo.be_Pages ADD IsDeleted bit NOT NULL DEFAULT '0';

CREATE TABLE [be_Rights]
[RightName] NVARCHAR(100) NOT NULL

ALTER TABLE [be_Rights]
CONSTRAINT [Primary Key] PRIMARY KEY ([RightName]);

CREATE TABLE [be_RightRoles]
[RightName] NVARCHAR(100) NOT NULL,

ALTER TABLE [be_RightRoles]
CONSTRAINT [Primary Key] PRIMARY KEY ([RightName], [Role]);

Nov 25, 2010 at 8:34 PM

Thanks Ben, I will dapple with this and see what happens, what about the option of using SQL_CE in 1.6 and using the migration tool?

Nov 25, 2010 at 8:48 PM

That's a little more difficult, because the DbBlogProvider in 1.6 isn't fully compatible with SQL CE.  The DbBlogProvider is in the BE core, and making some updates in it (in version 1.6) would be needed to get SQL CE to work in 1.6.

The DbBlogProvider in BE 2.0 will work with both SQL CE and VistaDB (once the new columns and tables have been added).

Nov 25, 2010 at 9:09 PM

I am being a pest again I know, could you refresh my old memory on how to run the script.  I copied the Vista DB into BE 2.0 App Data folder and I forgot how to proceed.  Thanks again for your precious time.

Nov 25, 2010 at 9:44 PM

One thing first that's worth mentioning (just in case), is that when you eventually deploy SQL CE to the live website, it will be best or even necessary that that website is running in a .NET 4.0 environment.  This is noted in the SQL CE ReadMe.txt file.  SQL CE can run in a .NET website regardless of the trust level (medium trust or full trust) if it's a .NET 4.0 environment.  If it's a .NET 3.5 environment, oftentimes at shared webhosts, they are running in a medium trust environment.  If your website is .NET 3.5 medium trust, then SQL CE will only run if the webhost has specifically permitted SQL CE to run.  If your website is .NET 3.5 full trust, then you will be okay.  If your website is .NET 4.0, then SQL CE will run even in medium trust.

Normally to run a DB script, you open up the DB management software (not included with BE) and run the script against the database.  For SQL Server, the management tool is Management Studio.  For MySQL, there are a few management tools.  And VistaDB has its own management tool.  I've not used the VistaDB management tool before.  It's most likely the same tool that you use to view the VistaDB tables and data.  But I'm guessing it has a way to open up a script window to be able to run a script.

Nov 25, 2010 at 9:58 PM

I decided to try and do this manually and acomlished everthing but this: 

 - IsDeleted, bit (default 0)


Vista won't let me alter this table.

be_Pages table, 1 new column1
 - IsDeleted, bit (default 0)


Nov 26, 2010 at 3:45 AM

It let you add "IsDeleted" to be_PostComments and be_Posts, but not be_Pages?  Is there any error message?  Maybe try it again, or try playing around with it (sorry, sort of nonspecific advice!)