Working with data

Topics: General
Sep 26, 2011 at 4:13 AM

I created a site using orchard and have all the standard components functioning well and am trying to figure out where to go from here. I am looking to add a page that displays a table of data, consisting of player statistics for a variety of sports (example of fields: player name, goals, assists, points, etc..). Currently I have all of the data stored in a separate MySQL database on the server (not the database orchard created automatically; i believe i used the sqlce database when setting the site up originally). Is it possible to access this data and display it on a page? If not, what is the best way to go about this? Is Orchard even capable of this? 

Thanks,

Mark

Coordinator
Sep 26, 2011 at 4:17 AM

Sure. Just write a controller action and a service class to access your data. You'll have to isolate your data access code from the ambient transaction with a using (new TransactionScope(TransactionScopeOption.Suppress)) block. Make your action build a shape result with your data on there, and start happily writing templates for it.

Sep 26, 2011 at 4:20 AM

Thanks for the quick response. I'm fairly new to programming, and it's my first time with orchard so I'll probably take a while to decipher that, but I appreciate the help. 

Oct 1, 2011 at 11:58 PM

Can anyone point me towards a tutorial or a module that functions similar to this?

Oct 2, 2011 at 3:42 PM

I was able to figure this out for the most part using bertrand's suggestions and piecing together info on a few other sources. Here is some of my code:

HomeController.cs (I took out some unnecessary code):

 

using System.Web.Mvc;
using Orchard.Themes;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Web;
using NFL.Models;
using System.Data.SqlClient;
using System.Transactions;
using System.Configuration;
using MySql.Data.MySqlClient;

namespace NFL.Controllers
{
    [Themed]
    public class HomeController : Controller
    {

        public ActionResult Index()
        {
            var playerlist = new List<playerstats>();

            string connectionString = ConfigurationManager.ConnectionStrings["StatsDb"].ConnectionString;

            var connection = new MySqlConnection(connectionString);
            MySqlCommand command = connection.CreateCommand();
            MySqlDataReader Reader;
            command.CommandText = "select * from nfl";

            using (new TransactionScope(TransactionScopeOption.Suppress))
            {
                                connection.Open();
                            }

            // the connection may now be used normally and closed at any time

            Reader = command.ExecuteReader();

            while (Reader.Read())
            {
                playerlist.Add(new playerstats
                {
                    PlayerName = (string)Reader["PlayerName"],
                    Position = (string)Reader["Position"],
                    TeamName = (string)Reader["TeamName"],
                });
            }

            connection.Close();

            return View(playerlist);
        }
    }
}

 

 

View/Home/Index.cshtml:

@model IEnumerable<NFL.Models.playerstats>
@{   
    int currentrow = 0;
   string classtype = "a";
   }
@{ Layout.Title = T("NFL Player Projections").ToString();
WorkContext.Layout.SubPageTitle.Add(New.SubPageTitleShape(Subtitle: "NFL Player Projections"), "10");}
<p>This page shows current projections for NFL players for the 2011-2012 season.</p>
<table>
<thead><tr><th>Player</th><th>Projected Points</th></tr></thead>
    @foreach (var player in Model)
    {
        if (currentrow % 2 == 0)
        { 
            classtype = "a";
        } else
        {
            classtype = "b"; 
        };
        <tr class=@classtype>
            <td>@player.PlayerName, @player.Position @player.TeamName</td><td>@(Math.Round(player.Projected))</td></tr>
        currentrow++;
    }
</table>
<p style="padding-top:10px">Showing @Model.Count() players...</p>

Only thing to do now would be to clean up my view by having the data table be created as a shape. I'm still unsure of how to do this. Probably simple. but I haven't found any solid examples of this yet. To clarify, I would like to have a shape for the data table, and be able to add that shape into the view with the data from the sql command. Any help would be appreciated. Thanks

Coordinator
Oct 2, 2011 at 10:14 PM

You can create shapes on the fly from your templates. For example, replace the rendering of the tr with @Display.Player_Row(Player: player, RowNumber: currentRow) and add a Player.Row.cshtml template in the views forlder of your theme with something like this:

        if ((int)Model.RowNumber % 2 == 0)
        { 
            classtype = "a";
        } else
        {
            classtype = "b"; 
        };
        
            <td> </td>@Model.Player.PlayerName, @Model.Player.Position @Model.Player.TeamName<td> </td>@(Math.Round(Model.Player.Projected))