Nothing Special   »   [go: up one dir, main page]

How To Get Return Value Using SQL Stored Procedure

In this article, we will learn how to get the return value using the SQL stored procedure Out parameter. There are some scenarios where we need the return value using the SQL stored procedure such as whenever a new order is created into the database, then return an OrderId to the user for reference.

Let's consider the similar scenario and we have the following table OrderDetails which maintains the processed orders as shown in the following image.


I hope you have created the same table structure as shown above. Now create the stored procedures to get the return value as in the following code snippet.

Create PROCEDURE PlaceOrder  
(  
@Product varchar(50),  
@Amount decimal(18,2),  
@OrderId int out  
)  
AS  
BEGIN  
SET NOCOUNT ON;  
  
INSERT INTO [dbo].[OrderDetails]  
           (  
            Product,  
            Amount
           )  
     VALUES  
           (  
          @Product,  
          @Amount
           )  
select @OrderId=isnull(max(OrderId),0) from OrderDetails

END 

Now execute the stored procedure using the SQL editor as follows.



In the preceding, The stored procedure inserts the new order into the SQL table and return the out value as OrderId using the stored procedure.
Summary
I hope from the preceding explanation, you have learned how to return the value using the SQL stored procedure. If you have any queries, then you can send using the following comment box.
Related articles

How To Delete Records From Azure SQL Table

In this article we will learn how to delete the records from the Azure SQL table using step by step, so beginners and students can also understand.Prerequisites

If you are new to the Azure SQL, then please watch my following videos on the Azure SQL database.

How To Create Azure SQL DatabaseHow To Connect Azure SQL Database

Let us consider we have the Employee table in our Azure SQL Server database having the following records,


The following two commands are used to delete the records from the Azure SQL table.

  • Delete
  • Truncate
Using Delete CommandThe delete command is used to delete all the records from the table or specific records by using the Where clause.
What does Delete Command Do?
  • The Delete command can be used with or without a WHERE clause.
  • The Delete command removes all records if you use it without the where condition.
  • The Delete command removes the one or more rows based on the condition given in the Where clause.
  • The Delete command maintains the logs of each deleted record. This helps to keep track of each deleted record.
  • The Delete command activates triggers. It means you can take certain action whenever you delete the record, such as inserting the deleted records in some other table etc.
  • The Delete can be rolled back. It means that if the records are deleted by mistake, then we can restore the deleted record back to the table.
  • The Delete command does not reset the identity of the table. It means that when you delete all the records from the table having Id between 1 and 10, then after adding the new record in the table, it will start with the ID count from 11, not 1.
Deleting Specific Records
To delete the specific record from the table, we need to use the where clause.

Syntax

delete from TableName where ColumName=value

Example

delete from Employee where Id=1

The above query will delete the records from the Employee table which has id 1. After deleting the record, then the table records will look like as follows.

Deleting all records
To delete all records from the table, we need to write a query without the where clause.

Example

delete from Employee

The above query will delete all records from the Employee table.Using Truncate command
The truncate command removes all the rows from a table.

What does Truncate Command Do?

  • Truncate removes all rows from a table, but the table structure such as columns, constraints, indexes remains. 
  • Truncate command can not be used with where or any condition.
  • Truncate command can not be activates the trigger, It means we can not take any action on deleted records.
  • Truncate cannot be rolled back, it means deleted records from the table cannot be restored.
  • Truncate resets the identity of the table, it means that when you delete all the records from the table having Id between 1 and 10, then after adding the new record in the table, it will start with the ID count from 1, not 11.
  • Truncate command can not be maintains the logs of deleted record. It means we can not keep the track of each deleted record.
Example

truncate table Employee

The preceding query deletes all the records from the table.
Summary
I hope this article helped to know how to delete records from the Azure SQL database. If you have a suggestion related to the article, then please comment using the comment box.

Azure SQL Alter Table Statement

In this article we will learn about the Azure SQL alter statement. In the previous article we have learned how create and connect to the Azure SQL database. If you are new to the Azure SQL, then please watch my following videos on the Azure SQL database.

How To Create Azure SQL DataBaseHow To Connect Azure SQL DataBase Using SQL Management Studio

What is SQL Alter Table Statement?

Alter statement used to modify the structure of the existing table. The alter statement used is to add, drop the table column. It can also be used to change the data types of the column etc.
Let us consider we have the table having name Employee in our Azure SQL Server database having the following records,



Add Column To Existing Table Using Alter Statement

The following SQL query will add the new column Salary into the existing table.

ALTER TABLE Employee
ADD salary  decimal(50);

You can alter the multiple columns at a time, the following query will alter the multiple columns of the existing table.

ALTER TABLE Employee ADD
(
Location varchar (50),
EmailID Varchar (50)
)

Modify The Existing Table Column
You can rename or change the data type of the column using the alter statement.
Example
ALTER TABLE Employee 
MODIFY Salary  decimal(60);

The preceding query will change the Salary column data type size to 60 from previous 50.
Drop The Column of Existing Table

ALTER TABLE Employee
DROP COLUMN Salary;

The preceding SQL query will remove the Salary column from the existing Employee table.
Summary
I hope from all the above demonstration, you have learned about the Azure SQL Alter statement and its uses of the Alter statement.

Related Article

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 {  })
</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 

My Upcoming Speaking : Building Distributed Architecture using ASP.NET Web API with Azure SQL Database

Speaking at C# Corner Pune Chapter event on Building Distributed Architecture using ASP.NET Web API with Azure SQL Database , If you wants to learn how to build and decide architecture for application  then join my interactive session on Building Distributed Architecture using ASP.NET Web API with Azure SQL Database.

Agenda

 Date : 19-11-2016


Building Distributed Architecture using ASP.NET Web API with Azure SQL Database
  • User Story
  • What is a Distributed Architecture?
  • Tier Vs Layer Architecture
  • WCF REST Vs Web API REST?
  • Designing & understanding Distributed architecture diagram
  • Building REST Service Layer
  • Building Business Logic Layer
  • Building Entity Layer
  • Building Data Access Layer with Dapper ORM
  • Creating Azure SQL database
  • Connecting Data Access Layer with Azure SQL database
  • Building Client Layer with ASP.NET MVC
  • Hosting layers on different machines
  • Consuming Web API REST Services in ASP.NET MVC using HttpClient
  • Consuming Web API REST Services in Windows Phone and Windows Application
  • Q & A


        Where:

        Fujitsu Consulting India
        A-15, IT Tower, M I D C Technology Park, 
        Talwade , Pimpri-Chinchwad - 412114
        Pune Maharashtra INDIA


        Price: Free of cost

        Requirement: Optional to bring your laptop and internet card.

        Date : 19-11-2016
        Time :04:00 PM - 05:30 PM

                                      Register Now

        Don't forget to connect with me

        www.CodeNirvana.in

        Protected by Copyscape
        Copyright © Compilemode