DATABASE: SEED SQL SERVER DATABASE FROM EXCEL SPREADSHEET.xls file example for entity framework codefirst and sqlserver. Also show repository pattern.

ApiUser.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CountingKs.Data.Entities
{
  public class ApiUser
  {
    public int Id { get; set; }
    public string Name { get; set; }
    public string Secret { get; set; }
    public string AppId { get; set; }
  }
}

AuthToken.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CountingKs.Data.Entities
{
  public class AuthToken
  {
    public int Id { get; set; }
    public string Token { get; set; }
    public DateTime Expiration { get; set; }
    public ApiUser ApiUser { get; set; }
  }
}

CountingKsContext.cs

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CountingKs.Data.Entities;

namespace CountingKs.Data
{
  public class CountingKsContext : DbContext
  {
    public CountingKsContext()
      : base("DefaultConnection")
    {
      this.Configuration.LazyLoadingEnabled = false;
      this.Configuration.ProxyCreationEnabled = false;
    }

    static CountingKsContext()
    {
      Database.SetInitializer(new MigrateDatabaseToLatestVersion<CountingKsContext, CountingKsMigrationConfiguration>());
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      base.OnModelCreating(modelBuilder);

      CountingKsMapping.Configure(modelBuilder);
    }

    public DbSet<ApiUser> ApiUsers { get; set; }
    public DbSet<AuthToken> AuthTokens { get; set; }
    public DbSet<Food> Foods { get; set; }
    public DbSet<Measure> Measures { get; set; }
    public DbSet<Diary> Diaries { get; set; }
    public DbSet<DiaryEntry> DiaryEntries { get; set; }
  }
}

CountingKsMapping.cs

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using CountingKs.Data.Entities;

namespace CountingKs.Data
{
  public class CountingKsMapping
  {
    public static void Configure(DbModelBuilder modelBuilder)
    {
      MapFood(modelBuilder);
      MapMeasure(modelBuilder);
      MapDiaryEntry(modelBuilder);
      MapDiary(modelBuilder);
      MapApiUser(modelBuilder);
      MapApiToken(modelBuilder);
    }

    static void MapApiToken(DbModelBuilder modelBuilder)
    {
      modelBuilder.Entity<AuthToken>().ToTable("AuthToken", "Security");
    }

    static void MapApiUser(DbModelBuilder modelBuilder)
    {
      modelBuilder.Entity<ApiUser>().ToTable("ApiUser", "Security");
    }

    static void MapFood(DbModelBuilder modelBuilder)
    {
      modelBuilder.Entity<Food>().ToTable("Food", "Nutrition");
    }

    static void MapMeasure(DbModelBuilder modelBuilder)
    {
      modelBuilder.Entity<Measure>().ToTable("Measure", "Nutrition");
    }

    static void MapDiaryEntry(DbModelBuilder modelBuilder)
    {
      modelBuilder.Entity<DiaryEntry>().ToTable("DiaryEntry", "FoodDiaries");
    }

    static void MapDiary(DbModelBuilder modelBuilder)
    {
      modelBuilder.Entity<Diary>().ToTable("Diary", "FoodDiaries");
    }

  }
}

CountingKsMigrationConfiguration.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity.Migrations;
using System.Data.OleDb;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Hosting;
using CountingKs.Data.Entities;

namespace CountingKs.Data
{
  public class CountingKsMigrationConfiguration : DbMigrationsConfiguration<CountingKsContext>
  {
    public CountingKsMigrationConfiguration()
    {
      this.AutomaticMigrationsEnabled = true;
      this.AutomaticMigrationDataLossAllowed = true;
    }

#if DEBUG
    protected override void Seed(CountingKsContext context)
    {
      // Seed the database if necessary
      new CountingKsSeeder(context).Seed();
    }
#endif
  }
}

CountingKsRepository.cs

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CountingKs.Data.Entities;

namespace CountingKs.Data
{
  public class CountingKsRepository : ICountingKsRepository
  {
    private CountingKsContext _ctx;
    public CountingKsRepository(CountingKsContext ctx)
    {
      _ctx = ctx;
    }

    public IQueryable<Food> GetAllFoods()
    {
      return _ctx.Foods;
    }

    public IQueryable<Food> GetAllFoodsWithMeasures()
    {
      return _ctx.Foods.Include("Measures");
    }

    public IQueryable<Food> FindFoodsWithMeasures(string searchString)
    {
      // Dumb search but adequate for our tests
      return _ctx.Foods.Include("Measures").Where(f => f.Description.Contains(searchString));
    }


