Using GoDaddy SQL Server w/ ASP.NET MVC5 & Code First

Share httpJunkie.com on..
Tweet about this on TwitterShare on FacebookShare on Google+Share on TumblrEmail this to someone

So even though I am a huge proponent of using Foundation over Bootstrap. This tutorial will be focused on the basic MVC5 APplication which utilizes Bootstrap. Once you are done with this tutorial I have several other tutorials specifically on removing Bootstrap & adding Foundation. They are both great Frameworks. In fact I have had some client work dealing with working solely on Bootstrap for the past month and I’m quite happy about how much of my deep knowledge of Foundation transfers right over to Bootstrap. The Framework is not the point of this tutorial, the point is to show how to configure GoDaddy SQL Server with MVC5, Identity & Code First by creating a real world (but simple) application for listing a companies national offices.

What you need at minimum for this project:

GoDaddy Hosting with access to at least 1 SQL Server instance.

Visual Studio Express 2013 for Web:
http://www.visualstudio.com/en-us/products/visual-studio-express-vs.aspx

If you only have VS 2012 See my tutorial here on setting up MVC5 & Identity in Visual Studio 2012:
http://httpjunkie.com/2013/158/install-zurbs-foundation-5-in-net-mvc-5/

Microsoft SQL Server Management Studio 2012:
http://www.microsoft.com/en-us/download/details.aspx?id=29062

1. Ceate a MVC 5 project Choose to use Individual Membership using OneASP.Net
2. Create a SQL Server DB at GoDaddy by logging into your Hosting Account > Add SQL Server DB
3. Create a Friendly Name, Database Name & Password then hit OK. I used..

Friendly Name: myLocations
Database name and username: myLocations
Password: a321Password!

 The DB will take a while to setup
in the meantime it will say Pending Setup for about ten minutes.

4. Once pending setup is done, you will be left on the Manage Databases page select the actions dropdown to the right of your database and select Details.

5. Copy the following Information to keep on file for later usage:

Hostname:
myLocations.db.9xx6x6x.hostedresource.com
Admin URL:
https://p3nmssqladmin.secureserver.net/2012/6

6. Get a connection String for our database by clicking Code Sample in the Details Tab.

SqlConnection (.NET):
Data Source=myLocations.db.9116567.hostedresource.com; Initial Catalog=myLocations; User ID=myLocations;     Password=’your password’;

7. Once this is done close out of GoDaddy for now. The rest of our work will be done locally. So let’s take our connection string and go plug it in as the default connection string in our project. I’m going to open up my project for the first time and run it oonce just to make sure it’s working. I get the typical Bootstrap template so I’m good!

8. Open your web.config file in the root directory and replace the line:

