Posts Tagged Database

Simple CRUD Application in Asp.Net Core Razor Pages

Introduction:

This article presents how to produce a simple application using Razor Pages in Asp.Net Core. This application will do the CRUD operation on a SQL Server database and then displays the details in a table.

You require to have Visual Studio 2017 Version 15.3 and .NET Core 2.0 installed in order to work with Razor Page Applications. You can get more details from here

Implementation

Create a new Project in Visual Studio

CoreProject

And Select the Web Application Template for Razor Pages

RazorPages

Project Structure

If you take a look at the project structure you will notice the main difference in Razor Pages is that there is No Controller and Views folder. All files in Razor Pages are inside the “pages” folder. You can also create subfolders within the pages folder to keep the files organized.

Another important point to note is the @page directive on the RazorPages. @page directive will cause the file to handle the requests directly, without utilizing a controller.

Sample Application:

Now let’s start building a simpler application which performs CRUD operations on Employee database using Razor Pages. Below is a sample demonstration of the application we are going to make.

demo

Model and Database

To load the information from database, we are using EntityFrameworkCore and its associated methods.  Make sure you install the EF Core nugget packages from here

In this example I have used the DatabaseFirstApporach.  DatabaseFirstApporach lets the EnityData Model to generate classes, dbcontext etc from the database automatically. All other functionalities like database and model sync exists as is.

Let’s create an entity class called “Employee” which matches the schema of the Employee Table in the database

Next we will add a DbContext class , which will act as a session between entity class and database.

Next we will provide the connection string for database in appsettings.json file

Finally we will register dbContext using dependency injection. Modify the startup.cs file and add the below code

Adding Views for Create, Edit, Update, Delete (CRUD)

Create View (CreatePage.cshtml)

On Create page we used 3 directives.

@page: Razor Pages mandates that “@page” directive should be the first directive on page.

@inject: Another directive you can find on page is @inject. @inject directive is used for dependency injection in to views. Using @inject we inject the dbContext to View, which will be used to for all db related operations.