    public IQueryable<Measure> GetMeasuresForFood(int foodId)
    {
      return _ctx.Measures.Include("Food")
                          .Where(m => m.Food.Id == foodId);
    }

    public Food GetFood(int id)
    {
      return _ctx.Foods.Include("Measures").Where(f => f.Id == id).FirstOrDefault();
    }

    public Measure GetMeasure(int id)
    {
      return _ctx.Measures.Include("Food")
                          .Where(m => m.Id == id)
                          .FirstOrDefault();
    }

    public IQueryable<Diary> GetDiaries(string userName)
    {
      return _ctx.Diaries.Include("Entries.FoodItem")
                         .Include("Entries.Measure")
                         .OrderByDescending(d => d.CurrentDate)
                         .Where(d => d.UserName == userName);
    }

    public Diary GetDiary(string userName, DateTime day)
    {
      return GetDiaries(userName).Where(d => d.CurrentDate == day.Date).FirstOrDefault();
    }

    public IQueryable<DiaryEntry> GetDiaryEntries(string userName, DateTime diaryDay)
    {
      return _ctx.DiaryEntries.Include("FoodItem")
                              .Include("Measure")
                              .Include("Diary")
                              .Where(f => f.Diary.UserName == userName && 
                                          f.Diary.CurrentDate == diaryDay);
    }

    public DiaryEntry GetDiaryEntry(string userName, DateTime diaryDay, int id)
    {
      return _ctx.DiaryEntries.Include("FoodItem")
                              .Include("Measure")
                              .Include("Diary")
                              .Where(f => f.Diary.UserName == userName &&
                                          f.Diary.CurrentDate == diaryDay &&
                                          f.Id == id)
                              .FirstOrDefault();
    }

    public IQueryable<ApiUser> GetApiUsers()
    {
      return _ctx.ApiUsers;
    }

    public AuthToken GetAuthToken(string token)
    {
      return _ctx.AuthTokens.Include("ApiUser").Where(t => t.Token == token).FirstOrDefault();
    }

    public bool SaveAll()
    {
      return _ctx.SaveChanges() > 0;
    }

    public bool Insert(AuthToken token)
    {
      try
      {
        _ctx.AuthTokens.Add(token);
        return true;
      }
      catch
      {
        return false;
      }
    }

    public bool Insert(DiaryEntry entry)
    {
      try
      {
        _ctx.DiaryEntries.Add(entry);
        return true;
      }
      catch
      {
        return false;
      }
    }

    public bool Insert(Diary diary)
    {
      try
      {
        _ctx.Diaries.Add(diary);
        return true;
      }
      catch
      {
        return false;
      }
    }

    public bool Update(DiaryEntry entry) 
    {
      return UpdateEntity(_ctx.DiaryEntries, entry);
    }

    public bool Update(Diary diary)
    {
      return UpdateEntity(_ctx.Diaries, diary);
    }

    public bool DeleteDiaryEntry(int id) 
    {
      try
      {
        var entity = _ctx.DiaryEntries.Where(f => f.Id == id).FirstOrDefault();
        if (entity != null) 
        {
          _ctx.DiaryEntries.Remove(entity);
          return true;
        }
      }
      catch
      {
        // TODO Logging
      }

      return false;
    }

    public bool DeleteDiary(int id)
    {
      try
      {
        var entity = _ctx.Diaries.Where(d => d.Id == id).FirstOrDefault();
        if (entity != null)
        {
          _ctx.Diaries.Remove(entity);
          return true;
        }
      }
      catch
      {
        // TODO Logging
      }

      return false;
    }

    // Helper to update objects in context
    bool UpdateEntity<T>(DbSet<T> dbSet, T entity) where T : class
    {
      try
      {
        dbSet.AttachAsModified(entity, _ctx);
        return true;
      }
      catch
      {
        return false;
      }
    }

  }
}

CountingKsSeeder.cs

//#define TEST_SEED
//#define FORCE_RECREATE

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.Hosting;
using CountingKs.Data.Entities;



namespace CountingKs.Data
{
  public class CountingKsSeeder
  {
    CountingKsContext _ctx;
    public CountingKsSeeder(CountingKsContext ctx)
    {
      _ctx = ctx;
    }

