How to Call the Store Procedure in Entity Framework for CRUD operation


Hi All,

This is just the extension of CRUD operation in Asp.net MVC using AngularJs

In the previous post we show that we are doing database operation using Entity Framework with LINQ query. But if we have some complex logic which require multiple tables to be join then we write the logic in store procedure. So entity framework also provide the feature to map the SP in edmx file.

Now we will go by step wise for this task

Step 1: Write the SP in Database like this

CREATE PROCEDURE DeleteDept
    @Id int
	
AS
Begin
	Delete from  tblDept where Id=@Id
END
RETURN

---------------------------------------------
CREATE PROCEDURE FetchDept_OnId
    @Id int
	
AS
Begin
	Select * from  tblDept where Id=@Id
END
RETURN

---------------------------------------------
CREATE PROCEDURE FetchDeptDetails
	
AS
	SELECT * from tblDept
RETURN
-----------------------------------------
CREATE PROCEDURE InsertDept
	@DeptName Varchar(250),
	@DeptDesc Varchar(500)
AS
Begin
	Insert into tblDept(DeptName,DeptDesc) Values(@DeptName,@DeptDesc)
END
RETURN
------------------------------------------
CREATE PROCEDURE UpdateDept
    @Id int,
	@DeptName Varchar(250),
	@DeptDesc Varchar(500)
AS
Begin
	Update  tblDept Set DeptName=@DeptName, DeptDesc= @DeptDesc where Id=@Id
END
RETURN

Step 2:
Do like my previous article How to fetch data from SP in EF

Step 3: Now select the Context.tt and Model.tt file of Edmx file and run the “Run Custom Tool” like this

This will generate the DAL layer code with SP for Insert/Update/Read/Delete functionality.

Step 4: Now go to the Dept Controller and write the code for calling SP like this

using AngularCRUD.Models;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;

namespace AngularCRUD.Controllers
{
    public class DeptController : Controller
    {
        // GET: /Dept/
        string msg = string.Empty;
        public ActionResult Index()
        {
            return View();
        }

        public JsonResult Get_AllDepts()
        {
            using (Database1Entities obj = new Database1Entities())
            {
                var  objDept = obj.FetchDeptDetails().ToList();
                return Json(objDept, JsonRequestBehavior.AllowGet);
            }
        }

        public JsonResult Get_DeptById(string Id)
        {
            using (Database1Entities obj = new Database1Entities())
            {
                int DeptId = int.Parse(Id);
                return Json(obj.FetchDept_OnId(DeptId), JsonRequestBehavior.AllowGet);
            }
        }

        public string Insert_Dept(tblDept dept)
        {
            if (dept != null)
            {

                using (Database1Entities obj = new Database1Entities())
                {
                    int flag= obj.InsertDept(dept.DeptName,dept.DeptDesc);
                    if (flag==1)
                    {
                      msg=  "Dept details Added Successfully";
                    }
                    return msg;
                }
            }
            else
            {
                return "Dept Details Not Inserted! Try Again";
            }

        }

        public string Update_Dept(tblDept Dept)
        {
            if (Dept != null)
            {
                
                using (Database1Entities Obj = new Database1Entities())
                {
                    int flag = Obj.UpdateDept(Dept.Id, Dept.DeptName, Dept.DeptDesc);
                    if (flag==1)
                    {
                        msg = "Dept details Updated Successfully";
                    }
                    return msg;
                }
            }
            else
            {
                return "Dept Details Not Updated! Try Again";
            }
        }  

        public string Delete_Dept(tblDept dept) 
        {
            if (dept != null)
            {
               
                using (Database1Entities obj = new Database1Entities())
                {
                   
                   int flag= obj.DeleteDept(dept.Id);

                   if (flag==1)
                   {
                       msg= "Dept details deleted Successfully";
                   }
                   return msg;
                }
            }
            else
            {
                return "Dept Details Not Deleted! Try Again";
            }
        }
    }
}

Advertisements

How to do CRUD operation in Asp.net MVC using AngularJs


Hi All,

In this post, we will see how to do CRUD(Create/Read/update/Delete) operation with database in Asp.net MVC using AngularJs and Bootstrap.

Step 1: Create the Empty MVC application.

Step 2: Right click on project and Go to the “Manage Nuget Package” and Install the Angularjs like this

Step 3: Create the database in application and add the tblDept table like this

Step 4: Go to the Model folder and Add the EntityFramework Model and map the table like this

Step 5: Go to the Controller folder and Add the Empty Dept Controller and write the code for doing Insert/Update/Delete/Fetch functionality like this.

