Posted on

CRUD Operations using AngularJS and WebAPI with MVC

Steps to build MVC CRUD operations using AngularJS and WebAPI

Step1: 

Create a MVC web application in visual studio

Step-2: 

Create a MDF file inside APP_Data folder.

Step-3: 

 Now create a table and single stored procedure to perform CRUD operations.

 

CREATE TABLE [dbo].[Employee]
(
       [Id] INT NOT NULL IDENTITY (1,1),
       [Name] nvarchar(50) Not null,
       [Address] nvarchar(50) not null,
       [Country] nvarchar(50) not null,
       [City] nvarchar(50) not null,
       [Mobile] nvarchar(10) not null,
       PRIMARY KEY CLUSTERED([Id] ASC)
);

Stored Procedure for CRUD Operation

CREATE PROCEDURE USP_InsUpdDelEmployee
@id int,
@name nvarchar(50),
@address nvarchar(50),
@Country nvarchar(50),
@city nvarchar(50),
@mobile nvarchar(50),
@type varchar(10)
 
As
Begin
if (@type='Ins') Begin
Insert into Employee
Values (@name,@address,@Country,@city,@mobile)
  End
  if(@type='Upd') Begin
  Update Employee Set Name=@name, Address=@address, Country=@Country, 
City=@city,Mobile=@mobile where Id=@id
  End
  if(@type='Del') Begin
  Delete from Employee where Id=@id
  end
  if(@type='GetById') Begin
 
  Select * from Employee where Id=@id
  End
  Select * From Employee
  End

 

Step-4:

Right click on models and create entity-data model then automatically connection will be created in web.config file.

Step-5:

Right click on controllers folder,create webapi empty controller and paste below code

public class EmployeeAPIController : ApiController
{

[HttpGet]
public List Get()
{
List emplist = new List();
using (MydatabaseEntities dc = new MydatabaseEntities())
{
var results = dc.USP_InsUpdDelEmployee(0, "", "", "", "", "", "Get").ToList();
foreach(var result in results)
{
var employee = new Employee()
{
Id = result.Id,
Name = result.Name,
Address = result.Address,
Country = result.Country,
City = result.City,
Mobile = result.Mobile
};
emplist.Add(employee);
}
return emplist;
}
}

public Employee Get(int id)
{
using (MydatabaseEntities dc = new MydatabaseEntities())
{
Employee employee = dc.Employees.Find(id);
if (employee == null)
{
throw newHttpResponseException(Request.CreateResponse(HttpStatusCode.NotFound));
}
return employee;
}
}

public HttpResponseMessage Post(Employee employee)
{
if (ModelState.IsValid)
{
using (MydatabaseEntities dc = new MydatabaseEntities())
{
var emplist = dc.USP_InsUpdDelEmployee
(0, employee.Name, employee.Address, employee.Country, employee.City, employee.Mobile,"Ins").ToList();
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Created, emplist);
return response;
}
} else
{
return Request.CreateResponse(HttpStatusCode.BadRequest, ModelState);
}
}

public HttpResponseMessage Put(Employee employee)
{
List emplist = newList();
if (!ModelState.IsValid)
{
return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ModelState);
}
using(MydatabaseEntities dc=new MydatabaseEntities())
{
try
{
emplist = dc.USP_InsUpdDelEmployee
(employee.Id, employee.Name, employee.Address, employee.Country, employee.City, employee.Mobile, "Upd").ToList();

}
catch (DbUpdateConcurrencyException ex)
{

returnRequest.CreateErrorResponse(HttpStatusCode.NotFound,ex);
}
return Request.CreateResponse(HttpStatusCode.OK, emplist);
}
}

public HttpResponseMessage Delete(int id)
{
using(MydatabaseEntities dc=new MydatabaseEntities())
{
List emplist = newList();
var results = dc.USP_InsUpdDelEmployee(id, "", "", "", "", "", "GetById").ToList();
if (results.Count==0)
{
return Request.CreateResponse(HttpStatusCode.NotFound);
}
try
{
emplist = dc.USP_InsUpdDelEmployee(id, "", "", "", "", "", "Del").ToList();
}
catch (DbUpdateConcurrencyException ex)
{
return Request.CreateErrorResponse(HttpStatusCode.NotFound, ex);
throw;
}
return Request.CreateResponse(HttpStatusCode.OK, emplist);
}
}

protected override void Dispose(bool disposing)
{
using (MydatabaseEntities dc = new MydatabaseEntities())
dc.Dispose();
base.Dispose(disposing);
}
}
}

Step-6:

Create another controller for MVC to create view.

 