    public void Seed()
    {
#if !(TEST_SEED || FORCE_RECREATE)
      if (_ctx.Foods.Count() > 0)
      {
        return;
      }
#endif

#if TEST_SEED || FORCE_RECREATE
      ExecuteQueries(
        "DELETE FROM FoodDiaries.DiaryEntry",
        "DELETE FROM FoodDiaries.Diary",
        "DELETE FROM Nutrition.Measure",
        "DELETE FROM Nutrition.Food",
        "DELETE FROM [Security].[AuthToken]",
        "DELETE FROM [Security].[ApiUser]"
      );
#endif

      SeedApiUsers();
      SeedFoods();
      SeedDiaries();

    }

    void SeedApiUsers()
    {
      try
      {
        var user = new ApiUser()
        {
          Name = "My Cool App",
          AppId = "SSB3aWxsIG1ha2UgbXkgQVBJIHNlY3VyZQ==",
          Secret = "VGhpcyBDb3Vyc2UgSXMgQXdlc29tZQ=="
        };

        _ctx.ApiUsers.Add(user);

        var token = new AuthToken()
        {
          Token = "1234567890",
          Expiration = DateTime.Today.AddDays(365),
          ApiUser = user
        };

        _ctx.AuthTokens.Add(token);

        _ctx.SaveChanges();

      }
      catch (Exception ex)
      {
        throw ex;
      }
    }

    void SeedFoods()
    {

      try
      {
        // Load the excel sheet with data into the database
        var rawData = OpenExcel("~/App_Data/ABBREV.xls", "ABBREV22");

#if TEST_SEED
        var rows = rawData.Rows.Cast<DataRow>().Take(10);
#else
        var rows = rawData.Rows.Cast<DataRow>();
#endif

        foreach (var row in rows)
        {
          var measures = new List<Measure>();
          var food = new Food()
          {
            Description = ToTitleCase(GetData<string>(row, "Shrt_Desc")),
            Measures = measures
          };

          var measureDescription1 = GetData<string>(row, "GmWt_Desc1");
          var measureDescription2 = GetData<string>(row, "GmWt_Desc2");

          if (!string.IsNullOrWhiteSpace(measureDescription1))
          {
            var divisor = GetData<double>(row, "GmWt_1");
            var m = CreateRawMeasure(row, divisor, ToTitleCase(measureDescription1));
            measures.Add(m);
          }

          if (!string.IsNullOrWhiteSpace(measureDescription2))
          {
            var divisor = GetData<double>(row, "GmWt_2");
            var m = CreateRawMeasure(row, divisor, ToTitleCase(measureDescription2));
            measures.Add(m);
          }

          if (measures.Count == 0)
          {
            var m = CreateRawMeasure(row, 100.0, "100g");
            measures.Add(m);
          }

          _ctx.Foods.Add(food);
        }

        _ctx.SaveChanges();
      }
      catch (Exception ex)
      {
        // log
        throw ex;
      }
    }

    void SeedDiaries()
    {


      try
      {
        var diary = new Diary()
        {
          CurrentDate = DateTime.Today,
          UserName = "shawnwildermuth",
        };

        foreach (var food in _ctx.Foods.Take(1000).ToList().OrderBy(m => Guid.NewGuid()).Take(15))
        {
          var entry = new DiaryEntry()
          {
            Diary = diary,
            Quantity = 1.5,
          };

          entry.FoodItem = food;
          entry.Measure = food.Measures.First();

          diary.Entries.Add(entry);
        }

        _ctx.Diaries.Add(diary);

        _ctx.SaveChanges();
      }
      catch (Exception ex)
      {
        throw ex;
      }
    }



    void ExecuteQueries(params string[] sqlStatements)
    {
      foreach (var sql in sqlStatements)
      {
        _ctx.Database.ExecuteSqlCommand(sql);
      }

    }

    string ToTitleCase(string s)
    {
      var raw = CultureInfo.CurrentCulture.TextInfo.ToLower(s.Replace(",", ", "));
      return CultureInfo.CurrentCulture.TextInfo.ToTitleCase(raw);
    }

    Measure CreateRawMeasure(DataRow row, double divisor, string desc)
    {
      return new Measure()
      {
        Description = desc,
        Calories = Math.Round((GetData<double>(row, "Energ_Kcal") / 100) * divisor, 1),
        Carbohydrates = Math.Round((GetData<double>(row, "Carbohydrt") / 100) * divisor, 1),
        Cholestrol = Math.Round((GetData<double>(row, "Cholestrl") / 100) * divisor, 1),
        Fiber = Math.Round((GetData<double>(row, "Fiber_TD") / 100) * divisor, 1),
        Iron = Math.Round((GetData<double>(row, "Iron") / 100) * divisor, 1),
        Protein = Math.Round((GetData<double>(row, "Protein") / 100) * divisor, 1),
        SaturatedFat = Math.Round((GetData<double>(row, "FA_Sat") / 100) * divisor, 1),
        Sodium = Math.Round((GetData<double>(row, "Sodium") / 100) * divisor, 1),
        Sugar = Math.Round((GetData<double>(row, "Sugar_Tot") / 100) * divisor, 1),
        TotalFat = Math.Round((GetData<double>(row, "Lipid_Tot") / 100) * divisor, 1)
      };
    }

