Saturday 30 August 2014

How to apply Ado.Net Connected Mode with Mvc 4.0 and Sql Server

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 :






0 comments:

Post a Comment