September 6, 2016 19:44 by
PeterToday, let me show you a CRUD example with ASP.NET MVC and SQL Server. This sample demonstrates how to use the CRUD (Create, Read, Update delete) record in MVC in Visual Studio. We are using SQL server database for this demo.
CrudController .cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using CurdMvc.Models;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
namespace CurdMvc.Controllers
{
public class CurdController: Controller
{
//
// GET: /Curd/
SqlConnection con = new SqlConnection("Data Source=BITS-PC;Initial Catalog=TestDB;Integrated Security=True");
public ActionResult Index()
{
List < CurdModel > lstRecord = new List < CurdModel > ();
SqlDataReader dr = null;
SqlCommand command = new SqlCommand("GetAllRecordSP", con);
command.CommandType = CommandType.StoredProcedure;
con.Open();
dr = command.ExecuteReader();
while (dr.Read())
{
CurdModel mdl = new CurdModel();
mdl.id = Convert.ToInt32(dr["Id"]);
mdl.email = dr["Email"].ToString();
mdl.name = dr["Name"].ToString();
lstRecord.Add(mdl);
}
con.Close();
return View(lstRecord);
}
[HttpGet]
public ActionResult Add(int ? id)
{
CurdModel mdl = new CurdModel();
if (id != null)
{
SqlCommand cmd = new SqlCommand("GetRecordByIdSP", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;
SqlDataReader dr = null;
con.Open();
dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
mdl.id = Convert.ToInt32(dt.Rows[0][0].ToString());
mdl.name = dt.Rows[0][1].ToString();
mdl.email = dt.Rows[0][2].ToString();
con.Close();
return View(mdl);
}
return View();
}
[HttpPost]
public ActionResult add(CurdModel model)
{
if (model.id > 0)
{
SqlCommand command = new SqlCommand("UpdateRecordByIdSP", con);
command.CommandType = CommandType.StoredProcedure;
// add parameters
command.Parameters.Add("@Name", SqlDbType.VarChar).Value = model.name;
command.Parameters.Add("@Email", SqlDbType.VarChar).Value = model.email;
command.Parameters.Add("@Id", SqlDbType.Int).Value = model.id;
con.Open();
int iRetVal = command.ExecuteNonQuery();
}
else
{
SqlCommand command = new SqlCommand("AddNewRecordSP", con);
command.CommandType = CommandType.StoredProcedure;
// add parameters
command.Parameters.Add("@Name", SqlDbType.VarChar).Value = model.name;
command.Parameters.Add("@Email", SqlDbType.VarChar).Value = model.email;
command.Parameters.Add("@Id", SqlDbType.Int).Direction = ParameterDirection.Output;
con.Open();
int iRetVal = command.ExecuteNonQuery();
con.Close();
}
return RedirectToAction("Index", "curd");
}
public ActionResult Delete(int id)
{
SqlCommand command = new SqlCommand("DeleteRecordByIdSP", con);
command.CommandType = CommandType.StoredProcedure;
// add parameters
command.Parameters.Add("@Id", SqlDbType.Int).Value = id;
con.Open();
command.ExecuteNonQuery();
con.Close();
return RedirectToAction("Index", "curd");
}
public ActionResult Details(int id)
{
CurdModel mdl = new CurdModel();
SqlCommand cmd = new SqlCommand("GetRecordByIdSP", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;
SqlDataReader dr = null;
con.Open();
dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
mdl.id = Convert.ToInt32(dt.Rows[0][0].ToString());
mdl.name = dt.Rows[0][1].ToString();
mdl.email = dt.Rows[0][2].ToString();
con.Close();
return View(mdl);
}
}
}
HostForLIFE.eu ASP.NET MVC 6 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.
