Meet Entity Framework, the Anti-SQL Framework

Entity Framework 6 is coming! Entity Framework 6 is now ramping up for a release. It brings nice Async functionality, but also gives lots more power to the Code First capability, as well as also bringing EF completely out of the core .NET framework - it's instead now a standalone NuGet package. So, the story … Continue reading Meet Entity Framework, the Anti-SQL Framework

Why Entity Framework renders the Repository pattern obsolete?

A post here on a pattern I thought was obsolete yet I still see cropping up in projects using EF time and time again... What is a Repository? The repository pattern – to me – is just a form of data access gateway. We used it to provide both a form of abstraction above the … Continue reading Why Entity Framework renders the Repository pattern obsolete?

Can’t wait for C#4…

Following on from my posts on dynamic typing… this week I wrote a quick app to import some data from a load of XML files to a load of tables in a SQL database. The columns in the tables and the attributes in the xml file have a 1:1 mapping. So I firstly wrote some code which goes through a given table and reads the schema from the database and creates a C# type based on the schema (using sp_columns). Then I placed a ColumnAttribute on each column etc. etc., and then goes through the task of iterating through the XML data and creating objects of that new type, copy the data across (doing the odd data type conversion along the way), and finally use the standard LinqToSql DataContext to insert the data into the DB. But a lot of the code was somewhat ugly – messing about with TypeBuilders and FieldBuilders and the like – I’m sure that I could have done something much more elegant in C#4…. none of it was compile-time safe, it was all using reflection etc.. But it did work great anyway – apparently was used to migrate a good few thousand records of XML data into SQL relatively quickly 🙂

Installing SQL Management Studio 2008 Express

When installing SQL Express 2008, you may get the following error if you already have VS 2008 installed: Rule “Previous releases of Microsoft Visual Studio 2008” failed. A previous release of Microsoft Visual Studio 2008 is installed on this computer. Upgrade Microsoft Visual Studio 2008 to the SP1 before installing SQL Server 2008. I’ve read a load of blogs about this issue, but none of them solved the problem that I was having, as I already had VS 2008 SP1 installed. Galin Iliev’s excellent blog post comes close but doesn’t quite hit the bullseye (although I believe that his solution would probably work as well). The problem is that I have Visual Studio 2008 database edition installed as well. So, from Galin’s post, I found that the following registry key stores version settings of VS: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DevDiv\VS\Servicing\9.0 Going in there, I see not one, but three sub-keys: - I’ve annotated the above with what I guess the different keys represent. Inside each of those keys are a load of properties, one of which is named SP. VSTD is set to 1 (correctly), but VSDB and VSDBGDR are both set to 0. Solution: Ensure that all three keys have the “SP” value set to 1. It appears that the SQL installer expects all of them to be set to 1 otherwise it won’t install… P.S. Probably best to set them back to 0 after the install 🙂

SQL Express – more aggravation

So, I installed SQL Express 2005 only to find that when removing SQL Express 2008, and then manually removing those two components (2008 object model and 2008 native client) that I’d somehow “broken” 2005 (even though I hadn’t yet installed it!). I guess there are some assemblies that are overwritten by the 2008 install that are backwards compatible – so when I uninstalled the 2008 items that the 2008 uninstall left behind, it screwed me completely. So, reinstalled them – and finally got SQL Express 2005 working. Until I accidentally changed the default database for my Windows account to my custom database that I was working on and then deleted it by accident! Now I can’t even log in to SQL. So I had to use SQLCMD to log in and change my default database back to master. Phew…. all this just to prepare a demo of a few features of C# 3 and LINQ!

SQL Express 2008 – Frustration in 10 easy steps!

I was looking for the SQL Express 2005 edition and came across the 2008 one. So I thought I’d give it a try – what a waste of time! Ran the installer for SQL Express 2008 Wouldn’t install because I didnt have the latest version of Windows Installer (4.5). Downloaded Windows Installer 4.5 Installed Windows Installer 4.5 Rebooted Wouldn’t install because I didn’t have Windows Powershell installed Downloaded Windows Powershell Installed Windows Powershell Ran the installer for SQL Express 2008 Wouldn’t install because I didn’t have Visual Studio 2008 SP1 installed – except I did! Gave up after this and just got the old 2005 version of SQL Express. But not before I had to manually uninstall half of the rubbish the 2008 installer left behind on my PC.

