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

General .NET Internet SingleUserBlog
Posted by: Brendan Kowitz
Last revised: 21 Sep 2013 12:15PM

Comments

5/2/2006 2:12:11 PM
Why not write a personalization provider?
5/2/2006 3:48:07 PM
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.
5/2/2006 4:46:05 PM
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.
5/2/2006 9:18:37 PM
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...
5/3/2006 12:52:41 AM
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.
5/13/2006 9:45:16 PM
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.
5/13/2006 10:08:29 PM
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!
5/13/2006 11:18:46 PM
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)
5/17/2006 1:38:18 AM
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
5/17/2006 9:23:06 AM
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>
5/18/2006 7:04:20 AM
Great! Thanks!

--jason
5/24/2006 12:57:25 AM
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.

9/7/2006 9:16:25 AM
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
Lai
Lai
8/24/2007 3:11:11 AM
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 ?
8/25/2007 9:14:07 AM
how does your "pageSettings.xml" look like ??
can u post the structure of this file too please ? thanks
11/15/2007 2:33:53 AM
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.
11/15/2007 4:48:54 AM
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.
11/15/2007 9:01:42 AM
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.
12/2/2007 9:28:35 AM
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!
6/13/2009 8:17:36 AM
well,it's really useful. would anyone exlain me how the tables in the .MDB are initiallized?
12/9/2009 12:56:43 PM
I always get a user in SavePersonalizationBlob username so i never store data...
any1????

No new comments are allowed on this post.