Working with SQL Server 2010 in VC# 2010

[Edit: so much for my first attempt at posting something useful! When I first wrote this post, I had resigned myself to the fact that C# in VS2012 Express was only going to be available for Windows 8. Now VS Express for Desktop is available for Windows 7 and both the Database Explorer and graphical EDMX creator solve the problem outlined below! For anyone with the problem of not being able to connect to SQL Server 2012 in VS2010 Express, my advice would now be to upgrade to VS2012.]

I came across a problem (and solution!) that intrigued me recently in Visual Studio 2010. I thought I’d share it in case anyone else found the same thing.

I have recently got back in to C# programming – there’s now a good reason to because I need a particular piece of bespoke software for my business. I’m using Windows 7 with Visual Studio 2010 Express Edition – I’m a hobby programmer after all! I have also recently acquired SQL Server 2012 Express Edition. My intention is to create an Entity Framework-based class library backend, initially with a WPF/C# frontend (although one of the reasons for keeping the two items separate from an early stage, is that I would ultimately like to access the database through Silverlight too).

Here’s the situation: Install Visual C# 2010 + SP1 – fine. Install SQL Server 2012 – fine. Create a class library project and add and “ADO.NET Entity Data Model”, generated from a database…but which data connection should I choose? I appear to have only 2 options, namely “Microsoft SQL Server Compact 3.5″ and “Microsoft SQL Server Database File”. Hmm. Not what I wanted, but OK – we can probably get away with the second option. It’s always possible to change the connection string to “properly” use SQL Server later:

Change Data Source Dialog Box

Visual C# 2010 Change Data Source Dialog Box

We’re then asked to select the database file to use. We can either point to an existing database file or create a new one. In my case, I had created a basic outline database in SQL Server Management Studio. Problem is, when we select this file (located using the database path location initially specified when installing SQL Server), we get an error that the file is in use and cannot proceed. I’ve tried fiddling with file and directory permissions, but to no avail.

OK – next port of call was to create a new database and edit it using the Database Explorer. Annoying, but surely workable. It turns out that this assumption was wrong.

Visual C# 2010 Entity Data Model Wizard Dialog Box

The Visual C# 2010 Entity Data Model Wizard Dialog Box – all seems well so far…

Server Not Supported Error

…but then!

So this got me thinking – I was really quite keen on using SQL Server 2012 and didn’t want to go through all the grief of either downgrading to SQL Server 2008 (which, by the way, you still cannot connect to in the “standard” way in VC# 2010), or having two alternative installations alongside one another on my PC.

A while ago now (when VS2010 Express was first released), I had a play with Visual Web Developer 2010 – although I’ve never deployed any ASP.NET in the real world (having been brought up on cheap hosting packages using PHP and MySQL – I’m not saying there’s anything wrong with PHP/MySQL just because the hosts are cheaper!). I was sure at that time I had set up a Vista PC running SQL Server 2008, IIS and Visual Web Developer – using a “standard” SQL server connection. Turns out, I had.

Visual Web Developer Change Data Source Dialog

And there it is!

This is very good news. The class library I am creating is going to be designed to work as a data provider for possibly multiple front-ends and a requirement is that it work with ASP.NET. The solution is therefore:

  1. Take the Class Library created in VC# 2010 and open in in Visual Web Developer 2010.
  2. Create the Entity Data Model (edmx) file using “Microsoft SQL Server” as the data provider.
  3. Save the project and reopen your solution in VC# 2010.

Simple! You now have a solution with a “standard” SQL Server connection (i.e. the connection string will be the same on a development and deployment machine, rather than using file access for one scenario and server access for the other). The caveat is that you still can’t use database explorer from within VC# 2010 (or Visual Web Developer for that matter – unless you want to switch to SQL Server 2008). This is not a big problem for me – I have the database open in SQL Server Management Studio and the solution open in VC# 2010 – excellent if you have a dual monitor setup! Once the edmx file has been set up initially in Visual Web Developer, you can add, update and alter Entity Objects as you wish from within VC# 2010.

Entity Model in Design View

Entity Model in Design View – note the “Add”, “Refresh” and “Delete” tabs are all functional despite the fact that VC# 2010 earlier complained about the SQL Server version.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>