public class TestController : Controller
    {
        // GET: Test
        public ActionResult Index()
        {
            return View();
        }
    }

Step-7:

Load angular js files from this url: https://www.angularjs.org/ (or) use CDN files.

Step-8:

Create javascript file to write angular code like below.


var app = angular.module('myapp', []);
app.controller('employeeController', ['$scope', '$http', employeeController]);
 
function employeeController($scope,$http)
{
    $scope.loading = true;
    $scope.updateShow = false;
    $scope.addShow = true;
    $http.get('/api/EmployeeAPI', this.newemployee).success(function (data) {
        $scope.employees = data;
    }).error(function () {
        $scope.error = 'An error has occured while posting';
    });
 
    $scope.add = function () {
        $scope.loading = true;
        $http.post('/api/EmployeeAPI', this.newemployee).success(function (data) {
            $scope.employees = data;
            $scope.updateShow = false;
            $scope.addShow = true;
            $scope.newemployee = '';
        }).error(function (data) {
            $scope.error = "error while saving" + data;
        });
    }
 
    $scope.edit = function () {
        var Id = this.employee.Id;
        $http.get('/api/EmployeeAPI/' + Id).success(function (data) {
            $scope.newemployee = data;
            $scope.updateShow = true;
            $scope.addShow = false;
        }).error(function () {
            $scope.error = "An Error has occured while loading posts!";
        });
    }
    $scope.update = function () {
        $scope.loading = true;
        console.log(this.newemployee);
        $http.put('/api/EmployeeAPI/', this.newemployee).success(function (data) {
            $scope.employees = data;
            $scope.updateShow = false;
            $scope.addShow = true;
            $scope.newemployee = '';
        }).error(function (data) {
            $scope.error = "An Error has occured while Updating employee! " + data;
        });
    }
    $scope.delete = function () {
        var Id = this.employee.Id;
        $scope.loading = true;
        $http.delete('/api/EmployeeAPI/' + Id).success(function (data) {
            $scope.employees = data;
        }).error(function (data) {
            $scope.error = "An Error has occured while Delete employee! " + data;
        });
    }
    $scope.cancel = function () {
        $scope.updateShow = false;
        $scope.addShow = true;
        $scope.newemployee = '';
    }
}

Step-9:

Create an index view and paste below code

 

@{

    Layout = “~/Views/Shared/_Layout.cshtml”;

}

<html ng-app=”myapp”>

<head>

    <title>MVC AngularJs With WebApi</title>

</head>

<body>

    <div ng-controller=”employeeController“ class=”container”>

        <div class=”row”>

            <div class=”col-md-12″>

                <h3 class=”header”>EMPLOYEE CRUD USING ANGULARJS AND WEBAPI</h3>

            </div>

        </div>

        <div class=”row”>

            <div class=”col-md-12″>

                <strong class=”error”>{{error}}</strong>

                <form name=”addemployee” style=”width: 600px; margin: 0px auto;“>

                    <div class=”form-group”>

                        <label for=”cname” class=”col-sm-2 control-label”>Name:</label>

                        <div class=”col-sm-10 space”>

                            <input type=”text” class=”form-control” id=”cname”placeholder=”please enter your name” ng-model=”newemployee.Name“ required />

                        </div>

                    </div>

                    <div class=”form-group”>

                        <label for=”address” class=”col-sm-2 control-label”>Address:</label>

                        <div class=”col-sm-10 space”>

                            <textarea class=”form-control” id=”address”placeholder=”please enter your address” ng-model=”newemployee.Address“ required></textarea>

                        </div>

                    </div>

                    <div class=”form-group”>

                        <label for=”country” class=”col-sm-2 control-label”>Domicile:</label>

                        <div class=”col-sm-10 space”>

                            <input type=”text” class=”form-control” id=”country”placeholder=”please enter your native” ng-model=”newemployee.Country“ required />

                        </div>

                    </div>

                    <div class=”form-group”>

                        <label for=”city” class=”col-sm-2 control-label”>Town:</label>

                        <div class=”col-sm-10 space”>

                            <input type=”text” class=”form-control” id=”city”placeholder=”please enter your town” ng-model=”newemployee.City“ required />

                        </div>

                    </div>

                    <div class=”form-group”>

                        <label for=”mobile” class=”col-sm-2 control-label”>Contact:</label>

                        <div class=”col-sm-10 space”>

                            <input type=”text” class=”form-control” id=”mobile”placeholder=”please enter your Contact no.” ng-model=”newemployee.Mobile“required />

                        </div>

                    </div>

                    <br />

                    <div class=”form-group space”>

                        <div class=”col-sm-offset-2 col-sm-10″>

                            <input type=”submit” value=”Insert” ng-click=”add()“ng-show=”addShow“ ng-disabled=”!addemployee.$valid“ class=”btn btn-primary” />

                            <input type=”submit” value=”Update” ng-click=”update()“ ng-show=”updateShow“ ng-disabled=”!addemployee.$valid“class=”btn btn-primary” />

                            <input type=”button” value=”Reset” ng-click=”cancel()“ class=”btn btn-primary” />

                        </div>

                    </div>

                    <br />

                </form>

            </div>

        </div>

        <div class=”row”>

            <div class=”col-md-12″>

                <div class=”table-responsive”>

                    <table class=”table table-bordered table-hover” style=”width: 800px; margin-left: 170px;“>

                        <tr>

                            <th>Name</th>

                            <th>Address</th>

                            <th>Domicile</th>

                            <th>Town</th>

                            <th>Contact</th>

                            <th>Actions</th>

                        </tr>

                        <tr ng-repeat=”employee in employees“>

                            <td>

                                <p>{{ employee.Name }}</p>

                            </td>

                            <td>

                                <p>{{ employee.Address }}</p>

                            </td>

                            <td>

                                <p>{{ employee.Country }}</p>

                            </td>

                            <td>

                                <p>{{ employee.City }}</p>

                            </td>

                            <td>

                                <p>{{ employee.Mobile }}</p>

                            </td>

                            <td>

                                <p><a ng-click=”edit()“href=”javascript:void(0);”>Edit</a> | <a ng-click=”delete()“href=”javascript:void(0);”>Delete</a></p>

                            </td>

                        </tr>

                    </table>

                </div>

            </div>

        </div>

    </div>

    

