CRUD Operations In Azure SQL Database Using ASP.NET MVC

In my last articles we have learned how to make CRUD operations using ASP.NET MVC which are all with on premises database but the many reader asking me how to make CRUD operations from Microsoft azure database so by considering their demand I have decided to write this article , Let's learn it step by step
Step 1: Create Azure (cloud) SQL database

First we need to create the Azure SQL database , To create Azure SQL database you need a Azure subscription account, I hope you have an azure account , Now go to the your azure portal using following link

Microsoft Azure Account

Now after logging into the Azure watch following  my video which explains the how to create the Azure SQL database



 I hope you have created Azure SQL Database by watching steps shown in preceding video , after creating the database it listed under the database section of azure portal as shown in the following image ,


The preceding is the sample Azure SQL Database named EDS and the Database server location  is Central India

Step 2 : Find the database credentials

Now we have created database and use from our premises we need to obtain the connecting string , So to obtain connection string  double click on listed database as shown in the preceding image , after clicking on database it will display the following screen


I hope by you have followed  the preceding steps as shown in the image and obtained the database connection string

Step 3 : Connect to the Azure SQL Database

Now our database is created in azure cloud and we have also the connection string details to connect that created database, Now open your SQL Server Management studio from you local system and watch the my following video which shows you how to connect SQL Server Management studio step by step


 

 I hope after watching the preceding video you have connected with your created cloud azure database.

Step 4 : Create an MVC Application.

Now let us start with a step by step approach from the creation of simple MVC application as in the following:
  1. "Start", then "All Programs" and select "Microsoft Visual Studio 2015".
  2. "File", then "New" and click "Project..." then select "ASP.NET Web Application Template", then provide the Project a name as you wish and click on OK. After clicking, the following window will appear:
 As shown in the preceding screenshot, click on Empty template and check MVC option, then click OK. This will create an empty MVC web application whose Solution Explorer will look like the following:

Step 5 : Add The Reference of Dapper ORM into Project

Now next step is to add the reference of Dapper ORM into our created MVC Project ,follow the following steps
  1. Right click on Solution ,find Manage NuGet Package manager and click on it
  2. After as shown into the image and type in search box "dapper"
  3. Select Dapper as shown into the image .
  4. Choose version of dapper library and click on install button

 After installing the Dapper library ,It will be added into the References of our solution explorer of MVC application as

hope you have followed the same steps and installed dapper library.

Step 6: Create Model Class

Now let us create the model class named EmpModel.cs by right clicking on model folder as in the following screenshot:

EmployeeModel.cs
public class EmployeeModel
    {
        [Display(Name = "Id")]
        public int Empid { get; set; }
        [Required(ErrorMessage = "First name is required.")]
        public string Name { get; set; }
        [Required(ErrorMessage = "City is required.")]
        public string City { get; set; }
        [Required(ErrorMessage = "Address is required.")]
        public string Address { get; set; }
    }

In the above model class we have added some validation on properties with the help of DataAnnotations.

Step 7:
Create Controller.

Now let us add the MVC 5 controller as in the following screenshot:

After clicking on Add button it will show the following window. Now specify the Controller name as Employee with suffix Controller as in the following screenshot:


After clicking on Add button controller is created with by default code that support CRUD operations and later on we can configure it as per our requirements.

Step 8 :
Create Table and Stored procedures.

Now We are connected with azure SQL database from SQL Server management studio and  before creating the views let us create the table name Employee in database according to our model fields to store the details:

I hope you have created the same table structure as shown above. Now create the stored procedures to insert, update, view and delete the details as in the following code snippet:

To Insert Records

Create procedure [dbo].[AddNewEmpDetails]
(
@Name varchar (50),
@City varchar (50),
@Address varchar (50)
)
as
begin
Insert into Employee values(@Name,@City,@Address)
End 
 
To View Added Records

CREATE Procedure [dbo].[GetEmployees]  
as  
begin  
select Id as Empid,Name,City,Address from Employee
End  
To Update Records

Create procedure [dbo].[UpdateEmpDetails]
(
@EmpId int,
@Name varchar (50),
@City varchar (50),
@Address varchar (50)
)
as
begin
Update Employee
set Name=@Name,
City=@City,
Address=@Address
where Id=@EmpId
End 

To Delete Records 

Create procedure [dbo].[DeleteEmpById]
(
@EmpId int
)
as
begin
Delete from Employee where Id=@EmpId
End 

Step 9: Create Repository class.

Now create Repository folder and Add EmpRepository.cs class for database related operations, after adding the solution explorer will look like the following screenshot:


Now create methods in EmpRepository.cs to handle the CRUD operation as in the following

