Read data from SQL Server using MVC application

Sourav.Kayal
Posted by in ASP.NET MVC category on for Beginner level | Points: 250 | Views : 37233 red flag

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.

 

 

Page copy protected against web site content infringement by Copyscape

About the Author

Sourav.Kayal
Full Name: Sourav Kayal
Member Level: Silver
Member Status: Member,MVP
Member Since: 6/20/2013 2:09:01 AM
Country: India
Read my blog here http://ctrlcvprogrammer.blogspot.in/
http://www.dotnetfunda.com
I am .NET developer working for HelixDNA Technologies,Bangalore in healthcare domain. Like to learn new technology and programming language. Currently working in ASP.NET ,C# and other microsoft technologies.

Login to vote for this post.

Comments or Responses

Posted by: Akiii on: 9/5/2013 | Points: 25
Nice article.
But you can use var in the below line also;

var Li = (List<MvcApplication1.Models.Person>) ViewData["Person"];

Akiii
Posted by: Sourav.Kayal on: 9/5/2013 | Points: 25
Yes akii. var is universal variable. :) We can use it anywhere.
Posted by: Rickyest on: 4/16/2014 | Points: 25
Hello.
I did everything the same but still I can not compile it always shows an error that I can not fix.

Model:
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Configuration;

namespace TesteMVC.Models
{
public class Demo
{
SqlConnection conexao = new SqlConnection();

List<Demo> DemoList = new List<Demo>();
public Int32 id { get; set; }
public Int32 ano { get; set; }
public Int32 sales { get; set; }
public Int32 expences { get; set; }

Demo d = null;
public List<Demo> GetDemo()
{
conexao.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["Demo"].ConnectionString;

conexao.Open();

using (conexao)
{
string strQuerySelect = "Select * from Demo";

SqlCommand cmd = new SqlCommand(strQuerySelect, conexao);

SqlDataReader dados = cmd.ExecuteReader();

while (dados.Read())
{
d = new Demo();
d.id = Convert.ToInt32(dados.GetInt32(0));
d.ano = Convert.ToInt32(dados.GetInt32(1));
d.sales = Convert.ToInt32(dados.GetInt32(2));
d.expences = Convert.ToInt32(dados.GetInt32(3));
DemoList.Add(d);
}
}
return DemoList;
}
}
}


Web.config:

 <connectionStrings>
<add name="Demo" providerName="System.Data.SqlClient" connectionString="Data Source=RONISE-PC\SQLEXPRESS;Initial Catalog=Demo;Integrated Security=SSPI" />
</connectionStrings>


Controler:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using TesteMVC;
using TesteMVC.Models;

namespace TesteMVC.Controllers
{
public class HomeController : Controller
{
//
// GET: /Home/

public ActionResult Index()
{
return View();
}


public ActionResult GetDemo()
{
Demo d = new Demo();
List<Demo> Li = new List<Demo>();

Li = d.GetDemo();
ViewData["Demo"] = Li;
return View();
}

}
}


View Razor

<h2>GetDemo</h2>

@{List<TesteMVC.Models.Demo> Li = (List<TesteMVC.Models.Demo>)ViewData["Demo"];

}
<table>
<tr>
<td></td>
<td>Demo Information</td>
</tr>
@foreach (TesteMVC.Models.Demo D in Li)
{
<tr>

<td>@D.id</td>

<td>@D.ano</td>

<td>@D.sales</td>

<td>@D.expences</td>

</tr>
}

</table>
}


But I keep getting the following error:

Server Error in '/' Application.
Invalid object name 'Demo'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'Demo'.

Source Error:


Line 35: SqlCommand cmd = new SqlCommand(strQuerySelect, conexao);
Line 36:
Line 37: SqlDataReader dados = cmd.ExecuteReader();
Line 38:
Line 39: while (dados.Read())


And I have these 3 message on my project

Message	1	Could not find schema information for the element 'entityFramework'.	
Message 2 Could not find schema information for the element 'defaultConnectionFactory'.
Message 3 Could not find schema information for the attribute 'type'.


I really need to solve this, can you help me please?

Login to post response

Comment using Facebook(Author doesn't get notification)