</body>

</html>  

 

Step-10:

Finally run the project and you get the output as you expected.

Good Luck:)

Posted on

SQL SERVER – Learning new multipurpose FORMAT function

In versions prior to 2012, you need to use the CONVERT function with different styles to convert the date values into a different format. Let us learn about the FORMAT function here.

Let us create this simple dataset

CREATE TABLE #date_test(dates DATETIME)

INSERT INTO #date_test(dates)

SELECT ‘2016-10-19 10:22:34’ AS DateTest

Executing above query will return no result, but it will create a temporary table with one row.

Suppose you want to format the dates in either dd/mm/yyyy or mm/dd/yyyy format. You can do it by

SELECT dates,CONVERT(VARCHAR(10),dates,101) AS [dates_mm/dd//yyyy]

FROM #date_test

SELECT dates,CONVERT(VARCHAR(10),dates,103) AS [dates_dd/mm/yyyy]

FROM #date_test

Executing above query will return following result:

If you want to show only month name, you can use

SELECT DATENAME(month,dates) AS [month_name] FROM #date_test

Executing above query will return following result:

If you want to show only day name, you can use

SELECT DATENAME(weekday,dates) AS [day_name] FROM #date_test

Executing above query will return following result:

As you can see, you need to use either CONVERT or different functions based on what you want to do. This requires the usage of many different functions.

But with version 2012 it is very easy. All you need to do is just make use of FORMAT function

SELECT dates,FORMAT(dates,’dd/MM/yyyy’) AS [dates_dd/mm/yyyy] FROM #date_test

 

SELECT dates,FORMAT(dates,’MM/dd/yyyy’) AS [dates_mm/dd/yyyy] FROM #date_test

Executing above query will return following result:

Just use change the second parameter accordingly

To display month name, or day name

SELECT dates,FORMAT(dates,’MMMM’) AS [month_name] FROM #date_test

 

SELECT dates,FORMAT(dates,’dddd’) AS [day_name] FROM #date_test

Executing above query will return following result:

You can also display long formatted dates with customized text included

SELECT dates,FORMAT(dates,'”The given date is ” MMMM d, yyyy’) AS long_format

FROM #date_test

Executing above query will return following result:

Now let us run following script

SELECT dates,FORMAT(dates,'”This is generated on” M/d/y “at” hh:mm:ss’) AS long_format FROM #date_test

Executing above query will return following result:

Not only you can do date time formatting, you can also do number formatting using the FORMAT function.

Suppose you want to always make 6 digits number and if the length is less than 6, you want to prefix leading zeroes to make the total length as 6, you can do it by

SELECT 935 AS [number], FORMAT(935,’000000′) AS length_6

Executing above query will return following result:

I believe this new function FORMAT is very handy function and after using this, you may not have to remember many different functions to the same task.

Posted on

Multilevel Grid in MVC using Jquery

Steps To build Multilevel Grid in MVC

Step1:- Create Empty MVC application in visual studio

Step-2: – Create a MDF file inside App_Data folder.

Step-3:- Now create a table for retrieving the data here i took employees as table name.

CREATE TABLE [dbo].[Employees] (

   [EmployeeID]     INT           NOT NULL,

   [LastName]        NVARCHAR (50) NULL,

   [FirstName]       NVARCHAR (50) NULL,

   [Title]           NVARCHAR (50) NULL,

   [TitleOfCourtesy] NVARCHAR (50) NULL,

   [BirthDate]       DATETIME      NULL,

   [HomePhone]       NVARCHAR (50) NULL,

   [ReportsTo]       INT           NULL,

   CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ([EmployeeID] ASC)

);

 After creating the table insert some data in that table like this

If you observe the above table, we have “ReportsTo” column this column contains EmployeeID values and based on this column we can assign employees to CEO, Project manager and Team Leader.

Step-4:- Create one ADO.Net Entity Model to Employees table. In solution you can see my entity model in below snippet.

 

Step-6:- Right click on controllers folder, create Controller.

public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
List<Employee> list = new List<Employee>();
using (MyDatabaseEntities dc = new MyDatabaseEntities())
{
list = dc.Employees.OrderBy(a => a.EmployeeID).ToList();
}
return View(list);

}
}

MyDatabaseEntities is class which inherited by DbContext. using “dc” we retrieve all employees details and assign to the list.

Employee.cs:-

public string HomePhone { get; set; }

public Nullable<int> ReportsTo { get; set; }

}

