TOP

Database Provider Factories: EF + SQL Compact

Now that Entity Framework 4.1 has been released and SQL Compact Edition 4.0 was released a while back, you can start using those technologies in your projects. I was working on a MVC 3 project when I discovered a NuGet package for SQL Compact Edition.

You have three option to install SQL Server Compact 4.0, using the Web Platform Installer, direct download, or you can add it as a NuGet package. I would suggest using the NuGet option, as that includes the required files with your source code. Make sure you get the documentation as well.

SqlServerCompact package: http://nuget.org/List/Packages/SqlServerCompact

With this package, you can start building code that queries and stores data in a file-based database. Yet, you still need one more package to make it work properly together with Entity Framework 4.1. Luckily, there is another NuGet package for this, EntityFramework.SqlServerCompact.

The SqlServerCompact package will try to modify your web.config/app.config with the following keys:

<configuration>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SqlServerCe.4.0" />
      <add name="Microsoft SQL Server Compact Data Provider 4.0" 
  invariant="System.Data.SqlServerCe.4.0" 
  description=".NET Framework Data Provider for Microsoft SQL Server Compact" 
  type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, 
  Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
    </DbProviderFactories>
  </system.data>
</configuration>

Also included in the package is the binaries for SQL Server Compact 4.0, located at the same level as your Visual Studio solution file, you should find this folder and within it all the assemblies needed to run: packages\SqlServerCompact.4.0.8482.1. Inside the lib folder is the System.Data.SqlServerCe.dll, which is the .NET assembly you need to work against SQL Server Compact.

The other package includes the assembly System.Data.SqlServerCe.Entity.dll, which contains code that generates proper queries against the SQL Server Compact. Additionally, it will add a source code file to your project inside the App_Start folder, that contains this code:

using System.Data.Entity;
using System.Data.Entity.Infrastructure;

[assembly: WebActivator.PreApplicationStartMethod(
typeof(InTheBoks.Test.Integration.App_Start.EntityFramework_SqlServerCompact), 
"Start")]

namespace InTheBoks.Test.Integration.App_Start {
    public static class EntityFramework_SqlServerCompact {
        public static void Start() {
            Database.DefaultConnectionFactory = 
              new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0");
        }
    }
}
 

What happens here is that the default connection factory of the Entity Framework is changed to the provider specified in the web.config/app.config. While I was working on the previous builds of SQL Server Compact 4 and Entity Framework 4.1 CTPs, I did not have the provider changed in the web.config. The new key in the config made me wonder for what reasons it removed and added the provider again. So I investigated with an integration test project to see what really happens.

The default connection factory is SqlConnectionFactory. That means that we need to change the connection factory in the start up of our application, which is done with the code inside the Start method displayed above, which changes it to SqlCeConnectionFactory.

To investigate what the default configuration is, I had to access a list of factories using the API: DbProviderFactories.GetFactoryClasses(). This will give you a data table with rows for all the factories. I did this to figure out why the NuGet package made the changes to my web.config/app.config and to see if there was any difference to my already installed factory and the one added in the config. Conclusion was, there is no difference. The reason why the package adds the provider, is probably because the factory is not registered on the computer if you don’t install using the manual or Web Platform Installer – so it have to add it manually in the config. My suggestion is to keep the configuration key in place, this ensures that your application will work without the need to install SQL Server Compact.

Reference Table for DbProviderFactory Classes.

Name Description Invariant
Odbc Data Provider .Net Framework Data Provider for Odbc System.Data.Odbc
Type System.Data.Odbc.OdbcFactory, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
OleDb Data Provider .Net Framework Data Provider for OleDb System.Data.OleDb
Type System.Data.OleDb.OleDbFactory, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
OracleClient Data Provider .Net Framework Data Provider for Oracle System.Data.OracleClient
Type System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
SqlClient Data Provider .Net Framework Data Provider for SqlServer System.Data.SqlClient
Type System.Data.SqlClient.SqlClientFactory, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Microsoft SQL Server Compact Data Provider .NET Framework Data Provider for Microsoft SQL Server Compact System.Data.SqlServerCe.3.5
Type System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
SQLite Data Provider .Net Framework Data Provider for SQLite System.Data.SQLite
Type System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=1.0.66.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139
Microsoft SQL Server Compact Data Provider 4.0 .NET Framework Data Provider for Microsoft SQL Server Compact System.Data.SqlServerCe.4.0
Type System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

