Saturday, 30 August 2014
How to apply Ado.Net Connected Mode with Mvc 4.0 and Sql Server
10:10
No comments
How to apply Ado.Net Connected Mode with
Mvc 4.0 and Sql Server………………
Note :
In this topic we will discuss that how
to connect Sql Server with Mvc 4.0 usually
User use this concept using
Entity Framwork and Linq but here we
going through Ado.Net Conneted Mode …………..
-- Sql Query
......................................
Create database shweta
use
shweta
create table student
(
RollNo int primary key,
Name varchar(50),
Gender varchar(50),
TotalMarks int
)
Step-1
First we create
class Student in Models ………………………
using System;
using
System.Collections.Generic;
using
System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
namespace
MvcwithRepositoryAdoConcept.Models
{
public class Student
{
[Display(Name="RollNo")]
[Required]
public int Roll { get; set; }
public string Name { get; set; }
public string Gender { get; set; }
[Range(1,100)]
public int TotalMarks { get; set; }
}
}
Step-2
then we create
a Folder we name is Repository and create HomeRepository class with in Folder ………
using System;
using
System.Collections.Generic;
using
System.Configuration;
using System.Data;
using
System.Data.SqlClient;
using System.Linq;
using System.Web;
using
MvcwithRepositoryAdoConcept.Models;
namespace
MvcwithRepositoryAdoConcept.Repository
{
public class HomeRepository
{
// connection concept.................
private static string constr = ConfigurationManager.ConnectionStrings["kush"].ConnectionString;
private SqlConnection con = new SqlConnection(constr);
public HomeRepository()
{
con.Open();
}
// code for
how to add data..................
public bool AddNewRecord(Student st)
{
var que = "insert into
Student values("
+ st.Roll + ",'"
+ st.Name + "','"
+ st.Gender + "',"
+ st.TotalMarks + ")";
SqlCommand cmd = new SqlCommand(que, con);
int i =
cmd.ExecuteNonQuery();
return i > 0;
}
// code for
how to serach data with RollNo
public Student GetStudent(int RollNo)
{
var que = "select *
from Student where RollNo=@RollNo";
SqlCommand cmd = new SqlCommand(que, con);
cmd.Parameters.AddWithValue("RollNo", SqlDbType.Int).Value=RollNo;
SqlDataReader dr =
cmd.ExecuteReader();
var stu = new Student();
while (dr.Read())
{
stu.Roll = Convert.ToInt32(dr["RollNo"]);
stu.Name = dr["Name"].ToString();
stu.Gender = dr["Gender"].ToString();
stu.TotalMarks = Convert.ToInt32(dr["TotalMarks"]);
}
return stu;
}
// code for
how to show all data..................
public IList<Student> GetAllStudent()
{
var que = "select *
from Student ";
SqlCommand cmd = new SqlCommand(que, con);
SqlDataReader dr =
cmd.ExecuteReader();
var list = new List<Student>();
while (dr.Read())
{
var stu=new Student
{
Roll = Convert.ToInt32(dr["RollNo"]),
Name = dr["Name"].ToString(),
Gender = dr["Gender"].ToString(),
TotalMarks = Convert.ToInt32(dr["TotalMarks"])
};
list.Add(stu);
}
return list;
}
// code for
how to update data with rollno..................
public void update(Student data)
{
var que = "update
Student set RollNo=@RollNo,Name=@Name,Gender=@Gender,TotalMarks=@TotalMarks
where RollNo=@RollNo";
SqlCommand cmd = new SqlCommand(que, con);
cmd.Parameters.AddWithValue("RollNo", SqlDbType.Int).Value =
data.Roll;
cmd.Parameters.AddWithValue("Name", SqlDbType.VarChar).Value =
data.Name;
cmd.Parameters.AddWithValue("Gender", SqlDbType.VarChar).Value =
data.Gender;
cmd.Parameters.AddWithValue("TotalMarks", SqlDbType.Int).Value =
data.TotalMarks;
cmd.ExecuteNonQuery();
}
// code for
how to delete data with RollNo..................
public void Delete(int RollNo)
{
var que = "Delete from Student where RollNo=@RollNo";
SqlCommand cmd = new SqlCommand(que, con);
cmd.Parameters.AddWithValue("RollNo", SqlDbType.Int).Value =
RollNo;
cmd.ExecuteNonQuery();
}
}
}
Step-3
then we create Controller which we name is HomeController
using System;
using
System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using
MvcwithRepositoryAdoConcept.Models;
using
MvcwithRepositoryAdoConcept.Repository;
namespace
MvcwithRepositoryAdoConcept.Controllers
{
public class HomeController : Controller
{
//
// GET: /Home/
public ActionResult Index()
{
return View();
}
// for insert data in
database......................
[HttpPost]
public ActionResult Index(Student model)
{
var respon = new HomeRepository();
respon.AddNewRecord(model);
return RedirectToAction("Index");
}
// for show all data from
database
[HttpGet]
public ActionResult AllData()
{
var respon = new HomeRepository();
var model = new AllData();
var v =
respon.GetAllStudent();
model.StudentList = v;
return View( model);
}
// for showdata with
RollNo............................
public ActionResult
ShowDatawithRollNo()
{
return View();
}
[HttpPost]
public ActionResult ShowDatawithRollNo(FormCollection fc)
{
var respon = new HomeRepository();
var model = new Student();
model = respon.GetStudent(Convert.ToInt32(fc["RollNo"]));
return View("ShowDatawithRollNo", model);
}
// code for
edit ..................
public ActionResult Edit(int RollNo)
{
var respon = new HomeRepository();
var model = new Student();
model =
respon.GetStudent(RollNo);
return View("Edit", model);
}
// code for update...................
public ActionResult Update(Student model)
{
var repo = new HomeRepository();
repo.update(model);
return RedirectToAction("AllData");
}
// code for
delete.....................
public ActionResult Delete(int RollNo)
{
var repo = new HomeRepository();
repo.Delete(RollNo);
return RedirectToAction("AllData");
}
}
}
Step-4 then we create
class AllData
in Models for fetch all record from Sql Server ………………………
using System;
using
System.Collections.Generic;
using System.Linq;
using System.Web;
namespace
MvcwithRepositoryAdoConcept.Models
{
public class AllData
{
public IList<Student> StudentList { get; set; }
}
}
Step-5 then we add view one by one …………………….
a. Index View
@model MvcwithRepositoryAdoConcept.Models.Student
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
<script src="~/Scripts/jquery-1.7.1.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
@using (Html.BeginForm())
{
@Html.ValidationSummary(true)
<fieldset>
<legend>Student</legend>
<div class="editor-label">
@Html.LabelFor(model
=> model.Roll)
</div>
<div class="editor-field">
@Html.EditorFor(model
=> model.Roll)
@Html.ValidationMessageFor(model
=> model.Roll)
</div>
<div class="editor-label">
@Html.LabelFor(model
=> model.Name)
</div>
<div class="editor-field">
@Html.EditorFor(model
=> model.Name)
@Html.ValidationMessageFor(model
=> model.Name)
</div>
<div class="editor-label">
@Html.LabelFor(model
=> model.Gender)
</div>
<div class="editor-field">
@Html.EditorFor(model
=> model.Gender)
@Html.ValidationMessageFor(model
=> model.Gender)
</div>
<div class="editor-label">
@Html.LabelFor(model
=> model.TotalMarks)
</div>
<div class="editor-field">
@Html.EditorFor(model
=> model.TotalMarks)
@Html.ValidationMessageFor(model
=> model.TotalMarks)
</div>
<p>
<input type="submit" value="Create" />
</p>
</fieldset>
}
<div>
@Html.ActionLink("Show All
Record",
"AllData")
@Html.ActionLink("Search with
RollNo","ShowDatawithRollNo")
</div>
</body>
</html>
b. Edit View
@model
MvcwithRepositoryAdoConcept.Models.Student
@{
Layout
= null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Edit</title>
</head>
<body>
<script src="~/Scripts/jquery-1.7.1.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
@using (Html.BeginForm("Update", "Home", FormMethod.Post)) {
@Html.ValidationSummary(true)
<fieldset>
<legend>Student</legend>
<div class="editor-label">
@Html.LabelFor(model
=> model.Roll)
</div>
<div class="editor-field">
@Html.EditorFor(model
=> model.Roll)
@Html.ValidationMessageFor(model
=> model.Roll)
</div>
<div class="editor-label">
@Html.LabelFor(model
=> model.Name)
</div>
<div class="editor-field">
@Html.EditorFor(model
=> model.Name)
@Html.ValidationMessageFor(model
=> model.Name)
</div>
<div class="editor-label">
@Html.LabelFor(model
=> model.Gender)
</div>
<div class="editor-field">
@Html.EditorFor(model
=> model.Gender)
@Html.ValidationMessageFor(model
=> model.Gender)
</div>
<div class="editor-label">
@Html.LabelFor(model
=> model.TotalMarks)
</div>
<div class="editor-field">
@Html.EditorFor(model
=> model.TotalMarks)
@Html.ValidationMessageFor(model
=> model.TotalMarks)
</div>
<p>
<input type="submit" value="Save" />
</p>
</fieldset>
}
<div>
@Html.ActionLink("Back to
List",
"Index")
</div>
</body>
</html>
c. AllData View
@model MvcwithRepositoryAdoConcept.Models.AllData
@{
//Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>AllData</title>
</head>
<body>
<caption> Student List</caption>
</br>
<table border="2" cellpadding="1" cellspacing="1">
<tr>
<th>RollNo</th>
<th>Name</th>
<th>Gender</th>
<th>Marks</th>
</tr>
</br>
@foreach (var item in Model.StudentList)
{
<tr>
<td>@item.Roll</td>
<td>@item.Name</td>
<td>@item.Gender</td>
<td>@item.TotalMarks</td>
<td>
<a href="~/Home/Edit?RollNo=@item.Roll">Edit</a>
</td>
<td>
<a href="~/Home/Delete?RollNo=@item.Roll">Delete</a>
</td>
</tr>
}
</table>
<a href="~/Home/Index"> Add New Record</a>
</body>
</html>
d. ShowDatawithRollNo
@model MvcwithRepositoryAdoConcept.Models.Student
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>ShowDatawithRollNo</title>
</head>
<body>
<div>
@using (Html.BeginForm())
{
<table width="50%" >
<tr><td>RollNo</td><td>@Html.TextBox("RollNo")<input type="submit" name="submit" id="submit" value="Search" /> </td></tr>
@if(Model!=null)
{
@Html.Hidden("Studentid",Model.Roll)
<tr><td>Name</td><td>@Html.TextBox("Name",Model.Name )</td></tr>
<tr><td>Gender</td><td>@Html.TextBox("Gender",Model.Gender)</td></tr>
<tr><td>Course</td><td>@Html.TextBox("Course",Model.TotalMarks)</td></tr>
}
@* @Html.DisplayForModel();*@
</table>
}
</div>
</body>
</html>
Result :
Subscribe to:
Posts (Atom)