Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Asp.Net MVC Get (Display) Data from Database using ADO.NET

Oct 13, 2016
Introduction

Here I will explain how to get data from database in
asp.net mvc using ADO.NET with example or asp.net mvc display or show data from database using ADO.NET with example or asp.net mvc retrieve or read data from database and show it in view with example. By using ADO.NET concept in asp.net mvc we can easily insert and get data from database based on our requirements.


Here we will see how to insert data into database in asp.net mvc and how to get data from database with example. Before we proceed to insert or get data from database first design userdetails table like as shown below.

Column Name
Data Type
Allow Nulls
userid
Int(IDENTITY=TRUE)
NO
username
varchar(50)
Yes
education
Varchar(50)
Yes
location
Varchar(50)
Yes
Or use following query to create userdetails table in database.


create table userdetails(
userid int primary key identity,
username varchar(50),
education varchar(50),
location varchar(50)
)

To insert and get data from userdetails write query like as shown below


Create Procedure usercrudoperations
(
@name varchar(50),
@education varchar(50),
@location varchar(50),
@status varchar(10)
)
As
BEGIN
-- Insert User Details
if @status ='INSERT'
BEGIN
INSERT INTO userdetails(username,education,location)
VALUES(@name,@education,@location)
END
-- Get User Details
if @status ='GET'
BEGIN
SELECT * FROM userdetails
END
END

Once we create userdetails table and stored procedure to insert and get data from database now create asp.net mvc application for that Open visual studio --> Go to File --> Select New --> Project like as shown below

Once we select Project new popup will open in that select Asp.Net Web Application and give name to application and click OK like as shown below

Once click OK new popup will open in that select MVC template and click OK like as shown below


Once we finished creating application our project structure will be like as shown below

Now we will add new model to our application to define properties in our application for that right click on Models folder --> select Add --> select Class like as shown below

Once we click on Class new popup will open in that give name of your model as “UserDetails” and click Add button like as shown below

Now open newly created model (UserDetails) and write the code like as shown below


using System.Collections.Generic;

namespace InsertGetUserDetails.Models
{
public class UserDetails
{
public int UserId { get; set; }
public string UserName { get; set; }
public string Education { get; set; }
public string Location { get; set; }
public List<UserDetails> usersinfo { get; set; }
}
}

Now we will add new controller to insert or get data from database for that right click on Controller folder --> select Add --> Controller like as shown below

Adding new controller to asp.net mvc application
Once we click on Controller new popup will open in that select MVC 5 Controller – Empty and click Add like as shown below.

select empty mvc controller to add it in asp.net mvc application
Once click on Add new window will open in that enter the name of controller and click Add like as shown below

Now open newly created controller and write the code like as shown below


using System;
using System.Collections.Generic;
using System.Web.Mvc;
using InsertGetUserDetails.Models;
using System.Data.SqlClient;
using System.Data;

namespace InsertGetUserDetails.Controllers
{
public class UserController : Controller
{
// GET: User
public ActionResult InsertUserDetails()
{
UserDetails objuser = new UserDetails();
DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection("Data Source=Suresh;Integrated Security=true;Initial Catalog=MySamplesDB"))
{
using (SqlCommand cmd = new SqlCommand("usercrudoperations", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@status", "GET");
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
List<UserDetails> userlist = new List<UserDetails>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
UserDetails uobj = new UserDetails();
uobj.UserId = Convert.ToInt32(ds.Tables[0].Rows[i]["userid"].ToString());
uobj.UserName = ds.Tables[0].Rows[i]["username"].ToString();
uobj.Education = ds.Tables[0].Rows[i]["education"].ToString();
uobj.Location = ds.Tables[0].Rows[i]["location"].ToString();
userlist.Add(uobj);
}
objuser.usersinfo = userlist;
}
con.Close();
}
return View(objuser);
}
[HttpPost]
public ActionResult InsertUserDetails(UserDetails user)
{
UserDetails objuser = new UserDetails();
using (SqlConnection con = new SqlConnection("Data Source=Suresh;Integrated Security=true;Initial Catalog=MySamplesDB"))
{
using (SqlCommand cmd = new SqlCommand("usercrudoperations", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@name", user.UserName);
cmd.Parameters.AddWithValue("@education", user.Education);
cmd.Parameters.AddWithValue("@location", user.Location);
cmd.Parameters.AddWithValue("@status", "INSERT");
con.Open();
ViewData["result"] = cmd.ExecuteNonQuery();
con.Close();
}
}
return View();
}
}
}