(Foto by Tim Morgan)

TOP

Trying to understand Microsoft.Data.dll

Here is my analysis of the recently “released” (embedded) Microsoft.Data.dll assembly, the namespace and the types it includes. It’s been the topic of a lot of heated debate recently, with viewpoints I’m unable to relate to and understand just from reading, so I needed to understand.

The debate is stemming from a blog post by David Fowler and his example that shows how some data-related tasks have a simpler syntax with Microsoft.Data and the ASP.NET WebPages with Razor Syntax.

What is inside the Microsoft.Data namespace?

There is very little code inside the namespace and the assembly. It’s simply some helper types that makes life's a little bit easier. It’s not a new data access framework, like Linq to SQL or Entity Framework.

It contains the following classes: ConfigurationManagerWrapper, ConfigurationConfiguration, Database, DbProviderFactoryWrapper, DynamicRecord, IConfigurationManager, IDbFileHandler, IDbProviderFactory, SqlCeDBFileHandler and SqlServerDbFileHandler. Of which only Database and DynamicRecord are public available, the others are internal.

All data access inside the Microsoft.Data types are using the common ADO.NET types, not the providers specific for any SQL platform. This means it’s not restricted to SQL Compact Edition nor SQL Server. It relies on DbConnection, DbTransaction, DataTable, etc.

Microsoft.Data on ASP.NET Web Forms

While Microsoft.Data.dll is currently not accessible in the Add References dialog, you can find it by looking on your computer, it’s located in the Global Assembly Cache (GAC). Microsoft probably don’t want us to use it outside of WebMatrix in the current release… but if you just take a copy of the assembly out of the GAC, then you can reference the assembly in any .NET project and it will load it from the GAC (you just need the file so you can add a reference).

In my project I added a database to my App_Data folder (which you normally would never do, unless you are working with a local read-only cache in a large-distributed system or working with SQL Compact Edition) and added the following code to my web form, to make it render the Name column of my Users table.

	var db = Database.OpenFile("Database1.mdf");
	var users = db.Query("SELECT Id, Name FROM Users");
	foreach (var user in users)
	{
	Response.Write(user["Name"]);
	}
	

Take notice of the OpenFile parameter, it’s simply the filename on disk. I don’t have to care about any specific details of the connection string, nor how to figure out where the App_Data folder is.

Obviously though, if you added an entity framework (EF) model of your database, you would have very similar example to achieve the same and you don’t have to care about the connection string, at least not in theory.

	using (var db = new Database1Entities())
	{
	var users = db.Users;
	foreach (var user in users)
	{
	Response.Write(user.Name);
	}
	}
	

The two big distinctions betweens these examples is that the first one is dynamic, I can modify the database schema whenever I want and it won’t (necessarily) break my web app, while the latter example with EF will need to refresh the entity types based on the database model.

The other distinctions is that the first example doesn’t require a connection string, while the latter generates one for you automatically, a rather cryptic looking one.

<add name="Database1Entities" connectionString="metadata=
res://*/Model1.csdl|
res://*/Model1.ssdl|
res://*/Model1.msl;
provider=System.Data.SqlClient;
provider connection string=&quot;
Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;
Integrated Security=True;
User Instance=True;
MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

 

While all of this are peanuts for me and anyone who’s been developing on .NET for a while, I think that making things simple where possible is positive, rather than negative. It doesn’t mean we will stop using NHibernate, do proper n-tier and layered architectures just because Microsoft makes some tasks simpler for beginners. It also means some of us probably will eventually have to maintain and possibly migrate solutions built on Microsoft WebMatrix, but does that give us any right to restrict beginners the privilege of building their own solutions and feeling the immense joy of realizing their dreams?

