Thursday, January 31, 2013

Restoring a DB when the database is “In Use”

We’ve all run into this issue before. We have a backup of a database that we need to restore into a DEV/QA environment, but when we attempt to restore, we get the “System.Data.SqlClient.SqlError:  Exclusive access could not be obtained because the database is in use

This error can be very frustrating to get around and a person can spend a LONG time trying to kill all of the attached queries/threads. I know I’ve (almost) lost my sanity on more than 1 occasiaion trying to do this.

The good news is that I’ve found a simple/easy way to restore a backup when the database is in use


  • Setting the database to single user (you!)
  • restore the backup
  • set it back to multiuser

Here is the SQL Script I use.


Simple and fast. Enjoy!

Thursday, January 24, 2013

Repository Pattern with Cross Table Objects


In a recent post, I mentioned one of the advantages of using Entity Framework in your application is that you can build a generic repository class very easily. Of course, its not realistic to think that all access to the data will be a single table at a time. Most often it’s the case where you need to return data that spans multiple tables.

I’m  going to show you how I created a simple repository class that spans tables.

Creating the Summary/DTO Object

The first thing I like to start with is to create the simple POCO object that will be used to transport the data. This is essential to define first so that you do not get caught up in data structures, but instead define the data as the application is going to need it.

In the case with my database, I have a table called “Avail” that contains a ton of foreign keys to a contact table. I needed to display this data, but instead of a bunch of foreign keys, I needed to display the actual names of people. etc.

I ended up defining the object as follows:


Creating the Repository Class

After the DTO Object is created, its just a matter of creating a repository class that manages this object. In my implementation of the repository, I have an IRepository Interface that all of the repositories use to promote ease of understanding when working with them:


On top of this, I created a separate IFullAvailRepository so that I could customize this down the road if necessary with custom calls. For now, its empty.


I then created the repository class an implement the interface:


Implementing the interface

Now the hard (easy?) part. Using the power of Entity Framework and LINQ to Entities, implementing the interface for a custom object is actually quite easy. The most important part is now the objects are retrieved from the data context. By using an IQueryable object, we can abstract out filling the custom object into a simple property, and the rest of the methods just leverage that property.


Using the repository

Using this implementation, the DB isn’t actually hit until needed, so we can do queries to retrieve objects or sets of objects and only the items requested are in memory, instead of loading all of the items and doing the queries there. For example, this unit test retrieves 1 specific item out of the database, and its super fast.




I’m new to the repository pattern, but so far I really like the abstraction is provides to the data layer. I’m sure I’ll run into issues in the future, but so far, its working great!

Friday, January 11, 2013

Showing System message to user when logging in

As part of my role at my company, I’m responsible for deploying code to servers as well as troubleshooting issues with environments as they come up. As a result of this, I’m typically logged into multiple servers at a time. As anyone who does this sort of thing knows, it can be very easy to forget which environment you are in and accidentally modify the wrong server at the wrong time. If this happens to be a production server, the consequences can be catastrophic.

Here is a nice little trick I use to make sure that people know which server they are accessing.
Remote desktop into the server and open Windows Task Scheduler: clip_image001
On the right hand side in the Actions pane click "Create task:" clip_image002
When the Create task window opens, on the General Tab enter a name:

Click on the Triggers tab and click the "New..." button
On the New Trigger window, select "At Log on" in the Begin the Task dropdown and make sure "Any User" is selected in the Settings box and click "OK". This will show the message when a user logs in to the server.

Add another trigger and select "On connection to user session". This will show the message when a user reconnects to an existing user session.

Click on the Actions tab and click "New..."
In the Action dropdown, select "Display a message". Then enter a title for your message and the message you want to show to the user:
Click "OK" to close the Create Task window.

Now when the user logs in or reconnects to a session, you'll see the message:

Wednesday, January 9, 2013

Entity Framework -Revisited

I'm starting a new project soon for a new client and I've finally had some time to do some prep work to set up the skeleton of the solution/projects as well as investigate some "new" technologies. One of the things that I've revisited is Entity Framework(EF). I thought a great way to get my blogging going again was to post my thoughts on EF.

<gasp>Turns out is not as bad as I thought it was...</gasp>

Why I hate(d) Entity Framework:

Until 4 weeks ago, I was not a fan of EF. This hate of EF was born out of early adoption of the technology (V1.1) and attempting to implement this in an N-Tier Application. The pain points were many and there were numerous times when we almost put the project on hold for a week while we ripped out all Entity Framework pumping and re-laid L2SQL  Each time the pain of doing so seemed to out weight the benefits and then 2 days later we would uncover something ELSE that would make EF seem even worse. This would then force us to revisit the EF vs. L2SQL thing again with the same resolution.