<add name="DefaultConnection" connectionString="Data Source=
(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-
MyNewProject-20140713052856.mdf;Initial Catalog=aspnet-
MyNewProject-20140713052856;Integrated Security=True"
providerName="System.Data.SqlClient" />

with

<add name="DefaultConnection" connectionString="Data Source=
myLocations.db.9116567.hostedresource.com;
Initial Catalog=myLocations; User ID=myLocations;
Password='your password';"
providerName="System.Data.SqlClient" />

9. Now before we run our ASP.Net application and register our first user (which will add the Identity Tables to our database) I want to open up Microsoft SQL Server Management Studio 2012 and click on Connect to Object Explorer or Connect to Database.

(*use your info not mine)

For Server Name use: myLocations.db.9116567.hostedresource.com
Login: myLocations
Password: a321Password!

10. CONNECT, now open (considering your credentials worked) open Databases Tree
Now I’m going to look for myLocations database. The databases you see here are all the other databases hosted on this server. Other than the ability to see their name, you cannot access them and they cannot access yours. The only database you will be able to manipulate is your database. But it is a bit of a pain sorting through to find yours. Next time we’ll name it starting with an “A” lol. Once you have found yours don’t do anything. just leave it alone for a few minutes. You will get an error until we have put some tables in it.

11. Let’s go into our application and hit Ctrl + F5 run our website and click on the register user option in the navigation. Make sure you remember your username and password you setup. I’m using:

U: me@mydomain.com
P: a321Password!

Once you register it will redirect back to the home page and you wil be logged in with your new email. This means that as long as we saved our webconfig file before attempting this ASP.Net just accessed our SQL Server Database and setup the tables required in order to maintain users for our site. But it also establishes a connection with our SQL Server so that we can start to use Code First to create our own project. This concludes the section about setting up a SQL Server Database using GoDaddy and ASP.Net. If you would like to continue we will be creating a set of manageable office locations for our website using Code First and scaffodling out our MVC Controls & Views. We will also include a makeshift Image Upload and use some custom routing techniques as well as some Bootstrap formatting. See you on the next line of text.

Model Creation and Enable Migrations

Let’s start by creating an Images folder in the main directory. I hate the fact that they don’t have an images folder there, so I always create one right off the bat.  There is a bg.jpg below that you can download and use for the demo if you want or you can create and image that is  I’m going to show you a quick way to add a background to the jumbotron background. Just to spice up the page a little bit. This is in no way professional grade CSS, but the standard front page is hideous and I’d rather look at something with some type of style.

bg.zip

12. Put the following CSS in your Site.css file inside the ~/Content folder, just add it to the bottom.

~/Content/Site.css

.jumboBg {
 background: url('/images/bg.jpg') no-repeat center center;
}

.jumboTitle {
 background: linear-gradient(left, white, rgba(0,0,0, 0.0));
 background: -webkit-linear-gradient(left, white, rgba(0,0,0, 0.0)); /* For Safari 5.1 to 6.0 */
 background: -o-linear-gradient(left, white, rgba(0,0,0, 0.0)); /* For Opera 11.1 to 12.0 */
 background: -moz-linear-gradient(left, white, rgba(0,0,0, 0.0)); /* For Firefox 3.6 to 15 */
 padding: 3px;
 color: #111;
 text-shadow: 2px 2px #ccc;
}

.jumboText {
 background: linear-gradient(left, #bcd3ff, rgba(0,0,0, 0.0));
 background: -webkit-linear-gradient(left, #bcd3ff, rgba(0,0,0, 0.0)); /* For Safari 5.1 to 6.0 */
 background: -o-linear-gradient(left, #bcd3ff, rgba(0,0,0, 0.0)); /* For Opera 11.1 to 12.0 */
 background: -moz-linear-gradient(left, #bcd3ff, rgba(0,0,0, 0.0)); /* For Firefox 3.6 to 15 */
 padding: 3px;
 color: #111;
 text-shadow: 1px 1px #fff;
}

13. Replace Index.cshtml with the following code:

~/Views/Home/Index.cshtml

@{
 ViewBag.Title = "Office Locations";
}

<div class="jumbotron jumboBg">
   <h1 class="jumboTitle">Office Locations</h1>
   <p class="lead jumboText">ASP.Net MVC5 Demo ~ Responsive office locations project</p>
   <p><a href="http://asp.net" class="btn btn-primary btn-lg">Learn more &raquo;</a></p>
</div>

<div class="row">
   <div class="col-md-4">
   <h2>Our New Business</h2>
  <p>
   This new business has clients all over America, therefore we need to have office locations in just about every darn near state, city and town.
   </p>
   <p><a class="btn btn-default" href="http://go.microsoft.com/fwlink/?LinkId=301865">Learn more &raquo;</a></p>
 </div>
 <div class="col-md-4">
   <h2>Get more us</h2>
   <p>Us is good, that's why we sell it and our locations give you us in a building with lights and walls, sometimes computers...</p>
     <p><a class="btn btn-default" href="http://go.microsoft.com/fwlink/?LinkId=301866">Learn more &raquo;</a></p>
 </div>
 <div class="col-md-4">
     <h2>What we do</h2>
     <p>You can easily find what we do by visiting where were at. Like a wise man once said it aint where you from it's where you's at..</p>
     <p><a class="btn btn-default" href="http://go.microsoft.com/fwlink/?LinkId=301867">Learn more &raquo;</a></p>
 </div>
</div>

Save the files and run the page, you should see the difference on the home page. Let’s move on.

14. Right click on the Models folder and select Add > Class and name it location.cs

15. make sure the using includes look like this:

using System;

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;

then make sure the location class looks like this:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;

namespace myLocations.Models
{
  public class location
  {
    //This Id must be named like: 'classNameId'
    public int locationId { get; set; }
    public string name { get; set; }
    [StringLength(10), Display(Name = "Address Number")]
    public string addressNum { get; set; }
    [StringLength(100), Column(TypeName = "varchar"), Display(Name = "Street")]
    public string addressStreet { get; set; }
    [StringLength(10), Display(Name = "Suite Number")]
    public string suiteNum { get; set; }
    [StringLength(20), Column(TypeName = "varchar")]
    public string city { get; set; }
    [StringLength(2), Column(TypeName = "varchar")]
    public string state { get; set; }
    [StringLength(10), Column(TypeName = "varchar")]
    public string zip { get; set; }
    [StringLength(16), Column(TypeName = "varchar"), Display(Name = "Phone Number")]
    public string phone { get; set; }
    [StringLength(16), Column(TypeName = "varchar"), Display(Name = "Fax Number")]
    public string fax { get; set; }
    [Required(ErrorMessage = "The email address is required")]
    [EmailAddress(ErrorMessage = "Invalid Email Address")]
    [StringLength(50), Column(TypeName = "varchar")]
    public string email { get; set; }
    [DataType(DataType.MultilineText)]
    [StringLength(255), Column(TypeName = "varchar"), Display(Name = "Counties Served")]
    public string countiesServed { get; set; }
    public bool hasLicense { get; set; }
    [StringLength(20), Column(TypeName = "varchar"), Display(Name = "License Number")]
    public string licenseNum { get; set; }
    [DataType(DataType.MultilineText)]
    [Column(TypeName = "varchar(Max)"), Display(Name = "Location Description")]
    public string description { get; set; }
    [DataType(DataType.MultilineText)]
    [Column(TypeName = "varchar(Max)"), Display(Name = "Why Us")]
    public string whyUs { get; set; }



    //Upload Map Image to /images/maps/ named: (addressNum + "_" + zipdoce + ".jpg")
    public string MapImage
    {
        get { return addressNum.Replace(" ", string.Empty) + "_" + zip.Replace(" ", string.Empty) + ".jpg"; }
    }
  }
}

16. Save the location.cs file then right click on the root of your project and click Build. This will ensure that the model class is available for us to create a controller that uses scaffolding. If we miss this Build step we will get an error when we scaffold out the controller & view because it won’t know about the model class yet.

After you have finished with the build, right click on the controllers directory and Add > Controller > MVC5 Controller with views, using Entity Framework and choose location from the Model class dropdown menu, for Data context class choose ApplicationDbContext, we will select the option to Use async controllers and leave all 3 checkboxes checked and the layout page blank (but checked). Name it the controller locationController

Note: If you have problems adding a controller, it may be because you did not Build your project after adding your Product model. If this is the case, close out of your controller dialog box, Build the project and try again.

At this point you have a working controller, a folder for views called locations and inside that folder the pages needed for CRUD operations (Create, Delete, Details, Edit & Index.cshtml). But before we use them , we need to talk to our database and make sure that we have persisted the model to the database, so we have no tables for the crud to work with yet.

17. In the upper right hand corner of Visual Studio there is a search field that says: Quick Launch (Ctrl+Q) click there and type console + Enter. In the console type the following:

PM> Enable-Migrations -EnableAutomaticMigrations

*Note: You could also pull up the Package manager console by either navigating to:
Tools > Library Package Manager > Package Manager Console

If you entered it correctly you will receive the following in your command line:

Checking if the context targets an existing database…
Code First Migrations enabled for project myLocations..

The enable-migrations command creates a Migrations folder, and it puts in that folder a Configuration.cs file that you can edit to configure Migrations. It has a Configuration class that lets you configure how Migrations behaves for your context. We are using the default configuration. Lets create our first migration to the database. The database is our meccas and we have yet to make a trip.

There are two command you must know.

Add-Migration will create a file which contains the instructions for the next migration based on changes you have made to your model or are applying for the first time.

Update-Database will then run that instruction and apply any pending changes to the database. This can be rolled back. Take a look at the file before you do an update-database and just see who it works so you know. There is an up and a down. Apply & rollback.

For info on rollbacks, see this Stack-O question: Rollbacks

We are not using Add-Migration (right now), Code First Migrations automatically calculates and applies the changes when we use Update-Database. THis will instruct Code First Migrations to push the changes to our model up to the database (Mecca).

Run the ‘Update-Database’ command in Package Manager Console.

PM> Update-Database -Verbose

the verbose flag we flew was to let the console know w want to watch the tables get added and get a verbose feedback of what our command is doing. You should be left with the full detail in the console once it’s done. Look through it. Understand what it did.

Let’s go look at our database in SQL Server Management Studio. If it’s not still open go to File > Connect Object Explorer.

*Use your information

Server Name: myLocations.db.9xx6x6x.hostedresource.com
Login: myLocations
Password: a321Password!

Search for your database ‘myLocations’, expand it and then expand Tables. YOu should now see not only your User tables, but also your locations table. DO not use Management Studio to edit your database unless you know exactly what yo are doing with code migrations and code first. You will screw up your project. For now, let’s do all updating of the database through Entity Framework & Code First Migrations.

Right click on the table locations and choose Edit Top 200 Rows. Don’t edit anything, just let it set there for now.

Go to your ~Views/Shared/_Layout.cshtml page and replace all with the following code

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>@ViewBag.Title - Office Locations</title>
    @Styles.Render("~/Content/css")
    @Scripts.Render("~/bundles/modernizr")
</head>
<body>
    <div class="navbar navbar-inverse navbar-fixed-top">
        <div class="container">
            <div class="navbar-header">
                <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                </button>
                @Html.ActionLink("myLocator", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
            </div>
            <div class="navbar-collapse collapse">
                <ul class="nav navbar-nav">
                    <li>@Html.ActionLink("Home", "Index", "Home")</li>
                    <li>@Html.ActionLink("About", "About", "Home")</li>
                    <li>@Html.ActionLink("Contact", "Contact", "Home")</li>
                    <li>@Html.ActionLink("Locations", "Index", "Locations")</li>
                </ul>
                @Html.Partial("_LoginPartial")
            </div>
        </div>
    </div>
    <div class="container body-content">
        @RenderBody()
        <hr />
        <footer>
            <p>&copy; It's @DateTime.Now.Year, do you know where are locations are?</p>
        </footer>
    </div>
    @Scripts.Render("~/bundles/jquery")
    @Scripts.Render("~/bundles/bootstrap")
    @RenderSection("scripts", required: false)
</body>
</html>
</body>
</html>

Hit Ctrl + F5 to run the site locally. In that edit we just performed we added a link to the navigation inside the layout page called locations. Click on it to get to the Index page of your Locations CRUD operations.

We are going to fix these pages one by one. We will transform the index page into our actual Locations page for our front end of our website. We will transform the details page into a specific location page. And the Edit, Insert and Delete pages will be linked to from our frontend pages BUT ONLY when someone is logged in with the proper admin role associated with their username.

First lets start by replacing all the CRUD pages they gave you. I have went through and made them a little more responsive and useful. As you change each page. Pay attention to the changes and what they do.

MORE TO COME SOON, I PROMISE, VERY TIRED….





 

 

Leave a Reply

You must be logged in to post a comment.