Step 1: Create a Database and Show the Poll Options

Article
09/12/2011

Applies to: Functional Programming

Authors: Tomas Petricek and Jon Skeet

Referenced Image

Get this book in Print, PDF, ePub and Kindle at manning.com. Use code “MSDN37b” to save 37%.

Summary: This step creates a database to store the poll options. It shows how to retrieve the data by calling a stored procedure from F# and how to display it using the Razor view engine.

This topic contains the following sections.

Creating a Database
Loading the Data Using F#
Creating the User Interface
Summary
Additional Resources
See Also

This article is associated with Real World Functional Programming: With Examples in F# and C# by Tomas Petricek with Jon Skeet from Manning Publications (ISBN 9781933988924, copyright Manning Publications 2009, all rights reserved). No part of these chapters may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

Creating a Database

A vast majority of web applications store data in databases. This step begins by explaining how to create a relational database for an ASP.NET MVC project. The tutorial uses Microsoft SQL Server 2008 Express, which is freely available and is included in Visual Studio's typical installation.

The tutorial keeps the database in a file that is located in the same directory as the web application. This is very convenient for application development because the data is kept together with the application. When deploying the application, the file can be copied to a database server and attached as a named database.

Adding a Local Database

As mentioned in the tutorial overview, the starting point for the web application is an ASP.NET MVC 3 template (discussed in Tutorial: Creating a Web Project in F# Using an Online Template). The following steps describe how to add a database file to the web project.

Adding a database to an ASP.NET web project

Right-click the FSharpWeb.Web project and select Add, Add ASP.NET Folder, and select App_Data. This creates a folder that is used for storing application data, including database files.
Right-click the App_Data folder and add a new database by selecting SQL Server Database in the Add New Item dialog. The file can be called, for example, PollData.mdf.
Double-clicking on the database file should open a Server Explorer window in Visual Studio, which can be used to create the database structure. The window is shown in Figure 1.

Figure 1. Newly created database for online poll application

Referenced Image

After the database is created, the next step is to design the database structure. The poll application uses a single table, which is created in the next section.

Designing a Data Table

An application for creating online polls would allow users to create their own polls, so it would need two tables. One table would store polls (with the name of the poll and perhaps the name of the creator), and the other would store the options of the poll (with the text and the number of votes). This tutorial is simple and it creates a single poll.

The poll allows the users to choose between several alternatives that are stored in a single table. For each alternative, the table contains a unique ID, a title that will be displayed, and a number of votes. The table can be created using the following steps.

Creating a database table for storing polls

Right-click on Tables and select Add New Table.
In the editor that appears, add a column ID of type int, a Title of type varchar(100) and Votes of type int. The varchar type represents a Unicode string with the maximum length of 100.
Set the ID column as the primary key (right-click and select Set Primary Key) and specify that its value is generated automatically (in Column Properties, select Identity Specification and change the (Is Identity) value to Yes).
Press Ctrl+S to save the table, and then enter its name; for example, PollOptions.
Add some poll options to test the web application. This can be done in an editor that appears when you right-click on the created table and select Show Table Data.

The application accesses the database data using stored procedures. The following section implements a procedure to obtain a list of alternatives for the poll.

Implementing a Stored Procedure

Stored procedures enable you to hide the database structure and provide a simple way of accessing the database from an application. To get the list of all poll options, web developers don't have to understand the database structure. They can just use the GetOptions procedure, which returns the results set in the required format.

The procedure is quite simple, but it does more than just return the data. In addition to columns ID, Title, and Votes, it also returns a Percentage column, which is calculated by the following procedure:

CREATE PROCEDURE dbo.GetOptions
AS
    DECLARE @Total int
    SET @Total = (SELECT SUM(Votes) FROM [PollOptions])
    SELECT 
        [ID], [Title], [Votes], 
        ROUND(CAST([Votes] AS float) / @Total * 100, 0) AS [Percentage]
    FROM [PollOptions]

The listing shows the script that creates the procedure. After it is executed (and the database contains the procedure), it is possible to modify the procedure by changing CREATE PROCEDURE to ALTER PROCEDURE.

The body of the procedure first declares a @Total variable and assigns it the sum of all votes. The rest of the snippet is a SELECT statement that returns all of the rows from the PollOptions table. It returns the three columns of the table and a Percentage value, which is calculated based on the number in the Votes column and the sum stored in @Total. Note that the expression converts Votes to float to make sure that the calculation is done using floating-point numbers. The final result is rounded so that the result looks neat when displayed on the web.

Adding the stored procedure was the last step of creating the database. The remaining two sections discuss how to call it from F# and how to create a web page showing the result.

Loading the Data Using F#

The F# code that connects to the database and loads the data forms the model component of the MVC application. It can be added to the Model.fs file of the FSharpWeb.Core project. The code uses ADO.NET so it requires adding a reference to System.Data.dll and System.Transactions.dll. (The code doesn't use transactions, but the assembly is required by F# IntelliSense.)

The tutorial uses the DynamicDatabase helper object, which is discussed in How to: Dynamically Invoke a Stored Procedure. The helper makes it possible to call the stored procedures using the (?) operator. The operator's implementation isn't explained in this tutorial but can be found in How to: Dynamically Invoke a Stored Procedureor in the complete source code below.

The following listing first declares a PollOption record, which represents one poll choice. The record corresponds to the result set constructed by the GetOptions stored procedure. Then, it creates a LoadPoll function, which reads the data from the database into a sequence of PollOption values:

namespace FSharpWeb.Core
open System.Data
open System.Data.SqlClient

type PollOption =
  { ID : int
    Votes : int
    Percentage : float
    Title : string }
          
module Model =
    let connectionString = 
       @"Data Source=.\SQLEXPRESS;AttachDbFilename=" +
       @"""|DataDirectory|PollData.mdf"";Integrated Security=True;" +
       @"User Instance=True"
  
    let LoadPoll() = 
      [ let db = new DynamicDatabase(connectionString)
        for row in db.Query?GetOptions() do
            yield { ID = row?ID; Votes = row?Votes;
                    Title = row?Title; Percentage = row?Percentage } ]

The declaration of the PollOption record is important because it defines the types of individual columns that are read from the database. Types are not explicitly mentioned anywhere else in the snippet, so F#'s type inference uses the types from the declaration when converting the data obtained from the database to the .NET types.

The module implementing the model first declares a connection string. The connection string would typically be loaded from the web.config file, but this tutorial keeps the implementation simple. The connection string specifies that the database is a PollDataa.mdf file located in the App_Data directory. (|DataDirectory| is a special variable.) When connecting to the database, ADO.NET should use the .\SQLEXPRESS instance running on the local machine, which is the default name used in SQL Server Express installations.

The most interesting part of the listing is the LoadPoll function. It is implemented as a list comprehension that generates values of the PollOption type. The function first creates a DynamicDatabase helper discussed in How to: Dynamically Invoke a Stored Procedure. The helper provides a query member that can be used to call the stored procedures that return a collection of results.

Using the helper, a GetOptions procedure can be called just by writing db.Query?GetOptions(). The (?) operator gets the name of the procedure as a string and it performs the call, returning a sequence of rows. The properties of individual rows are also not known at compile time so they are also accessed dynamically (for example, row?Votes). The dynamic operator for the row object automatically converts the result to the expected type. In the snippet above, the type is inferred from the declaration of PollOptions.

Equipped with a database and a model that loads all of the data, it is now easy to add a user interface to show the data in a web application.

Creating the User Interface

In this step of the tutorial, the web application contains a single page to display the poll. (The next step adds another action for voting.) The page needs to have a corresponding action in the controller. The controller is implemented in the Main.fs file of the FSharpWeb.Core project and looks like this:

type MainController() =
  inherit Controller()
  member x.Index() =
    x.ViewData.Model <- Model.LoadPoll()
    x.View()

The controller only connects the functionality provided by the model to the view, so it is extremely simple. The Index method (representing an action) calls the LoadPoll function and stores the result in the ViewData.Model property. The type of the data is a collection of PollOption values.

The view implemented in the following listing formats the data using the HTML markup. The code is implemented using the Razor view engine, so the embedded code snippets are written in C#:

@{ View.Title = "Online Poll Application"; }
@model IEnumerable<FSharpWeb.Core.PollOption>

<h2>What is your favorite programming language?</h2>
<div class="poll"><ul>
@foreach (var option in Model)
{
    <li>
        <h3><a href="/Vote/@option.ID">@option.Title</a></h3>
        <div class="box">
        <div class="vote" style="width:@(3 * option.Percentage)px;">
            &#160;
        </div>
        @option.Percentage % (@option.Votes)
        </div>
    </li>
}
</ul></div>

The line starting with @model specifies the type of data passed from the controller to the view. The view receives a collection of PollOption values, which is the F# record loaded from the database. The view generates some static HTML and then iterates over the alternative poll options. For each option, it generates a <li> element with a header, a box visualizing the percentage, and detailed statistics.

The header is a link that can be used to vote for the option. It takes the user to an action with a URL such as /Vote/3 where 3 is the ID of the current option. This action will be implemented in the next step of the tutorial. The width of the box below the header is calculated as three times the percentage of votes for the option. The generated attribute could be, for example, style="width:30px".

An attractive poll can be created by adding a couple of CSS style definitions that add color to the box showing the results and align the statistics appropriately. The complete source code of the application (available below) creates a design that is shown in the screenshot in the introductory article of the tutorial.

Summary

This step of the tutorial demonstrated how to create a simple web application that displays data stored in a database. It discussed how to add a database to a mixed F#/C# web application. The database contained a single table with data and a stored procedure that returns the data, together with a calculated percentage.

The second part of the article discussed using the database from F#. This was done using an F# helper that makes it possible to call stored procedures using the dynamic operator. The GetOptions procedure was called just by writing db.Query?GetOptions().

The next step of the tutorial completes the application and implements voting for options. It also demonstrates how to call stored procedures that take parameters but do not return results.

Additional Resources

This tutorial demonstrates how to create a web-based poll. The best way to read the tutorial is to continue to the next step, which implements voting:

The structure of the web application was created using an online template, which is discussed in a separate tutorial. It also used a helper for calling stored procedures using the dynamic operator. The following articles contain more information about these topics:

The online poll application doesn't perform a large number of I/O operations when handling a request, so it can be implemented synchronously. However, many applications call other services and should be implemented without blocking threads. This is discussed in the following tutorial:

Tutorial: Creating a News Aggregator

To download the code snippets shown in this article, go to https://code.msdn.microsoft.com/Chapter-5-Bulding-Data-ec639934