Posts in Category: Features

SQL Server Compact 4.0 and mojoPortal

On June 30, 2010, Scott Guthrie blogged New Embedded Database Support with ASP.NET, announcing that SQL CE 4 (SQL Server Compact Edition) would soon be available. It was officially released as a public CTP (Community Technology Preview) on July 7, 2010 with an announcement on the SQL Server Compact Team blog in a post by Ambrish Mishra entitled Introducing SQL Server Compact 4.0, the Next Gen Embedded Database from Microsoft.

This little database is big news for mojoPortal

Today I am happy to announce a preview release of mojoPortal for SQL CE 4 and ASP.NET 4.0. This package has a pre-configured database and can be deployed under Medium Trust with .NET 4 hosting.(*) This means it is easy to deploy and use even on budget shared hosting where Medium Trust security policy is almost always used. You can download the package from the bottom of this page on Codeplex. It is an MsDeploy package, so it can be installed by importing it in IIS, or you can just unzip it and install manually.

When installing web applications like mojoPortal, often one of the more challenging installation issues is setting up the database, for non-techies this is the part that people really struggle with, and even people who consider themselves fairly tech savvy can find themselves struggling to get it working unless they have a background in working with databases and understand all the nuances of connection strings and permissions. Using SQL CE 4 eliminates this challenge completely because the database is just a file on disk. There is no database software to install on the server, so it does not depend on the host having it installed, it only requires .NET 4 hosting. Since we ship a database file already populated with initial data, it is basically zero configuration for the database, you don't have to do anything, it just works.

I'm sure some of you are aware that mojoPortal has supported Sqlite for a long time, and it is also a zero configuration, file based database that many people like a lot. However, it has never worked under Medium Trust and if your site traffic starts to grow there is not an easy way to migrate the data to a more robust database.

  SQL CE Sqlite
Zero config deployment yes yes
Backing up the files backs up the database yes yes
Works in Medium Trust yes no
A supported migration path to a more robust database yes to SQL Express/Server/SqlAzure no convenient migration solutions that I know of


Now, I'm not yet sure yet how easy the migration of mojoPortal from SQL CE to SQL Server will be. Scott Gu mentioned that they will be shipping migration tools that will make it straight forward to migrate the tables and data from SQL CE to SQL Express, SQL Server or SQL Azure. However, since SQL CE does not support stored procedures and we do use them in the SQL Server data layer for mojoPortal, it means we will also need to make migration scripts available to install the latest stored procedures into the database after migration. So, in theory, the migration process will be to first use the tool that Microsoft will ship to migrate the tables and data, then run the scripts we will make available that contain the stored procedures for a given version of mojoPortal, then you would just deploy the mojoPortal version for SQL Server over your existing mojoPortal for SQL CE installation and set your connection string for the new database. Once the migration tools ship I will verify the process and create documentation with the migration steps.

In summary, I think that the SQL CE package for mojoPortal will make it easy for you to stand up impressive new web sites quickly and easily in budget hosting with the possibility to upscale to a more robust database platform later without too much difficulty. I would say that if you are putting up a site that you are expecting to grow traffic quickly, then you should probably just start out using SQL Express, SQL Server, or SQL Azure. But, how many sites that you put online for your customers really get a lot of traffic? If the answer is not many, then you may find SQL CE is the best choice for many of your projects, reducing costs and time to deployment.

Screen shot of mojoportal system information showing SQL CE


Top Secret Early Access! :-D

