Disabling the pluralization of table names when using database migrations

To disable the pluralizing of database names create a custom class that inherits from the DBContext class. In that class override the OnModelCreating method and call the Remove method of the dbModelBuilder.Conventions class as below.

using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;

namespace DemoApplication.Models
{
    public class EFDbContext : DbContext
    {
        //the base contains the name of the connection string provided in the web.config
        // this constructor should disable migrations
        public EFDbContext()
            : base("EFDbContext")
        {}

        protected override void OnModelCreating(DbModelBuilder dbModelBuilder)
        {
            dbModelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        }

        public DbSet<Query> Query { get; set; }
    }
}

Disabling Entityframework Migrations

I have found a couple ways migrations can be turned off. The version of Entityframework you are using may determine which method you use.

The first method is to set the AutomaticMigrationsEnabled property to false in the DBMigrationsConfiguration class.

using System.Data.Entity.Migrations;
 
namespace DemoApplication.Migrations
{
    internal sealed class Configuration : DbMigrationsConfiguration<MyDBContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = false;
        }
 
        protected override void Seed(MyDBContext context)
        {
            //  This method will be called after migrating to the latest version.
 
            //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
            //  to avoid creating duplicate seed data. E.g.
            //
            //    context.People.AddOrUpdate(
            //      p => p.FullName,
            //      new Person { FullName = "Andrew Peters" },
            //      new Person { FullName = "Brice Lambson" },
            //      new Person { FullName = "Rowan Miller" }
            //    );
            //
        }
    }
}

The second method is to set a null Database Initializer in the constructor of the DBContext class. I have used this method in projects using Entityframework 6 where DBMigrationsConfiguration class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;

namespace DemoApplication.Models.Concrete
{
    public class EFDbContext : DbContext
    {
        //the base contains the name of the connection string provided in the web.config
        // this constructor should disable migrations
        public EFDbContext()
            : base("EFDbContext")
        {
            //disable initializer
            Database.SetInitializer<EFDbContext>(null);
        }

        public DbSet<Query> Query { get; set; }
    }
}

In any case, if there is a _MigrationHistory table in the database then delete it. This will be a user table in EF6. I believe it will show up as a system table in earlier versions of Entityframework.

As a last note. Some people have reported to have used disable-migrations in the Package Manager Console window. However when I have tried this in the past on EF6 it has not worked.

