Webparts without SqlExpress or SqlServer

One of the things that has been annoying me for some time about Webparts is that silly dependency on SqlExpress/SqlServer. My webhosting at Jumba comes with MySQL or something and not SqlServer. So up until this point to get around the dependency on SqlServer I simply disabled all the calls from SqlBlogPersonalizationProvider and that did the trick, but I had to manually do all the layout stuff and couldn’t use the webparts properly. A while ago when Microsoft released the source code for the old AccessPersonalizationProvider from Beta 1 I thought I’d come back and address this issue.

Here’s what happened.
I plugged the Access providers into SUB, it all compiled started to run, then Bang! It all broke. Apparently I need an existing Access database with the correct schema and tables. I’m sure this wouldn’t have been very difficult to put together but I don’t have that much time anymore.

Fortunately I found this blog entry where Dion provides details on how to store the serialized byte array that the provider outputs as a Base64 string in the aspx page. I didn’t exactly want the page modifying itself so I simply modified it to store all the goodies in an Xml document in the App_Data folder.

Yey, so now I can use the Shared-Scope webpart features without SqlServer. Another advantage of this is my Xml document is around 5kb where I know the starting size for that SqlExpress db is 10mb. In future I would like to get a portable database solution working such as the Access one or even a Sqlite version.

View XmlFilePersonalizationProvider Source

Print | posted on Monday, May 01, 2006 9:34 PM

Comments on this post

# Why not just ..

Why not write a personalization provider?

Left by Sahil Malik on May 02, 2006 2:12 AM

# re: Personalization provider