If you observe above controller code, we defined InsertUserDetails method two times one is for insertion and another for getting data from database.

Now we will add view to our controller action method for that right click on InsertUserDetails action method --> select Add View like as shown below

Now give name “InsertUserDetails” to view, select template as “Empty” and select Model class as “UserDetails” which we created in our application then click on Add button like as shown below.

The newly created view will be added under Views folder like as shown below


Now open newly created view and write the code like as shown below


@model InsertGetUserDetails.Models.UserDetails
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>InsertUserDetails</title>
<script src="~/Scripts/jquery-1.10.2.js"></script>
</head>
<body>
@using (Html.BeginForm("InsertUserDetails","User",FormMethod.Post)) {
<table>
<tr>
<td>User Name:</td>
<td>@Html.TextBoxFor(u=>u.UserName)</td>
</tr>
<tr>
<td>Education:</td>
<td>@Html.TextBoxFor(u => u.Education)</td>
</tr>
<tr>
<td>Location:</td>
<td>@Html.TextBoxFor(u=>u.Location)</td>
</tr>
<tr>
<td></td>
<td> <input type="submit" value="Register" /> </td>
</tr>
</table>
}
<h4>User Details</h4>
@if (Model != null)
{
if (Model.usersinfo.Count > 0)
{
<table>
<tr>
<th>UserId</th>
<th>UserName</th>
<th>Education</th>
<th>Location</th>
</tr>
@foreach (var item in Model.usersinfo)
{
<tr>
<td>@Html.DisplayFor(modelitem => item.UserId) </td>
<td>@Html.DisplayFor(modelitem => item.UserName)</td>
<td>@Html.DisplayFor(modelitem => item.Education)</td>
<td>@Html.DisplayFor(modelitem => item.Location)</td>
</tr>
}
</table>
}
else {
<b>No Details Found.</b>
}
}
<script type="text/javascript">
$(function () {
var msg = '@ViewData["result"]';
if (msg == '1')
{
alert("User Details Inserted Successfully");
window.location.href = "@Url.Action("InsertUserDetails", "User")";
}
});
</script>
</body>
</html>

Now we will run and see the application result. (url always in the format of http://localhost:portnumber/controller name/action method name) and check the output that would be like as shown below

This is how we can insert and get data from database in asp.net mvc.

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

6 comments :

Unknown said...

Sir , i got a error : An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Procedure or function 'usercrudoperation' expects parameter '@username', which was not supplied.


in that line : da.Fill(ds);

Can you please help me ...!!!

Unknown said...

here is code :
// GET data from stored procedure
public ActionResult InsertUserDetails()
{
UserDetails objuser = new UserDetails();
DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection())
{
using (SqlCommand cmd = new SqlCommand("usercrudoperation", con))
{
con.ConnectionString = WebConfigurationManager.ConnectionStrings["mycon"].ToString();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@status", "GET");
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds); // got error in that line

List userlist = new List();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
UserDetails uobj = new UserDetails();
uobj.userid = Convert.ToInt32(ds.Tables[0].Rows[i]["userid"].ToString());
uobj.username = ds.Tables[0].Rows[i]["username"].ToString();
uobj.education = ds.Tables[0].Rows[i]["education"].ToString();
uobj.location = ds.Tables[0].Rows[i]["location"].ToString();
userlist.Add(uobj);
}
objuser.userinfo = userlist;
}
con.Close();
}
return View(objuser);
}

Vishal Yadav said...

Make changes in below procedure like this

Create Procedure usercrudoperations
(
@name varchar(50)=null,
@education varchar(50)=null,
@location varchar(50)=null,
@status varchar(10)
)
As
BEGIN
-- Insert User Details
if @status ='INSERT'
BEGIN
INSERT INTO userdetails(username,education,location)
VALUES(@name,@education,@location)
END
-- Get User Details
if @status ='GET'
BEGIN
SELECT * FROM userdetails
END
END

Anonymous said...

ypanchal10@gmail.com

Unknown said...

still get error on this line

da.Fill(ds);

please help

Unknown said...

ME GETTING ERROR IN da.fill(ds)

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.