如何从对 Oracle 执行的选择查询中获取数据表并将其显示在 MVC 的视图中？

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

提问by Hriskesh Ashokan

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:

我希望能够在我的 oracle 数据库中返回一个表，并将它发送到我的 MVC 结构中的视图中。对于我的模型，我的代码看起来像这样：

        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:

但它不工作。任何帮助是赞赏的家伙！我的 Controller 类中的代码如下：

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>

回答by Darin Dimitrov

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

我建议您的第一件事是使用ODP.NET 驱动程序来访问 Oracle 数据库而不是 ODBC。我建议你的第二件事是使用模型，而不是DataSet和DataTables它是在传统的使用WebForms（即使他们不应该有），但不是在ASP.NET MVC。

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

因此，一旦您从 Oracle 下载了正确的驱动程序，让我们开始编码。首先描述什么是 a Question。例如：

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):

最后在相应的显示模板 ( ~/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 hereabout using Entity Framework as an ORM to avoid writing SQL queries in your code.

您还可以在此处查看有关使用实体框架作为 ORM 以避免在代码中编写 SQL 查询的教程。

回答by danyolgiax

try looking here:

试试看这里：

Displaying standard DataTables in MVC

在 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>
<%} %>