using AngularCRUD.Models;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;

namespace AngularCRUD.Controllers
{
    public class DeptController : Controller
    {
        // GET: /Dept/

        public ActionResult Index()
        {
            return View();
        }

        public JsonResult Get_AllDepts()
        {
            using (Database1Entities obj = new Database1Entities())
            {
                List<tblDept> objDept = obj.tblDepts.ToList();
                return Json(objDept, JsonRequestBehavior.AllowGet);
            }
        }

        public JsonResult Get_DeptById(string Id)
        {
            using (Database1Entities obj = new Database1Entities())
            {
                int DeptId = int.Parse(Id);
                return Json(obj.tblDepts.Find(DeptId), JsonRequestBehavior.AllowGet);
            }
        }

        public string Insert_Dept(tblDept dept)
        {
            if (dept != null)
            {

                using (Database1Entities obj = new Database1Entities())
                {
                    obj.tblDepts.Add(dept);
                    obj.SaveChanges();
                    return "Dept details Added Successfully";
                }
            }
            else
            {
                return "Dept Details Not Inserted! Try Again";
            }

        }

        public string Update_Dept(tblDept Dept)
        {
            if (Dept != null)
            {
                using (Database1Entities Obj = new Database1Entities())
                {
                    tblDept DeptObj = Obj.tblDepts.Find(Dept.Id); 
                    ////tblDept DeptObj = Obj.tblDepts.Where(x => x.Id == Dept.Id).FirstOrDefault();
                    DeptObj.DeptName = Dept.DeptName;
                    DeptObj.DeptDesc = Dept.DeptDesc;
                   
                    Obj.SaveChanges();
                    return "Dept details Updated Successfully";
                }
            }
            else
            {
                return "Dept Details Not Updated! Try Again";
            }
        }  

        public string Delete_Dept(tblDept dept) 
        {
            if (dept != null)
            {

                using (Database1Entities obj = new Database1Entities())
                {
                    //one approach to find the object Entity by Id
                    tblDept DeptObj = obj.tblDepts.Find(dept.Id); 

                    //Other approach find the object entity by Id
                   // tblDept DeptObj = obj.tblDepts.Where(x => x.Id == dept.Id ).FirstOrDefault();
                    
                    obj.tblDepts.Remove(DeptObj);
                    obj.SaveChanges();
                    return "Dept details deleted Successfully";
                }
            }
            else
            {
                return "Dept Details Not Deleted! Try Again";
            }
        }
    }
}

Step 6: Go to the Script folder and create Myscript folder and Add angularDept.js file write the code for calling Json action method from Controller like this

/// <reference path="../angular.min.js" />
var app = angular.module("DemoApp", []);

app.controller("DeptController", function ($scope, $http) {
    $scope.InsertData = function () {
        var action = document.getElementById("btnSave").getAttribute("value");
        if (action == "Submit") {
            debugger;
            $scope.Dept = {};
            $scope.Dept.DeptName = $scope.DeptName;
            $scope.Dept.DeptDesc = $scope.DeptDesc;
            $http({
                method: "post",
                url: "http://localhost:51374/Dept/Insert_Dept",
                datatype: "json",
                data: JSON.stringify($scope.Dept)
            }).then(function (response) {
                $scope.GetAllData();
                $scope.DeptName = "";
                $scope.DeptDesc = "";

            }, function () {
                alert("Error Occur");
            });
        }
        else {
            debugger;
            $scope.Dept = {};
            $scope.Dept.DeptName = $scope.DeptName;
            $scope.Dept.DeptDesc = $scope.DeptDesc;
            $scope.Dept.Id = document.getElementById("DeptID_").value;
            console.log($scope.Dept.Id);
            $http({
                method: "post",
                url: "http://localhost:51374/Dept/Update_Dept",
                datatype: "json",
                data: JSON.stringify($scope.Dept)
            }).then(function (response) {
                alert(response.data);
                $scope.GetAllData();
                $scope.DeptName = "";
                $scope.DeptDesc = "";
                document.getElementById("btnSave").setAttribute("value", "Submit");
                document.getElementById("btnSave").style.backgroundColor = "cornflowerblue";
                document.getElementById("spn").innerHTML = "Add New Dept Details";
            }, function () {
                alert("Error Occur");
            })

        }
    }

    //This is for fetching data from database.
    $scope.GetAllData = function () {
        debugger;
        $http({
            method: "get",
            url: "http://localhost:51374/Dept/Get_AllDepts"
        }).then(function (response) {
            $scope.Depts = response.data;
            console.log(response.data);
        }, function () {
            alert("Error Occur");
        })

    };

    //This is for deleting the record.
    $scope.DeleteDept = function (Dept) {
        $http({
            method: "post",
            url: "http://localhost:51374/Dept/Delete_Dept",
            datatype: "json",
            data: JSON.stringify(Dept)
        }).then(function (response) {
            alert(response.data);
            $scope.GetAllData();
        }, function () {
            alert("Error Occur");
        })
    };


    //This is for selecting record on clicking particular record.
    $scope.UpdateDept = function (Dept) {
        debugger;
        document.getElementById("DeptID_").value = Dept.Id;
        $scope.DeptName = Dept.DeptName;
        $scope.DeptDesc = Dept.DeptDesc;
        document.getElementById("btnSave").setAttribute("value", "Update");
        document.getElementById("btnSave").style.backgroundColor = "Yellow";
        
    };

});

