Add SQL Azure database to Azure Web Role and persist data with entity framework code first.
- by MagnusKarlsson
In my last post I went for a warts n all approach to set up a web role on Azure. In this post I’ll describe how to add an SQL Azure database to the project. This will be described with an as minimal as possible amount of code and screen dumps. All questions are welcome in the comments area. Please don’t email since questions answered in the comments field is made available to other visitors. As an example we will add a comments section to the site we used in the previous post (Länk här). Steps: 1. Create a Comments entity and then use Scaffolding to set up controller and view, and add ConnectionString to web.config. 2. Create SQL Azure database in Management Portal and link the new database 3. Test it online! 1. Right click Models folder, choose add, choose “class…” . Name the Class Comment. 1.1 Replace the Code in the class with the following: using System.Data.Entity; namespace MvcWebRole1.Models { public class Comment { public int CommentId { get; set; } public string Name { get; set; } public string Content { get; set; } } public class CommentsDb : DbContext { public DbSet<Comment> CommentEntries { get; set; } } } Now Entity Framework can create a database and a table named Comment. Build your project to assert there are no build errors. 1.2 Right click Controllers folder, choose add, choose “class…” . Name the Class CommentController and fill out the values as in the example below. 1.3 Click Add. Visual Studio now creates default View for CRUD operations and a Controller adhering to these and opens them. 1.3 Open Web.config and add the following connectionstring in <connectionStrings> node. <add name="CommentsDb” connectionString="data source=(LocalDB)\v11.0;Integrated Security=SSPI;AttachDbFileName=|DataDirectory|\CommentsDb.mdf;Initial Catalog=CommentsDb;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" /> 1.4 Save All and press F5 to start the application. 1.5 Go to http://127.0.0.1:81/Comments which will redirect you through CommentsController to the Index View which looks like this: Click Create new. In the Create-view, add name and content and press Create. 1: //
2: // POST: /Comments/Create
3:
4: [HttpPost]
5: public ActionResult Create(Comment comment)
6: {
7: if (ModelState.IsValid)
8: {
9: db.CommentEntries.Add(comment);
10: db.SaveChanges();
11: return RedirectToAction("Index");
12: }
13:
14: return View(comment);
15: }
16:
The default View() is Index so that is the View you will come to. Looking like this:
1: //
2: // GET: /Comments/
3:
4: public ActionResult Index()
5: {
6: return View(db.CommentEntries.ToList());
7: }
Resulting in the following screen dump(success!):
2. Now, go to the Management portal and Create a new db.
2.1 With the new database created. Click the DB icon in the left most menu. Then click the newly created database. Click DASHBOARD in the top menu. Finally click Connections strings in the right menu to get the connection string we need to add in our web.debug.config file.
2.2 Now, take a copy of the connection String earlier added to the web.config and paste in web.debug.conifg in the connectionstrings node.
Replace everything within “ “ in the copied connectionstring with that you got from SQL Azure. You will have something like this:
2.3 Rebuild the application, right click the cloud project and choose “Package…” (if you haven’t set up publishing profile which we will do in our next blog post).
Remember to choose the right config file, use debug for staging and release for production so your databases won’t collide.
You should see something like this:
2.4 Go to Management Portal and click the Web Services menu, choose your service and click update in the bottom menu.
2.5 Link the newly created database to your application. Click the LINKED RESOURCES in the top menu and then click “Link” in the bottom menu. You should get something like this.
3. Alright then. Under the Dashboard you can find the link to your application. Click it to open it in a browser and then go to ~/Comments to try it out just the way we did locally.
Success and end of this story!