EmpRepository.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using CRUDUsingMVC.Models;
using Dapper;
using System.Linq;
namespace CRUDUsingMVC.Repository
{
        public class EmpRepository
    {
        public SqlConnection con;
        //To Handle connection related activities
        private void connection()
        {
        string constr = ConfigurationManager.ConnectionStrings["SqlConn"].ToString();
            con = new SqlConnection(constr);
        }
        //To Add Employee details
        public void AddEmployee(EmployeeModel objEmp)
        {
        //Additing the employess
        try
            {
                connection();
                con.Open();
                con.Execute("AddNewEmpDetails", objEmp, commandType: CommandType.StoredProcedure);
                con.Close();
            }
        catch (Exception ex)
            {
        throw ex;
            }
        }
        //To view employee details with generic list 
        public List<EmployeeModel> GetAllEmployees()
        {
        try
            {
                connection();
                con.Open();
                IList<EmployeeModel> EmpList = SqlMapper.Query<EmpModel>(
                                  con, "GetEmployees").ToList();
                con.Close();
        return EmpList.ToList();
            }
        catch (Exception)
            {
        throw;
            }
        }
        //To Update Employee details
        public void UpdateEmployee(EmployeeModel objUpdate)
        {
        try
            {
                connection();
                con.Open();
                con.Execute("UpdateEmpDetails", objUpdate, commandType: CommandType.StoredProcedure);
                con.Close();
            }
        catch (Exception)
            {
        throw;
            }
        }
        //To delete Employee details
        public bool DeleteEmployee(int Id)
        {
        try
            {
                DynamicParameters param = new DynamicParameters();
                param.Add("@EmpId", Id);
                connection();
                con.Open();
                con.Execute("DeleteEmpById", param, commandType: CommandType.StoredProcedure);
                con.Close();
        return true;
            }
        catch (Exception ex)
            {
        //Log error as per your need 
        throw ex;
            }
        }
    }
}

Note
  1. In the above code we manually opening and closing connection ,however you can directly pass the connection string to the dapper without opening it ,dapper will automatically handled .
  2. Log the exception in database or text file as per you convenience , since it article so i have not implemented it .
Step 10 : Create Methods into the EmployeeController.cs file.

Now open the EmployeeController.cs and create the following action methods:

using System.Web.Mvc;
using CRUDUsingMVC.Models;
using CRUDUsingMVC.Repository;
namespace CRUDUsingMVC.Controllers
{
        public class EmployeeController : Controller
    {
        // GET: Employee/GetAllEmpDetails
        public ActionResult GetAllEmpDetails()
        {
            EmpRepository EmpRepo = new EmpRepository();
        return View(EmpRepo.GetAllEmployees());
        }
        // GET: Employee/AddEmployee
        public ActionResult AddEmployee()
        {
        return View();
        }
        // POST: Employee/AddEmployee
        [HttpPost]
        public ActionResult AddEmployee(EmployeeModel Emp)
        {
        try
            {
        if (ModelState.IsValid)
                {
                    EmpRepository EmpRepo = new EmpRepository();
                    EmpRepo.AddEmployee(Emp);
                    ViewBag.Message = "Records added successfully.";
                }
        return View();
            }
        catch
            {
        return View();
            }
        }
        // GET: Bind controls to Update details
        public ActionResult EditEmpDetails(int id)
        {
            EmpRepository EmpRepo = new EmpRepository();
        return View(EmpRepo.GetAllEmployees().Find(Emp => Emp.Empid == id));
        }
        // POST:Update the details into database
        [HttpPost]
        public ActionResult EditEmpDetails(int id, EmployeeModel obj)
        {
        try
            {
                EmpRepository EmpRepo = new EmpRepository();
                EmpRepo.UpdateEmployee(obj);
        return RedirectToAction("GetAllEmpDetails");
            }
        catch
            {
        return View();
            }
        }
        // GET: Delete  Employee details by id
        public ActionResult DeleteEmp(int id)
        {
        try
            {
                EmpRepository EmpRepo = new EmpRepository();
        if (EmpRepo.DeleteEmployee(id))
                {
                    ViewBag.AlertMsg = "Employee details deleted successfully";
                }
        return RedirectToAction("GetAllEmpDetails");
            }
        catch
            {
        return RedirectToAction("GetAllEmpDetails");
            }
        }
    }
}
Step 11: Create the Partial view to Add the employees

To create the Partial View to add Employees, right click on ActionResult method and then click Add view. Now specify the view name, template name and model class in EmpModel.cs and After clicking on Add button it generates the strongly typed view whose code is given below:

AddEmployee.cshtml

@model CRUDUsingMVC.Models.EmployeeModel
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-horizontal">
<h4>Add Employee</h4>
<div>
@Html.ActionLink("Back to Employee List", "GetAllEmpDetails")
</div>
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
<div class="form-group">
@Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Save" class="btn btn-default" />
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10" style="color:green">
@ViewBag.Message
</div>
</div>
</div>
}
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script> 


