How to pass ADO.NET DataSet to a view

Question

I have a legacy project that we are slowly moving to MVC, but there are hundreds of ADO.NET SQL DataSet objects

I would like to keep the data sets in the model and access it in the view. Is this possible, or is there a better way to do this? Creating lists is difficult because there is too much data. We will eventually convert the SQL to entity, but not now.

Example:

Model:

 string query = "SELECT * FROM dbo.manf";
 SqlDataAdapter adapter = new SqlDataAdapter(query, conn);
 DataSet data = new DataSet();
 adapter.Fill(data, "manf");

View:

@foreach (DataRow data in manf.Tables["manf"].Rows)
{
    @:manf["id"] + " " + manf["name"]);
}  

Solution 1

You can pass any object you want to your View, so your controller code should look like this:

public ViewResult Index() 
{ 
    .... 
    return View(data); 
} 

And your View:

@model System.Data.DataSet

@foreach (DataRow row in Model.Tables["manf"].Rows)    
{    
    @(row["id"] + " " + row["name"])
}  

The passed object will be stored in the Model property of the generic base class of the view and you can access it with Model. The @model defines the data type of the object passed.

Any code will be HTML encoded anyway, the : is not needed. If you don't want to encode, you will have to use @Html.Raw(...).

EDIT: you can only move one parameter as a model. If you want to use more than one object, you either define a new datatype (a ViewModel) which containes properties for both. For simple objects such as a window title you can use the ViewBag.

ViewBag.Title = myTitle;

The ViewBag is a dynamic property of the controller and basically a wrapper over ViewData.

Solution 2

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

Comments

[tdjfdjdj]

perfect. Can I use more than 1 paramater for the view()? I am using a case statement that selects the view, so i will need to have: view("pagename",data) ?

[tdjfdjdj]

I cant seem to pass Data as a dataset. IS there something I am missing?

[slfan]

Is there an error message? You cannot use view("pagename", data). Regarding more than one parameter: I added more info to my answer, it is possible using ViewBag or a new dataType containing both properties.