How to read from SQL Server from MVC application
Read data from SQLServer using MVC application
In this article we will see how to read data from SQL Server
database using MVC Design pattern. As per MVC design we will create three
separate sections, Model, View and Controller. Let’s start
with Model.
Model Class
Here we have defined simple Person class to do database operation.
This model class contains two data member called Name and Age.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
namespace MvcApplication1.Models
{
public class Person
{
SqlConnection con = new SqlConnection();
List<Person> PersonList = new List<Person>();
public String Name { get; set; }
public Int32 Age { get; set; }
Person p = null;
public List<Person> GetPerson()
{
con.ConnectionString = "Data Source=SERVER-NAME\\SQL_INSTANCE2;Initial
Catalog=test;Integrated Security=True";
con.Open();
using (con)
{
SqlCommand cmd = new SqlCommand("Select * from Person",con);
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
p = new Person();
p.Name =Convert.ToString(rd.GetSqlValue(0));
p.Age = Convert.ToInt32(rd.GetInt32(1));
PersonList.Add(p);
}
}
return PersonList;
}
}
}
Controller Class
Controller class will manage user’s request and it will
display appropriate View. Here we have define GetPerson() action within
Controller Class. This action will call GetPerson() function which is define in
Model class.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MvcApplication1;
using MvcApplication1.Models;
namespace MvcApplication1.Controllers
{
public class PersonController : Controller
{
//
// GET: /Person/
public ActionResult Index()
{
return View();
}
public ActionResult GetPerson()
{
Person p = new Person();
List<Person> Li = new List<Person>();
Li = p.GetPerson();
ViewData["Person"] = Li;
return View();
}
}
}
Define View
Now, we have to define view to handle users request.
<%@ Page
Language="C#"
Inherits="System.Web.Mvc.ViewPage<MvcApplication1.Models.Person>"
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD
XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>GetPerson</title>
</head>
<body>
<div>
<% List<MvcApplication1.Models.Person> Li = (List<MvcApplication1.Models.Person>) ViewData["Person"];%>
<table>
<tr>
<td></td>
<td>Person Information</td>
</tr>
<% foreach (MvcApplication1.Models.Person P in Li)
{ %>
<tr>
<td><%= P.Name%></td>
<td><%= P.Age%></td>
</tr>
<% } %>
</table>
</div>
</body>
</html>
Here is table Structure of Person table.
Conclusion:-
This is simple database operation using MVC design pattern. Hope you have enjoyed reading the article.