Downloading Binary Files From SQL DataBase Using ASP.NET MVC

This articles explains the step by step how to download the files in ASP.NET MVC from database which is stored in a binary format , So lets learn step by step so beginners also can also understand .

Step 1 : View Binary formatted Uploaded Files

Step 2: Create an MVC Application.
Now, let us create a simple MVC application to download the uploaded  file as:
  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. After clicking, the following Window will appear:
Step 3: Create Model Class
Now, let us create the model class file, named FileDetailsModel.cs, by right clicking on Models folder and define the following properties  as:
public class FileDetailsModel  
   {  
       public int Id { get; set; }  
       [Display(Name = "Uploaded File")]  
       public String FileName { get; set; }  
       public byte[] FileContent { get; set; }  
  
  
   }  
Step 4 : Create Stored Procedure
Now Create the stored procedure to view the uploaded files using following script as
CREATE Procedure [dbo].[GetFileDetails]  
(  
@Id int=null  
)  
as  
begin  
select Id,FileName,FileContent from FileDetails  
where Id=isnull(@Id,Id)  
End  
 I hope same you have created ;
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 the Window. Specify the Controller name as Home with suffix Controller. Now, let's modify the default code of Home controller . After modifying the code of Homecontroller class, the code will look like:
HomeController.cs
using System;  
    using System.Collections.Generic;  
    using System.IO;  
    using System.Linq;  
    using System.Web;  
    using System.Web.Mvc;  
    using Dapper;  
    using System.Configuration;  
    using System.Data.SqlClient;
    using FileUploadDownLoadInMVC.Models;  
    using System.Data;  
      
    namespace FileUploadDownLoadInMVC.Controllers  
    {  
        public class HomeController : Controller  
        {  
             
            #region Upload Download file  
            public ActionResult Index()  
            {  
                return View();  
            }  
                   
            [HttpGet]  
            public FileResult DownLoadFile(int id)  
            {  
      
      
                List<FileDetailsModel> ObjFiles = GetFileList();  
      
                var FileById = (from FC in ObjFiles  
                                where FC.Id.Equals(id)  
                                select new { FC.FileName, FC.FileContent }).ToList().FirstOrDefault();  
      
                return File(FileById.FileContent, "application/pdf", FileById.FileName);  
      
            }  
            #endregion  
     
            #region View Uploaded files  
            [HttpGet]  
            public PartialViewResult FileDetails()  
            {  
                List<FileDetailsModel> DetList = GetFileList();  
      
                return PartialView("FileDetails", DetList);  
      
      
            }  
            private List<FileDetailsModel> GetFileList()  
            {  
                List<FileDetailsModel> DetList = new List<FileDetailsModel>();  
      
                DbConnection();  
                con.Open();  
                DetList = SqlMapper.Query<FileDetailsModel>(con, "GetFileDetails", commandType: CommandType.StoredProcedure).ToList();  
                con.Close();  
                return DetList;  
            }  
     
            #endregion       
            #region Database connection  
      
            private SqlConnection con;  
            private string constr;  
            private void DbConnection()  
            {  
                 constr =ConfigurationManager.ConnectionStrings["dbcon"].ToString();  
                 con = new SqlConnection(constr);  
      
            }  
            #endregion  
        }  
    }   
The preceding code snippet explained everything to upload  PDF file into database , I hope you have followed the same .
 Step 6:  Create View
Right click on View folder of the created Application and create view named Index and Partial view FileDetails , The code snippet of the view's is look like as following .
Index.cshtml
@{  
    ViewBag.Title = "www.compilemode.com";  
}  
  