Now you may wonder how I managed to have a version of mojoPortal for SQL CE ready so soon after SQL CE 4 was released. The answer is that I had early access to SQL CE 4.0 and access to a bunch of good folks on the IIS and SQL Server teams to help me with guidance and questions and was also able to give them feedback while they were working on this release.
Back in early March I was contacted by Jonathan Hawkins of Microsoft and was invited to an online meeting where they told me what they were planning for SQL CE and why it might be beneficial for mojoPortal to support it, I of course was very excited about it.  At the time the alpha bits of SQL CE 4 were not quite ready but I was provided with good help and work arounds that would enable me to work on support for SQL CE using the 3.5 version so I could get started without waiting. So I went ahead at that time and implemented it for the core mojoPortal features (it took me about a week) and managed to get it working as proof of concept. Once that was done I moved on to work on other things not knowing how long it would be until the alpha bits of SQL CE 4.0 would be ready. Finally on June 2nd I got an email that the alpha bits were available, but I was knee deep in other projects right at the time, so it was around June 27-28 when I finally tried the new bits and verified it worked under Medium Trust. Then when Scott Gu blogged about it on June 30, I realized it was going to be public very soon so I scrambled to complete the SQL CE data layer for the rest of the mojoPortal features (except for WebStore and my Add On Products). It took about 1 week to finish that work and by Friday July 9 I was ready to produce a package but decided to wait until Monday to blog about it.
So here we are, it is Monday, and I've uploaded the package on Codeplex, at the bottom of our current release. I've done sanity testing of all the features but not exhaustive testing of every configuration of every feature, so it is possible and perhaps likely there are still a few bugs here and there in the data layer, after all this is our newest data layer and therefore the least tested of all our data layers at this point. That is why I'm calling it a "Preview" release, and I hope you will give it a try and report any problems you may encounter. I will fix them quickly.
I'd like to thank all the folks at Microsoft for giving me this opportunity to work closely with them and get early access to the technology, and I especially thank Jonathan Hawkins, Parasuraman Narasimhan, Radhakrishnan Srikanth, Mohammad Imran Siddique, Himadri Sarkar, and Ambrish Mishra for all their help. It has really been a great experience for me getting to work with these guys and do something with mojoPortal that fit well with cutting edge work these guys have done on SQL CE. I really think they have solved one of the long standing problems in ASP.NET web deployment. For low traffic sites or quick prototyping or proof of concept deployment, or just low budget web sites, this is really going to reduce friction and make it easy to put a site up in minutes. 

Technical Notes

It turned out well that I waited for SQL CE 4 before implementing all the mojoPortal features. When I implemented the core features I only had SQL CE 3.5 to work with so in places where I needed to select a page of data, I was having to use some awkward SQL syntax to make it work, it did work but it was difficult to read and write. One of the coolest new things in SQL CE 4.0 is the new syntax for selecting a page of data like this:
It would be great if someday SQL Server supports an easier paging syntax, as it is still awkward to get a page of data efficiently in SQL Server, though it is a little easier than SQL CE 3.5 since SQL Server at least gives us stored procedures which provides a few additional strategies for getting a page of data. Anyway, you can guess I like this new syntax, it is much more convenient. Probably since I've worked with so many open source database platforms in mojoPortal, and have learned the major syntax differences between them, and really most of them have had easier ways to get a page of data than SQL Server for a long time, I'm glad to see something that makes a bit more sense. In a nutshell, MySql, PostgreSql, and Sqlite all support the LIMIT x OFFSET y syntax, Firebird has an interesting twist with SELECT FIRST x SKIP y, and SQL Server has always had the SELECT TOP (x) syntax but no equivalent to OFFSET, and this makes us have to jump through syntax hoops to get any efficient way to grab a page of data. So at the moment, in my view, SQL CE 4 has at least one cool syntax thing better than its big brother SQL Server even if it is not as capable in handling large traffic.

The mojoPortal Query Tool can talk to SQL CE

The above screen shot shows that the built in mojoPortal query tool can be used with SQL CE.

No need to install it in the GAC

One of the interesting things to me is that it seems like SQL CE 4 must be all managed code, that is, I think it must be implemented purely in .NET. The reasons I think this is because it seems that the database engine must be completely contained in System.Data.SqlServerCe.dll, and this dll just needs to be in the /bin folder, it doesn't even need to be installed in the GAC (Global Assembly Cache). The main reason why Sqlite doesn't work under Medium Trust is because it does some P/Invoke against a native dll written in C. To be allowed to do that the managed dll for Sqlite would have to be installed in the GAC. Since SQL CE can work without being installed in the GAC, I can guess it does not do any P/Invoke and is all managed code. Someone asked about this in the comments of Scott Gu's post, and while he did not specifically answer it, he did mention in comments that they are looking at enabling use of SQL CE in Silverlight which I think lends more evidence to it being fully managed code. I could be wrong though, possibly there are changes in code access security in .NET 4 that makes it possible to use without installing in the GAC.

