Cypris’ lookout

MS SQL Server Express: a good choice?

Microsoft SQL Server comes in many editions, ranging from completely free to use and distribute to versions costing tens of thousands of dollars.
For small businesses, or when you can live with the limits imposed, the Express edition is one option to consider.

Here are some reasons why SQL Server Express may be a good choice:

You’re upsizing an Access database

SQL Server is the natural extension of upsizing an existing Access database. It work automagically with minimum effort providing that you followed some simple good-design rules from the start.

You’re future-proofing your needs

Because SQL Server comes in many flavours, you know you -or your customers- can upgrade to a more capable (albeit more expensive) version in the future if needed.

Very flexible

As usual with a lot of Microsoft development tools, SQL Server will happily let you shoot yourself in the foot by providing you with a fairly easy way to treat your database as a complete development platform.
It’s good in the sense that you have interesting tools and capabilities included in the server, and it’s bad for the exact same reasons.
I tend to prefer database servers to be just that: data repositories, and I’m not too fond of relying on specific, non-standard features of a particular database system, but what do I know.

Excellent out-of-the-box development support

Deep integration with .Net and Visual Studio, without any effort, Microsoft saw to that of course.
In some cases, such as LINQ to SQL, it’s almost the only real choice, although the other database vendors are working hard at the necessary providers, so that lead should be short-lived.
There is something to be said about developer productivity: you have to give credit to Microsoft for making their tools well integrated and usable from each-other. What it means is that for small developer shops there is much to gain in surrendering to this “ease of the default”.
Of course, it’s a double-edged sword, but having a complete development infrastructure work out of the box is certainly a big help, and if you don’t like it, you’re still free to chose something else.

Lots of tools

With SQL Server Advanced Services, you also get Server Management and Reporting Services. These are great tools made available for free.
The only missing one for SQL Server is the Reporting designer. While the reporting service means that you can use existing reports, only SQL Server Standard and Enterprise have it.
There is an option for developers though: the (nearly free) SQL Server Developer edition is in fact the same as SQL Server Enterprise, without the license to use in non-developer or tester environment. This means that as a developer, you can create and distribute your reports to be used by your customers who will be using SQL Server Express.

Did I mention it’s free?

All this is free, as in beer, not as in liberty though.
For commercial applications targeted at small businesses, SQL Server Express is a really good choice: you can distribute it without problem, the customer gets all the tools, can easily find outside support, and they can always migrate to a more beefy version if their needs grow, all that without having to depend on you.
So it sort of offers customers a kind of freedom that they wouldn’t have with other choices.

Of course you can get that with other database systems, although you have to be careful which Open Source one you choose: I recently decided not to use MySQL any longer for the simple reason that it’s too expensive and restrictive in a business environment, at least for the kind of work I do.

Why would you not want to use SQL Server Express?

You don’t want to depend on Microsoft

That can be a good reason enough sometimes. There is nothing preventing Microsoft from crippling SQL Server Express in the future to force users to move to a paying version early.
I suppose that whatever database system you use, even Open Source ones, there is always the possibility that the company supporting its development goes bankrupt, the Open Source projects goes dead or decides to go in a direction that doesn’t suit you..

It’s only supported on Microsoft OS

True, and that’s a good reason to chose something else.
There is a hidden cost in SQL Server Express: it needs to run on a Windows machine, and that’s not free, although SQL Server will work on older Windows 2000 machines and Windows XP which are arguably not expensive.

Your database needs will exceed SQL Server Express specifications

If you think any of your databases will grow beyond 4GB or that it will get busy and you need all the RAM and CPU you can get, then SQL Server Express is probably not for you as it will only use 1 CPU and 1GB of RAM at most.
If your needs go beyond that, then you’ll have to move to a paying version.

Upgrading can be expensive

It’s true that moving to the next cheapest upgrade of SQL Server Workgroup will cost you about US$700 for a 5 user license. The limits imposed on the database are much higher (2 processors, 3GB RAM and no size limit) but if you need more clients / or higher limits, then the expense will grow quite fast, and you’ll have to manage those hateful client access licenses.

Your needs are more modest