To View Added Employees

To view the employee details let us create the partial view named GetAllEmpDetails:
Now click on add button, it will create GetAllEmpDetails.cshtml strongly typed view whose code is given below:

GetAllEmpDetails.CsHtml

@model IEnumerable<CRUDUsingMVC.Models.EmployeeModel>
<p>
@Html.ActionLink("Add New Employee", "AddEmployee")
</p>
<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
<th>
@Html.DisplayNameFor(model => model.City)
</th>
<th>
@Html.DisplayNameFor(model => model.Address)
</th>
<th></th>
</tr>
@foreach (var item in Model)
{
@Html.HiddenFor(model => item.Empid)
<tr>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.City)
</td>
<td>
@Html.DisplayFor(modelItem => item.Address)
</td>
<td>
@Html.ActionLink("Edit", "EditEmpDetails", new { id = item.Empid }) |
@Html.ActionLink("Delete", "DeleteEmp", new { id = item.Empid }, new { onclick = "return confirm('Are sure wants to delete?');" })
</td>
</tr>
}
</table> 
To Update Added Employees

Follow the same procedure and create EditEmpDetails view to edit the employees. After creating the view the code will be like the following:

EditEmpDetails.cshtml 


@model CRUDUsingMVC.Models.EmployeeModel
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-horizontal">
<h4>Update Employee Details</h4>
<hr />
<div>
@Html.ActionLink("Back to Details", "GetAllEmployees")
</div>
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
@Html.HiddenFor(model => model.Empid)
<div class="form-group">
@Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Update" class="btn btn-default" />
</div>
</div>
</div>
}
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script> 
 
Step 12 : Configure Action Link to Edit and delete the records as in the following figure:
The above ActionLink I have added in GetAllEmpDetails.CsHtml view because from there we will delete and update the records.

Step 13: Configure RouteConfig.cs to set default action as in the following code snippet:
 
public class RouteConfig
{
public static void RegisterRoutes(RouteCollection routes)
{
routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
routes.MapRoute(
name: "Default",
url: "{controller}/{action}/{id}",
defaults: new { controller = "Employee", action = "AddEmployee", id = UrlParameter.Optional }
);
}
} 

From the above RouteConfig.cs the default action method we have set is AddEmployee. It means that after running the application the AddEmployee view will be executed first.
Now after adding the all model, views and controller our solution explorer will be look like as in the following screenshot:



I hope from the preceding examples we have learned how to implement CRUD Operations in Azure SQL database using ASP.NET MVC

Note:
  • Configure the database connection in the web.config file depending on your Azure SQL database server location.
  • To Use Azure SQL Database you need Microsoft Azure Subscription
  • Since this is a demo, it might not be using proper standards, so improve it depending on your skills
  • This application is created completely focusing on beginners.
Summary

I hope, this article is useful for all the readers. If you have any suggestions, please contact me.


Don't Forget To 

Inserting Data into Microsoft Azure SQL DataBase Using ASP.NET MVC

In my previous video tutorial we have learned how to create Azure (cloud) SQL Database and how to connect Azure SQL Database using SQL Server Management studio , Now In this article we will learn how to insert data into Microsoft Azure (cloud) SQL Database  using ASP.NET MVC . Let's learn step by step so beginners can also understand

Step 1: Create Azure (Cloud) SQL Database

First we need to create the Azure SQL Database from Microsoft Azure portal , If you are new to the Microsoft Azure and wanted to know how to create Azure SQL Database then watch my videos tutorials using following link
I hope you went through the steps described in video tutorials and created the database . Now login to your Azure portal, The created database will be listed like are as shown in the following image


The preceding Azure SQL Portal screenshot EDS is the Database and the Database server location  is Central India

Step 2 : Create an MVC Application.

Now let us start with a step by step approach from the creation of simple MVC application as in the following:
  1. "Start", then "All Programs" and select "Microsoft Visual Studio 2015".
  2. "File", then "New" and click "Project..." then select "ASP.NET Web Application Template", then provide the Project a name as you wish and click on OK. After clicking, the following window will appear:


Step 3: Create Model Class

Now let us create the model class named EmployeeModel.cs by right clicking on model folder and write the get set properties inside the EmployeeModel.cs class file as
EmployeeModel.cs
 
public class EmployeeModel  
  {  
      [Display(Name = "Id")]  
      public int Empid { get; set; }  
  
      [Required(ErrorMessage = "First name is required.")]  
      public string Name { get; set; }  
  
      [Required(ErrorMessage = "City is required.")]  
      public string City { get; set; }  
  
      [Required(ErrorMessage = "Address is required.")]  
      public string Address { get; set; }  
  
  }  

Step 4:
  Create Controller.

Now let us add the MVC 5 controller as in the following screenshot:


