CRUD operation in MVC with out Entity framework

Question

Hi I am beginner in MVC , I am trying to achieve CRUD operation in MVC using SQL with out Entity Framework or LINQ-SQL class. I done well with insert and getting the details of the selected table but when coming to Edit, details and delete I am confused how to perform so can some one help me out.

This is my code

Create a new Employee

public ActionResult Index(Test test)
    {
        try
        {
            if (ModelState.IsValid)
            {
                test.insert(test);
                test.Name = "";
                test.LastName = "";
            }
        }
        catch (Exception)
        {

        }
        return View(test);
    }

Display all results

 public ActionResult display()
    {
        Test tst = new Test();
        return View(tst.getList());
    }

This is my code in class file

public class Test
{
    public int EmpID { get; set; }
    [Required]
    [DisplayName("Name")]
    public string Name { get; set; }

    [Required]
    [DisplayName("LastName")]
    public string LastName { get; set; }

    string strConnection = ConfigurationManager.ConnectionStrings["SomeDataBase"].ConnectionString.ToString();

    public void insert(Test test)
    {


        using (SqlConnection con = new SqlConnection(strConnection))
        {
            SqlCommand cmd = new SqlCommand("insert into Employee values('" + test.Name + "','" + test.LastName + "')", con);
            con.Open();
            cmd.ExecuteNonQuery();
        }
    }

    public List<Test> getList()
    {
        List<Test> _lstPoducts = new List<Test>();
        SqlConnection con = new SqlConnection(strConnection);
        SqlCommand cmd = new SqlCommand("select * from Employee", con);
        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            Test _Products = new Test();
            _Products.EmpID = Convert.ToInt16(dr["EmpID"].ToString());
            _Products.Name = dr["FirstName"].ToString();
            _Products.LastName = dr["LastName"].ToString();

            _lstPoducts.Add(_Products);
        }

        return _lstPoducts;
    }

    public List<Test> edit(int id)
    {
        List<Test> _lstPoducts = new List<Test>();
        SqlConnection con = new SqlConnection(strConnection);
        SqlCommand cmd = new SqlCommand("select * from Employee where EmpID='" + id + "'", con);
        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            Test _Products = new Test();
            _Products.EmpID = Convert.ToInt16(dr["EmpID"].ToString());
            _Products.Name = dr["FirstName"].ToString();
            _Products.LastName = dr["LastName"].ToString();

            _lstPoducts.Add(_Products);
        }
        return _lstPoducts;
    }

}

Can some one help me how to do the remaining operations like Details, Edit update and Delete.

Answer 1

for Details you can use

public Test details(int id)
{
    Test  _products = new Test();
    SqlConnection con = new SqlConnection(strConnection);
    SqlCommand cmd = new SqlCommand("select * from Employee where EmpID='" + id + "'", con);
    con.Open();

    try{
        SqlDataReader dr = cmd.ExecuteReader();
        _products.EmpID = Convert.ToInt16(dr["EmpID"].ToString());
        _products.Name = dr["FirstName"].ToString();
        _products.LastName = dr["LastName"].ToString();

    }
    catch(exception ex)
    {
         /* Do Some Stuff */
    }
    return _products;
}

And from your Controller

public ActionResult Details(int id)
{
    Test tst = new Test();
    return tst.Details(id);
}

for Edit you can use

public Bool Edit(test tst)
{
    SqlConnection con = new SqlConnection(strConnection);
    SqlCommand cmd = new SqlCommand("UPDATE TABLE Employee SET FirstName='"+tst.Name+"',Lastname='"+tst.LastName+"' where EmpID='" + tst.EmpId + "'", con);
    con.Open();
    try{
         SqlDataReader dr = cmd.ExecuteReader();
         return true;
    }  
    catch(exception ex)
    {
              /* Do Some Stuff */
    }

}

And from your Controller

public ActionResult Edit(test tsts)
{
    Test tst = new Test();
    return tst.Edit(tsts);
}

and proceed similarly for the rest of the cases