Step 7: Create the Empty View from Dept Controller and write the binding code like this

@{
    ViewBag.Title = "Index";
}
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<div>
    <form data-ng-app="DemoApp" data-ng-controller="DeptController" ng-submit="InsertData()" data-ng-init="GetAllData()"  name="myForm" novalidate >
      <br />
        <br />
        <div class="container">
        <div class="panel panel-primary">
      <div class="panel-heading">Dept List</div>
      <div class="panel-body">
           <table cellpadding="12" class="table table-bordered table-hover">
            <tr>
                <td>
                    <b>ID</b>
                </td>
                <td>
                    <b>DeptName</b>
                </td>
                <td>
                    <b>Decscription</b>
                </td>
                
                <td>
                    <b>Actions</b>
                </td>
            </tr>
            <tr data-ng-repeat="dept in Depts">
                <td>{{dept.Id}}  
                </td>
                <td>{{dept.DeptName}}  
                </td>
                <td>{{dept.DeptDesc}}  
                </td>
                
                <td>
                    <input type="button" class="btn btn-warning" value="Update" data-ng-click="UpdateDept(dept)" />
                    <input type="button" class="btn btn-danger" value="Delete" data-ng-click="DeleteDept(dept)" />
                </td>
            </tr>
        </table>
          </div>
       </div>
      </div> 
       <div class="container">
        <div class="panel panel-primary">
      <div class="panel-heading">Dept Entry Screen</div>
      <div class="panel-body">
        <div class="form-horizontal" role="form">
            <div class="container">
                <div class="row">
                    <div class="col-sm-6 col-lg-4">
                        <div class="form-group">
                            <label class="col-md-4 control-label">Dept Name:</label>
                            <div class="col-md-8">
                                <input type="text" name="name"  placeholder="Name" data-ng-model="DeptName" data-ng-required="true" >
                                 <span ng-show="myForm.$submitted || myForm.name.$touched">
                              <span style="color:red" ng-show="myForm.name.$error.required">Name Required</span>
                              </span>
                            </div>
                        </div>
                    </div>
                    <div class="col-sm-6 col-lg-4">
                        <div class="form-group">
                            <label class="col-md-4 control-label">Dept Desc:</label>
                            <div class="col-md-8">
                                <input type="text"  id="inputDeptDesc" required placeholder="Description" name="DeptDesc" data-ng-model="DeptDesc">
                                <span ng-show="myForm.$submitted || myForm.DeptDesc.$touched">
                              <span style="color:red" ng-show="myForm.DeptDesc.$error.required">Desc Required</span>
                              </span>
                            </div>
                        </div>
                    </div>
                   
                    <div class="col-sm-6 col-lg-4">
                        <div class="form-group">
                            <div class="col-md-4 control-label"></div>
                            <div class="col-md-6">
                                 <input type="submit" value="Submit" id="btnSave" class="btn btn-info" data-ng-disabled=" myForm.name.$invalid ||myForm.DeptDesc.$invalid " />
                            </div>
                        </div>
                    </div>
                </div>
               
            </div>
        </div>
          </div>
    
    @Html.Hidden("DeptID_")

 </div>
 </div>
 </form> 
        
</div>

Step 8: Go to the BundleConfig.cs file and Include the Javascript Angular file like this

Step 9: Go to the _Layout.cshtml file and include the bundles file like this

Step 10: Now run the application, you will get the above output.

How to limit the number of object creation in c# ?


Hi All,

One time i got this question while giving the interview so i m going to write small note on this.

We can limit the number of object creation of class in C# using the static variable.