The XmlFileSharedPersonalizationProvider (that I'm currently using) is a simple provider which saves the Base64 encoded byte array to an xml file. At this stage it's very simple and only saves the shared view-scope. I want to get a more complete provider working so I can use more of the features they provide.

Left by Brendan on May 02, 2006 3:48 AM

# Webparts

I think your post may have answered the question I've been hoping wouldn't have this answer

"So Question #1 - Does your hosted environment support SQLExpress?

Yes – Then your setup is easy (well maybe). The problem is, moving a SQLExpress DB from the cutsie tiny system tray webserver, to IIS is always a pain in the donkey. And for various other reasons I mentioned, I personally don’t recommend using SQLExpress in production.
No – Then you will have to migrate the SQLExpress DB from SQLExpress to a full fledged SQL Server. So the question now boils down to" - http://codebetter.com/blogs/sahil.malik/archive/2006/04/24/143261.aspx

So it seems Microsoft = Microsoft = Microsoft, there's just no room for some alternatives in that equation.

Left by Brendan on May 02, 2006 4:46 AM

# Access database

Hi Brendan. I've come across exactly the same problem as you! I want to use SUB, because it's an xml based solution - but then there's this great big dependancy on SQL server for the web parts stuff.

I also came across the access providers, but instead of installing them, simply changed the name from SampleAccessProviders.vsi to .zip. This contains another zip file, and then there's an empty access database already set up for you. It's still larger than you want (600Kb, which access can compress to about 300) but it's all there.

There's a slight problem though, which Darren works around in SqlBlogPersonalizationProvider.cs - all of the web part settings are per-page. So you have to design the web parts on each page of your site (I don't know if this includes query strings). Darren gets around this by deriving of the SqlPersonalizationProvider and just using a part of "urn:GlobalKey". I intend to rewrite this provider so that it will support any other provider, not just the sql server one. (Assuming there isn't another way to do this. I'm a little surprised Microsoft doesn't already support this)

And following that, I might actually put some content up on my site! I'm having too much fun hacking at SUB - it's a great codebase to learn asp.net 2...

Left by Matt Ellis on May 02, 2006 9:18 AM

# http://www.winsmarts.com

Brendan - yes having support for SQL Server makes things easier. But the architecture is extensible that you can write your own personalization provider. Frankly I don't see where the problem is.

Left by Sahil Malik on May 02, 2006 12:52 PM

# Brendan I love you!

Thanks a ton for that Brendan, I also hate the way it relies on SQL Server for personalization. I'll definately put the hard word on Darren to make this the default for SUB.

Left by Paul Stovell on May 13, 2006 9:45 AM

# Cool stuff!

I'll pull this down next week and have a good look at it. Well done Brendan :-)

BTW: I have a new build that I've been playing with that has some serious perf. improvements. I'll try to upload the new build with the XML Personalization Provider after I've done some testing on it.

Cheers!

Left by Darren Neimke on May 13, 2006 10:08 AM

# More options

After Matt pointed me in the right direction for finding the blank access database, I now have the Access provider working too.
Install to:
\App_Code\Portal\AccessPersonalizationProvider.cs
Files:
(http://www.kowitz.net/files/AccessPersonalizationProvider.zip)

Left by Brendan on May 13, 2006 11:18 AM

# Web.config

Thanks for the code, Brendon. I'm having trouble configuring web.config to use the AccessPersonalizationProvider. Can you post a sample using the Access and the XML Providers?

Thx,

--jason

Left by Jason Burton on May 16, 2006 1:38 PM

# re: Web.config

After you install the blank Access database into the App_Data folder, your web.config changes might look something like below.
The XmlFilePersonalizationProvider works much the same way but it doesn't need a connection string and it won't save the profile settings, but you can't beat it on filesize.
*Note: With the Access provider, I haven't tested the profile provider stuff because I only use the webpart personalisation.

<?xml version="1.0"?>
<configuration>

<connectionStrings>
<add name="AccessDB" connectionString="~\App_Data\ASPNetDB.mdb" />
</connectionStrings>

<system.web>
<webParts enableExport="true">
<personalization defaultProvider="AccessBlogPersonalizationProvider">
<providers>
<add connectionStringName="AccessDB" applicationName="/" name="AccessBlogPersonalizationProvider" type="MarkItUp.SingleUserBlog.Web.WebParts.AccessBlogPersonalizationProvider"/>
</providers>
</personalization>
</webParts>

<profile enabled="false" automaticSaveEnabled="true">
<providers>
<remove name="AspNetSqlProfileProvider"/>
<add name="AspNetSqlProfileProvider" connectionStringName="AccessDB" applicationName="/" type="MarkItUp.SingleUserBlog.Web.WebParts.AccessProviders.AccessProfileProvider"/>
</providers>
</profile>
</system.web>

Left by Brendan on May 16, 2006 9:23 PM

Great! Thanks!

--jason

Left by Jason Burton on May 17, 2006 7:04 PM

# Installing the XmlFilepersonalizationProvider into SUB

To install it, chuck the file in the source tree somewhere, then make sure your app.config includes the following lines (replacing or merging with the exisint SQL provider as you see fit):

<personalization defaultProvider="XmlFileSharedPersonalizationProvider">
<providers>
<add applicationName="/" name="XmlFileSharedPersonalizationProvider" type="MarkItUp.SingleUserBlog.Web.WebParts.XmlFileSharedPersonalizationProvider"/>
</providers>

Cheers, Grant.

Left by Grant Holliday on May 23, 2006 12:57 PM

# WebParts on Master Pages

Last time I spent a very long time griping about h
found at http://sticklebackplastic.com/Posts/Post.aspx?postId=bc368651-6a8d-4196-8fa8-2ff96b282dff

Left by on Sep 06, 2006 9:16 PM

# re: Webparts without SqlExpress or SqlServer

i' ve got XmlFilePersonalizationProvider.cs in App_code and add somr code in web.config
<webParts>
<personalization defaultProvider="XmlFileSharedPersonalizationProvider">
<providers>
<add applicationName="/" name="XmlFileSharedPersonalizationProvider" type="MarkItUp.SingleUserBlog.Web.WebParts.XmlFileSharedPersonalizationProvider"/>
</providers>
</personalization>
</webParts>
then i created a webpart, drag webpart to some position.. but i saw no XMl file in App_Data ? please tell me why ?

Left by Lai on Aug 23, 2007 3:11 PM

how does your "pageSettings.xml" look like ??
can u post the structure of this file too please ? thanks

Left by anan on Aug 24, 2007 9:14 PM

OK first thing I have to say is this will be great if I get it working.
Second maybe some one could come up with a way to use MySQL server.

Questions that might I might answer before they are asked is yes I have SQL server on my Dev box but the clients provider only supports MySQL so I am having to get this working with a alternate method.

ok I have added the below code to the web.config between <system.web></system.web>

<webParts enableExport="true">
<personalization defaultProvider="XmlFileSharedPersonalizationProvider">
<providers>
<add applicationName="/" name="XmlFileSharedPersonalizationProvider" type="MarkItUp.SingleUserBlog.Web.WebParts.XmlFileSharedPersonalizationProvider"/>
</providers>
</personalization >
</webParts>

I have added the XmlFilePersonalizationProvider.cs file to my App_Code. I ran the site and it still used the ASPNETDB.MDF. Tring to get this to work I added Personalization-ProviderName="XmlFileSharedPersonalizationProvider" to the WebPartManager and this is still creating and using sql server.
I beleave I have all the code in the correct place how do I tell the WebPartManager to use the XML provider not the SQL Server.

Left by JCPhlux on Nov 14, 2007 1:33 PM

If you're having trouble with the XmlFilePersonalizationProvider and can't use SQLServer or SQLExpress, then I'd look at using the MS Access providers. Found here: http://download.microsoft.com/download/5/5/b/55bc291f-4316-4fd7-9269-dbf9edbaada8/sampleaccessproviders.vsi

It's more feature complete then the Xml provider and also supports both Shared and User personalization scopes.

If someone finds a MySQL version that works well I'd be interested to hear about it.

Left by Brendan on Nov 14, 2007 3:48 PM

# Webparts WITH MySQL

OK I found it and have tested it.
<Link>http://www.codeproject.com/aspnet/MySQLsuite.asp</Link>
This works great with MySQL.
I want to thank you all if I would not have found this theard I would not have kept looking and found the one I posted.

Also I am giving a BIG shout out to J Snyman.

Left by JCPhlux on Nov 14, 2007 8:01 PM

It seemed that MySQLSuite is really a wonderful toolkit! I'm just a middle school student, and I wonna make a website for my class. It is so useful for me to set up a website using MySQL -- MySQL is cheaper than SQL Server! Thanks a lot!

Left by DrunkForest on Dec 01, 2007 8:28 PM

Brendan Kowitz's .NET Blog