We haven’t talked here about single-file/single-user database systems. These databases don’t user resident services and are usually meant for more limited needs, sometimes allowing only a single user to be connected.
The footprint of these non-server databases is a lot smaller, typically only requiring a single dll or a handful of files to be installed.
They are extremely useful for desktop application that do not really require multi-user support or advanced security features.
Here again, Microsoft offers SQL Server Compact, which, despite the name, doesn’t have much to do with the other SQL Server editions. This one is also free, but has a limited feature set and only allow single-user access as it is meant to be a lightweight database and works well in limited memory environments such as those found on mobile devices.
Of course, here again there is a lot of competition: Thunderbird, SQLite, MS Access and VistaDB (for embedding into .Net applications, not free) to name a few.

These are pretty good times when it comes to databases: we get more choices now than we ever had.
As usual, choosing a database as a back-end for your products isn’t easy: you need to consider cost, licensing, support and the future.
There isn’t a single database system that will meet everyone’s needs for all types of use, so choose carefully.
SQL Server Express is a very good contender in that market. It should not be dismissed out of hand because it’s from Microsoft, in the same way that PostgreSQL shouldn’t be dismissed because it’s Open Source.
Just use the tool that best answers your needs for your particular circumstances.

References:

• SQL Server Express main page
  www.microsoft.com/sql/editions/express/
• Compare SQL Server editions
  www.microsoft.com/sql/prodinfo/features/compare-features.mspx
• System Requirements for SQL Server Express
  www.microsoft.com/sql/editions/express/sysreqs.mspx
• SQL Server Compact (single-user, small footprint database)
  www.microsoft.com/sql/editions/compact/
• The VistaDB database, commercial embeddable database for .Net applications
  www.vistadb.net/
• SQLite database, public domain, flexible file-database system
  www.sqlite.org/
• The Firebird DB (based on Interbase 6 from Borland)
  http://www.firebirdsql.org/
• PostgreSQL Open Source database server
  http://www.postgresql.org/

Add comment January 30th, 2008

.Net: The limits of using Reflection

Reflection is a hugely useful technology: it allows you to get inside objects and get their intimate details, modify their values and even rewrite part of their code.
Today I wanted to build a simple treeview control that would display the hierarchical structure of an arbitrary object by going through its properties and building a tree-like structure. Useful to visualise the internals of an object and see changes at runtime.

Using PropertyInfo you get enough information about a particular property to decide if you want to display it or recurse into it if it’s a complex type that contain other properties.

The only big issue I faced was when dealing with indexers.

The issue with Indexers

Indexers are defined in code like that:
using System; using System.Collections; public class DayPlanner { // We store our indexer values in here Hashtable _meetings = new System.Collections.Hashtable(); // First indexer public string this[DateTime date] { get { return _meetings[date] as string; } set { _meetings[date] = value; } } // Second indexer, overloads the first public string this[string datestr] { get { return this[DateTime.Parse(datestr)] as string; } set { this[DateTime.Parse(datestr)] = value; } } } We’ve defined an overloaded indexer: one takes a DateTime, the other just a string representation of a date.

We could use them like that for instance:
DayPlanner myDay = new DayPlanner(); myDay[DateTime.Parse("2006/02/03")] = "Lunch"; ... string whatNow = myDay["2006/06/26"]; Now, let’s say we want to print all property names and their values for an arbitrary object.
Let’s try on our myDay intance:
Type type = myDay.GetType(); PropertyInfo[] picoll; picoll = type.GetProperties( BindingFlags.Public | BindingFlags.Instance | BindingFlags.Static); foreach (PropertyInfo pi in picoll) { object val; val = pi.GetValue(myDay, null); Debug.WriteLine(pi.Name+"="+val) ); } So, that part is easy: we iterate through all properties of myDay and print their Name and they value.
Problem is, when we reach the indexer property, we get a TargetParameterCountException on line 8.

We need to detect the indexer before attempting to get its value.
To do that, we need to modify our foreach loop to:
foreach (PropertyInfo pi in picoll) { object val; ParameterInfo[] parcoll = pi.GetIndexParameters(); if (parcoll.Length == 0) { val = pi.GetValue(o, null); Debug.WriteLine(pi.Name+"="+val) ); } } Here we attempt to get the indexer parameters for every property. If we’re dealing with an indexer, the parcoll array will contain a list of ParameterInfo related to that particular indexer.

So what really is this parcoll and its ParameterInfos?

A ParameterInfo contain information about the parameter of an indexer property.
The parcoll array will contain one entry for each overloaded indexer.
In our concrete example this is what we get:

