I was following this tutorial (.NET Web Application with SQL Azure) on Windows Azure website and realised that the tutorial were written using Entity Framework Code First approach. If you are like me - prefer Entity Framework Database First approach, below is the Entity Framework Database First version.
In this post, I will skip the details of the first few steps (setup Azure development environment, create MVC3 app, and Azure enable the app) and go straight to the Database part. You may refer to the article for the step by step guide and come back later for the Database implementation using Entity Framework Database First approach.
Azure + MVC3 + Entity Framework Database First Approach
1. Install Windows Azure SDK and setup development environment on Visual Studio.
2. Create ASP.NET MVC3 application.
3. Azure enable your ASP.NET MVC3 application.
4. Add new Database to our app. Right click on App_Data folder and select Add > New Item. Choose SQL Server Database and name it ToDoList.mdf.
5. Double click on the created database (ToDoList) to add ToDoItem table to the database. Right click on Tables > Add New Table.
6. Edit database table to include these fields – Id, Name, IsComplete. Make sure you set the Id field as primary key and set the Identity Specification. Hit Save button and enter “ToDoItem” as the database table name.
7. Add data model. Right click on “Model” in the solution explorer and then Add New Item > ADO.NET Entity Data Model > name it “ToDoListModel.edmx”. Choose "Generate from Database” option. Select “ToDoList.mdf” connection string. Click “Next”.
8. Choose to include “ToDoItem” database object.
9. Create Repository Class for ToDoItem to refactor all the data access logic on ToDoItem table. You may refer to this article on why Repository Pattern is good for data access logic implementation. Basically, Repository Pattern refactor all the data access logic to one class so it’s easier to maintain and test. Right click on Models > Add > Class > ToDoItemRepository.cs. Below is the complete implementation of my ToDoItemRepository class.
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
namespace ToDoListApp.Models
{ public class ToDoItemRepository
{ private readonly ToDoListEntities _entities = new ToDoListEntities();
//
// Query Methods
public ToDoItem GetTDI(int id)
{ return (from tdi in _entities.ToDoItems
where tdi.Id == id
select tdi).FirstOrDefault();
}
public IQueryable<ToDoItem> GetAllToDoItems()
{ return from tdi in _entities.ToDoItems
select tdi;
}
//
// Update/Add/Delete Methods
public void Update(ToDoItem tdi)
{ _entities.ToDoItems.Attach(tdi);
_entities.ObjectStateManager.ChangeObjectState(tdi, EntityState.Modified);
}
public void Add(ToDoItem tdi)
{ _entities.ToDoItems.AddObject(tdi);
}
public void Delete(ToDoItem tdi)
{ _entities.ToDoItems.DeleteObject(tdi);
}
//
// Save
public void Save()
{ _entities.SaveChanges();
}
// Dispose
public void Dispose()
{ _entities.Dispose();
}
}
}
10. Recreate the HomeController to show all the ToDoItems. Right click on Controllers > Add > Controller > HomeController.cs.
11. Modify the generated HomeController.cs to use Repository class.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using ToDoListApp.Models;
namespace ToDoListApp.Controllers
{ public class HomeController : Controller
{ private readonly ToDoItemRepository _tdiRepository = new ToDoItemRepository();
//
// GET: /Home/
public ViewResult Index()
{ var tdis = _tdiRepository.GetAllToDoItems();
return View(tdis.ToList());
}
//
// GET: /Home/Details/5
public ViewResult Details(int id)
{ var todoitem = _tdiRepository.GetTDI(id);
return View(todoitem);
}
//
// GET: /Home/Create
public ActionResult Create()
{ return View();
}
//
// POST: /Home/Create
[HttpPost]
public ActionResult Create(ToDoItem todoitem)
{ if (ModelState.IsValid)
{ _tdiRepository.Add(todoitem);
_tdiRepository.Save();
return RedirectToAction("Index"); }
return View(todoitem);
}
//
// GET: /Home/Edit/5
public ActionResult Edit(int id)
{ var todoitem = _tdiRepository.GetTDI(id);
return View(todoitem);
}
//
// POST: /Home/Edit/5
[HttpPost]
public ActionResult Edit(ToDoItem todoitem)
{ if (ModelState.IsValid)
{ _tdiRepository.Update(todoitem);
_tdiRepository.Save();
return RedirectToAction("Index"); }
return View(todoitem);
}
//
// GET: /Home/Delete/5
public ActionResult Delete(int id)
{ var todoitem = _tdiRepository.GetTDI(id);
return View(todoitem);
}
//
// POST: /Home/Delete/5
[HttpPost, ActionName("Delete")] public ActionResult DeleteConfirmed(int id)
{ var todoitem = _tdiRepository.GetTDI(id);
_tdiRepository.Delete(todoitem);
_tdiRepository.Save();
return RedirectToAction("Index"); }
protected override void Dispose(bool disposing)
{ _tdiRepository.Dispose();
base.Dispose(disposing);
}
}
}
12. Login to https://windows.azure.com/. Add SQL Database on Azure. Database > Subscription > Create > Choose Server Region > Key in username and password >
You can find my previous blog here –