PM> disable-migrations
The term ‘disable-migrations’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify
that the path is correct and try again.
At line:1 char:19
+ disable-migrations <<<<
+ CategoryInfo : ObjectNotFound: (disable-migrations:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

Adding Entityframework Data Access To A Project

Most new projects require some sort of data access method. I used the following steps with an MVC application in Visual Studio 2013, they should be enough to get you started. I am going to assume you have already have an MVC ready project open. To keep the code snippets short not all the namespace references are shown, just the key ones.

Setup the Database
It is up to you what type of database you use, SQLExpress, SQL Server or something else. Create a database and set the permissions as needed, most likely for development you will use integrated security.
For starters add a table to hold product data with the below script.

CREATE TABLE Products
(
    [ProductID] INT NOT NULL PRIMARY KEY IDENTITY,
    [ProductCode] NVARCHAR(100) NOT NULL,
    [Description] NVARCHAR(500) NOT NULL,
    [UnitPrice] DECIMAL(9, 2) NOT NULL
)

For now populate the table with some dummy data.

INSERT INTO Product ([ProductCode],[Description],[UnitPrice]) VALUES ('P1234', 'Torch', 23.99)
INSERT INTO Product ([ProductCode],[Description],[UnitPrice]) VALUES ('P3456', 'Toaster', 15.90)
INSERT INTO Product ([ProductCode],[Description],[UnitPrice]) VALUES ('P7890', 'Football', 10.00)
INSERT INTO Product ([ProductCode],[Description],[UnitPrice]) VALUES ('P1037', 'Hair Net', 3.50)
INSERT INTO Product ([ProductCode],[Description],[UnitPrice]) VALUES ('P1634', 'Cooker', 156.99)

Creating a table in the database is actually optional, if you go full Code-First and don’t create a table it will be created automatically by the DBInitializer on the first call requiring the table when the application is run. Just be aware that there will not be any data in the table initially.

Setup EntityFramework
Open the Package Manager Console by going to Tools > Nuget Package Manager > Package Manager Console. At the NuGet command line enter this command to install EntityFramework

Install-Package EntityFramework -projectname DemoApplication

the -projectname switch forces EntityFramework to be installed in a specific project. If you have only one project in your solution you could use

Install-Package EntityFramework

Create a Domain Model

  • In your application right-click the Models folder and select Add > Class… (Ctrl+Shift+A).
  • In the Add New Item dialog, select Class from the template list and enter a suitable name for your class e.g. Product.cs
  • Select the Add button
  • Edit the class with the properties you need
    namespace DemoApplication.Models
    {
        public class Product
        {
            public int ProductID { get; set; }
            public string ProductCode { get; set; }
            public string Description { get; set; }
            public decimal UnitPrice { get; set; }
        }
    }
    

Create a DBContext Class

  • In your application right-click the Models folder and select Add > Class… (Ctrl+Shift+A).
  • In the Add New Item dialog, select Class from the template list and enter a suitable name for your DBContext class e.g. EFDBContext.cs
  • Select the Add button
  • Edit the class similar to this
    using System.Data.Entity;
    
    namespace DemoApplication.Models 
    {
        public class EFDbContext : DbContext 
        {
            public DbSet&lt;Product&gt; Products { get; set; }
        }
    }
    

    The name of the property Products is the name of the table Entityframework will access. The type specified in the DBSet is the object Entityframework will populate.

Add a Connectionstring to Config
The connection string will depend on which database you connected to. AS I was using SQL Server my connectoin string looks similar to this.

&lt;connectionStrings&gt;
    &lt;add name=&quot;EFDbContext&quot; connectionString=&quot;Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=True&quot; providerName=&quot;System.Data.SqlClient&quot;/&gt;
&lt;/connectionStrings&gt;

Create a Repository

  • First of all an interface, In your application right-click the Models folder and select Add > New Item… (Ctrl+Shift+A).
  • In the Add New Item dialog, select Interface from the template list enter a suitable name for your repository interface e.g. IProductRepository.cs
  • Select the Add button
  • Edit the repository interface
    using SportsStore.Domain.Entities;
    namespace DemoApplication.Models
    {
        public interface IProductRepository
        {
            IEnumerable&lt;Product&gt; Products { get; }
        }
    }
    
  • Next, the concrete Repository class. In your application right-click the Models folder and select Add > New Item… (Ctrl+Shift+A).
  • In the Add New Item dialog, select Class from the template list enter a suitable name for your repository e.g. EFProductRepository.cs
  • Select the Add button
  • Edit the ProductRepository class as below
    using System.Collections.Generic;
    
    namespace DemoApplication.Models 
    {
        public class EFProductRepository : IProductRepository 
        {
            private EFDbContext context = new EFDbContext();
            public IEnumerable&lt;Product&gt; Products 
            {
                    get { return context.Products; }
            }
        }
    }
    

Controller Setup
In your controller, add a Product Repository

using DemoApplication.Models;

namespace DemoApplication.Controllers
{
    public class ProductController : Controller
    {
        private IProductRepository repository;

        public ProductController()
        {
            this.repository = new ProductRepository;
        }

        public ViewResult Index() 
        {
            return View(repository.Products);
        }      
    }
}

Controller Setup
The very last thing to do is set up the view to display a list of products.

@model IEnumerable&lt;DemoApplication.Models.Product&gt;
@{
 ViewBag.Title = &quot;Products&quot;;
}
&lt;table&gt;
@foreach (var product in Model) {
&lt;tr&gt;
&lt;td&gt;@product.ProductCode&lt;/td&gt;
&lt;td&gt;@product.Description&lt;/td&gt;
&lt;td&gt;@product.UnitPrice.ToString(&quot;c&quot;)&lt;/td&gt;
&lt;/tr&gt;
}&lt;/table&gt;

This is a bit of a whirlwind tour but hopefullly enough information to get a project going in accessing data with Entityframework.

Running website from shared folder on server in DMZ

So we have two load balanced servers in the DMZ (Demilitarized Zone), these servers are not connected to our company domain, so there are no domain users or domain drives. Normally we would locate the files for a website on each server, there was a requirement for this particular website to locate the files on a shared location. What we did was to have the website files on one server in the DMZ and to configure the sites in IIS (Internet Information Services) on both servers to point to the single shared location.

This is how we configured the sites, on the main server.

  1. Create a local user
    1. Navigate to Control Panel > Administrative Tools > Computer Management > Local Users and Groups.
    2. Right-click Users and select New User from the context menu.
    3. Enter values for User name, Full name, Description, Password and Confirm password.
    4. Ensure User must change password at next logon is unticked.
    5. Ensure User cannot change password is ticked.
    6. Ensure Password never expires is ticked.
    7. Ensure Account is disabled is unticked.
  2. Create a local folder e.g. C:\Websites\MySite.
  3. Copy the website files to this new folder.
  4. Share the website folder
    1. On the properties of the newly created folder (right-click > properties)
    2. Select the Sharing tab.
    3. In the File Sharing dialog add the local user previously created and set appropriate permissions e.g. Read/Write.
    4. Click the Share button close the dialog.
    5. Click the Close button to close the Properties dialog.
  5. Set up IIS. After opening IIS
    1. Create a new application pool. Set the appropriate .Net version and application pool identity. On my setup this was .Net4.0 and AppPoolIdentity respectively.
    2. Create a new site. Set the Physical Location to be the shared folder e.g. \\Server1\MySite. Also set the application pool the one just created.
    3. Set bindings domain name bindings as required.
    4. In IIS click the new site to display the Features View.
    5. In the Features View, double click the Authentication icon. Right-click Anonymous Authentication and select Edit from the context menu.
    6. In the Edit Anonymous Authentication Credentials dialog select the Application Pool identity radio button and click the OK button to close the dialog.

TheĀ first server is now configured and the website should run at this point. Setting up the second server requires similar actions. Carry out the following on the second server in the DMZ.

  1. Create a local user by completing point 1 above. Ensure the same credentials and settings are used.
  2. Set up IIS. After opening IIS
    1. Create a new application pool. Set the appropriate .Net version, e.g. .Net4.0. Set the application identity as the local user created above.
    2. Create a new site. Set the Physical Location to be the shared folder from first server e.g. \\Server1\MySite. Also set the application pool the one just created.
    3. Set bindings domain name bindings as required.

That should be about it for the second server. Both the servers should serve up the sites.

Disclaimer: By posting this information I am not suggesting this as the ‘right way’ or ‘best practise’ it was something we did temporarily to quickly get a site up and running without opening up file wall ports in to the company domain servers.

How to use a SQL cursor to iterate data.

It has been a while since I have used a cursor to manipulate data. The DBA’s I have spoken to have differing opinions on whether or even when they should be used. I am not a SQL specialist but I do tend to use them for one off tasks or for scheduled jobs that normally run our of hours.

I have included a template cursor here for reference for the next time.

-- Declare the list of fields here that you need access to withing the cursor
-- The fields below are an example.
DECLARE @AccountNo VARCHAR(10)
DECLARE @AccountName VARCHAR(255)
DECLARE @FirstName VARCHAR(50)
DECLARE @LastName VARCHAR(50)
DECLARE @Email VARCHAR(255)

DECLARE @MainCursor CURSOR
SET @MainCursor = CURSOR FAST_FORWARD
FOR
	-- Here we query the database retrieving the fields needed
	SELECT AccountNo, AccountName, FirstName, LastName, Email FROM Accounts
OPEN @MainCursor
	-- And execute the initial fetch for the first row of data
	FETCH NEXT FROM @MainCursor
	INTO @AccountNo, @AccountName, @FirstName, @LastName, @Email
WHILE @@FETCH_STATUS = 0
BEGIN

	/*
		Data manipulation statements go here.
	*/

	-- fetch next row if it exists 
	FETCH NEXT FROM @MainCursor
	INTO @AccountNo, @AccountName, @FirstName, @LastName, @Email
END
CLOSE @MainCursor
DEALLOCATE @MainCursor