How to use Business object as model using Store Procedure,Ado.Net
in Mvc
Note : In this concept we will discuss object as model until now we have being using entity framework and
entities ,Entities are mapped to database table and objects relationship mapping
tool like entity Framwork,Hibernate etc used to retrieve and same data. Means here we will
not work with Entity Framwork or
Linq ,we will use Store Procedure with
Ado.net
create database test
use test
-- create table student
create table Student
(
RollNo int primary key,
Name varchar(50),
Gender varchar(50),
Course varchar(50),
Dob Date
)
sp_help student
-- create procdure for insert data into Student table
create proc Ins_Data
@RollNo int,
@Name varchar(50),
@Gender varchar(50),
@Course varchar(50),
@Dob Date
as
begin
insert into Student values(@RollNo,@Name,@Gender,@Course,@Dob)
end
exec Ins_Data 1,'Raj Kumar Basera','Male','Mca','09/09/1987'
-- create procdure for show all data
from table Student
create proc ShowAll
as
begin
select * from Student
end
exec ShowAll
-- delete data from table by RollNo……………..
create proc Delete_Data @RollNo int
as
begin
delete from Student where RollNo=@RollNo
end
exec Delete_Data 1
-- update data table by RollNo…………………….
create proc Update_Data
@RollNo int,
@Name varchar(50),
@Gender varchar(50),
@Course varchar(50),
@Dob Date
as
begin
update Student set Name=@Name,Gender=@Gender,Course=@Course,Dob=@Dob where RollNo=@RollNo
end
exec Update_Data
1, 'Raj Kumar
Basera','Male','Mca','09/09/1987'
-- search data from table by RollNo…………….
create proc Search_Data @RollNo int
as
begin
select * from Student where RollNo=@RollNo
end
exec Search_Data 1
here we will work database test
so we must declare connection string in Web.Config
<configuration>
<configSections>
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection,
EntityFramework, Version=5.0.0.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>
<connectionStrings>
<add name="dbcon" connectionString="Data
Source=KUSH\SA;Initial Catalog=test;User ID=sa;Password=tiwari" providerName="System.Data.SqlClient" />
</connectionStrings>
<appSettings>
<add key="webpages:Version" value="2.0.0.0" />
<add key="webpages:Enabled" value="false" />
<add key="PreserveLoginUrl" value="true" />
<add key="ClientValidationEnabled" value="true" />
<add key="UnobtrusiveJavaScriptEnabled" value="true" />
</appSettings>
Frist we take MvcApplication and add new class library for Business Layer
After that we add new class which name is Student.cs in
Business
Layer
using System;
using
System.Collections.Generic;
using System.Linq;
using System.Text;
using
System.Threading.Tasks;
namespace BusinessLayer
{
public class Student
{
public int RollNo { get; set; }
public string Name { get; set; }
public string Gender { get; set; }
public string Course { get; set; }
public DateTime Dob { get; set; }
}
}
After that we add new class which name is StudentBusinessLayer.cs in BusinessLayer
using System;
using
System.Collections.Generic;
using System.Linq;
using System.Text;
using
System.Threading.Tasks;
//
using this name space...
using
System.Configuration;
using System.Data;
using
System.Data.SqlClient;
namespace BusinessLayer
{
public class StudentBusinessLayer
{
string strcon = ConfigurationManager.ConnectionStrings["dbcon"].ConnectionString;
//
code for show all table data......
public IEnumerable<Student> StudentDetails
{
get
{
List<Student> st = new List<Student>();
using (SqlConnection con = new SqlConnection(strcon))
{
SqlCommand cmd = new SqlCommand("ShowAll", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader dr =
cmd.ExecuteReader();
while (dr.Read())
{
Student s = new Student();
s.RollNo = Convert.ToInt32(dr["RollNo"]);
s.Name = dr["Name"].ToString();
s.Gender = dr["Gender"].ToString();
s.Course = dr["Course"].ToString();
s.Dob = Convert.ToDateTime(dr["Dob"]);
st.Add(s);
}
return st;
}
}
}
//...end.......................................
//
code for show insert data in table......
public void AddStudent(Student stu)
{
using (SqlConnection con = new SqlConnection(strcon))
{
SqlCommand cmd = new SqlCommand("Ins_Data", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter prollno = new SqlParameter();
prollno.ParameterName = "@RollNo";
prollno.Value = stu.RollNo;
cmd.Parameters.Add(prollno);
SqlParameter pname = new SqlParameter();
pname.ParameterName = "@Name";
pname.Value = stu.Name;
cmd.Parameters.Add(pname);
SqlParameter pgender = new SqlParameter();
pgender.ParameterName = "@Gender";
pgender.Value = stu.Gender;
cmd.Parameters.Add(pgender);
SqlParameter pcourse = new SqlParameter();
pcourse.ParameterName = "@Course";
pcourse.Value = stu.Course;
cmd.Parameters.Add(pcourse);
SqlParameter pdob = new SqlParameter();
pdob.ParameterName = "@Dob";
pdob.Value = stu.Dob;
cmd.Parameters.Add(pdob);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
//...end.......................................
//
code for delete data from table by RollNo……
public void DeleteData(string RollNo)
{
using (SqlConnection con = new SqlConnection(strcon))
{
SqlCommand cmd = new SqlCommand("Delete_Data", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter prollno = new SqlParameter();
prollno.ParameterName = "@RollNo";
prollno.Value = RollNo;
cmd.Parameters.Add(prollno);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
//...end.......................................
//
code for show insert data in table......
public void UpdateStudent(Student stu)
{
using (SqlConnection con = new SqlConnection(strcon))
{
SqlCommand cmd = new SqlCommand("Update_Data", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter prollno = new SqlParameter();
prollno.ParameterName = "@RollNo";
prollno.Value = stu.RollNo;
cmd.Parameters.Add(prollno);
SqlParameter pname = new SqlParameter();
pname.ParameterName = "@Name";
pname.Value = stu.Name;
cmd.Parameters.Add(pname);
SqlParameter pgender = new SqlParameter();
pgender.ParameterName = "@Gender";
pgender.Value = stu.Gender;
cmd.Parameters.Add(pgender);
SqlParameter pcourse = new SqlParameter();
pcourse.ParameterName = "@Course";
pcourse.Value = stu.Course;
cmd.Parameters.Add(pcourse);
SqlParameter pdob = new SqlParameter();
pdob.ParameterName = "@Dob";
pdob.Value = stu.Dob;
cmd.Parameters.Add(pdob);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
//...end.......................................
//
code for search data by RollNo…………..
public Student SelectDataByRollNo(string RollNo)
{
using (SqlConnection con = new SqlConnection(strcon))
{
SqlCommand cmd = new SqlCommand("Search_Data", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter prollno = new SqlParameter();
prollno.ParameterName = "@RollNo";
prollno.Value = RollNo;
cmd.Parameters.Add(prollno);
con.Open();
SqlDataReader dr =
cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
Student s = new Student();
s.RollNo = Convert.ToInt32(dr["RollNo"]);
s.Name = dr["Name"].ToString();
s.Gender = dr["Gender"].ToString();
s.Course = dr["Course"].ToString();
s.Dob = Convert.ToDateTime(dr["Dob"]);
return s;
}
return null;
}
}
//...end.......................................
}
}
Then right click on Business Layer and Build the
program then following given steps
MvcApplicationUsingStoredProcedure
References
=> right
click
=> Add
Reference
=> Projects
=> add BusinessLayer
dll in our project
MvcApplicationUsingStoredProcedure
Next step we will work in MvcApplicationUsingStoredProcedure
Add new controller which name HomeController…………………
using System;
using
System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
//
using name space BusinessLayer
using BusinessLayer;
namespace
MvcApplicationUsingStoredProcedure.Controllers
{
public class HomeController : Controller
{
//
// GET: /Home/
//create instance of
StudentBusinessLayer..................
StudentBusinessLayer stubusiness = new StudentBusinessLayer();
//
this ActionResult for Index View...........
public ActionResult Index()
{
List<Student> stu =
stubusiness.StudentDetails.ToList();
return View(stu);
}
//
this ActionResult for Create View...........
[HttpGet]
public ActionResult Create()
{
return View();
}
[HttpPost]
public ActionResult Create(FormCollection fc)
{
foreach (string item in fc.AllKeys)
{
Response.Write("Key =" + item + " ");
Response.Write(fc[item]);
Response.Write("</br>");
}
Student ss = new Student();
ss.RollNo = Convert.ToInt32(fc["RollNo"]);
ss.Name = fc["Name"];
ss.Gender = fc["Gender"];
ss.Course = fc["Course"];
ss.Dob =Convert.ToDateTime(fc["Dob"]);
stubusiness.AddStudent(ss);
return RedirectToAction("Index");
//return View();
}
//
this ActionResult for Delete View...........
[HttpGet]
public ActionResult Delete(string RollNo)
{
Student result =
stubusiness.SelectDataByRollNo(RollNo);
return View(result);
}
[HttpPost]
public ActionResult Delete(string RollNo, bool ? aa)
{
stubusiness.DeleteData(RollNo);
return RedirectToAction("Index");
}
//
this ActionResult for Edit View...........
[HttpGet]
public ActionResult Edit(string RollNo)
{
Student result =
stubusiness.SelectDataByRollNo(RollNo);
return View(result);
}
[HttpPost]
public ActionResult Edit(string RollNo,string Name,string Gender,string Course,string Dob)
{
Student ss = new Student();
ss.RollNo = Convert.ToInt32(RollNo);
ss.Name = Name;
ss.Gender = Gender;
ss.Course = Course;
ss.Dob = Convert.ToDateTime(Dob);
stubusiness.UpdateStudent(ss);
return RedirectToAction("Index");
}
}
}
How to add View
with strongly type is shown below in the given figure………………………
Source code for
Index.cshtml ……………
@model IEnumerable<BusinessLayer.Student>
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
<div style="font-family:Arial">
<p>
@Html.ActionLink("Create
New",
"Create")
</p>
<table border="1">
<tr>
<th>
@Html.DisplayNameFor(model
=> model.RollNo)
</th>
<th>
@Html.DisplayNameFor(model
=> model.Name)
</th>
<th>
@Html.DisplayNameFor(model
=> model.Gender)
</th>
<th>
@Html.DisplayNameFor(model
=> model.Course)
</th>
<th>
@Html.DisplayNameFor(model
=> model.Dob)
</th>
<th>Action</th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem
=> item.RollNo)
</td>
<td>
@Html.DisplayFor(modelItem
=> item.Name)
</td>
<td>
@Html.DisplayFor(modelItem
=> item.Gender)
</td>
<td>
@Html.DisplayFor(modelItem
=> item.Course)
</td>
<td>
@Html.DisplayFor(modelItem
=> item.Dob)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new {RollNo
=item.RollNo }) |
@Html.ActionLink("Details", "Details", new { /*
id=item.PrimaryKey */
}) |
@Html.ActionLink("Delete", "Delete", new { RollNo
=item.RollNo})
</td>
</tr>
}
</table>
</div>
</body>
</html>
Source code for
Create.cshtml ……………
@model BusinessLayer.Student
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Create</title>
</head>
<body>
@using (Html.BeginForm())
{
@Html.ValidationSummary(true)
<fieldset>
<legend>Student</legend>
<div class="editor-label">
@Html.LabelFor(model
=> model.RollNo)
</div>
<div class="editor-field">
@*
@Html.EditorFor(model => model.RollNo)*@
@Html.TextBox("RollNo")
@Html.ValidationMessageFor(model
=> model.RollNo)
</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.Course)
</div>
<div class="editor-field">
@*
@Html.EditorFor(model => model.Course)*@
@Html.DropDownList("Course", new List<SelectListItem>
{
new SelectListItem{Text="Mca", Value="Mca"},
new SelectListItem{Text="Bca", Value="Bca"}
},"Select Course")
@Html.ValidationMessageFor(model
=> model.Course)
</div>
<div class="editor-label">
@Html.LabelFor(model
=> model.Dob)
</div>
<div class="editor-field">
@Html.EditorFor(model
=> model.Dob)
@Html.ValidationMessageFor(model
=> model.Dob)
</div>
<p>
<input type="submit" value="Create" />
</p>
</fieldset>
}
<div>
@Html.ActionLink("Back to
List",
"Index")
</div>
</body>
</html>
Source code for Delete.cshtml
……………
@model BusinessLayer.Student
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Delete</title>
</head>
<body>
<h3>Are you sure you
want to delete this?</h3>
<fieldset>
<legend>Student</legend>
<div class="display-label">
@Html.DisplayNameFor(model
=> model.RollNo)
</div>
<div class="display-field">
@Html.DisplayFor(model
=> model.RollNo)
</div>
<div class="display-label">
@Html.DisplayNameFor(model
=> model.Name)
</div>
<div class="display-field">
@Html.DisplayFor(model
=> model.Name)
</div>
<div class="display-label">
@Html.DisplayNameFor(model
=> model.Gender)
</div>
<div class="display-field">
@Html.DisplayFor(model
=> model.Gender)
</div>
<div class="display-label">
@Html.DisplayNameFor(model
=> model.Course)
</div>
<div class="display-field">
@Html.DisplayFor(model
=> model.Course)
</div>
<div class="display-label">
@Html.DisplayNameFor(model
=> model.Dob)
</div>
<div class="display-field">
@Html.DisplayFor(model
=> model.Dob)
</div>
</fieldset>
@using (Html.BeginForm())
{
<p>
<input type="submit" value="Delete" /> |
@Html.ActionLink("Back to
List",
"Index")
</p>
}
</body>
</html>
Source code for Edit.cshtml
……………
@model BusinessLayer.Student
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Edit</title>
</head>
<body>
@using (Html.BeginForm())
{
@Html.ValidationSummary(true)
<fieldset>
<legend>Student</legend>
<div class="editor-label">
@Html.LabelFor(model
=> model.RollNo)
</div>
<div class="editor-field">
@Html.EditorFor(model
=> model.RollNo)
@Html.ValidationMessageFor(model
=> model.RollNo)
</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.Course)
</div>
<div class="editor-field">
@Html.EditorFor(model
=> model.Course)
@Html.ValidationMessageFor(model
=> model.Course)
</div>
<div class="editor-label">
@Html.LabelFor(model
=> model.Dob)
</div>
<div class="editor-field">
@Html.EditorFor(model
=> model.Dob)
@Html.ValidationMessageFor(model
=> model.Dob)
</div>
<p>
<input type="submit" value="Update
Data"
/>
</p>
</fieldset>
}
<div>
@Html.ActionLink("Back to
List",
"Index")
</div>
</body>
</html>
Result
0 comments:
Post a Comment