@functions: In Razor page, we can add the function-level content code(C#) to view. We can also have the content in code behind CreatePage.cshtml.cs page.

Edit View

Next we will see the EditPage.  Most of the coding are similar to create page which we already seen earlier. However in this page, we need to pass the selected row id as route parameter

We used asp-route-{value} attribute from anchor tag helper to generate the Edit Link. At runtime the {value} field (in our example ‘id’) will be mapped to the route parameter value which will be used by Edit Page to retrieve the selected row value.

<a asp-page="/EditPage" asp-route-id="@item.EmployeeID">Edit</a>

EditPage.cshtml.cs

All other pages have the similar code structure. The difference is only the operation performed on each pages. All codes have comments added which are select explanatory

Index View(Home Page)

Delete View

DeletePage.cshtml.cs

You can find complete code of this demo from here

Advertisements

, , , ,

Leave a comment

How to populate Angular UIGrid in MVC using AngularJS

Description

In this article I will explain the steps for populating UI-Grid in angularjs from database using WebAPI. UI-grid is a powerfull tool which provides a table like representation. Apart from table like structure Ui-Grid also provides functionality like filtering, sorting, exporting etc.  You can find more details about UI-Grid from here

PreRequisites:

Ui-Grid is dependant on AngularJS.Hence ensure that you have added reference of Angular js on page.

Implementation:

Model:

To load the data from database, we are using EntityFramework and its related method. In this example I have used the DatabaseFirstApporach.  DatabaseFirstApporach lets the EnityData Model to generate classes, dbcontext etc from the database automatically. All other functionalities like database and model sync exists as is.

Follow the below procedures to generate Model for Customer database in Northwind database

  • Right click on the Model folder
  • Select Add option
  • And then select the New Item option
  • Choose Data from Installed Templates group
  • Select the ADO.NET Entity Data Model option and Provide a name for it
  • In the Entity Data Model Wizard, Select EF Designer from database and click on Next
  • Choose the Connection string options
    • If you already have connection string configured then you can select that from the available list.
    • If you don’t have the connection string configured then use the New Connection option and create the connection
  • Expand the Table option and then select the tables which you want
  • Click on Finish

You will have necessary class files generated in your solution now

Northwind

You can get more details about how to use DatabaseFirst approach from this link.

Controller (WebAPI)

In this example we are using WebAPI to load the data. The next procedure is to write code to fetch records from database using WebAPI.  For this demo I have used a basic select operation. You can modify this as per your need.

Another point to note here is UI-grid read data which is in Json format, Hence we need to ensure that the data which we return from controller is of Json format. We will use the JsonResult method for this.

View:

On View we will have the codes for UI-Grid and its related files. If you look at the design of UI-Grid it’s a div which will be styled to look like a table at runtime.

We need to add div with an attribute as ui-grid=”gridOptions”.  We add all the details from angular js code to gridOptions and UI-Grid at runtime uses this div to populate the grid.

RouteConfig Changes:

I have changed the Action Name from “Get” to “GetCustomer” to be more specific with what the method do. In order for api to identify the proper method add the below route setting to web api route config

Angular JS Script

Below are the code to fetch the data from database and populate it to UI-Grid. I have used a factory to call the Get Customer method and used that in the main method.

 

, , , , ,

1 Comment

How to load data from database to Asp.Net Gridview using WebAPI

Introduction:

In this article we are discussing how to load data from database to Asp.Net Gridview using WebAPI.

Description:

We use WebAPI to get the data from database and then use the Jquery getJSON() method to Load JSON-encoded data from the server using a GET HTTP request.

Implementation:

In the New ASP.NET Project dialog, select the Empty template. Under “Add folders and core references for”, check WebForms , Web API. Click OK.

2016-04-30 19_11_49-Microsoft Visual Studio (Administrator)

When using WEBAPI you will get data in client side and you need to manipulate it at client side so that the data will get populated in Gridivew. Point to note here is we cannot go with the normal approach of setting DataSource and calling DataBind method. We have various options to load data into grid like AngularJs, Jquery etc.

As mentioned earlier in this article I am using getJSON() method to fetch the value and then populate the data in gridview. Gridview will load as table at runtime we will access this table and then append the values to it.

2016-04-30 14_29_24-http___localhost_52678_WebForm1.aspx - Internet Explorer

Sample Code:

HTML:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <script src="https://code.jquery.com/jquery-2.1.4.js"></script>
    <script type="text/javascript">
        //Code to load the data to gridview on pageload
        $(document).ready(function () {
           //Here you need to provide the webapi address
            $.getJSON('api/Customer')
                .done(function (data) {
                    $.each(data, function (key, item) {
                        //Add the result to gridview
                        $("#GridView1").append("

<tr>

<td>" + item.CustomerID + "</td>


<td>" + item.CompanyName + "</td>


<td>" + item.ContactName + "</td>


<td>" + item.ContactTitle + "</td>


<td>" + item.Address + "</td>

</tr>
");
                    });
                })
            .fail(function (d) {
                alert("error occured" + d.responseText);
            });
        });
    </script>
</head>
<body>
<form id="form1" runat="server">
<div>
            <asp:GridView ID="GridView1" runat="server" Width="1000px" ShowHeaderWhenEmpty="true"                 CellPadding="0" CellSpacing="0">
            </asp:GridView></div>
</form>

</body>
</html>

C#:

First we need to populate an empty row in GridView. Reason behind this is if we didn’t assign any datasource then the GridView won’t populate at runtime and we won’t be able to access it at client side.

protected void Page_Load(object sender, EventArgs e)
        {
            //Load and empty row to gridview
            if (!IsPostBack)
            {
                DataTable dt = new DataTable();
                dt.Columns.Add(&quot;CustomerID&quot;);
                dt.Columns.Add(&quot;CompanyName&quot;);
                dt.Columns.Add(&quot;ContactName&quot;);
                dt.Columns.Add(&quot;ContactTitle&quot;);
                dt.Columns.Add(&quot;Address&quot;);
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
        }

WebAPI Code:

When we add the WebAPI references in your project you will see a Model folder and Controller folder.

2016-04-30 19_20_50-WebAPISample - Microsoft Visual Studio (Administrator)

Model

Here we add the Model classes which basically represents data in WebAPI. For this article we add Customer class. Right Click on the Model folder and choose Add and then select class file. Name it like Customer

public class Customer
    {
        public string CustomerID { get; set; }
        public string CompanyName { get; set; }
        public string ContactName { get; set; }
        public string ContactTitle { get; set; }
        public string Address { get; set; }
    }

Controllers:

In controller we will add the code to fetch values from database. Right click on the Controllers folder and Choose Add and then select Controller. In code you can see an HttpGet entry added above to GetCustomers method. This is because Rest WebAPI is making use of verbs(Get,Post,Put,Delete) for each actions in Read,Create,Update,Delete respectively. You can get more details from here

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using WebApiSample.Models;

namespace WebApiSample.Controllers
{
    public class CustomerController : ApiController
    {
        [HttpGet]
        public IEnumerable<Customer> GetCustomers()
        {
            Customer[] aryCustomers = null;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NORTHWINDConnectionString"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "SELECT TOP 10 CustomerID,CompanyName,ContactName,ContactTitle,Address FROM Customers";
                    conn.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        List<Customer> lstcustomers = new List<Customer>();
                        while (reader.Read())
                        {
                            lstcustomers.Add(new Customer
                            {
                                CustomerID = reader["CustomerID"].ToString(),
                                CompanyName = reader["CompanyName"].ToString(),
                                ContactName = reader["ContactName"].ToString(),
                                ContactTitle = reader["ContactTitle"].ToString(),
                                Address = reader["Address"].ToString()
                            });
                            aryCustomers = lstcustomers.ToArray();
                        }
                    }
                }
            }
            return aryCustomers;
        }
    }
}

