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

AlHal2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MishaProgrammerCommented:
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
0
AlHal2Author Commented:
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

0
MishaProgrammerCommented:
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
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

AlHal2Author Commented:
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.
0
MishaProgrammerCommented:
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.
0
AlHal2Author Commented:
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.
0
MishaProgrammerCommented:
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
0
AlHal2Author Commented:
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

0
MishaProgrammerCommented:
Inherit your ApplicationDbContext from base with connectiong string name "AdventureWorks"

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

Open in new window

0
AlHal2Author Commented:
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

0
MishaProgrammerCommented:
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

0
AlHal2Author Commented:
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

0
MishaProgrammerCommented:
Sorry, my mistake
public BookContext():base("AdwentureWorks")
0
AlHal2Author Commented:
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

0
AlHal2Author Commented:
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

0
MishaProgrammerCommented:
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

0
AlHal2Author Commented:
no change.  Do I need to install anything eg Entity Framework from Nuget?
0
AlHal2Author Commented:
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?
0
AlHal2Author Commented:
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

0
MishaProgrammerCommented:
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

0
AlHal2Author Commented:
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).
0
MishaProgrammerCommented:
Rebuild your solution and execute command in NuGet console
Update-DataBase

Open in new window

If it does not help, delete your dataBase.
 execute command
Enable-Migrations

Open in new window

After this action you can see folder migration in your project. There is configuration file in this folder.
Set
 AutomaticMigrationsEnabled = true;

Open in new window

in this file.
 and  execute
Update-DataBas

Open in new window

e command again.
Also you really need to read more about Entity Framework before using ASP NET MVC.
http://www.entityframeworktutorial.net/code-first/what-is-code-first.aspx
I has posted this link early.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlHal2Author Commented:
Thanks Misha.  I got it working by renaming the book table to books
0
AlHal2Author Commented:
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

0
MishaProgrammerCommented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.