Other’s feedback and comments

Ayende Rahien comments on his blog on the example, where he mentions the use Response.Write within the loop. Understandable this is probably not the best way to do this, but it’s understandable with the sample in question, which was already using Response.Write. There are slightly better examples available out there. He also points out that having the SQL queries directly in the view code is an open invitation for SQL injection. Using proper parameterized queries will reduce this potential security problem. Looks like David updated the sample to use parameters after the initial reactions. After the security push at Microsoft some years back, they really cleaned up their practices with examples in the MSDN documentations, I think we should expect the same level of security thinking on employee blogs as well.

Ayende quotes David, which (David) made the assumption that Microsoft.Data is tied to Sql Server in any way, which my investigations has shown is not correct.

David tried to respond to some of the feedback on embedding SQL directly in the view, with hacking around to get the new dynamic keyword to work properly with LINQ. To me, this defeats the whole purpose of simplicity with Microsoft WebMatrix, Razor and Microsoft.Data.

KristoferA comments on the post and suggests generating Linq to SQL datacontext using a page-level directive, which would essentially give the developer entity objects to work with (and query against). This again defeats the purpose of simplicity, and now you can no longer change the database scheme without “recompiling” your web-app.

The namespace naming is another sour point for some, and I can agree that there is little point is “abusing” the Microsoft.Data namespace for such a trivial little helper, perhaps Microsoft.WebMatrix.Data or Microsoft.Data.Connection?

Who is this for?

Microsoft WebMatrix (and ASP.NET WebPages) is not a tool built for “professional” programmers, additionally is it not a fully generic framework for building everything. It’s a domain specific language that is optimized for building simple web applications without to much resources.

It is not meant for enterprise applications that handles millions of transactions. Will it be used for that? Yes, it probably will! We’ve seen plenty of classic examples of websites that starts with simple web-frameworks and find themselves in big trouble when their services become popular. Services like Twitter and Facebook was not built to scale to their current levels, yet they started as simple concepts and services and has grown to become important services which affects global policies and real-life social interactions.

It's Not Rocket Science, But It's Our Work: http://blog.twitter.com/2008/05/its-not-rocket-science-but-its-our-work.html

And obviously, it’s for those of us who still miss the old days with ASP Classic, here is a pretty good (and funny) read, 8 Reasons to Stick with ASP 3.0 in 2006 (and 2007).

Final thoughts

It’s very clear that Microsoft WebMatrix (and related technologies) are primarily is focused towards beginners and it’s a great tool to build simple websites. I wouldn’t advice anyone to use this if you already know ASP.NET MVC and want to build complex web solutions, ASP.NET Web Forms, MVC or other more general purpose frameworks would probably be more fit.

Additionally I think it’s important to remember that WebMatrix is primarily focused on SQL Compact Edition for data access, the built in editor doesn’t allow you to modify SQL Server database. So the question (and response to some of the comments) is how many layers do you want to wrap your data access logic for a SQLCE database?

Been a while since Microsoft did a push towards simplifying development for beginners, when we went from VB6 to VB.NET, everything was more complex and the entry level for VB.NET is on-par with C#. With the release of .NET Framework 4, the complexity and total amount of features is mind blowing. I for sure welcome tools, languages and frameworks that simplifies how we develop software.

Simplicity is hard and it's something we should strive towards in all that we do.

TOP

SQL Azure: Simpler Management

This is a follow-up to my previous post on SQL Azure: Getting Started. This time I’m going to show you how to do things simpler. Thanks to SQL Server Management Studio 2008 R2 (currently in November CTP) you can now connect without any hacks or tricks to your SQL Azure instance. This will make it much quicker and easier for you to connect, create and modify your SQL databases.

Get SQL Server 2008 R2