, , , , , , ,

Leave a comment

Cascading Dropdownlist using AngularJs in Asp.Net

Introduction:

In this article I am going to explain you how to implement the cascading dropdownlist using AngularJs.  This is a continuation to my previous article where I have blogged about how to populate the Dropdownlist from AngularJs. Hence I won’t be explaining the first part where we populate the Order Dropdownlist. You can get the details about that from link above.

Implementation:

Here we make use of ng-change event in AngularJs to call the function which in turn populate the CustomerList based on OrderID selected. ng-change event will fire when user changes the input in element  and the new values to be committed to model.

AngularJS code to populate the Order and Customer Dropdownlist

HTML:

C#:

Demo

, , ,

2 Comments

How to populate Dropdownlist from database using AngularJS ng-options Attribute

Introduction:

In this article I am going to explain how to populate dropdownlist control from database using AngularJS. AngularJS lets you extend HTML vocabulary for your application.

Prerequisites:

You need to add the necessary AngularJS files to your page prior to use this plugin. You can either download the file from here and add to your project or use the AngularJS file directly from CDN.

<script src="http://ajax.googleapis.com/ajax/libs/angularjs/1.3.14/angular.min.js"></script>

Implementation:

Here we are make use of Jquery Ajax post method to get the record from database and then pass it to dropdownlist. We will be using $http service function which communicates via the browser’s XMLHttpRequest object or via JSONP.

AngularJS code to populate the dropdownlist.

<script type="text/javascript">
        angular.module('drpdwnApp', []).controller('drpdwnCtrl', function ($scope, $http) {
            $scope.ProductList = null;
            //Declaring the function to load data from database
            $scope.fillProductList = function () {
                $http({
                    method: 'POST',
                    url: 'AngularCascadingDropDownList.aspx/GetProductList',
                    data: {}
                }).success(function (result) {
                    $scope.ProductList = result.d;
                });
            };
            //Calling the function to load the data on pageload
            $scope.fillProductList();
        });
    </script>

Now we will use the ng-options attribute to generate option for dropdownlist from the passed data.

    <div ng-app="drpdwnApp" ng-controller="drpdwnCtrl">
        <select ng-model="drpdpwnvalue" ng-options="item.OrderID for item in ProductList">
            <option value="" label="Select and item"></option>
        </select>
    </div>