After clicking on Add button it will show the following window. Now specify the Controller name as Employee with suffix Controller it will add the empty controller class
Step 5 : Create Table and Stored procedures.

Now before creating the view let us create the table name Employee in database according to our model fields to store the details



Now create the stored procedures to insert employee details into database , The code snippet will be look like as following
Create procedure [dbo].[AddNewEmpDetails]  
(  
   @Name varchar (50),  
   @City varchar (50),  
   @Address varchar (50)  
)  
as  
begin  
   Insert into Employee values(@Name,@City,@Address)  
End  

Step 6 : Modify the EmployeeController.cs file.

Now open the EmployeeController.cs file and create the methods for inserting data into Azure SQL Database and for displaying view as

EmployeeController.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using UsingAzureDB.Models;
using System.Linq;
using System.Web.Mvc;
namespace UsingAzureDB
{

public class EmployeeController : Controller    
    {    
          
        private SqlConnection con;
        //To Handle connection related activities
        private void connection()
        {
            string constr = ConfigurationManager.ConnectionStrings["getconn"].ToString();
            con = new SqlConnection(constr);

        }
          // GET: Employee/AddEmployee    
        public ActionResult AddEmployee()    
        {    
            return View();    
        }    
      
        // POST: Employee/AddEmployee    
        [HttpPost]    
        public ActionResult AddEmployee(EmployeeModel Emp)    
        {    
            try    
            {    
                if (ModelState.IsValid)    
                {    
                         
                    if (AddEmployee(Emp))    
                    {    
                        ViewBag.Message = "Employee details added successfully";    
                    }    
                }    
                  
                return View();    
            }    
            catch    
            {    
                return View();    
            }    
        }    
      
      //To Add Employee details
        public bool AddEmployee(EmployeeModel obj)
        {

            connection();
            SqlCommand com = new SqlCommand("AddNewEmpDetails", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@Name", obj.Name);
            com.Parameters.AddWithValue("@City", obj.City);
            com.Parameters.AddWithValue("@Address", obj.Address);        
            con.Open();
            int i = com.ExecuteNonQuery();
            con.Close();
            if (i >= 1)
            {

                return true;

            }
            else
            {

                return false;
            }
        }
              
    } 
}       

Step 7: Create strongly typed view.


To create the View to add Employees, right click on ActionResult method and then click Add view. Now specify the view name, template name and model class in EmployeeModel.cs , It will create the view named AddEmployee.cshtml

 AddEmployee.cshtml


@model UsingAzureDB.Models.EmployeeModel  
@using (Html.BeginForm())  
{  
    @Html.AntiForgeryToken()  
    <div class="form-horizontal">  
        <h4>Add Employee</h4>  
        <div>  
            @Html.ActionLink("Back to Employee List", "GetAllEmpDetails")  
        </div>  
        <hr />  
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })  
  
  
        <div class="form-group">  
            @Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })  
            <div class="col-md-10">  
                @Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })  
                @Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })  
            </div>  
        </div>  
  
        <div class="form-group">  
            @Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" })  
            <div class="col-md-10">  
                @Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })  
                @Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" })  
            </div>  
        </div>  
  
        <div class="form-group">  
            @Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })  
            <div class="col-md-10">  
                @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })  
                @Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })  
            </div>  
        </div>  
  
        <div class="form-group">  
            <div class="col-md-offset-2 col-md-10">  
                <input type="submit" value="Save" class="btn btn-default" />  
            </div>  
        </div>  
        <div class="form-group">  
            <div class="col-md-offset-2 col-md-10" style="color:green">  
                @ViewBag.Message  
  
            </div>  
        </div>  
    </div>  
  
}  
  

Step 8 - Run the Application
 
After running the application enter the appropriate values into the text boxes and click on save button it will insert the records into the Azure SQL  database as shown in the following image


 Now lets open the Azure SQL database using SQL server management studio , It will shows the inserted records are as follows



From the preceding examples we have learned how to insert data into Azure SQL database using ASP.NET MVC.

Note:
  • Configure the Database connection string in the web.config file depending on your Azure SQL Database server credentials.
  • To Use Azure SQL Database you need Microsoft Azure Subscription
  • Since this is a demo, it might not be using proper standards, so improve it depending on your skills
Summary
I hope, this article is useful for all the readers. If you have any suggestions, please contact me.

Don't Forget To 

Filters In ASP.NET MVC

Security of the web application is very important to ensure valuable information is protected and not accessible to unauthorized user or entity . There are lots of techniques to maintain the security of the application which depends on security type scenario. So in this article we learn in brief about the ASP.NET MVC filters which are useful to maintain the security during the request processing.

Following are the filters provided by ASP.NET MVC to check the business logic and validation before and after executing the action methods or view results .
  • Authorization filters
  • Action filters
  • Result filters
  • Exception filters
