Ok, not any "Hosted Environment" but specifically the hosting environment over at www.discountasp.net. If your app is hosted elsewhere, you still might be interested in this. The steps may be similar with your hosting company as well.

Note: As of this blog posting SQL CE 4.0 has been released to the public for 6 months - since January 2011.

Truthfully, I'm not much of an expert on SQL CE. (Compact Edition). The little I know is just the fact that it's embedded inside devices other than traditional computers to handle data duties using a schema and query language based on it's biggest brother: SQL Server. Because it's free from needing to run inside its own service or on its own machine, means that you can run it right along side all your code inside a phone, printer, mp3 player or other device.

However, with the release of the latest version (4.0), Microsoft added the ability of embedding it inside traditional web applications as well. CE has traditionally been a free Microsoft product and suddenly I was very interested in having a free & simple SQL database run in my web apps.

But I don't run my own web server, nor have a "Dedicated" hosting account. I have a shared hosting account that caters to ASP.NET web applications at DiscountASP.Net. If you're interested in a place to deploy your web apps, please visit them. ( www.discountasp.net) They are very good for many reasons. Including their support of CE.

( They don't have to support a free version SQL. They'd rather you pay for their offerings of the full version of SQL. But in reality, sometimes SQL Server is overkill. Sometimes you just need a bit of data, or a proof of concept, etc. So, kudos to them for understanding this from a developers point of view. )

The technical blog posts I was reading all had examples of CE deployment based on access to your own web server. Something got under my skin and had to chase down a process to see if I could make this work in my DiscountAsp.net hosted environment. I spent an afternoon fiddling with MSDeploy, Visual Studio 2010 Publish Settings and "My Account" settings at DiscountAsp.Net.

In the end: Success! After getting it running, I've been continually developing my app on top of it. And something else: Once deployed, you don't have to go through the entire process to upload small changes to your server. Simply use your favorite FTP program to punt new changes. That includes the SQL CE file (.sdf) too!

Remember that CE has serious limitations. No support for Functions. No support for Stored Procedures. It seems like it runs a little slow if you've got complicated queries. OK, well this all makes sense. Not much different than an XML blob wearing a SQL costume.

NOTE: Technical Evangelist Mark Wisecarver is working on a webcast based on these steps and will go into much more depth on the differences between SQL CE and SQL Server. Come 'on back for the link, it'll be [ here ] when the webcast is complete.

UPDATE: Mark has posted his Webcast HERE: http://www.dasptv.com/avc-view.aspx?videoid=39

For those of you who are pressed to carry on, here are the steps:


VIA DiscountASP.Net Control Panel

• Login to your DiscountASP.net account
• Go to IIS Tools
• Go to MS IIS Manager Tab
• Enable Primary Account User Access - (and click Update)
• Click "Web Deployment Tool" (and click Update)
• Go to Account Info/Edit
• Note Server name (example web101)

VIA Visual Studio 2010

Download and install Microsoft SQL CE 4.0 here: http://www.microsoft.com/download/en/details.aspx?id=17876

• File > New > Project > ASP.Net Web Application (Note: This is the template web app that comes with the ASPNETDB.MDF database already included - used for user login. Using the sequence listed here will NOT enable this .MDF database file to run on DiscountASP.Net servers - and the login feature of this template will not work using this database - it must use a regular sql server db)
• In App_Data file, (this is where the .mdf db resides) Add > New Item > Sql Server Compact 4.0 Local Database


• Add records to this database


• Add connection string to web.config - Mine looks like :
  <add name="DataConnection" connectionString="Data Source=|DataDirectory|\Data.sdf" providerName="System.Data.SqlServerCe.4.0"/> Where |DataDirectory| will automatically be changed to "App_Data" upon using MSDeploy (later in this sequence)


• Add some code to interact with the data on your default.aspx page


• Run app locally to make sure it's doing what it's supposed to


• Project > Add Deployable Dependencies (let VS go and add a bunch of folders with .dll files)


• Project > Package/Publish Settings - your app properties pages opens defaulted to the Package/Publish Web tab


• In the "Items to Deploy(Applies to All Deployment Methods)" section - set to: All files in this project


• Check = Exclude generated debug symbols


• unCheck = Exclude files from the App_Data Folder


• In the "Items to Deploy(Web only) section - leave all boxes UnChecked


• In the "Web Deployment Package Settings" section:


• Check = "create deployment package as .zip file"


• In the "IIS Web site/application name" section set to: Default Web Site/AppName (replace AppName with the name of your web application - no not change "Default Web Site")


• Go to the Package/Publish SQL TAB


• Click the "Import from Web.Config" button


• Check the connection string that appears in the list - that goes with your SQL CE database


• Use the Remove button to remove all other connection strings that you won't use on your deployed app (remember if the connection string to the ASPNETDB.MDF file is on the list, it must be removed. The login feature will not work using this mdf file on DASP servers...)


• SAVE YOUR SETTINGS


• Build > Publish (app Name)


• The "Publish dialog shows up


• Publish Method = Web Deploy


• Service URL = https://[yourWebServerHere].discountasp.net:8172/MsDeploy.axd


• Site/Application = yourdomain.com/yourAppName


• Check = Mark as IIS Application on destination


• Check = Leave extra files on server (Not sure if this makes any difference...)


• Check = Allow untrusted certificate
• Enter DASP UserName - the one you use to login to DiscountAsp.Net


• Enter DASP Password - the one you use to login to DiscountASP.Net


• Click Publish


• Check the live version of your site.

So anyway, I've NOT tested this method over and over. But have deployed a simple app, it worked, then made changes to the code, re-deployed and the changes made it through the deployment process. Remember you can also FTP small changes once you've done the first successful deployment.


Let me know if this works for you!