MENU

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

Filed Under: , , , , , , , on
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 

Post a Comment

www.CodeNirvana.in

Protected by Copyscape
Copyright © Compilemode