Authorization filters
Used to authenticate request before executing the action method to ensure the request is authenticated and genuine. Authorization filter uses IAuthorizationFilter interface in which authorization filter methods are defined.
 Authorization filters has following built in filters
  1. Authorize
  2. RequireHttps
Action filters

Action filter useful to take some action before and after executing the action methods, This action filter implements the IActionFilter interface which includes the following methods .
  1. OnActionExecuting 
  2. OnActionExecuted
Result filters

Result filter useful to perform some action before and after executing view result, This action filter implements the IResultFilter interface which includes the following methods .
  1. OnResultExecuting 
  2. OnResultExecuted 
Result  filter has built in OutputCache filter.

Exception filters  

Exception filter responsible to manage unhanded exceptions which are occurred during the execution of ASP.NET MVC pipeline request . This filter is very useful to capture and log the exception details wherever you wants. This filter implements the IExceptionFilter interface also it has also  built in filter named HandleError.

These filter are executed in the following sequence
  1. Authorization filters 
  2. Action filters 
  3. Response filters 
  4. Exception filters 
Key Points
  • Filters are used to check some business logic before and after executing the action methods or view result. 
  • Each filter implements the its own interface methods.
  • Filters can be applied on Controller level , action method level as well as globally using filterconfig class
  • We can apply multiple filters on single controller or action method
  • If given ASP.NET MVC filter does not fulfill the our requirement then we can also create the custom filters using  FilterAttribute class.
  • Usually filters are registered in Global.asax file 
 I hope you have got brief understanding about the ASP.NET MVC filters , In my next article we will learn each of the filter in details with real time examples.
Summary
I hope, this article is useful for all the readers. If you have any suggestions, please contact me.

Don't Forget To 

Insert Data into DataBase using ASP.NET MVC with ADO.NET

There are many techniques in ASP.NET MVC to insert the data into database including ORM such as Entity framework , Dapper etc and database access technology such as ADO.NET , In this article we will learn how to insert the data into database using ADO.NET in ASP.NET MVC application
Step 1 : Create an MVC Application.
Now let us start with a step by step approach from the creation of simple MVC application as in the following:
  1. "Start", then "All Programs" and select "Microsoft Visual Studio 2015".
  2. "File", then "New" and click "Project..." then select "ASP.NET Web Application Template", then provide the Project a name as you wish and click on OK. After clicking, the following window will appear:
 As shown in the preceding screenshot, click on Empty template and check MVC option, then click OK. This will create an empty MVC web application
Step 2: Create Model Class
Now let us create the model class named EmpModel.cs by right clicking on model folder and write the model properties inside the EmpModel.cs class after adding the properties the class will be look like as follows 
EmpModel.cs
public class EmpModel  
  {  
      [Display(Name = "Id")]  
      public int Empid { get; set; }  
  
      [Required(ErrorMessage = "First name is required.")]  
      public string Name { get; set; }  
  
      [Required(ErrorMessage = "City is required.")]  
      public string City { get; set; }  
  
      [Required(ErrorMessage = "Address is required.")]  
      public string Address { get; set; }  
  
  }  
In the above model class we have added some validation on properties with the help of Data Annotations.
Step 3:
  Create Controller.
Now let us add the MVC 5 controller as in the following screenshot:


After clicking on Add button it will show the following window. Now specify the Controller name as Employee with suffix Controller it will add the empty controller class
Step 4 : Create Table and Stored procedures.
Now before creating the view let us create the table name Employee in database according to our model fields to store the details

Now create the stored procedures to insert employee details into database , The code snippet will be look like as following
Create procedure [dbo].[AddNewEmpDetails]  
(  
   @Name varchar (50),  
   @City varchar (50),  
   @Address varchar (50)  
)  
as  
begin  
   Insert into Employee values(@Name,@City,@Address)  
End  

Step 5 : Modify the EmployeeController.cs file.
Now open the EmployeeController.cs and create the action methods and database insert logic, after modifying the EmployeeController.cs class the the code snippet will be look like as follows
EmployeeController.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using CRUDUsingMVC.Models;
using System.Linq;
using System.Web.Mvc;
namespace UsingMVC
{

public class EmployeeController : Controller    
    {    
          

        private SqlConnection con;
        //To Handle connection related activities
        private void connection()
        {
            string constr = ConfigurationManager.ConnectionStrings["getconn"].ToString();
            con = new SqlConnection(constr);

        }
          // GET: Employee/AddEmployee    
        public ActionResult AddEmployee()    
        {    
            return View();    
        }    
      
