asked on

Use MVC to display Database rows

I'd like to something like the code below using an MVC application. Happy to use the Home Controller. Which part of the code goes in the Controller and what goes in the model and view?

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="UI.WebForm1" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div><h1>Hello All</h1>
            <asp:Button ID="cmdDisplay" runat="server" Text="Button" OnClick="cmdDisplay_Click" />
            <br />
            <asp:GridView ID="GridView1" runat="server"></asp:GridView>
        </div>
    </form>
</body>
</html>

Open in new window

<connectionStrings>
    <add name="AdventureWorks" connectionString="Data Source=.\SqlExpress;Initial Catalog=AdventureWorks2016CTP3; Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

Open in new window

using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace UI
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void cmdDisplay_Click(object sender, EventArgs e)
        {
            string ConnectionString = ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString;
            using (SqlConnection sqlCon = new SqlConnection())
            {
                using (SqlCommand sqlcmd = new SqlCommand())
                {
                    using (SqlDataAdapter sqladp = new SqlDataAdapter())
                    {
                        using (DataTable dt = new DataTable())
                        {
                            sqlCon.ConnectionString = ConnectionString;
                            sqlcmd.Connection = sqlCon;
                            sqlcmd.CommandTimeout = 0;
                            sqlcmd.Parameters.Clear();
                            sqlcmd.CommandType = CommandType.StoredProcedure;
                            sqlcmd.CommandText = "uspGetManagerEmployees";
                            sqlcmd.Parameters.AddWithValue("@BusinessEntityID", 2);
                            sqladp.SelectCommand = sqlcmd;
                            sqladp.Fill(dt);
                            GridView1.DataSource = dt;
                            GridView1.DataBind();
                        }
                    }
                }
            }
        }
    }
}

Open in new window

Misha

The first code block like view. It is html code in which you can add javascript code or links on it. In ASP Net MVC you can also use HTML Helpers in these files;
The second code block is connection string from you web.config file. It is not model, view or controller.
The third code block likes controller. You get data from DataBase. But programmers usually use EntityFramework to get data in MVC. Not ADO net, but there is some cases where you need to use ADO net. You can make main logic in controller and pass model to your views.
There in no model in your code blocks. Model is class, where you can descride prorerties like fields in dataBase table and set relationships between models (tables)
Start to learn MVC with this link.
https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/introduction/getting-started

AlHal2

ASKER

I had a look at the tutorial and can create an MVC project in Visual Studio 2017.
Please could you show me what to put in the controller. At the moment I have

using System.Web.Mvc;


namespace UI.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }
    }
}

Open in new window

In Index.cshtml I have this. I don't know how to separate the logic which retrives the data using the Controller and view which displays it.
Please can you show me where to put all the code even if it means copying and Pasting your code into mine?

@using System.Data
@using System.Data.SqlClient
@{
    ViewBag.Title = "Home Page";
}

<div class="jumbotron">
    <h1>ASP.NET</h1>
    <p class="lead">ASP.NET is a free web framework for building great Web sites and Web applications using HTML, CSS and JavaScript.</p>
    <p><a href="https://asp.net" class="btn btn-primary btn-lg">Learn more &raquo;</a></p>
</div>


    <div class="row">
        <div class="col-md-4">
            <h2>Getting started</h2>
            <p>
                ASP.NET MVC gives you a powerful, patterns-based way to build dynamic websites that
                enables a clean separation of concerns and gives you full control over markup
                for enjoyable, agile development.
            </p>
            <p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301865">Learn more &raquo;</a></p>
        </div>
        <div class="col-md-4">
            <h2>Get more libraries</h2>
            <p>NuGet is a free Visual Studio extension that makes it easy to add, remove, and update libraries and tools in Visual Studio projects.</p>
            <p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301866">Learn more &raquo;</a></p>
        </div>
        <div class="col-md-4">
            <h2>Web Hosting</h2>
            <p>You can easily find a web hosting company that offers the right mix of features and price for your applications.</p>
            <p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301867">Learn more &raquo;</a></p>
        </div>
    </div>

Open in new window

Misha