Static variable is used to share the value to all instance of that class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace LINQ_Test
{
    public partial class Object_Creation : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            MyClass obj = new MyClass();
            MyClass obj1 = new MyClass();
            MyClass obj2 = new MyClass();
            MyClass obj3 = new MyClass();
            MyClass obj4 = new MyClass();
            // MyClass obj5 = new MyClass(); 
            // Exception will throw
        }
    }

    public class MyClass
    {
        public static int Count = 0;
        public MyClass()
        {
            if (MyClass.Count==5)
            {
                throw new Exception("You canot create the object more than 5");
            }
            else
            {
                Count++;
            }
        }
    }
}

Note : In the above sample, we have created the static variable count, which will hold the incremented count value while creating the instance of that class.

How to fetching data in asp.net using MySql database


Hi All,

This is the just continues post of my previous post. In this post we will see how to fetch the data from mysql database and display in asp.net gridview.

Step 1: Create the demo asp.net project.

Step 2: Install the Mysql.Data.MySqlClient.Net dll from NuGet Package Manager Tool.

Step 3: Create the tblEmp in Mysql database like this

Step 4: Take the gridview in aspx page like this

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="MySql.aspx.cs" Inherits="Default2" %>

<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" Runat="Server">
    <br />
<div class="container">

    <div class="panel panel-primary">
      <div class="panel-heading"><b>Fetching data using Mysql data Adapter</b></div>
      <div class="panel-body">
          <div class="table-responsive">
         <asp:GridView ID="GridView1" runat="server" CssClass="table table-striped">
          </asp:GridView> 
          </div>
     
</div>
    </div>
    </div>
</asp:Content>



Step 5: Write the code in code behind file like this


using System;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;
using System.Data;

public partial class Default2 : System.Web.UI.Page
{
    MySqlConnection con = new MySqlConnection("data source=localhost;port=3306;database=test;user id=root;SslMode=none;password=admin");
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            
            fillGrid();
            //This below code is written to implement the bootstrap on Gridview
            GridView1.HeaderRow.TableSection = TableRowSection.TableHeader;
        }
    }

    private void fillGrid()
    {
        using (MySqlCommand cmd=new MySqlCommand("Select * from tblEmp",con))
        {
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
    }
}

In the above code we show that we have implemented MySql.Data.MySqlClient namespace and we are using the MySqlCommand and MySqlDataAdapter from above namespace and remaining code is same as how we write with sql server database.

Step 6: Run the application we will see the output like this

How to install My Sql database ?


Since we know that mysql is open source database. It is also the relational database. So if we have some experience with working with Microsoft sql server than we can also work with mysql server.

Recently i got a chance to work with this database, so i m writing this small post

How to install the Mysql

There are two two version on this product i.e Community and Enterprise version. Community edition is free and Enterprise version is paid product

Step 1: Download the mysql community server from given URL

MySQL Community Server

Step 2: Install the MySQL workbench community for window
MySQL Workbench Community

Step 3: Install the both Server and Workbench editor, you will get the edition like this

How to Implement the dependency injection in asp.net MVC ?


what is the dependency injection ?

Dependency Injection is a software design pattern that allow us to develop loosely coupled application.

What is the Inversion of Control (IoC) ?

Inversion of Control (IoC) refers to a programming style where a framework controls the program flow with the help of Dependency Injection.

How to Implement the Dependency Injection in Asp.net MVC application ?

We can implement the dependency injection in the following ways

Step 1: Create the MVC application.
Step 2: Go to Solution explorer and click on Manage Nuget Packages and search for Unity.mvc5. Click to install Unity.mvc5 package in ASP.NET MVC application.

Step 3: Create the Service folder in asp.net MVC application and write some simple service using interface like this

namespace Dependency_Injection_Sample.Service
{
    /// <summary>
    /// Defines the <see cref="ICompanyService" />
    /// </summary>
    public interface ICompanyService
    {
        /// <summary>
        /// This is the interface
        /// </summary>
        /// <returns>string</returns>
        string getCompany();
    }
}

Step 4: Create the CompanyService class using interface like this

namespace Dependency_Injection_Sample.Service
{
    /// <summary>
    /// This is the CompanyService class
    /// </summary>
    public class CompanyService : ICompanyService
    {
        /// <summary>
        /// Defines the Name
        /// </summary>
        private const string Name = "Chandradev Software Pvt Ltd.";

        /// <summary>
        /// This will return the company Name
        /// </summary>
        /// <returns>string</returns>
        public string getCompany()
        {
            return Name;
        }
    }
}