The idea of being able to use SQL CE as a client side database in Silverlight is pretty compelling to me, so I'll be keeping my eyes open for future announcements. I had once done a proof of concept where I managed to use Sqlite as a client side database in Silverlight via Google Gears, but it had a few issues because database calls had to be marshalled back and forth from javascript to Silverlight via the Html Bridge. It would be much nicer to be able to use SQL CE directly from Silverlight. That would enable some really interesting client side scenarios.

(*) Actually I think SQL CE 4 could probably be used under 3.5 .NET but not under Medium Trust, it requires .NET 4 to work under Medium Trust, and the mojoPortal package for SQL CE is only going to be available for .NET 4.

Consolidation of Data Layers

At the moment we have a separate data layer for SqlAzure, SQL Server, and SQL CE. Back in March when I first began work on SQL CE support in mojoPortal, I blogged Anyone Still Using SQL 2000?, to guage how many people still use it. Going forward we are dropping support for SQL 2000 and then the MS SQL data layer will be modified to make it compatible with SqlAzure, and then we will eliminate the separate data layer for SqlAzure. I "think" the previous release of mojoPortal was still compatible with SQL 2000, but the latest code in the repository is not compatible because we have changed from ntext to nvarchar(max) which is not supported in SQL 2000. So the next release of mojoPortal for sure will not be compatible with SQL 2000. Going forward we will support SQL 2005/2008/SqlAzure and SQL CE.

Follow us on twitter or become a fan on Facebook

follow us on twitter become a fan on facebook

Gravatar Joe Audette is the founder of the mojoPortal project and was the primary developer until February 2017.

mojoPortal Released

I'm happy to announce the release of mojoPortal, available now from our download page.

What's New

  • New Bing Map Feature
  • New alternate site search features allow you to use Bing or Google for site search in addition to or instead of the internal Lucene search engine
  • Upgraded to the latest version of AjaxControlToolkit
  • Upgraded from TinyMCE .3.6 to 3.3.7
  • Upgraded from CKeditor 3.3 to 3.3.1
  • Added a required checkbox if a registration agreement is used
  • Updated Italian resources from Diego Mora
  • Fixed a bug introduced in version where if you were using excerpts in the blog, the read more link was malformed
  • Fixed a bug introduced in version where the FeedManager page size setting was ignored
  • Fixed some more places where we had not implemented the new TimeZone system and the old hard coded offsets were still being used
  • Fixed a bug where the google 404 enhancement gives a script error in IE, it is now disabled in IE
  • Fixed a bug in the pgsql data layer for the blog that caused an error on viewing blog categories
  • Other minor enhancements and fixes for things reported or requested in the forums since the last release

Bing Map Screen shot

There were also a few additions to CSS in included skins that you will need to add to custom skins, see this sticky thread for details.

Upgrades for Add On Products

Because of the upgrade to the latest version of AjaxControlToolkit, there are also corresponding compatibility updates for Event Calendar Pro and Form Wizard Pro because they must use the same version of AjaxControlToolkit as mojoPortal. Existing customers can download the updates from their purchase history. We have officially changed our upgrade policy, originally the policy was free upgrades for 1 year after purchasing our add on products, but now our policy is free upgrades for the life of the product and this is retro active to all existing customers. If you've never purchased our add on products now is a good time to consider adding them to your site, visit the store to learn more about our add on products for mojoPortal

Online User Group Meeting

Don't forget to sign up for our free online user group meeting coming up this Wednesday June 23, 2010 8 PM Eastern Daylight Time on Yamisee.

mojoportal user group ad

Follow us on twitter or become a fan on Facebook

follow us on twitter become a fan on facebook

Gravatar Joe Audette is the founder of the mojoPortal project and was the primary developer until February 2017.

mojoPortal Released

I'm happy to announce the release of mojoPortal, available now on our download page.

What's New?

  • This is the first release where we have separate release packages for .NET 3.5 and .NET 4.0.
  • New Facebook like button available in the Blog and in the HTML feature. This makes it easy for people to "Like" pages on your site on Facebook.
  • Upgrade from TinyMCE 3.2.7 to 3.3.6
  • Upgrade from CKeditor 3.2.1 to 3.3
  • Upgrade to the latest version of NeatUpload
  • We removed 7 skins from the main release packages to reduce the file size of the main packages and made them available in a separate download Don't worry there are still a lot of skins included in the main packages.
  • Various bug fixes and enhancements for things reported or requested in the forums.