Step7:- Now Right click on Index method of HomeController and add view.

 

Index.cshtml:-

 

@model List<MultilevelGrid.Employee>

 

<h2>Display Multilevel Grid</h2>

 

@* Jquery for Collapse/Expend *@

<script src="http://ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js"></script>

<script type="text/javascript">

$(document).ready(function () {

$('.icon').on('click', function () {

var $pRow = $(this).parents('tr');

var $nextRow = $pRow.next('tr');

$nextRow.toggle();

$(this).toggleClass('icon-s icon-e');

});

})

</script>

 

@* CSS for dispalay the grid *@

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />

<style>

.container {

min-width: 500px;

overflow: hidden;

}

.innerTable table {

margin: 0px auto;

border-left-width: 4px;

}

td.innerTable {

padding-left: 20px !important;

}

.icon {

background-repeat: no-repeat;

width: 16px;

height: 16px;

display: inline-block;

float: left;

color: navy;

}

.icon-e {

background-position: -32px -16px;

}

.icon-s {

background-position: -64px -16px;

}

</style>

 

@*Call NetedData.cshtml Page for displaying the grid*@

<div class="container">

@NestedData.GetHtml(Model, 0)

</div>

 

 

       

In above view we add Employee.cs file as model at top of Index.cshtml. End of the coding we call the NestedData.cshtml page.

 

Step8:- Create NestedData.cshtml page for displaying the multilevel grid.

 

@helper GetHtml(List<MultilevelGrid.Employee> employeeList, int parentID)

{

 <table class=”table table-bordered”>

   @{

     int currentID = 0;

     int rowIndex = 0;

     foreach (var i in employeeList.Where(a => a.ReportsTo.Equals(parentID)))

      {

        if (i.EmployeeID == currentID)

         {

           continue;

         }

        else

         {

          if (rowIndex == 0)

            {

              <thead>

                <tr>

                   <th>Employee ID</th>

                   <th>Employee Name</th>

                   <th>Designation</th>

                   <th>Contact Number</th>

                </tr>

               </thead>

             }

        rowIndex++;

        currentID = i.EmployeeID;

         var Sub = employeeList.Where(a =>                                                                                                                                                                                                    a.ReportsTo.Equals(i.EmployeeID)).ToList();

        var newEmployeeList = employeeList.

 Where(a => !a.EmployeeID.Equals(i.EmployeeID)).ToList();

        <tbody>

          <tr>

            <td>

               @if (Sub.Count > 0)

                {

                 <a class=”icon icon-e” style=”float:left; cursor:pointer;”>>></a>

                }

               else

                {

                 <span style=”display:inline-block;width:14px”>&nbsp;</span>

                }

                 @i.EmployeeID

                 </td>

                 <td>@i.TitleOfCourtesy @i.FirstName @i.LastName</td>

                 <td>@i.Title</td>

                 <td>@i.HomePhone</td>

            </tr>

            <tr style=”display:none;”>

                 @if (Sub.Count > 0)

                  {

                    <td colspan=”4″ class=”innerTable”>

                      @NestedData.GetHtml(newEmployeeList, i.EmployeeID)

                    </td>

                   }

                  else

                   {

                     <td colspan=”4″></td>

                   }

              </tr>

         </tbody>

        }

      }

    }

</table>

}

 