• pi.Name will be Items, this is standard for all indexer properties.
• pi.PropertyType is the type of the returned class of our indexer, in our case, a System.String.
• parcoll.Length will be equal to 2 because we have 2 overloaded indexers.
• parcoll[0].Name will be date.
• parcoll[1].Name will be datestr.
• parcoll[0].ParameterType will be System.DateTime.
• parcoll[1].ParameterType System.String.
• parcoll[0].Member and parcoll[1].Member are in fact references to pi itself

That’s a lot of nice information about our indexer, but what if we want to get a value from it? Consider the definition for GetValue:
PropertyInfo.GetValue(object o, object[]{} index). That second parameter is used to get a value from the indexer.

So, how do we use it?

If you know valid keys that you can feed to the indexer, then you just do that (assuming that pi is the PropertyInfo for the indexer).
string meeting; meeting = pi.GetValue(myDay, new string[]{"2006/02/25"}) as string; Or, if you prefer to use a DateTime: string meeting; meeting = pi.GetValue(myDay, new DateTime[]{DateTime.Now}) as string; But what if you don’t know anything about the values contained by the indexer?
Well, unless you use attributes to decorate the indexer or the underlying storage field with specific custom information, you’re basically screwed.

To understand why, just consider how the indexer is implemented:
When you pass it a DateTime or a string as a parameter, it just passes it on to the getter/setter as the value.
What is done with that is up to the implementer.
If you want to maintain a collection of some kind, then you need to use private fields to hold the keys and their values.
.Net doesn’t store the values passed to the indexer or the values returned by it anywhere.

Consider how .Net doesn’t give you any means of iterating through the keys and values of an indexer.
You can’t do a for(;;) or a foreach() loop, you can’t even know how many items are maintained through the indexer by directly questioning it.
So, it certainly is normal that you cannot get anything out of the indexer without knowing exactly how to access it.

The Observer Effect

In many fields of science, the act of measuring something, whether it’s an electronic circuit or a psychological experiment, can actually influence the observed phenomenon.
This is unsurprisingly called the Observer Effect.

In .Net, Reflecting on object can be very much the same.

Consider the use of Reflection when it comes to getting information about an object:
You use metadata describing the object to get more meta data information about its members You can use that meta data to direcly query the state of an object.

If you limit yourself to getting metada, then no harm is done: you’re not querying the object instance itself but the metadata about its class.

If you start querying an actual object instance for its values then you are actively interacting with it.
In most cases, if you’re just getting the value of Fields or Properties defined as follow, then it’s fine:
class GetMe { private string _meeting; public string Meeting { get { return _meeting; } set { _meeting = value; } } } Using reflection, we can get the values of both _meeting and Meeting without any side-effect to the object.

Now consider this:
class GetMeNot { private int _collection = null; public int Collection { get { if ( _collection == null ) { OpenDatabaseConnection(); _collection = FetchAllData(); } return _collection; } } } Now what we’ve got here is a case of lazy loading or lazy initialization.
The consequence is that by querying the Collection property we’re actually creating it, with a potential for a lot of side-effects, like the change of other properties, here maybe related to opening the database connection for instance.

So, when getting values of the properties of an object instance, we can actually completely change its state.
Whether this is a concern or not really depends on what you’re trying to achieve: you may not be able to achieve being a passive observer using Reflection unless you know about the construction of the particular class you’re dealing with.
Using Attributes, you could give hints about which Property not to query and which Field to get instead. In our case, we could add a custom attribute like [ImplementedBy("_collection")] to our Collection property for instance.

If you want to do something generic, then you’re out of luck it and have to deal with that fact.
One possible way to minimize the effect would be to not query Properties that only have a getter and no setter, but that’s not a guarantee that all properties that implement a getter/setter are going to play nice.
Then there is also the -probably rarer- case of the Property that only has a setter.
Then there is also the case of the property getter that has some in-build security and will return different things depending on who’s calling…

All this seems pretty obvious once you understand the whole picture but it’s easy to get so impressed and dazzled by the power of Reflection that you don’t realise its natural limits.

4 comments June 13th, 2006

XPO: eXpress Persistent Objects

XPO is an Object Relational Mapping .Net product from Developer Express, a cool company designing cool tools. It’s a programming component whose job is to abstract access to database while allowing the developer to concentrate on a simple object-oriented interface instead.

Continue Reading Add comment June 9th, 2006