        // POST: Employee/AddEmployee    
        [HttpPost]    
        public ActionResult AddEmployee(EmpModel Emp)    
        {    
            try    
            {    
                if (ModelState.IsValid)    
                {    
                    EmpRepository EmpRepo = new EmpRepository();    
      
                    if (EmpRepo.AddEmployee(Emp))    
                    {    
                        ViewBag.Message = "Employee details added successfully";    
                    }    
                }    
                  
                return View();    
            }    
            catch    
            {    
                return View();    
            }    
        }    
      
      //To Add Employee details
        public bool AddEmployee(EmpModel obj)
        {

            connection();
            SqlCommand com = new SqlCommand("AddNewEmpDetails", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@Name", obj.Name);
            com.Parameters.AddWithValue("@City", obj.City);
            com.Parameters.AddWithValue("@Address", obj.Address);        
            con.Open();
            int i = com.ExecuteNonQuery();
            con.Close();
            if (i >= 1)
            {

                return true;

            }
            else
            {

                return false;
            }
        }
              
    } 
}       

Step 6: Create strongly typed view.
To create the View to add Employees, right click on ActionResult method and then click Add view. Now specify the view name, template name and model class in EmpModel.cs and click on Add button, It will create the view named AddEmployee.cshtml, now open the AddEmployee.cshtml and modify the code as per our model class properties
 AddEmployee.cshtml
@model UsingMVC.Models.EmpModel  
@using (Html.BeginForm())  
{  
    @Html.AntiForgeryToken()  
    <div class="form-horizontal">  
        <h4>Add Employee</h4>  
        <div>  
            @Html.ActionLink("Back to Employee List", "GetAllEmpDetails")  
        </div>  
        <hr />  
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })  
  
  
        <div class="form-group">  
            @Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })  
            <div class="col-md-10">  
                @Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })  
                @Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })  
            </div>  
        </div>  
  
        <div class="form-group">  
            @Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" })  
            <div class="col-md-10">  
                @Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })  
                @Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" })  
            </div>  
        </div>  
  
        <div class="form-group">  
            @Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })  
            <div class="col-md-10">  
                @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })  
                @Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })  
            </div>  
        </div>  
  
        <div class="form-group">  
            <div class="col-md-offset-2 col-md-10">  
                <input type="submit" value="Save" class="btn btn-default" />  
            </div>  
        </div>  
        <div class="form-group">  
            <div class="col-md-offset-2 col-md-10" style="color:green">  
                @ViewBag.Message  
  
            </div>  
        </div>  
    </div>  
  
}  
  
<script src="~/Scripts/jquery-1.10.2.min.js"></script>  
<script src="~/Scripts/jquery.validate.min.js"></script>  
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>

Step 7 - Run the Application
After running the application type the appropriate values into the textboxes and click on save button it will insert the records into the database as shown in the following image
From the preceding examples we have learned how to insert data into database in ASP.NET MVC using ADO.NET.
Note:
  • Configure the database connection in the web.config file depending on your database server location.
  • Since this is a demo, it might not be using proper standards, so improve it depending on your skills
  • This application is created completely focusing on beginners.
Summary
I hope, this article is useful for all the readers. If you have any suggestions, please contact me.
Don't Forget To 

ViewBag ViewData and TempData in ASP.NET MVC

In web application maintaining the state of the application is very important to pass the data between the requests, ASP.NET uses lots of techniques to pass data between requests in which most of the techniques uses the Viewstate which slows the page load time, So in this article we will learn about ASP.NET MVC ViewBag, ViewData and TempData which is used in ASP.NET MVC application to pass data between the requests which not using Viewstate, Lets learn about them in short description with example which will understandable for beginners as wel as students .

What is ViewBag?

ViewBag is used to pass data from controller to view for single request.


Key points
  • Used to pass data from controller to view. 
  • The value assigned to ViewBag is only available for single request, if redirection happens then value will be null.
  • It is dynamic property of ControllerBase class which internally uses System.Dynamic.ExpandoObject() .
  • Its uses dynamic data type to hold data which was introduced in C# 4.0.
  •  Since it uses dynamic data type so it’s does not require type conversion such as int to string or string to int etc .
Example
Write the following code into your controller class
//Assigning value to viewBag
ViewBag.msg="Vithal Wadje";
In the above code ViewBag is the keyword and msg is the dynamic property . you can assign any property like msg because this is not predefined you need to assign as per your wish .
Accessing ViewBag value in View
<div>
@ViewBag.msg
</div>

