ADO.NET MVC Example

MVC (Model View Controller) is a web application design pattern that is widely used in application development. Here, we are creating an MVC application that connects to the SQL Server with the help of ADO.NET framework.

This application contains a Model, a View and a Controller file. Following are the source codes of the application.

Model

// Student.cs

1. using System;  
2. using System.ComponentModel.DataAnnotations;  
3. namespace Ado.NetMvcApplication.Models  
4. {  
5.     public class Student  
6.     {  
7.         public int ID { get; set; }  
8.         // — Validating Student Name  
9.         [Required(ErrorMessage = “Name is required”)]  
10.         [MaxLength(12)]  
11.         public string Name { get; set; }  
12.         // — Validating Email Address  
13.         [Required(ErrorMessage = “Email is required”)]  
14.         [EmailAddress(ErrorMessage = “Invalid Email Address”)]  
15.         public string Email { get; set; }  
16.         // — Validating Contact Number  
17.         [Required(ErrorMessage = “Contact is required”)]  
18.         [DataType(DataType.PhoneNumber)]  
19.         [RegularExpression(@”^\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$”, ErrorMessage = “Not a valid Phone number”)]  
20.         public string Contact { get; set; }  
21.     }  
22. }  

View

// Index.cshtml

1. @model Ado.NetMvcApplication.Models.Student  
2. @{  
3.     ViewBag.Title = “Index”;  
4. }  
5. <h2>Index</h2>  
6. @using (Html.BeginForm(“Save”, “Students”))  
7. {  
8.     @Html.AntiForgeryToken()  
9.       
10.     <div class=”form-horizontal”>  
11.         <h4>Student</h4>  
12.         <hr />  
13.         @Html.ValidationSummary(true, “”, new { @class = “text-danger” })  
14.         <div class=”form-group”>  
15.             @Html.LabelFor(model => model.Name, htmlAttributes: new { @class = “control-label col-md-2” })  
16.             <div class=”col-md-10″>  
17.                 @Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = “form-control” } })  
18.                 @Html.ValidationMessageFor(model => model.Name, “”, new { @class = “text-danger” })  
19.             </div>  
20.         </div>  
21.         <div class=”form-group”>  
22.             @Html.LabelFor(model => model.Email, htmlAttributes: new { @class = “control-label col-md-2” })  
23.             <div class=”col-md-10″>  
24.                 @Html.EditorFor(model => model.Email, new { htmlAttributes = new { @class = “form-control” } })  
25.                 @Html.ValidationMessageFor(model => model.Email, “”, new { @class = “text-danger” })  
26.             </div>  
27.         </div>  
28.         <div class=”form-group”>  
29.             @Html.LabelFor(model => model.Contact, htmlAttributes: new { @class = “control-label col-md-2” })  
30.             <div class=”col-md-10″>  
31.                 @Html.EditorFor(model => model.Contact, new { htmlAttributes = new { @class = “form-control” } })  
32.                 @Html.ValidationMessageFor(model => model.Contact, “”, new { @class = “text-danger” })  
33.             </div>  
34.         </div>  
35.         <div class=”form-group”>  
36.             <div class=”col-md-offset-2 col-md-10″>  
37.                 <input type=”submit” value=”Create” class=”btn btn-default” />  
38.             </div>  
39.         </div>  
40.     </div>  
41. }  
42. @section Scripts {  
43.     @Scripts.Render(“~/bundles/jqueryval”)  
44. }  

Controller

// StudentsController.cs

1. using System.Web.Mvc;  
2. using Ado.NetMvcApplication.Models;  
3. using System.Data.SqlClient;  
4. namespace Ado.NetMvcApplication.Controllers  
5. {  
6.     public class StudentsController : Controller  
7.     {  
8.         // GET: Students  
9.         public ActionResult Index()  
10.         {  
11.             return View();  
12.         }  
13.         [HttpPost]  
14.         public ContentResult Save(Student student)  
15.         {  
16.             string status = “”;  
17.             // Creating Connection  
18.             using (SqlConnection con = new SqlConnection(“data source=.; database=student; integrated security=SSPI”))  
19.             {  
20.                 // Insert query  
21.                 string query = “INSERT INTO student(name,email,contact) VALUES(@name, @email, @contact)”;  
22.                 using (SqlCommand cmd = new SqlCommand(query))  
23.                 {  
24.                     cmd.Connection = con;  
25.                     // opening connection  
26.                     con.Open();  
27.                     // Passing parameter values  
28.                     cmd.Parameters.AddWithValue(“@name”, student.Name);  
29.                     cmd.Parameters.AddWithValue(“@email”, student.Email);  
30.                     cmd.Parameters.AddWithValue(“@contact”, student.Contact);  
31.                     // Executing insert query  
32.                     status = (cmd.ExecuteNonQuery() >= 1) ?  “Record is saved Successfully!” : “Record is not saved”;  
33.                     status += “<br/>”;  
34.                 }  
35.                     // Executing select command  
36.                 using (SqlCommand cmd = new SqlCommand(“select * from student”))  
37.                 {  
38.                     cmd.Connection = con;  
39.                     // Retrieving Record from datasource  
40.                     SqlDataReader sdr = cmd.ExecuteReader();  
41.                     // Reading and Iterating Records  
42.                     while (sdr.Read())  
43.                     {  
44.                         status += “<b>name:</b> “+sdr[“name”]+”<br/> <b>Email:</b> “+sdr[“email”]+”<br> <b>Contact:</b> “+sdr[“contact”];  
45.                     }  
46.                 }  
47.                 return Content(status);  
48.             }  
49.         }  
50.     }  
51. }  

Output:

After submitting, it stores and fetches data from the SQL Server database and produce the following result to the browser.