Ok, let`s create simple MVC application together.
1) I assume, that you install Entity Framework in NUget, and set correct connection string,
2) Create two simple classes (two files) in Model folder. It will be your Models and tables in your dataBase

// book.cs
public class Book
    {
        // ID 
        public int Id { get; set; }
        public string Name { get; set; }
        public string Author { get; set; }
        public int Price { get; set; }
    }
public class Purchase
    {
        // ID 
        public int PurchaseId { get; set; }
        public string Person { get; set; }
        public string Address { get; set; }
        public int BookId { get; set; }
        public DateTime Date { get; set; }
    }

Open in new window

Then you should create dbcontext class (file)

public class BookContext : DbContext
    {
        public DbSet<Book> Books { get; set; }
        public DbSet<Purchase> Purchases { get; set; }
    }

Open in new window

DbContext is the primary class that is responsible for interacting with data as an object.
Referenced from: - http://www.entityframeworktutorial.net/EntityFramework4.3/dbcontext-vs-objectcontext.aspx
3) Work with controller:

public class HomeController : Controller
    {
// create context db
        BookContext db = new BookContext();
        public ActionResult Index()
        {
            //get all books
            IEnumerable<Book> books = db.Books;
            // post in to ViewBag
            ViewBag.Books = books;
            return View();
        }
    }

Open in new window

4) simple view. It can be book store later..

@{
    Layout = null;
}
 
<!DOCTYPE html>
 
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Book store</title>
</head>
<body>
    <div>
        <h3>Many book!!</h3>
        <table>
            <tr><td><p>Book Name</p></td>
                <td><p>Author</p></td>
                <td><p>Price</p></td><td></td>
            </tr>
            @foreach (var b in ViewBag.Books)
            {
            <tr>
                <td><p>@b.Name</p></td>
                <td><p>@b.Author</p></td>
                <td><p>@b.Price</p></td>
                <td><p><a href="/Home/Buy/@b.Id">Buy</a></p></td>
            </tr>
            }
        </table>
    </div>
</body>
</html>

Open in new window

Also I has posted not correct link. Sorry.
This link is better.
http://www.tutorialsteacher.com/mvc/asp.net-mvc-tutorials

AlHal2

ASKER

I created book.cs in models folder

Here are the error messages.

The type or namespace name 'Book' could not be found (are you missing a using directive or an assembly reference?)      UI      D:\OneDrive\Documents\MyPrograms\MVC\GetData\UI\BookContext.cs      11      Active
The type or namespace name 'Purchase' could not be found (are you missing a using directive or an assembly reference?)      UI      D:\OneDrive\Documents\MyPrograms\MVC\GetData\UI\BookContext.cs      12      Active
The type or namespace name 'Book' could not be found (are you missing a using directive or an assembly reference?)      UI      D:\OneDrive\Documents\MyPrograms\MVC\GetData\UI\Controllers\HomeController.cs      13      Active

Perhaps I need to add some using statements. For now I just want to extract data from the Database using ADO.NET or Entity Framework, but I need code from scratch.

Misha

Add reference to Model folder in your BookContext.

using YourProjectName.Models;

Open in new window

Because your Dbcontext has placed most likely not in this folder.

AlHal2

ASKER

That's helped.
I've created a Book table in my SQL Database with a few rows. How do I get the program to look at it?
I have the connection string in web.config.

Misha

You use db context for this.
Try to place breakpoint to your code

            BookContext db = new BookContext();
//breakpoint
            IEnumerable<Book> books = db.Books;
// or 
List<Book> LIstbooks  = db.Books.ToList();

Open in new window

In variable books you can see all your books, stored in DataBase

AlHal2

ASKER

With this code the books object is null

using System.Collections.Generic;
using System.Web.Mvc;
using UI.Models;

namespace UI.Controllers
{
    public class HomeController : Controller
    {
        //BookContext db = new BookContext();
        public ActionResult Index()
        {
            BookContext db = new BookContext();
            //get all books
            IEnumerable<Book> books = db.Books;
            // post in to ViewBag
            ViewBag.Books = books;
            return View();
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }
    }
}

Open in new window

Misha

Inherit your ApplicationDbContext from base with connectiong string name "AdventureWorks"

       public BookContext()
            : base("AdventureWorks")
        {
        }

Open in new window

AlHal2

ASKER

Where is the right place to put it? This is giving an error.

using System.Collections.Generic;
using System.Web.Mvc;
using UI.Models;

namespace UI.Controllers
{

public class HomeController : Controller
    {
        public BookContext()
: base("AdventureWorks");
            
    //BookContext db = new BookContext();
    public ActionResult Index()
        {
     
            //BookContext db = new BookContext();
            //get all books
            IEnumerable<Book> books = db.Books;
            // post in to ViewBag
            ViewBag.Books = books;
            return View();
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }
    }
}

Open in new window

Misha

NO! You should do it in BookContext class! You can not inherit class in other class. In HomeController you only create new instance of this class,

 public class BookDbContext : DbContext
    {
        public ApplicationDbContext()
            : base("AdventureWorks")
        {
        }
 public DbSet<Book> Books { get; set; }
        public DbSet<Purchase> Purchases { get; set; }
}

Open in new window

AlHal2

ASKER

It's saying method must have a return type

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using UI.Models;
namespace UI
{
    public class BookContext:DbContext
    {
        public ApplicationDbContext()
    : base("AdventureWorks")
        {
        }

        public DbSet<Book> Books { get; set; }
        public DbSet<Purchase> Purchases { get; set; }
    }
}

Open in new window

Misha

Sorry, my mistake
public BookContext():base("AdwentureWorks")

AlHal2

ASKER

Sorry.
'BookContext.BookContext()' must declare a body because it is not marked abstract, extern, or partial

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using UI.Models;
namespace UI
{
   
    public class BookContext : DbContext
    {
        public BookContext() : base("AdventureWorks");
        

        public DbSet<Book> Books { get; set; }
        public DbSet<Purchase> Purchases { get; set; }
    }
}

Open in new window

AlHal2

ASKER

Thanks very much for yesterday. I got the program to run with this code. Why is it giving no output when the book and Purchase tables have data in them? All that appears on the page is:

Many book!!
Book Name
Author
Price

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using UI.Models;
namespace UI
{
   
    public class BookContext : DbContext
    {
        public BookContext() : base("AdventureWorks") { }
        

        public DbSet<Book> Books { get; set; }
        public DbSet<Purchase> Purchases { get; set; }
    }
}

Open in new window

@using System.Data
    @using System.Data.SqlClient
    @{
        ViewBag.Title = "Home Page";
    }

@{
    Layout = null;
}

[code]@using System.Data
    @using System.Data.SqlClient

    @{
        ViewBag.Title = "Home Page";
    }

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Book store</title>
</head>
<body>
    <div>
        <h3>Many book!!</h3>
        <table>
            <tr>
                <td><p>Book Name</p></td>
                <td><p>Author</p></td>
                <td><p>Price</p></td>
                <td></td>
            </tr>
            @foreach (var b in ViewBag.Books)
            {  
                <tr>
                    <td><p>@b.Name</p></td>
                    <td><p>@b.Author</p></td>
                    <td><p>@b.Price</p></td>
                    <td><p><a href="/Home/Buy/@b.Id">Buy</a></p></td>
                </tr>
            }
        </table>
    </div>
</body>
</html>

Open in new window

using System.Collections.Generic;
using System.Web.Mvc;
using UI.Models;

namespace UI.Controllers
{

public class HomeController : Controller
    {

            
    //BookContext db = new BookContext();
    public ActionResult Index()
        {
     
            BookContext db = new BookContext();
            //get all books
            IEnumerable<Book> books = db.Books;
            // post in to ViewBag
            ViewBag.Books = books;
            return View();
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }
    }
}

Open in new window

Misha

Try to put this code in Index.cshtml:

@{
    ViewBag.Title = "Home Page";
}

<div>
    <h3>Many book!!</h3>
    <table>
        <tr>
            <td><p>Book Name</p></td>
            <td><p>Author</p></td>
            <td><p>Price</p></td>
            <td></td>
        </tr>
        @foreach (var b in ViewBag.Books)
        {
            <tr>
                <td><p>@b.Name</p></td>
                <td><p>@b.Author</p></td>
                <td><p>@b.Price</p></td>
                <td><p><a href="/Home/Buy/@b.Id">Buy</a></p></td>
            </tr>
        }
    </table>
</div>

Open in new window

AlHal2

ASKER

no change. Do I need to install anything eg Entity Framework from Nuget?

AlHal2

ASKER

I've found the possible problem. In the DB, I've created a table book.
The SQL generated by EF is looking for a table books.
How do I change it to look for book instead of books?

AlHal2

ASKER

I've amended BookContext and made corresponding adjustments, so the program compiles.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using UI.Models;
namespace UI
{
   
    public class BookContext : DbContext
    {
        public BookContext() : base("AdventureWorks") { }
        

        public DbSet<Book> Book { get; set; }
        public DbSet<Purchase> Purchase { get; set; }
    }
}

Open in new window

Misha

It is better to use plural when you create collection in your context class:

public DbSet<Book> Books { get; set; }

Open in new window

You can use Table attribute to set any table name, corresponding your class:

using System.ComponentModel.DataAnnotations.Schema;
///---
 [Table("Book")]
public class Book
    {
        // ID 
        public int Id { get; set; }
        public string Name { get; set; }
        public string Author { get; set; }
        public int Price { get; set; }
    }

Open in new window

AlHal2

ASKER

When I add the attribute I get a message:
The model backing the 'BookContext' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).

ASKER CERTIFIED SOLUTION

Misha

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AlHal2

ASKER

Thanks Misha. I got it working by renaming the book table to books

AlHal2

ASKER

Here is the code. I'm used to writing a GUI with a pre populated Database, so find it a bit scary that I may need to delete a Database.

using System.Collections.Generic;
using System.Web.Mvc;
using UI.Models;

namespace UI.Controllers
{

public class HomeController : Controller
    {

            
    //BookContext db = new BookContext();
    public ActionResult Index()
        {
     
            BookContext db = new BookContext();
            //get all books
            IEnumerable<Book> book = db.Books;
            // post in to ViewBag
            ViewBag.Books = book;
            return View();
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }
    }
}

Open in new window

using System;
namespace UI.Models
{
    
    public class Book
        {
            // ID 
            public int Id { get; set; }
            public string Name { get; set; }
            public string Author { get; set; }
            public int Price { get; set; }
        }
        public class Purchase
        {
            // ID 
            public int PurchaseId { get; set; }
            public string Person { get; set; }
            public string Address { get; set; }
            public int BookId { get; set; }
            public DateTime Date { get; set; }
        }
    
}

Open in new window

@{
    ViewBag.Title = "Home Page";
}

<div>
    <h3>Many book!!</h3>
    <table>
        <tr>
            <td><p>Book Name</p></td>
            <td><p>Author</p></td>
            <td><p>Price</p></td>
            <td></td>
        </tr>
        @foreach (var b in ViewBag.Books)
        {
            <tr>
                <td><p>@b.Name</p></td>
                <td><p>@b.Author</p></td>
                <td><p>@b.Price</p></td>
                <td><p><a href="/Home/Buy/@b.Id">Buy</a></p></td>
            </tr>
        }
    </table>
</div>

Open in new window

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using UI.Models;
namespace UI
{
   
    public class BookContext : DbContext
    {
        public BookContext() : base("AdventureWorks") { }
        

        public DbSet<Book> Books { get; set; }
        public DbSet<Purchase> Purchases { get; set; }
    }
}

Open in new window

In web.config:

  </system.web>
  <connectionStrings>
    <add name="AdventureWorks" connectionString="Data Source=.\SqlExpress;Initial Catalog=AdventureWorks2016CTP3; Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

Open in new window

Misha

There is a code-first approach in Entity Framework. In this case you write C# code and your dataBase is created and is populated after.
Also there is a dataBase-first approach. In this case you create dataBase and EntityFramework create C# code after. You can read more details in article abour EntiyFramework, which I posted early.
Glad to help you!