HTML:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="http://ajax.googleapis.com/ajax/libs/angularjs/1.3.14/angular.min.js"></script>
    <script type="text/javascript">
        angular.module('drpdwnApp', []).controller('drpdwnCtrl', function ($scope, $http) {
            $scope.ProductList = null;
            //Declaring the function to load data from database
            $scope.fillProductList = function () {
                $http({
                    method: 'POST',
                    url: 'AngularCascadingDropDownList.aspx/GetProductList',
                    data: {}
                }).success(function (result) {
                    $scope.ProductList = result.d;
                });
            };
            //Calling the function to load the data on pageload
            $scope.fillProductList();
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div ng-app="drpdwnApp" ng-controller="drpdwnCtrl">
        <select ng-model="drpdpwnvalue" ng-options="item.OrderID for item in ProductList">
            <option value="" label="Select and item"></option>
        </select>
    </div>
    </form>
</body>
</html>

C#:

[System.Web.Services.WebMethod()]
        public static List<OrderList> GetProductList()
        {
            List<OrderList> list = new List<OrderList>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NORTHWINDConnectionString"].ConnectionString))
            {
                conn.Open();
                string strquery = "Select TOP 10 * from Orders";
                using (SqlCommand cmd = new SqlCommand(strquery, conn))
                {
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        OrderList objorder = new OrderList(int.Parse(reader["OrderID"].ToString()), reader["ShipAddress"].ToString(), reader["ShipName"].ToString(), double.Parse(reader["Freight"].ToString()));
                        list.Add(objorder);
                    }
                }
 
            }
            return list;
        }
 
        public class OrderList
        {
            public int OrderID;
            public string ShipName;
            public string ShipAddress;
            public double Freight;
            public OrderList(int orderID, string shipName, string shipAddress, double freight)
            {
                OrderID = orderID;
                ShipName = shipName;
                ShipAddress = shipAddress;
                Freight = freight;
            }
        }

, , , ,

5 Comments

Search and Populate GridView from database using Jquery Ajax

Introduction:

In this article I am going to explain how to search and Populate GridView from database using Jquery Ajax.

Prerequisites:

You need to add the necessary JQuery files to your page prior to use this plugin. In this example I have used the online JQuery CDN files available. If you don’t want to use the online available cdn file then you can manually add the jquery files to your solution. I have explained the steps here in detail.

I have used Address table from AdventureWorks Database. You can download it from here.

Implementation:

Here we are make use of Jquery Ajax post method in Jquery to check if record exists in database and get the details on the blur event of textbox.

HTML

 <html xmlns="http://www.w3.org/1999/xhtml">
 <head runat="server">
     <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
     <title runat="server"></title>
     <script src="http://code.jquery.com/jquery-1.10.2.min.js"></script>
     <script type='text/javascript'>
         $(document).ready(function () {
             //Attach on change event to textbox here
             $("#TextBox1").change(function () {
                 //Remove all previously added rows in gridview
                 $("[id*=sampleGV] tr").not($("[id*=sampleGV] tr:first-child")).remove();
                 $.ajax({
                     type: "POST",
                     //Call the method to get data from database 
                     url: "WebForm4.aspx/GetOrderDetails",
                     //Pass the value from textbox to method
                     data: '{"addressID":' + $("#TextBox1").val() + '}',
                     contentType: "application/json; charset=utf-8",
                     dataType: "json",
                     success: function (result) {
                         //Add the resul to gridview
                         $("#sampleGV").append("<tr><td>" + result.d[0][0] + "</td><td>" + result.d[0][1] + "</td><td>" + result.d[0][2] + "</td></tr>");
                     },
                     failure: function (response) {
                         alert(response.d);
                     },
                     error: function (response) {
                         alert(response.d);
                     }
                 });
 
             });
         });
     </script>
 </head>
 <body class="myCss">
     <div>
         <form id="form1" runat="server" class="hidden">
             <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
             <asp:GridView ID="sampleGV" runat="server" Width="1000px" ShowHeaderWhenEmpty="true"
                 CellPadding="0" CellSpacing="0">
             </asp:GridView>
         </form>
     </div>
 </body>
 </html>
 

C#:

First we need to populate an empty row in GridView. Reason behind this is if we didn’t assign any datasource then the GridView won’t populate at runtime and we won’t be able to access it at client side

 protected void Page_Load(object sender, System.EventArgs e)
 {
         //Load and empty row to gridview
         if (!IsPostBack) {
                DataTable dt = new DataTable();
                dt.Columns.Add("AddressID");
                dt.Columns.Add("AddressLine1");
                dt.Columns.Add("City");
                sampleGV.DataSource = dt;
                sampleGV.DataBind();
         }
 }
 

Next is the GetOrderDetails method which will read the parameter from client side and the search the database and then pass the result set back. Note the [Services.WebMethod()] attribute I added to the GetOrderDetails method,this is important  otherwise the serverside method will not be called from client side jQuery AJAX call.

[Services.WebMethod()]
public static Array GetOrderDetails(string addressID)
{
	//Create sql connection object here
	SqlConnection conn = null;
	//Create sql DataTable object here
	DataTable dt = new DataTable();
	//Create sql SqlDataAdapter object here
	SqlDataAdapter da = default(SqlDataAdapter);
	//Create sql ArrayList object here
	ArrayList rows = new ArrayList();
	//Get the connectionstring from webconfig file
	string connection = ConfigurationManager.ConnectionStrings("Adventure WorksConnectionStringPersonal").ConnectionString;
	//Create sql SqlConnection object here and assign the conenctionstring
	conn = new SqlConnection(connection);
	//Create sql query here
	string sql = "SELECT AddressID,AddressLine1,City FROM Person.Address a WHERE a.AddressID = @AddressID";
	//Create sql SqlCommand and assign connection and command here
	SqlCommand cmd = new SqlCommand(sql, conn);
	//Pass your parameter here
	cmd.Parameters.AddWithValue("@AddressID", addressID);
	//Open the SQLConnection here
	conn.Open();
	//Create SqlDataAdapter object here
	da = new SqlDataAdapter(cmd);
	using (DataSet ds = new DataSet()) {
		//Load the DataTable
		da.Fill(dt);
		foreach (DataRow dataRow in dt.Rows) {
			rows.Add(dataRow.ItemArray.Select(item => item.ToString()));
		}
		//Create an array and then return the values
		return rows.ToArray();
	}
	conn.Close();
}

Demo

, , , ,

Leave a comment

How to implement a GridView in MVC

Introduction:

In this article I am going to explain how to implement a GridView in MVC.

Prerequisites:

We are going to use Grid.Mvc Jquery plugin to implement GridView in MVC. You can get more details of this control from here.

To use Grid.Mvc we have to first add the necessary Jquery files to solution. One easy option to do this is to use the Nugget package to add Grid.Mvc to your solution. You can use the below command

install-package Grid.Mvc

After successful installation you will be able to see the below messages.

You can also download the file and add it to your solution manually.

Implementation:

Here I am going to explain how to implement GridView in MVC without using the table approach.

Model:

First you need to create a class in the Model. In this example I have used the DatabaseFirst approach.

You can use the below steps to generate the class files

  • Right click on the Model folder
  • Select Add option
  • And then select the New Item option
  • Select the Net Entity Data Model option and Provide a name for it
  • In the Entity Data Model Wizard, Select EF Designer from database and click on Next

If you already have connection string configured then you can select that from the available list.

If you don’t have the connection string configured then use the New Connection option and create the connection

  • Expand the Tableoption and then select the tables which you want
  • Click on Finish
  • You will have necessary class files generated in your solution now

You can get more details about how to use DatabaseFirst approach from this link.

Controller:

Now we have the Model files created from database. Next step is to fetch the data from database and then pass it to the View from Controller. We will have a basic select in EF for this demo. You can modify it as per your need.

  public ViewResult Index()
        {
            //Create db context object here this is test record
            AdventureWorksDbContext dbContext = new AdventureWorksDbContext();
            //Get the value from database and then Pass it View
            var items = dbContext.Addresses.Take(10);
            return View(items.ToList());
        }

View:

In View you can use the below code to create GridView in MVC. This will generate a very basic GridView in MVC View

@model IEnumerable<MvcApplication1.Models.Address>
<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <!-- CSS Includes -->
    <link rel="stylesheet" href="//netdna.bootstrapcdn.com/bootstrap/3.1.1/css/bootstrap.min.css">
    <link href="@Url.Content("~/Content/Gridmvc.css")" rel="stylesheet" type="text/css" />
    <!-- Script Includes-->
    <script src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
    <script src="@Url.Content("~/Scripts/gridmvc.min.js")" type="text/javascript"> </script>
</head>
<body>
    <div style="width:550px">
        @*Refer the GridView reference here*@
        @using GridMvc.Html
        @Html.Grid(Model).Columns(columns =>
           {
               //Put the column details here
               columns.Add(s => s.AddressID).SetWidth(110).Sortable(true);
               columns.Add(s => s.AddressLine1).Sortable(true).SetWidth(110);
               columns.Add(s => s.City).Sortable(true).SetWidth(110);
               columns.Add(s => s.ModifiedDate).Sortable(true).SetWidth(110);
           }).WithPaging(20)
    </div>
</body>
</html>

Demo

Here I have used very little options, Grid.Mvc plugin supports variety of options like CustomColumns, Button controls in column, Editing in Gridview. You can get more details of this control from here.

, , ,

Leave a comment