In above code we retrieve the two categories of employees based “ReportsTo” column.i.e:

 

1)var Sub = employeeList.Where

(a => a.ReportsTo.Equals(i.EmployeeID)).ToList();

2) var newEmployeeList = employeeList.Where

(a =>!a.EmployeeID.Equals(i.EmployeeID)).ToList();

 

Step9:- Now run the application, by default the application load like this.

If you click on “>>” button, the next row will be expanded like this.

 

The above output indicates, the Project manager and Team Leader are working under CEO. Again if you click on second EmployeeID it will be expanded.

 

 

It’s indicates the Jr.Developer works under Project Manager.

 

This all explains how multilevel grid worked on employees table. Thanks!

Posted on

Passing XML as a parameter in stored procedure and inserting using ADO.NET code with Example In MVC

XML_Stored_Proc

if we have number of parameters. We can append all parameters to xml parameter and passing as a single parameter in xml. This if helpful for more performance to a stored procedure.

Follow the Below steps 

Step-1: Create Table

Table

Step-2:  Create Procedure like below

CREATE PROCEDURE  [dbo].[USP_XMLInsert]
(
 @xmlx xml
)
AS
BEGIN
 SET NOCOUNT ON;
      INSERT INTO Tbl_Customer
      SELECT
      Customer.value(‘(Name/text())[1]’,’NVARCHAR(50)‘) as Name , –TAG
      Customer.value(‘(City/text())[1]’,’NVARCHAR(50)‘) as City, –TAG
      Customer.value(‘(IndustryType/text())[1]’,’NVARCHAR(50)‘) as IndustryType –TAG
      FROM
      @xmlx.nodes(‘/Customer‘)AS TEMPTABLE(Customer)

END

GO

Step 3: Create sample MVC application and create model class in models folder and create properties like below;

namespace Insert_XMLPROC.Models
{
    public class Customer
    {
        public string Name { get; set; }
        public string City { get; set; }
        public string IndustryType { get; set; }
    }
}

Step 4: Create controller in controller folder and write insert logic

    public class CustomerController : Controller
    {
        // GET: Customer
        public ActionResult Index()
        {
            return View();
        }

        [HttpPost]
        public ActionResult Index(Customer objcustomer)
        {
            StringBuilder objstring = new StringBuilder();
            objstring.Append(“<Customer>“);
            objstring.Append(“<Name>“);
            objstring.Append(objcustomer.Name);
            objstring.Append(“</Name>“);
            objstring.Append(“<City>“);
            objstring.Append(objcustomer.City);
            objstring.Append(“</City>“);
            objstring.Append(“<IndustryType>“);
            objstring.Append(objcustomer.IndustryType);
            objstring.Append(“</IndustryType>“);
            objstring.Append(“</Customer>“);

            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(objstring.ToString());

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“MyConnection“].ToString());
            con.Open();
            SqlCommand cmd = new SqlCommand(“USP_XMLInsert“, con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue(“@xmlx“, xmlDoc.InnerXml);
            cmd.ExecuteNonQuery();
            return View();
        }
    }


Step 5: Create index view and write insert logic

@model Insert_XMLPROC.Models.Customer

@{
    ViewBag.Title = “Index“;
}

<h2>Index</h2>

<script src=“~/Scripts/jquery-1.10.2.js”></script>
<script src=”~/Scripts/jquery-1.10.2.min.js“></script>
<div>
    <table>
        <tr>
            <td>Name</td>
            <td>
                <input type=”text” id=”txtName” />
            </td>
        </tr>
        <tr>
            <td>City</td>
            <td>
                <input type=”text” id=”txtCity” />
            </td>
        </tr>
        <tr>
            <td>IndustryType</td>
            <td>
                <input type=”text” id=”txtIndustryType” />
            </td>
        </tr>
        <tr>
            <td>
                <input type=”button” id=”btnsubmit” value=”Save” />
            </td>
        </tr>
    </table>

    <script type=“text/javascript”>
        $(document).ready(function () {
            $(‘#btnsubmit‘).click(function () {
                $.ajax(
                {
                    type: “POST“,
                    url: ‘/Customer/Index‘,
                    data: {
                        Name: $(‘#txtName‘).val(),
                        City: $(‘#txtCity‘).val(),
                        IndustryType: $(‘#txtIndustryType‘).val(),
                    }

                });

            });
        });
    </script>
</div>