Step 5: Go to the UnityConfig file and register the components like this

Step 6: Now go to the home controller and create the dependency injection using private constructor like this

Step 7: In index page, you call the Viewbag like this

@ViewBag.CompanyName

Step 7: Now run the application, you will see the output like this

How to implement Angular Js UI-Grid in Asp.net MVC ?


UI-Grid is one of the most popular and stable open source angularJs Grid. It is completely written using angular js. It is very fast. It contains almost all the features which contains the commercial third party grid controls.

For more details, please go to this official site

http://ui-grid.info/

To implement in asp.net MVC, we can do like this

Step 1: Create the asp.net MVC application.

Step 2: Create the tblEmp with (Id,EmpName,EmpAddress,EmailId) field in database and keep some data for demo.

Step 3: Create the Emp Model class like this in Model folder

namespace UI_Grid_Sample.Models
{
    public class Emps
    {
        public int Id { get; set; }
        public string EmpName { get; set; }
        public string EmpAddress { get; set; }
        public string EmailId { get; set; }
        public string Operation { get; set; }
    }
}

Step 4: Create the Emp Controller using Asp.net Web API and Write the code to Fetch data from tblEmp like this



using System;
using System.Collections.Generic;
using System.Web.Http;
using System.Data.SqlClient;
using UI_Grid_Sample.Models;

namespace UI_Grid_Sample.Services
{
    public class EmpController : ApiController
    {
        const string sConnString = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integrated Security=True";

        List<Emps> MyEmps = new List<Emps>();
       

        [HttpPost]
        public IEnumerable<Emps> FetchBookList(Emps objList)
        {
            using (SqlConnection con = new SqlConnection(sConnString))
            {
                SqlCommand objComm = new SqlCommand("SELECT Id,EmpName,EmpAddress,EmailId FROM tblEmp", con);
                con.Open();

                SqlDataReader reader = objComm.ExecuteReader();

                while (reader.Read())
                {
                    MyEmps.Add(new Emps
                    {
                        Id = Convert.ToInt32(reader["Id"]),
                        EmpName = reader["EmpName"].ToString(),
                        EmpAddress = reader["EmpAddress"].ToString(),
                        EmailId =  reader["EmailId"].ToString()
                    });
                }
                con.Close();
            }

            return MyEmps;
        }

        
    }
}  

Step 5: Create One controller in asp.net MVC i.e. Emp

using System.Web.Mvc;

namespace UI_Grid_Sample.Controllers
{
    public class EmpController : Controller
    {
        //
        // GET: /Emp/

        public ActionResult Index()
        {
            return View();
        }

    }
}
 

Step 5: Create a view and add the reference for angularjs, UIGrid, UIGrid Css and write the code for consuming consuming Web API method in AngularJs as given below



@{
    ViewBag.Title = "Index";
}
<script src="http://ajax.googleapis.com/ajax/libs/angularjs/1.5.0/angular.js"></script>
 <script src="http://ui-grid.info/release/ui-grid.js"></script>
 <link rel="stylesheet" href="http://ui-grid.info/release/ui-grid.css" type="text/css">

   
    <style>
        p, div {
            font:15px Verdana;
        }
        .uiGrd {
            width: 580px;
            height: 200px;
        }
    </style>

<script type="text/javascript">
    var myApp = angular.module('myApp', ['ui.grid']);
    myApp.controller('myController',
        function ($scope, $http) {

            FetchEmpList('READ');

            function FetchEmpList(ops) {
                var request = {
                    method: 'post',
                    url: '/api/emp/',
                    data: {
                        Operation: ops
                    },
                    dataType: 'json',
                    contentType: "application/json"
                };

                $scope.arrEmps = new Array;

                // POST DATA WITH $http.
                $http(request)
                    .success(function (data) {
                        var i = 0;      // JUST A COUNTER.

                        // LOOP THROUGH EACH DATA.
                        angular.forEach(data, function () {
                            var b = {
                                EmpId: data[i].Id,
                                EmpName: data[i].EmpName,
                                EmpAddress: data[i].EmpAddress,
                                EmailId: data[i].EmailId
                            };

                            $scope.arrEmps.push(b);
                            i += 1;
                        });

                    })
                    .error(function () {

                    });

                $scope.gridData = { data: 'arrEmps' };
            };
        });

</script>

<h2>Sample code for AngularJs GridUI </h2>

<div ng-app="myApp"  ng-controller="myController">
       
        
        <div class="uiGrd" id="grd" ui-grid="gridData"></div>
</div>