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.