MVC CRUD Example with Visual Studio and SQL Server

CrudController .cs 

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6. using CurdMvc.Models;  
  7. using System.Data;  
  8. using System.Configuration;  
  9. using System.Data.SqlClient;  
  10. namespace CurdMvc.Controllers  
  11. {  
  12.     public class CurdController: Controller  
  13.     {  
  14.         //  
  15.         // GET: /Curd/  
  16.         SqlConnection con = new SqlConnection("Data Source=BITS-PC;Initial Catalog=TestDB;Integrated Security=True");  
  17.         public ActionResult Index()  
  18.             {  
  19.                 List < CurdModel > lstRecord = new List < CurdModel > ();  
  20.                 SqlDataReader dr = null;  
  21.                 SqlCommand command = new SqlCommand("GetAllRecordSP", con);  
  22.                 command.CommandType = CommandType.StoredProcedure;  
  23.                 con.Open();  
  24.                 dr = command.ExecuteReader();  
  25.                 while (dr.Read())  
  26.                 {  
  27.                     CurdModel mdl = new CurdModel();  
  28.                     mdl.id = Convert.ToInt32(dr["Id"]);  
  29.                     mdl.email = dr["Email"].ToString();  
  30.                     mdl.name = dr["Name"].ToString();  
  31.                     lstRecord.Add(mdl);  
  32.                 }  
  33.                 con.Close();  
  34.                 return View(lstRecord);  
  35.             }  
  36.             [HttpGet]  
  37.         public ActionResult Add(int ? id)  
  38.             {  
  39.                 CurdModel mdl = new CurdModel();  
  40.                 if (id != null)  
  41.                 {  
  42.   
  43.                     SqlCommand cmd = new SqlCommand("GetRecordByIdSP", con);  
  44.                     cmd.CommandType = CommandType.StoredProcedure;  
  45.                     cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;  
  46.                     SqlDataReader dr = null;  
  47.                     con.Open();  
  48.                     dr = cmd.ExecuteReader();  
  49.   
  50.                     DataTable dt = new DataTable();  
  51.                     dt.Load(dr);  
  52.   
  53.                     mdl.id = Convert.ToInt32(dt.Rows[0][0].ToString());  
  54.                     mdl.name = dt.Rows[0][1].ToString();  
  55.                     mdl.email = dt.Rows[0][2].ToString();  
  56.                     con.Close();  
  57.                     return View(mdl);  
  58.                 }  
  59.                 return View();  
  60.             }  
  61.             [HttpPost]  
  62.         public ActionResult add(CurdModel model)  
  63.         {  
  64.             if (model.id > 0)  
  65.             {  
  66.                 SqlCommand command = new SqlCommand("UpdateRecordByIdSP", con);  
  67.                 command.CommandType = CommandType.StoredProcedure;  
  68.                 // add parameters     
  69.                 command.Parameters.Add("@Name", SqlDbType.VarChar).Value = model.name;  
  70.                 command.Parameters.Add("@Email", SqlDbType.VarChar).Value = model.email;  
  71.                 command.Parameters.Add("@Id", SqlDbType.Int).Value = model.id;  
  72.                 con.Open();  
  73.                 int iRetVal = command.ExecuteNonQuery();  
  74.   
  75.   
  76.             }  
  77.             else  
  78.             {  
  79.                 SqlCommand command = new SqlCommand("AddNewRecordSP", con);  
  80.                 command.CommandType = CommandType.StoredProcedure;  
  81.                 // add parameters     
  82.                 command.Parameters.Add("@Name", SqlDbType.VarChar).Value = model.name;  
  83.                 command.Parameters.Add("@Email", SqlDbType.VarChar).Value = model.email;  
  84.                 command.Parameters.Add("@Id", SqlDbType.Int).Direction = ParameterDirection.Output;  
  85.                 con.Open();  
  86.                 int iRetVal = command.ExecuteNonQuery();  
  87.                 con.Close();  
  88.             }  
  89.             return RedirectToAction("Index", "curd");  
  90.         }  
  91.   
  92.         public ActionResult Delete(int id)  
  93.         {  
  94.             SqlCommand command = new SqlCommand("DeleteRecordByIdSP", con);  
  95.             command.CommandType = CommandType.StoredProcedure;  
  96.             // add parameters    
  97.   
  98.             command.Parameters.Add("@Id", SqlDbType.Int).Value = id;  
  99.             con.Open();  
  100.             command.ExecuteNonQuery();  
  101.             con.Close();  
  102.   
  103.             return RedirectToAction("Index", "curd");  
  104.         }  
  105.         public ActionResult Details(int id)  
  106.         {  
  107.             CurdModel mdl = new CurdModel();  
  108.             SqlCommand cmd = new SqlCommand("GetRecordByIdSP", con);  
  109.             cmd.CommandType = CommandType.StoredProcedure;  
  110.             cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;  
  111.             SqlDataReader dr = null;  
  112.             con.Open();  
  113.             dr = cmd.ExecuteReader();  
  114.   
  115.             DataTable dt = new DataTable();  
  116.             dt.Load(dr);  
  117.   
  118.             mdl.id = Convert.ToInt32(dt.Rows[0][0].ToString());  
  119.             mdl.name = dt.Rows[0][1].ToString();  
  120.             mdl.email = dt.Rows[0][2].ToString();  
  121.   
  122.             con.Close();  
  123.             return View(mdl);  
  124.   
  125.         }  
  126.     }  
  127. }