DAL
using MyMVCAccount.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
namespace MyMVCAccount
{
public class DAL
{
public string InsertData(User user)
{
SqlConnection con = null;
string result = "";
try
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());
SqlCommand cmd = new SqlCommand("Usp_InsertUpdateDelete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", 0);
//
i will pass zero to MobileID beacause its Primary .
cmd.Parameters.AddWithValue("@Name",
user.Name);
cmd.Parameters.AddWithValue("@LoginId",
user.LoginId);
cmd.Parameters.AddWithValue("@Password",
user.Password);
cmd.Parameters.AddWithValue("@Query", 1);
con.Open();
result =
cmd.ExecuteScalar().ToString();
return result;
}
catch
{
return result = "";
}
finally
{
con.Close();
}
}
public IList<User> SelectAllData()
{
IList<User> users = new List<User>();
SqlConnection con = null;
//string
result = "";
DataSet ds = null;
try
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());
SqlCommand cmd = new SqlCommand("Usp_InsertUpdateDelete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ID", 0);
cmd.Parameters.AddWithValue("@Name", null);
cmd.Parameters.AddWithValue("@LoginId", null);
cmd.Parameters.AddWithValue("@Password", null);
cmd.Parameters.AddWithValue("@Query", 4);
con.Open();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
ds = new DataSet(); da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i <
ds.Tables[0].Rows.Count; i++)
{
User user = new User()
{
Id = Convert.ToInt32(ds.Tables[0].Rows[i]["ID"].ToString()),
Name =
ds.Tables[0].Rows[i]["Name"].ToString(),
LoginId =
ds.Tables[0].Rows[i]["LoginId"].ToString(),
Password = ds.Tables[0].Rows[i]["Password"].ToString()
};
users.Add(user);
}
}
return users;
}
catch
{
return users;
}
finally
{
con.Close();
}
}
public User SelectAllDatabyID(int Id)
{
User user=null;
SqlConnection con = null;
string result = "";
DataSet ds = null;
try
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());
SqlCommand cmd = new SqlCommand("Usp_InsertUpdateDelete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", Id); // i will pass zero to MobileID beacause its Primary .
cmd.Parameters.AddWithValue("@Name", null);
cmd.Parameters.AddWithValue("@LoginId", null);
cmd.Parameters.AddWithValue("@Password", 0);
cmd.Parameters.AddWithValue("@Query", 5);
con.Open();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
user = new User()
{
Id = Convert.ToInt32(ds.Tables[0].Rows[0]["ID"].ToString()),
Name =
ds.Tables[0].Rows[0]["Name"].ToString(),
LoginId =
ds.Tables[0].Rows[0]["LoginId"].ToString(),
Password = ds.Tables[0].Rows[0]["Password"].ToString()
};
}
return user;
}
catch
{
return user;
}
finally
{
con.Close();
}
}
public string UpdateData(User user)
{
SqlConnection con = null;
string result = "";
try
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());
SqlCommand cmd = new SqlCommand("Usp_InsertUpdateDelete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id",
user.Id);
cmd.Parameters.AddWithValue("@Name",
user.Name);
cmd.Parameters.AddWithValue("@LoginId",
user.LoginId);
cmd.Parameters.AddWithValue("@Password",
user.Password);
cmd.Parameters.AddWithValue("@Query", 2);
con.Open();
result =
cmd.ExecuteScalar().ToString();
return result;
}
catch
{
return result = "";
}
finally
{
con.Close();
}
}
}
}
Controller
using MyMVCAccount.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MyMVCAccount;
namespace MyMVCAccount.Controllers
{
public class UserController : Controller
{
//
// GET:
/User/
public ActionResult Index()
{
DAL objDAL = new DAL(); //calling class DBdata
User user = new User();
user.users = objDAL.SelectAllData();
return View(user);
}
//
// GET:
/User/Details/5
public ActionResult Details(int id)
{
return View();
}
//
// GET:
/User/Create
public ActionResult Create()
{
return View();
}
//
// POST:
/User/Create
[HttpPost]
public ActionResult Create(User user)
{
try
{
//
TODO: Add insert logic here
if (ModelState.IsValid) //checking
model is valid or not
{
DAL objDAL = new DAL(); //calling class DBdata
string result =
objDAL.InsertData(user); // passing Value to DBClass
from model
ViewData["result"] =
result;
ModelState.Clear(); //clearing model
return RedirectToAction("Index");
}
else
{
ModelState.AddModelError("", "Error in saving data");
return View();
}
}
catch
{
return View();
}
}
//
// GET:
/User/Edit/5
public ActionResult Edit(int id)
{
DAL obDAL = new DAL(); //calling class DBdata
User user = obDAL.SelectAllDatabyID(id);
User userDtl = new User()
{
Id = user.Id,
Name=user.Name,
LoginId=user.LoginId,
Password=user.Password
};
return View(userDtl);
}
//
// POST:
/User/Edit/5
[HttpPost]
public ActionResult Edit(User user)
{
try
{
//
TODO: Add update logic here
DAL objDAL = new DAL(); //calling class DBdata
string result = objDAL.UpdateData(user); // passing Value to DBClass from model
ViewData["resultUpdate"]
= result; // for dislaying message after updating
data.
return RedirectToAction("Index");
}
catch
{
return View();
}
}
//
// GET:
/User/Delete/5
public ActionResult Delete(int id)
{
return View();
}
//
// POST:
/User/Delete/5
[HttpPost]
public ActionResult Delete(int id, FormCollection collection)
{
try
{
//
TODO: Add delete logic here
return RedirectToAction("Index");
}
catch
{
return View();
}
}
}
}
Model
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Linq;
using System.Web;
namespace MyMVCAccount.Models
{
public class User
{
public int Id { get; set; }
[Required(ErrorMessage = "*")]
public string Name { get; set; }
[Required(ErrorMessage = "*")]
public string LoginId { get; set; }
[DataType(DataType.Password)]
[Required(ErrorMessage = "*")]
public string Password { get; set; }
public IList<User> users { get; set; }
}
}
View
Create
@model
MyMVCAccount.Models.User
@{
ViewBag.Title = "Create";
}
<h2>Create</h2>
<link href="~/Content/Site.css" rel="stylesheet" />
<script src="~/Scripts/jquery-1.8.2.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
<table>
<tr>
<td>
@Html.ActionLink("Show
Users", "Index")
</td>
</tr>
</table>
@using (Html.BeginForm())
{
<table style="margin-left:100px" width="100%">
<tr>
<td>
@Html.LabelFor(a => @Model.Name)
</td>
</tr>
<tr>
<td>
@Html.TextBoxFor(a => @Model.Name)
@Html.ValidationMessageFor(a => @Model.Name)
</td>
</tr>
<tr>
<td>
@Html.LabelFor(a => @Model.LoginId)
</td>
</tr>
<tr>
<td>
@Html.TextBoxFor(a => @Model.LoginId)
@Html.ValidationMessageFor(a => @Model.LoginId)
</td>
</tr>
<tr>
<td>
@Html.LabelFor(a => @Model.Password)
</td>
</tr>
<tr>
<td>
@Html.EditorFor(a => @Model.Password, new { @autocomplete = "off" })
@Html.ValidationMessageFor(a => @Model.Password)
</td>
</tr>
<tr>
<td colspan="2">
<input id="Submit1" type="submit" value="submit" />
</td>
</tr>
</table>
}
@{
@*if (ViewData["result"] != ""
&& ViewData["result"] != null)
{
ViewData["result"] =
null;
<script
type="text/javascript" language="javascript">
alert("Data saved
Successfully");
</script>
}*@
}
Edit
@model
MyMVCAccount.Models.User
@{
ViewBag.Title = "Edit";
}
<h2>Edit</h2>
@using (Html.BeginForm())
{
<table width="100%">
<tr>
<td colspan="2">
@Html.HiddenFor(a => Model.Id)
</td>
</tr>
<tr>
<td>
@Html.LabelFor(a => Model.Name)
</td>
</tr>
<tr>
<td>
@Html.TextBoxFor(a => Model.Name)
@Html.ValidationMessageFor(a => Model.Name)
</td>
</tr>
<tr>
<td>
@Html.LabelFor(a => Model.LoginId)
</td>
</tr>
<tr>
<td>
@Html.TextBoxFor(a => Model.LoginId)
@Html.ValidationMessageFor(a => Model.LoginId)
</td>
</tr>
<tr>
<td>
@Html.LabelFor(a => Model.Password)
</td>
</tr>
<tr>
<td>
@Html.TextBoxFor(a => Model.Password)
@Html.ValidationMessageFor(a => Model.Password)
</td>
</tr>
<tr>
<td colspan="2">
<input id="Submit1" type="submit" value="Update" />
</td>
</tr>
</table>
}
Index
@model
MyMVCAccount.Models.User
@{
Layout = null;
}
<h2>ShowAllMobileDetails</h2>
<style>
table {
border-collapse: collapse;
}
table, th, td {
border: 1px solid black;
}
</style>
<tr>
<td>
@Html.ActionLink("Add
New User", "Create")
</td>
</tr>
<br />
<br />
<table width="100%">
<tr>
<td>
Name
</td>
<td>
LoginId
</td>
<td>
Password
</td>
<td>
EDIT
</td>
<td>
DELETE
</td>
</tr>
@foreach (var item in Model.users)
{
<tr>
<td>
@Html.EditorFor(i=>item.Name)
</td>
<td>
@Html.EditorFor(i => item.LoginId)
</td>
<td>
@Html.EditorFor(i => item.Password)
</td>
<td>
@Html.ActionLink("EDIT", "Edit", new { id = item.Id })
</td>
<td>
@Html.ActionLink("Delete", "DELETEMOBILEDATA", new { id = item.Id })
</td>
</tr>
}
@*@for (int i = 0; i < Model.users.Count; i++)
{
<tr>
<td>
@Model.users[i].Name
</td>
<td>
@Model.users[i].LoginId
</td>
<td>
@Model.users[i].Password
</td>
<td>
@Html.ActionLink("EDIT",
"Edit", new { id = Model.users[i].Id })
</td>
<td>
@Html.ActionLink("Delete", "DELETEMOBILEDATA", new {
id = Model.users[0].Id })
</td>
</tr>
}*@
</table>
