Thursday, December 26, 2013

MVC 5 Entity Framework and Dealing with Multi-key tables

I have a legacy table with 100 columns and 3 key fields. The Create, Delete, Details, and Edit pages created by adding “MVC 5 Controller with views, using Entity Framework” can only handle single key tables out of the box.

The Database
The data I need is in a single table on Microsoft SQL Server 2008 R2. The table I’m using has 3 keys: [Order_], [Company_Name] and [Acct_]

In the server explorer, click “Add Connection” and follow the prompts to add the table.

Adding the Entity Data Model
Return to the Solution Explorer and right click on Models and [Add] -->[ADO.NET Entity Data Model].
(New Item --> Data --> ADO.NET Entity Data Model)

I called my model [Order]

Since the database already exists, choose [Generate from Database]

The name of the database is “temp” (not my choice and not something I can change), so leaving defaults on the data connection page means the entity connection settings in Web.Config will be “tempEntities”

As of 12/24/2013, Entity Framework 6.0 isn’t widely supported, so I chose 5.0

Choose the specific table(s) needed. In my case, I just need one: [ORDERS]
Again, leaving the default value, the Model Namespace is tempModel. Click [Finish]


The edmx diagram is created. At the top, you can see the 3 keys.

Build the Project
You need to build the project at this point before continuing or the controller build process will error out.

Adding the Controller
Right click on Controllers and add [Controller…] to add a controller scaffold.

Choose the “MVC 5 Controller with views, using Entity Framework”

I called my controller [OrderController]. Choose the Model class we just created [ORDER (WebApplication4.Models)] and the Data context class is the one we created [tempEntities (WebApplication4.Models)].

When this step completes, you’ll see the [OrderController.cs] under Controllers and an [Order] folder under Views that contains Create, Delete, Details, Edit and Index.cshtml.

Browsing for the First Time
The temp.ORDERS table has about 48,000 rows and over 100 columns (!). I didn’t need all of the columns, so I cut out all except the three key fields for this exercise.

@model IEnumerable<WebApplication4.Models.ORDER>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
       
        <th>
            @Html.DisplayNameFor(model => model.ORDER_)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.COMPANY_NAME)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.ACCT_)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.ORDER_)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.COMPANY_NAME)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.ACCT_)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { /* id=item.PrimaryKey */ }) |
            @Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
            @Html.ActionLink("Delete", "Delete", new { /* id=item.PrimaryKey */ })
        </td>
    </tr>
}

</table>

*Notice the commented “id=item.PrimaryKey” parameter.

Even with this reduced display, 48,000 rows still crashed my browser, so I added a counter that breaks out of the loop after the first 50 rows.