There are also corresponding releases of Form Wizard Pro and Event Calendar Pro. Users who have purchased these add-on products should upgrade them at the same time or right after upgrading mojoPortal.

Form Wizard Pro

  • compatibility updates for mojoPortal
  • includes a build for both .NET 3.5 and .NET 4.0
  • fixed bug where an incorrect redirect would happen after importing a form definition if the site was running in a virtual directory instead of a root site
  • added option to use regular expression validation of date questions
  • added a setting for a custom CSS class so forms can be styled differently

Event Calendar Pro

  • compatibility update for mojoPortal
  • includes a build for .NET 3.5 and .NET 4.0

Follow us on twitter or become a fan on Facebook

follow us on twitter become a fan on facebook

Gravatar Joe Audette is the founder of the mojoPortal project and was the primary developer until February 2017.

Event Calendar Pro Released

I'm happy to announce the release of Event Calendar Pro This is a free upgrade for existing customers, though you should first upgrade to mojoPortal It "probably" works with mojoPortal or higher but has only been tested with mojoPortal Customers who already purchased Event Calendar Pro can download the new version by signing into the site and then click the "My Account" link at the top of the page, and then click the Order History tab.

Whats New?

  • Better support for time zones with automatic adjustment for Daylight Savings when creating future events.
  • It is now possible to accept will pay later ticket orders without configuring any ecommerce provider.
  • There is a new setting to allow ticket purchases/registration without having to register or sign into the site.
  • There is a new recurrence option for Bi-weekly (every 14 days) events.
  • When making recurring events that require registration, it is now possible to use the same begin date for ticket sales/registration for all recurrences.
  • Fixed a bug in the RSS feed where the urls for events were not correct if the site was hosted in a virtual directory sub folder.

Important: Since we removed the previous time zone offset setting and added a new Time Zone setting, after upgrading you should immediately go to the settings for any existing instances of the Event Calendar and set the time zone.

Event Calendar Pro Screen shot


Follow us on twitter or become a fan on Facebook

follow us on twitter become a fan on facebook

Gravatar Joe Audette is the founder of the mojoPortal project and was the primary developer until February 2017.

mojoPortal Released

I'm happy to announce the release of mojoPortal, available now on our download page.

Feature Improvements

  • Image Gallery now uses friendlier file names and you can optionally move existing Galleries below the /Data/Sites/[SiteID]/media folder to make it easy to browse gallery images from the editor. Just add this setting to your user.config file: <add key="ImageGalleryUseMediaFolder" value="true" />
  • People often don't notice that the forums are integrated into site search, so we added a search box in the forums that redirects to site search and filters for forum content.
  • Added a search feature in the Url Manager to make it easier to find a URL when you have lots of them.
  • Upgraded FCKeditor from 2.6.5 to 2.6.6
  • Updated config setting to use new jQuery 1.4.2
  • Updated Russian resources files from Alexander aka SkySandy
  • Updated Portuguese resources from Leonard Pedrini

Bug Fixes

  • Fixed a bug where re-sorting root pages in a multi-site installation caused root level pages in all sites to be re-sorted and with lots of sites and pages, this could result in it becoming impossible to sort pages in some cases.
  • Fixed a bug where if you tabbed out of the page name in page settings or out of the blog title when updating a blog post, a new URL was being suggested when the name or title did not change which could lead to unintended URL changes.
  • Fixed sitemap so that it uses https URLs if the page requires SSL to avoid an extra redirect
  • Fixed bug in Image Gallery where portrait images were not creating correctly sized thumbnails or web images.
  • Fixed issue where the content rating control did not work well in some cases

Thanks to all community members for providing bug reports and feedback, especially Alexander aka SkySandy for his work in diagnosing bugs and suggesting fixes.

Related Product Updates

Just as we had to make changes in mojoPortal to support Artisteer templates, we also made corresponding changes in our add-on products Event Calendar Pro and Form Wizard Pro. If you are an existing customer who already purchased these products, you should upgrade them at the same time as you upgrade mojoPortal. You can download the latest version by signing into this site as the same user who made the purchase, then visit the "My Account" link at the top of the page and look under the Order History tab where you will see links for your products

Follow us on twitter or become a fan on Facebook

follow us on twitter become a fan on facebook

Gravatar Joe Audette is the founder of the mojoPortal project and was the primary developer until February 2017.