@using (Html.BeginForm())  
{  
    @Html.AntiForgeryToken()    
        <div class="form-group">  
            <div class="col-md-offset-2 col-md-10 text-success">  
                @ViewBag.FileStatus  
            </div>  
        </div>  
  
        <div class="form-group">  
            <div class="col-md-8">  
                @Html.Action("FileDetails", "Home")  
  
            </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>  
FileDetails.cshtml
@model IEnumerable<FileUploadDownLoadInMVC.Models.FileDetailsModel>  
<table class="table table-bordered">  
    <tr>  
        <th class="col-md-4">  
            @Html.DisplayNameFor(model => model.FileName)  
        </th>  
          
        <th class="col-md-2"></th>  
    </tr>  
  
@foreach (var item in Model) {  
    <tr>  
        <td>  
            @Html.DisplayFor(modelItem => item.FileName)  
        </td>  
          
        <td>  
            @Html.ActionLink("Downlaod", "DownLoadFile", new { id=item.Id })   
             
        </td>  
    </tr>  
}  
  
</table> 
Now, we have done all the coding.
Step 7 - Run the Application
After running the Application, the UI of the Application will look like as follows


Now click on download button , then it will shows the following popup


Choose to open or save the file , I have chosen to open the files , the contents of the files will be look like as follows


I hope, from the preceding examples, you have learned, how to download binary formatted PDF files from database.
Note
  • This article used dapper ORM to interact with the database. Thus, you need to install dapper ORM into the Application. If you don't know how to install dapper ORM in MVC, watch the video, using the link, given below-
  • Makes changes in web.config file connectionstring tag, based on your database location and configuration.
  • Since this is a demo, it might not be using the proper standards. Thus, 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.

Read related article
Don't Forget To 

    Publishing ASP.NET MVC Application Using File System

    In my previous few articles we have learned how to create ASP.NET MVC web application, Now in this article, we will learn how to publish ASP.NET MVC Applications, using File system method, Let's start step by step, so beginners can also understand .
    What is Publishing ?
    Publishing is the process to make an application code suitable to host on the Server by merging multiple class codes into DLLs and by keeping only the required UI code .
    The web application goes through the following standard procedure before hosting to the Server.
    • Developing an application 
    • Publishing an application
    • Hosting an application on IIS
    Now, let's demonstrate the preceding concept step by step; i.e, how to publish ASP.NET Web MVC applications practically.
    Step 1: Create ASP.NET MVC application
    If you want to publish any application, first we need to create the Application. If you don't know how to create ASP.NET MVC application, Please refer to my previous articles and Video tutorials by using following links,
     Watch video which explains how to create ASP.NET MVC application step by step


    Step 2: Publish an Application
    Right click on the created ASP.NET MVC application, as shown below.


    Now, click the previously shown Publish option. It will open the Windows, mentioned below.
    Step 3: Select Publish Target
    Now, select the option, where you want to save the published code. There are lots of options given by Visual Studio to save the published code , as shown below.



    In the preceding image, we have seen that there are many options listed to publish the application, let's learn about them in brief.
    • Microsoft Azure App Service 
    This is one of the services provided by Microsoft Azure to publish the different types of the Application such as Web, mobile, REST and other into Azure (Cloud). You need a Microsoft Azure subscription to use this option.
    • Import 
    This option is used to publish the application, which is based on the predefined configuration files given by your hosting provider , which will directly publish the files in your hosting provider environment.
    • Custom
    This option is used to publish the Application into your own machine location or FTP Server, as per your most convenient way.
    • Microsoft Azure API apps 
    This option is used to publish REST API's Services in Cloud.
    • Microsoft Azure virtual machine 
    This option is used to publish the Application in Microsoft Azure virtual machine (Cloud), which requires an Azure subscription .
    There are many other options to publish the Application, using which we can publish .NET application.
    Step 4: Create Publish profile
    Now, in this article, we are going to publish an application in our local machine file system, so we need to choose custom target option. First, we need to create the profile to publish the application, so it can be a consistent setting to publish the same application in the future. Choose custom option from preceding explained target option, as shown below.


    Now, give the profile name as per your requirement and click ok. It will create the custom profile, as per your given name.
    Step 5: Choose Publish Method
    Now, configure the created profile, which decides where and how to publish the code. Select the publish method from the number of options, which are shown below.



    Now, choose the file system method from preceding methods and click Next button.
    Step 6: Choose File System Location
    Now, choose the target location of your local system in which you want to store the published code, as shown below.



    In the preceding screenshot, we have selected the target location E drive to store the published code. It may be different in your case, as per you machine, No click Next button .

    Step 7: Choose Publish code type
    Now, choose what type of code you want in production environment by choosing release or debug option, as shown below.


    Now, let's learn in brief about debug and release option.
    • Debug: Choose this option when you want to debug your application after the hosting.
    • Release: Choose this option when you do not want to debug the Application after hosting and make the application faster . 
    Step 8: Preview the configuration
    After the configuration, publish configuration tool will look like, as shown below.



    Now, click Publish option and watch the output Window, as shown below.


    Now, as shown in the preceding screenshot's highlighted section, our application code is published in our specified file system location. Now, navigate to the target folder and see the published code and files as shown below.


    Preceding is our published code, which is used to host in IIS. I hope from all the preceding examples, we learned how to publish ASP.NET MVC application in File system .
    Note
    • Choose the publish target location, as per your system.
    • Select proper publish method, as per your requirement.
    • These Publish steps are different in Visual Studio 2017
    Summary
    I hope, this article is useful for all the readers. If you have any suggestions, please contact me.

    Don't Forget To 

    Remote Validation in ASP.NET MVC

    This article explains the use of Remote attribute of Data Annotation in ASP.NET MVC with real time example, So lets learn step by step

    What is Remote Validation in ASP.NET MVC ?

    Remote is the attribute for validation in Data Annotation class which comes under System.web.mvc namespace, which is used in model class to validate records instantly. Let's demonstrate the above mentioned concept by creating sample ASP.NET MVC application.
    Step 1 - Create an MVC Application

    Now, let us start with a step-by-step approach from the creation of a simple MVC application.
    1. Go to "Start", followed by "All Programs" and select "Microsoft Visual Studio 2015".
    2. Click "File", followed by "New" and click "Project". 
    3. Select "ASP.NET Web Application Template", provide the Project a name of your choice and click OK. 
    After clicking, the following window will appear.

      Step 2 - Create Model Class

      Now, let us create the model class, named RegisterModel.cs, by right clicking on Models folder, as
      shown in the screenshot.


      After creating the class, write the following properties in RegisterModel.cs class.

      RegisterModel.cs

      using System.ComponentModel.DataAnnotations;  
      using System.Web.Mvc;  
        
      namespace RemoteValiDationInMVC.Models  
      {  
          public class RegisterModel  
          {  
                
              [Required]  
              [DataType(DataType.EmailAddress)]  
              [Display(Name ="Enter EmailId")]  
              //Using Remote validation attribute   
              [Remote("IsAlreadySigned", "Register",HttpMethod ="POST", ErrorMessage = "EmailId already exists in database.")]  
              public string UserEmailId { get; set; }  
              [Required]  
              public string Designation { get; set; }  
                       
              [Required]  
              [DataType(DataType.Password)]  
              [Display(Name =("Choose Password"))]  
              public string PassWord { get; set; }  
        
          }  
      }  

      In the preceding code, we have used Remote attribute for UserEmaiId model class method with some properties which are explained below.



      In the above image, we have defined a few properties of Remote attribute to work on remote validation properly, let's know them in brief.
      • IsAlreadySigned
        This is the JsonResult method which checks the details from database and returns true or false.
      • Register
        This is MVC Controller name and inside that, IsAlreadySigned JsonResult method is defined to check the details from database.
      • HttpMethod
        This is HttpMethod type which is called on Remote attribute e.g. Get , Put , Post. This is optional to define.
      • ErrorMessage
        This is used to show the message at client side. 
      There are many optional properties of Remote attribute which are used as per the validation requirements.
      Step 3 - Add Controller Class
      Now, let us add the MVC 5 Controller, as in the following screenshot.


      After clicking on Add button, it will show the window. Specify the Controller name as Register with suffix Controller. Now, modify the default code in RegisterController.cs class file and create JsonResult method. After modifying, the code will look like as follows.

      RegisterController.cs

      using System.Collections.Generic;  
      using System.Linq;  
      using System.Web.Mvc;  
      using RemoteValiDationInMVC.Models;  
        
      namespace RemoteValiDationInMVC.Controllers  
      {  
          public class RegisterController : Controller  
          {  
              // GET: Register  
              [HttpGet]  
              public ActionResult SignUp()  
              {  
                  return View();  
              }  
              [HttpPost]  
              public ActionResult SignUp(RegisterModel ObjModel )  
              {  
                  if (ModelState.IsValid)  
                  {  
        
                      return View();  
        
                  }  
                  else  
                  {  
        
                      return View();  
        
                  }  
        
              }  
              [HttpPost]  
              public JsonResult IsAlreadySigned(string UserEmailId)  
              {  
                    
                  return Json(IsUserAvailable(UserEmailId));  
        
              }  
              public bool IsUserAvailable(string EmailId)  
              {  
                  // Assume these details coming from database  
              List<RegisterModel> RegisterUsers = new List<RegisterModel>()  
              {  
        
                  new RegisterModel {UserEmailId="vithal.wadje@abc.com" ,PassWord="compilemode",Designation="SE"},  
                  new RegisterModel {UserEmailId="Sudhir@abc.com" ,PassWord="abc123",Designation="Software Dev"}  
        
              };  
                  var RegEmailId = (from u in RegisterUsers  
                                    where u.UserEmailId.ToUpper() == EmailId.ToUpper()  
                                    select new { EmailId }).FirstOrDefault();  
        
                  bool status;  
                  if (RegEmailId!=null)  
                  {  
                      //Already registered  
                      status = false;  
                  }  
                  else  
                  {  
                      //Available to use  
                     status = true;  
                  }  
                      
                  return status;  
            }            
          }  
        
      }  

      Note

      In the preceding code, IsAlreadySigned returns the JSON data at client side and it takes the UserEmailId as input parameter, the name of which, must match with the get set property defined under the remote attribute.

      Step 4 - Create View

      Now, let's create StronglyTyped View named SignUp from RegisterModel class.


      Click on Add button. It will create the View named SignUp. Now, open the SignUp.cshtml View. You will see some default code which is generated by MVC scaffolding template. Let's now, modify the default code to make as per our requirement.

      SignUp.cshtml

      @model RemoteValiDationInMVC.Models.RegisterModel  
        
      @{  
          ViewBag.Title = "SignUp";  
      }  
      @using (Html.BeginForm())  
      {  
          @Html.AntiForgeryToken()  
        
          <div class="form-horizontal">  
        
              <hr />  
              @Html.ValidationSummary(true, "", new { @class = "text-danger" })  
              <div class="form-group">  
                  @Html.LabelFor(model => model.UserEmailId, htmlAttributes: new { @class = "control-label col-md-2" })  
                  <div class="col-md-10">  
                      @Html.EditorFor(model => model.UserEmailId, new { htmlAttributes = new { @class = "form-control" } })  
                      @Html.ValidationMessageFor(model => model.UserEmailId, "", new { @class = "text-danger" })  
                  </div>  
              </div>  
        
              <div class="form-group">  
                  @Html.LabelFor(model => model.Designation, htmlAttributes: new { @class = "control-label col-md-2" })  
                  <div class="col-md-10">  
                      @Html.EditorFor(model => model.Designation, new { htmlAttributes = new { @class = "form-control" } })  
                      @Html.ValidationMessageFor(model => model.Designation, "", new { @class = "text-danger" })  
                  </div>  
              </div>  
        
              <div class="form-group">  
                  @Html.LabelFor(model => model.PassWord, htmlAttributes: new { @class = "control-label col-md-2" })  
                  <div class="col-md-10">  
                      @Html.EditorFor(model => model.PassWord, new { htmlAttributes = new { @class = "form-control" } })  
                      @Html.ValidationMessageFor(model => model.PassWord, "", new { @class = "text-danger" })  
                  </div>  
              </div>  
        
              <div class="form-group">  
                  <div class="col-md-offset-2 col-md-10">  
                      <input type="submit" value="SignUp" class="btn btn-primary" />  
                  </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>  

      After adding the Model, add View and Controller into our project, the Solution Explorer will look like the following.


      Step - 5 

      Now, run the application and type emaild id which is already in the  list that we defined in the RegisterController class.

      Now, without posting the form, it's showing instantly that the given email id already exists in the database, This approach will save a lot of time for the user in unnecessary validation.
      Note
      • Since this is a demo, it might not be using proper standards. So, improve it as per your own skills.
      Summary
      I hope this article is useful for all readers. If you have any suggestions, please contact me.
      Don't Forget To 

      Calling Another Controller Action Method Using ActionLink in ASP.NET MVC

      This article explains how to call another controller action method using ActionLink in ASP.NET MVC. we need to pass the following parameters in ActionLink are as follows
      @Html.ActionLink("Index", "ActionName", "ControllerName", null, new { id = "OT",
      style = "color: white" })
      In the above code
      •     Index is the ActionLink Name
      •     ActionName is the View name or ActionResult Name.
      •     ControllerName will be name of our controller .
      after implementing the  in parent view entire code will be look like as follows

      Parentview.Cshtml

      @{
          ViewBag.Title = "Parentview";
      }
      
      <h2>Parentview</h2>
      @*To call same same controller view*@
      @Html.ActionLink("Index", "ActionName")
      
      @*To call another controller view*@
      @Html.ActionLink("Index", "ActionName", "ControllerName", null, new { id = "OT",
       style = "color: white" })
      Summary
      I hope this article is useful for all readers. If you have any suggestion then please contact me.

      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 

      www.CodeNirvana.in

      Protected by Copyscape Online Copyright Protection
      Copyright © Compile Mode