@{int counter = 0;}
@foreach (var item in Model) {
    counter = counter + 1;
    if (counter == 50) { break; }
                …

This results in output that looks like this:


Since the primary key is commented out of the edit, details and delete links, clicking any of them at this point will cause a 400 error.

The account I’m using does not have write access to the database, so “Details” is the only link I will activate. Uncommenting [Details] and replacing “PrimaryKey” with one of the real keys [ORDER_]will result in an exception:

            @Html.ActionLink("Details", "Details", new { id = @item.ORDER_}) |


“The number of primary key values passed must match number of primary key values defined on the entity.” The entity requires three key values and I am only passing in 1. A quick look at the tempController.cs shows how the [Details] action is handled:

The [id] value is passed in by querystring and is used by Find(id)to retrieve the proper record. It took a bit of searching to learn how to use Find() with multiple keys. A comma delimited list will work , for example: db.ORDERS.find(firstcolvar, “plain_text”, 3) looks for 3 keys.
#1 specified by the var firstcolvar,
#2 looks for the plain text “plain_text” and
#3 looks for the integer 3.

Note: The values must match the column data type.

The variable “id” is defined as a string by default, so I decide to pass all 3 key values in as a comma delimited string. I edited the Details Html.ActionLink code on Index.cshtml as follows:

@Html.ActionLink("Details", "Details", new {id= @item.ORDER_+','+@item.COMPANY_NAME+','+@item.ACCT_}) |

All 3 key values, separated by commas.

In the ActionResult Details inside OrderController.cs, I need to parse each of the 3 keys out. I use split to pull the values into an array of strings, then I use each of the values individually in the Find() method.

One tricky part: the third key is an integer, not a string, so I cast the value in the call to the Find method (highlighted).
            string[] splitid = id.Split(',');
            ORDER order = db.ORDERS.Find(splitid[0],splitid[1],Convert.ToInt32(splitid[2]));

Another tricky part: the order of the keys is important, and I have the order wrong above!

The class ORDER in the Models was created automatically from the SQL table.

The order of the keys presented to the find method must match the order of the keys in the Models/Order.edmx/Order.tt/ORDER.cs file.


Company_Name first, ACCT_ second, and ORDER_ third. The ActionLink in Index.cshtml becomes:

@Html.ActionLink("Details", "Details", new { id = @item.ORDER_ + ',' + @item.COMPANY_NAME + ',' + @item.ACCT_ }) |

And the ActionResult in OrderController becomes:

            string[] splitid = id.Split(',');
            ORDER order = db.ORDERS.Find(splitid[1], int.Parse(splitid[2]),splitid[0]);

At this point the details link works!


The next goal is to figure out how to do this with a complex query (in LINQ?) against 2 databases, one of which does not have a key.


Friday, December 6, 2013

Learning One ASP.NET MVC 5

MVC 5 Tutorials
MVC 5 Books
MVC 4 Books

What I like most about these books is they both take a real world scenario and build it out completely, explaining details in a way that video tutorials can't. It is not easy for a novice to translate the MVC 4 examples to MVC 5, but if you build for MVC 4 in Visual Studio 2013, you can follow along with the MVC 4 books almost exactly.

2/10/2014 Removed most of my editorial blather.
4/14/2014 Wrox publish date bumped out yet again.
8/6/2014 Wrox finally released their book. I have changed language stacks to ColdFusion/Java, so this is the last time I will update this page or my ASP.NET content.

Friday, November 8, 2013

"Hello World!" Using One ASP.NET MVC 5 From the Visual Studio 2013 Empty Template

I am in the process of learning MVC 5 and Visual Studio 2013. I am not a stranger to MVC concepts, but I am new to ASP.Net. I thought this might be helpful for new ASP.Net students who have already played with the pre-built MVC 5 template and want to start their own app from scratch.

Update 2/10/2014 
To avoid confusion, I changed the title of this post. This is not how to build a complete sample MVC 5 site from the ground up; it is just a tutorial on getting the empty template running. MVC5 books are out now that will explain site construction in extreme detail.

Create an ASP.Net 4.5 Empty MVC 5 Project
Create a new project/solution.

Choosing the MVC template will load a complete web application shell including controller, views…etc. We want an empty MVC shell with no pre-built pages. Choose Empty (C#) and check MVC.

If you try to run or debug the app immediately after creation, you’ll get an error: “Server Error in ‘/’ Application. The resource cannot be found.” The template is missing the default controller and view needed to display properly.

In the solution explorer, twist open Views and App_Start. Notice that pages are completely absent from Controllers, Models and the root. The Views folder contains a web.config file thinly stocked with Razor definitions.


App_Start contains the RouteConfig.cs file which is where you’ll find the default controller and action definition. According to line 19, the app will look for a controller named “Home” to start.

Create the Default Controller “HomeController”
Right click on Controllers and go to [Add]-->[New Scaffold Item…]

Choose “MVC 5 Controller – Empty” and click [Add]

Change the name of the controller to “HomeController” so it agrees with the default in the RouteConfig.cs file. 

Open the HomeController.cs file and notice the default code returns the view for the Index action.

Create the Default View “Index”
Now we need to create the index.cshtml view that the controller is looking for. Right-click on the Home folder under "Views" in Solution Explorer. Choose [Add] à MVC 5 View page (Razor).


Change the name of the view item to “Index” to match the ActionResult in the Controller.

Open Index.cshtml and insert “Hello World!” between the div tags on lines 13/14.

Click Save, then [Ctrl]+[Shift]+W to view your page in a browser.

Success!










Friday, November 1, 2013

Understanding Visual Studio 2013 and TFS - Projects vs Team Projects

As a developer, I left the Microsoft stack back in 2000 and am very excited to return to the fold with a cutting-edge, large scale One ASP.Net / MVC 5 app. As I am reorienting myself to the Visual Studio 2013 environment, I am learning some things that other new users might find useful.

Visual Studio Solutions and Projects
According to Microsoft, a Project in Visual Studio is "used in a solution to logically manage, build, and debug the items that make up your application... A solution includes one or more projects, plus files and metadata that help define the solution as a whole. Visual Studio automatically generates a solution when you create a new project. " [1

A project groups files for an app and a solution groups projects.


Team Foundation Service (TFS) and Team Foundation Server (also TFS)
TFS is Microsoft's answer to version control, but it is also a tool for project planning and build automation. MSDN Blogger Steve Lange has a great post on the differences between Team Foundation Server and Team Foundation Service that includes a handy PowerPoint brief summarizing the service features. In a nutshell, the service is a cloud based version of the locally hosted server. Microsoft provides the Team Foundation Service free for the first 5 users, so it is well worth checking out.

TFS Team Project
Visual Studio Project and a Team Project are completely different groupings. Reuse of the name is very confusing and, in fact, is what motivated me to create this blog post.  An Article on TFS in MSDN Magazine dated April 2011 describes a team project as a “container for artifacts, including source code (organized into folders, branched folders and branches) and containing one or more Visual Studio solutions, Team Build configuration files, Team Load Test Agents, an optional SharePoint repository containing the pertinent documents for the project…” In other words, a team project is a source control container for one or more Visual Studio solutions. [2]


A TFS team project groups Visual Studio solutions. In this diagram, a new "Web Application" as created in Visual Studio would be a project that is part of a larger solution, which is checked into a team project repository.

TFS Collection
A TFS Collection is a group of Team Projects. The default collection created when you first register with TFS is called DefaultCollection… Unfortunately, with the Team Foundation Service provided with Visual Studio only one collection is allowed per account. [3] If you are running a Team Foundation Server, it is possible to create multiple collections.

A collection groups team projects. (Multiple team projects as pictured above are not possible in the Team Foundation Service provided with Visual Studio.)

Workspaces
What is a workspace and how does it fit into this picture? “Your workspace is a local copy of your team’s codebase.” [4] It is where you actually work on the code.

Local Workspaces vs Server Workspaces
When tying your workspace to your Team Project, you have the option to work locally, which copies all files from the repository, or you can work in server mode, which only copies files one at a time.  (The MSDN article titled “Decide between using a local or a server workspace” has a great explanation of why this would be desirable.)

Team Project Settings: Speed Server Workspaces with Asynchronous Check-in
“You can reduce the time the system takes to check out files to server workspaces by selecting Enable asynchronous checkout in server workspaces. If you select this option: The PendChange permission is no longer enforced [and] Checkout locks are disabled.” [5]






Wednesday, August 28, 2013

Percussion CM System (Rhythmyx) and SQL Server Express on Amazon EC2


imageI am in the process of installing Percussion Rhythmyx 7.0.3 on an EC2 instance in Amazon's AWS cloud. This is a running log of the process that should serve as a how-to for even the most novice Percussion admin.


Amazon AWS EC2 AMI
I created a medium instance using the AMI with Windows Server 2008 R2 Datacenter Ed, IIS and SQL Server Express pre-installed. (Anything smaller than medium will be very slow.)
Windows_Server-2008-R2_SP1-English-64Bit-SQL_2008_R2_SP2_Express-2013.07.10 (ami-86c6baef)
I reserved 40gb storage for the primary drive. (This may have been shortsighted.)

Configure the Amazon Firewall
These are my firewall settings:
image

Port 80 is open to the world. The rest are locked down to the IP address of my machine at home.
Ports 20-22 are for FTP/sFTP.
Ports 1024-2048 are the fixed range for passive FTP connect mode.
Port 1433 is SQL Server
Port 3389 is Remote Desktop
and 9992-9998 is for CM System.
The read-me notes say that the 6 sequential ports after the main CM port are reserved.
image


Create the Database
To install CM System to SQL Server Express, you have to first create an empty database for the installer to use.
The SQL Server Management Studio is installed with dbo rights for the administrator, so just login with windows authentication.
image

Right click on [Databases] in the tree and choose [New Database...]. I called my database "rxMaster".
image

Since this is a test instance, I like to restrict the log file growth so it doesn't grow out of control...
image

and I set auto shrink to true under options.
image

Click [OK] to create.

Create the Percussion User and Set Permissions
Since this is for test use, these permissions are going to be wide open. Please lock everything down for production use.

Open [Security] in the Object Explorer tree and right click on [Logins]. Choose [New Login...]
image

Under the General tab, I used the login name "Percussion". Choose "SQL Server Authentication" and enter a password. Uncheck "Enforce password policy" to keep the password from expiring. Finally, choose "rxMaster" as the default database.
image

Under User Mapping, check "rxMaster" under "Users mapped to this login:" and in the default column, type "dbo". Choose "db_owner" for the "Database role membership for: rxMaster".
image

Click [Ok] to create the user

Enable SQL Server Authentication
I have heard that mixed-mode authentication with SQL Server poses security problems, but I was unable to get CM to install using a local Windows account.

Right click on the server (the topmost node in the object explorer) and choose [Properties]. Click the radio button beside "SQL Server and Windows Authentication mode"
image



Windows Firewall on the Windows Server
The Windows Firewall on this AMI should already be configured to allow SQL Server to operate... but it doesn't hurt to double check that it is allowed. (I am not sure whether this affects local app to app communications.)
image

In order to reach your installation from outside the server, create an inbound rule to allow TCP ports 9992-9998. To create this rule, double click [New Rule] on the upper right hand corner of the "Windows Firewall with Advanced Security"
image

SQL Server Configuration Manager
These steps are very important. If these options are not set, you will get the dreaded generic "Failed to establish connection to the database" error.

Under [SQL Native Client 10.0 Configuration] --> [Client Protocols], ensure that TCP/IP is Enabled. (It is by default in this AMI)
image

Under [SQL Server Network Configuration] --> [Protocols for MSSQLSERVER] ensure that TCP/IP is Enabled. (It is by default in this AMI.)
image

Double click TCP/IP above and ensure that [Listen All] is "Yes". (Again, it is by default in this AMI)

INSTALL PERCUSSION CM SYSTEM 7.0.3

Follow the Percussion Installation Guide until you get to the Repository setup.
note: I chose not to install FastForward.
For SQL Server Express, this is how your entry should look:
image
If you receive the "Failed to establish connection to the database" error when clicking [Next], double check all of the preceding instructions.

Choose rxMaster as the Database
image

Accept the remaining defaults. Continue past the port warning.
When the installation is complete, click [Done]
image

I received no error messages, so this is the end of the install. Consult the Installation Guide p.52 to see what warnings and errors look like.

Starting CM System

CM System creates an auto-start windows service to run the CM server, but the server does not start automatically after install.The Rhythmyx Administration Manual covers the start-up process.

In Windows Services, find the entry "Percussion Rhythmyx Server". Right click and [Start]
image

A message will pop up saying a program is trying to display a message:
image

Click [View the message] if you want to see the console window while CM system starts up.
If everything runs properly, you should see the JBoss started message at the bottom:
image

Logging Into CM System 
On the rhythmyx server, you should be able to reach the login prompt here:
http://127.0.0.1:9992/Rhythmyx/
image
Use the default username and password credentials: admin1/demo
The password demo can be used for all of the default accounts.

Note: The usernames and passwords are stored as plain text in the database. For testing this is fine, but for production, use LDAP or some other authentication method.

Install Java
After logging in for the first time, you will be prompted to download and install Java 1.5.0_12. I tend to stick with this version unless other development efforts require a more recent version. As I have upgraded through the years - especially early versions of 1.6.0_xx, I had strange security problems and other compatibility problems crop up. Of course, being out of date, this version is vulnerable to exploits...
image
Note: If you run into bizarre problems, ensure that you do not have multiple versions of Java installed.

Installation Complete!
Once Java is installed, you are ready to start building your sites
image

Questions or comments are welcome!

[Edit 8/29/2013 to include server-side Windows Firewall configuration step]
[Edit 11/12/2013 I had an interesting side conversation with S.A. that I thought might be useful for others. An edit of the conversation is provided below.]

Hi Brain. I like to thank you for your blog post on installing Percussion CM on Amazon cloud. I have a couple of questions. Do I need a licensed copy of Percussion or is there a trial available? Also do you know if there is an Amazon ec2 instance that comes with Percussion already installed?
S.A. • Oct 22, 8:25 AM

I am not aware of an EC2 instance with Percussion pre-installed. And no... [as far as I know] They don't offer a demo version of CM System... [When I contacted them, I was told that I had to buy a $5000 developer license for CM system.]
Brien Oct 23, 2:46 AM

Hey thanks for the message. I did download CM1, and tried to install it on an AMI. I am stuck on an error message about Rhythmyx not being available. You don't think I am running into a license issue do you? The install wizard did not prompt me for a license. I think I am little confused about the products they offer. There is CMS, CM1, Rhythmyx. You seem to know quite a bit about them. What is the difference between these products?
S.A. • Oct 23, 5:08 AM

Their product lines are indeed confusing. Once upon a time, they only produced one content management system called Rhythmyx. A few years ago they changed the name of Rhythmyx to CM system. They simultaneously released a new product line called CM1, which is a light version of CM system intended for smaller businesses.

Make sure that Windows firewall is configured to allow all of the ports that I mentioned in my blog. I believe CM1 uses MySQL by default. Make sure that it is running and reachable. [That is the only thing I can think of to check. I made this post while re-tooling my skill set for a project bid that fell through, so I'm afraid I have stopped my re-discovery of Percussion for now.] Percussion has a very active forum full of helpful people. If you have not already, join that forum and post your questions there.
Brien Oct 23, 12:06 PM