Model classes to perform CRUD Operations on Dept table using ADO.NET

Model classes to perform CRUD Operations on Dept table using ADO.NET

File1: /Models/Dept.cs

File1: /Models/Dept.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace MvcApplication1.Models
{
 public class Dept
 {
 public int Deptno { get; set; }
 public string Dname { get; set; }
 public string Loc { get; set; }
 }
}

FileName: /Models/DataContext.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace MvcApplication1.Models
{
 public class Dept
 {
 public int Deptno { get; set; }
 public string Dname { get; set; }
 public string Loc { get; set; }
 }
}

FileName: /Models/DataContext.cs
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
namespace MvcApplication1.Models
{
 public class DataContext
 {
 public List GetDepts()
 {
 List deptList = new List();
string connStr = "Server=narasimha-pc;
Database=TestDb; Integrated Security=true;";
 string cmdText = "SELECT * FROM DEPT";
SqlDataAdapter da = new SqlDataAdapter(cmdText,
connStr);
 DataTable dt = new DataTable();
 da.Fill(dt);
 foreach (DataRow item in dt.Rows)
 {
 Dept obj = new Dept();
 obj.Deptno = (int)item["Deptno"];
 obj.Dname = (string)item["Dname"];
 obj.Loc = (string)item["Loc"];
 deptList.Add(obj);
 }
 return deptList;
 }
public Dept GetDept(int n)
 {
 Dept obj = new Dept();
 string cmdText = "SELECT * FROM DEPT WHERE
DEPTNO=" + n;
string connStr = "Server=narasimha-pc;
Database=TestDb; Integrated Security=true;";
 SqlConnection conn = new SqlConnection(connStr);
 SqlCommand cmd = new SqlCommand(cmdText, conn);
 conn.Open();
 SqlDataReader dr = cmd.ExecuteReader();
 if (dr.HasRows == true)
 {
 dr.Read();
 obj.Deptno = (int)dr["DEPTNO"];
 obj.Dname = (string)dr["DNAME"];
 obj.Loc = (string)dr["LOC"];
 }
 dr.Close();
 conn.Close();
 return obj;
}
public void AddDept(Dept obj)
 {
 string cmdText = string.Format("INSERT INTO
DEPT VALUES({0}, '{1}', '{2}')", obj.Deptno, obj.Dname,
obj.Loc);
string connStr = "Server=narasimha-pc;
Database=TestDb; Integrated Security=true;";
SqlConnection conn = new SqlConnection(connStr);
 SqlCommand cmd = new SqlCommand(cmdText, conn);
 conn.Open();
 cmd.ExecuteNonQuery();
 conn.Close();
 }
 public void EditDept(Dept obj)
 {
 string cmdText = string.Format(" UPDATE DEPT
SET DNAME='{0}' , LOC='{1}' WHERE DEPTNO={2}",
obj.Dname, obj.Loc, obj.Deptno);
string connStr = "Server=narasimha-pc;
Database=TestDb; Integrated Security=true;";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(cmdText, conn);
 conn.Open();
 cmd.ExecuteNonQuery();
 conn.Close();
 }
public void DeleteDept(int n)
 {
 string cmdText = string.Format(" DELETE FROM
DEPT WHERE DEPTNO={0}", n);
string connStr = "Server=narasimha-pc;
Database=TestDb; Integrated Security=true;";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(cmdText, conn);
 conn.Open();
 cmd.ExecuteNonQuery();
 conn.Close();
 }
 }
}
Prepare similar type of classes for Emp and Student Tables.
Prepare the Emp and Student tables in Database
o Emp : Empno, Ename, Job, Sal, Deptno
o Student : StudentId, Sname, Course, Email, ContactNo
Develop MVC Applications to implement the CURD operations on these
tables.

Author : Sandeep Kumar Jha

Share this