In this article I will explain with an example, how to update Database Table using ListBox in ASP.Net MVC.
Database
I have made use of the following table Hobbies with the schema as follow.
![ASP.Net MVC: Update Database Table using ListBox]()
I have already inserted few records in the table.
![ASP.Net MVC: Update Database Table using ListBox]()
Note: You can download the database table SQL by clicking the download link below.
Model
The Model class consists of following properties.
public class HobbyModel
{
public List<SelectListItem> Hobbies { get; set; }
public List<string> HobbyIds { get; set; }
}
Namespaces
You will need to import the following namespaces.
using System.Data.SqlClient;
using System.Configuration;
Controller
The Controller consist of following Action methods.
Action method for handling GET operation
Inside this Action method, an object of HobbyModel class is created and its Hobbies property is set with the Generic List collection i.e. records of the Hobbies Table which is returned from the PopulateHobbies method (explained later).
PopulateHobbies
Inside this method, fist a Generic List collection of SelectListItem class object is created.
Then, the connection string is read from Web.Config file.
After that, a connection to the database is established using the SqlConnection class and records are fetched from the Hobbies Table using ExecuteReader method of the SqlCommand class.
The fetched records are added to the Generic List collection of SelectListItem class created earlier which is finally returned.
Action method for handling POST operation
This Action method gets called when Submit Button is clicked which accepts HobbyModel class object as a parameter.
Inside this Action method, first the Hobbies property of HobbyModel class is set with the records returned from the PopulateHobbies method (explained earlier).
Then, a check is performed if the HobbyIds property is NULL or not, if not NULL then a FOR EACH loop is executed over the Generic List collection of Hobbies.
Inside the loop, the UpdateHobby method (explained later) is called which accepts the value i.e. HobbyId and the status of that Hobby whether it is selected or not.
Note: The status is determined based on the presence of that HobbyId in the Generic List collection of string created earlier in the HobbyModel class.
Finally, the success message is set into a
ViewBag object and the object of
HobbyModel class sis returned to the View.
UpdateHobby
Inside this method, a connection to the database is established using the SqlConnection class.
Then, the HobbyId and IsSelected value i.e. TRUE or FALSE are passed as parameter to the SqlCommand class object and the records are updated using ExecuteNonQuery method.
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
HobbyModel hobby = new HobbyModel();
hobby.Hobbies = this.PopulateHobbies();
return View(hobby);
}
[HttpPost]
public ActionResult Index(HobbyModel hobby)
{
hobby.Hobbies = this.PopulateHobbies();
if (hobby.HobbyIds != null)
{
foreach (SelectListItem item in hobby.Hobbies)
{
this.UpdateHobby(int.Parse(item.Value), hobby.HobbyIds.Contains(item.Value));
}
ViewBag.Message = "Records successfuly updated!";
}
return View(hobby);
}
private void UpdateHobby(int hobbyId, bool isSelected)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string sql = "UPDATE Hobbies SET IsSelected = @IsSelected WHERE HobbyId = @HobbyId";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@HobbyId", hobbyId);
cmd.Parameters.AddWithValue("@IsSelected", isSelected);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
private List<SelectListItem> PopulateHobbies()
{
List<SelectListItem> hobbies = new List<SelectListItem>();
string sql = "SELECT HobbyId, Hobby FROM Hobbies";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
hobbies.Add(new SelectListItem
{
Text = sdr["Hobby"].ToString(),
Value = sdr["HobbyId"].ToString()
});
}
}
con.Close();
}
}
return hobbies;
}
}
View
HTML Markup
Inside the View, in the very first line the HobbyModel class is declared as Model for the View.
The View consists of an HTML Form which has been created using the Html.BeginForm method with the following parameters.
ActionName – Name of the Action. In this case the name is Index.
ControllerName – Name of the Controller. In this case the name is Home.
FormMethod – It specifies the Form Method i.e. GET or POST. In this case it will be set to POST.
The View consists of a ListBox created using Html.ListBoxFor Html Helper method.
Html.ListBoxFor
The ListBox created using Html.ListBoxFor is set with following properties:
model property – The name of the property is set using Lambda expression.
The View also consists of a Submit Button.
Submitting the Form
When the
Submit Button is clicked then, the
ViewBag object is checked for NULL and if it is not NULL then, the value of the
ViewBag object is displayed using
JavaScript Alert Message Box.
@model ListBox_Update_DB_MVC.Models.HobbyModel
@{
Layout = null;
}
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
@using (Html.BeginForm("Index", "Home", FormMethod.Post))
{
<b>Hobbies:</b>
<br /><br />
@Html.ListBoxFor(m => m.HobbyIds, Model.Hobbies)
<br /><br />
<input type="submit" value="Save" />
}
@if (ViewBag.Message != null)
{
<script type="text/javascript">
window.onload = function () {
alert("@ViewBag.Message");
};
</script>
}
</body>
</html>
Screenshots
Form
![ASP.Net MVC: Update Database Table using ListBox]()
Records before update
![ASP.Net MVC: Update Database Table using ListBox]()
Records after update
![ASP.Net MVC: Update Database Table using ListBox]()
Downloads
