Posts Tagged Database

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.

 

Advertisements

, , , , ,

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

How to check the ID availability in Database using Jquery Ajax.

Introduction:

In this article I am going to explain how to check the data availability in database using Jquery Ajax.

Description:

Here I am going to explain one of the options to check if record is available in database and show error message according to the availability. This method or logic which I have explained here can be useful to check if the username is already used or it is available to use.

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.

Implementation:

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

HTML:

 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="http://code.jquery.com/jquery-1.10.2.min.js"></script>
    <script type="text/javascript">
        function checkIDAvailability() {
            $.ajax({
                type: "POST",
                url: "JqueryAjaxPost.aspx/checkUserName",
                data: "{IDVal: '" + $("#<% =txtID.ClientID %>").val() + "' }",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: onSuccess,
                failure: function (AjaxResponse) {
                    document.getElementById("Label1").innerHTML = "Error Occured";
                }
            });
            function onSuccess(AjaxResponse) {
                document.getElementById("Label1").innerHTML = AjaxResponse.d;
            }
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        Enter your ID here
     <asp:TextBox ID="txtID" runat="server" onblur="checkIDAvailability();"></asp:TextBox>
        <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
    </div>
    </form>
</body>
</html>

C#:

This method will get called from clientside using Jquery Ajax. You need to ensure that you have added the WebMethod attribute to the method.

 
        //Server Side Method which will get called from Client Side
        //Ensure that you have declared the method like WebMethod
        [System.Web.Services.WebMethod(EnableSession = true)]
        public static string checkUserName(string IDVal)
        {
            string result = string.Empty;
            //Get your connection string here
            string conString = System.Configuration.ConfigurationManager.ConnectionStrings["AdventureWorks2008R2ConnectionString2"].ConnectionString;
            //Change your query here
            string qry = "Select AddressID from Person.Address Where AddressID =@AddressID";
            SqlDataAdapter da = new SqlDataAdapter(qry, conString);
            //Pass the value to paramter
            da.SelectCommand.Parameters.AddWithValue("@AddressID", IDVal.Trim());
            DataSet ds = new DataSet();
            da.Fill(ds, "IDTable");
            //Check if dataset is having any value
            if (ds.Tables["IDTable"].Rows.Count > 0)
            {
                // User Name Not Available
                result = "ID already in use";
            }
            else
            {
                //User_Name is available
                result = "ID is available, you can use it";
            }
            //Return the result
            return result;
        }

Demo

, , , , , ,

Leave a comment