How do I get a data table from a select query done to Oracle and display it in my view in MVC?

Question

I want to be able to return a table in my oracle db and send it to be displayed in my view in my MVC structure. My code looks something like this for my model:

        public DataTable Show(int Poll_ID)
    {

        OleDbDataReader myOleDBDataReader = DBConn("SELECT * FROM MCQ_QUESTIONS WHERE Poll_ID = 1");

        DataSet dataSet = new DataSet();

        DataTable schemaTable = myOleDBDataReader.GetSchemaTable();
        DataTable dataTable = new DataTable();

        for (int cntr = 0; cntr < schemaTable.Rows.Count; ++cntr)
        {
            DataRow dataRow = schemaTable.Rows[cntr];
            string columnName = dataRow["ColumnName"].ToString();
            DataColumn column = new DataColumn(columnName, dataRow.GetType());
            dataTable.Columns.Add(column);
        }
        myOleDBDataReader.Close();
        myOleDbConnection.Close();
        return dataTable;

But its not working. Any help is appreciated guys!! Code from my Controller class is as follows:

public ActionResult Details(int id)
    {
        PollModel poll = new PollModel();
        DataTable dt = new DataTable();
        dt = poll.Show(1);
        //ViewData["Poll"] = poll.Show();
        ViewData["Data"] = dt;
        //ViewData["Poll"] = "Testing";
        return View(dt);
    }

Code in my view is as follows :

    <%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage" %>
<%@ Import Namespace = "MvcApplication3.Models" %>
<%@ Import Namespace = "System.Data" %>

<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">
    Details
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<%= Html.Encode(ViewData["Data"])%>

</asp:Content>

Any reason for using `DataSet` and `DataTable` instead of strongly typed models? — Darin Dimitrov, May 24 '11 at 06:27
Im new to c# and MVC for that matter and googling recommended using both of those types to reference a select query done on a table — Hriskesh Ashokan, May 24 '11 at 06:27
Sorry, I couldn't find the code that add row to dataTable. you just create the table's schema without data. Is it right? — Peyman, May 24 '11 at 06:32

score 2 · Answer 1 · answered May 24 '11 at 06:42

The first thing I would recommend you is to use the ODP.NET driver to access an Oracle database instead of ODBC. The second thing I would recommend you is to use models instead of DataSet and DataTables which were used in classic WebForms (even if they shouldn't have) but not in ASP.NET MVC.

So once you have downloaded the proper driver from Oracle let's get to coding. Start by describing what a Question is. For example:

public class Question
{
    public int Id { get; set; }
    public string Title { get; set; }
}

then write a repository method which will return a list of questions:

public IEnumerable<Question> GetQuestions()
{
    using (var conn = new OracleConnection("put your CS string here or fetch from app.config"))
    using (var cmd = conn.CreateCommand())
    {
        conn.Open();
        cmd.CommandText = "SELECT MCQ_ID, MCQ_TITLE FROM MCQ_QUESTIONS WHERE Poll_ID = 1";
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                yield return new Question
                {
                    Id = reader.GetInt32(reader.GetOrdinal("MCQ_ID")),
                    Title = reader.GetString(reader.GetOrdinal("MCQ_TITLE"))
                };
            }
        }
    }
}

Now we can move on to the controller:

public ActionResult Index()
{
    var questions = repository.GetQuestions().ToArray();
    return View(questions);
}

And finally the corresponding strongly typed view:

<%@ Page Title="" 
         Language="C#" 
         MasterPageFile="~/Views/Shared/Site.Master" 
         Inherits="System.Web.Mvc.ViewPage<IEnumerable<MvcApplication3.Models.Question>>" 
%>
<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">
    Details
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
    <table>
        <thead>
            <tr>
                <th>Id</th>
                <th>Title</th>
            </tr>
        </thead>
        <tbody>
            <%= Html.DisplayForModel() %>
        </tbody>
    </table>
</asp:Content>

And finally in the corresponding display template (~/Views/Shared/DisplayTemplates/Question.ascx):

<%@ Control 
    Language="C#" 
    Inherits="System.Web.Mvc.ViewUserControl<MvcApplication3.Models.Question>" 
%>
<tr>
    <td><%= Html.DisplayFor(x => x.Id) %></td>
    <td><%= Html.DisplayFor(x => x.Title) %></td>
</tr>

You may also checkout the tutorials here about using Entity Framework as an ORM to avoid writing SQL queries in your code.

public IEnumerable GetQuestions() doesn't seem to work despite importing System.Collections, error I'm getting is as follows: "The non-generic type 'System.Collections.IEnumerable' cannot be used with type arguments". Help! — Hriskesh Ashokan, May 24 '11 at 06:57
This works great if/when a query returns one row... what would you suggest for a query that returns >1 row? Thanks! — Cole, Apr 30 '13 at 15:43

score 0 · Answer 2 · edited May 23 '17 at 12:31

0

try looking here:

Displaying standard DataTables in MVC

this can be interesting:

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

edited May 23 '17 at 12:31

Community

1
1

answered May 24 '11 at 06:29

danyolgiax

12,798
10
65
116

I'll try whats stated in that post. – Hriskesh Ashokan May 24 '11 at 06:31
I've to return in my prev question... are you sure datatable contains data? Have you debugged it for rows property? – danyolgiax May 24 '11 at 06:41

How do I get a data table from a select query done to Oracle and display it in my view in MVC?

2 Answers2