Note: I will allow that we were all moving really quickly in the project and that none of us really understood EF to the level that we all should have, but the idea of the ORM in general is to make the lives of developers easier. (If it can't do that, then there is no reason to use a technology IMO). However, if we had known how it really worked, we probably would not have used it, so........

Here are 2 of the many reasons why it was hard to use:


Reason 1:

If you just wanted to update the foreign key on a row to a different foreign key, you had to use EF to pull that item out of the Database and then assign that item to the navigation property. So if an address table had a foreign key to the state table (StateId), you couldn't just update the value in the StateId column to the proper state, you had to set the Address.State object and then save changes.

Reason 2:

There was a large amount of work/tracking that the code (and hence the developer) had to do to track the various states of the objects through the life cycle. Essentially pulling data out of the database was nice and easy, but inserting or updating data was a nightmare. We would end up with duplicate entries in the database, or exceptions thrown by the EF context for primary key constraints as the context would attempt to insert an object instead of updating an existing item.

There are many more reasons, but for the sake of sanity, I've repressed those into the dark recesses of my mind which I hope never again see the light of day.

How much did I really hate it?

It was so difficult that eventually we stopped any new development with EF and created a L2SQL context that we started using from that point with any new code. While it would seem like managing two ORM instances would be a pain, EF was so immature that it seemed less painful to us.

Additionally, On the 2 subsequent projects I've been on, we've exclusively used L2SQL, even though there hasn't been any new Features or developments in that technology for a while, and is for the most part considered Dead. It did what we needed it to do, and kept simple things simple. What else can you ask for?

2nd chance?

I've been to a few conferences and MSFT events over the past couple of years that piqued my interest in the newer versions, but my previous experiences prevented me from actually looking into it as a viable ORM for future projects. It still didn't have the feel of an enterprise solution, especially when all I would see mentioned in the talks  would be the code first approach. While the code first approach is "cool" IMO I its not something an enterprise app could/would use, much like RIA services for Silverlight. Potentially useful in small rapid application development scenarios, but not much elsewhere

However, I decided to take another look and I was pleasantly surprised. Here are the things I'm finding super useful:
DBContext object
The introduction of the DBContext object makes working with objects much easier. It abstracts away most of the dirty work of attaching/detaching and monitoring objects that are going in and out of the database. There is a lot of information about there about this that I wont reproduce here, but it simplifies working with the database context and the object graph much easier.
If you need more functionality than what is exposed in the DBContext object, you can still use the object context for more operations that are closer to the metal.
If you are able to accomplish all of your work with the DBContext, upgrading to a newer version of EF is supposed to be pain free as the DBContext object is "guaranteed" to be supported in future versions of EF
Object Inheritance:
Entity Framework now supports multiple types of Entity structures(Table Per type, Table per Hierarchy, and Table per Concrete Type). For us this is going to be immensely helpful. We're working off of an existing database, that has tables that contain data for multiple types of entities so by setting up inheritance in the model we can use built in EF functionality to extract just the types we need.

Example: We have a large "Contact" table that has Vendors, Companies, People etc. We simply create a "Person" entity and base it off of the type field on the database. (Great explanation here)

While the idea of inheritance isn’t brand spanking new to EF, its something we never took advantage of before and I have to say I’m super excited about it.

Enforcement of Non-existent relationships
As mentioned, we're working with an existing database. Unfortunately, the people who created the database didn't believe in using  relationships to enforce data integrity. While this has the "nice" side effect of making data cleansing easy by being able to delete almost any row out of any table, it destroys the massive benefit of navigation properties in the ORM. Entity Framework allows you to add the relationships directly into the model. This allows the code to enforce the relationship, even though the database does not.
Simply open the design surface, select Relationship and drag the relationship between the entities. The properties of the relationship can be modified to match you specific scenario (Multiplicity, name, etc.)

Creation of a generic EF Repository Class
While this might not be something that everyone needs (or even endorses) Entity Framework does allow you to create a simple Repository Class that can be used for all entities in the model. The use of DBContext.Set() allows you to create a repository class of type TEntity and do all manner of operations generically, thus reducing the amount of code needed for a simple repository (Context name blurred to protect the innocent)

Cross your fingers!

I hoping that since I've done more investigation into EF this time and understand it a bit better, that we'll be more successful using it and hopefully reap the benefits of the good work of the folks on the Entity Framework team. I'll post more later as a follow up to let everyone know how it went!