Deploying SQL Compact databases – Part III

I ended up using ClickOnce deployment instead of Windows Installer after all! This is because I uninstalled the beta of VS2008 and installed the "proper" version of VS 2008 Express - which doesn't support Setup projects. ClickOnce is fairly easy to set up - the only thing that I couldn't work out was how to get it to notice Content files in dependant projects for deployment purposes i.e. the .sdf file that is the database for the application is in another project, and doesn't get picked up by the ClickOnce publisher. So I had to move the .sdf file into my main project that is published - not ideal but not the end of the world. I have also then found that after installing and running the first time, occasionally after the install, the database file can't be found by the application. Once you run it a second time, it works fine - but the first time on install it doesn't always work. Weird.

Deploying SQL Compact databases – Part II

I found a solution to the aforementioned .sdf problem but it's one that I would think is ideal or "clean". Firstly, assuming that you are using the VS-generated data adapters (you might have written your own instead) - the connection string when created is an absolute path to your database file e.g. C:\Users\Isaac\My Documents\Visual Studio 2008\Projects\MySolution\MyDataTierProject\MyDatabase.sdf Obviously this is a completely useless path for when it comes to deploying your application. To get around this, there seem to be a couple of choices. The first one that I thought of was to make an install-time task which modifies this page to wherever your .sdf gets deployed - most likely this is the same directory that your application lives in. However, this solution seemed quite a lot of work. I eventually came across a couple of somewhat useful articles on MSDN, this was the most useful one: Working with local databases (Ironically enough written for VS2005, not 2008). It talks about the DataDirectory "macro" - it's effectively a property that gets dynamically resolved by .NET at run time, depending on how the application has been deployed (either Windows Installer or ClickOnce). For Windows Installer packages, it'll just look in the current directory - which was exactly what I wanted. However, this posed a problem for development time - I wanted to use, at development time, my development .sdf file which I have been working on - not the one that gets copied to the \bin\release or \bin\debug directories. The solution was to edit the settings.cs file for the project which contains the connection string to override it. This wasn't as simple as it sounds as the ConnectionString is an Application-level property, and therefore read-only in C#. However, you can change the path that DataDirectory points to by using the AppDomain classes. Here's what I did: void Settings_SettingsLoaded(object sender, System.Configuration.SettingsLoadedEventArgs e) { // Only do the following code to set the path of the database if in debug mode #if DEBUG System.IO.DirectoryInfo ProjectDir = new System.IO.DirectoryInfo(System.IO.Directory.GetCurrentDirectory()); ProjectDir = new System.IO.DirectoryInfo(ProjectDir.Parent.Parent.Parent.FullName + "\\MyDataProject"); System.AppDomain.CurrentDomain.SetData("DataDirectory", ProjectDir.FullName); #endif } [Code Snippet plugin for Windows Live Writer available from here.] This checks to see if we're in debug mode, and if we are, updates the DataDirectory to point to the correct location. It still feels "wrong" though. I'd prefer to have some way to amend the connection string at install time to DataDirectory instead of having to write some code to check if we're in debug mode. Any ideas more than welcome.

Deploying SQL Compact databases in VS 2008 installer packages

I'm writing a C# small application in VS 2008 which has a SQL Compact (.sdf) database used to persist data (funnily enough). I'm writing the installer package for it - wondering whether to use standard Windows installer technology or ClickOnce. I'm leaning towards the former because it seems like I'll get more control over it that way. I need to be able to ensure that the connection string to the SDF file is correct. Bear in mind that I'm using the connection string setting that is created when you hook up the sdf to your DataSet - so I'll need some way of changing the app.config file post-install. I imagine that I should also run some check to see if the database already exists or not in the location (as if the user is installing a newer version over the top they wouldn't want to lose all their data). I have no idea how to go about writing custom install-time tasks like this. Anyone got any ideas?