Home > dotnet 4, Entity Framework > EF Code First:Running SQL scripts

EF Code First:Running SQL scripts

In this post I am going to show you how to run SQL scripts in Entity Framework 4.1 as we have seen that it’s quite easy to define our database entities using the DBSet and all the relationship using the EntityTypeConfiguration class.

The best part of this whole infrastructure classes are that they have pretty simple and clean interface which promotes writing “Clean Code” and have a nice “Single Responsibility Principle” and “Separation of concern”.

In order to run script as part of your database initialization you have to simply implement the IDatabaseInitializer interface and wire it up with your database context object.For example after your database tables are initialized and relationship set you want to create an unclustered index on a particular table and I will show you how we use the above interface to do the job.

Since we are dealing with scripts it means we will have to either write them as string literal or add them as SQL files to your project and somewhere in your project start-up read the script files. I will use the string literal approach for illustration and feel free to choose what ever approach works better for you.

In order to minimize the “Magic String” problem I am going to wrap it inside a static class as constant so that it is easy to call,maintain and unit test.
So this is how the code looks like.


POCOContextScript.cs

public static class POCOContextScript
{
   public const string CreateUnClusteredIndexOnProduct =
   @"if  exists (select * from sys.indexes
    where object_id = object_id('[dbo].[product]')
    and name = 'product_price_un_clustered_index')
    drop index [product_price_un_clustered_index] on
   [dbo].[product] with ( online = off )
    go
   create nonclustered index [product_price_un_clustered_index]
    on [dbo].[product]
    (
       [price] asc
    )
    with (pad_index  = off, statistics_norecompute  = off,
   sort_in_tempdb = off,ignore_dup_key = off, drop_existing = off,
   online = off, allow_row_locks  = on,allow_page_locks  = on)
   on [primary]
   go";
}

and now we write our DbInitializer class which will implment the IDatabaseInitializer interface.

DbInitializer.cs

public class DbInitializer : IDatabaseInitializer<POCOContext>
{
   public void InitializeDatabase(POCOContext context)
   {
      context.Database.SqlCommand (
      POCOContextScript.CreateUnClusteredIndexOnProduct
       );
   }
}

 

Nothing special here just calling the SqlCommand method of the Database object and since we wrapped our SQL inside our static class we can simply refer to the constant “CreateUnClusteredIndexOnProduct” as if it’s a property of POCOContext class.

Lets write a unit test to see how we are going to invoke and wire up rest of the stuff.

UnitTest.cs

[TestMethod]
public void Should_be_able_to_initialize_database()
{
   var dbInitializer = new DbInitializer();
   DbDatabase.SetInitializer(dbInitializer);
   var target = new POCOContext();
   target.Database.Initialize(true);
}

As you can I am creating a new instance of our DbInitializer class and passing that object to SetInitializer method of DbDatabase object provided by Entity Framework 4.1 and when I call the initialize method of my POCOContext instance the script gets executed and the unclustered index gets created.

In a large scale real life project you probably will have many configuration class and database initializer class and the above could should be wired up with a BootStrapper class and should use an IoC.

Advertisements
  1. jwizecom
    May 5, 2012 at 8:09 am

    You can go a little further with that by creating a direction in you application and save your .sql files to it. Set the sql files to EmbeddedResource and then just fetch the string and execute during your Seed method.

    internal string GetFromResources(string resourceName)
    {
    Assembly assem = this.GetType().Assembly;
    using (Stream stream = assem.GetManifestResourceStream(resourceName))
    {
    using (var reader = new StreamReader(stream))
    {
    return reader.ReadToEnd();
    }
    }
    }

    context.Database.ExecuteSqlCommand(GetFromResources(“Ellevate.BusinessCards.Db.SQL.GetLatestActions.sql”));

    Just remember to delete the code at the beginning of your procedure that selects your database.

    • May 11, 2012 at 11:38 pm

      Hi,
      Nice suggestion and embedding the resource as part of the assembly and fetching it is a nice approach.

  2. jwizecom
    May 12, 2012 at 12:21 am

    oops, *Directory not direction, Thanks

  3. Anonymous
    January 11, 2013 at 1:19 pm

    It’s difficult to find educated people on this subject, but you seem like you know what you’re talking
    about! Thanks

  4. Gwen
    February 15, 2013 at 12:56 am

    What’s up, just wanted to mention, I enjoyed this post. It was inspiring. Keep on posting!

    • February 21, 2013 at 6:49 pm

      Thanks Gwen & your word of appreciation is quite inspiring. I’ll try to keep posting quality stuff as much as i can đŸ™‚

  5. Tyf0x
    July 29, 2013 at 7:11 am

    Thanks for this post, was exactly what I was looking for.

    As an addition to jwizecom comment, you also can use

    assem.GetManifestResourceNames().Where(r => r.EndsWith(“.sql”));

    to get all sql ressource embedded in the assembly and then iterate through them.

  1. May 11, 2011 at 8:40 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: