asp.net-mvc 如何将数据集返回到视图

How to return a DataSet to a View

提问by bob.mazzo

I am sending a standard Sql select statement to my Sql box via the SqlDataAdapter, then populating a DataSet object.

我通过 SqlDataAdapter 向我的 Sql 框发送一个标准的 Sql select 语句，然后填充一个 DataSet 对象。

I can access the rows in the resulting DataSet, but how can I convert the DataSet into a List which can be returned to the MVC View. i.e. I'm assuming a List object is the best way to handle this.

我可以访问生成的 DataSet 中的行，但是如何将 DataSet 转换为可以返回到 MVC 视图的列表。即我假设 List 对象是处理此问题的最佳方式。

Here's my controller c# code:

这是我的控制器 C# 代码：

public class QAController : Controller
{

    private readonly static string connString = ConfigurationManager.ConnectionStrings["RegrDBConnection"].ToString();
    private readonly static SqlConnection sqlConn = new SqlConnection(connString);
    private readonly static SqlCommand sqlComm = new SqlCommand();

    public ActionResult Index()
    {
        DbRegressionExec();
        return View();
    }
    public static void DbRegressionExec()
    {
        // SELECT TABLE CONTENTS FROM SQL !!
        RegressDB_TableList regresDB = new RegressDB_TableList();
        string sqlStr = "select * from [RegressionResults].[dbo].[Diff_MasterList] order by TableName";

        // POPULATE DATASET OBJECT
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(sqlStr, sqlConn);
        da.SelectCommand.CommandType = CommandType.Text;
        sqlConn.Open();
        try
        {
            da.Fill(ds, "RegresDB");
        }
        catch (Exception e)
        {
            throw;
        }
        finally
        {
            sqlConn.Close();
        }

       // I can iterate thru rows here, but HOW DO CONVERT TO A LIST OBJECT ????

        int numRows = ds.Tables["RegresDB"].Rows.Count;
        for (int i = 0; i < numRows; i++)
        {
            string tblName = ds.Tables["RegresDB"].Rows[i].Field<string>("TableName");
        }

        //List<RegressDB_TableList> masterList = regresDB.RegresTableList.ToList(); //not working !!
        //var masterList = regresDB.TableName.ToList(); //

    }

}

and a simple class I may need to make this happen:

和一个简单的类，我可能需要实现这一点：

namespace RegressionMvc.Models
{
  public class RegresDB_TableName
  {
     public string TableName { get; set; }
  }
  public class RegressDB_TableList
  {
     public List<RegresDB_TableName> RegresTableList { get; set; }
  }

}

}

In the end, I'm trying to figure out the best way to handle DataSet results from Sql Server and how to make them back to an MVC View.

最后，我试图找出处理来自 Sql Server 的 DataSet 结果的最佳方法以及如何将它们返回到 MVC 视图。

I can probably go with jQuery and Json, meaning just convert the data fields to Json and return to JQuery, but I'm sure there are several ways to handle Sql based result sets.

我可能可以使用 jQuery 和 Json，这意味着只需将数据字段转换为 Json 并返回到 JQuery，但我确信有几种方法可以处理基于 Sql 的结果集。

Thanks in advance for your advice....

在此先感谢您的建议......

Best, Bob

最好的，鲍勃

采纳答案by Charlino

Short answer

简答

Directly answering your question:

直接回答你的问题：

