I am trying to implement my project with ASP.NET MVC without entity framework. I am getting my values to index fine, however my create and edit functions do not work. I clicked on create or edit and it ends up in my catch block with what appears to be a null reference exception.
I have set up my stored procedures as follows:
CREATE PROCEDURE [Project].[AddStudent]
(
@FirstName NVARCHAR(32),
@LastName NVARCHAR(32),
@Email NVARCHAR(32)
)
AS
INSERT INTO Project.Student
VALUES
( @FirstName,
@LastName,
@Email
)
CREATE PROCEDURE [Project].[UpdateStudent]
(
@StudentId INT,
@FirstName NVARCHAR(32),
@LastName NVARCHAR(32),
@Email NVARCHAR(32)
)
AS
UPDATE Student
SET FirstName = @FirstName,
LastName = @LastName,
Email = @Email
WHERE StudentId = @StudentId
Which I believe it should work on their own. The StudentId value in the first procedure should get filled in since it is as Identity.
I then have created a db handle class:
public class StudentDBHandle
{
private SqlConnection con;
private void Connection()
{
string constring = ConfigurationManager.ConnectionStrings["studentconn"].ToString();
con = new SqlConnection(constring);
}
// **************** ADD NEW STUDENT *********************
public bool AddStudent(Student smodel)
{
Connection();
SqlCommand cmd = new SqlCommand("AddStudent", con)
{
CommandType = CommandType.StoredProcedure
};
cmd.Parameters.AddWithValue("@FirstName", smodel.FirstName);
cmd.Parameters.AddWithValue("@LastName", smodel.LastName);
cmd.Parameters.AddWithValue("@Email", smodel.Email);
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
if (i >= 1)
return true;
else
return false;
}
// ********** VIEW STUDENT DETAILS ********************
public List<Student> GetStudent()
{
Connection();
List<Student> studentlist = new List<Student>();
SqlCommand cmd = new SqlCommand("Project.GetStudentDetails", con)
{
CommandType = CommandType.StoredProcedure
};
SqlDataAdapter sd = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
con.Open();
sd.Fill(dt);
con.Close();
foreach (DataRow dr in dt.Rows)
{
studentlist.Add(
new Student
{
StudentId = Convert.ToInt32(dr["StudentId"]),
FirstName = Convert.ToString(dr["FirstName"]),
LastName = Convert.ToString(dr["LastName"]),
Email = Convert.ToString(dr["Email"])
});
}
return studentlist;
}
// ***************** UPDATE STUDENT DETAILS *********************
public bool UpdateDetails(Student smodel)
{
Connection();
SqlCommand cmd = new SqlCommand("UpdateStudent", con)
{
CommandType = CommandType.StoredProcedure
};
cmd.Parameters.AddWithValue("@StudentId", smodel.StudentId);
cmd.Parameters.AddWithValue("@FirstName", smodel.FirstName);
cmd.Parameters.AddWithValue("@LastName", smodel.LastName);
cmd.Parameters.AddWithValue("@Email", smodel.Email);
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
if (i >= 1)
return true;
else
return false;
}
Which seems to be where the problem occurs, on int i = cmd.ExecuteNonQuery();
and then I have my controller as follows:
// GET: Student/Create
public ActionResult Create()
{
return View();
}
// POST: Student/Create
[HttpPost]
public ActionResult Create(Student smodel)
{
try
{
if (ModelState.IsValid)
{
StudentDBHandle sdb = new StudentDBHandle();
if (sdb.AddStudent(smodel))
{
ViewBag.Message = "Student Added Successfully";
ModelState.Clear();
}
}
return View();
}
catch
{
return View();
}
}
// GET: Student/Edit/5
public ActionResult Edit(int id)
{
StudentDBHandle sdb = new StudentDBHandle();
return View(sdb.GetStudent().Find(smodel => smodel.StudentId == id));
}
// POST: Student/Edit/5
[HttpPost]
public ActionResult Edit(int id, Student smodel)
{
try
{
StudentDBHandle sdb = new StudentDBHandle();
sdb.UpdateDetails(smodel);
return RedirectToAction("Index");
}
catch
{
return View();
}
}
I am new to this, especially without entity framework. So I am unsure where I am going wrong. Any help figuring out or providing me with best practice guidance would be much appreciated.
