How to populate a model without entity framework in Asp.net MVC 5

Question

Note:- This question will arise in the mind of developer who don't have an idea about entity framework and have directly started learning asp.net MVC 5. Once you are on the road to learn a new thing it is difficult to grasp several new concept. This question will help new developer getting started with MVC straight away using their existing knowledge of Ado.net.

I have been trying to use asp.net MVC 5 with my existing database. I am not going to use Entity framework for populating the model. How can I populate my model without using any dbcontext. How can i fetch data from database and put inside model? Here is my Controller Action:-

    public ActionResult Index()
            {
                /* WHAT TO DO HERE TO LOAD THE TRANSACTIONS? This part is resolved in following lines*/
/* I have used following code to get it using the direct db connection */
var transactions = new List<Portal.Models.TransactionModels>();
            var connectionString = "server=yourserver;database=yourdatabase;uid=youruid;pwd=yourpwd";
            MySqlConnection oMySqlConnection = new MySqlConnection(connectionString);
            oMySqlConnection.Open();
            MySqlCommand oMySqlCommand = new MySqlCommand("Select * from yourtable;", oMySqlConnection);
            MySqlDataReader oMySqlDataReader = oMySqlCommand.ExecuteReader();
            while (oMySqlDataReader.Read())
            {
                transactions.Add(new Portal.Models.TransactionModels
                {
                    transaction_id = oMySqlDataReader["transaction_id"].ToString()
                });
            }
            oMySqlConnection.Close();
            return View(transactions);

                return View();
            }

Here is my Model:-

  public class TransactionModels
    {
        public virtual string id{ get; set;}
        public virtual int statusid { get; set; }
    }

Here is my View:-

@model IEnumerable<Portal.Models.TransactionModels>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.id)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.statusid)
        </th>       
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.id)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.statusid)
        </td>

        <td>
            @Html.ActionLink("Edit", "Edit", new { /* id=item.PrimaryKey */ }) |
            @Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
            @Html.ActionLink("Delete", "Delete", new { /* id=item.PrimaryKey */ })
        </td>
    </tr>
}

</table>

Answer 1

you're almost there. your controller would look like this:

public ActionResult Index()
{
  List<BillingValidation> list = new List<BillingValidation>();
  try
  {
    // runs stored procedure and returns data to main page
    using (SqlConnection con = new SqlConnection())
    {
      String sql = @"yourquery";
      con.ConnectionString = @"yourconnection"

      DataTable dt = new DataTable();
      SqlDataAdapter da = new SqlDataAdapter();
      da.SelectCommand = new SqlCommand(sql, con);

      da.Fill(dt);

      foreach (DataRow row in dt.Rows)
      {
        var bilVal = new BillingValidation();
        bilVal.Total = row["Total"].ToString();
        bilVal.Section = row["Section"].ToString();
        bilVal.Details = row["Details"].ToString();
        list.Add(bilVal);
      }
    }
    return View(list);
  }
}

for a model that looks like this:

using System.Data.Entity;
using System.Security.Claims;
using System.ComponentModel.DataAnnotations;

namespace SO_GUI.Models
{    
    public class BillingValidation
    {

        [Key]
        public string Section { get; set; }

        public string Details { get; set; }

        public string Total { get; set; }
    }    
}

your view would look something like this:

@model IEnumerable<SO_GUI.Models.BillingValidation>

@{
    ViewBag.Title = "Index";
}

<h2>Billing Validation</h2>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Section)
        </th> 
        <th>
            @Html.DisplayNameFor(model => model.Details)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Total)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Section)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Details)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Total)
        </td>
    </tr>
}
</table>

hope this helps.

Answer 2

You can do whatever you want in an action. It's, within reason, just a method, like any other method. How would you get the data if you weren't using MVC?

It sounds like you're looking for something that uses ADO.net, classes like SqlConnection and SqlCommand.

Using ADO.net is pretty straight-forward, albeit a tad messy at times, so I'd be tempted to pull it out into its own layer. But in any event, writing that code is outside the scope of this question, and asking how to is too broad given the information you've given us.

Put simply, though, again, you can do whatever you want in that method. Disregarding the data access piece, you can build from this.

public ActionResult Index()
{
    var transactions = new List<Portal.Models.TransactionModels>();

    transactions.Add(new Portal.Models.TransactionModels{
        id = "1",
        statusId = 4
    })
    transactions.Add(new Portal.Models.TransactionModels{
        id = "2",
        statusId = 3
    })

    return View(transactions);
}

If you put that in, your view should be populated with those two fake lines.