var tableList = new List<RegresDB_TableName>();
int numRows = ds.Tables["RegresDB"].Rows.Count;
for (int i = 0; i < numRows; i++)
{
    string tblName = ds.Tables["RegresDB"].Rows[i].Field<string>("TableName");
    tableList.Add(new RegresDB_TableName() { TableName = tblName };
}

return View(tableList);

Long answer(that's actually shorter)

长答案（实际上更短）

Try out dapper-dot-net.

试试dapper-dot-net。

Your code could change to something like:

您的代码可能会更改为：

string sqlStr = "SELECT * FROM [RegressionResults].[dbo].[Diff_MasterList] ORDER BY TableName";
return sqlConn.Query<RegresDB_TableName>(sqlStr);

回答by Deepu T

In your controller put the code like this

在您的控制器中放置这样的代码

[HttpGet]
public ActionResult View(Modelclass viewmodel)
{
    List<Modelclass> employees = new List<Modelclass>();
    DataSet ds = viewmodel.GetAllAuthors();
    var empList = ds.Tables[0].AsEnumerable().Select(dataRow => new Modelclass{
       AuthorId = dataRow.Field<int>("AuthorId"),
        Fname = dataRow.Field<string>("FName"),
       Lname = dataRow.Field<string>("Lname")
    });
    var list = empList.ToList();



    return View(list);
}

And in view

并且鉴于

@{
var gd = new WebGrid(Model, canPage: true, rowsPerPage: 5, selectionFieldName: "selectedRow",ajaxUpdateContainerId: "gridContent");
    gd.Pager(WebGridPagerModes.NextPrevious);}
@gd.GetHtml(tableStyle: "table",

        columns: gd.Columns(
                 gd.Column("AuthorId", "AuthorId"),
                 gd.Column("Fname", " Fname"),
                 gd.Column("Lname", "Lname", style: "description")

 )) 

回答by Erik Philips

If you're stuck with using DAO, I would suggest not using a DataSet and instead use a strongly typed class with the speed of SqlDataReader.GetValues()method. It's more work, but it has to be done somewhere if you want strongly typed classes which I would highly recommend.

如果您坚持使用 DAO，我建议不要使用 DataSet，而是使用具有SqlDataReader.GetValues()方法速度的强类型类。这是更多的工作，但如果您想要我强烈推荐的强类型类，则必须在某处完成。

public class Person
{
  public Person(Object[] values]
  {
    this.FirstName = (string)values[0];
    this.LastName = (string)values[1];
    this.Birthday = (DateTime)values[2];
    this.HasFavoriteColor = (bool)values[3];
  }

  public string FirstName { get; private set; }
  public string LastName { get; private set; }
  public DateTime Birthday { get; private set; }
  public bool HasFavoriteColor { get; private set; }
}

public static void DbRegressionExec()
{
    List<Person> viewModel = new List<Person>();

    // SELECT TABLE CONTENTS FROM SQL !!
    RegressDB_TableList regresDB = new RegressDB_TableList();
    string sqlStr = "select
        FirstName
        ,LastName
        ,Birthday
        ,HasFavoriteColor
      from [RegressionResults].[dbo].[Diff_MasterList] 
      order by TableName";

    // POPULATE VIEWMODEL OBJECT
    sqlConn.Open();

    try
    {
      using (SqlCommand com = new SqlCommand(sqlStr, sqlConn))
      {
        using (SqlDbReader reader = com.ExecuteReader())
        {
          while(reader.Read())
          {
            viewModel.Add(new Person(com.GetValues()));
          }
        }
      }
    }
    catch (Exception e)
    {
        throw;
    }
    finally
    {
        sqlConn.Close();
    }

    return this.View(viewModel);
}

回答by wesley7

Controller code

控制器代码

 //pQ is your query you have created 
 //P4DAL is the key name for connection string 

  DataSet ds = pQ.Execute(System.Configuration.ConfigurationManager.ConnectionStrings["Platform4"].ConnectionString);


  //ds will be used below
  //create your own view model according to what you want in your view 
 //VMData is my view model

 var _buildList = new List<VMData>();
            {
                foreach (DataRow _row in ds.Tables[0].Rows)
                {
                    _buildList.Add(new VMData
                    {  
     //chose what you want from the dataset results and assign it your view model fields 

                        clientID = Convert.ToInt16(_row[1]),
                        ClientName = _row[3].ToString(),
                        clientPhone = _row[4].ToString(),
                        bcName = _row[8].ToString(),
                        cityName = _row[5].ToString(),
                        provName = _row[6].ToString(),
                    });



                }

            }

  //you will use this in your view
  ViewData["MyData"] = _buildList;

View

看法

@if (ViewData["MyData"] != null)
{

    var data = (List<VMData>)ViewData["MyData"];
    <div class="table-responsive">
        <table class="display table"  id="Results">

            <thead>
                <tr>
                    <td>Name</td>
                    <td>Telephone</td>
                    <td>Category </td>
                    <td>City </td>
                    <td>Province </td>

                </tr>
            </thead>

            <tbody>
                @foreach (var item in data)
                {
            <tr>
                <td>@Html.ActionLink(item.ClientName, "_Display", new { id = item.clientID }, new { target = "_blank" })</td>
                <td>@item.clientPhone</td>
                <td>@item.bcName</td>
                <td>@item.cityName</td>
                <td>@item.provName</td>

            </tr>

            }
            </tbody>


        </table>
        </div>
        }