Monday 4 May 2015

How to use Business object as model using Store Procedure,Ado.Net in MVC

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