The first thing you need to do is get SQL Server 2008 R2 from this download page. Choose the proper edition (32- or 64-bit) for your computer and start the download. The installation is a hefty 1.3GB so depending on your connection it might take a while.

Installation of 2008 R2 is very similar to 2008, on the first screen when you can specify edition I suggest choosing the “Enter the product key” option. The input field in filled with —- which is OK, just leave it as default and go with Next. This is an technology preview, so there are no need for a real license key. It’s nice to see that you can actually install an Express instance for the same installation medium.

Installation Options

Depending on previous installations of SQL Server 2008 or SQL Server Express and what management tools you have installed, there are different things that might happen. My suggestion is to go with the upgrade option if you’re using a development machine, but remember there are always risks involved with using pre-release software.

My own machine had SQLEXPRESS already installed Management Studio 2008 and blocked a new installation of R2. Therefore I had to run the upgrade option, on which I choose to upgrade the shared features, see screenshot below.

UpgradeSQL 

After a few minutes the installation/upgrade is complete and you’re ready to connect to SQL Azure in a much simpler way than previously possible.

Connecting to SQL Azure

To connect to your SQL Azure instance, you need to get the instance server URL. This is available on sql.azure.com when you login in to your account. The instance URL is the full URL, like for example o555n7czrm.database.windows.net.

Make sure the firewall settings are correct, as mentioned in the previous post. Fill out the server name, choose SQL authentication as SQL Azure doesn’t support Windows authentication. The login can be custom login you create, as mentioned in the previous post, or the main administrative account you create on the SQL Azure administrative web interface.

SQLConnect 

When you’re logged in, you should now be able to see all your databases, tables, views, stored procedures, security and a lot more in the Object Explorer. This is a great improvement from previous experience.

Creating a new SQL Azure table

The really cool thing about SQL Server Management Studio 2008 R2 is the fact that it now includes ready scripts that have been aligned with the features of SQL Azure. This means when you right click on any of your databases, you get a template SQL script that conforms to the feature-set of SQL Azure – which is limited compared to the on-premises SQL Server.

SSMS2008R2

In the above screenshot you can see how much of the functionality in Management Studio which is now compatible with SQL Azure. This means most of the previous manual tasks of working with SQL Azure no longer applies, but it’s of course still entirely possible to do your database management “the hard way”.

Hope this quick introduction to SQL Server Management Studio 2008 R2 is helpful and it should make the introduction and adoption of SQL Azure much smoother.

Here is the million dollar question: Do you have any projects which could save time and money from utilizing SQL Azure for a project instead of a huge investments in large-scale on-premises hardware running expensive SQL Server software? You might for a production environment end up with an on-premises SQL Server, and you could utilize SQL Azure as a manner to ramp up an development environment faster than ever before.

TOP

SQL Azure: Getting Started

Update: Some of the details in this blog post is no longer required, by downloading and installing SQL Server Management Studio 2008 R2 you get better support for SQL Azure. Read my post about simpler management with SSMS. 

Here follows a step-by-step introduction to SQL Azure. With all new technologies, and especially those which are available as pre-release form, there can be a step learning curve. Additionally some of your knowledge might be wrong and irrelevant when the final product is complete. It’s hard to keep yourself up-to-date on the latest, but it’s very awarding when you master new technology which makes you more productive and capable of delivering more value than previously.

Cloud Computing is a subject which is seeing a lot of attention lately and it is a very important improvement to existing development and deployment models for service-oriented solutions.

This example is written and based on the November 2009 version of Windows Azure platform.

Create the SQL Server instance

First step to getting started with SQL Azure is to create your instance of the SQL Server. Access the website https://sql.azure.com/, login with your Windows Live ID. If you’re doing this on the CTP (Customer Technology Preview), you can use tokens to get access to the SQL Azure service. When the service goes live, there will be other options to buying service tokens.

When you create a new SQL Server instance, you will be asked to provide a username and password. I suggest using a strong password for this account, preferably use tools like KeePass to generate strong password that you can store securely.