In the above way we can access the ViewBag value into the view, I hope from all preceding example you have learned about the ViewBag.
    What is ViewData?

    ViewData is used to pass data from controller to view for single request which holds the data in key-value pairs were key will be string type and value is object type.


    Key points
    • Used to pass data from controller to view. 
    • ViewData is the object of ViewDataDictionary class which holds the data by key and value pair. 
    • In ViewData Key is the string type and value is the object type .
    • We need to handle null otherwise if ViewData value is null and we tried to access value then it throws the exception.
    • The value assigned to ViewData is only available for single request, if redirection happens then value will be null.
    • It is  property of ControllerBase class.
    •  It’s  require type conversion such as int to string or string to int etc .
    Example
    There are two ways to declare or assign the value to the ViewData which are as follows
    Write the following code into your controller class
    Method :1
    //Assigning value to ViewData
     ViewData["City"] = "Mumbai";
    In the above code City is the key and Mumbai is the value for ViewData object.
    Accessing ViewData value in View
    <div>
    @ViewData["City"]
    </div>
    In the above way we can access the ViewData value into the view.
    Method :2
    //Assigning value to ViewData
      ViewData.Add("Name", "Vithal Wadje");
    In the above code City is the key and Mumbai is the value for ViewData object.
    Accessing ViewData value in View
    <div>
    @ViewData["Name"]
    </div>
    In the above way we can access the ViewData value into the view, I hope from preceding example you have learned about ViewData
    What is TempData ? 

    TempData is used to pass data from controller to controller and also controller to view for current request.
    TempData in ASP.NET MVC by compilemode.com


    In Web application passing data from one request to another request is very important to perform the some action on data. In traditional ASP.NET application data passed to the session is stay's long time time in memory as per session timeout defined in the web.config file, But sometime we need to maintain data only for current request instead of all requests and data should be disappears in second request , for this situation session is not fit which maintain the data for all request so we need to use TempData to maintain data for only current request which saves the server memory by releasing the data when second request fires.I hope you understand when to use TempData over session variable.
    Key points
    • Used to pass data from controller to controller as well as controller to view.
    • It holds the Data for short time means till current request.
    • TempData internally uses session variable to hold the values during request.
    • TempData is the object of ViewDataDictionary class.
    • We need to handle null otherwise if  TempData value is null and we tried to access value then it throws the exception.
    • The value assigned to TempData is only available for single request, if again new request initiated then value will become null.
    • It is  property of ControllerBase class.
    •  It’s  require type conversion such as int to string or string to int etc . 
    Example
    We can assign the value to the TempData in the following way
    //Assigning value to TempData
     TempData["CityName"] = "Latur";
    In the above code CityName is the key and Latur is the value for TempData object.
    Accessing TempData value in View
    <div>
    @TempData["CityName"]
    </div>
    In the above way we can access the TempData value into the view.
    Accessing TempData value in Controller Action method
        public ActionResult CheckTempValue()
            {
               //Reading the values of TempData
                var TempName = TempData["EmpName"].ToString();
                return View();
            }
    In the above way we can access the TempData value into the controller, I hope from preceding example you have learned about the TempData.
    Summary
    I hope this article is useful for all readers to learn and know about the ViewBag, ViewData and TempData . If you have an any suggestion related to this article then please contact me.

    Don't Forget To 

    Display Data From Hosted Web API REST Service in ASP.NET MVC Using HttpClient

    Many reader asking me to write on how to get data from remotely hosted web API REST service , So I have decided to write this article to explain step by step how to display data from hosted web API REST Service in ASP.NET MVC using HttpClient. To demonstrate this lets create simple ASP.NET MVC application
    If are new and wants to learn web API REST service from creating to hosting to consuming in client application.
    In this article, we will use the same hosted Web API REST service  to consume in our created ASP.NET MVC web application. Now, let's start consuming Web API REST service in ASP.NET MVC application step by step.
    Step 1 - Create MVC Application.
    1. "Start", followed by "All Programs" and select "Microsoft Visual Studio 2015".
    2. Click "File", followed by "New" and click "Project". Select "ASP.NET Web Application Template", provide the Project a name as you wish and click OK.
    3. After clicking, the following Window will appear. Choose empty project template and check on MVC option.

    The preceding step creates the simple empty ASP.NET MVC application without Model, View, and Controller, The Solution Explorer of created web application will look like the following.


    Step 2 - Install HttpClient library from NuGet

    We are going to use HttpClient to consume the Web API REST Service, so we need to install this library from NuGet Package Manager .
    What is HttpClient ?
    HttpClient is base class which is responsible to send HTTP request and receive HTTP response resources i.e from REST services.
    To install HttpClient, right click on Solution Explorer of created application and search for HttpClient, as shown in the following image.

    Step 3 - Install WebAPI.Client library from NuGet
    This package is used for formatting and content negotiation which provides support for System.Net.Http. To install, right click on Solution Explorer of created application and search for WebAPI.Client, as shown in following image.
    Now, click on "Install" button after choosing the appropriate version. It will get installed after taking few seconds depending on your internet speed. We have installed necessary NuGet packages to consume Web API REST services in web application. I hope you have followed the same steps.
    Step 4 - Create Model Class 
    Now, let us create the Model class named Employee.cs  or as you wish, by right clicking on Models folder with same number of entities which are exposing by our hosted Web API REST service to exchange the data. The code snippet of created Employee.cs class will look like this.
        public class Employee  
        {  
            public int Id { get; set; }  
            public string Name { get; set; }  
                     
            public string City { get; set; }  
          
        }   
    
    
    Step 5 - Add Controller Class
    Now, let us add ASP.NET MVC controller, as shown in the screenshot given below.


    After clicking Add button, it will show in the Window. Specify the Controller name as Home with suffix Controller. Now, let's modify the default code of Home controller . Our hosted Web API REST Service includes these two methods, as given below.
    • GetAllEmployees (GET )
    • GetEmployeeById (POST ) which takes id as input parameter 
    The url of the hosted web API REST Service is  http://192.168.95.1:5555/api/Employee/GetAllEmployees

    In the preceding url
    • http://localhost:56290 Is the base address of web API service, It can be different as per your server.
    • api It is the used to differentiate between Web API controller and MVC controller request .
    • Employee This is the Web API controller name.
    • GetAllEmployees This is the Web API method which returns the all employee list.
    After modifying the code of Homecontroller class, the code will look like the following.
    Homecontroller.cs
    using ConsumingWebAapiRESTinMVC.Models;  
    using Newtonsoft.Json;  
    using System;  
    using System.Collections.Generic;  
    using System.Net.Http;  
    using System.Net.Http.Headers;  
    using System.Threading.Tasks;  
    using System.Web.Mvc;  
      
    namespace ConsumingWebAapiRESTinMVC.Controllers  
    {  
        public class HomeController : Controller  
        {  
            //Hosted web API REST Service base url  
            string Baseurl = "http://192.168.95.1:5555/";      
            public async Task<ActionResult> Index()  
            {  
                List<Employee> EmpInfo = new List<Employee>();  
                  
                using (var client = new HttpClient())  
                {  
                    //Passing service base url  
                    client.BaseAddress = new Uri(Baseurl);  
      
                    client.DefaultRequestHeaders.Clear();  
                    //Define request data format  
                    client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));  
                      
                    //Sending request to find web api REST service resource GetAllEmployees using HttpClient  
                    HttpResponseMessage Res = await client.GetAsync("api/Employee/GetAllEmployees");  
      
                    //Checking the response is successful or not which is sent using HttpClient  
                    if (Res.IsSuccessStatusCode)  
                    {  
                        //Storing the response details recieved from web api   
                        var EmpResponse = Res.Content.ReadAsStringAsync().Result;  
      
                        //Deserializing the response recieved from web api and storing into the Employee list  
                        EmpInfo = JsonConvert.DeserializeObject<List<Employee>>(EmpResponse);  
      
                    }  
                    //returning the employee list to view  
                    return View(EmpInfo);  
                }  
            }  
        }  
    }  

    I hope, you have gone through the same steps and understood about the how to use and call Web API REST service resource using HttpClient .
    Step 6 - Create strongly typed View
    Now, right click on Views folder of the created application and create strongly typed View named by Index by choosing Employee class to display the employee list from hosted web API REST Service, as shown in the following image.


    Now, click on "Add" button. It will create View named index after modifying the default code. The code snippet of the Index View looks like the following.

    Index.cshtml 
    @model IEnumerable<ConsumingWebAapiRESTinMVC.Models.Employee>  
      
    @{  
        ViewBag.Title = "www.compilemode.com";  
    }  
      
    <div class="form-horizontal">  
      
        <hr />  
        <div class="form-group">  
      
      
            <table class="table table-responsive" style="width:400px">  
                <tr>  
                    <th>  
                        @Html.DisplayNameFor(model => model.Name)  
                    </th>  
                    <th>  
                        @Html.DisplayNameFor(model => model.City)  
                    </th>  
                      
                </tr>  
      
                @foreach (var item in Model) {  
                    <tr>  
                        <td>  
                            @Html.DisplayFor(modelItem => item.Name)  
                        </td>  
                        <td>  
                            @Html.DisplayFor(modelItem => item.City)  
                        </td>  
                          
                    </tr>  
    }  
      
            </table>  
        </div>  
    </div>  

    The preceding View will display all employees list . Now, we have done all the coding.
    Step 7 - Run the Application
    After running the Application, the employee list from hosted web API REST service will look like this.


    I hope, from the above examples, you have learned how to consume Web API REST Service in ASP.NET MVC using HttpClient.

    Note
    • This article is just guideline on how to consume Web API REST Service in ASP.NET MVC application using HttpClient .
    • In this article, the optimization is not covered in depth; do it as per your skills.
    Summary
    I hope, this article is useful for all the readers. If you have any suggestions, please contact me.
    Don't Forget To 

    www.CodeNirvana.in

    Protected by Copyscape Online Copyright Protection
    Copyright © Compile Mode