    T GetData<T>(DataRow row, string name)
    {
      var result = row[name];
      if (result == DBNull.Value) return default(T);
      return (T)result;
    }

    DataTable OpenExcel(string path, string sheet)
    {
      var filename = HostingEnvironment.MapPath(path);
      var cs = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", filename);
      OleDbConnection conn = new OleDbConnection(cs);
      conn.Open();

      OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM [{0}$]", sheet), conn);
      OleDbDataAdapter da = new OleDbDataAdapter();
      da.SelectCommand = cmd;

      System.Data.DataTable dt = new System.Data.DataTable();
      da.Fill(dt);
      conn.Close();

      return dt;

    }
  }
}

Diary.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CountingKs.Data.Entities
{
  public class Diary
  {
    public Diary()
    {
      Entries = new List<DiaryEntry>();
    }

    public int Id { get; set; }
    public DateTime CurrentDate { get; set; }
    public ICollection<DiaryEntry> Entries { get; set; }
    public string UserName { get; set; }
  }
}

DiaryEntry.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CountingKs.Data.Entities
{
  public class DiaryEntry
  {
    public int Id { get; set; }
    public Food FoodItem { get; set; }
    public Measure Measure { get; set; }
    public double Quantity { get; set; }

    public virtual Diary Diary { get; set; }

  }
}

Extensions.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Objects;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CountingKs.Data
{
  public static class Extensions
  {
    public static void AttachAsModified<T>(this DbSet<T> dbSet, T entity, DbContext ctx) where T : class
    {
      DbEntityEntry<T> entityEntry = ctx.Entry(entity);
      if (entityEntry.State == EntityState.Detached)
      {
        // attach the entity
        dbSet.Attach(entity);
      }

      // transition the entity to the modified state
      entityEntry.State = EntityState.Modified;
    }

  }
}

Food.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CountingKs.Data.Entities
{
  public class Food
  {
    public Food()
    {
      Measures = new List<Measure>();
    }

    public int Id { get; set; }
    public string Description { get; set; }

    public virtual ICollection<Measure> Measures { get; set; }
  }
}

ICountingKsRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using CountingKs.Data.Entities;
namespace CountingKs.Data
{
  public interface ICountingKsRepository
  {
    // General
    bool SaveAll();

    // Food
    IQueryable<Food> FindFoodsWithMeasures(string searchString);
    IQueryable<Food> GetAllFoods();
    IQueryable<Food> GetAllFoodsWithMeasures();
    Food GetFood(int id);
    Measure GetMeasure(int id);

    // Measure
    IQueryable<Measure> GetMeasuresForFood(int foodId);

    // Diary
    IQueryable<Diary> GetDiaries(string userName);
    Diary GetDiary(string userName, DateTime day);

    // DiaryEntry
    IQueryable<DiaryEntry> GetDiaryEntries(string userName, DateTime diaryDay);
    DiaryEntry GetDiaryEntry(string userName, DateTime diaryDay, int id);

    // Users
    IQueryable<ApiUser> GetApiUsers();

    // Tokens
    AuthToken GetAuthToken(string token);

    // Inserts
    bool Insert(DiaryEntry entry);
    bool Insert(Diary diary);
    bool Insert(AuthToken token);

    // Updates
    bool Update(DiaryEntry entry);
    bool Update(Diary diary);

    // Deletes
    bool DeleteDiaryEntry(int id);
    bool DeleteDiary(int id);
  }
}

Measure.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CountingKs.Data.Entities
{
  public class Measure
  {
    public int Id { get; set; }
    public string Description { get; set; }
    public double Calories { get; set; }
    public double TotalFat { get; set; }
    public double SaturatedFat { get; set; }
    public double Protein { get; set; }
    public double Carbohydrates { get; set; }
    public double Fiber { get; set; }
    public double Sugar { get; set; }
    public double Sodium { get; set; }
    public double Iron { get; set; }
    public double Cholestrol { get; set; }

    public virtual Food Food { get; set; }
  }
}