Loading data in to a DataTable from a SQL Server database and displaying Charts in MVC3

Question

I'm terribly new to MVC and programming in general, and I'm having trouble loading data from a SQL Server database into my MVC3 application. I couldn't get it to work with a model, so I only have a controller and a view. The code looks like this:

Controller:

public ActionResult Datatable()
{
    string conn = System.Configuration.ConfigurationManager.ConnectionStrings["SampleDBConn"].ToString();
    SqlConnection connect = new SqlConnection(conn);
    connect.Open();

    SqlCommand RID = new SqlCommand("SELECT Request_ID FROM SampleDB.dbo.Transactions", connect);
    SqlCommand Tdate = new SqlCommand("SELECT Trans_Date FROM SampleDB.dbo.Transactions", connect);
    SqlCommand MID = new SqlCommand("SELECT Merchant_ID FROM SampleDB.dbo.Transactions", connect);
    SqlCommand Ttype = new SqlCommand("SELECT Trans_Type FROM SampleDB.dbo.Transactions", connect);
    SqlCommand Tamt = new SqlCommand("SELECT Total_Amt FROM SampleDB.dbo.Transactions", connect);

    DataTable dt = new DataTable("MyTable");
    dt.Columns.Add(new DataColumn("Request_ID", typeof(SqlCommand)));
    dt.Columns.Add(new DataColumn("Trans_Date", typeof(SqlCommand)));
    dt.Columns.Add(new DataColumn("Merchant_ID", typeof(SqlCommand)));
    dt.Columns.Add(new DataColumn("Trans_Type", typeof(SqlCommand)));
    dt.Columns.Add(new DataColumn("Total_Amt", typeof(SqlCommand)));

        DataRow row = dt.NewRow();
        row["Request_ID"] = RID;
        row["Trans_Date"] = Tdate;
        row["Merchant_ID"] = MID;
        row["Trans_Type"] = Ttype;
        row["Total_Amt"] = Tamt;
        dt.Rows.Add(row);

    connect.Close();
    return View(dt);
}

View:

    @model System.Data.DataTable

    @{
        ViewBag.Title = "Datatable";
        Layout = "~/Views/Shared/_Layout.cshtml";
    }

   < h2 > Datatable < /h2 >

   < div id="header" >< /div >

   < br / >

   < style type"text/css" >

         #DT1{display:inline}

   < /style >

   < table border="1" id="DT1" style="background-color: Lime" >

        < thead style="background-color:Olive" >
            < tr >

             @foreach (System.Data.DataColumn col in Model.Columns)
             {
                    < th >@col.Caption< /th >
             }
            </tr>

        < /thead >

        < tbody >

           @foreach (System.Data.DataRow row in Model.Rows)
           {
                <tr>
                    @foreach (var cell in row.ItemArray)
                    {
                       <td>@cell.ToString()</td>
                    }
               </tr>
             }
       </tbody>
    </table>

The resulting table displays each column like this:

Request_Id      
System.Data.SqlClient.SqlCommand

Where "Request_Id" is the Column header and "System.Data.SqlClient.SqlCommand" is where the data should be

I used this method from another stackoverflow solution: Displaying standard DataTables in MVC

The end goal of my application is to dynamically query my database via checkboxes or drop-down lists to display charts, and I'll also need to be able to export the data to an excel file. If anyone has any thoughts or suggestions on how I should approach this, it would be greatly appreciated.

I've been able to used a stored procedure to statically create a chart via this method:

    @{
        ViewBag.Title = "Chart1"; 
     }

    < h2 >Chart1< /h2 >
    @{
          var db = Database.Open("SampleDBConn");
          var dbdata = db.Query("August2012byMerch");

          var myChart = new Chart(width: 1100, height: 600, theme: ChartTheme.Green)
          .AddTitle("Merchant Totals by Transaction Type: August 2012")
          .AddSeries("Default",
           xValue: dbdata, xField: "Transaction Type",
           yValues: dbdata, yFields: "Total")
           .Write();
           db.Close();
       }

Thank you!

In my opinion I think that you should try to understand the framework from 0. — Jorge, Jan 08 '13 at 19:46
@Jorge I get the framework. Build a model that handles the data, then use the controller as an orchestrator to pass the model to the view. Then the view displays the data. I've just had troubles building the model for this so I'm trying a hands on approach to learn what can and can't be done — gbills, Jan 08 '13 at 20:09
Typically your model is a simple POCO. Instead of trying to use a DataTable as your model I would add a data layer (Look at Repository and Unit of Work design patterns) that returns POCO's that you would use in your model. EF works great for this. — Kevin Junghans, Jan 08 '13 at 20:47

score 0 · Accepted Answer · answered Jan 16 '13 at 15:39

I've found a solution that worked for me as far as the DataTable goes.

From the advice from Kevin in the comments section, I used a POCO model with an Entity Framework Code-First design. Also research lead me to a great tutorial here -> http://www.asp.net/mvc/tutorials/mvc-music-store/mvc-music-store-part-4

I based my model off this tutorial, then modified it a little to meet my needs.

Here is the model:

    namespace Example_1.Models
    {
        public class Transaction
        {
            [Key]
            public int TID { get; set; }
            public DateTime TDate { get; set; }   
            public decimal TAmt { get; set; }
            public string Trans_Type { get; set; }
            public int MID { get; set; }
        }
    }

    namespace Example_1.Models
    {
         public class Merchants
         {
            [Key]
            public int MID { get; set; }
            public string Merchant { get; set; }
            public List<Transaction> Transactions { get; set; }
         }
    }
    namespace Example_1.Models
    {
        public class SampleDBEntities : DbContext
        {
            public DbSet<Transaction> Transactions { get; set; }
            public DbSet<Merchants> Merchants { get; set; }
        }
    }

The SampleDBEntities class must match the connection string in the web.config file. While the Transactions class and Merchants class must match the variables set up in my database.

Here is the controller:

     public ActionResult Browse(string Merchant)
    {
        var mercmodel = sampledb.Merchants.Include("Transactions")
            .SingleOrDefault(g => g.Merchant == Merchant);
        return View(mercmodel);
    }

The Merchant variable must match the path taken in your URL. For example my URL for this is "http://localhost:64269/Transaction/Browse?Merchant=ISCO_CA"

Then in the view, I constructed a table where I passed my data into:

    <script src="@Url.Content("~/Scripts/sorttable.js")" type="text/javascript">   </script>


   @model Example_1.Models.Merchants

    @{
        ViewBag.Title = "Browse";
    }


    <h2>Browse</h2>

      <h3>Transactions: @Model.Merchant</h3>
     <table class="sortable">
     <tr>
     <th>ID</th>
     <th>Date</th>
     <th>Merchant</th>
     <th>Amount</th>
     </tr>
        @foreach (var transaction in Model.Transactions)
         {
            <tr>
              <td>
                @transaction.TID
             </td>
             <td>
               @transaction.TDate
             </td>
             <td>
               @transaction.Trans_Type
             </td>
             <td>
               $@transaction.TAmt
            </td>
          </tr>
        }
     </table>

This displays a beautiful table that looks like this:

MyTable

Loading data in to a DataTable from a SQL Server database and displaying Charts in MVC3

1 Answers1