Tip: Never use the SQL Azure administrative account in your applications, use a custom SQL login which you can store in configuration files, etc. This will be explained in further details in this post.

Create database using web interface

If you want to, you can use the web interface to create a new database. Through this interface you get option to pick between the different sizes. Remember, there is a price difference on the db-size so don’t choose more than you really need. We will later explain how you can create a new database using SQL scripts.

SQLAzure_Create_Db

Open up the firewall

Before you can connect to your SQL Server instance, you need to open up the firewall. By default, nobody can connect to your instance.

Click the Firewall Settings tab on the administrative page, then Add Rule. Fortunately the webpage informs you which IP address you are currently connected from, so it’s easy to add the correct rules.

Tip: It can take a few minutes for the firewall settings to update.

Connect using SQL Server Management Studio

It is now possible to connect to your SQL Server instance using different SQL tools, one is of course SQL Server Management Studio 2008 (or R2). While you won’t get the full suite of functionality that you are normally used to, you can use it to connect and execute SQL statements against your databases.

When you first start management studio, close the Connect to Server dialog that appears. Instead click the New Query button on the toolbar. This will again display the Connect to Server dialog, only this time it will work without an error message.

Fill out the server name, which you can find on the SQL Azure web administration. It should be something like INSTANCE_ID.database.windows.net. Fill out the username and password, sometimes you might need to write the username with the following pattern, depending on which tool you use, username@INSTANCE_ID.

If the firewall updates are not complete or you have something wrong in the firewall rules, you might see a dialog like the following. This means you have network connection problems.

SQLAzure_Connect_Failure

Create database using scripts

MSDN contains documentation on which type of SQL operations are allowed on SQL Azure, if we take a look at the CREATE TABLE statement, it contains fewer options than normally, basically only name and size (1GB or 10GB at the time of writing this post, default is 1GB).

Create database statement has to be run by itself, so type the following in the query window and execute it by pressing F5:

CREATE DATABASE TestDb

Create login and user with correct permissions

As I mentioned earlier, you don’t want to use the master login when you later on connect to your custom database. Therefore you should create an alternative login with the following command. Make sure you generate a strong password.

CREATE LOGIN TestLogin WITH PASSWORD = 'PASSWORD'

Next it’s time to change the query window to connect to our newly created database, right-click on the query window and choose Connection/Change Connection. Use the master login as before, but expand the Options and fill out the database name in the Connection Properties.

SQLAzure_Connect_Options

When you are connected to the right database, you can create a user on that database, which is basically registering an existing login account to a database. This user will by default have no permissions on the database, unless you already gave them one or more SQL Server roles.

CREATE USER TestUser FROM LOGIN TestLogin

Next step is to give the permissions needed for this user, to simplify we’ll give the login SELECT, UPDATE, INSERT and DELETE permissions on all tables on the database.

GRANT SELECT TO TestUser;
GRANT INSERT TO TestUser;
GRANT UPDATE TO TestUser;
GRANT DELETE TO TestUser;

Creating tables and inserting data

Without doing a deep explanation of how to create tables, here is a create table statement for a very small table. Run this in your query window.

CREATE TABLE Item
(
    Id uniqueidentifier NOT NULL PRIMARY KEY DEFAULT newid(),
    Name varchar(100),
    Created datetime,
    Modified datetime
)

Then it’s time to insert some sample data, run the following statements.

INSERT INTO Item (Id, Name, Created, Modified) VALUES(newid(), 'Test #1', getdate(), null)
INSERT INTO Item (Id, Name, Created, Modified) VALUES(newid(), 'Test #2', getdate(), null)
INSERT INTO Item (Id, Name, Created, Modified) VALUES(newid(), 'Test #3', getdate(), null)

Congratulations, you have successfully created a SQL Azure database, table and populated it with some data.

To validate that everything is working fine, run a query that selects data from your new table.

SQLAzure_Query

In my next post, you will learn how you can connect to the database from any website, both running locally and inside the Windows Azure cloud.